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
We will be using Oracle 9i, in our tutorials for PL/SQL.
* 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.
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.
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;
Constants are declared by using the keyword constant in the declaration of the variable.
* 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.
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.
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 :
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 is used to declare a record with the same types as found in the specified database table, view or cursor.
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.
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).
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.
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.
Here the value when asked by the user in the prompt, will be assigned to new_empno variable.