PL/SQL Variables

Variables can be any SQL data type such as CHAR, DATE, NUMBER or PL/SQL data type such as BOOLEAN or PLS_INTEGER.


Declare data type for variables in PL/SQL

You can use the special words %ROWTYPE or %TYPE to declare variables that keeps the columns of tables or records of the tables.

%TYPE attribute provides the ability to set the data type of a variable as being the same with the data type of a column from the table.

Example: v_last_name students.last_name% TYPE;

%ROWTYPE attribute offers a type of record that represents a row in a database table. Can store all rows from the table.

1. Declaring Variables in PL/SQL

DECLARE
student_id NUMBER;
first_name VARCHAR2(100);
is_graduate BOOLEAN;
birthday DATE;

2. Variable use example

DECLARE
   var_number NUMBER:=40;
   var_char VARCHAR2(250):='PL/SQL Tutorial';
   emp_rec employees%ROWTYPE;
BEGIN
   DBMS_OUTPUT.PUT_LINE(var_number);
   DBMS_OUTPUT.PUT_LINE(var_char);

   var_char:= UPPER(var_char);
   DBMS_OUTPUT.PUT_LINE(var_char);

   SELECT * INTO emp_rec from employees WHERE id = 1;
   DBMS_OUTPUT.PUT_LINE(emp_rec.id);
   DBMS_OUTPUT.PUT_LINE(emp_rec.name);
END;