Oracle PL/SQL :Declaring Variables
PL/SQL program stores values in variables and constants.You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package.
Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.
As the program executes, the values of variables can change, but the values of constants cannot.
Declaring Variables
To declare simple variable, the format is:
VARIABLE_NAME DATATYPE;
Some examples follow:
DECLARE
v_emp_name VARCHAR2(10);
v_emp_number NUMBER(10);
v_emp_bdate DATE;
BEGIN
–some executable statments using these variables
END;
Assigning values to Variables
To declare some variables and assign values we use := (the assignment operator)
The format is
VARIABLE_NAME DATATYPE := VARIABLE_VALUE;
Some examples follow:
DECLARE
v_emp_name VARCHAR2(20) := ‘PETER’;
v_emp_number NUMBER(10) := 1899891;
BEGIN
–some executable statments using these variables
END;
When the program executes, the values of variables may change.In the below example value of v_emp_number 0 in Declaration,As the program executes the value of v_emp_number change to a value which stored in the database associated with the employee name ‘PETER’
DECLARE
v_emp_name VARCHAR2(20) := ‘PETER’;
v_emp_number NUMBER(10) := 0;
BEGIN
– This Query find employee number associated with the name ‘PETER’ and stores it into variable v_emp_number
SELECT emp_number into v_emp_number
FROM employees
WHERE emp_name=’PETER’;
–This prints the output to the user screen
DBMS_OUTPUT.PUT_LINE(v_emp_number);
END;
OUTPUT
————-
1899891