Oracle PL SQL Tutorial Day 2
In today's lesson we will try to understand Oracle PL SQL anonymous block.
There are two type of block in pl sql one is named other one is anonymous.
The named block stored in oracle database as procedure or function while anonymous can be save as file in local directory.
1. What is an Anonymous Block?
An Anonymous Block is a PL/SQL block that is unnamed and not stored in the database for reuse. It is written and executed on the fly, often used for quick operations, testing, or scripting.
Key characteristics:
- Not stored in the database.
- Cannot be explicitly called like procedures/functions.
- Ideal for quick, one-time operations.
----------------------------------------------------
2. Structure of a PL/SQL Anonymous Block
----------------------------------------------------
A basic PL/SQL block has the following structure:
DECLARE
-- variable declarations
BEGIN
-- executable statements
EXCEPTION
-- exception handling
END;
Only the BEGIN and END sections are mandatory. DECLARE and EXCEPTION are optional.
----------------------------------------------------
3. Simple Anonymous Block Example
----------------------------------------------------
Example 1: Display a static message
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to Oracle PL/SQL!');
END;
Explanation:
- DBMS_OUTPUT.PUT_LINE is used to print output to the console.
- This block simply prints a welcome message when executed.
----------------------------------------------------
4. Using Variables in Anonymous Blocks
----------------------------------------------------
Example 2: Declare and use a variable
DECLARE
v_message VARCHAR2(50) := 'PL/SQL is powerful!';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
END;
Explanation:
- v_message is a variable declared with a default value.
- The value is printed in the BEGIN section.
----------------------------------------------------
5. Performing Calculations
----------------------------------------------------
Example 3: Calculate employee bonus
DECLARE
v_salary NUMBER := 50000;
v_bonus NUMBER;
BEGIN
v_bonus := v_salary * 0.10;
DBMS_OUTPUT.PUT_LINE('Bonus is: ' || v_bonus);
END;
Explanation:
- Salary and bonus variables are declared.
- Bonus is calculated as 10% of salary.
- The result is printed.
----------------------------------------------------
6. Conditional Logic with IF Statement
----------------------------------------------------
Example 4: Grade evaluation
DECLARE
v_score NUMBER := 85;
BEGIN
IF v_score >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
ELSIF v_score >= 80 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: C');
END IF;
END;
Explanation:
- A score is evaluated using an IF-ELSIF-ELSE construct.
- Based on the value, a grade is printed.
----------------------------------------------------
7. Using Loops in Anonymous Blocks
----------------------------------------------------
Example 5: Print numbers using a FOR loop
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || i);
END LOOP;
END;
Explanation:
- The loop prints numbers from 1 to 5.
- FOR loops are very useful in repetitive tasks.
----------------------------------------------------
8. Anonymous Block with SELECT INTO Statement
----------------------------------------------------
Example 6: Fetch data into a variable
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
Explanation:
- SELECT INTO fetches data from the employees table.
- %TYPE is used to assign the same datatype as first_name.
- The employee's name is printed.
----------------------------------------------------
9. Exception Handling in Anonymous Blocks
----------------------------------------------------
Example 7: Handle NO_DATA_FOUND exception
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = 9999;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with that ID.');
END;
Explanation:
- The block handles the scenario where no employee is found.
- NO_DATA_FOUND is a predefined exception.
----------------------------------------------------
10. Working with Multiple Variables
----------------------------------------------------
Example 8: Calculate Net Pay
DECLARE
v_salary NUMBER := 40000;
v_tax NUMBER := 0.15;
v_netpay NUMBER;
BEGIN
v_netpay := v_salary - (v_salary * v_tax);
DBMS_OUTPUT.PUT_LINE('Net Pay: ' || v_netpay);
END;
Explanation:
- Net pay is calculated by deducting 15% tax from salary.
- Result is printed using DBMS_OUTPUT.
----------------------------------------------------
11. Using Boolean Logic
----------------------------------------------------
Example 9: Check eligibility for bonus
DECLARE
v_salary NUMBER := 30000;
v_bonus_eligible BOOLEAN;
BEGIN
v_bonus_eligible := v_salary < 50000;
IF v_bonus_eligible THEN
DBMS_OUTPUT.PUT_LINE('Eligible for bonus.');
ELSE
DBMS_OUTPUT.PUT_LINE('Not eligible.');
END IF;
END;
Explanation:
- A boolean variable is used to determine eligibility.
- Conditional logic is applied to print appropriate message.
----------------------------------------------------
12. Nested Blocks
----------------------------------------------------
Example 10: Demonstrate nested anonymous blocks
DECLARE
v_outer NUMBER := 100;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer block value: ' || v_outer);
DECLARE
v_inner NUMBER := 200;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inner block value: ' || v_inner);
END;
-- Outer block continues
DBMS_OUTPUT.PUT_LINE('Back to outer block.');
END;
Explanation:
- Anonymous blocks can be nested.
- Inner block variables are not accessible outside.
----------------------------------------------------
13. Summary and Best Practices
----------------------------------------------------
Anonymous blocks are foundational to PL/SQL development. They are quick to write, test, and execute, making them perfect for:
- Ad hoc database tasks
- Testing PL/SQL logic
- Executing simple DML statements
- Learning and debugging new code
Best Practices:
- Always use exception handling to capture unexpected errors.
- Use meaningful variable names for better readability.
- Avoid hardcoding values unless for testing purposes.
- Comment your code for clarity.
----------------------------------------------------
Conclusion
----------------------------------------------------
Oracle PL/SQL Anonymous Blocks are a simple yet powerful feature that enable users to write procedural logic within SQL scripts. They serve as the building blocks for more advanced PL/SQL constructs such as procedures, functions, and packages.
Whether you are fetching a record, performing calculations, or looping through data, anonymous blocks give you a flexible environment to manage and manipulate data effectively. Mastering them will not only enhance your PL/SQL proficiency but also lay the groundwork for more advanced Oracle database development.
0 Comments