Oracle12C--子程序的嵌套(四十二)

知识点的梳理：

• 通过CREATE OR REPLACE定义的过程会作为一个对象保存在数据字典中，并且此程序代码也可以保存在共享池中，以便重用；
  • 定义的嵌套过程会作为一个过程的一部分出现，只能从包含它的程序块中被调用，且嵌套过程不能被保存到共享池；

    

• 一个子程序内部还可以定义其他子程序
  • 示例1：定义嵌套过程

CREATE OR REPLACE PROCEDURE dept_insert_proc(

p_dno dept.deptno%TYPE,

p_dna dept.dname%TYPE,

p_dlo dept.loc%TYPE ,

p_result OUT NUMBER)

AS

v_deptCount NUMBER ; --保存COUNT()函数结果

PROCEDURE get_dept_count_proc( --定义嵌套过程，判断部门编号是否存在

p_temp dept.deptno%TYPE ,

p_count OUT NUMBER) --返回统计结果

AS

BEGIN

SELECT COUNT(deptno) INTO p_count FROM dept WHERE deptno=p_temp ; --统计

END ;

PROCEDURE insert_operate_proc( --定义嵌套过程，执行增加

p_temp_dno dept.deptno%TYPE,

p_temp_dna dept.dname%TYPE,

p_temp_dlo dept.loc%TYPE ,

p_count NUMBER ,

p_flag OUT NUMBER) --通过此参数返回结果

AS

BEGIN

IF p_count > 0 THEN --有此编号的部门

p_flag := -1 ;

ELSE

INSERT INTO dept(deptno,dname,loc) VALUES (p_temp_dno , p__tempdna , p_tempdlo) ;

p_flag := 0 ; --修改返回标记

COMMIT ;

END IF ;

END ;

BEGIN

get_dept_count_proc(p_dno , v_deptCount) ; --判断是否有此部门

insert_operate_proc(p_dno,p_dna,p_dlo,v_deptCount,p_result) ;

END ;

/

分析：
本程序在声明部分一共定义了两个嵌套过程：
get_dept_count_proc过程：
根据传入的部门编号，统计出此部门信息数量，且通过OUT参数返回统计结果
insert_operate_proc：传入部门编号，名称，位置，统计结果，返回标记（OUT模式），
首先会判断统计结果（p_count）是否为0，如果为0表示部门不存在，则执行增加操作，同时将返回标记（p_flag）设置为0，表示增加成功，否则返回标记设置为1；

• 示例2：简化定义

CREATE OR REPLACE PROCEDURE dept_insert_proc(

p_dno dept.deptno%TYPE ,

p_dna dept.dname%TYPE ,

p_dlo dept.loc%TYPE ,

p_result OUT NUMBER)

AS

v_deptCount NUMBER ; --保存COUNT()函数结果

PROCEDURE get_dept_count_proc --返回统计结果

AS

BEGIN

SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=p_dno ; --统计

END ;

PROCEDURE insert_operate_proc

AS

BEGIN

IF v_deptCount > 0 THEN

p_result := -1 ;

ELSE

INSERT INTO dept(deptno,dname,loc) VALUES (p_dno,p_dna,p_dlo) ;

p_result := 0 ;

COMMIT ;

END IF ;

END ;

BEGIN

get_dept_count_proc() ; --查询个数

insert_operate_proc() ;

END ;

/

分析：
此程序的内部子程序没有编写任何的参数传递，直接使用了外部子程序中的参数与变量。

• 过程的相互调用
  • 示例1：定义错误的子程序调用

DECLARE

PROCEDURE a_proc(p_paramA NUMBER)

AS

BEGIN

DBMS_OUTPUT.put_line('A过程，p_paramA = ' || p_paramA) ;

b_proc('www.mldnjava.cn') ;

END ;

PROCEDURE b_proc(p_paramB VARCHAR2)

AS

BEGIN

DBMS_OUTPUT.put_line('B过程，p_paramB = ' || p_paramB) ;

a_proc(100) ;

END ;

BEGIN

NULL ;

END ;

/

运行结果：

错误报告:

ORA-06550: 第 6 行, 第 3 列:

PLS-00313: 在此作用域中没有声明 'B_PROC'

ORA-06550: 第 6 行, 第 3 列:

PL/SQL: Statement ignored
分析：
过程"B_PROC"并没有被定义，所以出现异常

• 示例2：可以利用前导声明（预声明）的方式来解决此问题。前导声明是指进行过程的定义，但不包含程序的结构体

DECLARE

PROCEDURE b_proc(p_paramB VARCHAR2) ; --前导声明

PROCEDURE a_proc(p_paramA NUMBER)

AS

BEGIN

DBMS_OUTPUT.put_line('A过程，p_paramA = ' || p_paramA) ;

b_proc('www.mldnjava.cn') ;

END ;

PROCEDURE b_proc(p_paramB VARCHAR2)

AS

BEGIN

DBMS_OUTPUT.put_line('B过程，p_paramB = ' || p_paramB) ;

a_proc(100) ;

END ;

BEGIN

NULL ;

END ;

/

• 示例3：子程序支持重载，只需要考虑参数的类型及个数不同即可

DECLARE

PROCEDURE get_dept_info_proc(p_deptno dept.deptno%TYPE) AS

BEGIN

DBMS_OUTPUT.put_line('部门编号：' || p_deptno) ;

END ;

PROCEDURE get_dept_info_proc(p_dname dept.dname%TYPE) AS

BEGIN

DBMS_OUTPUT.put_line('部门名称：' || p_dname) ;

END ;

BEGIN

get_dept_info_proc(30) ;

get_dept_info_proc('微软') ;

END ;

/

运行结果：

部门编号：30

部门名称：微软

• 函数的定义
  • 函数除了可以返回定义的数据类型外，也可以返回集合数据；
  • 示例1：定义函数，返回嵌套表类型数据

DECLARE

TYPE emp_nested IS TABLE OF emp%ROWTYPE ;

v_emp_return emp_nested ;

FUNCTION dept_emp_fun(p_dno emp.deptno%TYPE) RETURN emp_nested

AS

v_emp_temp emp_nested ;

BEGIN

SELECT * BULK COLLECT INTO v_emp_temp FROM emp WHERE deptno=p_dno ;

RETURN v_emp_temp ;

END ;

BEGIN

BEGIN

v_emp_return := dept_emp_fun(10) ;

FOR x IN v_emp_return.FIRST .. v_emp_return.LAST LOOP

DBMS_OUTPUT.put_line('雇员编号：' || v_emp_return(x).empno || '，姓名：' || v_emp_return(x).ename || '，职位：' || v_emp_return(x).job) ;

END LOOP ;

EXCEPTION

WHEN others THEN

DBMS_OUTPUT.put_line('此部门没有雇员！') ;

END ;

END ;

/

运行结果：
此部门没有雇员！
分析：
本程序首先定义了一个嵌套表类型（emp_nested），而在定义函数（dept_emp_fun()）时，由于此函数要返回多行的雇员信息，所以直接使用了自定义的嵌套表类型，当查询出数据之后会将此嵌套表变量返回给调用处，之后进行全部数据的输出

• 函数的递归操作
  • 使用递归的两个前提：
    • 需要提供函数递归调用结束的操作，如果没有此类操作，可能会出现内存溢出的问题；
    • 每次函数进行递归调用时，都需要修改传递的参数值；
  • 示例1：实现函数递归调用

DECLARE

v_sum NUMBER ;

FUNCTION add_fun(p_num NUMBER)

RETURN NUMBER

AS

BEGIN

IF p_num = 1 THEN

RETURN 1 ;

ELSE

RETURN p_num + add_fun(p_num - 1) ;

END IF ;

END ;

BEGIN

v_sum := add_fun(100) ; --进行1~100累加

DBMS_OUTPUT.put_line('累加结果：' || v_sum) ;

END ;

/

运行结果：5050