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.
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
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
IN OUTparameter to a subprogram (procedure or function) and then assigning the value inside the subprogram.
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.
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
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
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 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.
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.