в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:11:51Z

1 версия

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

== IN OUT NOCOPY==



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE very_confusing (
2 arg1 IN VARCHAR2
3 , arg2 IN OUT VARCHAR2
4 , arg3 IN OUT NOCOPY VARCHAR2
5 )
6 IS
7 BEGIN
8 arg2 := "Second value";
9 DBMS_OUTPUT.put_line ("arg2 assigned, arg1 = " || arg1);
10 arg3 := "Third value";
11 DBMS_OUTPUT.put_line ("arg3 assigned, arg1 = " || arg1);
12 END;
13 /
Procedure created.
SQL>
SQL> DECLARE
2 str VARCHAR2 (100) := "First value";
3 BEGIN
4 DBMS_OUTPUT.put_line ("str before = " || str);
5 very_confusing (str, str, str);
6 DBMS_OUTPUT.put_line ("str after = " || str);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL></source>

== Performance improvement of NOCOPY==



<source lang="sql">
SQL>
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 is
3 type arr is varray(100000)of product%ROWTYPE;
4 procedure p1(ip1 IN OUT arr);
5 procedure p2(ip1 IN OUT NOCOPY arr);
6 FUNCTION get_time RETURN NUMBER;
7 END myPackage;
8 /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 is
3 PROCEDURE p1(ip1 IN OUT arr)
4 IS
5 BEGIN
6 NULL;
7 END;
8 PROCEDURE p2(ip1 IN OUT NOCOPY arr)
9 IS
10 BEGIN
11 NULL;
12 END;
13 FUNCTION get_time RETURN NUMBER
14 IS
15 BEGIN
16 RETURN (dbms_utility.get_time);
17 EXCEPTION WHEN OTHERS THEN
18 RAISE_APPLICATION_ERROR(-20010,SQLERRM);
19 END get_time;
20 END myPackage;
21 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
2 arr1 myPackage.arr :=myPackage.arr(null);
3 cur_t1 number;
4 cur_t2 number;
5 cur_t3 number;
6 begin
7 select * into arr1(1)from product where product_id =1;
8
9
10 arr1.extend(99999,1);
11 cur_t1 :=myPackage.get_time;
12 myPackage.p1(arr1);
13 cur_t2 :=myPackage.get_time;
14 myPackage.p2(arr1);
15 cur_t3 :=myPackage.get_time;
16
17 dbms_output.put_line("Without NOCOPY "||to_char((cur_t2-cur_t1)/100));
18 dbms_output.put_line("With NOCOPY "||to_char((cur_t3-cur_t2)/100));
19 end;
20 /
Without NOCOPY .17
With NOCOPY 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
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>

== The hint NOCOPY is applicable only to OUT and IN OUT types of variables==



<source lang="sql">
SQL>
SQL> create or replace procedure p_validate(io_string IN OUT NOCOPY VARCHAR2)
2 is
3 v_invalid VARCHAR2(8):="!@#$%^&";
4 begin
5 io_string:=replace (io_string,v_invalid);
6 if length(io_string)>4000
7 then
8 io_string:=substr(io_string,1,3997)||"...";
9 end if;
10 end;
11 /
Procedure created.
SQL></source>

== You can pass variables by reference, even in PL/SQL==



<source lang="sql">
SQL>
SQL> create or replace procedure p_validate(io_string IN OUT NOCOPY VARCHAR2)
2 is
3 v_invalid VARCHAR2(8):="!@#$%^&";
4 begin
5 io_string:=replace (io_string,v_invalid);
6 if length(io_string)>4000
7 then
8 io_string:=substr(io_string,1,3997)||"...";
9 end if;
10 end;
11 /
Procedure created.
SQL></source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия