PL/SQL Variable Attributes

PL/SQL variables can be declared in two ways. First one is by using the in-built data types. Second method is by using existing database objects in variable declaration instead of data type. The second method is the topic of discussion of this post. So, let’s learn about PL/SQL Variable Attributes.

PL/SQL Variable Attributes

There are two types of PL/SQL variable attributes used in PL/SQL codes-  %TYPE and %ROWTYPE

%TYPE

%TYPE is used when you want to declare a variable with the data type of an existing variable or a column of a table. It is also known as anchored declaration. In this case the data type of a new variable is dependent on another existing object or variable.

PL/SQL programs are written to manipulate data in database. It is possible that table structure may get altered with the changing need of applications. If PL/SQL program variables are declared with inbuilt PL/SQL data types, and the table used in it altered, the program may not run correctly.

It is always advisable to declare the variables in PL/SQL with data types of the table columns. %TYPE is used for this. Any change in the data type of variables previously declared or table columns will be automatically reflected in the %TYPE variables with altering the PL/SQL program.

Syntax

variable_name variable/table-column-name%TYPE;

variable_name  is the name of the variable

variable  is the name of an existing variable whose data-type you want to use to declare  a new variable

table-column-name is the name of a column of an existing table whose data-type you want to use to declare  a new variable

%TYPE is the variable attribute to be written as it is

Example

emp_name varchar2(40);   -a variable is declared using in-built PL/SQL data type

stu_name emp_name%TYPE;  -a variable is declared with data type of an existing variable

stu_name employees.first_name% TYPE – a variable declared with data type of a column of an exiting table

Example

DECLARE
c_id cus_master.code_no%type := 103; 
c_name cus_master.first_name%type; 
BEGIN 
SELECT code_no, first_name INTO c_id, c_name FROM cus_master WHERE code_no = c_id;
 dbms_output.put_line ('Customer with id ' ||c_id || ' is ' || c_name ); 
END;

Output

Using the table data

%ROWTYPE

In database, tables store records. A record can be considered as a composite data type made up of more than one element. A record is a single unit (row) of a table in which each column value is part of a record. In PL/SQL you can use table-based records or cursor-based records.

A table-based record is composed of all the columns of a table. A cursor-based record is composed of the structure of the cursor defined by its underlying SELECT statement. You can create a variable that stores elements of a table-based record or a cursor based record. It is done by using %ROWTYPE attribute.

Syntax

record-name table-name%ROWTYPE |cursor-name%ROWTYPE ;

record-name  is the name of the record variable

table-name is the name of an existing table whose structure you want to use to declare  a record variable

cursor-name is the name of a cursor you have declared in your PL/SQL program whose structure you want to use to declare a record variable. If you are using a cursor-name then you must declare the cursor before declaring the dependent record variable.

%TYPE is the variable attribute to be written as it is

Example

cust_record cus_master%ROWTYPE;

The elements of the records can be used with dot operator(.)

DBMS_OUTPU.PUT_LINE(cust_record.first_name)

With this you have understood how to declare variables from existing variables, table column or tables using PL/SQL Variable Attributes.

admin