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

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. %ROWTYPE Attribute

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  w w .  ja va  2  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> 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