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

存储子程序和应用子程序的区别：
4、建立存储子程序的文档存储在数据字典中； 建立应用子程序的文档存储在当前的应用中。
5、存储子程序的安全性有数据库提供保证，必 须通过授权才能使用；应用子程序的安全性靠 应用程序保证。
过程——应用子程序
在PL/SQL程序中，应用子程序通常在 DECLARE说明部分的最后定义，在执行部 分调用，仅限于在本程序内使用。
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
过程——应用子程序
过程的格式： 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
存储过程与过程的区别
存储子程序和应用子程序的区别： 1、存储子程序存储在数据库中；应用子程序存
储在应用程序中。 2、任何数据库工具或应用中都可以调用存储子
程序；只有在子程序建立的应用中才能调用应 用子程序。 3、存储子程序不可以调用应用子程序；应用子 程序可以调用存储子程序。
存储过程与过程的区别
BEGIN 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;
调用该过程： 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);
使用EXECUTE(简写EXEC)命令调用。 使用CALL命令调用。 在匿名的程序块中直接以过程名调用。
例8.2 使用三种方式调用上面创建的存储过程 display_time 。
方式一：
SET SERVEROUTPUT ON
EXECUTE display_time;
方式二：
CALL 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 scott.emp.comm%TYPE DEFAULT NULL, deptno scott.emp.deptno%TYPE DEFAULT 10 )
定义输入型参数时可以指定IN关键字，也可以省 略。
例8.4 为scott.emp表创建一个能完成插入功能的存储 过程insert_emp。
CREATE OR REPLACE PROCEDURE insert_emp (no IN scott.emp.empno%TYPE, name IN scott.emp.ename%TYPE DEFAULT NULL, job IN scott.emp.job%TYPE DEFAULT
过程
在主程序中调用该过程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;
BEGIN a:=100; p3(a); DBMS_OUTPUT.PUT_LINE(a);
END; /
三种模式参数的比较
IN 参数
OUT参数
IN OUT参数
默认模式
显示指定
显示指定
传送值给过程或函数 过程返回值给调用者 双向数据传递
形参作用如同一个常量 形参作用如同一个未 形参作用如同一个初
初始化的变量
Oracle 数据库
第8章 存储过程与函数的Baidu Nhomakorabea建
本章要点
1
存储过程的创建与执行
2
函数的创建与执行
存储过程、函数
存储过程、函数是以编译形式存储在数据库 中的命名的PL/SQL程序块，它们可以带有参 数，并可以被调用。
过程和函数可以称为PL/SQL的子程序，包括 存储在服务器端的存储子程序或应用程序中 的应用子程序，是由说明部分，执行部分和 可选择的出错处理部分组成。
例，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;
始化的变量
形参不能被赋值，只读
实参可以是常量、初始 化的变量或表达式
形参必须赋值
形参可读写
实参必须是一个变量 实参必须是一个变量
地址传送（值的传入是 值传送（一个值的复 值传送（一个值的复 通过向过程传送一个指 制从过程中被传出） 制被传入传出） 向实参值的指针来实现）
8.1 存储过程
存储过程是一个命名的程序块，包括过程 的名称、过程使用的参数、过程执行的操作。
8.1.1 创建与调用存储过程
创建存储过程包括存储过程头部的声明和过程内操作的定 义两部分。
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [IN | OUT | IN OUT] data_type , argument2 [IN | OUT | IN OUT] data­_type,…)]
BEGIN a:=100; p2(a); a:=a+10; DBMS_OUTPUT.PUT_LINE(a);
END; /
（3）in out：
过程
表示该类参数既可以向过程体传值，也可以在 过程体中赋值，以便向过程体外传值。也就是说， 一个in out 参数允许调用程序通过它向过程传递初 始数据，并且可返回经过程修改后的数据。
（2）out：
一个out参数主要用于过程返回某些值给过程的 调用者，能够通过out模式传递的参数只能是变量类 型。
在过程内部，该参数初始值为null，使用前必 须为其赋值。在调用程序中，由于out参数只返回值， 不接收值，所以调用程序不必为该过程传递参数。
过程
例：DECLARE a integer; PROCEDURE p2 (b out number) IS BEGIN b:=b+50; END;
权限出错 CONNECT system/abcdef; --以system用户连接数据库 GRANT EXECUTE ON display_time TO scott; --为scott
用户授予EXECUTE权限 CONNECT scott/tiger; SET SERVEROUTPUT ON; EXEC system.display_time;
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,
1. 无参数存储过程的创建与调用
例8.1 创建存储过程，输出系统的日期和时间 。
CREATE OR REPLACE PROCEDURE display_time
IS BEGIN
dbms_output.put_line(systimestamp); END display_time;
在SQL*Plus环境中调用存储过程有三种方 法：
方式三： BEGIN display_time;
注意：用户调用存储过程 时必须具有EXECUTE执 行权限 。
END;
例8.3 假设例8.1中的存储过程display_time是由 system用户创建的，那么现在由scott用户调用， 执行过程如下。
CONNECT scott/tiger; --以scott用户连接数据库 EXEC system.display_time; --调用存储过程，由于缺乏
2. 带有IN参数的存储过程的创建
形参和实参 过程和函数的参数表定义的是形参，在过程或
函数体中直接使用形参的名称来引用，在主程序中 调用过程或函数时，与形参相对应的实际的数值就 会传递到过程或函数中，这些值就是实参，可以是 文字，常量或变量的形式。
2. 带有IN参数的存储过程的创建
在存储过程中可以通过使用输入参数，将应用程 序的数据传递给存储过程。
END; 或
test (p3=>v3, p1=>v1, p2=>v2, p4=>v4)
过程
过程和函数通过参数传递数据 过程参数有以下三种模式
（1）in （2）out （3）in out
过程
（1）in：
在过程内部，in参数类似一个常量，不能对它赋值，是 只读的参数。在编译时，如果检测到in 参数位于赋值表达 式的左侧，则编译失败。
能够通过in out模式传递的参数只能是变量。
在过程中，可对in out参数进行读写，改变所 传递的数据。通过in out形参可以实现调用程序和被 调用过程之间双向的数据传递。
过程
DECLARE a integer; PROCEDURE p3 (b in out number) IS BEGIN b:=b+50; END;
过程——应用子程序
形参和实参
过程的参数表定义的是形参。 在调用过程和函数时，实参与形参要一一
对应，对应方式有两种，一种是位置对应， 另一种是名字对应。
过程——应用子程序
① 位置对应
过程和函数中形参的位置与调用程序中调用它们时实参一一 对应，数据类型相同。任何情况下对应关系不能打乱。
PROCEDURE test(p1 varchar2,p2 number,p3 char, p4 date) IS … BEGIN… END;
IS |AS [declaration_section;]
BEGIN
注意与匿名块有三点区别： 1.无DECLARE关键字
executable_section; [EXCEPTION
2.在END后面可以加过程名 作为定义结束的标志
exception_handlers;] 3.存储过程定义完成后需要调 END [procedure_name]; 用才能执行过程内部的代码。
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; /