Oracle Day09 存储与触发器

时间:2022-04-01 05:46:37

1.存储

  存储过程、存储函数:指存储在数据库*所有用户程序调用的子程序。

  --创建存储过程(procedure)

    --用create procedure 命令建立存储过程。

        格式: create or replace procedure 过程名(参数列表)

             as

              PLSQL子程序体;

SQL> create or replace procedure hh  //创建存储过程
2 as
3 begin
4 dbms_output.put_line('世界您好!');
5 end;
6 / 过程已创建。
SQL> set serveroutput on;
SQL> begin //读取存储内容
2 hh();
3 end;
4 / 世界您好!

    --例:给一个员工号,要求返回它的年薪

  1  create or replace procedure nx(sd in number,psal out number)
2 as
3 csal emp.sal%type;
4 ccomm emp.comm%type;
5 begin
6 select sal,comm into csal,ccomm from emp where empno=sd;
7 psal :=csal*12 + nvl(ccomm,0);
8* end;
SQL> / 过程已创建。 SQL> ed
已写入 file afiedt.buf 1 declare
2 psal number;
3 begin
4 nx(7566,psal);
5 dbms_output.put_line(psal);
6* end;
SQL> /
35700

   

  --存储函数(function)

      --函数(function)是一个已命名的程序,可以带参数,并返回一个计算值。函数和过程的结构类似,但必须要有一个return返回语句,用于返回函数值。函数要指定函数名、结果值的类型和参数类型。

        格式: create or replace function 函数名(参数列表)

            return 函数值类型

            as

            PLSQL子程序体;

      --过程和函数中的in 和 out

       注意:过程和函数的区别: 函数可以有一个返回值,而过程没有返回值。

           过程和函数都可以通过out指定一个或多个输出参数。可以利用out在过程和函数中实现返回多个值。

      什么时候用存储过程或存储函数?

         如果只有一个返回值,用存储函数;否则,用存储过程。

2.触发器

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

  创建触发器:

        create or replace trigger

        before/after

        delete/insert/update of 列名

        on 表名

            (for each row)  when(条件)

           plsql块;

  --触发器的类型

    --语句级触发器: 在指定的操作语句之前或之后执行一次,不管语句影响了多少行。

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

  --触发器总结

      --触发器用途:   1)数据确认

              2)复杂的安全性检查

              3)做审计,跟踪表上所做的数据操作

              4)数据的备份和同步

      --查询触发器、过程及函数

              select * from user_trigger;

              select * from user_source;

  --触发语句与伪记录的值

  Oracle Day09 存储与触发器

 --例:对emp表不能在周末和非上班时间进行insert操作
SQL> create or replace trigger empinsert
2 before insert on emp
3 begin
4 if to_char(sysdate,'day') = '星期六' or to_char(sysdate,'hh24') not between 9 and 18 then raise_application_error(-20001,'不能在非上班时间做插入操作');
5 end if;
6 end;
7 / 触发器已创建 SQL> insert into emp(empno,deptno) values(1001,30);
insert into emp(empno,deptno) values(1001,30)
*
第 1 行出现错误:
ORA-20001: 不能在非上班时间做插入操作
ORA-06512: 在 "SCOTT.EMPINSERT", line 2
ORA-04088: 触发器 'SCOTT.EMPINSERT' 执行过程中出错

  --例:  监控每个部门的人数不能超过6人

SQL> ed
已写入 file afiedt.buf 1 create or replace trigger empinsert
2 before insert on emp
3 for each row
4 declare
5 jnumber number;
6 begin
7 select count(*) into jnumber from emp where deptno=:new.deptno;
8 if jnumber>=6 then raise_application_error(-20001,'部门人数不能超过6人');
9 end if;
10* end;
SQL> / 触发器已创建 SQL> ed
已写入 file afiedt.buf 1* select count(*) from emp where deptno=30
SQL> / COUNT(*) //查看部门号为30的部门人数
----------
6 SQL> insert into emp(empno,deptno) values(123,30);
sert into emp(empno,deptno) values(123,30)
*
1 行出现错误:
ORA-20001: 部门人数不能超过6人
ORA-06512: 在 "SCOTT.EMPINSERT", line 5
ORA-04088: 触发器 'SCOTT.EMPINSERT' 执行过程中出错