Oracle PL/SQL Tutorial/Function Procedure Packages/Out Parameters
Calling myProc
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 /
IN OUT difference
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>
Out parameter
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>
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.
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>