Oracle PL SQL Tutorial Day 3
PL/SQL (Procedural Language/SQL) is Oracle’s procedural extension for SQL. It allows the use of variables, control structures, loops, and exception handling to create powerful scripts and programs inside Oracle Database.
In this article, we focus on PL/SQL Variables — their declaration, scope, data types, usage, and practical examples. We’ll also cover best practices and real-world applications.
1. What Are Variables in PL/SQL?
Variables are named storage locations used to store data temporarily during the execution of a PL/SQL block.
These variables allow programs to process data dynamically by assigning values, retrieving them, and performing operations.
About variables:
Variables always define inside declaration section of PL SQL. It can be initialize with some value at the time of declaration otherwise it would be defined with NULL value.
Every variable defined has a scope and it wouldn't be available out of its scope. If a variable defined in main declaration section then it will available in whole execution.
Remember we are taking about variable with reference if PL SQL anonymous block, procedure or function. The package has bit different compare to them and we will see in separate topic.
2. Syntax to Declare a Variable
The basic syntax to declare a variable:
variable_name datatye;
Or
variable_name datatype [NOT NULL] [:= initial_value];
Example:
DECLARE
v_name VARCHAR2(50);
v_salary NUMBER := 5000;
Remember always define variable in declaration section. A variable can be defined with or without initial value.
In above example we define two variables one as character other one as numeric with initial value.
We will understand different data types in next section
3. Types of PL/SQL Variables
PL/SQL supports several types of variables:
- Scalar Variables – store a single value (NUMBER, VARCHAR2, DATE)
- Composite Variables – records and collections (RECORD, TABLE)
- Reference Variables – CURSOR and REF CURSOR
- Bind Variables – used in host environment (e.g., SQL*Plus)
- Boolean Variables – TRUE/FALSE/NULL values
4. Scalar Variables – Simple Value Holders
Example 1: Using NUMBER and VARCHAR2 variables
DECLARE
v_name VARCHAR2(100) := 'John';
v_salary NUMBER := 50000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
Explanation:
- v_name holds a string.
- v_salary stores a numeric value.
- Both values are printed using DBMS_OUTPUT.PUT_LINE.
5. Using DATE Variables
Example 2: Assigning and formatting dates
DECLARE
v_hire_date DATE := TO_DATE('2024-01-15', 'YYYY-MM-DD');
BEGIN
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(v_hire_date, 'DD-Mon-YYYY'));
END;
Explanation:
- TO_DATE converts a string into a DATE object.
- TO_CHAR formats the DATE for display.
- Useful for date comparisons and calculations.
6. BOOLEAN Variables
Example 3: Decision based on boolean
DECLARE
v_is_active BOOLEAN := TRUE;
BEGIN
IF v_is_active THEN
DBMS_OUTPUT.PUT_LINE('Status: Active');
ELSE
DBMS_OUTPUT.PUT_LINE('Status: Inactive');
END IF;
END;
Explanation:
- BOOLEAN variables can be TRUE, FALSE, or NULL.
- Used in conditional logic within blocks.
7. Using %TYPE to Inherit Data Types
Example 4: Using %TYPE with table column
DECLARE
v_emp_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;
Explanation:
- v_emp_name inherits the datatype from employees.first_name.
- Reduces errors and keeps declarations in sync with table structure.
----------------------------------------------------
8. Constants in PL/SQL
----------------------------------------------------
Constants are like variables, but their values cannot be changed after initialization.
Example 5: Using a constant
DECLARE
c_bonus_rate CONSTANT NUMBER := 0.10;
v_salary NUMBER := 60000;
v_bonus NUMBER;
BEGIN
v_bonus := v_salary * c_bonus_rate;
DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
Explanation:
- c_bonus_rate is a constant set to 10%.
- It is used in bonus calculation.
9. Variable Scope in PL/SQL
Scope determines where a variable can be accessed:
- Local Scope: Declared within a block.
- Inner blocks can access outer variables, but not vice versa.
Example 6: Nested scope
DECLARE
v_outer NUMBER := 10;
BEGIN
DECLARE
v_inner NUMBER := 20;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer: ' || v_outer); -- Accessible
DBMS_OUTPUT.PUT_LINE('Inner: ' || v_inner);
END;
-- DBMS_OUTPUT.PUT_LINE(v_inner); -- Error: not accessible here
END;
Explanation:
- v_inner is only available inside the inner block.
- v_outer is accessible from both blocks.
10. Assigning Values to Variables
Variables can be assigned:
- Directly during declaration
- With the := operator
- Using the SELECT INTO syntax
Example 7: SELECT INTO usage
DECLARE
v_dept_name departments.department_name%TYPE;
BEGIN
SELECT department_name INTO v_dept_name FROM departments WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
END;
Explanation:
- SELECT INTO fetches a value into a variable.
- The query must return exactly one row.
11. Best Practices for Using Variables
- Use meaningful names (e.g., v_total_salary instead of x).
- Initialize variables whenever possible.
- Use %TYPE and %ROWTYPE to match database structures.
- Use constants for fixed values.
- Avoid using uninitialized variables in expressions.
12. Debugging with Variables
- Print values at different points using DBMS_OUTPUT.
- Track values before and after computations.
- Use conditional prints based on status variables.
13. Real-World Example: Salary Adjustment Script
Example 8: Increase salary based on job title
DECLARE
v_job_id employees.job_id%TYPE;
v_salary employees.salary%TYPE;
v_new_salary NUMBER;
BEGIN
SELECT job_id, salary INTO v_job_id, v_salary FROM employees WHERE employee_id = 103;
IF v_job_id = 'IT_PROG' THEN
v_new_salary := v_salary * 1.10;
ELSE
v_new_salary := v_salary * 1.05;
END IF;
DBMS_OUTPUT.PUT_LINE('Old Salary: ' || v_salary);
DBMS_OUTPUT.PUT_LINE('New Salary: ' || v_new_salary);
END;
Explanation:
- Salary is increased based on job role.
- v_job_id and v_salary are fetched from the table.
- Conditional logic applies different increments.
14. Summary
PL/SQL variables are essential for any Oracle programming task. They offer the ability to:
- Store temporary data
- Perform calculations
- Handle decision-making
- Enhance procedural logic
15. Conclusion
In this blog, we covered the fundamentals of PL/SQL variables — including how to declare, assign, and use them. We explored data types, constants, scoping rules, and best practices.
Variables are not just placeholders; they are building blocks for all complex logic in PL/SQL. From business rule processing to dynamic SQL execution, variables provide the necessary tools to control data flow and logic within Oracle.
The more familiar you become with PL/SQL variables, the more robust, readable, and powerful your database programs will be.
0 Comments