Oracle PL/SQL - Record Variable that Can Represent Partial Row

Introduction

To declare a record variable that can represent a partial row of a database table or view, use this syntax:

variable_name cursor%ROWTYPE; 

A cursor is associated with a query.

For every column that the query selects, the record variable would have a corresponding, type-compatible field.

The cursor must be either an explicit cursor or a strong cursor variable.

The following code defines an explicit cursor whose query selects only the columns first_name, last_name, and phone_number from the emp table.

Then the example declares a record variable that has a field for each column that the cursor selects.

The variable represents a partial row of emp.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  ww. j a  v  a2  s .co  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> DECLARE
  2    CURSOR c IS
  3      SELECT first_name, last_name, phone_number
  4      FROM emp;
  5
  6    friend c%ROWTYPE;
  7  BEGIN
  8    friend.first_name   := 'John';
  9    friend.last_name    := 'Smith';
 10    friend.phone_number := '1-650-555-1234';
 11
 12    DBMS_OUTPUT.PUT_LINE (
 13      friend.first_name  || ' ' ||
 14      friend.last_name   || ', ' ||
 15      friend.phone_number
 16    );
 17  END;
 18  /
John Smith, 1-650-555-1234

PL/SQL procedure successfully completed.

SQL>

Related Topic