Parameters

Contact Us or call 1-877-932-8228
Parameters

Parameters

The following demonstrations consist of stored procedures and calls to them from PL/SQL anonymous blocks.

Stored procedures and functions can be passed information through parameters. Parameters appear in the subprogram declaration and identify the name and data type of the parameter being passed.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_params.sql
CREATE OR REPLACE PROCEDURE in_params
( p_varchar2 IN VARCHAR2, 
  p_number IN NUMBER, 
  p_date IN DATE
) 
IS
BEGIN
  dbms_output.put_line('p_varchar2: ' || p_varchar2);
  dbms_output.put_line('p_number: ' || p_number);
  dbms_output.put_line('p_date: ' || p_date);
END in_params;
/

Code Explanation

In this example, three parameters of different types are passed in to the procedure and then printed out.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_params_call.sql
BEGIN
  IN_PARAMS(P_VARCHAR2 => 'Ray Bradbury', 
            P_NUMBER => 451,
            P_DATE => to_date('22-AUG-1920', 'dd-mm-yyyy')
           );
END;
/

-- This next demo illustrates the advantage of calling
-- a stored procedure with named notation:
-- the parameters do not have to be in the declared order
-- when the stored procedure is called.
BEGIN
  IN_PARAMS(P_NUMBER => 451,
            P_VARCHAR2 => 'Ray Bradbury', 
            P_DATE => to_date('22-AUG-1920', 'dd-mm-yyyy')
           );
END;
/

Code Explanation

This anonymous block can be used to call the procedure above.

The DEFAULT keyword allows you to specify a value for an input parameter that will be used if one is not supplied by the invoking program.

Code Sample:

Stored-Procedures-and-Functions/Demos/default_value.sql
create or replace PROCEDURE default_value
( p_varchar2 IN VARCHAR2 DEFAULT 'This is a default value'
) 
IS
BEGIN
  dbms_output.put_line(p_varchar2);
END default_value;
/

Code Explanation

Input parameters can be assigned default values using the default keyword.

Code Sample:

Stored-Procedures-and-Functions/Demos/default_value_call.sql
BEGIN

  dbms_output.put_line('*** Parameter not specified...');  
  DEFAULT_VALUE();
  
  dbms_output.put_line('*** With a value assigned...');  
  DEFAULT_VALUE('Hey a value');

  dbms_output.put_line('*** With a null value...');  
  DEFAULT_VALUE(NULL);
  dbms_output.put_line('*** end.');  
  
END;
/

Code Explanation

This anonymous block can be used to call the procedure. It illustrates the behavior of the default keyword. If a parameter is specified in the subprogram call, it is assigned to the parameter. If the parameter is not referenced, a default value is used. Note that explicitly assigning a parameter with a default value to null results in the parameter being null. The default keyword is not used in this case.

So a fundamental way that information gets passed into a subprogram is through parameters. However, parameters can also be used to pass data out of a subprogram. Although - strictly speaking - a function differs from a stored procedure in that it can return a value, values can effectively be "returned" from subprograms using default values.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_params.sql
CREATE OR REPLACE PROCEDURE out_params
( p_varchar2 OUT VARCHAR2, 
  p_number OUT NUMBER, 
  p_date OUT DATE
)
IS
BEGIN

  p_varchar2 := 'George Orwell';
  p_number   := 1984;
  p_date     := to_date('25-Jun-1903','dd-mon-yyyy');
  
END out_params;
/

Code Explanation

An OUT parameter must be declared to modify a parameter within a subprogram so that its value is available to the calling program.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_params_call.sql
DECLARE
  v_varchar2 VARCHAR2(200);
  v_number NUMBER;
  v_date DATE;
BEGIN

  out_params(p_varchar2 => v_varchar2,
             p_number => v_number,
             p_date => v_date
            );
  DBMS_OUTPUT.PUT_LINE('v_varchar2 = ' || v_varchar2);
  DBMS_OUTPUT.PUT_LINE('v_number = ' || v_number);
  DBMS_OUTPUT.PUT_LINE('v_date = ' || v_date);
END;
/

Code Explanation

Another anonymous block to call the OUT parameter example procedure above.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_out_param.sql
CREATE OR REPLACE PROCEDURE in_out_param
( p_in_out IN OUT VARCHAR2
)
IS
BEGIN
  p_in_out := 'This value was set within the procedure.';
END in_out_param;
/

Code Explanation

Parameters can be declared as both IN and OUT parameters.

Code Sample:

Stored-Procedures-and-Functions/Demos/in_out_param_call.sql
DECLARE
 V_IN_OUT varchar2(50) := 'This is the value going in...';
BEGIN
  IN_OUT_PARAM(V_IN_OUT);
  DBMS_OUTPUT.PUT_LINE('Upon return, V_IN_OUT = ' || V_IN_OUT);
END;
/

Code Explanation

This anonymous block can be used to call the procedure above.

Next