PostgreSQL存储过程(2)-基于PL/PgSQL的存储过程

时间：2022-08-02 12:06:16

介绍

　　PL/pgSQL 是PostgreSQL 数据库系统的一个可加载的过程语言。 PL/pgSQL 的设计目标是创建一种可加载的过程语言，可以 用于创建函数和触发器过程, 为SQL 语言增加控制结构, 执行复杂的计算 继承所有用户定义类型、函数、操作符, 定义为被服务器信任的语言。PL/pgSQL创建的函数可以在那些使用内置函数一样的情形下使用。 比如，可以创建复杂的条件计算函数，并随后将之用于定义操作符或者用于函数索引中。

使用PL/pgSQL的优点

  • SQL是PostgreSQL和大多数其它关系型数据库的命令语言。
  • 它是可移植的并且容易学习使用。但是所有SQL语句都必须由数据库服务器独立地执行.
  • 通过PL/pgSQL，可以把运算块和一系列命令在数据库服务器内部组成一个块， 这样就拥有了过程语言的能力并且简化 SQL 的使用， 因而节约了大量的时间，因为不需要进行客户端/服务器通讯。
  • 消除了服务器和客户端之间的往返通信。
  • 客户端不需要的中间结果无需在服务器端和客户端来回传递。
  • 不需要额外的语法分析步骤。比起不使用存储函数来，这样做能够产生明显的性能提升

块结构

　　PL/pgSQL是一个块结构语言。函数定义的所有文本都必须是一个块。一个块用下面的方法定义：
　　PL/pgSQL程序由三个部分组成，即声明部分、执行部分、异常处理部分。
　　PL/pgSQL的结构如下：

DECLARE

   --声明部分: 在此声明PL/SQL用到的变量,类型及游标.

 BEGIN

   -- 执行部分:  过程及SQL语句,即程序的主要部分

 EXCEPTION

   -- 执行异常部分: 错误处理

 END;

其中：执行部分不能省略。

  • 块中的每一个declaration和每一条statement都由一个分号终止
  • 块支持嵌套，嵌套时子块的END后面必须跟一个分号，最外层的块END后可不跟分号
  • BEGIN后面不必也不能跟分号，END后跟的label名必须和块开始时的标签名一致
  • 所有关键字都不区分大小写。标识符被隐含地转换成小写字符，除非被双引号包围
  • 声明的变量在当前块及其子块中有效，子块开始前可声明并覆盖（只在子块内覆盖）外部块的同名变量
  • 变量被子块中声明的变量覆盖时，子块可以通过外部块的label访问外部块的变量

变量声明

声明一个变量的语法如下：

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

这跟oracle中的存储过程声明变量是一样的。如

name text := 'lottu';

empno numeric := 110;

PL/pgSQL程序设计中的变量定义与SQL的标识符定义的要求相同。要求和限制有：

  • 标识符名不能超过30字符；
  • 第一个字符必须为字母；
  • 不分大小写；
  • 不能用’-‘(减号);
  • 不能是SQL保留字。

参数

声明一个参数的语法如下：

name [in|out|in out] type

如果只指定输入参数类型，不指定参数名，则函数体里一般用$1，$n这样的标识符来使用参数。

CREATE OR REPLACE FUNCTION discount(NUMERIC)

RETURNS NUMERIC

AS $$

BEGIN

    RETURN $1 * 0.8;

END;

$$ LANGUAGE PLPGSQL;

但该方法可读性不好，此时可以为$n参数声明别名，然后可以在函数体内通过别名(ALIAS FOR)指向该参数值。

CREATE OR REPLACE FUNCTION discount(NUMERIC)

RETURNS NUMERIC

AS $$

DECLARE

    total ALIAS FOR $1;

BEGIN

    RETURN total * 0.8;

END;

$$ LANGUAGE PLPGSQL;

幸好PostgreSQL提供另外一种更为直接的方法来声明函数参数，即在声明参数类型时同时声明相应的参数名。

CREATE OR REPLACE FUNCTION discount(total NUMERIC)

RETURNS NUMERIC

AS $$

BEGIN

    RETURN total * 0.8;

END;

$$ LANGUAGE PLPGSQL;

注释

在PL/SQL里，可以使用两种符号来写注释，即：

使用双 ‘-‘ ( 减号) 加注释PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：

-- This is a single-line comments ...

使用 /*   */  来加一行或多行注释，如：

/*

 This is a multiline comment ...

*/

案例分析

　　采用跟oracle的plsql存储过程做对比。

--使用PL/PgSQL语言的函数定义如下：

CREATE FUNCTION somefunc() RETURNS integer AS $$

DECLARE

    quantity integer := 30;

BEGIN

    -- Prints 30

    RAISE NOTICE 'Quantity here is %', quantity;

    quantity := 50;

    -- Create a subblock

    DECLARE

        quantity integer := 80;

    BEGIN

        -- Prints 80

        RAISE NOTICE 'Quantity here is %', quantity;

    END;

    -- Prints 50

    RAISE NOTICE 'Quantity here is %', quantity;

    RETURN quantity;

END;

$$ LANGUAGE plpgsql;
-- 使用plsql语言编写如下：

DECLARE

  quantity number := 30;

BEGIN

  -- Prints 30

  DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);

  quantity := 50;

  -- Create a subblock

    DECLARE

      quantity number := 80;

    BEGIN

      -- Prints 80

      DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);

    END;

    -- Prints 50

  DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);

END;

该案例而说；postgres存储过程跟oracle存储过程结构很相似。postgres中的'RAISE NOTICE'替换了oracle中'DBMS_OUTPUT.PUT_LINE'。

函数重载（Overwrite）

　　在PostgreSQL中，多个函数可共用同一个函数名，但它们的参数必须得不同。这一规则与面向对象语言（比如Java）中的函数重载类似。也正因如此，在PostgreSQL删除函数时，必须指定其参数列表，如：

DROP FUNCTION get_array(anyelement, anyelement);

另外，在实际项目中，经常会用到CREATE OR REPLACE FUNCTION去替换已有的函数实现。如果同名函数已存在，但输入参数列表不同，会创建同名的函数，也即重载。如果同名函数已存在，且输入输出参数列表均相同，则替换。如果已有的函数输入参数列表相同，但输出参数列表不同，则会报错，并提示需要先DROP已有的函数定义。

相关文章