08-过程&函数和程序包

Oracle 10g 数据库管理、应用与开发
16
（1）函数的创建
例如，创建一个以部门号为参数，返回该部门最高工 资的函数。
CREATE OR REPLACE FUNCTION return_maxsal (p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_maxsal emp.sal%TYPE; BEGIN SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno; RETURN v_maxsal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('The deptno is invalid!'); END return_maxsal;
CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE) AS BEGIN SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('The department don’’t exists!'); END return_deptinfo;
WHERE type='PROCEDURE';
重新编译存储过程
ALTER PROCEDURE…COMPILE ALTER PROCEDURE show_emp COMPILE;
删除存储过程
DROP PROCEDURE DROP PROCEDURE show_emp;
Oracle 10g 数据库管理、应用与开发
Oracle10g管理、应用与开发
第8章 过程、函数和程序包
软件学院 刘杨涛
Email:liuytny@126.cFra Baidu bibliotekm
南阳理工学院软件学院.数据库技术教研室
2012年9月
大型数据库技术
过程、函数和包序
存储过程 函数
局部子程序
程序包
依赖性
Oracle 10g 数据库管理、应用与开发
2
存储子程序
Oracle 10g 数据库管理、应用与开发
15
（1）函数的创建
注意
在函数定义的头部，参数列表之后，必须包含一个RETURN语句来指 明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如 果使用%TYPE，则可以隐含地包括长度、精度、刻度等约束信息； 在函数体的定义中，必须至少包含一个RETURN 语句，来指明函数返 回值。也可以有多个RETURN语句，但最终只有一个RETURN语句被 执行。
Oracle 10g 数据库管理、应用与开发
10
（2）存储过程的调用
在SQL*PLUS中调用
EXEC procedure_name(parameter_list)
EXECUTE show_emp(10)
在PL/SQL块中调用
BEGIN procedure_name(parameter_list);
Oracle 10g 数据库管理、应用与开发
3
1. 存储过程
存储过程的创建 存储过程的调用 存储过程的管理 局部变量和子过程
Oracle 10g 数据库管理、应用与开发
4
（1）存储过程的创建
基本语法
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) AS|IS /*Declarative section is here */ --变量声明 BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END[procedure_name];
存储子程序是被命名的PL/SQL块，以编译的形式存储在 数据库服务器中，可以在应用程序中进行调用，是PL/SQL程 序模块化的一种体现。 PL/SQL中的存储子程序包括存储过程和(存储)函数两种。 存储子程序是以独立对象的形式存储在数据库服务器中，因 此是一种全局结构，与之对应的是局部子程序，即嵌套在 PL/SQL块中的局部过程和函数，其存储位置取决于其所在的 父块的位置。
Oracle 10g 数据库管理、应用与开发
12
（3）存储过程的管理
修改存储过程
CREATE OR REPLACE PROCEDURE procedure_name
查看存储过程及其源代码
查询数据字典视图USER_SOURCE
SELECT name,text FROM user_source
13
2. 函数
函数的创建 函数的调用
函数的管理
Oracle 10g 数据库管理、应用与开发
14
（1）函数的创建
基本语法为
CREATE [OR REPLACE] FUNCTION function_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) RETURN return_datatype AS|IS /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END [function_name];
Oracle 10g 数据库管理、应用与开发
17
（1）函数的创建
如果需要函数返回多个值，可以使用OUT或IN OUT模式参数。 例如，创建一个函数，以部门号为参数，返回部门名、部门 人数及部门平均工资。
CREATE OR REPLACE FUNCTION ret_deptinfo( p_deptno dept.deptno%TYPE, p_num OUT NUMBER, p_avg OUT NUMBER) RETURN dept.dname%TYPE AS v_dname dept.dname%TYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname; END ret_deptinfo;
Oracle 10g 数据库管理、应用与开发
19
（2）函数的调用
DECLARE v_maxsal emp.sal%TYPE; v_avgsal emp.sal%TYPE; v_num NUMBER; v_dname dept.dname%TYPE; BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_maxsal:=return_maxsal(v_dept.deptno); v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal); DBMS_OUTPUT.PUT_LINE(v_dname||' '||v_maxsal||' '|| v_avgsal||' '||v_num); END LOOP; END;
参数默认值
可以为参数设置默认值，这样存储过程被调用时如果没有给该参数传递值， 则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。
Oracle 10g 数据库管理、应用与开发
7
（1）存储过程的创建
例如，创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输 出该部门中比平均工资高的员工号、员工名。
Oracle 10g 数据库管理、应用与开发
8
（1）存储过程的创建
通常，存储过程不需要返回值，如果需要返回一个值可以通 过函数调用实现。但是，如果希望返回多个值，可以使用OUT 或IN OUT模式参数来实现。
Oracle 10g 数据库管理、应用与开发
9
（1）存储过程的创建
例如，创建一个存储过程，以部门号为参数，返回该部门的人 数和平均工资。
END；
注意
在PL/SQL程序中，存储过程可以作为一个独立的表达式被调用。
Oracle 10g 数据库管理、应用与开发
11
（2）存储过程的调用
DECLARE v_avgsal emp.sal%TYPE; v_count NUMBER; BEGIN show_emp(20); return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE(v_avgsal||' '|| v_count); END;
CREATE OR REPLACE PROCEDURE show_emp( p_deptno emp.deptno%TYPE) AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary is:' ||v_sal); FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND sal>v_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('The department doesn’’t exists!'); END show_emp;
Oracle 10g 数据库管理、应用与开发
18
（2）函数的调用
在SQL语句中调用函数 在PL/SQL中调用函数 注意
函数只能作为表达式的一部分被调用。
示例
通过return_maxsal函数的调用，输出各个部门的最高 工资；通过ret_deptinfo函数调用，输出各个部门名、 部门人数及平均工资。
Oracle 10g 数据库管理、应用与开发
5
（1）存储过程的创建
参数说明
参数的模式(mode)
IN(默认参数模式)表示当过程被调用时，实参被传递给形参；在过 程内，形参起常量作用，只能读该参数，而不能修改该参数；当子 程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以 是常量或表达式。 OUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初 始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作； 当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模 式参数只能是变量，不能是常量或表达式。 IN OUT表示当过程被调用时，实参值被传递给形参；在过程内， 形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用 结束返回调用环境时，形参值被赋给实参。IN OUT模式参数只能 是变量，不能是常量或表达式。
Oracle 10g 数据库管理、应用与开发
6
（1）存储过程的创建
参数的限制
在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递 机制的一部分被传递的，是由实参决定的。
参数传递方式
当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN 参数为引用传递，即实参的指针被传递给形参；OUT，IN OUT参数为值 传递，即实参的值被复制给形参。