plsql learning - four:procedure

最新推荐文章于 2013-07-20 11:51:47 发布

最新推荐文章于 2013-07-20 11:51:47 发布 · 130 阅读

文章标签：

#Go #UP

DataBase 专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍了PL/SQL中过程的三种参数类型：IN、OUT、INOUT，并通过实例展示了不同参数类型的使用方法及效果。此外，还对比了传值与传引用的不同效率，指出在大量数据处理时使用nocopy选项可以显著提高程序运行速度。

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.