PL/SQL Variables and Constants

You can define variables and constants in PL/SQL and then use them in procedural statements and in SQL anywhere an expression can be used.

For example:

Command> DECLARE
           v_hiredate DATE;
           v_deptno   NUMBER (2) NOT NULL := 10;
           v_location VARCHAR2 (13) := 'San Francisco';
           c_comm     CONSTANT NUMBER := 1400;

You can use the %TYPE attribute to declare a variable according to either a TimesTen column definition or another declared variable. For example, use %TYPE to create variables emp_lname and min_balance:

Command> DECLARE
           emp_lname  employees.last_name%TYPE;
           balance    NUMBER (7,2);
           min_balance  balance%TYPE:= 1000;
         BEGIN
           SELECT last_name INTO emp_lname FROM employees WHERE employee_id = 100;
           DBMS_OUTPUT.PUT_LINE (emp_lname);
           DBMS_OUTPUT.PUT_LINE (min_balance);
         END;
         /
King
1000
 
PL/SQL procedure successfully completed.

You can assign a value to a variable in the following ways.

• With the assignment operator (:=).

• By selecting or fetching values into it.

• By passing the variable as an OUT or IN OUT parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram.

Note:

The DBMS_OUTPUT package used in these examples is supplied with TimesTen. For information on this and other supplied packages, refer to TimesTen Supplied PL/SQL Packages.

This example assigns a value to a variable with the assignment operator:

Command> DECLARE -- Assign values in the declarative section 
           wages NUMBER;
           hours_worked NUMBER := 40;
           hourly_salary NUMBER := 22.50;
           bonus NUMBER := 150;
           country VARCHAR2(128);
           counter NUMBER := 0;
           done BOOLEAN;
           valid_id BOOLEAN;
           emp_rec1 employees%ROWTYPE;
           emp_rec2 employees%ROWTYPE;
           TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
           comm_tab commissions;
         BEGIN -- Assign values in the executable section
           wages := (hours_worked * hourly_salary) + bonus;
           country := 'France';
           country := UPPER('Canada');
           done := (counter   100);
           valid_id := TRUE;
           emp_rec1.first_name := 'Amy';
           emp_rec1.last_name := 'Feiner';
           emp_rec1 := emp_rec2;
           comm_tab(5) := 20000 * 0.15;
         END;
         /
 
PL/SQL procedure successfully completed.

Note:

This example uses records, which are composite data structures that have fields with different data types. You can use the %ROWTYPE attribute, as shown, to declare a record that represents a row in a table or a row from a query result set. Records are further discussed under PL/SQL Composite Data Types.

The next example assigns a value to a variable by selecting or fetching values into it

Select 10% of an employee's salary into the bonus variable:

Command>  DECLARE
            bonus NUMBER(8,2);
            emp_id NUMBER(6) := 100;
          BEGIN
            SELECT salary * 0.10 INTO bonus FROM employees
              WHERE employee_id = emp_id;
            DBMS_OUTPUT.PUT_LINE (bonus);
          END;
          /
2400
 
PL/SQL procedure successfully completed.

The following example assigns a value to a variable by passing the variable as an OUT or IN OUT parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram.

Declare the variable new_sal and then pass the variable as a parameter (sal) to procedure adjust_salary. Procedure adjust_salary computes the average salary for employees with job_id='ST_CLERK' and then updates sal. After the procedure is executed, the value of the variable is displayed to verify that the variable was correctly updated.

Command> DECLARE
           new_sal NUMBER(8,2);
           emp_id NUMBER(6) := 126;
         PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
           emp_job VARCHAR2(10);
           avg_sal NUMBER(8,2);
         BEGIN
           SELECT job_id INTO emp_job FROM employees
             WHERE employee_id = emp_id;
           SELECT AVG(salary) INTO avg_sal FROM employees
             WHERE job_id = emp_job;
           DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job
             || ' employees: ' || TO_CHAR(avg_sal));
           sal := (sal + avg_sal)/2;
           DBMS_OUTPUT.PUT_LINE ('New salary is ' || sal);
         END;
         BEGIN
           SELECT AVG(salary) INTO new_sal FROM employees;
           DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
             || TO_CHAR(new_sal));
           adjust_salary(emp_id, new_sal);
           DBMS_OUTPUT.PUT_LINE ('Salary should be same as new salary ' ||
             new_sal);
         END;
         /
The average salary for all employees: 6461.68
The average salary for ST_CLERK employees: 2785
New salary is 4623.34
Salary should be same as new salary 4623.34
 
PL/SQL procedure successfully completed.

Note:

This example illustrates the ability to nest PL/SQL blocks within blocks. The outer anonymous block contains an enclosed procedure. This PROCEDURE statement is distinct from the CREATE PROCEDURE statement documented in PL/SQL Procedures and Functions, which creates a subprogram that remains stored in the user's schema.