Oracle PL/SQL Tutorial/Function Procedure Packages/Out Parameters
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>