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> declarev_name emp.ename%TYPE;v_sal emp.sal%TYPE;cursor emp_cursor is select ename,sal from empwhere deptno=10;beginopen emp_cursor;loopfetch 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> declarecursor emp_cursor is select ename, sal from empwhere deptno=10;--注意创建记录类型的方式emp_record emp_cursor%ROWTYPE;beginopen emp_cursor;loopfetch 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,...)]ISselect_statement;10.sql> declarecursor emp_cursor(v_deptno NUMBER) isselect ename,sal from emp where deptno=v_deptno;emp_record emp_cursor%ROWTYPE;beginopen emp_cursor(20);loopfetch emp_cursor into emp_record;exit when emp_cursor%NOTFOUND;dbms_output.put_line(emp_ || ‘的薪水是’ || 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 cursorreturn dept%ROWTYPE--定义游标变量dept_cursor dept_cursor_type;--定义记录类型dept_record dept%ROWTYPE;beginopen dept_cursor for select deptno,dname,locfrom dept;fetch dept_cursor into dept_record;while dept_cursor%FOUND loopdbms_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 loopstatement1;statement2;…end loop;14.declarecursor emp_cursor(v_deptno NUMBER) isselect ename,sal from emp where deptno=v_deptno;beginfor emp_record in emp_cursor(20) loopdbms_output.put_line(emp_record.ename||’的薪水是’||emp_record.sal); end loop;end;15.cursor cursor_name is select_statementfor 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_nametimingevent1 [OR event2 OR event3]ON table_namewhen conditionstrigger_body7. SQL>CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT ON empBEGINIF(TO_CHAR(SYSDATE,’DY’) IN (‘星期六’,’星期日’)) OR (TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘8:00’ AND ’18:00’) THENRAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行插入操作！’)；END IF；END；8. RAISE_APPLICATION_ERROR（error_number_in IN NUMBER,error_msg_in INVARCHAR2）;（error_number_in的取值范围是-20000—20999之间，error_msg_in的长度不能超过2K，否则截取前面的2K），是一个系统存储过程，作用是将应用程序的错误从服务器端传递到客户端应用程序。

9. SQL> SQL>CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT OR UPDATE OR DELETE ON empBEGINIF(TO_CHAR(SYSDATE,’DY’) IN (‘星期六’,’星期日’)) OR (TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘8:00’ AND ’18:00’) THENIF DELETING THENRAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行删除操作！’)；ELSEIF INSERTING THENRAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行插入操作！’)；ELSEIF UPDATING(‘SAL’) THENRAISE_APPLICATION_ERROR(-20500,’你只有在工作时间对EMP表执行更新操作！’)；END IF；END IF；END；10. 创建行级触发器的语法格式：CREATE [OR REPLACE] TRIGGER trigger_nametimingevent1 [OR event2 OR event3]ON table_name[REFERENCING OLD AS old|NEW AS new]FOR EACH ROW[WHEN (condition)]Trigger_body其中REFERENCING子句是声明触发器替换前后的引导前缀名的，默认替换前的前缀名为OLD，替换后的前缀名为NEW，也可以自己声明替换前后的变量前缀规则。