oracle11g游标及触发器相关知识

oracle11g 游标：

1. 当在PL/SQL中使用SQL语句时，Oracle会为其分配上下文区域，这是一段

私有的内存区域，用于暂时保存SQL语句影响到的数据。游标是指向这段内存区域的指针。

2. Oracle中主要有两种类型的游标:

(1) 隐式游标:所有的DML语句和PL/SQL SELECT 语句都有;

(2) 显式游标:由开发人员声明和控制。

3. 可以使用的游标属性包括四种：%ROWCOUNT、%FOUND、%NOTFOUND、

%ISOPEN，这四种属性对于显式游标和隐式游标都有用，但是含义和使用方法略有不同。游标在使用属性时，需要以游标名称作为前缀，以表明该属性是哪个游标的，隐式游标没有名称，所以在使用隐式游标时采取了统一的一个名称SQL。

4. 在PL/SQL中的SELECT语句只能且必须取出一行数据，取出多行或者零行都

被认为是异常，所以在对多行数据进行操作时，必须使用显式游标来实现。

5. 使用显式游标的步骤：

(1)声明游标：CURSOR cursor_name is select_statement;

(2)打开游标：OPEN cursor_name;

(3)取游标中的数据：FETCH cursor_name INTO variable1,variable2,...;

(4)关闭游标：CLOSE cursor_name;

6.用变量接收游标中的数据

sql> declare

v_name emp.ename%TYPE;

v_sal emp.sal%TYPE;

cursor emp_cursor is select ename,sal from emp

where deptno=10;

begin

open emp_cursor;

loop

fetch emp_cursor into v_name,v_sal;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line(v_name || ‘的薪水是’ || v_sal);

end loop;

dbms_output.put_line(‘共取出了’ || emp_cursor%ROWCOUNT || ‘条记录’);

close emp_cursor;

end;

7.通常简单LOOP循环与%NOTFOUND属性配合使用，而WHILE循环与%FOUND属性配合使用。

8.使用记录接收游标中的数据

sql> declare

cursor emp_cursor is select ename, sal from emp

where deptno=10;

--注意创建记录类型的方式

emp_record emp_cursor%ROWTYPE;

begin

open emp_cursor;

loop

fetch emp_cursor into v_name,v_sal;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line(v_name || ‘的薪水是’ || v_sal);

end loop;

dbms_output.put_line(‘共取出了’ || emp_cursor%ROWCOUNT || ‘条记录’);

close emp_cursor;

end;

9.带有参数的游标的语法:

CURSOR cursor_name

[(parameter_name datatype,...)]

IS

select_statement;

10.sql> declare

cursor emp_cursor(v_deptno NUMBER) is

select ename,sal from emp where deptno=v_deptno;

emp_record emp_cursor%ROWTYPE;

begin

open emp_cursor(20);

loop

fetch emp_cursor into emp_record;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line(emp_https://www.360docs.net/doc/1610797195.html, || ‘的薪水是’ || emp_cursor.sal);

end loop;

dbms_output.put_line(‘共取出了’ || emp_cursor%ROWCOUNT || ‘条记录’);

close emp_cursor;

end;

11.游标变量：也叫做动态游标，通过REF CURSOR方式定义，它仍然是指向一段SQL语句的内存地址的指针。和动态游标相比，之前在声明时就定义好SELECT语句的游标称作静态游标，而动态游标在打开时才指定其所对应的SELECT语句。12.sql> declare

--定义游标变量类型

type dept_cursor_type is ref cursor

return dept%ROWTYPE

--定义游标变量

dept_cursor dept_cursor_type;

--定义记录类型

dept_record dept%ROWTYPE;

begin

open dept_cursor for select deptno,dname,loc

from dept;

fetch dept_cursor into dept_record;

while dept_cursor%FOUND loop

dbms_output.put_line(dept_record.deptno||’,’||dept_record.dname||’,’||dept_record.loc);

fetch dept_cursor into dept_record;

end loop;

dbms_output.put_line(‘GAME OVER’);

end;

以上定义的游标变量中指定了返回类型，也就是说，游标对应的结果集必须按照返回类型的规定来定义，这种游标称作强类型游标变量，除此之外还有一种弱类型游标变量。

13.游标的FOR循环：

for record_name in cursor_name loop

statement1;

statement2;

…

end loop;

14.declare

cursor emp_cursor(v_deptno NUMBER) is

select ename,sal from emp where deptno=v_deptno;

begin

for emp_record in emp_cursor(20) loop

dbms_output.put_line(emp_record.ename||’的薪水是’||emp_record.sal); end loop;

end;

15.cursor cursor_name is select_statement

for update [of column_reference][nowait]

在游标结果集上加锁，NOWAIT子句指定不等待锁，也就是如果要加锁的数据已经被锁定，则不等待直接返回。

将显示游标行中的当前行修改或者删除，更新或删除的语法如下：

update table_name set column=..

where current of cursor_name;

delete from table_name where current of cursor_name;

oracle11g 触发器：

1. PL/SQL程序中的触发器的结构类似于函数和过程，同样是具有声明部分、

执行部分和异常处理部分的命名PL/SQL块。但与过程和函数不同的是，触发器是在事件发生时隐式地运行的，并且触发器不能接收参数，而过程和函数都是被用户显式的调用的。

2. 运行触发器的方式叫触动，或者点火（触发），是指在指定的事件发生的时

候（前或者后）自动运行定义的PL/SQL块。

3. 在ORACLE数据库中主要有三种触发器类型：

（1） DML触发器，由表上执行的INSERT、UPDATE、DELETE操作触发；

（2） INSTEAD OF替代触发器，用于视图的操作；

（3）系统触发器，用于系统事件触发。

4. 触发器的触发范围又分为行级触发器和语句级触发器。

5. 在Oracle数据库中,触发器主要用于下列情况:

(1) 安全性方面,确定用户的操作是否可以继续进行;

(2) 产生对数据值修改的审计,将修改的信息记录下来,产生数据改动记录;

(3) 提供更灵活的完整性校验规则,能够根据更复杂的规则校验数据;

(4) 提供表数据的同步复制,使多个表的数据达到同步;

(5) 事件日志记录,记录数据库的重要操作信息。

6. 创建语句级触发器的语法格式：

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [OR event2 OR event3]

ON table_name

when conditions

trigger_body

7. SQL>CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON emp

BEGIN

IF(TO_CHAR(SYSDATE,’DY’) IN (‘星期六’,’星期日’)) OR (TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘8:00’ AND ’18:00’) THEN

RAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行插入操作！’)；

END IF；

END；

8. RAISE_APPLICATION_ERROR（error_number_in IN NUMBER,error_msg_in IN

VARCHAR2）;（error_number_in的取值范围是-20000—20999之间，error_msg_in的长度不能超过2K，否则截取前面的2K），是一个系统存储过程，作用是将应用程序的错误从服务器端传递到客户端应用程序。

9. SQL> SQL>CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT OR UPDATE OR DELETE ON emp

BEGIN

IF(TO_CHAR(SYSDATE,’DY’) IN (‘星期六’,’星期日’)) OR (TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘8:00’ AND ’18:00’) THEN

IF DELETING THEN

RAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行删除操作！’)；

ELSEIF INSERTING THEN

RAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行插入操作！’)；

ELSEIF UPDATING(‘SAL’) THEN

RAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执

行更新操作！’)；

END IF；

END IF；

END；

10. 创建行级触发器的语法格式：

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [OR event2 OR event3]

ON table_name

[REFERENCING OLD AS old|NEW AS new]

FOR EACH ROW

[WHEN (condition)]

Trigger_body

其中REFERENCING子句是声明触发器替换前后的引导前缀名的，默认替换前的前缀名为OLD，替换后的前缀名为NEW，也可以自己声明替换前后的变量前缀规则。

11. SQL> CREATE OR REPLACE TRIGGER restrict_salary

BEFORE INSERT OR UPDATE OF sal ON emp

FOR EACH ROW

BEGIN

IF NOT (:NEW.job IN(‘MANAGER’,’PRESIDENT’))

AND :NEW.sal>15000

THEN

RAISE_APPLICATION_ERROR

(-20202,’员工不能赚到这么多薪水’);

12.—注意在头定义中，NEW和OLD不需要加冒号前缀

SQL>create or replace trigger check_salary

before update of sal on emp

for each now

when(new.sal

begin

raise_application_error(-20508,’不允许降低薪水’);

end;

12. 对于简单视图来说，可以通过更新视图来更新基表，对于复杂视图来说，

无法通过视图更新基表，当需要通过视图影响它所对应的基表时，可能通过替代触发器来实现，当执行视图的更新时，将会触发替代触发器。

13. 创建视图上的INSTEAD OF触发器的语法格式：

SQL> CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

event1 [OR event2 OR event3]

ON view_name

[REFERENCING OLD AS old|NEW AS new]

FOR EACH ROW

[WHEN (condition)]

Trigger_body

14. sql>create or replace view emp_view

as

select

deptno,sum(sal),sum_sal,count(*) count_num

from emp

group by deptno;

sql> delete from emp_view where deptno=10;

sql> select * from emp_view;

sql>create or replace trigger emp_view_delete

instead of delete on emp_view

for each row

begin

delete from emp where deptno=:old.deptno;

end;

sql> delete from emp_view where deptno=10;

sql> select * from emp_view;

sql> select * from emp where deptno=10;

15.系统级触发器监听的事件主要分成两种：用户触发事件和系统触发事件。用户触发事件主要包括：

(1)CREATE，ALTER或者DROP命令等DDL命令；

(2)登录或者退出数据库连接。

系统触发事件包括：

(1) 启动、关闭数据库；

(2) 特殊错误发生。

15. 创建DDL触发器的语法：

CREATE [OR REPLACE] TRIGGER trigger_name

timing

[dd1_event1 [OR dd1_event2 OR…]]

ON {DATABASE|SCHEMA}

Trigger_body

16. sql> conn sys/admin as sysdba

sql> create sequence event_seq;

sql> create table event_log(

eventide number primary key,

eventname varchar2(20),

obj_type varchar2(20),

obj_name varchar2(20),

obj_owner varchar2(20),

occurtime date default sysdate);

sql> create or replace trigger trig4dd1

after create or alter or drop on database

begin

insert into sys.event_log(

eventide,eventname,obj_type,old_name,

obj_owner)

values(event_seq.nextval,

sysevent,

dictionary_obj_type,

dictionary_obj_name,

dictionary_obj_owner);

end;

sql> conn scott/tiger

sql> drop table temp;

sql> create view view_emp10

as

select empno,ename,job,sal

from emp where deptno=10;

sql> conn sys/admin as sysdba

sql> select * from event_log;

17. create or replace trigger trigger_name

timing

database_event1 [or database_event2 or …]]

on {database|schema}

trigger_body

18. sql> conn sys/admin as sysdba

sql> create table event(

eventuser varchar2(20),

action varchar2(20),

logtime date default sysdate);

sql> --记录用户的登入

create or replace trigger trig4_logon

after logon on database

begin

insert into sys.event(eventuser,action)

values(user,’log on’);

end;

sql> --记录用户的登出

create or replace trigger trig4_logoff

after logoff on database

begin

insert into sys.event(eventuser,action)

values(user,’log off’);

end;

sql> conn scott/tiger

sql> conn hr/hr

sql> conn sys/admin as sysdba

sql> select * from event;

19. 在触发器中调用过程或者函数：CALL procedure_name;注意这种方式创建触发器，不需要再写BEGIN和END，程序末尾也不需要写分号“;”来表示结束。

20. sql> select object_name from user_objects where

object_type=’TRIGGER’;

sql> select trigger_name,status,table_name from

user_triggers;

sql>--定义LONG类型的显示长度

set long 1000

sql> select trigger_body from user_triggers

where trigger_name=’RESTRICT_SALARY’;

21. 触发器的执行顺序：

(1) 执行所有的BEFORE STATEMENT触发器；

(2) 根据操作的行循环执行：

A．所有的BEFORE ROW 触发器；

B．所有的AFTER ROW 触发器；

(3) 执行DML语句，进行完整性约束条件检测；

(4) 执行所有的ALTER STATEMENT触发器。

22. 启用或者禁用某个触发器的语法：

alter trigger trigger_name disable|enable;

启用或者禁用某个对象上的所有触发器：

alter table table_name disable|enable

all triggers;

23. 就像其他存储程序一样，触发器有时候也会失效，比如触发器中引用的表

被意外删掉，对于失效的触发器，当失效条件被修正，比如表被重新建立起来，应该重新使它生效，否则触发触发器时可能出错，重新使触发器生效的方法就是重新编译触发器，语法如下：

alter trigger trigger_name compile;

24.删除触发器：drop trigger trigger_name;