oracle触发器、存储过程和函数的介绍

oracle触发器、存储过程和函数

触发器

1、触发器概念

触发器不能显示调用，只能通过事件触发，且不能接受参数。

2、触发器分类：

DML触发器 --insert update delete
instead-of（替代）触发器 --复杂视图（多张图）
DDL触发器 --数据定义语言
系统或数据库事件触发器 --数据库备份 数据库警告

3、触发器语法：

CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER] --触发时间
[INSTEAD OF]
[INSERT | UPDATE |UPDATE OF 列名称[,列名称,…] | DELETE] --触发事件
ON [表名称 | 视图 | DATABASE | SCHEMA]
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PAERNT AS 标记] ]
[FOR EACHE ROW]
[FOLLOWS 触发器名称]
[DISABLE]
[WHEN 触发条件]
[DECLARE]
[触发器声明部分;]
BEGIN
程序代码部分;
END [触发器名称];

CREATE [OR REPLACE] TRIGGER 触发器名称：创建一个触发器，设置名称，如果选择了OR REPLACE选项，则表示替换已有的触发器
[BEFORE | AFTER] ：指的是该触发器的触发时间，是在操作之前before还是在操作之后after触发。
[DISABLE]:一个触发器建立之后默认是启用状态，可以使用此选项，将其定义为禁用状态。
[WHEN 触发条件]：当满足指定条件时才执行触发器操作。
[PRAGMA AUTONOMOUS TRANSACTION:]:自治事务声明，编写此语句后会在触发器中启用一个子事务处理，并且可以使用COMMIT

4、在编写触发器过程中应该注意以下几点：

A、触发器不接收任何参数，并且只能是在产生了某一触发事件之后才会自动调用。
B、对于一张数据表的触发器，最多只能有12个。同一种类型的触发器，只能定义一次。
C、一个触发器最大为32KB，所以如果需要编写的代码较多，可以通过过程或者函数调用来完成。
D、默认情况下，触发器中不能使用事务处理操作（ROLLBACK、COMMIT等），或者采用自治事务进行处理。
E、在一张数据表中，如果定义过多的触发器，则会造成DML性能的下降。

----------------------------------------------------------表级触发器---------------------------------------------------------------
DML表级触发器

DML触发器主要由DML语句进行触发，
当用户执行了增加insert、修改update、删除delete操作时就会触发操作。

create or replace trigger 触发器名称
[before | after]
[insert | update | update of 列名称[,列名称] | delete] on 表名称
[disable]
[when 触发器条件]
[declare]
[程序声明部分;]
BEGIN
程序代码部分;
END [触发器名称];

表级触发器指的是针对全表数据的检查，每次更新数据表时，
只会在更新之前或者之后触发一次，
表级触发器不需要配置FOR EACH ROW选项。

用户权限：
CREATE ANY TRIGGER:为任意用户创建触发器的权限。
ALTER ANY TRIGGER:修改任意触发器的权限。
DROP ANY TRIGGER:删除任意触发器的权限。

例子：
只有在每个月的10日才允许办理新雇员入职与离职，其他时间不允许增加新雇员数据。
create or replace trigger emp_trigger
before insert or update on emp
declare
v_day number(2);
BEGIN
select to_number(to_char(sysdate,‘dd’)) into v_day from dual;
if v_day = 10 then
null;
else
raise_application_error(-20000,‘只有每月10号才能办理业务’);
end if;
END emp_trigger;

例子：每一位雇员都要根据其收入上缴所得税，假设所得税的上缴原则为：
2000元以下缴3%、
2000-5000元上缴8%、
5000元以上上缴10%，
现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、佣金、上缴所得税数据，
并且在每次修改雇员表中sal和comm字段后可以自动更新记录。

create or replace trigger emp_tax_trigger
after update of sal,comm on emp
declare
cursor v_emp_cursor
is
select * from emp;
v_total number(7,2);
v_tax number(7,2);
begin
delete from emp_tax;
for v_emp in v_emp_cursor loop
v_total :=v_emp.sal+nvl(v_emp.comm,0);
if v_total<2000 then
v_tax :=v_total0.03;
elsif v_total>=2000 and v_total<5000 then
v_tax :=v_total0.08;
else v_total>5000 then
v_tax :=v_total*0.1;
end if;
insert into emp_tax(empno,ename,sal,comm,tax)
values(v_emp.empno,v_emp.ename,v_emp.sal,v_emp.comm,v_tax);
end emp_tax_trigger;

----------------------------------------------------------行级触发器---------------------------------------------------------------
语法

create or replace trigger 触发器名称
[before | after]
[insert | update | update of 列名称[,列名称] | delete] on 表名称
[referencing [old as 标记] [new as 标记]]
[for each row] --行级触发器和 表级触发器的区别
[disable]
[when 触发器条件]
[declare]
[程序声明部分;]
BEGIN
程序代码部分;
END [触发器名称];

例子
增加雇员信息时，某职位必须在已有职位之内选择，并且工资不能超过5000元
create or replace trigger emp_insert_trigger
before insert on emp
for each row
declare
v_count number(1);
BEGIN
select count(*) into v_count from dual where :new.job in(select distinct job from emp);

if v_count=0 or :new.sal>5000 then
raise_application_error(-20000,‘输入信息不符合要求’);
end if;

END emp_insert_trigger;

例子
修改emp表的基本工资涨幅不能超过10%

create or replace trigger emp_insert_trigger
before insert on emp
for each row
declare

BEGIN
if abs( :new.sal - :old.sal)/(:old.sal) >0.1 then
raise_application_error(-20000,‘输入信息不符合要求’);
end if;
END emp_insert_trigger;

例子
不能删除所有部门是10的雇员

create or replace trigger emp_insert_trigger
before delete on emp
for each row
declare
BEGIN
if :old.deptno = 10 then
raise_application_error(-20000,‘输入信息不符合要求’);
end if;
END emp_insert_trigger;

create or replace trigger emp_insert_trigger
before delete on emp
referencing old as emp_old new as emp_new
for each row
when (emp_old.deptno = 10) --不要前面加:
declare
BEGIN
–if :emp_old.deptno = 10 then
raise_application_error(-20000,‘输入信息不符合要求’);
–end if;
END emp_insert_trigger;

---

----------------------------------------------------------触发器谓词---------------------------------------------------------------
INSERT INSERT触发器
UPDATE INSERT触发器
DELETE INSERT触发器
create or replace trigger dept_trigger
after delete or insert or update on dept
for each row
declare
BEGIN

if INSERTING then
dbms_output.put_line(‘INSERTING’);
end if;

if DELETING then
dbms_output.put_line(‘DELETING’);
end if;

if UPDATING then
dbms_output.put_line(‘UPDATING’);
end if;

END dept_trigger;

---

----------------------------------------------------------复合触发器---------------------------------------------------------------
复合触发器是在Oracle 11g 之后引入的一种新结构的触发器，它既是表级触发器，又是行级触发器。在之前对于不同级别的触发器，如果要写一张数据表上完成表级触发器（before和after）与行级触发器
（before和after）则需要编写4个触发器才可以，而有了复合触发器之后，只需要一个触发器就可以定义全部的4个功能，所以使用复合触发器可以捕获4个操作事件：

create or replace trigger dept_trigger
for insert | update | update of 列名称 on 表名称
compound trigger
before statement is
声明部门;
begin
程序主体部分：
end before statement;

before each row is
声明部门;
begin
程序主体部分：
end before each row;

after statement is
声明部门;
begin
程序主体部分：
end after statement;

after each row is
声明部门;
begin
程序主体部分：
end after each row;
end;

存储过程

1、存储过程概念

存储过程是一类命名的子程序，与匿名块不同他有名字
可以被其他的子程序（匿名块、存储过程）调用
可以接收参数
不必指定返回类型

2、存储过程语法

CREATE [OR REPLACE] PROCEDURE 过程名([参数名称 [参数模式] NOCOPY 数据类型])
[AUTHID[DEFINER| CURRENT_USER]]
AS|IS
[PRAGMA AUTONOMOUS TRANSACTION;]
声明部分
BEGIN
程序部分
[EXCEPTION
异常处理;]
END [过程名];

温馨：v_notfound_exception exception;
pragma exception_init(v_notfound_exception, -20000);
raise_application_error(-20000,‘员工不存在！’);
exception
when v_notfound_exception then dbms_output.put_line(sqlerrm);

when others then dbms_output.put_line(sqlerrm);
–将测试存储过程的权限赋予给用户
GRANT dubug any procedure,debug connect session TO scott;
–收回
revoke dubug any procedure,debug connect session TO scott;

3、查询存储过程
select object_name,object_type,status from user_objects(当前用户所有的对象) where object_name=‘存储过程名’;
4、重新编译存储过程
alter procedure 存储过程名 compile;
5、删除存储过程
drop procedure 存储过程名;

6、参数模式

存储过程和函数往往需要接收传递的参数，这样对参数的定义就分为了3类：
in,out,in out模式
in 模式
（默认，地址传递）：在存储过程中不能修改in类型的参数。
out 模式
（空进带值出）：不带任何数值到子程序中，子程序可以通过此变量将数值返回给调用者。
in out 模式
（值传递）：可以将值传递到子程序中，同时也会将子程序中对变量的修改返回到调用者处。
nocopy参数

在使用out和in out模式的参数，默认情况下使用的是指值传递。
就是将一个数据copy给out或者in out类型的参数。
如果拷贝的数据很大会带来性能问题。

将一个存储大量数据的嵌套表作为输入结果，拷贝给out类型的参数。

declare
type emp_nested_table is table of emp%rowtype;
v_emp_nested_table emp_nested_table;
–在匿名块声明存储过程前面不能加 CREATE OR REPLACE
procedure p_nocopy(v_emp_table in out nocopy emp_nested_table)
as
begin
dbms_output.put_line(v_emp_table.count);
end p_nocopy;

begin

select * bulk collect into v_emp_nested_table from emp;
–存储过程扩容：将第一行复制50000次
v_emp_nested_table.extend(50000,1);
dbms_output.put_line(systimestamp);
p_nocopy(v_emp_nested_table);
dbms_output.put_line(systimestamp);
end;

函数

1、函数的概念

函数是一类命名的子程序，与匿名块不同他有名字。
可以被其他的子程序（匿名块、存储过程）调用
可以接收参数
必须指定返回值类型

2、函数语法

CREATE OR REPLACE FUNCTION 函数名([参数,[参数,…]])
RETURN 返回值类型
[AUTHID{DEFINER|CURRENT_USER}]
AS|IS
[PRAGMA AUTONOMOUS TRANSACTION;]
声明部分
BEGIN
程序部分
[RETURN 返回值;]
[EXCEPTION
异常处理]
END[函数名];

3、根据员工的编号查询每个员工的工资
CREATE OR REPLACE FUNCTION GET_SAL(V_EMPNO EMP.EMPNO%TYPE)
RETURN EMP.SAL%TYPE
AUTHID DEFINER
AS
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL+NAL(COMM,0) INTO V_SAL FROM EMP WHERE EMPNO=V_EMPNO;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN V_SAL;
END GET_SAL;

4、调用函数
SELECT EMPNO,ENAME,GET_SAL(EMPNO) FROM EMP;
或者
DECLARE
V_SAL EMP.SAL%TYPE;
BEGIN
V_SAL:=GET_SAL(7788);
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
5、删除函数
DROP FUNCTION GET_SAL;