Đang chuẩn bị liên kết để tải về tài liệu:
Oracle Built−in Packages- P77

Oracle Built−in Packages- P77: Ah, for the good old days of Version 1.0 of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms 3.0, and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk The program does not assert a purity level with the RESTRICT_REFERENCES pragma. 7.2.3.2 Example The following procedure could be part of an application that maintains corporate departmental information Filename on companion disk apinex1.sql CREATE OR REPLACE PROCEDURE drop_dept deptno_IN IN NUMBER reassign_deptno_IN IN NUMBER IS temp_emp_count NUMBER BEGIN DBMS_APPLICATION_INFO.SET_MODULE module_name DEPARTMENT FIXES action_name null -- first check dept for employees DBMS_APPLICATION_INFO.SET_ACTION action_name CHECK EMP SELECT COUNT INTO temp_emp_count FROM emp WHERE deptno deptno_IN -- reassign any employees IF temp_emp_count 0 THEN DBMS_APPLICATION_INFO.SET_ACTION action_name REASSIGN EMPLOYEES UPDATE emp SET deptno reassign_deptno_IN WHERE deptno deptno_IN END IF -- OK now drop the department DBMS_APPLICATION_INFO.SET_ACTION action_name DROP DEPT DELETE FROM dept WHERE deptno deptno_IN COMMIT DBMS_APPLICATION_INFO.SET_MODULE null null EXCEPTION WHEN OTHERS THEN DBMS_APPLICATION_INFO.SET_MODULE null null END drop_dept Notice in this example that DBMS_APPLICATION_INFO is called three times to distinguish between the three steps involved in the process of dropping the department. This gives a very fine granularity to the level at which the application can be tracked. 7.2.3.3 Recommendations for usage Note the following recommendations for using the SET_ACTION procedure 7.2.3 The DBMS_APPLICATION_INFO.SET_ACTION procedure 371 Appendix A What s on the Companion Disk Set the action name to a name that can identify the current transaction or logical unit of work within the module. When the transaction terminates call SET_ACTION and pass a NULL value for the action_name parameter. In case subsequent transactions do not register using DBMS_APPLICATION_INFO passing the NULL value ensures that they are not incorrectly counted as part of the current action. As in the example if the program handles exceptions the exception handler .