游标和触发器

第八章  游标和触发器

 

初识游标

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

       定位行，逐行跳动

游标分类

1. 显式 游标用于处理SELECT语句返回的多行数据；人为创建，干预

2. 隐式 游标用于处理SELECT INTO和非查询的DML语句；系统自动创建，管理

   

常用属性   CURSOR

%ISOPEN

 用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE

  %FOUND

 检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE

  %NOTFOUND

 与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE

 %ROWCOUNT

 返回到当前行为止已经提取到的实际行数

 

显示游标格式：

1. 定义游标

    CURSOR cursor_name IS select_SQL;

    2. 打开游标

    OPEN cursor_name;

    3. 提取数据

FETCH cursor_name INTO variable1,variable2,...;

当运行fetch后，游标才开始工作，记录当前行，有数据则下移，然后置%FOUND,%NOTFOUND的值

4. 关闭游标

CLOSE cursor_name;

 

显示游标：

 

游标动态传参，显示游标

 

隐式for循环游标：

 

隐含游标，当执行一条非查询的DML语句或者SELECT...INTO语句时，都会创建一个隐含游标

1. 隐含游标的名称是SQL，不能对SQL游标显式执行OPEN、FETCH和CLOSE语句。

2. Oracle隐式地打开、提取，并总是自动地关闭SQL游标

3. 隐含游标的属性

    SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

 

示例

declare

  v_stuId number := "&input_stuId";

begin

  delete from student where stu_id=v_stuId;

  if sql%found then

    DBMS_OUTPUT.PUT_LINE("找到员工");

  else

    DBMS_OUTPUT.PUT_LINE("未找到员工");

  end if;

end;

 

初始触发器

触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发

当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码

触发器的类型

1、DML触发器

在对数据库表进行DML（insert,update,delete）操作时触发，并且可以对每行或者语句操作上进行触发。

2、替代触发器

是oracle8专门为进行视图操作的一种触发器

3、系统触发器

对数据库系统事件进行触发，如启动、关闭等

 

注意事项：

1. 触发器不接受参数。

2. 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

3. 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

4. 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

5. 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

 

 

表头复制

CREATE TABLE dept_log AS SELECT * FROM dept WHERE 1=2;

 

DML触发器

删除示例：

create or replace trigger tr_del_student

       --指定触发时机为删除操作前触发

       before delete

       on student

       --说明创建的是行级触发器 

       for each row

begin

      --将修改前数据插入到日志记录表,要确保SQL可以正常工作

      insert into student_log values(:old.stu_id,:old.stu_name,:old.stu_age,:old.stu_sex,:old.stu_class,:old.stu_create_date);

end;

 

 

 

修改示例

create or replace trigger tr_update_student

       --指定触发时机为修改操作后触发

       after update

       on student

       for each row

begin

       -- 记录修改后的值

      insert into student_log

values(:new.stu_id,:new.stu_name,:new.stu_age,:new.stu_sex,:new.stu_class,:new.stu_create_date);

end;

 

 

 

删除触发器

drop trigger trigger_name

 

 

系统触发器

 

 

 

创建日志记录表

create table log_event(

  log_type varchar2(20),

  username varchar2(20),

  logonTime date,

  logoffTime date

);

登录触发器

create or replace trigger logon_trigger

after logon on database

begin

  insert into log_event(log_type,username,logonTime)

  values("logon",ora_login_user,sysdate);

end;

 

登出触发器

create or replace trigger logoff_trigger

before logoff on database

begin

  insert into log_event(log_type,username,logoffTime)

  values("logoff",ora_login_user,sysdate);

end;