Examples Using Input and Output Parameters and Bind Variables

The examples in this section use IN, OUT, and IN OUT parameters, including bind variables (host variables) from outside PL/SQL.

Using IN and OUT Parameters

This example creates a procedure query_emp to retrieve information about an employee, passes the employee_id value 171 to the procedure, and retrieves the name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
           (p_id IN employees.employee_id%TYPE,
            p_name  OUT employees.last_name%TYPE,
            p_salary OUT employees.salary%TYPE) IS
         BEGIN
           SELECT last_name, salary INTO p_name, p_salary
           FROM employees
           WHERE employee_id = p_id;
         END query_emp;
         /
 
Procedure created.

Command> -- Execute the procedure
         DECLARE
          v_emp_name employees.last_name%TYPE;
          v_emp_sal  employees.salary%TYPE;
         BEGIN
          query_emp (171, v_emp_name, v_emp_sal);
          DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
           TO_CHAR (v_emp_sal, '$999,999.00'));
         END;
         /
Smith earns    $7,400.00
 
PL/SQL procedure successfully completed.

Using IN OUT Parameters

Consider a situation where you want to format a phone number. This example takes a 10-character string containing digits for a phone number and passes this unformatted string to a procedure as an IN OUT parameter. After the procedure is executed, the IN OUT parameter contains the formatted phone number value.

Command> CREATE OR REPLACE PROCEDURE format_phone
           (p_phone_no IN OUT VARCHAR2 ) IS
         BEGIN
          p_phone_no := '('  || SUBSTR (p_phone_no,1,3) ||
                        ') ' || SUBSTR (p_phone_no,4,3) ||
                        '-'  || SUBSTR (p_phone_no,7);
         END format_phone;
         /

Procedure created.

Create the bind variable, execute the procedure, and verify the results.

Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';

PL/SQL procedure successfully completed.

Command> PRINT b_phone_no;
B_PHONE_NO           : 8006330575
Command> BEGIN
           format_phone (:b_phone_no);
         END;
         /

PL/SQL procedure successfully completed.

Command> PRINT b_phone_no
B_PHONE_NO           : (800) 633-0575

Using Associative Arrays

This example uses ttIsql to bind a NUMBER array and a VARCHAR2 array to corresponding OUT associative arrays in a PL/SQL procedure.

See Using Associative Arrays from Applications.

Assume the following SQL setup.

DROP TABLE FOO;
 
CREATE TABLE FOO (CNUM INTEGER,
                  CVC2 VARCHAR2(20));
 
INSERT INTO FOO VALUES ( null,
     'VARCHAR  1');
INSERT INTO FOO VALUES (-102,
     null);
INSERT INTO FOO VALUES ( 103,
     'VARCHAR  3');
INSERT INTO FOO VALUES (-104,
     'VARCHAR  4');
INSERT INTO FOO VALUES ( 105,
     'VARCHAR  5');
INSERT INTO FOO VALUES ( 106,
     'VARCHAR  6');
INSERT INTO FOO VALUES ( 107,
     'VARCHAR  7');
INSERT INTO FOO VALUES ( 108,
     'VARCHAR  8');
 
COMMIT;

Assume the following PL/SQL package definition. This includes the INTEGER associative array type NUMARRTYP and the VARCHAR2 associative array type VCHARRTYP, used for output associative arrays c1 and c2, respectively, in the definition of procedure P1.

CREATE OR REPLACE PACKAGE PKG1 AS
  TYPE NUMARRTYP IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
  TYPE VCHARRTYP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP);
 
END PKG1;
/
 
CREATE OR REPLACE PACKAGE BODY PKG1 AS
 
  CURSOR CUR1 IS SELECT CNUM, CVC2 FROM FOO;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP) IS
  BEGIN
    IF NOT CUR1%ISOPEN  THEN
      OPEN CUR1;
    END IF;
    FOR i IN 1..8 LOOP
      FETCH CUR1 INTO c1(i), c2(i);
      IF CUR1%NOTFOUND THEN
        CLOSE CUR1;
        EXIT;
      END IF;
    END LOOP;
  END P1;
 
END PKG1;

Now ttIsql calls PKG1.P1, binds arrays to the P1 output associative arrays, and prints the contents of those associative arrays.

Command> var c1[10] number;
Command> var c2[10] varchar2(20);
Command> print;
C1                   : ARRAY [ 10 ] (Current Size 0)
C2                   : ARRAY [ 10 ] (Current Size 0)
Command> BEGIN PKG1.P1(:c1, :c2); END; /
 
PL/SQL procedure successfully completed.
 
Command> print
C1                   : ARRAY [ 10 ] (Current Size 8)
C1[1] : <NULL>
C1[2] : -102
C1[3] : 103
C1[4] : -104
C1[5] : 105
C1[6] : 106
C1[7] : 107
C1[8] : 108
C2                   : ARRAY [ 10 ] (Current Size 8)
C2[1] : VARCHAR  1
C2[2] : <NULL>
C2[3] : VARCHAR  3
C2[4] : VARCHAR  4
C2[5] : VARCHAR  5
C2[6] : VARCHAR  6
C2[7] : VARCHAR  7
C2[8] : VARCHAR  8