PLSQL Variable Attributes

Variable Attributes is also known as Anchor Notations, it is used to place of datatypes in variable declaration or procedure parameter declaration in PLSQL blocks.

There are two types of variable attributes supported by PLSQL

  1. Column Level Attributes
  2. Row Level Attributes

Column Level Attributes:

In this method we are defining attributes for individual columns, this attribute is represented by “%Type”

Example with syntax:

VariableName TableName.ColumnName%Type;

Whenever we are using this attribute PLSQL runtime engine allocates memory for this variable corresponding to the columns in database tables.

Example with Syntax:

DECLARE

a emp.ename%type;

b emp.sal%type;

BEGIN

Select ename, sal

Into a, b from emp

Where empno = 2837;

dbms_output.put_line(a||’ ’||b);

END;

Row Level Attributes:

In this method a single variable represents all different datatypes in entire row in a table that’s why this variable is also called as record type variable.

This variable is represented using “%rowtype”

Example with Syntax:

VariableName TableName%Rowtype;

Example with syntax:

DECLARE

i emp%rowtype;

BEGIN

Select ename, hiredate, sal

Into i.ename, i.hiredate, i.sal

From emp

Where empno=7839;

Dbms_output.put_line(i.ename||’ ’||i.hiredate||’ ’||i.sal);

END;

Output=> KING 17-NOV-81 5000