Oracle PL/SQL/PL SQL/Utility Package

Содержание

1 Debug package
2 Debug package based on UTL_FILE
3 Debug package: inserts into a test table
4 Debug package: uses DBMS_OUTPUT.
5 stop_watch
6 This object type represents a point on a Cartesian grid.
7 time the performance enhancements of native dynamic SQL.

Debug package

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PACKAGE Debug AS

 2    PROCEDURE Debug(p_Description IN VARCHAR2,
 3                    p_Value IN VARCHAR2);
 4
 5    PROCEDURE Reset;
 6  END Debug;
 7  /

Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Debug AS

 2    PROCEDURE Debug(p_Description IN VARCHAR2,
 3                    p_Value IN VARCHAR2) IS
 4    BEGIN
 5      DBMS_OUTPUT.PUT_LINE(p_Description || ": " || p_Value);
 6    END Debug;
 7
 8    PROCEDURE Reset IS
 9    BEGIN
10      DBMS_OUTPUT.DISABLE;
11      DBMS_OUTPUT.ENABLE(1000000);
12    END Reset;
13  BEGIN /* Package initialization code */
14    Reset;
15  END Debug;
16  /

Package body created. SQL> SQL> SQL>

</source>

Debug package based on UTL_FILE

   <source lang="sql">

CREATE OR REPLACE PACKAGE Debug AS

 v_DebugDir VARCHAR2(50);
 v_DebugFile VARCHAR2(20);
 PROCEDURE Debug(p_Description IN VARCHAR2,
                 p_Value IN VARCHAR2);
 PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                 p_NewDir IN VARCHAR2 := v_DebugDir) ;
 PROCEDURE Close;

END Debug; / CREATE OR REPLACE PACKAGE BODY Debug AS

 v_DebugHandle UTL_FILE.FILE_TYPE;
 PROCEDURE Debug(p_Description IN VARCHAR2,p_Value IN VARCHAR2) IS
 BEGIN
   UTL_FILE.PUTF(v_DebugHandle, "%s: %s\n", p_Description, p_Value);
   UTL_FILE.FFLUSH(v_DebugHandle);
 EXCEPTION
   WHEN UTL_FILE.INVALID_OPERATION THEN
     RAISE_APPLICATION_ERROR(-20102, "Debug: Invalid Operation");
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
     RAISE_APPLICATION_ERROR(-20103, "Debug: Invalid File Handle");
   WHEN UTL_FILE.WRITE_ERROR THEN
     RAISE_APPLICATION_ERROR(-20104, "Debug: Write Error");
 END Debug;
 PROCEDURE Reset(p_NewFile IN VARCHAR2 := v_DebugFile,
                 p_NewDir IN VARCHAR2 := v_DebugDir) IS
 BEGIN
   IF UTL_FILE.IS_OPEN(v_DebugHandle) THEN
     UTL_FILE.FCLOSE(v_DebugHandle);
   END IF;
   v_DebugHandle := UTL_FILE.FOPEN(p_NewDir, p_NewFile, "w");
   v_DebugFile := p_NewFile;
   v_DebugDir := p_NewDir;
 EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
     RAISE_APPLICATION_ERROR(-20100, "Reset: Invalid Path");
   WHEN UTL_FILE.INVALID_MODE THEN
     RAISE_APPLICATION_ERROR(-20101, "Reset: Invalid Mode");
   WHEN UTL_FILE.INVALID_OPERATION THEN
     RAISE_APPLICATION_ERROR(-20101, "Reset: Invalid Operation");
 END Reset;
 PROCEDURE Close IS
 BEGIN
   UTL_FILE.FCLOSE(v_DebugHandle);
 END Close;

BEGIN

 v_DebugDir := "/tmp";
 v_DebugFile := "debug.out";
 Reset;

END Debug; / show error

</source>

Debug package: inserts into a test table

   <source lang="sql">

SQL> SQL> CREATE TABLE debug_table (

 2    linecount  NUMBER PRIMARY KEY,
 3    debug_str  VARCHAR2(200));

SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Debug AS

 2    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2);
 3
 4    PROCEDURE Reset;
 5  END Debug;
 6  /

Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Debug AS

 2    v_LineCount NUMBER;
 3
 4    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2) IS
 5    BEGIN
 6      INSERT INTO debug_table (linecount, debug_str)
 7        VALUES (v_LineCount, p_Description || ": " || p_Value);
 8      COMMIT;
 9      v_LineCount := v_LineCount + 1;
10    END Debug;
11
12    PROCEDURE Reset IS
13    BEGIN
14      v_LineCount := 1;
15      DELETE FROM debug_table;
16    END Reset;
17
18  BEGIN
19    Reset;
20  END Debug;
21  /

Package body created. SQL> SQL> drop table debug_table; Table dropped. SQL> SQL>

</source>

Debug package: uses DBMS_OUTPUT.

   <source lang="sql">

SQL> SQL> CREATE TABLE debug_table (

 2    linecount  NUMBER PRIMARY KEY,
 3    debug_str  VARCHAR2(200));

Table created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Debug AS

 2    PROCEDURE Debug(p_Description IN VARCHAR2, p_Value IN VARCHAR2);
 3
 4    PROCEDURE Reset;
 5  END Debug;
 6  /

Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY Debug AS

 2    PROCEDURE Debug(p_Description IN VARCHAR2,
 3                    p_Value IN VARCHAR2) IS
 4    BEGIN
 5      DBMS_OUTPUT.PUT_LINE(p_Description || ": " || p_Value);
 6    END Debug;
 7
 8    PROCEDURE Reset IS
 9    BEGIN
10      DBMS_OUTPUT.DISABLE;
11      DBMS_OUTPUT.ENABLE(1000000);
12    END Reset;
13
14  BEGIN
15    Reset;
16  END Debug;
17  /

Package body created. SQL> SQL>

</source>

stop_watch

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PACKAGE stop_watch AS

 2     pv_start_time_num     PLS_INTEGER;
 3     pv_stop_time_num      PLS_INTEGER;
 4     pv_last_stop_time_num PLS_INTEGER;
 5  PROCEDURE start_timer;
 6  PROCEDURE stop_timer;
 7  END stop_watch;
 8  /

Package created. SQL> CREATE OR REPLACE PACKAGE BODY stop_watch AS

 2  PROCEDURE start_timer AS
 3  BEGIN
 4     pv_start_time_num     := DBMS_UTILITY.GET_TIME;
 5     pv_last_stop_time_num := pv_start_time_num;
 6  END start_timer;
 7
 8  PROCEDURE stop_timer AS
 9  BEGIN
10     pv_stop_time_num := DBMS_UTILITY.GET_TIME;
11     DBMS_OUTPUT.PUT_LINE("Total Time Elapsed: " ||
12        TO_CHAR((pv_stop_time_num - pv_start_time_num)/100,
13        "999,999.99") || " sec   Interval Time: " ||
14        TO_CHAR((pv_stop_time_num - pv_last_stop_time_num)/100,
15        "99,999.99") || " sec");
16     pv_last_stop_time_num := pv_stop_time_num;
17  END stop_timer;
18  END;
19  /

Package body created. SQL> SQL> CREATE OR REPLACE PROCEDURE test_if (p_condition_num NUMBER) AS

 2     lv_temp_num         NUMBER := 0;
 3     lv_temp_cond_num    NUMBER := p_condition_num;
 4  BEGIN
 5     stop_watch.start_timer;
 6     FOR lv_count_num IN 1..1000 LOOP
 7        IF lv_temp_cond_num = 1 THEN
 8           lv_temp_num := lv_temp_num + 1;
 9        ELSIF lv_temp_cond_num = 2 THEN
10           lv_temp_num := lv_temp_num + 1;
11        ELSIF lv_temp_cond_num = 3 THEN
12           lv_temp_num := lv_temp_num + 1;
13        ELSIF lv_temp_cond_num = 4 THEN
14           lv_temp_num := lv_temp_num + 1;
15        ELSIF lv_temp_cond_num = 5 THEN
16           lv_temp_num := lv_temp_num + 1;
17        ELSIF lv_temp_cond_num = 6 THEN
18           lv_temp_num := lv_temp_num + 1;
19        ELSIF lv_temp_cond_num = 7 THEN
20           lv_temp_num := lv_temp_num + 1;
21        ELSE
22           lv_temp_num := lv_temp_num + 1;
23        END IF;
24     END LOOP;
25     stop_watch.stop_timer;
26  END;
27  /

Procedure created. SQL> SQL> SQL> SQL> SQL> SQL>

</source>

This object type represents a point on a Cartesian grid.

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE Point AS OBJECT (

 2    x NUMBER,
 3    y NUMBER,
 4
 5    MEMBER FUNCTION ToString RETURN VARCHAR2,
 6    PRAGMA RESTRICT_REFERENCES(ToString, RNDS, WNDS, RNPS, WNPS),
 7
 8    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
 9      RETURN NUMBER,
10    PRAGMA RESTRICT_REFERENCES(Distance, RNDS, WNDS, RNPS, WNPS),
11
12    MEMBER FUNCTION Plus(p IN Point) RETURN Point,
13    PRAGMA RESTRICT_REFERENCES(Plus, RNDS, WNDS, RNPS, WNPS),
14
15    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point,
16    PRAGMA RESTRICT_REFERENCES(Times, RNDS, WNDS, RNPS, WNPS)
17  );
18  /

Type created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE TYPE BODY Point AS

 2    MEMBER FUNCTION ToString RETURN VARCHAR2 IS
 3      myResult VARCHAR2(20);
 4      v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x), 1, 8);
 5      v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y), 1, 8);
 6    BEGIN
 7      myResult := "(" || v_xString || ", ";
 8      myResult := myResult || v_yString || ")";
 9      RETURN myResult;
10    END ToString;
11
12    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
13      RETURN NUMBER IS
14    BEGIN
15      RETURN SQRT(POWER(x - p.x, 2) + POWER(y - p.y, 2));
16    END Distance;
17
18    MEMBER FUNCTION Plus(p IN Point) RETURN Point IS
19      myResult Point;
20    BEGIN
21      myResult := Point(x + p.x, y + p.y);
22      RETURN myResult;
23    END Plus;
24
25    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point IS
26      myResult Point;
27    BEGIN
28      myResult := Point(x * n, y * n);
29      RETURN myResult;
30    END Times;
31  END;
32  /

Type body created. SQL> show errors No errors. SQL> SQL> set serveroutput on SQL> SQL> DECLARE

 2    v_Point1 Point := Point(1, 2);
 3    v_Point2 Point;
 4    v_Point3 Point;
 5  BEGIN
 6    v_Point2 := v_Point1.Times(4);
 7    v_Point3 := v_Point1.Plus(v_Point2);
 8    DBMS_OUTPUT.PUT_LINE("Point 2: " || v_Point2.ToString);
 9    DBMS_OUTPUT.PUT_LINE("Point 3: " || v_Point3.ToString);
10    DBMS_OUTPUT.PUT_LINE("Distance between origin and point 1: " ||
11      v_Point1.Distance);
12    DBMS_OUTPUT.PUT_LINE("Distance between point 1 and point 2: " ||
13      v_Point1.Distance(v_Point2));
14  END;
15  /

Point 2: (4, 8) Point 3: (5, 10) Distance between origin and point 1: 2.23 Distance between point 1 and point 2: 6.70 PL/SQL procedure successfully completed. SQL>

</source>

time the performance enhancements of native dynamic SQL.

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PACKAGE Timing AS

 2    PROCEDURE StartTiming;
 3    PROCEDURE StopTiming;
 4    PROCEDURE PrintElapsed(p_Message IN VARCHAR2);
 5  END Timing;
 6  /

Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Timing AS

 2    v_StartTime NUMBER;
 3    v_EndTime NUMBER;
 4
 5    PROCEDURE StartTiming IS
 6    BEGIN
 7      v_StartTime := DBMS_UTILITY.GET_TIME;
 8    END StartTiming;
 9
10    PROCEDURE StopTiming IS
11    BEGIN
12      v_EndTime := DBMS_UTILITY.GET_TIME;
13    END StopTiming;
14
15    PROCEDURE PrintElapsed(p_Message IN VARCHAR2) IS
16      v_Elapsed NUMBER := (v_EndTime - v_StartTime) / 100;
17    BEGIN
18      DBMS_OUTPUT.PUT_LINE(
19        "Elapsed Time for " || p_Message || " is " ||
20        v_Elapsed  || " seconds.");
21    END PrintElapsed;
22  END Timing;
23  /

Package body created. SQL> SQL> SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> SQL> TRUNCATE TABLE MyTable; Table truncated. SQL> SQL> DECLARE

 2    v_CursorID INTEGER;
 3    v_Dummy NUMBER;
 4    v_SQLStatement VARCHAR2(100) := "INSERT INTO MyTable (num_col) VALUES (:num)";
 5
 6    c_NumRows CONSTANT NUMBER := 5000;
 7  BEGIN
 8    DELETE FROM MyTable;
 9    COMMIT;
10
11    Timing.StartTiming;
12    v_CursorID := DBMS_SQL.OPEN_CURSOR;
13
14    FOR v_Count IN 1..c_NumRows LOOP
15      DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE);
16      DBMS_SQL.BIND_VARIABLE(v_CursorID, ":num", v_Count);
17      v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
18    END LOOP;
19
20    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
21
22    Timing.StopTiming;
23    Timing.PrintElapsed("DBMS_SQL");
24
25    DELETE FROM MyTable;
26    COMMIT;
27
28    Timing.StartTiming;
29    FOR v_Count IN 1..c_NumRows LOOP
30      EXECUTE IMMEDIATE v_SQLStatement USING v_Count;
31    END LOOP;
32    Timing.StopTiming;
33    Timing.PrintElapsed("Native Dynamic SQL");
34  END;
35  /

Elapsed Time for DBMS_SQL is 1.04 seconds. Elapsed Time for Native Dynamic SQL is .52 seconds. PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table MyTable; Table dropped. SQL>

</source>

Oracle PL/SQL/PL SQL/Utility Package

Содержание

Debug package

Debug package based on UTL_FILE

Debug package: inserts into a test table

Debug package: uses DBMS_OUTPUT.

stop_watch

This object type represents a point on a Cartesian grid.

time the performance enhancements of native dynamic SQL.

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты