Variables and Constants

Variables and constants are used to hold and manipulate the values with in the PL/SQL. In the declare section of a block you declare the variables and its data type.

suppose we want to hold the customer number in a block then how we declare a variable?

first we should know the data type of customer number, whether its a number data type or char data type. If its a number data type then

var_custno  NUMBER(5);

to declare a constant value 
ticket_price CONSTANT number(4) := 150;

SQL data types - CHAR, DATE, NUMBER, VARCHAR
PL/SQL data types - BOOLEAN, BINARY_INTEGER, EXCEPTION

Example for %TYPE

What is the use of %TYPE declaration in PL/SQL students instead of hardcoding the datatype?

DECLARE
      var_custno number(3);
      var_custname varchar(100);
BEGIN
      Select customer_name into var_custname from customer where customer_no = var_custno;
      dbms_output.put_line('Customer Name is ' || var_custname);
EXCEPTION
      WHEN no_data_found then
      dbms_output.put_line('Customer does not exists in Customer table');
END;

If this code is in the database (which is in production), after some days if they changed the customer_no column from number(3) to number(4) then your program should change otherwise you will end up getting invalid number error while trying to execute.

If we could use %TYPE instead of number(3) then we dont need to change the program, just changing the datatype in table automatically reflects in the PL/SQL block as it gets the datatype dynamically from the table when the PL/SQL block gets executed.

DECLARE
    var_custno CUSTOMER.CUSTOMER_NO%TYPE;
    var_custname CUSTOMER.CUSTOMER_NAME%TYPE;
BEGIN
    Select customer_name into var_custname from customer where customer_no = var_custno;
    dbms_output.put_line('Customer Name is ' || var_custname);
EXCEPTION
    WHEN no_data_found then
     dbms_output.put_line('Customer does not exists in Customer table');
END;

Example for %ROWTYPE

In the %ROWTYPE we can assign the whole selected row from a table to that variable. The ROWTYPE variable have as many columns in the table from which the ROW is defined.

Main use of this ROWTYPE is, we can pass the whole row to a function or procedure instead of passing all the columns as seperate arguments, so that maintenance will be easiar.

Declaring the ROWTYPE variable.

TABLENAME%ROWTYPE;

Example

DECLARE
        var_custno CUSTOMER.CUSTOMER_NO%TYPE;
        var_custrec CUSTOMER%ROWTYPE;
BEGIN
        var_custno := &CustomerNumber;
        SELECT * into var_custrec FROM customer WHERE customer_no = var_custno;
        dbms_output.put_line( var_custrec.customer_name || ' , ' || var_custrec.cust_addr);
EXCEPTION
        WHEN no_data_found then
         dbms_output.put_line( 'No data found for the Customer no you entered');
END;

Record Datatype

A record in PL/SQL is nothing but a variable which includes more than one datatype. First we have to declare the record type data type and then assign then declare a variable of that type so that we can use it in the block.

So there are two steps and the syntax is

• TYPE rec_datatype IS RECORD ( var_name1 datatype, var_name2 datatype,....)
• var_rec rec_datatype

Example for RECORD type

DECALRE
       TYPE custinforec IS RECORD ( var_custno customer.cust_no%TYPE, var_custname 
                                                           customer.cust_name%TYPE);
       var_custrec custinforec;
BEGIN
        SELECT cust_no, cust_name into var_custrec FROM customer WHERE cust_no = 1123;
         dbms_output.put_line(var_custrec.cust_no);
EXCEPTION
         WHEN no_data_found then
          dbms_output.put_line('No data found for the query');
END;

Variable Scope

DECLARE -- Outermost block
   var_customer number(4);
   BEGIN
   -- In this block we can see var_customer variable which we declared in this block
   DECLARE -- inner block
       var_innername varchar(20);
   BEGIN
       -- In this block we can see var_innernmae variable as well as var_customer which is declared in the
       outermost block. We cannot refer var_inexception variable from this block becase its in a different
       block. We can see the current blocks variables as well as outer block variables
    EXCEPTION 
       -- Error handling for the inner block
    END;
EXCEPTION
    WHEN an exception occurs
    DECLARE
           var_inexception number(4); begin -- another block
    BEGIN
           -- In this block we can refer var_inexception variables as well as var_customer which is declared in
            the outermost block. We cannot refer var_innername variable from this block because its in a
            different block.
    EXCEPTION
           -- Error handling for the exception block i.e current block
    END;
END;