基本语句

在编写PL/SQL过程中，会定义一些变量，给变量赋值，调用其他存储过程等。介绍PL/SQL中的基本语句，包括定义变量、赋值语句、调用语句以及返回语句。

说明: 尽量不要在存储过程中调用包含密码的SQL语句，因为存储在数据库中的存储过程文本可能被其他有权限的用户看到导致密码信息被泄漏。如果存储过程中包含其他敏感信息也需要配置存储过程的访问权限，保证敏感信息不会泄漏。

定义变量

介绍PL/SQL中变量的声明，以及该变量在代码中的作用域。

变量声明

变量声明语法请参见图1。

图 1 declare_variable::=

对以上语法格式的解释如下：

• variable_name：变量名。
• type：变量类型。
• value：该变量的初始值（如果不给定初始值，则初始为NULL）。value也可以是表达式。

示例

MogDB=# DECLARE
    emp_id  INTEGER := 7788;  --定义变量并赋值
BEGIN
    emp_id := 5*7784;  --变量赋值
END;
/

变量类型除了支持基本类型，还可以是使用%TYPE和%ROWTYPE去声明一些与其他表字段或表结构本身相关的变量。

%TYPE属性

%TYPE主要用于声明某个与其他变量类型（例如，表中某列的类型）相同的变量。假如我们想定义一个my_name变量，它的变量类型与employee的firstname类型相同，我们可以通过如下定义：

my_name employee.firstname%TYPE

这样定义可以带来两个好处，首先，我们不用预先知道employee表的firstname类型具体是什么。其次，即使之后firstname类型有了变化，我们也不需要再次修改my_name的类型。

TYPE employee_record is record (id INTEGER, firstname VARCHAR2(20));
my_employee employee_record;
my_id my_employee.id%TYPE;
my_id_copy my_id%TYPE;

%ROWTYPE属性

%ROWTYPE属性主要用于对一组数据的类型声明，用于存储表中的一行数据或从游标匹配的结果。假如，我们需要一组数据，该组数据的字段名称与字段类型都与employee表相同。我们可以通过如下定义：

my_employee employee%ROWTYPE

同样可以使用在cursor上面，该组数据的字段名称与字段类型都与employee表相同（对于PACKAGE中的cursor，可以省略%ROWTYPE）。%TYPE也可以引用cursor中某一列的类型，我们可以通过如下定义：

cursor cur is select * from employee;
my_employee cur%ROWTYPE
my_name cur.firstname%TYPE
my_employee2 cur -- 对于PACKAGE中定义的cursor，可以省略%ROWTYPE字段

须知:

• %TYPE不支持引用复合类型或RECORD类型变量的类型、RECORD类型的某列类型、跨PACKAGE复合类型变量的某列类型、跨PACKAGE cursor变量的某列类型等。
• %ROWTYPE不支持引用复合类型或RECORD类型变量的类型、跨PACKAGE cursor的类型。

变量作用域

变量的作用域表示变量在代码块中的可访问性和可用性。只有在它的作用域内，变量才有效。

• 变量必须在declare部分声明，即必须建立BEGIN-END块。块结构也强制变量必须先声明后使用，即变量在过程内有不同作用域、不同的生存期。
• 同一变量可以在不同的作用域内定义多次，内层的定义会覆盖外层的定义。
• 在外部块定义的变量，可以在嵌套块中使用。但外部块不能访问嵌套块中的变量。

赋值语句

语法

给变量赋值的语法请参见图2。

图 2 assignment_value::=

对以上语法格式的解释如下：

• variable_name：变量名。
• value：可以是值或表达式。值value的类型需要和变量variable_name的类型兼容才能正确赋值。

示例

MogDB=# DECLARE
    emp_id  INTEGER := 7788;--赋值
BEGIN
    emp_id := 5;--赋值
    emp_id := 5*7784;
END;
/

嵌套赋值

给变量嵌套赋值的语法请参见图3。

图 3 nested_assignment_value::=

对以上语法格式的解释如下：

• variable_name：变量名。
• col_name：列名。
• subscript：下标，针对数组变量使用，可以是值或表达式，类型必须为int。
• value：可以是值或表达式。值value的类型需要和变量variable_name的类型兼容才能正确赋值。

示例

MogDB=# CREATE TYPE o1 as (a int, b int);
MogDB=# DECLARE
    TYPE r1 is VARRAY(10) of o1;
    emp_id  r1;
BEGIN
    emp_id(1).a := 5;--赋值
    emp_id(1).b := 5*7784;
END;
/

须知:

• INTO 方式赋值仅支持对第一层列赋值，且不支持二维及以上数组。

INTO/BULK COLLECT INTO

• 将存储过程内语句返回的值存储到变量内，BULK COLLECT INTO允许将部分或全部返回值暂存到数组内部。
• 支持返回空结果集。

语法格式

SELECT select_expressions INTO [STRICT] target FROM ...
SELECT INTO [STRICT] target expression [FROM ..]

说明：

• 通过基础 SQL 命令加INTO子句可以将单行或多列的结果赋值给一个变量（记录、行类型、标量变量列表）。
• target参数可以是一个记录变量、一个行变量或一个有逗号分隔的简单变量和记录/行域列表。
• STRICT选项 在开启参数set behavior_compat_options = 'select_into_return_null'的前提下（默认未开启），若指定该选项则该查询必须刚好返回一行不为空的结果集，否则会报错，报错信息可能是NO_DATA_FOUND（没有行）、TOO_MANY_ROWS（多于一行）或QUERY_RETURNED_NO_ROWS (没有数据返回)。若不指定该选项则没有该限定，且支持返回空结果集。

示例：

MogDB=# DECLARE
    my_id integer;
BEGIN
    select id into my_id from customers limit 1; -- 赋值
END;
/

MogDB=# DECLARE
    type id_list is varray(6) of customers.id%type;
    id_arr id_list;
BEGIN
    select id bulk collect into id_arr from customers order by id DESC limit 20; -- 批量赋值
END;
/

MogDB=# CREATE TABLE test(a integer);
MogDB=# CREATE OR REPLACE FUNCTION check_test() RETURNS integer
    language plpgsql
    AS $function$
    DECLARE
        b integer;
    BEGIN
        SELECT INTO b a FROM test WHERE a=1; -- 返回空结果集
            RETURN b;
    END;
    $function$;
MogDB=# SELECT check_test();
 check_test
------------

(1 row)

须知:

• BULK COLLECT INTO 只支持批量赋值给数组或集合。集合类型合理使用LIMIT字段避免操作过量数据导致性能下降。
• INTO/BULK COLLECT INTO 只支持4层以下Record类型直接嵌套。
• 返回空结果集需要数据库初始化使用PG兼容参数，配置GUC参数**set behavior_compat_options = 'select_into_return_null'为开启。配置GUC参数set behavior_compat_options = ''**则关闭。
• 对于数组变量，小括号"()“将优先识别为下标，因此对于带括号的表达式，不支持写在数组变量后面。如对于select (1+3) into va(5)，不支持写为select into va(5) (1+3)或select into va[5] (1+3)。
• INSERT INTO、UPDATE INTO、DELETE INTO、EXECUTION INTO不支持返回空结果集。
• 给多个变量赋值时，由于后面的变量存在语法错误，所以均不赋值。
• 不支持分布式。

调用语句

语法

调用一个语句的语法请参见图4。

图 4 call_clause::=

对以上语法格式的解释如下：

• procedure_name：存储过程名。
• parameter：存储过程的参数，可以没有或者有多个参数。

示例

--创建存储过程proc_staffs
MogDB=# CREATE OR REPLACE PROCEDURE proc_staffs
(
section     NUMBER(6),
salary_sum out NUMBER(8,2),
staffs_count out INTEGER
)
IS
BEGIN
SELECT sum(salary), count(*) INTO salary_sum, staffs_count FROM hr.staffs where section_id = section;
END;
/

--调用存储过程proc_return.
MogDB=# CALL proc_staffs(2,8,6);

--清除存储过程
MogDB=# DROP PROCEDURE proc_staffs;