Oracle PL/SQL存储过程,函数,包,触发器的使用

时间:2022-06-13 00:06:42

Oracle PL/SQL存储过程,函数,包,触发器的使用

 

PL/SQL高级编程部分
无名块不存储在数据库中,并且不能从其他的PL/SQL块中调用
有名块存储在数据库数据字典中,可以在客户端与服务器端的任何工具和任何应用中运行
(1).存储过程

(2).存储函数

(3).包

(4).触发器
过程执行某一动作,函数计算一个值,包将相关的过程函数逻辑上捆绑在一起,触发器隐式执行某一动作
存储过程和函数是把一个PL/SQL块编译后存进数据库数据字典中,以后可以重复使用的模式对象
注意:在存储过程和函数中的形式参数及返回结果如果是字符型,不要指定指定长度

1.存储过程
(1).创建存储过程

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [OR REPLACEPROCEDURE 过程名[(参数名[IN|OUT|IN OUT]数据类型...)]  
  2. {IS | AS}  
  3.     [说明部分]  
  4. BEGIN  
  5.     语句系列  
  6. [EXCEPTION 出错处理]  
  7. END[过程名];  

 

参数说明:

IN 输入参数:用来从调用环境中向存储过程传递值,即IN模式参数不能出现在赋值语句式边
OUT输出参数:用来从存储过程中返回值给调用者,在过程体内,必须给OUT模式参数赋值,OUT模式参数
可以出现在赋值语句的左边.没有值的OUT模式参数不能出现赋值语句的右边.
IN OUT:输入参数,输出参数.即可从调用者向存储过程传递值,过程执行后又可返回改变后的值给调用者. 
eg:
给某一指定的员工涨指定数量的工资,该存储过程有两个形式参数:emp_id和v_increase,没有指定参数
的模式,默认是IN 模式.

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create procedure raise_salary(emp_id integer, v_increase realis  
  2. BEGIN  
  3.   update emp set sal = sal + v_increase where empno = emp_id;  
  4.   commit;  
  5. end raise_salary;  

 
根据给定的员工号(通过IN模式参数代入),用OUT模式参数返回员工的姓名,工资和奖金信息.

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace procedure query_emp(v_emp_no   IN emp.empno%type,  
  2.                                       v_emp_name out emp.ename%type,  
  3.                                       v_emp_sal  out emp.sal%type v_emp_comm out emp.comm%type) is  
  4. BEGIN  
  5.   select ename, sal, comm  
  6.     into v_emp_name, v_emp_sal, v_emp_comm  
  7.     from emp  
  8.    where empno = v_emp_no;  
  9. end query_emp;  

 

2.存储函数
(1).创建存储函数的语法如下:

 

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [or replace ]function 函数名[(参数名[in]数据类型...)] return 数据类型  
  2. {is|as}  
  3.     [说明部分]  
  4. BEGIN  
  5.     语句序列  
  6.     return (表达式)  
  7. [EXCEPTION 例外处理程序]  
  8. END 函数名;  

 
编写一个函数,计算几个人的平均工资,并在函数体的循环过程中输出结果

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace function average_sal(v_n in number(3)) return number  
  2. is   
  3.    cursor c_emp is select empno,sal from emp;  
  4.    v_total_sal emp.sal%type:=0;  
  5.    v_counter number;  
  6.    v_emp_no emp.empno%type  
  7. BEGIN  
  8.    FOR r_emp in c_emp loop exit when c_emp% rowcount>v_n or c_emp%notfound;  
  9.            v_total_sal:=v_total_sal+r_emp.sal;  
  10.            v_counter:=c_emp%rowcount;  
  11.            v_emp_no:=r_emp.empno;  
  12.            dbms_output.put_line('loop='||v_counter||';empno='||v_emp_no);  
  13.    end loop;  
  14.    return (v_total_sal/v_counter);  
  15. end average_sal;  

 


注意存储过程与函数的区别

1...返回值不同:存储函数有零个或多个参数,函数可以有IN参数,但不能有OUT参数。函数只返回一个值,函数值的返回是靠return 子句返回的
2...存储过程有零个或多个参数,地程可以有IN参数,在调用过程时通过参数列表接受IN参数的输入。过程不返回值,其返回值是靠OUT参数带出来的
过程可以有零个或多个OUT参数返回结果
调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现
过程名(实际参数1,实际参数2....)
函数可以在任何表达式能够出现的地方被调用,调用函数的语句不能作为可执行语句单独出现在PL/SQL块中
变量名:函数名(实际参数1,实际参数2....);

 

存储过程的调用
(1).在PL/SQL块中的调用
eg:

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. DECLARE  
  2.   V_empno emp.empno%type := 7777;  
  3.   v_ename emp.ename%type;  
  4.   v_sal   emp.sal%type;  
  5.   v_comm  emp.comm%type;  
  6. BEGIN  
  7.   query_emp(v_empno, v_ename, v_sal, v_comm);  
  8.   dbms_output.put_line(v_ename || ' ' || v_sal || ' ' || v_comm);  
  9. END;  

 

 

函数的调用

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. DELCARE   
  2.   v_empno number:=111;  
  3.   v_sal nubmer;  
  4. BEGIN  
  5.   v_sal:=get_sal(v_empno);  
  6.   dbms_output.put_line(v_sal);  
  7. end;  

 

(2).在SQL*PLUS中的调用
过程的调用
eg:

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. set serveroutput on/*激活DBMS_OUTPUT系统包*/  
  2. ACCEPT p_emp_no prompt 'please enter the employee number:';/*接受员工号*/  
  3. variable v_emp_name varchar2(14);/*定义存放OUT参数输出结果的变量 */  
  4. variable v_emp_sal number;  
  5. variable v_emp_comm number;  
  6. execute query_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);  

 

注意在SQL*PLUS中,用variable定义的变量在引用时,必须前面加昌号(:),用accept接收的变量在引用时,必须前面加& 符号
函数的调用
函数不能作为一条语句单独出现,只能出现在别的过程中作为别的过程的参数
SQL>EXECUTE dbms_output.put_line(get_sal(0000));
或者
SQL>SELECT get_sal(0000) from dual;

 

3.包
(1).创建包的说明:

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [or replace] package 包名  
  2.    {is|as}  
  3.    公共变量的定义  
  4.    公共类型的定义  
  5.    公共出错处理的定义  
  6.    公共游标的定义  
  7.    函数说明  
  8.    过程说明  
  9.   
  10. end;  

 

eg:制作一个包的说明,生成一个管理雇员薪水的包sal_package,其中包括一个为雇员加薪的过程和降薪的过程
并且在包中还有两个记录所有雇员薪水增加和减少的全局变量

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create package sal_package is  
  2.   procedure raise_sal(v_empno emp.empno%type), v_sal_increment emp.sal%type);  
  3.   procedure reduce_sal(v_empno emp.empno%type, v_sal_reduce emp.sal%type);  
  4.   v_raise_sal  emp.sal%type := 0;  
  5.   v_reduce_sal emp.sal%type := 0;  
  6. end;  

 

 

创建包的主体

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [or replace] package BODY 包名  
  2.    {is|as}  
  3.    公共变量的定义  
  4.    公共类型的定义  
  5.    公共出错处理的定义  
  6.    公共游标的定义  
  7.    函数说明  
  8.    过程说明  
  9.   
  10. end;  

 制作sal_package包的包主体

 

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace package body sal_package is  
  2.   procedure raise_sal(v_empno emp.empno%type, v_sal_increment emp.sal%type) is  
  3.   BEGIN  
  4.     UPDATE emp set sal = sal + v_sal_increment where empno = v_empno;  
  5.     commit work;  
  6.     v_raise_sal := v_raise_sal + v_sal_increment;  
  7.   end;  
  8.   procedure reduce_sal(v_empno emp.empno%type, v_sal_reduce emp.sal%type) is  
  9.   BEGIN  
  10.     update emp set sal = sal - v_sal_reduce where empno = v_empno;  
  11.     commit work;  
  12.     v_reduce_sal := v_reduce_sal + v_sal + reduce;  
  13.   end;  
  14. end;  

 

包的调用:包名.过程名
SQL>EXECUTE sal_package.raise_sal(111,23423);

 

4.触发器
eg:
制作一个数据库触发器,将那些超过其工种工资范围的员工信息记录到audit_message表中,在sal_guide记录
了每一工种的工资范围

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace trigger check_sal  
  2.   before insert or update of sal, job on emp  
  3.   for each row  
  4.   when (new.job <> 'persident')  
  5. DECLARE  
  6.   v_minsal sal_guide.minsal%type;  
  7.   v_maxsal sal_guide.maxsal%type;  
  8.   e_sal_out_of_range exception;  
  9. BEGIN  
  10.   select minsal, maxsal  
  11.     into v_minsal, v_maxsal  
  12.     from sal_guide  
  13.    where job = :new.job;  
  14.   if :new.sal < v_minsal or :new :sal > v_maxsal then  
  15.     raise e_sal_out_of range;  
  16.   end if;  
  17. exception  
  18.   when e_sal_out_of_range then  
  19.     insert into audit_message  
  20.       (line_nr, line)  
  21.     values  
  22.       (1,  
  23.        'Salary' || TO_CHAR(:new.sal) || 'is out of range for employee' ||  
  24.        TO_CHAR(:new.empno));  
  25. END;  

 

(1).触发器的组成
触发时间:before after
触发事件:insert update delete
触发器类型:statement,row (语句级,行级)
触发器体(完整的PL/SQL块)
触发器可分语句级触发器,行级触发器
(2).语句级触发器
创建语法如下:

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [or replacetrigger trigger_name  
  2.   {before|after} event1[or event2] on table_name  

 

eg:
创建一个before 型语句级触发器,限制一周内向emp表插入数据的时间,如果是周六,周日
或晚上6点到第二天早上8点之间插入,则中断操作,并提示用户不允许在此时间向emp表插入

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace trigger secure_emp  
  2.   before insert on emp  
  3. BEGIN  
  4.   IF (TO_CHAR(sysdate, 'DY'IN ('SAT''SUN')) or  
  5.      (TO_CHAR(sysdate, 'HH24'NOT BETWEEN '8' and '18'then  
  6.     raise_application_error(-20500,  
  7.                             'you may only insert into emp during normal hours.');  
  8.   END IF;  
  9. END;  

 


(3).使用触发器谓词(inserting,updating,deleting)
通过谓词可以创建一个包含多个触发事件的触发器
对上例进行扩展不但限制插入数据的时间,还限制进行数据修改和删除的时间

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace trigger secure_emp  
  2. before delete or insert or update on emp  
  3. BEGIN   
  4.    if (TO_CHAR(sysdate,'DY' IN('SAT','SUN'))  OR (TO_NUMBER(sysdate,'HH24')NOT BETWEEN 8 AND 18 )THEN  
  5.     if deleting then  
  6.        raise_application_error(-20502,'You may only deletefrom emp during normal hours. ');  
  7.     elsif inserting then  
  8.        raise_application_error(-20500,'You may only insert into emp during mormal hours');  
  9.     else  
  10.        RAISE_APPLICATION_ERROR(-20504,'You may only update emp table during normal hours.');  
  11.     end if ;  
  12.    end if ;  
  13. end;  

 

(4).行级触发器
创建语法

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create [or replace]trigger trigger_name  
  2.    {before|after} event1[or event2....] on table_name  
  3.    for each row [when restricting_condition]  

 
PL/SQL
eg:创建一个行级触发器,将每个用户对数据库emp表进行数据库操纵(插入,更新,删除)的次数记录到
audit_table表中

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace trigger audit_emp  
  2.   after delete or insert or update on emp  
  3.   for each row  
  4. BEGIN  
  5.   IF DELETING THEN  
  6.     update audit_table  
  7.        set del = del + 1  
  8.      where user_name = user  
  9.        and table_name = 'emp'  
  10.        and column_name is null;  
  11.   elsif inserting then  
  12.     update audit_table  
  13.        set ins = ins + 1  
  14.      where user_name = user  
  15.        and table_name = 'emp'  
  16.        and column_name is null;  
  17.   else  
  18.     update audit_table  
  19.        set upd = upd + 1  
  20.      where user_name = user  
  21.        and table_name = 'emp'  
  22.        and column_name is null;  
  23.   end if;  
  24. end;  

 
使用行级触发器的标识符(:OLD和:NEW)
在列名前加上:OLD标识符表示该列变化前的值,加上:NEW标识符表示变化后的值
eg:
在行级触发器加WHEN限制条件,根据销售员工资的改变自动计算销售员的奖金

Sql代码  Oracle PL/SQL存储过程,函数,包,触发器的使用
  1. create or replace trigger derive_comm  
  2.   before update of sal on emp  
  3.   for each row  
  4.   when (new.job = 'SALESMAN')  
  5. BEGIN  
  6.   :new.comm := :old.comm * (:new.sal / :old.sal);  
  7. end;