Oracle PL/SQL - UPDATE Statement Assigns Values to Record Variable

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

SQL Statements can Return Rows in PL/SQL Record Variables.

The SQL statements INSERT, UPDATE, and DELETE have an optional RETURNING INTO clause that can return the affected row in a PL/SQL record variable.

In the following code, the UPDATE statement updates the salary of an employee and returns the name and new salary of the employee in a record variable.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w w  w . j av a2  s. c o m

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, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL>
SQL> DECLARE
  2    TYPE EmpRec IS RECORD (
  3      last_name  emp.last_name%TYPE,
  4      salary     emp.salary%TYPE
  5    );
  6    emp_info    EmpRec;
  7    old_salary  emp.salary%TYPE;
  8  BEGIN
  9    SELECT salary INTO old_salary
 10     FROM emp
 11     WHERE empid = 100;
 12
 13    UPDATE emp
 14      SET salary = salary * 1.1
 15      WHERE empid = 100
 16      RETURNING last_name, salary INTO emp_info;
 17
 18    DBMS_OUTPUT.PUT_LINE (
 19      'Salary of ' || emp_info.last_name || ' raised from ' ||
 20      old_salary || ' to ' || emp_info.salary
 21    );
 22  END;
 23  /
Salary of King raised from 24000 to 26400

PL/SQL procedure successfully completed.

SQL>

Related Topic