第7章 存储过程、触发器和程序包

1200.00 1600.00 1250.00 2975.00 1250.00 2850.00 2450.00 3000.00 5000.00 1500.00 1100.00 950.00 3000.00 1300.00
declare name emp.ename%type; sal emp.sal%type; begin search_employee(7499,name,sal); dbms_output.put_line('姓名:' || name); dbms_output.put_line('薪金:' || sal); End; 姓名:ALLEN 薪金:1600
位置表示法,按定义时的顺序传递参数
add_employee(8000, 'ATG', 'CLERK', to_date('2013.10.21','yyyy.mm.dd'), 1500);
2. Out参数
传入后，由用户接收参数值
create or replace procedure search_employee(empno_param in number, name_param out emp.ename%type, salary_param out emp.sal%type) is begin select ename,sal into name_param, salary_param from scott.emp where empno=empno_param; exception when no_data_found then name_param :='NULL'; salary_param :=-1; dbms_output.put_line('未找到指定编号的员工信息!'); end search_employee;
22
7.2函数
create or replace function average(num1 number,num2 number) return number is res number; begin res:=(num1+num2)/2; return res; end average;
declare avg_number number; begin avg_number:=average(45,59); dbms_output.put_line(avg_number); end;
--exec存储过程名称（参数）
错误信息显示
SQL*PLUS中
SQL> show error
PL/SQL Developer
7.1.2 参数
参数的三种模式： IN
用于接受调用程序的值 默认的参数模式
OUT
用于向调用程序返回值 返回值需要变量接收
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值 一般不推荐使用
1. In参数
传入，只能被读取 create or replace procedure add_employee( id_param in number, name_param in varchar2,job_param in varchar2, hire_param in date, salary_param in number) is begin insert into scott.emp(empno,ename,job,hiredate,sal) values(id_param,name_param,job_param,hire_ param,salary_param); end add_employee;
begin insert_into_log('添加数据到TEMP表之前调用'); insert into temp values(1); insert_into_log('添加数据到TEMP表之后调用'); rollback; end; 分析下列语句执行结果： Select * from temp; Select * from log_table;
第七章 存储过程、触发器
本章知识要点：
创建和使用子程序(过程和函数) 理解和应用触发器 程序包
2
7.1 存储过程
命名的 PL/SQL 块，编译并存储在数据库中。 可以接受零个或多个参数 存储过程的各个部分：
声明部分 可执行部分 异常处理部分(可选)
3
7.1 存储过程
只有in参数才有默认值 没有默认值的In参数放在参数列表的开始位置 其后是out参数 然后是In Out参数 最后才是具有默认值的In参数 对按位置表示法传输参数，必须遵循上述原则
7.1.4过程中的事务处理
Orcale支持事务嵌套 子事务可以独立于父事务处理进行提交和回滚
7.1.3பைடு நூலகம்默认值
create or replace procedure add_employee( id_param in number, name_param in varchar2, job_param in varchar2 default 'SALESMAN', hire_param in date default sysdate, salary_param in number default 1000) is begin insert into scott.emp(empno,ename,job,hiredate,sal) values(id_param,name_param,job_param, hire_param,salary_param); end add_employee; add_employee(id_param => 8124,name_param => '苏珊');
过程和函数的比较
过程
作为 PL/SQL 语句执行 独立表达式被调用
函 数
作为表达式(PL/SQL 块或SQL 语句)的一部分调用
在规格说明中不包含 RETURN 子句 必须在规格说明中包含 RETURN 子句 不返回任何值 可以包含 RETURN 语句，但是与函 数不同，它不能用于返回值 必须返回单个值 必须包含至少一条 RETURN 语句
24
思考与练习
二、选择题（2,3,4,7） 三、简答题（1,2）
7.3 触发器
触发器类似过程和函数，存储在数据库中,包括： 声明部分 执行逻辑处理部分 异常处理部分
26
7.3.1 触发器的概述
触发器是当特定事件出现时自动执行的存储过 程 特定事件可以是执行更新的DML语句和DDL 语句 触发器不能被显式调用 触发器的功能：
Create or replace procedure insert_into_log (msg_param varchar2) is Begin insert into log_table(username, message) values(user,msg_param); commit; End;
自动生成数据 强制复杂的完整性约束条件 提供审计和日志记录 启用复杂的业务逻辑
27
1.创建触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] ON table_or_view_name [REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW] [WHEN (condition)] pl/sql_block;
参数传递方法
名称表示法
Procedure_name(param_name => value [, param_name => value])
add_employee(id_param => 8000,name_param => 'ATG',job_param => 'CLERK',hire_param => to_date('2012.10.18','yyyy.mm.dd'),salary_param => 1500);
存储过程的优点： 模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全
4
7.1.1创建存储过程
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] IS <local variable declaration> 创建过程，可指定运行过程需传递的参数 BEGIN 包括在过程中要执行的语句 <executable statements> [EXCEPTION 处理异常 <exception handlers>] END [procedure name];
存储过程调用方式
在PL/SQL块中
SQL> set serveroutput on SQL> begin 2 sample_proc; --直接输入存储过程名称（参数） 3 end; 4 / hello world
在SQL*PLUS中
SQL> exec sample_proc;
hello world
EMPNO ENAME SAL 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
7.2函数
函数是可以返回值的命名的 PL/SQL 子程序。 创建函数的语法：
CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)] RETURN <datatype> IS [local declarations] BEGIN Executable Statements; RETURN result; EXCEPTION Exception handlers; END;
3. In Out参数
In参数
调用过程时接收一个值，使用过程不能修改
Out参数
调用过程时为空，在过程执行中将为这个参数指定一个值
In Out参数
create or replace procedure swap (num1_param in out number, num2_param in out number) is var_temp number; begin var_temp := num1_param; num1_param := num2_param; num2_param := var_temp; end swap;
5
最简单的存储过程
SQL> create procedure sample_proc is 2 begin 3 null; 4 end sample_proc; 5 / Procedure created SQL>
create procedure sample_proc is begin dbms_output.put_line('hello world'); end sample_proc; ORA-00955: name is already used by an existing object create or replace procedure sample_proc is begin dbms_output.put_line('hello world'); end sample_proc; Procedure created
无论父事务是提交还是回滚，用户可以确保子事务被执行
每个过程相当于子事务
过程中的事务处理示例
Create table temp (n number); Create table log_table(username varchar2(20), message varchar2(4000));