ORACLE学习资料第五章

第五章游标和触发器游标：隐式游标： %FOUND,%NOTFOUND,%ROWCOUNT1 %FOUND 用法，只有在DML 语句影响一行或者多行时， %FOUND 属性才返回TRUE。下列示例演示了 FOUND的用法：beginupdateemployees2setfirst_name=first_name|twhereemployee_id=2;ifSQL%foundthendbms_output.put_line（ 数据已经更新）;-dbms_output.put_line（rowCount=|mrowcount）;elsedbms_output.put_line（ 数据没有找到 ）;endif;end;/以下代码演示了创建了一个游标，返回 employees2 表中 salary 大于 300000 的记录，注意 type 的使用：declarecsalaryemployees2.salary%type;cursoremp2_cursorisselectsalaryfromemployees2wheresalary30000 0;beginopenemp2_cursor;loopfetchemp2_cursorintocsalary;exitwhenemp2_cursor%notfound;dbms_output.put_line(csalary=|csalary);endloop;end;/以下代码演示了创建了一个游标，返回 employees2 表中 division_id= SAL 的记录。注意 rowtype 的使用：declarecursoremployee2_cursorisselect*fromemployees2wheredivision_id=SAL;myrecordemployees2%rowtype;beginopenemployee2_cursor;fetchemployee2_cursorintomyrecord;whileemployee2_cursor%foundloop dbms_output.put_line(employeeid=|myrecord.employee_id); dbms_output.put_line(firstName=|myrecord.first_name);dbms_output.put_line(lastname=|myrecord.last_name);fetchemployee2_cursorintomyrecord;endloop;end;/以下代码演示了带参数的游标，根据divisionid 查询指定的记录：declaremyrecordemployees2%rowtype;cursoremp_cursor(divisionidvarchar2)isselect*fromemployees2wheredivi sion_id=divisionid;beginopenemp_cursor(&divisionid);-loopfetchemp_cursorintomyrecord;whileemp_cursor%foundloop-exitwhenemp_cursor%notfound;dbms_output.put_line(employeeid=|myrecord.employee_id);dbms_output.put_line(divisionid=|myrecord.division_id);dbms_output.put_line(firstname=|myrecord.first_name);fetchemp_cursorintomyrecord;endloop;closeemp_cursor;end;/以下代码演示了如何更新employees2 表中的 first_name 字段：setserveroutputondeclarefirstNamevarchar2(20);cursoremployees2_cursorisselectfirst_namefromemployees2whereemployee_id=1forupdateoffirst_name;beginopenemployees2_cursor;loopfetchemployees2_cursorintofirstName;exitwhenemployees2_cursor%notfound;updateemployees2setfirst_Name=jeffwherecurrentofemployees2_cursor;endloop;closeemployees2_cursor;commit;end;/触发器：触发器是当特定事件出现时自动执行的存储过程 特定事件可以是执行更新的 DML 语句和 DDL 语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器语法：CREATEORREPLACETRIGGERtrigger_nameAFTER|BEFORE|INSTEADOFINSERTORUPDATEOFcolumn_listORDELETEONtable_or_view_nameREFERENCINGOLDASold/NEWASnewFOREACHROWWHEN(condition)pl/sql_block;创建触发器， 以下代码演示了插入或者修改employees2 表中的 first_name 如果等于 scott 时触发器就会执行：createorreplacetriggertri_employees2beforeinsertorupdateoffirst_nameonemployees2referencingNEWasnewdataOLDasolddataforeachrowwhen(newdata.first_name=scott)begin:newdata.salary:=20000;dbms_output.put_line(new.salary:|:newdata.salary);dbms_output.put_line(old.salary:|:olddata.salary);end;执行以上触发器：insertintoemployees2values(38,SUP,WOR,scott,mp,50000);或者：updateemployees2setsalary=90000,first_name=scottwhereempl oyee_id=38;以下代码针对数据完整性进行操作：删除操作：createorreplacetriggerdel_deptidafterdeleteondeptforeachrowbegindeletefromemployeewheredeptid=:old.id;enddel_deptid;执行以上触发器：deletefromdeptwhereid=1; 查看 employee 表中的 deptid 记录 ;添加操作：createorreplacetriggerinsert_deptafterinsertondeptforeachrowbegininsertintoemployee(id,name,deptid)values(6,chenmp,:new.id);end;/执行以上触发器：insertintodeptvalues(6, 销售部门 );查看 employee 表中的 deptid 记录修改操作：createorreplacetriggerupdate_deptafterupdateondeptforeachrowbeginupdateemployeesetdeptid=:new.idwheredeptid=:old.id;end;/执行以上触发器：updatedeptsetid=8whereid=1;查看 employee 表中的 deptid 记录以下代码演示了行级触发器：创建表：droptablerowtable;createtablerowtable(idnumber(8),namevarchar2(100);创建序列createsequencerowtablesequence;创建触发器：createorreplacetriggerset_sequencebeforeinsertonrowtableforeachrowdeclarersequencenumber(8);beginselectrowtablesequence.nextvalintorsequencefromdual;:NEW.id:=rsequence;end;/执行 SQL 语句：insertintorowtablevalues(232,scott);创建表：createtablemylog(curr_uservarchar2(100),curr_datedate,operavarchar2(10);创建触发createorreplacetriggertri_mylogafterinsertordeleteorupdateonemployees2beginifinsertingtheninsertintomylogvalues(user,sysdate,insert);elsifdeletingtheninsertintomylogvalues(user,sysdate,delete);elseinsertintomylogvalues(user,sysdate,update);endif;end;/INSTEADOF 触发器INSTEADOF 触发器是在视图上而不是在表上定义的触发器， 它是用来替换所使用实际语句的触发器。以下代码创建了视图：createview employee_job asselect e.job_id,e.employee_id,e.first_name,e.last_name,j. namefrom employees2e,jobsj where e.job_id=j.job_id;以下代码创建INSTEADOF 触发器。createorreplacetriggertri_viewinsteadofinsertonemployee_jobforeachrowbegininsertintojobsvalues(:new.job_id,:new.name);insertintoemployees2(employee_id,first_name,last_name,job_id)values(:new.employee_id,:new.first_name,:new.last_name,:new.job_id);end;/执行以下语句查看操作：insertintoemployee_jobvalues(OTH,43,abc,dd,OTHER);模式触发器：可以在模式级的操作上建立触发器，如：create,alter,drop,grant,revoke 和 truncate 等 DDL 语句：以下示例对用户所删除的所有对象进行日志记录。1 创建数据库表：droptabledropped_obj;CREATETABLEdropped_objobj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);2 创建触发器：CREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/3 创建和删除对象：创建对象：CREATETABLEfor_drop(xCHAR);删除对象：DROPTABLEfor_drop;4查看日志表中的信息：SELECT*FROMdropped_obj;起用和禁用触发器：以下代码演示了禁用biu_emp_deptno触发器：ALTERTRIGGERbiu_emp_deptnoDISABLE;以下代码演示了启用biu_emp_deptno触发器：ALTERTRIGGERbiu_emp_deptnoenable;可以使用：Altertabletable_nameenable|disablealltriggers;禁用或者起用在特定表上建立的所有触发器。删除触发器：Droptriggertrigger_name;查看触发器信息，可以使用 user_trigers 数据字典视图。Descuser_triggers