PL/SQL Handout
*** Make sure SET SERVEROUTPUT ON; is enabled to see the output
***
PL/SQL is Oracle Corporation’s procedural extension of SQL, used
primarily with the Oracle Corporation Database.
It combines:
SQL (for data manipulation)
Procedural constructs (loops, conditions, variables, exceptions)
Why PL/SQL?
SQL alone:
Executes one statement at a time
Has no loops or conditional logic
PL/SQL:
Supports IF-ELSE, LOOP, WHILE
Allows variables and constants
Handles exceptions
Improves performance by reducing network traffic
Supports modular programming
1. Structure of a PL/SQL Block
DECLARE -- Variable declaration (Optional)
BEGIN -- Executable statements (Mandatory)
EXCEPTION -- Error handling (Optional)
END;
2. Simple PL/SQL Programs
Program 1: Print a Message
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, Welcome to PL/SQL');
END;
/
Explanation: Prints a simple message using DBMS_OUTPUT.PUT_LINE.
Program 2: Add Two Numbers
DECLARE
a NUMBER := 10;
b NUMBER := 20;
c NUMBER;
BEGIN
c := a + b;
DBMS_OUTPUT.PUT_LINE('Sum = ' || c);
END;
/
Explanation: Declares variables, performs addition, and prints result.
Program 3: Even or Odd
DECLARE
num NUMBER := 7;
BEGIN
IF MOD(num,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Even Number');
ELSE
DBMS_OUTPUT.PUT_LINE('Odd Number');
END IF;
END;
/
Explanation: Uses IF-ELSE condition to check even or odd.
Program 4: To get the data from database tables and display it on the
console
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT ename INTO v_name
FROM emp
WHERE empno = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
Types of PL/SQL Blocks
Types of PL/SQL Blocks
Anonymous Block
A PL/SQL block that is not stored in the database.
It is executed immediately and mainly used for testing or writing small
programs.
Stored Procedure
A named PL/SQL block stored permanently in the database.
It performs a specific task and may accept parameters but does not
mandatorily return a value.
Function
A named PL/SQL block stored in the database that must return a value.
It can be used inside SQL statements like SELECT queries.
Trigger
A PL/SQL block that automatically executes in response to events like
INSERT, UPDATE, or DELETE.
It is used for enforcing rules and maintaining data integrity.
Package
A collection of related procedures, functions, variables, and cursors
grouped together.
It improves modularity, security, and performance of PL/SQL programs.
Important Features
Feature Description
Variables Store temporary data
%TYPE Inherit datatype from table column
%ROWTYPE Record of entire row
Cursors Handle multiple rows
Exception Handling Handle runtime errors
Triggers Automatic execution on events
3. Procedures
Procedure: Square of a Number
CREATE OR REPLACE PROCEDURE square_num(p_num NUMBER)
IS
result NUMBER;
BEGIN
result := p_num * p_num;
DBMS_OUTPUT.PUT_LINE('Square = ' || result);
END;
/
CALL:
BEGIN
square_num(5); -- 5 is the input number
END;
/
Or
EXEC square_num(5);
Explanation: Takes a number as input and prints its square.
Example: Stored Procedure
CREATE OR REPLACE PROCEDURE raise_salary
(p_empid NUMBER, p_percent NUMBER)
IS
BEGIN
UPDATE emp
SET sal = sal + (sal * p_percent/100)
WHERE empno = p_empid;
END;
/
CALL:
BEGIN
raise_salary(101, 10); -- 101 = Employee ID, 10 = 10% increment
END;
/
Or
EXEC raise_salary(101, 10);
4. Functions
Function: Factorial
CREATE OR REPLACE FUNCTION factorial(n NUMBER)
RETURN NUMBER
IS
fact NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
fact := fact * i;
END LOOP;
RETURN fact;
END;
/
CALL:
BEGIN
DBMS_OUTPUT.PUT_LINE('Factorial = ' || factorial(5));
END;
Or
SELECT factorial(5) FROM dual;
Explanation: Returns factorial of a given number.
Function: Annual Salary
CREATE OR REPLACE FUNCTION annual_salary(p_empid NUMBER)
RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empid;
RETURN v_sal * 12;
END;
/
Explanation: Retrieves monthly salary and returns annual salary.
5. Exception Handling Example
DECLARE
v_sal [Link]%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/
Explanation: Handles NO_DATA_FOUND exception to prevent runtime
error.
Questions:
Write a PL/SQL program to:
Check whether a given number is positive, negative, or zero.
Write a PL/SQL block to:
Print numbers from 1 to 10 using a loop
Write a PL/SQL program to:
Find the largest of three numbers using IF-ELSIF.
Write a PL/SQL block to:
Fetch salary of an employee (empno given)
Display it using SELECT INTO.
Write a procedure to:
Accept a number
Print its cube.
Write a procedure update_salary that:
Accepts employee ID and increment amount
Updates the salary in emp table.
Write a procedure to:
Count total number of employees
Display the count.
Write a function to:
Return the maximum of two numbers.
Write a function to:
Return total salary of all employees in the emp table.