Oracle PL SQL Tutorial day 7
PL/SQL (Procedural Language/SQL) is Oracle’s powerful extension to SQL that combines
the strengths of SQL with procedural features of programming languages. Among the most
commonly used constructs in PL/SQL are functions—blocks of code that perform a specific
task and return a value.
In this guide, we’ll explore Oracle PL/SQL functions in depth. We’ll discuss what
functions are, how they work, when to use them, and how to create and manage them
effectively. We'll also work through real examples using Oracle's sample HR schema
to solidify your understanding.
We will cover below topics
1. What Is a PL/SQL Function?
2. Function vs. Procedure
3. Benefits of Using Functions
4. Syntax of a PL/SQL Function
5. Example 1: Simple Function to Return Full Name
6. Example 2: Function to Calculate Annual Salary
7. Example 3: Function with IN OUT Parameters
8. Calling Functions in SQL Statements
9. Using Functions in SELECT, WHERE, and CASE
10. Best Practices for Writing Functions
11. Security and Privileges
12. Managing PL/SQL Functions in Oracle
13. Summary
1. What Is a PL/SQL Function?
A PL/SQL function is a named subprogram that can be stored in the database and returns
a single value to the calling environment. It can accept zero or more input parameters,
perform operations, and return a result.
Functions are useful for encapsulating logic that computes and returns a result that
you want to use in SQL statements, PL/SQL blocks, or applications.
These can be use by other users by giving appropriate execution rights. A function or procedure always execute in current schema. The default settings can be change by developer or DBA
Function can be use in SQL statement as well but some terms and conditions apply.
3. Benefits of Using Functions
- - Modularity – Encapsulate logic into reusable components.
- - Reusability – Call functions multiple times in SQL, PL/SQL, or forms.
- - Maintainability – Isolate business logic into manageable pieces.
- - Performance – Stored functions are compiled and cached by Oracle.
- - Security – Use grants to control access to logic.
4. Syntax of a PL/SQL Function
CREATE OR REPLACE FUNCTION function_name (
param1 IN data_type,
param2 IN data_type
) RETURN return_data_type
IS
-- Variable declarations
BEGIN
-- Function logic
RETURN result;
EXCEPTION
-- Exception handling
END function_name;
5. Example 1: Simple Function to Return Full Name
CREATE OR REPLACE FUNCTION get_full_name (
p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
) RETURN VARCHAR2
IS
v_full_name VARCHAR2(100);
BEGIN
SELECT FIRST_NAME || ' ' || LAST_NAME
INTO v_full_name
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_emp_id;
RETURN v_full_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Employee not found';
WHEN OTHERS THEN
RETURN 'Error: ' || SQLERRM;
END get_full_name;
Usage:
DECLARE
v_name VARCHAR2(100);
BEGIN
v_name := get_full_name(101);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
In above example we got the full employee name via function. Now in case user demands they need last name then first name we just need to change function and wherever it used it will change to new logic. This is an example of modularity
6. Example 2: Function to Calculate Annual Salary
CREATE OR REPLACE FUNCTION calculate_annual_salary (
p_emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
) RETURN NUMBER
IS
v_salary EMPLOYEES.SALARY%TYPE;
v_comm EMPLOYEES.COMMISSION_PCT%TYPE;
v_annual_sal NUMBER;
BEGIN
SELECT SALARY, NVL(COMMISSION_PCT, 0)
INTO v_salary, v_comm
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_emp_id;
v_annual_sal := (v_salary * 12) + (v_salary * 12 * v_comm);
RETURN v_annual_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END calculate_annual_salary;
Usage:
SELECT EMPLOYEE_ID, calculate_annual_salary(EMPLOYEE_ID) AS annual_salary
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;
Here we call the function in SQL statement instead of building complex query to achieve the results.
7. Example 3: Function with IN OUT Parameters
Functions can’t have OUT or IN OUT parameters directly. Use procedures or packages
if you need to return multiple values or use state management.
8. Calling Functions in SQL Statements
Functions that are deterministic and side-effect free can be used in SQL queries.
SELECT FIRST_NAME, LAST_NAME, calculate_annual_salary(EMPLOYEE_ID) AS annual_salary
FROM EMPLOYEES
WHERE JOB_ID = 'SA_REP';
9. Using Functions in WHERE, CASE, and JOIN
SELECT EMPLOYEE_ID, get_full_name(EMPLOYEE_ID) AS full_name
FROM EMPLOYEES
WHERE calculate_annual_salary(EMPLOYEE_ID) > 100000;
10. Best Practices for Writing Functions
Some best Practices will be useful are as below
- - Use %TYPE and %ROWTYPE for type safety.
- - Use DETERMINISTIC for pure functions.
- - Avoid DML inside SQL-used functions.
- - Always include exception handling.
- - Use clear, descriptive names and documentation.
11. Security and Privileges
GRANT EXECUTE ON calculate_annual_salary TO hr_user;
To run a function with the invoker’s rights:
CREATE OR REPLACE FUNCTION ... AUTHID CURRENT_USER
Search the default settings on function to get more details for above
12. Managing PL/SQL Functions in Oracle
List functions:
SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
Compile:
ALTER FUNCTION function_name COMPILE;
Drop:
DROP FUNCTION function_name;
You can use DBA_Funvtions and All_functions as well to see other schema's/ user functions.
DBA must grant access to view them
13. Summary
- - Functions return a single value and can be reused in SQL, PL/SQL, and other apps. Like oracle forms, oracle apex
- - Ideal for calculations, lookups, and formatting logic.
- - Examples covered full name retrieval and annual salary computation.
- - Good coding practices and security settings help maintain performance and safety.
- Start using PL/SQL functions to make your Oracle codebase cleaner, more modular, and easier to maintain.
- Execution can be control via Grants from the owner
0 Comments