Oracle中 关于数据库存储过程和存储函数的使用

存储过程和存储函数指存储在数据库*所有用户程序调用的子程序叫存储过程、存储函数。
存储过程没有返回值。存储函数有返回值

  创建存储过程
      用create procedure命令建立存储过程和存储函数。

      语法：
create [or replace] procedure过程名(参数列表)
as
        plsql子程序体；

  存储过程示例：为指定的职工在原工资的基础上长10%的工资
 
/*
为指定的职工在原工资的基础上长10%的工资，并打印工资前和工资后的工资
*/
sql> create or replace procedure raisesalary(empid in number)
    as
    psal emp.sal%type;--保存员工当前 工资
    begin
--查询该员工的工资
    select sal into psal from emp where empno=empid;
--给该员工涨工资
    update emp set sal = sal*1.1 where empno=empid;
--打印涨工资前后的工资
    dbms_output.put_line('员工号:' || empid || '涨工资前
   ' || psal || '涨工资后' || psal*1.1);
    end;
 1  /

procedure created
--存储过程调用
--方法一
sql> set serveroutput on
sql> exec raisesalary(7369);

员工号:7369涨工资前
800涨工资后880

方法二
    set serveroutput on
begin
 raisesalary(7369);
end;
/

pl/sql procedure successfully completed

 
      存储函数
      函数（function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

     建立存储函数的语法：

create [or replace] function函数名(参数列表)
return 函数值类型
as
plsql子程序体；

 
示例：查询某职工的年收入。
sql> /**/
    /*
    查询某职工的总收入
    */
    create or replace function queryempsalary(empid in number)
    return number
   as
    psal number; --定义变量保存员工的工资
    pcomm number; --定义变量保存员工的奖金
   begin
   select sal,comm into psal,pcomm from emp where empno = empid;
   return psal*12+nvl(pcomm,0);
   end;
   /

function created

   l   函数的调用

sql> declare
    v_sal number;
    begin
    v_sal:=queryempsalary(7934);
    dbms_output.put_line('salary is:'|| v_sal);
    end;
    /

salary is:15600

pl/sql procedure successfully completed

sql> begin
    dbms_output.put_line('salary is:'|| queryempsalary(7934));
    end;
    /

salary is:15600

pl/sql procedure successfully completed

 
       触发器
       数据库触发器是一个与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。

       触发器的类型
         语句级触发器
        在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

         行级触发器（for each row）
        触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量,识别值的状态。

      创建触发器
create  [or replace] trigger 触发器名
   {before | after}
   {delete | insert | update [of列名]}
   on  表名
   [for each row [when(条件) ] ]
   plsql 块

       示例1：限制非工作时间向数据库插入数据
sql> create or replace
    trigger securityemp
    before insert on emp
    declare
    begin
    if to_char(sysdate,'day')in('星期四','星期六','星期日')
    or to_number(to_char(sysdate,'hh24'))not between 8 and 18 then
    raise_application_error(-20001,'不能在非工作时间插入数据。');
    end if;
   end;
   /

trigger created

触发语句与伪记录变量的值

触发语句	:old	:new
insert	所有字段都是空(null)	将要插入的数据
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

示例2：确认数据（检查emp表中sal的修改值不低于原值）
sql> create or replace trigger checksal
    before update of sal on emp
    for each row
    declare
    begin
    if :new.sal<:old.sal then
    raise_application_error(-20001,'更新后的薪水比更新前小');
    end if;
    end;
   /

trigger created
运行后结果：
sql> update emp set sal=260 where empno=7499;

update emp set sal=260 where empno=7499

ora-20001: 更新后的薪水比更新前小
ora-06512: 在 "scott.checksal", line 4
ora-04088: 触发器 'scott.checksal'执行过程中出错

       触发器总结
      触发器可用于
•         数据确认
•         实施复杂的安全性检查
•         做审计，跟踪表上所做的数据操作等

      查询触发器、过程及函数
•         select * from user_triggers;
•         select * from user_source;