人大金仓数据库KingbaseES PLSQL子程序介绍

关键字：

KingbaseES、子程序、过程、函数

1.为什么要使用子程序？

在PL/SQL中，子程序是一组可以重复调用的命名代码块，它可以接收参数并执行特定的任务。实际编写过程中，用户可以根据自己的需求来设计子程序，而在使用子程序时只需要知道它的功能，无需知道其实现细节。子程序主要包括过程和函数，其中，过程用于执行某些操作，而函数执行操作并返回结果值。一旦子程序编译成功， 便可以在不同的环境中调用。子程序具有模块化、可重用性、可维护性和可包装性等优点，它可以将一个程序分解为多个小的、相对独立的模块，从而使代码更加便于管理和维护。通过使用子程序，将相关功能的代码封装在一起，不仅可以降低代码的复杂性，并且当需要对某个功能进行修改时，只需要修改特定的子程序，而不是需要更改其调用程序和其他代码。此外，子程序还是其他可维护性功能的重要组成部分（例如包、抽象数据类型等）。

2.子程序的构成

1.子程序的构成：子程序的构成与匿名块一样，包含（可选的）声明部分、可执行部分以及（可选的）异常处理部分：

1)声明部分：包含对类型、游标、常量、变量、异常和嵌套子程序进行声明和定义，但这些内容都是局部的，因此当程序执行完成时，这些内容会自动销毁。

2)可执行部分：包含一个或多个赋值语句、控制执行语句和数据操作语句。如果该子程序接收参数，将在子程序的执行部分，通过名称引用形式参数。

3)异常处理部分：包含处理运行时错误的代码，处理在执行部分可能出现的异常情况。

其中，过程的简化语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[（parameter [IN | OUT | IN OUT type [, … ] ]）]
{ IS | AS }
<variable_definition>   --如果没有变量需定义，该部分可以省略
BEGIN
<procedure_body>
END [ procedure_name ]; 
函数与过程具有相同的结构，但函数的头部必须包含有RETURN语句，该子句用于指定函数返回值的数据类型，函数的简化语法如下：
CREATE [OR REPLACE] PROCEDURE function_name 
[（parameter [IN | OUT | IN OUT type [, … ] ]）]
RETURN return_type
{ IS | AS }
<variable_definition>   --如果没有变量需定义，该部分可以省略
BEGIN
<procedure_body>
END [ function _name ]; 
/

3.子程序参数

如果用户希望子程序具有参数，需要在子程序头中声明形式参数，并在每个形式参数中，指定参数的名称和数据类型，以及（可选）的参数模式和默认值。对于一个需要接受参数的子程序，调用时使用不同的参数可能返回不同的结果值。

如果形参的数据类型是受约束的子类型，则：

a.子类型具有NOT NULL约束，实际参数会继承子类型；

b.子类型具有VARCHAR2基类型，实际参数不会继承子类型的大小；

c.子类型具有数字基类型，实际参数会继承子类型的范围、但不会继承其精度和小数位数。

其中，形式参数（简称形参）是指在定义子程序时，在定义语句中定义的参数；实际参数（简称实参）是指在调用子程序时，具体传入的参数。此外，形参的模式将决定它的行为，对子程序参数模式的特征总结如下：

参数模式	是否为默认值	功能
IN（输入参数）	是	将值传递给子程序
OUT（输出参数）	否，需要指定	向调用者返回一个值
IN OUT（输入/输出参数）	否，需要指定	是将初始值传递给子程序并将更新的值返回给调用者

注：尽量不要将OUT和IN OUT用于函数参数，在理想环境下，函数接受零个或多个参数并返回一个结果值，因此带有OUT和IN OUT参数的函数将会返回多个结果值，进而具有一定的副作用。

注意，在 KingbaseES V8 版本的默认情况下，函数的 OUT/INOUT 模式的参数不能指定默认值（否则在创建时会报错）。但是在需要使用的时候，可以通过参数 comp_v7_program_para_def 控制兼容 KingbaseES V7 版本函数 OUT/INOUT 模式的参数支持默认值。

4.子程序的调用

可以通过如下形式对子程序进行调用:

subprogram_name [ ( [ parameter [, parameter]... ] ) ]

其中，接受的参数个数是可以选择的，如果子程序没有参数或指定了默认值，则参数列表可以省略，或指定一个空的参数列表。

一个过程调用是一个PL/SQL语句，示例如下：

raise_salary(Stuloyee_id, amount);

一个函数调用是一个表达式，示例如下：

new_salary := get_salary(Stuloyee_id);
IF salary_ok(new_salary, new_title) THEN ...

值得注意的是，函数可以在语句中直接调用，而过程必须单独调用。

1.一个声明、定义和调用PL/SQL过程的简单示例如下：

DECLARE
str varchar(30) = '';
-- Declare and define procedure
PROCEDURE add_str (str1 text)
IS
error_message VARCHAR2(30) := 'str is too long.';
BEGIN
str := str || '_' || str1;
EXCEPTION
WHEN VALUE_ERROR THEN
RAISE NOTICE '%', error_message;
END add_str;
BEGIN
add_str('add_string1'); -- 第一次调用
RAISE NOTICE 'str is %', str;
add_str('add_string2'); -- 第二次调用
RAISE NOTICE 'str is %', str;
add_str('add_string3'); -- 第三次调用
END;
/

该示例中，一个匿名块同时声明和定义一个过程，然后调用该过程三次，在第三次调用时引发过程的异常处理部分。结果如下：

NOTICE: str is _add_string1
NOTICE: str is _add_string1_add_string2
ERROR: value too long for type character varying(
CONTEXT: PL/SQL function add_str line 12 at assig
SQL statement "CALL add_str('add_string3')"
PL/SQL function inline_code_block line 24 at CALL

2.一个声明、定义和调用PL/SQL过程的简单示例如下：

DECLARE
-- Declare and define function
FUNCTION square (num NUMBER) RETURN NUMBER
AS
num_squared NUMBER;
BEGIN
num_squared := num * num;
RETURN num_squared;
END;
BEGIN
RAISE NOTICE '%', square(90);
END;
/

该示例中，一个匿名块声明和定义了一个函数，并在执行部分调用它，结果如下：

NOTICE: 8100

3.子程序的递归调用

递归是一种解决问题、简化算法的强大技术。在递归过程中，子程序直接或间接的调用自身。递归子程序至少包含两条执行路径：1）通往递归调用的路径；2）通往终止条件的路径。一个实现n阶乘（n!）的递归函数定义如下：

CREATE OR REPLACE FUNCTION factorial (n INT) RETURN INT
AUTHID DEFINER
IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN n;
ELSE
RETURN n * factorial(n-1); -- 递归调用
END IF;
END;
/
BEGIN
FOR i IN 1..3 LOOP
RAISE NOTICE '%! = %',i ,factorial(i);
END LOOP;
END;
/
结果如下：
NOTICE: 1! = 1
NOTICE: 2! = 2
NOTICE: 3! = 6

需要注意的是，如果未定义终止条件，递归将一直进行，直到PL/SQL内存不足引发预定义异常。

5.总结

在PL/SQL中，子程序提供了一种将代码模块化、封装、复用的方法，进一步提高了代码的可维护性、可包装性、和可重用性。每个子程序都通过可执行的形式来编译和存储，存储的子程序被缓存并在用户之间共享，从而降低了内存需求和调用开销，提供了更好的性能，使得数据库应用程序开发更加高效。子程序可以是过程，也可以是函数，它们的主要区别在于：过程没有返回部分，仅执行特定的操作；而函数除了执行特定的操作还必须包含返回部分，用于返回一个结果值给调用者。此外，除了IN参数，过程还可接受OUT和INOUT参数，用于向调用者返回一个或多个值，但尽量不要将OUT和IN OUT参数用于函数，否则函数会返回多个值，具有一定的副作用。因此，如果需要返回需要返回一个参数值可以使用函数，而需要返回多个参数值最好使用过程。

更多信息，参见https://help.kingbase.com.cn/v8/index.html