PL/SQL Variables

PL/SQL Variables

Variables are the storage locations mainly identified using memory addresses and a specially allocated name. If we define and call a variable, the variable name is used to refer to the storage location and the storage value.

While working in SQL database, various times while executing a query we want to perform some operations like saving some values for later use. But, we cannot perform those operations due to SQL limitations. To solve this issue, we have PL / SQL, whose main purpose is to save objects and then reuse them later, and to perform this operation, variable comes into play.

Variables work as a temporary storage location for data, and we can easily manipulate, save and reuse them whenever we need them. PL / SQL provide us with the feature to send variables as parameters for subprograms.

Types of Variables in PL/SQL

There are different types of variables that PL / SQL provides us to choose out according to our needs or the program's needs.

Every variable must contain a data type to function, and we have to choose which datatype to use and when and also, we have to choose the length of the variables defined.

Note: You can easily learn about data types in the PL/SQL data type section.

Rules for naming a variable and some naming conventions

  • The name of the variable must start with a letter, or else the compiler will give an unexpected error.
  • A variable name can contain some special characters like ( _, #, $, etc. ) anywhere in the name but not at the starting of a variable.
  • Oracle database allows a variable name up to only 30 characters, and anything above 30 will not be considered.
  • Variable name must not consist of any keywords reserved by Oracle, i.e., any data type name should not be used, for example – Select, Boolean, Char, varcahr2, etc.
  • The naming convention is a structured way of writing variables name to make it easy for other programmers to understand the codes easily and to manipulate any code from a different programmer easily.
  • Variable name should be in form V _ VARIABLE _ NAME <-> V _ MAX _ SALARY.
  • Cursor name should be in form CUR _ CURSOR _ NAME <-> CUR _ STUDENT.
  • Exception name should be in form E _ EXCEPTION _ NAME <-> E _ INVALID _ SALARY.
  • Procedure name should be in the form P _ PROCEDURE _ NAME <-> P _ AVERAGE _ SALARY.
  • Bind Variable name should be in form B _ BIND _ NAME <-> B _ EMPLOYEE _ NO.

Declaring and initializing Variables

General usage:-

SYNTAX =>

Name [ CONSTANT ] data type [ NOT NULL ] [ : = DEFAULT value | expression ] ;

In this syntax, first, we have to define the variable name. If the variable is constant, we don't have to write the name, but we have to specify it as constant if we want to protect our variable against any change. If we initialize a variable as a constant, its value cannot be changed until we want to change it.

After the name, we have to define the data type of the variable. The data type can be anything Char, Integer, etc. but cannot be left blank or null.

After the data type, we have to assign a value to the defined variable using the ": =" symbol. We cannot use only "=" in PL / SQL "=" for comparison. If we do not assign any value to the variable, then the default value will be assigned automatically.

Program To Declare Variables

 SET SERVEROUTPUT ON;
 DECLARE
     v varchar2(20) := 2 + 25 * 3;
 BEGIN
     dbms_output.put_line(v);
 END; 

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
77

Here in this program, we have declared a variable V with a data type of VARCHAR2 with a maximum length of 20 and added an arithmetic operation. Later, it called the variable to display the output and compile the equation by giving the output.

Program To Declare Variables With Different Data Types

 DECLARE
     v_text varchar2(50) NOT NULL DEFAULT 'Hello';
     v_number1 number := 50;
     v_number2 number(2) := 50.42;
     v_number3 number(10,2) := 50.42;
     v_number4 PLS_INTEGER := 50;
     v_number5 BINARY_float := 50.42;
     v_DATE1 DATE := sysdate;
     v_DATE2 timestamp := systimestamp;
     v_DATE3 timestamp(9) WITH TIME ZONE := systimestamp;
     v_DATE4 interval day(4) to second (3) := '124 02:05:21.012 ';
     v_DATE5 interval year to month := '12-3';
 BEGIN
     V_TEXT := 'PL/SQL' || 'Course';
     DBMS_OUTPUT.PUT_LINE(V_TEXT);
     DBMS_OUTPUT.PUT_LINE(v_number1);
     DBMS_OUTPUT.PUT_LINE(v_number2);
     DBMS_OUTPUT.PUT_LINE(v_number3);
     DBMS_OUTPUT.PUT_LINE(v_number4);
     DBMS_OUTPUT.PUT_LINE(v_number5);
     DBMS_OUTPUT.PUT_LINE(v_DATE1);
     DBMS_OUTPUT.PUT_LINE(v_DATE2);
     DBMS_OUTPUT.PUT_LINE(v_DATE3);
     DBMS_OUTPUT.PUT_LINE(v_DATE4);
     DBMS_OUTPUT.PUT_LINE(v_DATE5);
     END; 

When we execute the code as mentioned above, the compiler will give the output -

Statement processed.
PL / SQL Course
50
50
50.42
50
5.04199982E+001
20 – DEC - 20
20 – DEC - 20 04.25.14.361358 PM
20 – DEC - 20 04.25.14.361362000 PM + 00:00
+ 0124 02:05:21.012
+ 12 -03

Here in this program, we have declared a variable V with multiple data types and different precisions and later called the variable to display each data type's output.

Local And Global Variables

Global Variable – Any variable that can be accessed from anywhere in the whole program, i.e., the program's scope is Global. In general Global variables are static variables whose life extends till the entire runtime of the program.

Program to show global variables

 DECLARE
   v VARCHAR2(50) := 'Outer !';
 BEGIN
   DECLARE
     v1 VARCHAR2(30) := 'Inner variable';
   BEGIN
     dbms_output.put_line('inside -> ' || v);
     dbms_output.put_line('inside _ inner -> ' || v1);
   END;
   dbms_output.put_line(v);
 END; 

When we execute the code as mentioned above, the compiler will give the output -

 anonymous block completed
 inside -> Outer !
 inside _ inner -> Inner variable
 Outer! 

Here in this program, we have declared a variable V with a data type of VARCHAR2 with a maximum length of 50, and added a value. Later called the variable to display the output and compile the equation by giving the output. Here the variable V is declared in the global scope, and therefore we can use variable V anywhere in the program as per our convenience.

Local variable – When we create an external function or declare a variable in between a pre-defined function, the function creates its personal space, also called a local scope, which is limited to that declared function only. Whenever we declare a variable inside the local scope, it is called a local variable, and the variable is limited to that area only. We cannot use it anywhere in the program.

PROGRAM TO SHOW LOCAL VARIABLES

 SET SERVEROUTPUT ON;
 DECLARE
   v VARCHAR2(50) := 'Outer !';
 BEGIN
   DECLARE -- start of local scope
     v1 VARCHAR2(30) := 'Inner variable';
   BEGIN
     dbms_output.put_line('inside -> ' || v);
     dbms_output.put_line('inside _ inner -> ' || v1);
   END; -- end of local scope
   dbms_output.put_line('Outside _ inner -> ' || v1);
   dbms_output.put_line(v);
 END; 

When we execute the above mentioned code, the compiler will give the output as-

Error starting at line 2 in command:

 DECLARE
   v VARCHAR2(50) := 'Outer !';
 BEGIN
   DECLARE
     v1 VARCHAR2(30) := 'Inner variable';
   BEGIN
     dbms_output.put_line('inside -> ' || v);
     dbms_output.put_line('inside _ inner -> ' || v1);
   END;
   dbms_output.put_line('Outside _ inner -> ' || v1);
   dbms_output.put_line(v);
 END;
 Error report:
 ORA-06550: line 10, column 49:
 PLS-00201: identifier 'V1' must be declared
 ORA-06550: line 10, column 3:
 PL/SQL: Statement ignored
 06550. 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.
 *Action: 

Here in this program, we have declared a variable V with a data type of VARCHAR2, and a maximum length of 50. We have added a value and later called the variable to display the output and compile the equation by giving the output. Here the variable V is declared in the global scope, and therefore we can use variable V anywhere in the program as per our convenience. But the variable V1 is declared inside a local scope; therefore, it can only be used inside local parameters. When we use the local variable V1 after the END command, it gives us an error.

Program to show both global and local variables

 DECLARE
    -- Global variables 
    n_numG1 varchar2(40) := 'I AM A GLOBAL VARIABLE'; 
    n_numG2 number := 25; 
 BEGIN 
    dbms_output.put_line('Outer Variable or Global variable n_numG1: ' || n_numG1);
    dbms_output.put_line('Outer Variable or Global variable n_numG2: ' || n_numG2);
    DECLARE 
       -- Local variables
       n_numL1 varchar2(40) := 'I AM A LOCAL VARIABLE'; 
       n_numL2 number := 225; 
    BEGIN 
       dbms_output.put_line('Inner Variable or Local variable n_numL1: ' || n_numL1);
       dbms_output.put_line('Inner Variable or Local variable n_numL2: ' || n_numL2);
    END; 
 END; 

When we execute the code as mentioned above, the compiler will give the output as-

 Outer Variable or Global variable n_numG1: I AM A GLOBAL VARIABLE
 Outer Variable or Global variable n_numG2: 25
 Inner Variable or Local variable n_numL1: I AM A LOCAL VARIABLE
 Inner Variable or Local variable n_numL2: 225 

Here in this program, we have declared two-variables n_numG1 and n_numG2 with a data type of VARCHAR2 with a maximum length of 40. A second variable with the data type of number and assigned 2 values to both the variables, respectively. These variables are defined in the global scope, and they can be used anywhere in the program. Later we have assigned a PL / SQL block and called the global variable to display the output.

In the same block, we have used 2 more variables, variables n_numL1 and n_numL2 with a data type of VARCHAR2 with a maximum length of 40 and a second variable with the data type of number and assigned 2 values to both the variables, respectively. These variables are defined in the local scope and can only be used in this block of the program.

Program to show both global and local variables

 begin <<outer>>
 DECLARE
   --v_outer VARCHAR2(50) := 'Outer Variable!';
   v_text  VARCHAR2(20) := 'Out-text';
 BEGIN
   DECLARE
     v_text  VARCHAR2(20) := 'In-text';
     v_inner VARCHAR2(30) := 'Inner Variable';
   BEGIN
     --dbms_output.put_line('inside -> ' || v_outer);
     --dbms_output.put_line('inside -> ' || v_inner);
       dbms_output.put_line('inner -> ' || v_text);
       dbms_output.put_line('outer -> ' || outer.v_text);
   END;
   --dbms_output.put_line('inside -> ' || v_inner);
   --dbms_output.put_line(v_outer);
   dbms_output.put_line(v_text);
 END;
 END outer; 

When we execute the code as mentioned above, the compiler will give the output -

 anonymous block completed
 inner -> In-text
 outer -> Out-text
 Out-text 

Here in this program, we have declared two variable V _ text and V _ inner, with a data type of VARCHAR2 with a maximum length of 50 and added a value. Here the variable V _ text is declared in the global scope, and therefore we can use variable V _ text anywhere in the program as per our convenience. But the variable V _ inner is declared inside a local scope; therefore, it can only be used inside local parameters. We used the local variable V _ inner inside the END command.

How to use PL / SQL Variables with SQL Queries?

We can assign SQL queries to PL / SQL variables easily using the SELECT and INTO statement of SQL.

Program to use PL/Sql variables with sql queries

(first, we have to create a SQL table using the below-mentioned commands)

 CREATE TABLE CUSTOMER (
    C_ID   INT NOT NULL,
    C_NAME VARCHAR (20) NOT NULL,
    C_AGE INT NOT NULL,
    C_ADDRESS CHAR (25),
    C_SALARY   DECIMAL (18, 2),       
    PRIMARY KEY (C_ID)
 ); 
PLSQL Variables

(Now we have to add rows to the above-created table customer using INSERT INTO command).

 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (1, 'ABC', 32, 'Ahmedabad', 2000.00 ); 
 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (2, 'DEF', 25, 'Delhi', 1500.00 ); 
 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (3, 'GHI', 23, 'Kota', 2000.00 );
 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (4, 'JKL', 25, 'Mumbai', 6500.00 );
 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (5, 'MNO', 27, 'Bhopal', 8500.00 ); 
 INSERT INTO CUSTOMER (C_ID,C_NAME,C_AGE,C_ADDRESS,C_SALARY)
 VALUES (6, 'PQR', 22, 'MP', 4500.00 ); 
PLSQL Variables

(Now we have to assign the value from table CUSTOMER using the SELECT and INTO commands of SQL to the PL / SQL variable).

 DECLARE
    id customer.c_id%type := 3;
    name  customer.c_name%type;
    addr customer.c_address%type;
    sal  customer.c_salary%type;
 BEGIN
    SELECT c_name, c_address, c_salary INTO name, addr, sal
    FROM customer
    WHERE c_id = id; 
    dbms_output.put_line
    ('Customer ' ||name || ' from ' || addr || ' earns ' || sal);
 END; 

When we execute the above mentioned code, the compiler will give the output as-

 anonymous block completed
 Customer GHI from Kota  
  earns 2000 

Here in this program, we have created a new table CUSTOMER using SQL command CREATE TABLE. Later we have added values to the table CUSTOMER using the INSERT INTO command. For more SQL-related commands, you can prefer our course on SQL DATABASE.

Once we are finished with the table, we take values from table CUSTOMER using SELECT and INTO command and assign the variables to PL / SQL variables.

In this program, we have declared multiple variables with name id, name, address, sal and assign the values using % attribute to inherit the data type values from the main table and assign the primary key that we want to extract. Later in the block section, we have called the PL / SQL variables using the SELECT command and assigned them with values using INTO command. We also have declared the table's name, defined what data to extract, and displayed it.

Bind Variable

While working with SQL statements, in large manipulations, we encounter many similar statements that are executed repeatedly but with different literals. Every time a statement is passed, the compiler process it, and working with a large number of statements and processing all these statements is a mare waste of time. To reduce the efforts and time, we can use bind variables.

Bind variables are placeholders in SQL statements, i.e., they work like variables and must be replaced with an acceptable value or address while the statement is executed.

Bind variable converts simple SQL statements into a parameterized statement that can accept parameters at the execution time.

Bind variables are declared in their host environment outside the declared block. They are also known as host variables or block-free variables. They can easily be changed or manipulated by multiple subprograms. And these bind variables can be declared anywhere in the program and can be called anywhere as they function similar to global scopes as their range is the complete worksheet.

Program to show the use of bind variables inside pl/sql block

 set serveroutput on;
 variable var_text varchar2(30);
 declare
 v_text varchar2(30);
 begin
 :var_text := ' HELLO PL / SQL ';
 v_text := :var_text;
 dbms_output.put_line(v_text);
 dbms_output.put_line(:var_text);
 end; 

When we execute the code as mentioned above, the compiler will give the output -

 anonymous block completed
 HELLO PL / SQL
 HELLO PL / SQL 

Here in this program, we have declared a bind variable var _ text using the data type varchar2 with a maximum length of 30. We have defined a bind variable outside the block. Therefore, it can also be called as a host variable, but we cannot call it a PL / SQL variable. Still, we can easily use this host variable in our PL / SQL blocks. To declare a bind variable, we have to use the keyword variable before declaring the variable name. We have declared the bind variable outside our block and later declared a second variable v _ text with data type varchar2 with maximum length 30. In the main program first, we have called our bind variable var _ text using a colon ( : ) and added a value HELLO PL / SQL and using decelerators we gave declared variable v _ text equals to our bind variable: var _ text.

And later called both the variable and bind variable using DBMS _ OUTPUT. PUT_LINE to get our desired output.

Program to show the use of bind variables outside pl/sql block

 set serveroutput on;
 variable var_text varchar2(30);
 variable var_number number;
 declare
 v_text varchar2(30);
 begin
 :var_text := ' HELLO PL / SQL ';
 v_text := :var_text;
 dbms_output.put_line(v_text);
 dbms_output.put_line(:var_text);
 end;
 /
 Print var_text;
 Print var_number; 

When we execute the code as mentioned above, the compiler will give the output as-

 VAR_TEXT
 ------------
 HELLO PL/SQL
 VAR_NUMBER 

To display the result outside the PL / SQL block, we are provided with function PRINT to get the output outside the PL / SQL block.

Program to show the use of bind variables with sql query

 SET serveroutput ON;
 SET autoprint ON;
 BEGIN
   :var_sql := 100;
 END;
 /
 SELECT * FROM employees WHERE employee_id = :var_sql 

When we execute the code as mentioned above, the compiler will give the output -

PLSQL Variables

Here in this program, we have declared a bind variable var _ sql, and added 100 to it. Later in the program, we have added an SQL query

SELECT * FROM employees WHERE employee_id =:var_sql, select all the employees whose employee _ id is equal to our bind variable. And in the output, we can see our record of the employee with an id of 100.