游标和触发器

最新推荐文章于 2024-04-07 21:26:07 发布

眼錑

最新推荐文章于 2024-04-07 21:26:07 发布

阅读量103

点赞数

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

显式游标则用于处理SELECT语句返回的多行数据；人为创建，干预
隐式游标用于处理SELECT INTO和DML语句；系统自动创建，管理

常用属性
%ISOPEN
用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回 FALSE
%FOUND
检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE
%NOTFOUND
与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE
%ROWCOUNT
返回到当前行为止已经提取到的实际行数

显示游标格式：

定义游标
CURSOR cursor_name IS select_SQL;
2. 打开游标
OPEN cursor_name;
3. 提取数据
FETCH cursor_name INTO variable1,variable2,…;
关闭游标
CLOSE cursor_name;

示例：
DECLARE
–定义一个游标

 CURSOR C_CURSOR IS
   SELECT DEPTNO,DNAME FROM DEPT

;
–定义收参的变量

V_DEPTNO    DEPT.DEPTNO%TYPE;
   V_DNAME     DEPT.DNAME%TYPE

BEGIN
–打开游标
OPEN C_CURSOR;
–loop 循环获取值

LOOP
     FETCH C_CURSOR INTO V_DEPTNO,V_DNAME;
     EXIT WHEN C_CURSOR%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'---'||V_DNAME);
   END LOOP

-关闭游标

CLOSE c_cursor;
END;

示例：
DECLARE
–定义一个游标

CURSOR C_CURSOR IS
   SELECT DEPTNO,DNAME FROM DEPT;

–定义收参的变量

 -V_VAL    C_CURSOR%ROWTYPE;
    BEGIN

– 隐含打开游标 open
FOR V_VAL IN C_CURSOR LOOP
– 隐含执行 fetch

DBMS_OUTPUT.PUT_LINE(V_VAL.deptno || ' '|| V_VAL.dname);

– 隐含执行 %NOTFOUND
END LOOP;
– 隐含关闭游标 close
END;

游标传参
DECLARE
– 定义游标时，指定参数的类型

CURSOR C_CURSOR(param NUMBER) IS
   select EMPNO,ENAME,JOB from emp where deptno=param;
  V_EMPNO NUMBER;
  V_ENAME  VARCHAR2(32);
  V_JOB    VARCHAR2(32);
BEGIN

– 打开游标，同时传入参数

OPEN C_CURSOR(20);
  LOOP
    FETCH C_CURSOR INTO V_EMPNO,V_ENAME,V_JOB;
    EXIT WHEN C_CURSOR%NOTFOUND ;
    DBMS_OUTPUT.put_line(V_EMPNO ||' '|| V_ENAME ||' '|| V_JOB);
  END LOOP;
  CLOSE C_CURSOR;
END;

隐含游标，当执行一条DML语句或者SELECT…INTO语句时，都会创建一个隐含游标

隐含游标的名称是SQL，不能对SQL游标显式执行OPEN、FETCH和CLOSE语句。
Oracle隐式地打开、提取，并总是自动地关闭SQL游标
隐含游标的属性
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

示例

declare
  v_empno number(4):=7369;
begin
    delete from emp where empno=v_empno;
    if sql%found then
        dbms_output.put_line('存在该员工！');
    else
        dbms_output.put_line('不存在该员工！');
    end if;
end;

初始触发器
触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码
触发器的类型
1、DML触发器
在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
2、替代触发器
是oracle8专门为进行视图操作的一种触发器
3、系统触发器
对数据库系统事件进行触发，如启动、关闭等

注意事项：

触发器不接受参数。
一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

表头复制

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

DML触发器
删除示例：

CREATE OR REPLACE TRIGGER tr_del_dept  
    BEFORE DELETE --指定触发时机为删除操作前触发 
     ON dept
     FOR EACH ROW   --说明创建的是行级触发器  
 BEGIN

–将修改前数据插入到日志记录表 del_emp ,以供监督使用

 INSERT INTO dept_log VALUES( :old.deptno, :old.dname, :old.loc); 
 END

修改示例

CREATE OR REPLACE TRIGGER  tr_update_dept
    after update
    on dept
    for each row
BEGIN
  INSERT INTO dept_log VALUES( :new.deptno, :new.dname, :new.loc);
END;

**系统触发器

创建日志记录表**

create table log_event(
  log_type varchar2(20),
  username varchar2(20),
  logonTime timestamp,
  logoffTime timestamp
);

登录触发器

create or replace trigger logon_trigger
after logon on database
begin
  insert into log_event(log_type,username,logonTime)
  values('logon',ora_login_user,systimestamp);
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,systimestamp);
end;