Mega Code Archive
Timing Package function call
<![CDATA[
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, 'Mar', 'Wells','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, 'Man', 'Kyte','Economics', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, 'Pat', 'Poll','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, 'Tim', 'Viper','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, 'Barbara', 'Blues','Economics', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, 'David', 'Large','Music', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, 'Rose', 'Bond','Music', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE CopyFast AS
2 TYPE StudentArray IS
3 TABLE OF lecturer%ROWTYPE;
4
5 PROCEDURE Passlecturer1(p_Parameter IN StudentArray);
6 PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray);
7 PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray);
8
9 PROCEDURE Go;
10 END CopyFast;
11 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS
2 PROCEDURE Passlecturer1(p_Parameter IN StudentArray) IS
3 BEGIN
4 NULL;
5 END Passlecturer1;
6
7 PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray) IS
8 BEGIN
9 NULL;
10 END Passlecturer2;
11
12 PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray) IS
13 BEGIN
14 NULL;
15 END Passlecturer3;
16
17 PROCEDURE Go IS
18 myLecturerArray StudentArray := StudentArray(NULL);
19 myLecturerRec lecturer%ROWTYPE;
20 v_Time1 NUMBER;
21 v_Time2 NUMBER;
22 v_Time3 NUMBER;
23 v_Time4 NUMBER;
24 BEGIN
25 SELECT *
26 INTO myLecturerArray(1)
27 FROM lecturer
28 WHERE ID = 10007;
29 myLecturerArray.EXTEND(50000, 1);
30
31 v_Time1 := DBMS_UTILITY.GET_TIME;
32 Passlecturer1(myLecturerArray);
33 v_Time2 := DBMS_UTILITY.GET_TIME;
34 Passlecturer2(myLecturerArray);
35 v_Time3 := DBMS_UTILITY.GET_TIME;
36 Passlecturer3(myLecturerArray);
37 v_Time4 := DBMS_UTILITY.GET_TIME;
38
39 DBMS_OUTPUT.PUT_LINE('Time to pass IN: ' || TO_CHAR((v_Time2 - v_Time1) / 100));
40 DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT: ' || TO_CHAR((v_Time3 - v_Time2) / 100));
41 DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT NOCOPY: ' || TO_CHAR((v_Time4 - v_Time3) / 100));
42 END Go;
43 END CopyFast;
44 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 CopyFast.Go;
3 END;
4 /
Time to pass IN: 0
Time to pass IN OUT: .19
Time to pass IN OUT NOCOPY: 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
]]>