Oracle 数据库管理与应用：第8章 存储过程与函数的创建

1、第8章 存储过程与函数的创建本章要点存储过程的创建与执行1函数的创建与执行28.1 存储过程 存储过程是一个命名的程序块，包括过程的名称、过程使用的参数、过程执行的操作。 8.1.1 创建与调用存储过程 创建存储过程包括存储过程头部的声明和过程内操作的定义两部分。 CREATE OR REPLACE PROCEDURE procedure_name (argument1 IN | OUT | IN OUT data_type , argument2 IN | OUT | IN OUT data_type,)IS |AS declaration_section;BEGIN executable_

2、section;EXCEPTION exception_handlers;END procedure_name;注意与匿名块有三点区别：1.无DECLARE关键字2.在END后面可以加过程名 作为定义结束的标志3.存储过程定义完成后需要调用才能执行过程内部的代码。1. 无参数存储过程的创建与调用 例8.1 创建存储过程，输出系统的日期和时间CREATE OR REPLACE PROCEDURE display_time ISBEGIN dbms_output.put_line(systimestamp);END display_time;在SQL*Plus环境中调用存储过程有三种方法：使用EX

3、ECUTE(简写EXEC)命令调用。使用CALL命令调用。在匿名的程序块中直接以过程名调用。例8.2 使用三种方式调用上面创建的存储过程display_time 。方式一： SET SERVEROUTPUT ON EXECUTE display_time; 方式二： CALL display_time( );方式三： BEGIN display_time; END;注意：用户调用存储过程时必须具有EXECUTE执行权限 。例8.3 假设例8.1中的存储过程display_time是由system用户创建的，那么现在由scott用户调用，执行过程如下。CONNECT scott/tiger; -

4、以scott用户连接数据库EXEC system.display_time; -调用存储过程，由于缺乏权限出错CONNECT system/abcdef; -以system用户连接数据库GRANT EXECUTE ON display_time TO scott; -为scott用户授予EXECUTE权限CONNECT scott/tiger;SET SERVEROUTPUT ON;EXEC system.display_time;2. 带有IN参数的存储过程的创建定义输入型参数时可以指定IN关键字，也可以省略。例8.4 为scott.emp表创建一个能完成插入功能的存储过程insert_em

5、p。CREATE OR REPLACE PROCEDURE insert_emp (vno IN scott.emp.empno%TYPE, vname IN scott.emp.ename%TYPE DEFAULT NULL, vjob IN scott.emp.job%TYPE DEFAULT SALESMAN, vmgr IN scott.emp.mgr%TYPE DEFAULT 7369, vhired scott.emp.hiredate%TYPE DEFAULT SYSDATE, vsal scott.emp.sal%TYPE DEFAULT 800, vcomm m%TYPE D

6、EFAULT NULL, vdno scott.emp.deptno%TYPE DEFAULT 10)IS e_integrity EXCEPTION; PRAGMA EXCEPTION_INIT (e_integrity,-2291); -违反完整约束条件,未找到父项关键字 BEGIN INSERT INTO scott.emp VALUES(vno,vname,vjob,vmgr,vhired,vsal,vcomm,vdno);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line(该员工已经存在！); WHEN e_integr

7、ity THEN dbms_output.put_line(部门编号填写错误！);END;注意：在参数的定义上，除了向主键字段empno插入值的变量no没有设置默认值外，其他所有的变量都给出了默认值，这样当用户调用该存储过程时，可以指定1-8个任意个数的实参。 创建存储过程时有一个很重要的问题值得注意，那就是创建存储过程需要的权限，主要涉及到两类权限：创建存储过程自身需要的权限，即CREATE PROCEDURE系统权限。在存储过程内部执行各种操作时需要的显式权限。隐式权限在匿名块中起作用，但在命名块中不起作用。显式授权(直接将对象授权给用户)，隐式授权(通过将角色授权给用户)。思考：为什么命

8、名块对权限的要求更严格？如果以system用户创建例8.4中的存储过程 应首先执行以下命令：CONNECT scott/tiger; -以scott用户连接数据库GRANT INSERT ON emp TO system; -将emp表的插入数据权限授予给system用户。CONNECT system/abcdef;3. 有参数存储过程的调用 形参与实参的传递方式包括三种，分别是： 按名称传递。 EXEC insert_emp(no=1000,name=张三 , salary=1500); 按位置传递。 EXEC insert_emp(1001, 李四, CLERK); 混合传递。 EXEC

9、insert_emp(1002, 王五, salary=2500, deptno=30);不受参数位置的限制受参数位置的限制先位置传递后名称传递4. 带有OUT参数的存储过程的创建与执行 存储过程输出数据是利用OUT或IN OUT模式的参数实现。当定义输出参数时，必须使用OUT关键字标识。 例8.5 从scott.emp表中查询给定职工编号的职工姓名和工资，并利用OUT模式的参数将值传给调用者。CREATE OR REPLACE PROCEDURE select_emp(no IN scott.emp.empno%TYPE,name OUT scott.emp.ename%TYPE,salar

10、y OUT scott.emp.sal%TYPE)ISBEGIN SELECT ename,sal into name,salary FROM scott.emp WHERE empno=no;EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(该职工不存在！);END; 存储过程的调用对具有OUT参数的存储过程调用要特别注意，给出的实参一定是事先定义好的变量来接收OUT参数输出的值。例8.5的调用（使用绑定变量）（注：单句执行）VAR emp_name VARCHAR2(10); -定义绑定变量VAR emp_salary NUMBE

11、R; -定义绑定变量为NUMBER类型时，不能加长度EXEC select_emp(7369,:emp_name,:emp_salary); -使用绑定变量时，需要在绑定变量前添加冒号PRINT emp_name emp_salary; -输出两个绑定变量的值，中间用空格隔开存储过程的调用也可以使用匿名块调用，如下例所示。例8.6 使用匿名块调用存储过程select_emp 。DECLARE emp_name scott.emp.ename%TYPE; emp_salary scott.emp.sal%TYPE;BEGIN select_emp(7369,emp_name,emp_salary

12、); -调用存储过程 IF emp_name IS NOT NULL THEN -如果该职工存在，则输出 dbms_output.put_line(姓名是：|emp_name| 工资是：|emp_salary); END IF;END;5. 带有IN OUT参数的存储过程的创建 例8.7 编写程序，交换两个变量的值并输出。 CREATE OR REPLACE PROCEDURE swap(x IN OUT NUMBER ,y IN OUT NUMBER)IS z NUMBER;BEGINz:=x;x:=y;y:=z;END swap;例8.8 使用匿名块调用以上存储过程swap。DECLARE

13、 a NUMBER:=10; b NUMBER:=20;BEGIN dbms_output.put_line(交换前a和b是：|a| |b); swap(a,b); dbms_output.put_line(交换后a和b是：|a| |b);END;8.1.2 修改与删除存储过程 修改存储过程在创建存储过程时添加OR REPLACE选项 删除存储过程DROP PROCEDURE procedure_name事先应具有DROP ANY PROCEDURE系统权限 8.2 函数函数是另外一种命名的程序块，可以通过RETURN子句返回函数的执行结果。如果在应用程序中经常需要通过执行SQL语句来返回特定

14、数据，那么就可以基于这些操作建立特定的函数。 8.2.1 创建与调用函数创建与调用函数需要的权限和存储过程相同，都是CREATE PROCEDURE系统权限和EXECUTE对象权限，只是在语法上稍有不同，具体格式如下： CREATE OR REPLACE FUNCTION function_name (argument1 IN | OUT | IN OUT data_type , argument2 IN | OUT | IN OUT data_type,)RETURN data_typeIS |AS declaration_section;BEGIN executable_section;

15、RETURN expression;EXCEPTION exception_handlers; RETURN expression;END function_name; 例8.10 创建函数，从scott.emp表中查询指定职工的工资。CREATE OR REPLACE FUNCTION select_sal(no scott.emp.empno%TYPE)RETURN scott.emp.sal%TYPEIS salary scott.emp.sal%TYPE;BEGIN SELECT sal INTO salary FROM scott.emp WHERE empno=no; RETURN salary;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0;END;注意：因为函数具有返回值，所以调用函数是作为一个表达式的一部分，而不能像调用过程那样作为一个独立的语句使用。 调用函数的三种方式调用函数的方式1：使用变量接收返回值VAR salary NUMBER;EXEC :sa