DBMS Unit IV – PL/SQL Programming (Descriptive
Notes with SQL Examples)
4.1 Introduction to PL/SQL
PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL developed by
Oracle. It adds procedural capabilities to SQL, allowing developers to write programs that include
variables, loops, and conditions.
Advantages of PL/SQL:
1 Combines SQL with procedural statements for powerful database applications.
2 Increases performance by reducing network traffic (block execution).
3 Provides better error handling using exceptions.
4 Supports modular programming with procedures, functions, and triggers.
PL/SQL Block Structure:
A PL/SQL block is divided into four sections: Declaration, Execution, Exception, and End.
DECLARE
-- Declaration section (variables, constants, cursors)
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling statements
END;
/
PL/SQL Data Types:
Common data types include NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN.
Variables and Constants Example:
DECLARE
v_name VARCHAR2(20) := 'John';
v_salary NUMBER := 5000;
c_bonus CONSTANT NUMBER := 1000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Total Pay: ' || (v_salary + c_bonus));
END;
/
4.2 Control Structures
Conditional Control:
IF v_salary > 4000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Average Salary');
END IF;
/
Iterative Control:
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || i);
END LOOP;
/
Sequential Control:
Sequential control executes statements in order unless control is transferred using GOTO or EXIT.
4.3 Exception Handling
Exceptions handle runtime errors. They can be predefined or user-defined.
Predefined Exception Example:
BEGIN
SELECT salary INTO v_salary FROM employees WHERE emp_id = 101;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/
User-defined Exception Example:
DECLARE
e_low_salary EXCEPTION;
v_salary NUMBER := 2000;
BEGIN
IF v_salary < 3000 THEN
RAISE e_low_salary;
END IF;
EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary below limit!');
END;
/
4.4 Cursors
A cursor is a pointer to the context area that holds the result of a query.
Implicit Cursor Example:
BEGIN
UPDATE employees SET salary = salary + 500 WHERE dept_id = 10;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Record Updated.');
END IF;
END;
/
Explicit Cursor Example:
DECLARE
CURSOR emp_cursor IS SELECT emp_name FROM employees;
v_name employees.emp_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
/
Parameterized Cursor Example:
DECLARE
CURSOR dept_cursor(d_id NUMBER) IS SELECT emp_name FROM employees WHERE dept_id = d_id;
v_name employees.emp_name%TYPE;
BEGIN
OPEN dept_cursor(10);
LOOP
FETCH dept_cursor INTO v_name;
EXIT WHEN dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE dept_cursor;
END;
/
4.5 Procedures
A procedure is a stored subprogram that performs a specific task.
Create Procedure Example:
CREATE OR REPLACE PROCEDURE add_bonus(p_id NUMBER, p_bonus NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_bonus WHERE emp_id = p_id;
DBMS_OUTPUT.PUT_LINE('Bonus added successfully!');
END;
/
Execute and Delete Procedure:
EXEC add_bonus(101, 1000);
DROP PROCEDURE add_bonus;
/
4.6 Functions
Functions return a single value and can be used in SQL statements.
Create Function Example:
CREATE OR REPLACE FUNCTION get_total_salary(p_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id;
RETURN v_salary + 500;
END;
/
Execute and Delete Function:
DECLARE
v_total NUMBER;
BEGIN
v_total := get_total_salary(101);
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total);
END;
/
DROP FUNCTION get_total_salary;
/
4.7 Database Triggers
A trigger is a stored procedure automatically executed when an event occurs in the database
(INSERT, UPDATE, DELETE).
Create Trigger Example:
CREATE OR REPLACE TRIGGER emp_audit
AFTER INSERT OR DELETE OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('New Employee Added');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Employee Updated');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Employee Deleted');
END IF;
END;
/
Delete Trigger:
DROP TRIGGER emp_audit;
/