知识点的梳理:
-
子程序分为:"过程"和"函数"两类,虽然统称子程序,但实际上两者有很大不同;
- "过程"也可以被称为"存储过程",其与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:定义一个简单的过程
CREATE OR REPLACE PROCEDURE mldn_proc AS BEGIN DBMS_OUTPUT.put_line('www.java.cn') ; END; / | 调用过程:EXEC mldn_proc |
方式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) |
- 示例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(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块验证函数
| ||
此示例使用了PL/SQL进行函数调用,用户也可以定义一个过程,通过过程来调用函数,示例代码: | ||
|
- 示例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,调用过程要比函数更加实用;
-