oracle %type %rowtype,oracle %type %rowtype

Using the %TYPE Attribute

The%TYPEattribute

provides the datatype of a variable or database column. As shown

inExample

2-6, variables

declared with%TYPEinherit

the datatype of a variable, plus default values and

constraints.

Example 2-6 Using

%TYPE With the Datatype of a Variable

DECLARE

credit PLS_INTEGER RANGE 1000..25000;

debit credit%TYPE;

v_name VARCHAR2(20);

name VARCHAR2(20) NOT NULL := 'JoHn

SmItH';

-- If we increase the length of NAME, the other variables become

longer also

upper_name name%TYPE := UPPER(name);

lower_name name%TYPE := LOWER(name);

init_name name%TYPE := INITCAP(name);

BEGIN

-- display inherited default values

DBMS_OUTPUT.PUT_LINE('name: ' || name || '

upper_name: ' || upper_name

|| ' lower_name: ' || lower_name || ' init_name: ' ||

init_name);

-- lower_name := 'jonathan henry smithson'; invalid, character

string is too long

-- lower_name := NULL; invalid, NOT NULL CONSTRAINT

-- debit := 50000; invalid, value out of range

END;

/

Note that

variables declared using%TYPEare

treated like those declared using a datatype specifier. For

example, given the previous declarations, PL/SQL

treatsdebitlike

aPLS_INTEGERvariable.

A%TYPEdeclaration

can also include an initialization clause.

The%TYPEattribute

is particularly useful when declaring variables that refer to

database columns. You can reference a table and column, or you can

reference an owner, table, and column, as in:

DECLARE

-- If the length of the column ever changes, this code

-- will use the new length automatically.

the_trigger

user_triggers.trigger_name%TYPE;

When you

usetable_name.column_name.%TYPEto

declare a variable, you do not need to know the actual datatype,

and attributes such as precision, scale, and length. If the

database definition of the column changes, the datatype of the

variable changes accordingly at run time.

However,%TYPEvariables

do not inherit column constraints, such as theNOTNULLor

check constraint, or default values. For example, even though the

database columnempidis

defined asNOTNULLinExample

2-7, you can

assign aNULLto

the variablev_empid.

Example 2-7 Using

%TYPE With Table Columns

CREATE TABLE employees_temp (empid NUMBER(6) NOT

NULL PRIMARY KEY,

deptid NUMBER(6) CONSTRAINT check_deptid CHECK

(deptid BETWEEN 100 AND 200),

deptname VARCHAR2(30) DEFAULT 'Sales');

DECLARE

v_empid employees_temp.empid%TYPE;

v_deptid employees_temp.deptid%TYPE;

v_deptname

employees_temp.deptname%TYPE;

BEGIN

v_empid :=

NULL; -- this works, null constraint is not

inherited

-- v_empid := 10000002; -- invalid, number precision too

large

v_deptid := 50; -- this works,

check constraint is not inherited

-- the default value is not inherited in the following

DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);

END;

/

See"Constraints

and Default Values With Subtypes"for

information on column constraints that are inherited by subtypes

declared using%TYPE.

Using the %ROWTYPE Attribute

The%ROWTYPEattribute

provides a record type that represents a row in a table or view.

Columns in a row and corresponding fields in a record have the same

names and datatypes. However, fields in a%ROWTYPErecord

do not inherit constraints, such as theNOTNULLor

check constraint, ordefault

values, as shown inExample

2-8. See

alsoExample

3-11.

Example 2-8 Using

%ROWTYPE With Table Rows

DECLARE

emprec employees_temp%ROWTYPE;

BEGIN

emprec.empid :=

NULL; -- this works, null constraint is not

inherited

-- emprec.empid := 10000002; -- invalid, number precision too

large

emprec.deptid := 50; -- this

works, check constraint is not inherited

-- the default value is not inherited in the following

DBMS_OUTPUT.PUT_LINE('emprec.deptname: ' || emprec.deptname);

END;

/

The record can store an entire row of

data selected from the table, or fetched from a cursor or strongly

typed cursor variable as shown inExample

2-9.

Example 2-9 Using

the %ROWTYPE Attribute

DECLARE

-- %ROWTYPE can include all the columns in a table...

emp_rec

employees%ROWTYPE;

-- ...or a subset of the columns, based on a cursor.

CURSOR c1 IS

SELECT department_id, department_name FROM departments;

dept_rec c1%ROWTYPE;

-- Could even make a %ROWTYPE with columns from multiple

tables.

CURSOR c2 IS

SELECT employee_id, email, employees.manager_id, location_id

FROM employees, departments

WHERE employees.department_id = departments.department_id;

join_rec c2%ROWTYPE;

BEGIN

-- We know EMP_REC can hold a row from the EMPLOYEES table.

SELECT * INTO emp_rec FROM

employees WHERE ROWNUM < 2;

-- We can refer to the fields of EMP_REC using column names

-- from the EMPLOYEES table.

IF emp_rec.department_id = 20

AND emp_rec.last_name = 'JOHNSON' THEN

emp_rec.salary := emp_rec.salary * 1.15;

END IF;

END;

/