ORACLE学习资料第五章

第五章游标和触发器游标：隐式游标：%FOUND,%NOTFOUND,%ROWCOUNT1．%FOUND用法，只有在DML语句影响一行或者多行时，%FOUND属性才返回TRUE。下列示例演示了%FOUND的用法：beginupdateemployees2setfirst_name=first_name||'t'whereemployee_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_cursorisselectsalaryfromemployees2wheresalary300000;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%foundloopdbms_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*fromemployees2wheredivision_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='jeff'wherecurrentofemployees2_cursor;endloop;closeemployees2_cursor;commit;end;/触发器：触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器语法：CREATE[ORREPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOREACHROW][WHEN(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='scott'whereemployee_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触发器是在视图上而不是在表上定义的触发器，它是用来替换所使用实际语句的触发器。以下代码创建了视图：createviewemployee_jobasselecte.job_id,e.employee_id,e.first_name,e.last_name,j.namefromemployees2e,jobsjwheree.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_obj(obj_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_