子程序

功能描述

PL/pgSQL子程序是能够接受参数并被其它程序所调用的命名PL/pgSQL块。PL/pgSQL子程序有两种类型，过程和函数。通常来说，过程用于执行一个操作，而函数用于计算一个结果值。

与匿名块一样，子程序也有声明部分（可选），执行部分和异常处理部分（可选）。

声明部分（可选）：声明和定义局部类型、游标、常量、变量、异常和嵌套子程序。当子程序完成执行时，这些项目将不复存在。声明的局部类型、变量或子程序等的作用域，仅限于当前对象，或者当前对象嵌套的子程序。
执行部分（必填）：包含一个或多个用于赋值、控制执行和操作数据的语句。这部分可以包含对嵌套子程序的调用，其中函数类型嵌套子程序被调用，返回值可以参与表达式运算。
异常处理部分（可选）：包含处理运行时错误的代码。异常处理代码可以是子程序的异常处理,同样嵌套子程序也可以包含异常处理部分。

子程序的特点

子程序支持可靠的开发和维护，具有以下功能的可重用代码：

模块化：子程序使用戶可以将程序分解为可管理的、定义明确的模块。
更简单的应用设计：在设计应用程序时，用户可以推迟子程序的实现细节，直到用户测试了主程序，然后逐步细化。例如子程序创建期间以NULL语句作为占位符，可以在之后来完善它。
可维护性：用户可以更改子程序的实现细节而不更改其调用程序。
可包装性：子程序可以分组到包中。
可重用性：在许多不同的环境中，任何数量的应用程序都可以使用相同的包子程序或独立子程序。
更好的性能：每个子程序都以可执行的形式编译和存储，可以重复调用。因为存储的子程序在数据库服务器中运行，所以通过网络进行的一次调用就可以启动一项大型作业。这种工作分工减少了网络流量并缩短了响应时间。存储的子程序被缓存并在用户之间共享，从而降低了内存需求和调用开销。
嵌套子程序：在PL/SQL 块内创建的子程序是嵌套子程序，用户可以同时声明和定义。

最多支持两次嵌套，层数不超过3层。

注意事项

本功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
针对函数类型的子程序，还应该包括如下特性：
- 函数标题必须包含RETURN子句，该子句指定函数返回值的数据类型（过程标题不能有RETURN子句）。
- 在函数的可执行部分，每条可执行路径都必须包含一个RETURN语句。否则SQL编译器会发出编译时警告（在过程中，RETURN语句是可选的，不推荐使用）。
- 只有功能标题可以包含DETERMINISTIC选项，表示确定性选项，帮助优化器避免冗余函数调用。
嵌套子程序支持使用游标，row等自定义复合类型。
嵌套层数不能超过两层。
嵌套子程序中不支持自治事务。
内外层程序可以同名，但子程序的参数不可与外层程序参数同名。

语法格式

子程序的创建和调用

DECLARE
subprogram_name [ (parm [,parm...])]
subprogram_body
BEGIN
...
END [ func_name | proc_name ];
/

subprogram中定义嵌套对象语法如下：

函数

函数名称可以相同，同名函数可以拥有不同的参数个数/参数类型/返回值，语法如下：

FUNCTION function_name
( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }
[, ...]  ] )
RETURN rettype [ DETERMINISTIC | PIPELINED ]
[
    {IMMUTABLE  | STABLE  | VOLATILE }
    | {SHIPPABLE | NOT SHIPPABLE}
    | {PACKAGE}
    | [ NOT  ] LEAKPROOF
    | {CALLED ON NULL INPUT  | RETURNS NULL ON NULL INPUT  | STRICT }
    | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER | |
AUTHID DEFINER  | AUTHID CURRENT_USER}
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { {TO | =} value | FROM CURRENT }
][...]
{
    IS | AS
} 
plsql_body
/

存储过程

针对存储过程，同名存储拥有不同的参数个数或者参数类型，语法如下：

PROCEDURE procedure_name
[ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
{ IS | AS } 
plsql_body
/

其中plsql_body语法与普通的schema下的subprogram的plsql_body语法一致，并支持异常处理。

参数说明

subprogram_name

子程序标题名称。

子程序以子程序标题开始，该标题指定其名称（必选）和其参数列表（可选）。
parm [,parm…]

子程序参数列表。
subprogram

子程序部分。

子程序部分是子程序的内容，其包含如下几部分：
- 声明部分（可选）
  
  这部分声明和定义局部类型、游标、常量、变量、异常和嵌套子程序。当子程序完成执行时，这些项目将不复存在。声明的局部类型、变量或子程序等的作用域，仅限于当前对象，或者当前对象嵌套的subprogram。
- 可执行部分（必填）
  
  这部分包含一个或多个用于赋值、控制执行和操作数据的语句。这部分可以包含对嵌套子程序的调用，其中函数类型嵌套子程序被调用，返回值可以参与表达式运算。
- 异常处理部分（可选）
  
  这部分包含处理运行时错误的代码。异常处理代码可以是子程序的异常处理，同样嵌套子程序也可以包含异常处理部分。
END [ func_name | proc_name ]

嵌套的外层程序支持以END + 程序名结尾。

func_name / proc_name：嵌套外层的函数 / 存储过程名称。（可选）
嵌套对象函数参数说明详见CREATE FUNCTION。
嵌套对象存储过程参数说明详见CREATE PROCEDURE。

示例

示例1： 嵌套函数结合表使用。

1、创建测试表employees_subpro。

CREATE TABLE employees_subpro 
( employee_id number(6,0), 
first_name varchar2(20), 
last_name varchar2(25), 
email varchar2(25), 
phone_number varchar2(20), 
hire_date date , 
job_id varchar2(10) , 
salary number(8,2), 
commission_pct number(2,2), 
manager_id number(6,0), 
department_id number(4,0)
);

2、向表中插入数据。

INSERT INTO employees_subpro VALUES ('100', 'Steven', 'King', 'SKING', '515.123.4567', date'1987-06-17', 'AD_PRES', '24000.00', null, null, '90'); 
INSERT INTO employees_subpro VALUES ('200', 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', date'1987-09-17', 'AD_ASST', '4400.00', null, '101', '10'); 
INSERT INTO employees_subpro VALUES ('101', 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', date'1989-09-21', 'AD_VP', '17000.00', null, '100', '90'); 
INSERT INTO employees_subpro VALUES ('102', 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', date'1993-01-13', 'AD_VP', '17000.00', null, '100', '90'); 
INSERT INTO employees_subpro VALUES ('103', 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', date'1990-01-03', 'IT_PROG', '9000.00', null, '102', '60'); 
INSERT INTO employees_subpro VALUES ('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', date'1991-05-21', 'IT_PROG', '6000.00', null, '103', '60'); 
INSERT INTO employees_subpro VALUES ('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', date'1997-06-25', 'IT_PROG', '4800.00', null, '103', '60');

3、创建带有声明和定义一起的函数的子程序嵌套。

--定义函数subprogram_f_1的，接受一个int类型的参数id3，并返回一个varchar2类型的结果
CREATE OR REPLACE FUNCTION  subprogram_f_1(id3 int) return varchar2  
as 
    --在函数内部定义变量
    id5 int; 
    id4 varchar2(20); 

    --定义一个游标cur，查询employees_subpro表中department_id等于id3的所有employee_id，并按升序排列
    CURSOR cur IS select employee_id from employees_subpro where department_id= id3 order by 1; 
    --定义了一个名为p的子函数
    function p ( id1 int) return varchar2  
    IS 
    id2 varchar2(20); 
    BEGIN  
        --子函数内部，通过查询employees_subpro表，将employee_id等于id1的first_name存储到id2变量中，并返回id2
        SELECT first_name INTO id2 FROM employees_subpro where employee_id = id1; 
        return id2; 
    END; 
--函数体
BEGIN 
    open cur; 
    LOOP 
    FETCH cur INTO id5; 
    EXIT WHEN cur%NOTFOUND; 
    id4 := p(id5); 
    END LOOP; 
    CLOSE cur; 
    return id4; 
END; 
/

4、调用函数查询表内容。

SELECT subprogram_f_1(department_id) FROM employees_subpro ORDER BY 1;

返回结果为：

 subprogram_f_1
----------------
 David
 David
 David
 Jennifer
 Lex
 Lex
 Lex
(7 rows)

5、清理环境。

DROP TABLE employees_subpro;
DROP FUNCTION subprogram_f_1;

示例2： 创建两层嵌套的函数并调用（内外层程序不同名）。

1、创建嵌套函数，外层函数结束时采用END function_name形式。

CREATE OR REPLACE FUNCTION func_1200204(p_input1 NUMBER) RETURN NUMBER IS
v_result1 NUMBER;
FUNCTION inner_function(p_input2 NUMBER) RETURN NUMBER IS
v_result2 NUMBER;
BEGIN
v_result2 := p_input2 * 2;
RETURN v_result2;
END;
BEGIN
v_result1 := inner_function(p_input1) + 5;
RETURN v_result1;
END func_1200204;
/

2、调用外层函数。

select func_1200204(10) from dual;

调用成功，返回结果如下：

 func_1200204
--------------
           25
(1 row)

3、清理环境。

drop function func_1200204;

示例3： 创建两层嵌套的存储过程并调用（内外层程序同名不同参）。

1、创建测试表并插入测试数据。

create table table_1200203
( employee_id number(6,0),
first_name varchar2(20),
last_name varchar2(25),
email varchar2(25));
INSERT INTO table_1200203 VALUES ('150', 'Steven', 'King', 'SKING');
INSERT INTO table_1200203 VALUES ('125', 'Jennifer', 'Whalen', 'JWHALEN');

2、创建内外层程序同名不同参的存储过程并调用。

create procedure proc_1200203(id int,na1 varchar2,na2 varchar2)
as
ema varchar2(36);
emp_id int;
procedure proc_1200203(id1 int,na11 varchar2,na12 varchar2)
is
begin
ema:=na11 || na12 || '@163.com';
emp_id:=id1+150;
end;
begin
proc_1200203(id,na1,na2);
insert into table_1200203 values(emp_id,na1,na2,ema);
update table_1200203 set first_name='wang';
delete from table_1200203 where employee_id=id;
end proc_1200203;
/
call proc_1200203(70,'zhang','yx');

3、验证存储过程执行效果。

SELECT * FROM table_1200203 order by employee_id;

返回结果为：

 employee_id | first_name | last_name |      email
-------------+------------+-----------+-----------------
         125 | wang       | Whalen    | JWHALEN
         150 | wang       | King      | SKING
         220 | wang       | yx        | zhangyx@163.com
(3 rows)

4、清理环境。

DROP PROCEDURE proc_1200203;
DROP TABLE table_1200203;

VastbaseG100

子程序

功能描述

注意事项

语法格式

参数说明

示例

弹框头部