Declaring and Setting Variables in Oracle Subprograms - Oracle

Local variables are often required to serve as temporary placeholders when carrying out tasks within a subprogram. This section shows you how to both declare variables and assign values to variables. 

Declaring Variables

Unlike PHP, you must declare local variables within a subprogram before using them, specifying their type by using one of Oracle’s supported datatypes. Variable declaration is achieved with theDECLAREsection of the PL/SQL subprogram or anonymous block, and its syntax looks like this:

DECLARE variable_name1type [:= value]; 
        variable_name2type [:= value];
        . . .

Here is a declaration section for a procedure that initializes some values for the area of a circle:

DECLARE
   pi       REAL := 3.141592654;
   radius   REAL := 2.5;
   area     REAL := pi * radius**2;
BEGIN
   . . .

There are a few things to note about this example. Variable declarations can refer to other variables already defined. In the previous example, the variableareais initialized to the area of a circle with a radius of 2.5. Note also the datatypeREAL; it is one of PL/SQL’s internal datatypes not available for Oracle table columns but is provided as a floating-point datatype within PL/SQL to improve the performance of PL/SQL subprograms that require many high-precision floating point calculations.

Also note that by default any declared variable can be changed within the procedure. If you don’t want the application to change the value ofpi, you can add theCONSTANTkeyword as follows:

pi     CONSTANT REAL := 3.141592654;

Setting Variables

You use the:=operator to set the value of a declared subprogram variable. Its syntax looks like this:

variable_name := value;

Here are a couple of examples of assigning values in the body of the subprogram:

BEGIN
   radius := 7.7;
   area := pi * radius**2;
   dbms_output.put_line
      ('Area of circle with radius: ' || radius || ' is: ' || area);

It’s also possible to set variables from table columns using aSELECT INTOstatement. The syntax is identical to aSELECTstatement you might run in SQL Commands or SQL*Plus but with the addition of theINTO variable_name clause to specify which PL/SQL variable will contain the table column’s value. We use this construct to retrieve the employee’s last name in theraise_salaryprocedure created earlier in the chapter:

SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;