The “PL/SQL Tutorial” is a helpful guide for anyone wanting to learn PL/SQL, which is Oracle’s way to add programming features to SQL. It explains basic concepts, control structures, data types, Cursor, Triggers, Procedures and Functions with Examples to help users create and manage database applications effectively.
What is PL/SQL
PL/SQL is a version of SQL that lets you do more than just run one command at a time. With PL/SQL, you can write scripts that handle several commands and include logic, making it easier to manage databases. It’s structured like other programming languages such as C or C++, so if you’re familiar with those, learning PL/SQL should be straightforward. Overall, PL/SQL makes working with Oracle databases more efficient and flexible.
Advantages of PL/SQL
The advantages of PL/SQL are the following.
- Strong Integration with SQL: PL/SQL works well with SQL, allowing you to run SQL commands in your code, making it easier to work with databases.
- High Performance: PL/SQL is designed to run efficiently, making it faster to handle complex tasks compared to using SQL alone.
- Increased Productivity: PL/SQL allows you to write more organized code that can be reused. It helps to work faster and more efficiently.
- Portability: PL/SQL code can be easily moved between different Oracle database systems.
- Scalability: PL/SQL can handle both small and large applications. It makes suitable for a wide range of database sizes.
- Easy Manageability: PL/SQL helps simplify the management of databases by automating tasks, reducing manual work.
- Support for Object-Oriented Programming: PL/SQL supports features like objects and classes, which help in modeling and organizing complex data.
- Web Application Development: PL/SQL allows you to build web applications within the Oracle system, working smoothly with web technologies.
- Server Pages Development: PL/SQL enables the creation of dynamic server-side web pages, useful for building interactive websites.
PL/SQL BLOCK
The PL/SQL block contains the following section:-
DECLARE: Used to declare variables, constants, and cursors.
BEGIN: Contains the executable SQL and PL/SQL code.
EXCEPTION: Handles errors that may occur during execution.
END: Marks the end of the block.
Example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DECLARE stu_id NUMBER := 5001; -- Declare variable stu_name VARCHAR2(40); BEGIN -- SQL executable statements SELECT first_name INTO stu_name FROM students WHERE student_id = stu_id; -- PL/SQL executable statements DBMS_OUTPUT.PUT_LINE('Student Name: ' || stu_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No student found with ID ' || stu_id); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred.'); END; |
Explanation:
In the DECLARE section, a variable stu_id and stu_name are defined.
The BEGIN section contains an SQL query to fetch the student’s name based on the student ID and print it.
The EXCEPTION section handles any errors, such as when no data is found or other issues arise.
END completes the block.
This structure allows PL/SQL to combine SQL commands with procedural logic to create efficient and flexible programs.
PL/SQL DATA-TYPE
PL/SQL provides a wide range of data types to store numbers, text, dates, and even collections of data. It also allows you to create custom types, handle entire rows, and work with large data like text documents or images. This variety helps you write efficient programs to manage databases easily.
1. Basic Data Types:
NUMBER: Used for numbers (like salaries or counts).
1 | salary NUMBER(8,2); -- 8 digits total, 2 after the decimal |
VARCHAR2: Stores text with variable length (names, descriptions).
1 | name VARCHAR2(50); -- Up to 50 characters |
CHAR: Stores fixed-length text (like single letters).
1 | gender CHAR(1); -- 1 character, like 'M' or 'F' |
BOOLEAN: Stores TRUE, FALSE, or NULL.
1 | is_active BOOLEAN; -- True or false values |
DATE: Stores dates and times.
1 | hire_date DATE; |
2. Composite Data Types:
These hold multiple values.
RECORD: Groups data together like a mini-database row (e.g., ID and name together).
1 2 | TYPE emp_record IS RECORD (emp_id NUMBER, emp_name VARCHAR2(50)); emp_data emp_record; -- Storing both ID and name |
TABLE or VARRAY: Stores a list of items (like an array).
1 2 | TYPE emp_list IS VARRAY(10) OF NUMBER; -- Array of 10 numbers emp_array emp_list; |
3. Reference Types:
REF CURSOR: Points to a query result, so you can process each row one by one
1 2 | TYPE ref_cursor IS REF CURSOR; emp_cursor ref_cursor; |
4. Large Objects (LOBs):
It store big data:
CLOB: Large text (like documents).
BLOB: Large binary data (like images or files).
1 2 | doc CLOB; -- Large text image BLOB; -- Binary data like an image |
5. Row Data Types:
%ROWTYPE: Stores an entire row from a table.
1 | emp_row employees%ROWTYPE; -- Holds all columns of a row from employees table |
6. Special PL/SQL Types:
PLS_INTEGER and BINARY_INTEGER: Stores whole numbers. PLS_INTEGER is faster in PL/SQL code.
7. User-Defined Types:
Subtypes: You can create custom data types based on existing ones.
1 | SUBTYPE small_salary IS NUMBER(7,2); -- Custom salary type |
8. Cursor Data Types:
CURSOR: Lets you retrieve and work with data row by row from a query.
1 | CURSOR emp_cursor IS SELECT * FROM employees; |
9. %TYPE Attribute:
%TYPE: Lets you declare a variable with the same type as a database column. It automatically adjusts if the table changes.
1 | emp_name employees.first_name%TYPE; -- Same type as employees.first_name column |
10. NULL Values:
NULL represents missing or unknown data. It can be assigned to any variable but must be handled carefully in logic.
1 | employee_bonus NUMBER := NULL; |
PL/SQL Operators
PL/SQL operators are symbols used to perform operations on data, such as arithmetic calculations, comparisons, logical tests, and string concatenation. They allow developers to manipulate values, make decisions, and control the flow of PL/SQL programs.
SQL Arithmetic Operators | ||
| Operator | Description | Example |
| + | Add | select 20 + 20; |
| – | Subtract | select 20 – 10; |
| * | Multiply | select 20 * 20; |
| / | Divide | select 30 / 10; |
| % | Modulo | select 30 % 8; |
SQL Comparison Operators | ||
| Operator | Description | Example |
| = | Equal to | SELECT * FROM Emp WHERE Sal = 10000; |
| > | Greater than | SELECT * FROM Emp WHERE Sal > 10000; |
| < | Less than | SELECT * FROM Emp WHERE Sal < 10000; |
| >= | Greater than or equal to | SELECT * FROM Emp WHERE Sal >= 10000; |
| <= | Less than or equal to | SELECT * FROM Emp WHERE Sal <= 10000; |
| < > | Not equal to | SELECT * FROM Emp WHERE Sal < > 10000; |
SQL Logical Operators | ||
| Operator | Description | Example |
| ALL | Return true if all comparisons are true | |
| AND | Return true if both expressions are true | SELECT * FROM emp WHERE City = “Delhi” AND City = “Punjab”; |
| ANY | Return true if any one of the comparisons is true. | |
| BETWEEN | Return true if the operand is within a range | SELECT * FROM emp WHERE salary BETWEEN 8000 AND 15000 order by salary; |
| EXIST | Return true if a subquery contains any rows | |
| IN | Return true if the operand is equal to one of the value in a list | SELECT * FROM emp WHERE eid IN ( 1008,1009) order by eid; |
| LIKE | Return true if the operand matches a pattern | SELECT * FROM emp WHERE ename LIKE ‘Am%’ ORDER BY ename; SELECT * FROM emp WHERE ename LIKE ‘_m%’ ORDER BY ename; |
| NOT | Reverse the result of any other Boolean operator. | |
| OR | Return true if either expression is true | SELECT * FROM emp WHERE City = “Delhi” OR City = “Punjab”; |
| SOME | Return true if some of the expressions are true | |
How to print output in PL/SQL
DBMS_OUTPUT is a built-in PL/SQL package that allows you to display output or messages from PL/SQL programs. It’s useful for debugging and tracking the flow of execution. The output is stored in a buffer, and It can be displayed by PUT_LINE procedure.
Features:-
1. PUT_LINE: It adds a message to the buffer with a new line.
1 | DBMS_OUTPUT.PUT_LINE('Hi, Welcome to the PL/SQL'); |
2. Enabling Output: In SQL*Plus, It is must to turn on SERVEROUTPUT to view the output.
1 | SET SERVEROUTPUT ON; |
3. Buffering: The output is not displayed immediately. It is saved in the buffer and displayed once the PL/SQL block has finished executing.
1 2 3 | BEGIN DBMS_OUTPUT.PUT_LINE('This is the Welcome message'); END; |
Know about comments. A comment can have following 2 forms in the pl/sql.
- To comment a single line in pl/sql use double hyphen(–), in front of line.
- The C style comment such as /* I am a comment */ is used for multi line comments.
PL/SQL Control Structures
Control structures in PL/SQL help control how the code runs inside a PL/SQL block or program. These structures allow you to make decisions, repeat actions (loops), and handle errors, just like in other programming languages.
Types of Control Structures in PL/SQL:
1. Conditional Control (IF-THEN-ELSE): This structure helps make decisions in the program.
IF-THEN: Executes if condition is true.
IF-THEN-ELSE: Executes ‘if’ block if true, another if false.
ELSIF: Adds multiple conditions to check.
Example:
1 2 3 4 5 6 7 | IF salary > 50000 THEN DBMS_OUTPUT.PUT_LINE('High salary'); ELSIF salary > 20000 THEN DBMS_OUTPUT.PUT_LINE('Average salary'); ELSE DBMS_OUTPUT.PUT_LINE('Low salary'); END IF; |
2. Iterative Control (Loops): Loops are used to repeat a block of code multiple times.
LOOP: Repeats until an explicit exit condition is met.
WHILE LOOP: Repeats until given condition gets true.
FOR LOOP: A certain number of times is repeated.
Example (FOR LOOP):
1 2 3 | FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('Iteration: ' || i); END LOOP; |
3. Sequential Control (GOTO): GOTO transfers control to a labeled part of the code. It’s rarely used because it makes code harder to follow.
Example:
1 2 3 4 | GOTO end_label; <some code> <<end_label>> DBMS_OUTPUT.PUT_LINE('Skipped to end'); |
4. Exception Handling: This control structure manages runtime errors, allowing you to define what to do when an error occurs.
EXCEPTION: Catches errors and handles them with appropriate actions.
Example:
1 2 3 4 5 6 7 8 | BEGIN -- Code block EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Other error occurred'); END; |
Programs of Control Structures in PL/SQL
Some examples of Conditional Control, Iterative Control, Sequential Control and Exception Handling are the given below.
Example 1 :- Addition of 3 numbers
1 2 3 4 5 6 7 8 9 10 11 12 | declare a number; b number; c number; d numberl begin a:=&a; b:=&b; c:=&c; d:=a+b; dbms_output.put_line('Sum = ' || d); end; |
Note:- Here ‘&’ is used to take user input at runtime
Output
Enter value for a: 10
Enter value for b: 20
Enter value for c: 50
Sum = 80
Example 2 :- Sum of 100 Numbers
1 2 3 4 5 6 7 8 9 10 11 12 | Declare a number; s1 number default 0; Begin a:=1; loop s1:=s1+a; exit when (a=100); a:=a+1; end loop; dbms_output.put_line('Sum between 1 to 100 is '||s1); End; |
Output
Sum between 1 to 100 is 5050
Example 3 :- Sum of Odd numbers from 1 to 100 using for loop
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | declare n number; sum1 number default 0; endvalue number; begin endvalue:=&endvalue; n:=1; for n in 1.. endvalue loop if mod(n,2)=1 then sum1:=sum1+n; end if end loop; dbms_output.put_line('sum = ' || sum1); end; |
Output
Enter value for endvalue: 100
sum = 2500
Example 4 :- Sum of Odd numbers from 1 to 100 using While loop
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | declare n number; endvalue number; sum1 number default 0; begin endvalue:=&endvalue; n:=1; while (n < endvalue) loop sum1:=sum1+n; n:=n+2; end loop; dbms_output.put_line('Sum of odd numbers between 1 and ' || endvalue || ' is ' || sum1); end; |
Output
Enter value for endvalue: 100
Sum of odd numbers between 1 and 100 is 2500
Example 5 :- Calculation of net salary
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | declare ename varchar2(15); basic number; da number; hra number; pf number; netsalary number; begin ename:=&ename; basic:=&basic; da:=basic * (41/100); hra:=basic * (15/100); if (basic < 10000) then pf:=basic * (5/100); elsif (basic >= 10000 and basic <= 15000) then pf:=basic * (7/100); elsif (basic > 15000 and basic <= 20000) then pf:=basic * (8/100); else pf:=basic * (10/100); end if; netsalary:=basic + da + hra -pf; dbms_output.put_line('Employee name : ' || ename); dbms_output.put_line('Providend Fund : ' || pf); dbms_output.put_line('Net salary : ' || netsalary); end; |
Output
Enter value for ename: Amit Singh
Enter value for basic: 18000
Employee name : Amit Singh
Providend Fund : 1440
Net salary : 26640
Example 6 :- pl/sql program to find greatest of three numbers
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Declare a number; b number; c number; d number; Begin a:=&a; b:=&b; c:=&b; if (a>b) and (a>c) then dbms_output.putline('A is Greatest'); elsif (b>a) and (b>c) then dbms_output.putline('B is Greatest'); else dbms_output.putline('C is Greatest'); end if; End; |
Output
Enter value for a: 25
Enter value for b: 39
Enter value for c: 12
B is Greatest
Example 7 :- Example of Loop Statement on EXIT WHEN
1 2 3 4 5 6 7 8 9 | Declare a number:= 200; begin loop a := a+50; exit when a=500; dbms_output.put_line (to_Char(a)); end loop; end; |
Output
250
300
350
400
450
Example 8 :- Simple example of For Loop
1 2 3 4 5 6 7 | Declare begin for i in 11..15 loop dbms_output.put_line(to_char(i)); end loop; end; |
Output
11
12
13
14
15
Example 9 :- Simple example of while loop
1 2 3 4 5 6 7 8 9 10 | Declare i number:=0; j number:= 0; begin while i <20 loop j := j+1; i := i +2; dbms_output.put_line(to_char(i)); end loop; end; |
Output
4
8
12
16
20
Example 9 :- Sequential Control GOTO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE x NUMBER := 1; BEGIN -- printing the initial value of x DBMS_OUTPUT.PUT_LINE('Starting the program. Value of x: ' || x); -- check condition if true then jump to label section IF x = 1 THEN GOTO skip_section; END IF; -- This will not print if GOTO executed DBMS_OUTPUT.PUT_LINE('This statement will be skipped because x = 1.'); <<skip_section>> -- print code after the GOTO DBMS_OUTPUT.PUT_LINE('You are in skipped section. Value of x is still: ' || x); END; |
Output
Starting the program. Value of x: 1
You are in skipped section. Value of x is still: 1
EXCEPTION HANDLING
Exception Handling in PL/SQL: Errors in PL/SQL block can be handled by error handling. It describes how we handle errors in the PL/SQL block. This is exactly the same as we do in C++ or Java programming. There are following two types of exception handling in pl/sql:
1. Predefined exceptions:
NO_DATA_FOUND == when a SELECT INTO statement returns more than one row
INVALID_CURSOR == If a cursor is not open and you are tyring to close that.
ZERO_DEVIDE == divide a number by zero.
TOO_MANY_ROWS == if more rows are returned by select statement.
OTHERS = Catches all exceptions that are not explicitly named.
CURSOR_ALREADY_OPEN == when a cursor is already opened
DUP_VAL_ON_INDEX == for duplicate entry of index
STORAGE_ERROR == if memory is damaged
PROGRAM_ERROR == internal problem in PL/SQL
LOGIN_DENIED == invalid user name or password
INVALID_NUMBER == if you are inserting a string datatype for a number datatype that is already declared
Example of Exception Handling
1 2 3 4 5 6 7 8 9 10 11 12 13 | BEGIN -- Trying to fetch a record SELECT salary INTO v_salary FROM employees WHERE employee_id = 1001; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with this ID.'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee found.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error occurred: ' || SQLERRM); END; |
2. Handling User Defined Exceptions
1 2 3 4 5 6 7 8 9 10 11 | DECLARE my_exception EXCEPTION; BEGIN -- some condition to raise exception IF a>100 THEN RAISE my_exception; END IF; EXCEPTION WHEN my_exception THEN DBMS_OUTPUT.PUT_LINE('user defined exception handled.'); END; |
CURSORS
In PL/SQL, a cursor is a pointer to the result set of a query. It allows row-by-row processing of query results. First, the cursor is declared, then opened to execute the query, followed by fetching rows into variables, and finally closed when processing is complete.
Types of cursors in In PL/SQL
1. Implicit Cursor: Automatically created by Oracle for SQL statements like SELECT INTO, INSERT, UPDATE, or DELETE. It is used when a single row is returned by the query.
2. Explicit Cursor: It is specified by the users for queries with many rows. You manually declare, open, fetch, and close these cursors to process query results row by row.
Both types help handle query result sets in PL/SQL efficiently.
Cursor Attributes
In PL/SQL, cursor attributes provide information about the state of cursors after query execution. The key attributes are:
%FOUND: It returns TRUE, if a row fetched successfully, otherwise FALSE.
%NOTFOUND: It returns TRUE, if no rows fetched, otherwise FALSE.
%ROWCOUNT: It returns, number of rows fetched.
%ISOPEN: Returns TRUE if the cursor is open, otherwise FALSE.
These attributes help in managing cursor operations efficiently.
Example 1 :- Example of SQL%FOUND (Implicit Cursor)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DECLARE v_emp_id NUMBER := 1001; BEGIN -- Trying to update record in employees table UPDATE employees SET salary = salary + 3500 WHERE employee_id = v_emp_id; -- Check if updated successfully IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' salary updated successfully.'); ELSE DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' record not found.'); END IF; END; / |
Example 2 :- Example of SQL%NOTFOUND (Implicit Cursor)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE v_emp_id NUMBER := 1001; BEGIN -- Trying to delete a record in employees table DELETE from employees WHERE employee_id = v_emp_id; -- Check if any record were deleted successfully IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No any Employee with ID' || v_emp_id || ' found.'); ELSE DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' deleted successfully.'); END IF; END; / |
Example 3 :- Example of SQL%ROWCOUNT (Implicit Cursor)
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE v_emp_id NUMBER := 1001; v_salary_increase NUMBER := 3500; BEGIN -- Update the salary of employees UPDATE employees SET salary = salary + v_salary_increase WHERE employee_id > v_emp_id; DBMS_OUTPUT.PUT_LINE('Number of employees whose salary was updated: ' || SQL%ROWCOUNT); END; / |
Example 4 :- Example of SQL%ISOPEN (Implicit Cursor)
1 2 3 4 5 6 7 8 9 10 11 12 13 | BEGIN UPDATE employees SET salary = salary + 2500 WHERE employee_id = 1005; -- Check if implicit cursor open IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('The implicit cursor is open.'); ELSE DBMS_OUTPUT.PUT_LINE('The implicit cursor is closed.'); END IF; END; / |
Example 5 :- Simple Explicit Cursor
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | DECLARE CURSOR emp_cursor IS SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 100; v_emp_id employees.employee_id%TYPE; v_firstname employees.first_name%TYPE; v_lastname employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN -- Open the cursor OPEN emp_cursor; -- Loop through the result set and fetch rows LOOP FETCH emp_cursor INTO v_emp_id, v_firstname, v_lastname, v_salary; EXIT WHEN emp_cursor%NOTFOUND; -- Exit the loop when no more rows are found DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_firstname || ' ' || v_lastname || ', Salary: ' || v_salary); END LOOP; -- Close the cursor CLOSE emp_cursor; END; / |
Example 6 :- Explicit Cursor with Parameters
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DECLARE CURSOR emp_cursor (dept_id NUMBER) IS SELECT employee_id, first_name, salary FROM employees WHERE department_id = dept_id; v_emp_id employees.employee_id%TYPE; v_name employees.first_name%TYPE; v_salary employees.salary%TYPE; BEGIN -- Open the cursor with department ID 105 as a parameter OPEN emp_cursor(20); -- Looping to fetch rows LOOP FETCH emp_cursor INTO v_emp_id, v_name, v_salary; EXIT WHEN emp_cursor%NOTFOUND; -- Exit the loop when no more rows found DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_name || ', Salary: ' || v_salary); END LOOP; -- Close the cursor CLOSE emp_cursor; END; / |
Example 7 :- Explicit Cursor with FOR Loop
1 2 3 4 5 6 7 | BEGIN FOR emp_record IN (SELECT employee_id, first_name, salary FROM employees WHERE department_id = 30) LOOP DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.employee_id || ', Name: ' || emp_record.first_name || ', Salary: ' || emp_record.salary); END LOOP; END; / |
Example 8 :- Display the 10 employees list who got the maximum salary
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | DECLARE -- Define a cursor to get 10 highest salary persons CURSOR top_10_salaries IS SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY; -- Variables to store employee details v_employee_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN -- Open the cursor OPEN top_10_salaries; -- Looping to fetch record by the cursor LOOP -- Fetch the data from the cursor into variables FETCH top_10_salaries INTO v_employee_id, v_first_name, v_last_name, v_salary; -- Exit the loop when no more records are fetched EXIT WHEN top_10_salaries%NOTFOUND; -- Display employee details DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary); END LOOP; -- Close the cursor CLOSE top_10_salaries; END; / |
Example 9 :- Example of Strong Cursor
Strong Cursor: A cursor with a fixed, predefined structure (with a known return type at compile time). A strong cursor is defined with an explicit SELECT statement where the return type (columns and data types) is known.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DECLARE -- Define a strong cursor with predefined structure CURSOR strong_cursor IS SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 101; v_emp_id employees.employee_id%TYPE; v_fname employees.first_name%TYPE; v_lname employees.last_name%TYPE; BEGIN OPEN strong_cursor; LOOP FETCH strong_cursor INTO v_emp_id, v_fname, v_lname; EXIT WHEN strong_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_fname || ' ' || v_lname); END LOOP; CLOSE strong_cursor; END; / |
Example 10 :- Example of Weak Cursor
Weak Cursor: A cursor where the structure is not fixed, meaning the return type can vary, and is determined dynamically at runtime. A weak cursor does not have a fixed structure. The query can return any number of columns and types of data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE -- Define a weak cursor with no predefined structure CURSOR weak_cursor (dept_id IN NUMBER) IS SELECT * FROM employees WHERE department_id = dept_id; -- Define record with weak structure v_employee weak_cursor%ROWTYPE; BEGIN OPEN weak_cursor(105); -- Open the cursor for department 105 LOOP FETCH weak_cursor INTO v_employee; EXIT WHEN weak_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee.employee_id || ', Name: ' || v_employee.first_name || ' ' || v_employee.last_name); END LOOP; CLOSE weak_cursor; END; / |
TRIGGERS
In PL/SQL, a trigger is a stored procedure that is automatically executed in response to certain events on a particular table or view. Triggers can be used to enforce business rules, maintain audit trails, or perform validation checks automatically when DML (Data Manipulation Language) operations such as INSERT, UPDATE, or DELETE occur.
Types of Triggers
Before Trigger: Fires before the triggering DML statement (e.g., BEFORE INSERT).
After Trigger: Fires after the triggering DML statement (e.g., AFTER UPDATE).
Row-level Trigger: Fires once for each row affected by the triggering statement.
Statement-level Trigger: Fires once for the entire DML statement, regardless of the number of rows affected.
Structure of a Trigger
1 2 3 4 5 6 7 8 9 | CREATE [OR REPLACE] TRIGGER trigger_name [BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name [FOR EACH ROW] DECLARE -- Optional variable declaration BEGIN -- PL/SQL code to execute when the trigger is fired END; |
Example 1: Before Insert Trigger
This trigger ensures that, before any employee is inserted into the employees table, their salary is automatically adjusted to be at least 9000.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TRIGGER trg_before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN IF :NEW.salary < 9000 THEN :NEW.salary := 9000; -- Ensure minimum salary is 3000 END IF; END; / |
Note:- :NEW.salary refers to the salary of the new row being inserted.
Example 2: After Update Trigger
This trigger logs any changes made to the salary of employees after an UPDATE on the employees table.
1 2 3 4 5 6 7 8 | CREATE OR REPLACE TRIGGER trg_after_update_employee_salary AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, update_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; / |
Note:- :OLD.salary refers to the salary before the update, and :NEW.salary refers to the new salary after the update.
Example 3: Before Delete Trigger
This trigger prevents the deletion of employees from the employees table if the employee is a manager.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TRIGGER trg_before_delete_employee BEFORE DELETE ON employees FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM employees WHERE manager_id = :OLD.employee_id) THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete a manager who has subordinates.'); END IF; END; / |
Example 4: AFTER UPDATE Trigger: Auto Update Last Modified Date
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TRIGGER trg_update_last_modfd AFTER UPDATE ON employees FOR EACH ROW BEGIN UPDATE employees SET last_modified = SYSDATE WHERE employee_id = :OLD.employee_id; END; / |
Example 5: BEFORE INSERT Trigger: Assigning Default Values
This trigger assigns a default value to the job_title if it is not provided during an insert.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TRIGGER trigger_default_job_title BEFORE INSERT ON employees FOR EACH ROW BEGIN IF :NEW.job_title IS NULL THEN :NEW.job_title := 'Other Staff'; END IF; END; / |
PROCEDURES AND FUNCTIONS
A procedure is a small program made up of SQL statements, designed to perform a particular task. It is similar to a function. Procedures and functions combine SQL and PL/SQL statements to complete a specified purpose. A stored procedure or function is a named block of PL/SQL code that is saved in the database and can be reused whenever necessary.
Difference between Procedures and Functions
Below is explaining the difference between procedures and functions in PL/SQL:
| Features | Procedure | Function |
| Return Value | Does not have to return a value. | Must return a single value. |
| Use in SQL | Cannot be called directly in SQL queries. | Can be called and used in SQL queries. |
| Purpose | Perform actions or tasks, like updating data. | Used for calculations or to return result. |
| Call Method | Called as a standalone statement. | Called as part of an expression. |
| Multiple Outputs | Can return multiple outputs via parameters. | Return only one value. |
| Syntax | EXEC procedure_name; | SELECT function_name(…) from dual; |
Syntax for Creating a Stored Procedure
A stored procedure in PL/SQL is a block of code that performs some specific task. It can accept parameters, perform operations, and return results (if needed). The basic syntax for creating a stored procedure in PL/SQL is following:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1 [IN | OUT | IN OUT] datatype, parameter2 [IN | OUT | IN OUT] datatype) IS -- Variable declarations BEGIN -- Procedure logic goes here -- SQL statements EXCEPTION -- Exception handling (optional) WHEN exception_name THEN -- Error handling code END procedure_name; |
Explanation:
- CREATE [OR REPLACE] PROCEDURE: Used to create new or update an existing procedure.
- procedure_name: Name of the procedure.
- parameter1, parameter2, …: The parameters that the procedure accepts. You can specify the direction of the parameters using:
- IN: Input parameter (read-only).
- OUT: Output parameter (write-only).
- IN OUT: Both input and output (read and write).
- IS/AS: Keyword that begins the declaration of the procedure’s body.
- BEGIN: Marks the start of the executable part of the procedure.
- EXCEPTION: This section is optional and used for handling exceptions (errors).
- END procedure_name: Ends of procedure.
Example of a Simple Procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE OR REPLACE PROCEDURE update_employee_salary ( emp_id IN NUMBER, salary_grow IN NUMBER ) IS BEGIN -- Update the salary of the employee UPDATE employees SET salary = salary + salary_grow WHERE employee_id = emp_id; -- Commit the transaction COMMIT; EXCEPTION WHEN OTHERS THEN -- Handle any exception ROLLBACK; DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END update_employee_salary; |
Here, the procedure update_employee_salary accepts two parameters: emp_id (employee ID) and salary_grow (Amount to increase salary). It updates the salary of an employee in the employees table and handles any exceptions.
Syntax for creating a function
In PL/SQL, a function is created using the CREATE OR REPLACE FUNCTION statement. The basic syntax for creating a function is the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE OR REPLACE FUNCTION function_name ( parameter_name1 datatype, -- Input parameters parameter_name2 datatype ) RETURN return_datatype IS -- Variable Declaration if any variable_name datatype; BEGIN -- Executable section -- Function logic goes here -- Use RETURN keyword to return a value RETURN value; EXCEPTION -- Exception handling section (optional) WHEN exception_name THEN -- Handle exception RETURN default_value; END function_name; |
Explanation
- CREATE OR REPLACE FUNCTION: Creates a new function or replaces an existing one.
- function_name: The name of the function.
- parameter_name1, parameter_name2: Input parameters with their data types.
- RETURN: return data type of the function.
- IS: Introduces the declaration section.
- BEGIN: Begins the executable section.
- RETURN value: Specifies the value the function returns.
- EXCEPTION: An optional section for exception handling.
- END function_name: Ends of function.
Example of a function
Here, the function get_employee_salary takes an employee ID as input and returns the salary of that employee. If no data is found, it returns 0.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION get_employee_salary ( emp_id NUMBER ) RETURN NUMBER IS emp_salary NUMBER; BEGIN SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; RETURN emp_salary; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END get_employee_salary; |
Other Examples
Stored Procedure with IF-ELSE Logic : This procedure checks whether a number is even or odd.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE PROCEDURE chk_even_odd(num IN NUMBER) IS BEGIN IF MOD(num, 2) = 0 THEN DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is even.'); ELSE DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is odd.'); END IF; END; / |
Use
1 2 3 4 | BEGIN chk_even_odd(21); END; / |
Stored Procedure to Calculate Factorial: This procedure calculates the factorial of a given number.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE PROCEDURE find_factorial(num IN NUMBER, result OUT NUMBER) IS fact NUMBER := 1; BEGIN FOR i IN 1..num LOOP fact := fact * i; END LOOP; result := fact; END; / |
Use:
1 2 3 4 5 6 7 | DECLARE result NUMBER; BEGIN find_factorial(4, result); DBMS_OUTPUT.PUT_LINE('Factorial: ' || result); END; / |
Stored Procedure with Multiple OUT Parameters: This procedure accepts two numbers and returns their sum, subtraction, and multiply.
1 2 3 4 5 6 7 8 | CREATE OR REPLACE PROCEDURE calculate_all(num1 IN NUMBER, num2 IN NUMBER, addition OUT NUMBER, substract OUT NUMBER, multiply OUT NUMBER) IS BEGIN addition := num1 + num2; substract := num1 - num2; multiply := num1 * num2; END; / |
Use:
1 2 3 4 5 6 7 8 9 10 11 | DECLARE addition NUMBER; substract NUMBER; multiply NUMBER; BEGIN calculate_all(45, 25, addition, substract, multiply); DBMS_OUTPUT.PUT_LINE('Sum is : ' || Addition); DBMS_OUTPUT.PUT_LINE('Sub is : ' || Substract); DBMS_OUTPUT.PUT_LINE('Prod is : ' || multiply); END; / |
PACKAGE
A package is an Oracle object that contains other objects within it. It is a schema object that groups logically related PL/SQL types, variables, procedures, and functions.
Types of Packages
Packages have two following parts:
Package Specification: This is the interface to the package, declaring all the public elements (procedures, functions, variables, types) that can be accessed by the calling programs.
Package Body: This contains the implementation of the procedures and functions defined in the specification, along with any private variables, types, or procedures.
Advantages of PL/SQL Packages
Modularity: Code can be logically grouped.
Encapsulation: You can hide implementation details in the package body.
Reusability: Packages include procedures and functions that are reusable in several applications.
Performance: When a packaged procedure is called, the entire package is loaded into memory, resulting in quicker access for subsequent calls to its elements.
Example of a PL/SQL Package
1. Package Specification : This part defines the interface (what’s accessible to the outside).
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE employee_package AS PROCEDURE get_employee_name(p_emp_id IN NUMBER, p_name OUT VARCHAR2); PROCEDURE update_employee_salary(p_emp_id IN NUMBER, p_new_salary IN NUMBER); FUNCTION calculate_bonus(p_salary IN NUMBER) RETURN NUMBER; END employee_package; / |
2. Package Body : This part provides the implementation of the procedures and functions declared in the specification.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | CREATE OR REPLACE PACKAGE BODY employee_package AS PROCEDURE get_employee_name(p_emp_id IN NUMBER, p_name OUT VARCHAR2) IS BEGIN SELECT employee_name INTO p_name FROM employees WHERE employee_id = p_emp_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_name := 'No such employee'; END get_employee_name; PROCEDURE update_employee_salary(p_emp_id IN NUMBER, p_new_salary IN NUMBER) IS BEGIN UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id; IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Employee not found.'); ELSE DBMS_OUTPUT.PUT_LINE('Salary updated successfully.'); END IF; END update_employee_salary; FUNCTION calculate_bonus(p_salary IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_salary * 0.25; END calculate_bonus; END employee_package; / |
Usage of Package
Once the package is created, you can use its procedures and functions like this:
Example 1: Get Employee Name
1 2 3 4 5 6 7 | DECLARE name VARCHAR2(100); BEGIN employee_package.get_employee_name(101, name); DBMS_OUTPUT.PUT_LINE('Employee Name: ' || name); END; / |
Example 2: Update Employee Salary
1 2 3 4 | BEGIN employee_package.update_employee_salary(101, 55000); END; / |
Example 3: Calculate Employee Bonus
1 2 3 4 5 6 7 | DECLARE bonus NUMBER; BEGIN bonus := employee_package.calculate_bonus(60000); DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus); END; / |
Overloading in PL/SQL
PL/SQL supports overloading, meaning you can define multiple procedures or functions with the same name, as long as they have different numbers or types of parameters (or both). This is a feature called overloading, which allows you to reuse the same function or procedure name for different purposes based on the input parameters.
Example of Overloading in PL/SQL
Overloaded Procedures:
1 2 3 4 5 | CREATE OR REPLACE PACKAGE example_package AS PROCEDURE display_value(p_value IN NUMBER); PROCEDURE display_value(p_value IN VARCHAR2); END example_package; / |
Package Body:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE PACKAGE BODY example_package AS PROCEDURE display_value(p_value IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Number: ' || p_value); END display_value; PROCEDURE display_value(p_value IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('String: ' || p_value); END display_value; END example_package; / |
Use:
1 2 3 4 5 | BEGIN example_package.display_value(785); -- Calling procedure with NUMBER argument example_package.display_value('Welcome'); -- Calling procedure with VARCHAR2 argument END; / |
The End