PL/SQL Variables

PL/SQL Placeholders

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.

Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below.
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile

PL/SQL Variables

These are placeholders that store the values that can change through the PL/SQL Block.

The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ]; 

• variable_name is the name of the variable.
• datatype is a valid PL/SQL datatype.
• NOT NULL is an optional specification on the variable.
• value or DEFAULT valueis also an optional specification, where you can initialize a variable.
• Each variable declaration is a separate statement and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.

DECLARE

salary  number (6);

* “salary” is a variable of datatype number and of length 6.

When a variable is specified as NOT NULL, you must initialize the variable when it is declared.

For example: The below example declares two variables, one of which is a not null.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below. 

1) We can directly assign values to variables.
    The General Syntax is:         

  variable_name:=  value;

2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement. The General Syntax is: 

SELECT column_name INTO variable_name FROM table_name [WHERE condition];

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.

DECLARE 

 var_salary number(6); 

 var_emp_id number(6) = 1116; 

BEGIN

 SELECT salary 

 INTO var_salary 

 FROM employee 

 WHERE emp_id = var_emp_id; 

 dbms_output.put_line(var_salary); 

 dbms_output.put_line('The employee ' 

   || var_emp_id || ' has  salary  ' || var_salary); 

END; 

/

NOTE: The backward slash '/' in the above program indicates to execute the above PL/SQL Block.

Scope of Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

• Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
• Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.

For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block. 
 

1> DECLARE

2>  var_num1 number; 

3>  var_num2 number; 

4> BEGIN 

5>  var_num1 := 100; 

6>  var_num2 := 200; 

7>  DECLARE 

8>   var_mult number; 

9>   BEGIN 

10>    var_mult := var_num1 * var_num2; 

11>   END; 

12> END; 

13> / 

Numeric variables

variable_name NUMBER(P[,S]) := VALUE;

To define a numeric variable, the programmer appends the variable type NUMBER to the name definition. To specify the (optional) precision(P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. ("Precision" in this context refers to the number of digits which the variable can hold, "scale" refers to the number of digits which can follow the decimal point.)

A selection of other datatypes for numeric variables would include: binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, smallint, binary_integer

Character variables

variable_name VARCHAR2(L) := 'Text';

To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters which the variable can store.

Other datatypes for character variables include: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, bfile

Date variables

variable_name DATE := '01-Jan-2005';

Oracle provides a number of data types that can store dates (DATE, DATETIME, TIMESTAMP etc.), however DATE is most commonly used.

Programmers define date variables by appending the datatype code "DATE" to a variable name. The TO_DATE function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example:

 TO_DATE('31-12-2004','dd-mm-yyyy')

or

 TO_DATE ('31-Dec-2004','dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

To convert the dates to strings one uses the function TO_CHAR (date_string, format_string).

PL/SQL also supports the use of ANSI date and interval literals. The following clause gives an 18-month range:

WHERE dateField BETWEEN DATE '2004-12-31' - INTERVAL '1-6' YEAR TO MONTH
    AND DATE '2004-12-31'

Datatypes for specific columns

Variable_name Table_name.Column_name%type;

This syntax defines a variable of the type of the referenced column on the referenced tables.

Programmers specify user-defined datatypes with the syntax:

type data_type is record (field_1 type_1 :=xyz, field_2 type_2 :=xyz, ..., field_n type_n :=xyz);

For example:

DECLARE
    TYPE t_address IS  RECORD (
        name address.name%TYPE,
        street address.street%TYPE,
        street_number address.street_number%TYPE,
        postcode address.postcode%TYPE);
    v_address t_address;
BEGIN
    SELECT name, street, street_number, postcode INTO v_address FROM address WHERE ROWNUM = 1;
END;

This sample program defines its own datatype, called t_address, which contains the fields name, street, street_number and postcode.

So according to the example, we are able to copy the data from the database to the fields in the program.

Using this datatype the programmer has defined a variable called v_address and loaded it with data from the ADDRESS table.

Programmers can address individual attributes in such a structure by means of the dot-notation, thus: "v_address.street := 'High Street';"