Oracle讲义第07章(游标与触发器)

第七章游标管理与触发器1目标掌握游标管理技巧理解和应用触发器2游标简介2-1Oracle服务器执行PL/SQL程序内存单元保存到游标中一次处理一行检索行提取行3游标简介2-2逐行处理查询结果，以编程的方式访问数据游标的类型：隐式游标REF游标显式游标在PL/SQL程序中执行DMLSQL语句时自动创立隐式游标。显式游标用于处理返回多行的查询。REF游标用于处理运行时才能确定的动态SQL查询的结果游标类型4隐式游标4-1在PL/SQL中使用DML语句时自动创立隐式游标隐式游标自动声明、翻开和关闭，其名为SQL通过检查隐式游标的属性可以获得最近执行的DML语句的信息隐式游标的属性有：%FOUND–SQL语句影响了一行或多行时为TRUE%NOTFOUND–SQL语句没有影响任何行时为TRUE%ROWCOUNT–SQL语句影响的行数%ISOPEN-游标是否翻开，始终为FALSE5隐式游标4-2SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEtoysSETtoyprice=270 WHEREtoyid='P005'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE(‘表已更新'); ENDIF;END; /只有在DML语句影响一行或多行时，才返回True6隐式游标4-3SQL>SETSERVEROUTPUTONSQL>DECLARE v_TOYIDTOYS.ID%type:='&TOYID'; v_TOYNAMETOYS.NAME%Type:='&TOYNAME';BEGIN UPDATETOYSSETNAME=v_TOYNAME WHEREtoyid=v_TOYID; IFSQL%NOTFOUNDTHEN DBMS_OUTPUT.PUT_LINE('编号未找到。'); ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新'); ENDIF;END;/如果DML语句不影响任何行，那么返回True7隐式游标4-4SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEvendor_master SETvenname='RobMathew' WHEREvencode='V004'; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);END;/返回DML语句影响的行数8SELECTINTO语句2-1SQL>SETSERVEROUTPUTONSQL>DECLARE empidVARCHAR2(10); desigVARCHAR2(10);BEGIN empid:='&Employeeid'; SELECTdesignationINTOdesig FROMemployeeWHEREempno=empid;EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('职员未找到');END;/如果没有与SELECTINTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常9SELECTINTO语句2-2SQL>SETSERVEROUTPUTONSQL>DECLARE empidVARCHAR2(10);BEGIN SELECTempnoINTOempidFROMemployee;EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('该查询提取多行');END;/如果SELECTINTO语句返回多个值，将引发TOO_MANY_ROWS异常10显式游标2-1显式游标在PL/SQL块的声明局部定义查询，该查询可以返回多行显式游标的操作过程：数据库翻开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量关闭游标11显式游标2-2SQL>SETSERVEROUTPUTONSQL>DECLAREmy_toy_pricetoys.toyprice%TYPE; CURSORtoy_curIS SELECTtoypriceFROMtoys WHEREtoyprice<250;BEGIN OPENtoy_cur; LOOP FETCHtoy_curINTOmy_toy_price; EXITWHENtoy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('TOYPRICE=:玩具单价=：'||my_toy_price); ENDLOOP; CLOSEtoy_cur;END;声明游标翻开游标提取行关闭游标12带参数的显式游标声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下：

CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;SQL>SETSERVEROUTPUTONSQL>DECLARE desigVARCHAR2(20); emp_codeVARCHAR2(5); empnmVARCHAR2(20); CURSORemp_cur(desigparamVARCHAR2)IS SELECTempno,enameFROMemployee WHEREdesignation=desig;BEGIN desig:='&desig'; OPENemp_cur(desig); LOOP FETCHemp_curINTOemp_code,empnm; EXITWHENemp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm); ENDLOOP; CLOSEemp_cur;END;13使用显式游标更新行2-1允许使用游标删除或更新活动集中的行声明游标时必须使用SELECT…FORUPDATE语句

CURSOR<cursor_name>IS SELECTstatementFORUPDATE; UPDATE<table_name> SET<set_clause> WHERECURRENTOF<cursor_name>更新的语法DELETEFROM<table_name>WHERECURRENTOF<cursor_name>删除的语法14使用显式游标更新行2-2SQL>SETSERVEROUTPUTONSQL>DECLAREnew_priceNUMBER;CURSORcur_toyISSELECTtoypriceFROMtoysWHEREtoyprice<100FORUPDATEOFtoyprice;BEGINOPENcur_toy;LOOPFETCHcur_toyINTOnew_price;EXITWHENcur_toy%NOTFOUND;UPDATEtoysSETtoyprice=1.1*new_priceWHERECURRENTOFcur_toy;ENDLOOP;CLOSEcur_toy;COMMIT;END;15循环游标2-1循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;16循环游标2-2SQL>SETSERVEROUTPUTONSQL>DECLARE CURSORmytoy_curIS SELECTtoyid,toyname,toyprice FROMtoys;BEGIN FORtoy_recINmytoy_cur LOOP DBMS_OUTPUT.PUT_LINE(‘玩具编号：'||''||toy_rec.toyid||''||‘玩具名称：'||''||toy_rec.toyname||''||‘玩具单价：'||''||toy_rec.toyprice); ENDLOOP;END;17REF游标和游标变量3-1REF游标和游标变量用于处理运行时动态执行的SQL查询创立游标变量需要两个步骤：声明REF游标类型声明REF游标类型的变量用于声明REF游标类型的语法为： TYPE<ref_cursor_name>ISREFCURSOR [RETURN<return_type>];18REF游标和游标变量3-2TYPEmy_curtypeISREFCURSORRETURNstud_det%ROWTYPE;order_curmy_curtype;声明强类型的REF游标翻开游标变量的语法如下： OPENcursor_nameFORselect_statement;TYPEmy_ctypeISREFCURSOR;stud_curmy_ctype;声明弱类型的REF游标19REF游标和游标变量3-3SQL>DECLARE TYPEtoys_curtypeISREFCURSORRETURNtoys%ROWTYPE; toys_curvartoys_curtype; toys_rectoys%ROWTYPE;BEGIN OPENtoys_curvarFOR SELECT*FROMtoys; FETCHtoys_curvarINTOtoys_rec;... CLOSEtoys_curvar;END;声明REF游标类型声明游标变量20游标变量的优点和限制游标变量的功能强大，可以简化数据处理。游标变量的优点有：可从不同的SELECT语句中提取结果集可以作为过程的参数进行传递可以引用游标的所有属性可以进行赋值运算使用游标变量的限制：不能在程序包中声明游标变量FORUPDATE子句不能与游标变量一起使用不能使用比较运算符21使用游标变量执行动态SQL可以使用游标变量执行动态构造的SQL语句翻开执行动态SQL的游标变量的语如下：OPENcursor_nameFORdynamic_sqlstring[USINGbind_argument_list];DECLAREr_empemp%ROWTYPE;TYPEc_typeISREFCURSOR;curc_type;p_salaryNUMBER;BEGINp_salary:=2500;OPENcurFOR'select*fromempwheresal>:1orderbysaldesc'USINGp_salary;DBMS_OUTPUT.PUT_LINE('薪水大于'||p_salary||'的员工有：');LOOPFETCHcurINTOr_emp;EXITWHENcur%NOTFOUND;DBMS_OUTPUT.PUT_LINE('编号：'||r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.sal);ENDLOOP;CLOSEcur;END;22触发器触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的平安权限提供审计和日志记录启用复杂的业务逻辑23创立触发器的语法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;24触发器的组成局部3-1触发器由三局部组成：触发器语句〔事件〕定义激活触发器的DML事件和DDL事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作〔主体〕包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行25触发器的组成局部3-2SQL>CREATEORREPLACETRIGGERtrig_sal AFTERUPDATEOFempsalONsalary_records …触发器语句为salary_records表创立trig-sal触发器在更新emp_sal列之后激活触发器触发器限制SQL> … FOREACHROW WHEN(NEW.empsal>OLD.empsal) DECLARE Sal_diffNUMBER; … 只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器触发器操作SQL>… BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff); END;如果WHEN子句中的条件得到满足，将执行BEGIN块中的代码26触发器的组成局部3-3Oracle数据库更新表保存更新激活触发器AFTER触发器的工作原理BEFORE触发器的工作原理更新表激活触发器保存更新Oracle数据库27创立触发器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入记录');ENDIF;END;/28触发器类型6-1触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEADOF触发器29触发器类型6-2DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器在模式中执行DDL语句时执行在发生翻开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图30触发器类型6-3行级触发器SQL>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));SQL>CREATESEQUENCESEQ_TEST;SQL>CREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,'不允许更新ID值！');ENDIF;END;/31触发器类型6-4SQL>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(‘已更新ORDER_MASTER中的数据');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已删除ORDER_MASTER中的数据');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入数据');ENDIF;END;/语句级触发器32触发器类型6-5SQL>CREATEORREPLACETRIGGERupd_ord_viewINSTEADOFUPDATEONord_viewFOREACHROWBEGIN UPDATEorder_masterSETvencode=:NEW.vencode WHEREorderno=:NEW.orderno; DBMS_OUTPUT.PUT_LINE(‘已激活触发器');END;/INSTEADOF触发器33触发器类型6-6SQL>CREATETABLEdropped