в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:00:57Z

1 версия

Новая страница

==Possible speed benefits of NOCOPY==



<source lang="sql">

SQL>
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563,.99,99, 1, 2, 3);
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765,.99,99, 4, 5);
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server",4,.99,01, 6, 7, 8);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE CopyFast AS
2 TYPE BookArray IS TABLE OF book%ROWTYPE;
3
4 PROCEDURE Passbook1(p IN BookArray);
5 PROCEDURE Passbook2(p IN OUT BookArray);
6 PROCEDURE Passbook3(p IN OUT NOCOPY BookArray);
7
8 PROCEDURE Go;
9 END CopyFast;
10 /
Package created.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS
2 PROCEDURE Passbook1(p IN BookArray) IS
3 BEGIN
4 NULL;
5 END Passbook1;
6
7 PROCEDURE Passbook2(p IN OUT BookArray) IS
8 BEGIN
9 NULL;
10 END Passbook2;
11
12 PROCEDURE Passbook3(p IN OUT NOCOPY BookArray) IS
13 BEGIN
14 NULL;
15 END Passbook3;
16
17 PROCEDURE Go IS
18 v_BookArray BookArray := BookArray(NULL);
19 v_Time1 NUMBER;
20 v_Time2 NUMBER;
21
22 BEGIN
23
24 SELECT * INTO v_BookArray(1) FROM book WHERE ISBN = "3";
25 v_BookArray.EXTEND(50000, 1);
26
27 v_Time1 := DBMS_UTILITY.GET_TIME;
28 Passbook1(v_BookArray);
29 v_Time2 := DBMS_UTILITY.GET_TIME;
30 Passbook2(v_BookArray);
31
32
33 DBMS_OUTPUT.PUT_LINE("Time to pass IN: " || TO_CHAR((v_Time2 - v_Time1) /0));
34 END Go;
35 END CopyFast;
36 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 CopyFast.Go();
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "sqle.COPYFAST", line 33
ORA-06512: at line 2

SQL>
SQL>
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>
SQL>

</source>

==The behavior of NOCOPY.==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE NoCopyTest(p_In IN NUMBER,p_Out OUT NOCOPY VARCHAR2,p_InOut IN OUT NOCOPY CHAR) IS
2 BEGIN
3 NULL;
4 END NoCopyTest;
5 /
Procedure created.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> -- A modified version of RaiseError, with the out parameter specified as NOCOPY.
SQL> CREATE OR REPLACE PROCEDURE RaiseErrorNoCopy(p_Raise IN BOOLEAN,pA OUT NOCOPY NUMBER) AS
2 BEGIN
3 pA := 5;
4 IF p_Raise THEN
5 RAISE DUP_VAL_ON_INDEX;
6 ELSE
7 RETURN;
8 END IF;
9 END RaiseErrorNoCopy;
10 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- When we call RaiseErrorNoCopy the exception semantics are changed due to NOCOPY.
SQL> DECLARE
2 v_Num NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Value before first call: " || v_Num);
5 RaiseErrorNoCopy(FALSE, v_Num);
6 DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_Num);
7 DBMS_OUTPUT.PUT_LINE("");
8
9 v_Num := 2;
10 DBMS_OUTPUT.PUT_LINE("Value before second call: " || v_Num);
11 RaiseErrorNoCopy(TRUE, v_Num);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_Num);
15 END;
16 /
Value before first call: 1
Value after successful call: 5
Value before second call: 2
Value after unsuccessful call: 5
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Stored Procedure Function/NOCOPY - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия