Oracle PL SQL Tutorial Day 6
PL/SQL (Procedural Language/Structured Query Language) is a powerful extension of SQL used in Oracle databases. It adds programming constructs to standard SQL, allowing developers to write robust, reusable, and secure database logic.
One of the most important constructs in PL/SQL is the procedure. In this article, we’ll explore what procedures are, how they work, and how to create them with real-world examples using Oracle’s built-in HR schema.
What Is a PL/SQL Procedure?
A PL/SQL procedure is a named block of code stored in the Oracle database that performs a specific task. Unlike functions, which return a value, procedures may or may not return any output, and they can accept input and output parameters.
You can think of procedures as subprograms that help you modularize your code, enhance readability, and enforce security.
Two major steps required for oracle pl sql procedure
- Create a procedure
- Execute the procedure
Syntax of a Procedure
CREATE OR REPLACE PROCEDURE procedure_name (
param1 IN datatype,
param2 OUT datatype,
param3 IN OUT datatype
)
IS
-- Variable declarations
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling
END procedure_name;
- IN: Input parameter (default).
- OUT: Output parameter.
- IN OUT: Parameter acts as both input and output.
Why Use Procedures?
Using procedures in Oracle PL/SQL provides many benefits:
- Modularity: Break large codebases into smaller, manageable units.
- Reusability: Write once and call anywhere.
- Performance: Stored procedures are compiled and stored in the database.
- Security: Control access with GRANT EXECUTE instead of exposing SQL.
Example 1: A Simple Procedure to Fetch Employee Info
----------------------------------------------------
Let’s create a basic procedure that accepts an EMPLOYEE_ID and displays the employee’s name, email, and salary.
Table Used: HR.EMPLOYEES
CREATE OR REPLACE PROCEDURE get_employee_info (
p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
v_email EMPLOYEES.EMAIL%TYPE;
v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT FIRST_NAME, LAST_NAME, EMAIL, SALARY
INTO v_first_name, v_last_name, v_email, v_salary
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Name : ' || v_first_name || ' ' || v_last_name);
DBMS_OUTPUT.PUT_LINE('Email : ' || v_email);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || p_emp_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END get_employee_info;
ASQLERRM use to show oracle errors. It will show meaning full error to user or developer instead of breaking the program due to exception handling
Execution:
BEGIN
get_employee_info(100);
END;
You can execute above code multiple time with different value to see the impact
Example 2: Procedure to Insert a New Employee
We’ll now create a procedure that inserts a new record into the EMPLOYEES table.
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
p_first_name IN EMPLOYEES.FIRST_NAME%TYPE,
p_last_name IN EMPLOYEES.LAST_NAME%TYPE,
p_email IN EMPLOYEES.EMAIL%TYPE,
p_hire_date IN EMPLOYEES.HIRE_DATE%TYPE,
p_job_id IN EMPLOYEES.JOB_ID%TYPE,
p_salary IN EMPLOYEES.SALARY%TYPE
)
IS
BEGIN
INSERT INTO EMPLOYEES (
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY
) VALUES (
p_emp_id, p_first_name, p_last_name, p_email, p_hire_date, p_job_id, p_salary
);
DBMS_OUTPUT.PUT_LINE('Employee inserted successfully.');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Employee ID already exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error inserting employee: ' || SQLERRM);
END add_employee;
Execution:
BEGIN
add_employee(
p_emp_id => 999,
p_first_name => 'Alice',
p_last_name => 'Smith',
p_email => 'asmith',
p_hire_date => SYSDATE,
p_job_id => 'IT_PROG',
p_salary => 7000
);
END;
In this example we added new exception handling to avoid duplication. You must try a value which already exists.
Example 3: Update Salary and Return Old Salary
This procedure updates the salary of an employee and returns the previous salary as an OUT parameter.
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
p_new_salary IN EMPLOYEES.SALARY%TYPE,
p_old_salary OUT EMPLOYEES.SALARY%TYPE
)
IS
BEGIN
SELECT SALARY INTO p_old_salary
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_emp_id;
UPDATE EMPLOYEES
SET SALARY = p_new_salary
WHERE EMPLOYEE_ID = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_employee_salary;
Execution:
DECLARE
v_old_salary EMPLOYEES.SALARY%TYPE;
BEGIN
update_employee_salary(100, 9000, v_old_salary);
DBMS_OUTPUT.PUT_LINE('Previous Salary: ' || v_old_salary);
END;
No data found exception used here which shows an error if employee id doesn't exists. Try with correct and wrong values to get the impact
Best Practices for Writing PL/SQL Procedures
--------------------------------------------
- Use %TYPE for Data Types
- Ensures your procedure automatically matches table column types.
- Always Handle Exceptions
- Use EXCEPTION blocks to handle known and unknown errors gracefully.
- Avoid Hardcoding Values
- Parameterize your procedures for flexibility.
- Keep Procedures Focused
- Each procedure should do one specific task.
Add Comments and Documentation
Make your code readable and maintainable.
Security Considerations
- Use GRANT EXECUTE to allow specific users to run the procedure.
- Use AUTHID CURRENT_USER if you want procedures to run with caller’s privileges.
- Avoid embedding sensitive data directly in procedures.
Managing Procedures in Oracle
View All Procedures:
SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';
You can use DBA_Objects or ALL_objects to view other schema/owner procedures
Make sure execute rights granted to execute the procedure
Recompile a Procedure:
ALTER PROCEDURE procedure_name COMPILE;
Drop a Procedure:
DROP PROCEDURE procedure_name;
When to Use Procedures
Procedures are ideal for:
- - Batch jobs and scheduled tasks
- - ETL -Data transformation or loading
- - Validating and updating records
- - Encapsulating business logic in database APIs
- Avoid load on front end as execution would be fast over database
Bonus Challenge
Create a procedure called delete_employee that:
- - Accepts an EMPLOYEE_ID
- - Deletes the employee
- - Returns a message via OUT parameter about success or failure
Summary
- Procedures are essential for modular PL/SQL programming.
- They improve performance, security, and code maintainability.
- We explored examples for retrieving, inserting, and updating employee records.
- Best practices and exception handling ensure code quality and stability.
- Many task mentioned in this can be performed through normal SQL as well but it's depends on requirements.
0 Comments