关键字:
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参数用于函数,否则函数会返回多个值,具有一定的副作用。因此,如果需要返回需要返回一个参数值可以使用函数,而需要返回多个参数值最好使用过程。