In this tutorial, I am giving 4 PL/SQL anonymous block examples, so that you can learn how to create and use anonymous blocks in Oracle database. Anonymous blocks in PL/SQL are the programs which are not having any Create Procedure or Create Function statement, and it does not save in Oracle database. PL/SQL anonymous blocks mostly used for testing or to run the program in Oracle database for one time only.
1. PL/SQL Anonymous Block To Print Numbers in Reverse Order
The following PL/SQL program will print the numbers 0 to10 in reverse order, using REVERSE clause in for loop.
SET SERVEROUTPUT ON; DECLARE counter NUMBER; BEGIN FOR counter IN REVERSE 0..10 LOOP DBMS_OUTPUT.PUT_LINE (counter); END LOOP; END; /
Output
10 9 8 7 6 5 4 3 2 1 0 PL/SQL procedure successfully completed.
2. Print Employees Count in a Department
The following PL/SQL program will count the employees in department 90. The table is from the HR schema of the Oracle database. You can download this schema from the following link Download HR Schema.
SET SERVEROUTPUT ON; DECLARE emp_count NUMBER; v_department_id NUMBER := 90; BEGIN SELECT COUNT ( * ) INTO emp_count FROM employees WHERE department_id = v_department_id; DBMS_OUTPUT.PUT_LINE( 'The employee count is: ' || emp_count || ' for the department with an ID of: ' || v_department_id); END; /
Output
The employee count is: 3 for the department with an ID of: 90 PL/SQL procedure successfully completed.
3. Take Input in PL/SQL Anonymous Block and Print
In the below example, it will ask for the input from the user for department id and will print the employee count in that department, and also will print the department name.
SET SERVEROUTPUT ON; DECLARE dept_id_var NUMBER (4) := &department_id; dept_name VARCHAR2 (30); emp_count NUMBER; BEGIN SELECT COUNT ( * ) INTO emp_count FROM employees WHERE department_id = dept_id_var; SELECT department_name INTO dept_name FROM departments WHERE department_id = dept_id_var; DBMS_OUTPUT.PUT_LINE( 'There are ' || emp_count || ' employees ' || 'in the ' || dept_name || ' department.'); END; /
Output
Enter value for department_id: 60 old 2: dept_id_var NUMBER(4) := &department_id; new 2: dept_id_var NUMBER(4) := 60; There are 4 employees in the IT department. PL/SQL procedure successfully completed.
4. PL/SQL Block With <<Labels>> Example
The following PL/SQL block is using the labels for parent and child block. The child block will access parent block's variable by using the label reference of the parent block. The program will update the old manager id with new manager id for the departments.
SET SERVEROUTPUT ON; <<outer_block>> DECLARE mgr_id NUMBER (6) := '¤t_manager_id'; dept_count NUMBER := 0; BEGIN SELECT COUNT ( * ) INTO dept_count FROM departments WHERE manager_id = outer_block.mgr_id; IF dept_count > 0 THEN <<inner_block>> DECLARE dept_name VARCHAR2 (30); mgr_id NUMBER (6) := '&new_manager_id'; BEGIN SELECT department_name INTO dept_name FROM departments WHERE manager_id = outer_block.mgr_id; UPDATE departments SET manager_id = inner_block.mgr_id WHERE manager_id = outer_block.mgr_id; DBMS_OUTPUT.PUT_LINE ( 'Department manager ID has been changed for ' || dept_name); END inner_block; ELSE DBMS_OUTPUT.PUT_LINE ( 'There are no departments listed for the manager'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ( 'There are no departments listed for the manager'); END outer_block; /
Output
Enter value for current_manager_id: 100 old 3: mgr_id NUMBER (6) := '¤t_manager_id'; new 3: mgr_id NUMBER (6) := '100'; Enter value for new_manager_id: 201 old 16: mgr_id NUMBER (6) := '&new_manager_id'; new 16: mgr_id NUMBER (6) := '201'; Department manager ID has been changed for Executive PL/SQL procedure successfully completed.