第8章 存储过程与函数的创建

过程
在主程序中调用该过程P1: DECLARE zero CONSTANT INTEGER:=0; my_data integer:=2; PROCEDURE p1(v in integer) IS BEGIN dbms_output.put_line(v+1); END; BEGIN p1(123); p1(zero); p1(my_data); END;
权限出错 CONNECT system/abcdef; --以system用户连接数据库 GRANT EXECUTE ON display_time TO scott; --为scott
用户授予EXECUTE权限 CONNECT scott/tiger; SET SERVEROUTPUT ON; EXEC system.display_time;
'SALESMAN', mgr IN scott.emp.mgr%TYPE DEFAULT 7369, hiredate scott.emp.hiredate%TYPE DEFAULT
SYSDATE, salary scott.emp.sal%TYPE DEFAULT 800, comm m%TYPE DEFAULT NULL, deptno scott.emp.deptno%TYPE DEFAULT 10 )
调用该过程： DECLARE v1 varchar2(10); v2 number(2); v3 char(6); v4 date; BEGIN test(v1,v2,v3,v4); END;
过程——应用子程序
② 名字对应
参数位置不重要
BEGIN test (p1=>v1, /*=>链接运算符*/ p2=>v2, p3=>v3, p4=>v4);
8.1.1 创建与调用存储过程
创建存储过程包括存储过程头部的声明和过程内操作的定 义两部分。
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [IN | OUT | IN OUT] data_type , argument2 [IN | OUT | IN OUT] data­_type,…)]
始化的变量
形参不能被赋值，只读
实参可以是常量、初始 化的变量或表达式
形参必须赋值
形参可读写
实参必须是一个变量 实参必须是一个变量
地址传送（值的传入是 值传送（一个值的复 值传送（一个值的复 通过向过程传送一个指 制从过程中被传出） 制被传入传出） 向实参值的指针来实现）
8.1 存储过程
存储过程是一个命名的程序块，包括过程 的名称、过程使用的参数、过程执行的操作。
例，PROCEDURE p1(v in integer) IS BEGIN v:=v+1; dbms_output.put_line(v); END; 编译出错
____________________________________________________ PROCEDURE p1(v in integer) IS BEGIN dbms_output.put_line(v+1); END;
IS |AS [declaration_section;]
BEGIN
注意与匿名块有三点区别： 1.无DECLARE关键字
executable_section; [EXCEPTION
2.在END后面可以加过程名 作为定义结束的标志
exception_handlers;] 3.存储过程定义完成后需要调 END [procedure_name]; 用才能执行过程内部的代码。
BEGIN a:=100; p3(a); DBMS_OUTPUT.PUT_LINE(a);
END; /
三种模式参数的比较
IN 参数
OUT参数
IN OUT参数
默认模式
显示指定
显示指定
传送值给过程或函数 过程返回值给调用者 双向数据传递
形参作用如同一个常量 形参作用如同一个未 形参作用如同一个初
初始化的变量
Bቤተ መጻሕፍቲ ባይዱGIN a:=100; p2(a); DBMS_OUTPUT.PUT_LINE(a);
END; / 请大家检查程序的问题！ 在b:=b+50;之前要先给b赋初值b:=0; 否则b为NULL.
过程
例：DECLARE a integer; PROCEDURE p2 (b out number) IS BEGIN b:=50; FOR i IN 1..10 LOOP b:=b+1; END LOOP; END;
存储子程序和应用子程序的区别：
4、建立存储子程序的文档存储在数据字典中； 建立应用子程序的文档存储在当前的应用中。
5、存储子程序的安全性有数据库提供保证，必 须通过授权才能使用；应用子程序的安全性靠 应用程序保证。
过程——应用子程序
在PL/SQL程序中，应用子程序通常在 DECLARE说明部分的最后定义，在执行部 分调用，仅限于在本程序内使用。
存储过程与过程的区别
存储子程序和应用子程序的区别： 1、存储子程序存储在数据库中；应用子程序存
储在应用程序中。 2、任何数据库工具或应用中都可以调用存储子
程序；只有在子程序建立的应用中才能调用应 用子程序。 3、存储子程序不可以调用应用子程序；应用子 程序可以调用存储子程序。
存储过程与过程的区别
方式三： BEGIN display_time;
注意：用户调用存储过程 时必须具有EXECUTE执 行权限 。
END;
例8.3 假设例8.1中的存储过程display_time是由 system用户创建的，那么现在由scott用户调用， 执行过程如下。
CONNECT scott/tiger; --以scott用户连接数据库 EXEC system.display_time; --调用存储过程，由于缺乏
过程——应用子程序
形参和实参
过程的参数表定义的是形参。 在调用过程和函数时，实参与形参要一一
对应，对应方式有两种，一种是位置对应， 另一种是名字对应。
过程——应用子程序
① 位置对应
过程和函数中形参的位置与调用程序中调用它们时实参一一 对应，数据类型相同。任何情况下对应关系不能打乱。
PROCEDURE test(p1 varchar2,p2 number,p3 char, p4 date) IS … BEGIN… END;
能够通过in out模式传递的参数只能是变量。
在过程中，可对in out参数进行读写，改变所 传递的数据。通过in out形参可以实现调用程序和被 调用过程之间双向的数据传递。
过程
DECLARE a integer; PROCEDURE p3 (b in out number) IS BEGIN b:=b+50; END;
v_sal NUMBER(5); sal_exp EXCEPTION; BEGIN SELECT sal INTO v_sal FROM scott.emp WHERE empno=emp_no; IF v_sal IS NULL THEN
RAISE sal_exp; ELSE
UPDATE scott.emp SET sal=sal+inc WHERE empno=emp_no; END IF; EXCEPTION
（2）out：
一个out参数主要用于过程返回某些值给过程的 调用者，能够通过out模式传递的参数只能是变量类 型。
在过程内部，该参数初始值为null，使用前必 须为其赋值。在调用程序中，由于out参数只返回值， 不接收值，所以调用程序不必为该过程传递参数。
过程
例：DECLARE a integer; PROCEDURE p2 (b out number) IS BEGIN b:=b+50; END;
IS
e_integrity EXCEPTION;
注意：在参数的
PRAGMA EXCEPTION_INIT (e_integ定ri义ty上,-2，2除91了);向
BEGIN
主键字段empno插 入值的变量no没
INSERT INTO scott.emp
有设置默认值外，
VALUES(no,name,job,mgr,hiredat其e,他sa所la有ry的,c变o量mm,
过程——应用子程序
过程的格式： PROCEDURE 过程名[参数1，参数2…]
IS 说明部分
BEGIN 执行部分
EXCEPTION 出错处理部分
END;
例：给某一指定的员工涨指定数量的工资。
set serveroutput on DECLARE
eno scott.emp.empno%type; PROCEDURE raise_salary(emp_no NUMBER, inc NUMBER) IS
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(emp_no)||'无此职工'); WHEN sal_exp THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(emp_no)||'工资为空'); END; BEGIN eno:=7000; raise_salary(eno,300); END; /
使用EXECUTE(简写EXEC)命令调用。 使用CALL命令调用。 在匿名的程序块中直接以过程名调用。
例8.2 使用三种方式调用上面创建的存储过程 display_time 。
方式一：
SET SERVEROUTPUT ON
EXECUTE display_time;
方式二：