Variables and Constants in PL/SQL

This is out first of many tutorial in the PL/SQL series. Today we will be discussing about variables and coonstants in PL/SQL.

PL/SQL stands for Procedural Language to Structured Query Language. We can use PL/SQL in the Oracle relational databases, in Oracle Server, and in client-side application development tools, such as Oracle Forms.

PL/SQL has features such as : 

* Data abstraction

* Data encapsulation

* Object-Orientation

* Exception-handling.

We will be using Oracle 9i, in our tutorials for PL/SQL.

PL/SQL is a block-structured language. The PL/SQL block has three parts :

* Declarative Part

* Executable Part

* Exception Handling Part

PL/SQL BLOCK STRUCTURE :

DECLARE
 -- declaration
BEGIN
 -- statements
EXCEPTION
 -- handlers
END;

Variables & Constants

Like other languages have variables, PL/SQL also have variables which help in transmitting information through PL/SQL program and the database.

* Every variable has a specific type associated with it.

In PL/SQL the variables has to be declared in the declaration part before it can be used further in the program.

Declaring Variable 

SYNTAX for declaring variable :

variable_name data_type [:= value]

Some example of valid declaration of variable are : 

* emp_Name VARCHAR2(20);
* empNo NUMBER(4);

Initial value to a variable can also be assigned to a variable at the time of declaration. 

NOTE :
           To assign a value to a variable, the assignment operator has to be used i.e. ':='.
** We can also use the default keyword instead of the ':=' assignment operator.

Example w/o default keyword :

* pinCode NUMBER(6):=400076;

Example with default keyword :

* pinCode NUMBER(6) default 400076;

Declaring Constants

Constants are declared by using the keyword constant in the declaration of the variable.

Example :

* pinCode CONSTANT NUMBER(6) :=400076; 

Data-types for declaration :

We know that there are various kind of data_types available i.e. char, varchar2, number, date etc, but apart from these there are two other kind of data-types.

* %TYPE 
* %ROWTYPE

%TYPE :

We use %TYPE  when we want a declared variable to have a data-type same as that of a column in a table, so as to avoid the data-type conflict and size conflict.

Example :

If we have a table called as emp which stored employee data i.e. employee Name, employee No, employee address, employee DOB etc. So if we want a variable empNo to have the same data-type same as employee No, then we will need to use %TYPE. 

We will declare like below :

empNo emp.employeeNo%TYPE;

The advantage of the use this type of definition for a variable is that, whenever the data-type/size of the column is changed then the variable data-type also gets changed automatically.

%ROWTYPE :

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. 

Examples :

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;

DECLARE
v_EmpRecord emp%ROWTYPE;
BEGIN
SELECT * INTO v_EmpRecord FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpRecord.ename);
DBMS_OUTPUT.PUT_LINE('Salary = ' || v_EmpRecord.sal);
END;

SCOPE & VISIBILITY

The scope and visibility of the variable can be defined as global or local. Global when it is used in the main program and Local when it is used inside the sub-program.

Example :

DECLARE
 v_EmpRecord emp%ROWTYPE;
BEGIN
 SELECT * INTO v_EmpRecord FROM emp WHERE ROWNUM = 1;
 DECLARE
  v_emp emp%ROWTYPE;
 BEGIN
  v_emp.empno := 10;
  v_emp.ename := 'XXXXXXX';
 END;
 DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpRecord.ename);
 DBMS_OUTPUT.PUT_LINE('Salary = ' || v_EmpRecord.sal);
END;

Here v_EmpRecord  is global variable thus will be visible across the whole program and v_emp is local variable thus will be visible in the sub-program.

BIND Variable :

Bind variables are also called as NON PL/SQL variable. Ut can be referenced in PL/SQL statements. 

A bind variable appears in a PL\SQL query as an alphanumeric string preceded by a colon (:var1, :var2, :var3).

Example :

variable v_sal number(4);
DECLARE
new_empno emp.empno%TYPE:='&no';
BEGIN
SELECT sal INTO :v_sal FROM emp WHERE empno=new_empno;
END;

The above example displays the SALARY of the employee when the employee entered by the user matched the employee no stored in the table.

NOTE :

To accept values from the user, we need to use &.

When declaring a variable if we use &  after the assignment operator then we can accept vales from the user. 

The below example shows how we use the & operator to accept values from  user.

new_empno emp.empno%TYPE:='&no';

Here the value when asked by the user in the prompt, will be assigned to new_empno variable.