Oracle PL/SQL Variable

In Oracle PL/SQL a variable is an identifier used as a storage area that our programs can manipulate. The layout of the variable memory is determines based on the data type and its size. It also defines the range of values that can be stored within that memory and the set of operations that can be applied to the variable. The Structure for a PL/SQL block is,

DECLARE
   -- declaration section
   <declaration statements>
BEGIN
   -- executable section
   <executable statements>
EXCEPTIONS
   -- exception handling section
   <exception handling statements>
END;

All the variables which will be used in the PL/SQL program must be declared in the declaration section of the PL/SQL block or inside a package as a global variable. The value of the variable can be assigned either in the DECLARE section or in the EXECUTABLE section of the PL/SQL block.

Declare Variable in PL/SQL

The variables are declared in the DECLARE section of the PL/SQL block. When we declare a variable, PL/SQL allocates memory for the variable’s value and the storage location is identified by the variable name.

The syntax to declare a variable:-
var_name datatype (size);

To store a value into a variable we have to use the assignment operator (:=). We can store variable value at the time of declaration in the DECLARE section or after the declaration in the EXECUTABLE section of the PL/SQL block.

Syntax to store value at variable declaration:-
var_name datatype (size) := value;

Syntax to store value after declaration:-
var_name := value;

Example of assigning value at the time of declaration,

a NUMBER(10):= 10;

Example of assigning value after declaration,

DECLARE
   a NUMBER(10);
BEGIN
   a := 50;
END;

Print Variable in PL/SQL

dbms_output.put_line( variableName );

To display the variable, we can use put_line() procedure of dbms_output package. When server output is on then it will display the values to the screen. Example1:-

DECLARE
   n NUMBER(10);
BEGIN
   n := 9;
   dbms_output.put_line(n);
END;

Execution,

SQL> set serveroutput on;
SQL> /

Output:-

9

PL/SQL procedure successfully completed.

Example2:- Write a PL/SQL program to display ‘Hello, World!’ message by using a variable.

DECLARE
   message VARCHAR2(15);
BEGIN
   message := 'Hello, World!';
   dbms_output.put_line(message);
END;

Execution,

SQL> /

Output:-

Hello, World!

PL/SQL procedure successfully completed.

Example3:- Write a PL/SQL program to add two numbers and display its sum, take values at run time. (To take values at runtime use &)

DECLARE
   a NUMBER(10);
   b NUMBER(10);
   c NUMBER(10);
BEGIN
   -- take input
   a := &a_value;
   b := &b_value;
   c := a + b;
   dbms_output.put_line('Sum = ' || c);
END;

Execution,

SQL> /

Output:-

Enter value for a_value: 10
old 7: a := &a_value;
new 7: a := 10;
Enter value for b_value: 20
old 8: b := &b_value;
new 8: b := 20;
Sum = 30

PL/SQL procedure successfully completed.

PL/SQL Constant

The constant values in PL/SQL are declared by using CONSTANT keyword. Along with CONSTANT keyword we can also use NOT NULL optional constraint.

Syntax:
varname CONSTANT datatype(size) [ NOT NULL ] := value;

The NOT NULL is an optional constraint used with the CONSTANT keyword to prevent the constant variable from storing NULL value or an empty String. The constant value must be assigned at the time of variable declaration in the DECLARE section of the PL/SQL block. Example:-

DECLARE
   a CONSTANT NUMBER(10) NOT NULL := 10;
BEGIN
   dbms_output.put_line('a = ' || a);
END;

Execution,

SQL> /

Output:-

a = 10

PL/SQL procedure successfully completed.

Since the variables are constant so we can’t change their value after assigning value in the declaration section.

DECLARE
   a CONSTANT NUMBER(10) NOT NULL := 10;
BEGIN
   -- we can't assign input value
   a = &a; -- error
   dbms_output.put_line('a = ' || a);

   -- we can't change the existing value
   a = 100;  -- error
   dbms_output.put_line('a = ' || a);
END;

Example2:- Write a PL/SQL program to calculate area of circle. Use PI value as constant.

DECLARE
   r FLOAT(10);
   area FLOAT(10);
   PI CONSTANT FLOAT NOT NULL := 3.14;
BEGIN
   r := &radius; 
   area := PI * r * r;
   dbms_output.put_line('Area = ' || area);
END;

Execution,

SQL> /

Output:-

Enter value for radius: 5
old 6: r := &radius;
new 6: r := 5;
Area = 78.5

PL/SQL procedure successfully completed.

SELECT INTO Clause

The SELECT INTO clause is used to retrieve data from the table and store it into PL/SQL variables. It always returns a single record or single value at a time. It is used in the executable section of the PL/SQL block. Syntax:-

SELECT col1, col2, …, col_N
INTO var1, var2, …, var_N
FROM tableName
WHERE condition;

Example1) Write a PL/SQL program for user entered employee number that display name of employee and his salary from emp table.

DECLARE
   -- declare variables
   name VARCHAR2(15);
   salary NUMBER(10);
BEGIN
   -- fetch data and store into variables
   SELECT ename, sal
   INTO name, salary
   FROM emp
   WHERE empno = &empno;

   -- display variable value
   dbms_output.put_line(name || ' ' || salary);
END;

Execution,

SQL> /

Output:-

Enter value for empno: 7788
old 10: WHERE empno = &empno;
new 10: WHERE empno = 7788;
SCOTT 3000

PL/SQL procedure successfully completed.

Note1:- Whenever PL/SQL block contains “SELECT … INTO clause” and also through that if requested data is not available in a table through this clause then the Oracle server returns an error:- ora-1403: no data found

SQL> /
Enter value for empno: 1234
old  10:    WHERE empno = &empno;
new  10:    WHERE empno = 1234;
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

There is no any employee having empno=1234, that’s why we get an error:- ora-1403: no data found

But whenever PL/SQL block having pure DML statements and also if the requested data is not available in a table through DML statements then the Oracle server doesn’t return any error. For handling these types of blocks we are using implicit cursor attributes. Example:- The emp table doesn’t have any employees having the name ‘Hello’.

BEGIN
   DELETE FROM emp
   WHERE ename = 'Hello';
END;

SQL> /
PL/SQL procedure successfully completed.

Example2) Write a PL/SQL program to retrieve max salary from emp table, store salary into variable and display that salary.

DECLARE
   -- declare variable
   salary NUMBER(10);
BEGIN
   -- fetch data and store into variable
   SELECT max(sal)
   INTO salary
   FROM emp;

   -- display variable value
   dbms_output.put_line('Max sal = ' || salary);
END;

Execution,

SQL> /

Output:-

Max sal = 5000

PL/SQL procedure successfully completed.

Note2:- In Pl/SQL expression we are not allowed to use group function, decode() conversion function. But we are allowed to use number function, character function, date function, date conversion function in PL/SQL expression.

Example3) Write a PL SQL program to display the maximum of two numbers using pre-defined function.

DECLARE
   -- declare variable
   a NUMBER(10);
   b NUMBER(10);
   c NUMBER(10);
BEGIN
   -- take input
   a := &a;
   b := &b;

   -- find greatest value
   c := greatest(a, b);

   -- display result
   dbms_output.put_line('Greatest value = ' || c);
END;

Execution,

SQL> /

Output:-

Enter value for a: 15
Enter value for b: 25
Greatest value = 25

PL/SQL procedure successfully completed.

In Oracle SQL greatest() or max() predefined functions can be used to find the largest of two numbers. But since we are writing PL/SQL program so we can’t use the max() function which is a group or aggregate function. We must use the number function greatest().

Example4) Write a PL SQL program to display lowercase letter to uppercase letter.

DECLARE
   -- declare variable
   lower_string VARCHAR2(30);
   upper_string VARCHAR2(30);
BEGIN
   -- take input
   lower_string := '&string';

   -- convert to uppercase
   upper_string := upper(lower_string);

   -- display result
   dbms_output.put_line(upper_string);
END;

SQL> /

Variable Attributes

The variable attributes are used in place of datatypes in variable declaration. Whenever we are using a variable attribute then the Oracle server automatically allocates memory for the variable as same as the corresponding column datatype in the table. The variable attribute is also called anchor notation.

PL/SQL having two types of variable attributes. These are:
1) Column level attribute (%TYPE)
2) Row-level attribute (%ROWTYPE)
Both TYPE and ROWTYPE are keywords.

Column level attribute:- In the column level attribute, we are defining attributes for the individual column. The column level attribute is represented by using %TYPE. Whenever we are using a column-level attribute then the oracle server automatically allocates the same memory for variables based on the corresponding column datatype in a table. Syntax:-

variablename tablename.columnname%TYPE;

Example:-

DECLARE
   -- declare variable
   name emp.ename%TYPE;
   salary emp.sal%TYPE;
   hiredate emp.hiredate%TYPE;
BEGIN
   -- fetch data and assign to variable
   SELECT ename, sal, hiredate
   INTO name, salary, hiredate
   FROM emp WHERE empno = &empno;

   -- display result
   dbms_output.put_line(name || ' ' || salary || ' ' || hiredate);
END;

Execution,

SQL> /

Output:-

Enter value for empno: 7788
SCOTT 3000 19-APR-87

PL/SQL procedure successfully completed.

Row-level attribute:- In row-level attribute, a single variable can represent all different datatype in a row within a table. The row-level attributes are represented by using %ROWTYPE. It is also called as a record type variable. We can say that it is similar to structures in C language. Syntax:-

variablename tablename%ROWTYPE;

Here we can retrieve the column data using variablename.columnname;

Example:-

DECLARE
   -- declare variable
   i emp%ROWTYPE;
BEGIN
   -- fetch data and assign to variable
   SELECT ename, sal, hiredate
   INTO i.ename, i.sal, i.hiredate
   FROM emp WHERE empno = &empno;

   -- display result
   dbms_output.put_line(i.ename || ' ' || i.sal || ' ' || i.hiredate);
END;

Execution,

SQL> /

Output:-

Enter value for empno: 7788
SCOTT 3000 19-APR-87

PL/SQL procedure successfully completed.

Or,

DECLARE
   -- declare variable
   i emp%ROWTYPE;
BEGIN
   -- fetch data and assign to variable
   SELECT * INTO i FROM emp
   WHERE empno = &empno;

   -- display result
   dbms_output.put_line( i.ename || ' ' || i.sal || ' ' || 
                         i.hiredate || ' ' || i.deptno );
END;

Note:- In PL/SQL block whenever the SELECT INTO clause tries to return multiple records or try to return multiple values in a column at a time then the Oracle server returns an error:- ORA-1422: exact fetch returns more than requested number of rows. Example:-

DECLARE
   i emp%rowtype;
BEGIN
   SELECT * INTO i FROM emp
   WHERE deptno = 10;
END;

Execution,

SQL> /
DECLARE
 *
 ERROR at line 1:
 ORA-01422: exact fetch returns more than 
 requested number of rows
 ORA-06512: at line 4

In the emp table, there are many employees working in deptno=10, so our PL/SQL program tries to select multiple rows that’s why the Oracle server gives an error.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!