Передача параметров. Атрибут nocopy

create or replace procedure my_p2

(s1 out varchar2, s2 in out nocopy varchar2)

as

begin

s1 := 'aaaaaaaaaaaaaaaaaaaaaaaaaa';

s2 := 'bbbbbbbbbbbbbbbbbbbbbbbbbb';

end;

Модули pl/sql

create or replace package PackageTest

as

type point is record

(

x number,

y number

);

tpoint point;

n number;

procedure p(ii in number, ss out varchar2);

function f(n in point) return number;

function f(x in number, y in number) return number;

end PackageTest;

create or replace package body PackageTest

as

type t1 is record

(

i number,

j number

);

cnt number;

trow1 test%rowtype;

procedure p(ii in number, ss out varchar2)

is

begin

select s into ss from test where i = ii;

exception

when no_data_found then

ss := 'empty string';

end;

function f(n in point) return number

as

begin

return n.x+n.y;

end;

function f(x in number, y in number) return number

as

begin

return x+y;

end;

begin

trow1.i := 1;

trow1.s := 'null string';

n := 100;

cnt := -10;

end PackageTest;

create or replace procedure PackageTest_test

as

-- error

-- d1 PackageTest.tl;

s varchar2(100);

n PackageTest.point;

begin

n.x := 100;

n.y := 200;

PackageTest.p(1, s);

dbms_output.put_line('s = ' || s);

dbms_output.put_line('f((100, 100)) = ' || PackageTest.f(n));

dbms_output.put_line('f(100, 100) = ' || PackageTest.f(n));

dbms_output.put_line('n = ' || PackageTest.n);

-- error

-- dbms_output.put_line('cnt = ' || PackageTest.cnt);

end;

Просмотр объектов и компиляция

desc user_objects

select object_name, object_type, status from user_objects;

Перекомпиляция процедур и функций

create or replace function f(i in number)

return varchar2

as

begin

return to_char(i+1);

end;

create or replace procedure p(ii in number, ss out varchar2)

as

begin

select s into ss from test where i = ii;

exception

when no_data_found then

ss := 'no data';

end;

select substr(object_name, 1, 30), object_type,

status from user_objects;

select substr(object_name, 1, 30), object_type,

status from user_objects where object_type in

('PROCEDURE', 'FUNCTION', 'PACKAGE',

'PACKAGE BODY') and (object_name like 'P%'

or object_name like 'F%');

declare

i number;

s varchar2(100);

begin

i := 10;

s := f(i);

dbms_output.put_line(s);

i := 1;

p(i, s);

dbms_output.put_line(s);

end;

alter table test add h date;

alter table test drop column h;

alter procedure p compile;

select * from user_errors;

alter function f compile;

alter package packagetest compile specification;

alter package packagetest compile body;

alter package packagetest compile package;

select substr(object_name, 1, 30), object_type,

status from user_objects where object_type in

('PROCEDURE', 'FUNCTION', 'PACKAGE',

'PACKAGE BODY') and (object_name like 'P%'

or object_name like 'F%');

select * from user_errors;