Oracle PL/SQL Tutorial/Function Procedure Packages/Out Parameters

Материал из SQL эксперт
Перейти к: навигация, поиск

Calling myProc

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE myProc(ip_line_length IN NUMBER,ip_separator IN VARCHAR2,op_line OUT VARCHAR2)

 2  IS
 3    myString VARCHAR2(150);
 4  BEGIN
 5
 6    FOR idx in 1..ip_line_length LOOP
 7
 8      myString :=myString ||ip_separator;
 9
10    END LOOP;
11
12    op_line :=myString;
13
14  EXCEPTION WHEN OTHERS THEN
15
16    dbms_output.put_line(SQLERRM);
17
18    op_line :=null;
19  END;
20  /

Procedure created. SQL> SQL> SQL> DECLARE

 2    v_length NUMBER :=50;
 3
 4    v_separator VARCHAR2(1):="=";
 5
 6    v_line VARCHAR2(150);
 7  BEGIN
 8
 9    myProc(v_length,v_separator,v_line);
10
11    dbms_output.put_line(v_line);
12  END;
13  /</source>
   
  

IN OUT difference

   <source lang="sql">

SQL> create or replace package PARAMETERS as

 2  FUNCTION in_out_inout(
 3  aiv_in                         in     varchar2,
 4  aov_out                           out varchar2,
 5  aiov_inout                     in out varchar2)
 6  return                                varchar2;
 7  PROCEDURE in_out_inout(
 8  aiv_in                         in     varchar2,
 9  aov_out                           out varchar2,
10  aiov_inout                     in out varchar2);
11  end PARAMETERS;
12  /

Package created. SQL> SQL> SQL> create or replace package body PARAMETERS as

 2  FUNCTION in_out_inout(
 3  aiv_in                         in     varchar2,
 4  aov_out                           out varchar2,
 5  aiov_inout                     in out varchar2)
 6  return                                varchar2 is
 7  begin
 8
 9    dbms_output.put_line(aiv_in);
10    dbms_output.put_line(aov_out);
11    dbms_output.put_line(aiov_inout);
12    aov_out   := "OUT";
13    aiov_inout := aiv_in;
14    aiov_inout := aiov_inout||"OUT";
15
16    dbms_output.put_line(aiv_in);
17    dbms_output.put_line(aov_out);
18    dbms_output.put_line(aiov_inout);
19    return "OK";
20
21  end in_out_inout;
22
23
24  PROCEDURE in_out_inout(
25  aiv_in                         in     varchar2,
26  aov_out                           out varchar2,
27  aiov_inout                     in out varchar2) is
28  begin
29
30    dbms_output.put_line(aiv_in);
31    dbms_output.put_line(aov_out);
32    dbms_output.put_line(aiov_inout);
33    aov_out   := "OUT";
34    aiov_inout := aiv_in;
35    aiov_inout := aiov_inout||"OUT";
36
37    dbms_output.put_line(aiv_in);
38    dbms_output.put_line(aov_out);
39    dbms_output.put_line(aiov_inout);
40  end in_out_inout;
41  end PARAMETERS;
42  /

Package body created. SQL> SQL> SQL> SQL> declare

 2      v_in                         varchar2(30) := "IN";
 3      v_out                        varchar2(30) := "AAA";
 4      v_inout                      varchar2(30) := "BBB";
 5      v_return                     varchar2(30);
 6  begin
 7
 8    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
 9    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
10    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
11    dbms_output.put_line("Test function PARAMETERS.in_out_inout(v_in, v_out, v_inout).");
12
13    v_return := PARAMETERS.in_out_inout(v_in, v_out, v_inout);
14
15    dbms_output.put_line(v_return);
16    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
17    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
18    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
19    dbms_output.put_line("Resetting initial values...");
20
21    v_out   := "AAA";
22    v_inout := "BBB";
23
24    dbms_output.put_line("Before calling the procedure...");
25    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
26    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
27    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
28    dbms_output.put_line("Test procedure PARAMETERS.in_out_inout(v_in, v_out, v_inout).");
29
30    PARAMETERS.in_out_inout(v_in, v_out, v_inout);
31
32    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
33    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
34    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
35  end;
36  /

Inside test unit parameters v_in = IN Inside test unit parameters v_out = AAA Inside test unit parameters v_inout = BBB Test function PARAMETERS.in_out_inout(v_in, v_out, v_inout). IN BBB IN OUT INOUT OK Inside test unit parameters v_in = IN Inside test unit parameters v_out = OUT Inside test unit parameters v_inout = INOUT Resetting initial values... Before calling the procedure... Inside test unit parameters v_in = IN Inside test unit parameters v_out = AAA Inside test unit parameters v_inout = BBB Test procedure PARAMETERS.in_out_inout(v_in, v_out, v_inout). IN BBB IN OUT INOUT Inside test unit parameters v_in = IN Inside test unit parameters v_out = OUT Inside test unit parameters v_inout = INOUT PL/SQL procedure successfully completed. SQL></source>


Out parameter

   <source lang="sql">

SQL> CREATE PROCEDURE changePrice (old_price NUMBER,percent_update NUMBER := 5,new_price OUT NUMBER)

 2  IS
 3  BEGIN
 4          new_price := old_price + old_price * percent_update / 100;
 5  END changePrice;
 6  /

Procedure created. SQL> SQL> set serveroutput on SQL> DECLARE

 2          price_to_update NUMBER(6,2) := 20;
 3          updated_price NUMBER(6,2) := 0;
 4  BEGIN
 5          dbms_output.put_line("price before " || price_to_update);
 6          dbms_output.put_line("updated_price before " || updated_price);
 7          changePrice (old_price => price_to_update, new_price => updated_price);
 8          dbms_output.put_line("price_to_update after update " || price_to_update);
 9          dbms_output.put_line("updated_price after update " || updated_price);
10  END;
11  /

price before 20 updated_price before 0 price_to_update after update 20 updated_price after update 21 PL/SQL procedure successfully completed. SQL> SQL></source>


OUT Parameters

An OUT parameter returns a value to the main program.

An OUT parameter allows you to return more than one variable from the subprogram.

The actual parameter is a variable.



   <source lang="sql">

SQL> create or replace procedure p_split (i_date_dt DATE,o_hour OUT NUMBER, o_min OUT NUMBER)

 2  is
 3  begin
 4      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
 5      o_hour:= to_NUMBER(to_char(i_date_dt,"hh24"));
 6      o_min := TO_CHAR(i_date_dt,"mi");
 7      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
 8  end;
 9  /

Procedure created. SQL> SQL> declare

 2    v_hour NUMBER:=12;
 3    v_min  NUMBER:=20;
 4  begin
 5      p_split(sysdate, v_hour, v_min);
 6      DBMS_OUTPUT.put_line ("Total minutes:"||(v_hour*60+v_min));
 7  end;
 8  /

/ 21/27 Total minutes:1287 PL/SQL procedure successfully completed. SQL></source>