There are three type of parameters of procedure,IN,OUT,IN OUT,here is the example:
create or replace procedure ModeTest(
p_InParameter in number,
p_OutParameter out number,
p_InOutParameter in out number
) is
v_LocalVariable number:=0;
begin
if(p_InParameter is null) then
dbms_output.put_line('p_InParameter is NULL');
else
dbms_output.put_line('p_InParameter = '||p_InParameter);
end if;
if(p_OutParameter is null) then
dbms_output.put_line('p_OutParameter is NULL');
else
dbms_output.put_line('p_OutParameter = '||p_OutParameter);
end if;
if(p_InOutParameter is null) then
dbms_output.put_line('p_InOutParameter is NULL');
else
dbms_output.put_line('p_InOutParameter = '||p_InOutParameter);
end if;
p_OutParameter := 4;
--p_InParameter := 9;illegal
p_InOutParameter := 9;
v_LocalVariable := p_InParameter;
dbms_output.put_line(to_char(v_LocalVariable));
v_LocalVariable := p_InOutParameter;
dbms_output.put_line(to_char(v_LocalVariable));
v_LocalVariable := p_OutParameter;
dbms_output.put_line(to_char(v_LocalVariable));
if(p_InParameter is null) then
dbms_output.put_line('p_InParameter is NULL');
else
dbms_output.put_line('p_InParameter = '||p_InParameter);
end if;
if(p_OutParameter is null) then
dbms_output.put_line('p_OutParameter is NULL');
else
dbms_output.put_line('p_OutParameter = '||p_OutParameter);
end if;
if(p_InOutParameter is null) then
dbms_output.put_line('p_InOutParameter is NULL');
else
dbms_output.put_line('p_InOutParameter = '||p_InOutParameter);
end if;
end ModeTest;
then test it:input 1,2,3 for testing,result is 1,4,9
log printed is:
Before assigned value:
p_InParameter = 1
p_OutParameter is NULL
p_InOutParameter = 3
After assigned value:
p_InParameter = 1
p_OutParameter = 4
p_InOutParameter = 9;
then the example of nocopy usage:
create or replace package CopyFast as
type StudentArray is table of students%rowtype;
--procedure with using pass-by-value
procedure PassStudents1(p_Parameter in StudentArray);
--procedure with using pass-by-reference
procedure PassStudents2(p_Parameter in out StudentArray);
--procedure with using pass-by-value
procedure PassStudents3(p_Parameter in out nocopy StudentArray);
--test procedure
procedure go;
end CopyFast;
create or replace package body CopyFast as
procedure PassStudents1(p_Parameter in StudentArray) is
begin
null;
end PassStudents1;
procedure PassStudents2(p_Parameter in out StudentArray) is
begin
null;
end PassStudents2;
procedure PassStudents3(p_Parameter in out nocopy StudentArray) is
begin
null;
end PassStudents3;
procedure go is
v_StudentArray StudentArray := StudentArray(NULL);
v_StudentRec students%rowtype;
v_Time1 number;
v_Time2 number;
v_Time3 number;
v_Time4 number;
begin
--Fill up the array with 50001 copies of Daivid Dinsmore's record
select * into v_StudentArray(1)
from students where id = 10007;
v_StudentArray.extend(50000,1);
--call each version of procedure PassStudents,and time them.
v_Time1 := dbms_utility.get_time;
PassStudents1(v_StudentArray);
v_Time2 := dbms_utility.get_time;
PassStudents2(v_StudentArray);
v_Time3 := dbms_utility.get_time;
PassStudents3(v_StudentArray);
v_Time4 := dbms_utility.get_time;
--output the results
dbms_output.put_line(to_char(v_Time3)||'=='||to_char(v_Time2));
dbms_output.put_line('Time to pass IN :'||to_char((v_Time2-v_Time1)/100));
dbms_output.put_line('Time to pass IN OUT:'||to_char((v_Time3-v_Time2)/100));
dbms_output.put_line('Time to pass IN OUT NOCOPY:'||to_char((v_Time4-v_Time3)/100));
end go;
end CopyFast;
then exec the procedure,result is:0,11,0
so we can use nocopy for change pass-by-value to pass-by-reference of parameter.