Oracle PL/SQL/PL SQL/Utility Package
Содержание
Debug package
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>
Debug package based on UTL_FILE
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
Debug package: inserts into a test table
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>
Debug package: uses DBMS_OUTPUT.
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>
stop_watch
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>
This object type represents a point on a Cartesian grid.
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>
time the performance enhancements of native dynamic 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>