Oracle12C--子程序(三十九)

知识点的梳理：

• 子程序分为："过程"和"函数"两类，虽然统称子程序，但实际上两者有很大不同；
  • "过程"也可以被称为"存储过程"，其与PL/SQL的关系：过程（存储过程）=过程的声明 + PL/SQL块
  • 两者区别：
    • 函数可以有返回值；
    • 过程只能依靠OUT或IN OUT返回数据
• 调用过程的语法为：exec 过程名称
• 子程序就是将定义的PL/SQL程序块在过程或函数中进行统一的管理；
• 查询子程序的详细定义，可以使用user_source数据字典；
• 子程序有3中参数模式：IN(默认),IN OUT,OUT;
• 使用"PRAGMA AUTONOMOUS_TRANSACTION;"可以启动一个子事务，子事务的操作与主事务无关；

    

• 子程序定义
  • Oracle中，将重复代码块封装到一个结构体中，该结构体被称为子程序；
    • 子程序中的代码块为Oracle对象，其对象信息保存在对应的数据字典中；
  • 创建子程序所需的权限，下表不包含触发器权限：

create any procedur	为任意用户创建存储过程的权限
create procedure	为用户创建存储过程的权限
alter procedure	修改拥有的存储过程权限
execute any procedure	执行任意存储过程的权限
execute function	执行存储函数的权限
execute procedure	执行用户存储过程的权限
drop any procedure	删除任意存储过程的权限

• 定义过程
  • 定义："过程"是指大型数据库中专门定义的一组SQL语句集，它可以定义用户操作参数，且存在于数据库中，使用时直接调用即可。
  • 语法：定义过程

CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称 [参数模式] NOCOPY 数据类型 [,参数名称 [参数模式] NOCOPY 数据类型 ,....]]) [AUTHID [DEFINER | CURRENT_USER]] AS | IS [PRAGMA AUTONOMOUS_TRANSACTION;] 声明部分; BEGIN 程序部门; EXCEPTION 异常处理; END; /

本语法中的部分解释如下： 1.参数中定义的参数模式表示过程的数据接收操作，一般分为IN,OUT,IN OUT 3 类； 2.CREATE [OR REPLACE]：表示创建或替换过程，如果此过程存在则替换，如果不存在则创建一个新的； 3.AUTHID子句：定义了一个过程的所有者权限，DEFINER（默认）表示定义者权限执行，或者使用CURRENT_USER覆盖程序的默认行为，变为使用者权限执行； 4.PRAGMA AUTONOMOUS_TRANSACTION：表示由过程启动一个自治事务，自治事务可以让主事务挂起，在过程中执行完SQL后，由用户处理提交或回滚自治事务，然后再恢复主事务；

• 示例1：定义一个简单的过程

CREATE OR REPLACE PROCEDURE mldn_proc AS BEGIN DBMS_OUTPUT.put_line('www.java.cn') ; END; /	调用过程：EXEC mldn_proc 执行结果： www.java.cn
方式2：将"AS"替换为"IS" CREATE OR REPLACE PROCEDURE mldn_proc IS BEGIN DBMS_OUTPUT.put_line('www.java.cn') ; END; /	执行结果相同

• 示例2：定义一个带参数的过程，根据雇员编号（参数）找到雇员姓名及工资

CREATE OR REPLACE PROCEDURE get_emp_info_proc(p_eno emp.empno%TYPE) AS v_ename emp.ename%TYPE ; v_sal emp.sal%TYPE ; v_count NUMBER ; BEGIN SELECT COUNT(empno) INTO v_count FROM emp WHERE empno=p_eno ; IF v_count = 0 THEN -- 没有发现数据 RETURN ; -- 结束过程调用 END IF ; SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=p_eno ; DBMS_OUTPUT.put_line('编号为' || p_eno || '的雇员姓名：' || v_ename || '，工资：' || v_sal) ; END; /

调用过程：exec get_emp_info_proc(7369) 执行结果： 编号为7369的雇员姓名：SMITH，工资：10800 分析： 建立过程时定义了一个接收参数（p_eno），则用户在调用此过程时就需要传入一个与之类型相符的数值。在过程之中，会首先使用COUNT()函数来判断是否存在指定的雇员信息，如果不存在则统计结果为0，可直接使用RETuRN结束方法的调用

• 示例3：将增加数据的操作定义为过程

CREATE OR REPLACE PROCEDURE dept_insert_proc( p_dno dept.deptno%TYPE, p_dna dept.dname%TYPE, p_dlo dept.loc%TYPE) AS v_deptCount NUMBER ; -- 保存COUNT()函数结果 BEGIN SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=p_dno ; -- 统计 IF v_deptCount > 0 THEN -- 有此编号的部门 RAISE_APPLICATION_ERROR(-20789,'增加失败：该部门已存在！') ; ELSE INSERT INTO dept(deptno,dname,loc) VALUES (p_dno,p_dna,p_dlo) ; DBMS_OUTPUT.put_line('新部门增加成功！') ; COMMIT ; END IF ; EXCEPTION WHEN others THEN DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ; ROLLBACK ; -- 事务回滚 END ; /

调用过程： 部门编号重复：exec dept_insert_proc(10,'MLDN','北京') 运行结果：SQLERRM = ORA-20789: 增加失败：该部门已存在！   部门编号不重复：exec dept_insert_proc(15,'微软','北京') 运行结果：新部门增加成功！

• 定义函数
  • 用户定义的函数，被SQL语句或PL/SQL直接调用；
  • 语法：定义函数

CREATE [OR REPLACE] FUNCTION 函数名([参数 ,[参数,.....]]) RETURN 返回值类型 [AUTHID {DEFINER | CURRENT_USER}] AS | IS [PRAGMA AUTONOMOUS_TRANSACTION;] 声明部分; BEGIN 程序部门; [RETURN 返回值;] [EXCEPTION 异常处理] END [函数名]; /

• 示例1：定义函数，通过雇员编号查找此雇员的月薪

CREATE OR REPLACE FUNCTION get_salary_fun(p_eno emp.empno%TYPE) RETURN NUMBER AS v_salary emp.sal%TYPE ; BEGIN SELECT sal + nvl(comm,0) INTO v_salary FROM emp WHERE empno=p_eno ; RETURN v_salary ; END; /

分析： 该函数会根据雇员的编号查询出每位雇员的月薪， 同时将查询出来的月薪通过RETURN返回给调用处

• 示例2：通过PL/SQL块验证函数

DECLARE v_salary NUMBER ; BEGIN v_salary := get_salary_fun(7369) ; DBMS_OUTPUT.put_line('雇员7369的工资为：' || v_salary) ; END ; / 运行结果： 雇员7369的工资为：800

此示例使用了PL/SQL进行函数调用，用户也可以定义一个过程，通过过程来调用函数，示例代码：

扩展示例：定义过程调用函数 CREATE OR REPLACE PROCEDURE invoke_proc AS v_salary NUMBER ; BEGIN v_salary := get_salary_fun(7369) ; DBMS_OUTPUT.put_line('雇员7369的工资为：' || v_salary) ; END ; / 执行过程：exec invoke_proc; 运行结果与上例相同

• 示例3：除了PL/SQL调用外，还可以像单行函数一样，直接利用SQL调用

SELECT get_salary_fun(7369) FROM dual ;

• 示例4：可以使用CALL，将函数的返回值设置给变量

VAR v_salary NUMBER ; CALL get_salary_fun(7369) INTO : v_salary ; PRINT v_salary ;

运行结果：800

• 如何选择过程与函数?
  • 根据实际需求，从两个方式区分它们：
    • 过程处理返回值时不如函数方便，过程只能依靠OUT或IN OUT参数模式传回数据；
    • 编程语句，例如java，调用过程要比函数更加实用；