Oracle PL/SQL - Assigning Values to Variables as Parameters of a Subprogram

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Variable Declarations

Introduction

If you pass a variable to a subprogram as an OUT or IN OUT parameter, and the subprogram assigns a value to the parameter.

The variable retains that value after the subprogram finishes running.

The following code passes the variable new_sal to the procedure update_sal.

The procedure assigns a value to the corresponding formal parameter, sal.

Because sal is an IN OUT parameter, the variable new_sal retains the assigned value after the procedure finishes running.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- ww w  . jav a  2  s  .  com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Jack', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL> DECLARE
  2    emp_salary  NUMBER(8,2);
  3
  4    PROCEDURE update_sal (
  5      emp        NUMBER,
  6      sal IN OUT NUMBER,
  7      adjustment NUMBER
  8    ) IS
  9    BEGIN
 10      sal := sal + adjustment;
 11    END;
 12
 13  BEGIN
 14    SELECT salary INTO emp_salary
 15    FROM emp
 16    WHERE empid = 100;
 17
 18    DBMS_OUTPUT.PUT_LINE ('Before invoking procedure, emp_salary: ' || emp_salary);
 19
 20    update_sal (100, emp_salary, 1000);
 21
 22    DBMS_OUTPUT.PUT_LINE ('After invoking procedure, emp_salary: ' || emp_salary);
 23  END;
 24  /
Before invoking procedure, emp_salary: 24000
After invoking procedure, emp_salary: 25000

PL/SQL procedure successfully completed.

SQL>

Related Topic