Functions in Oracle Database 23ai are stored PL/SQL programs that accept parameters, perform operations, and return a single value. Unlike procedures, which may or may not return values, functions are designed to return exactly one result. Functions are particularly useful when you want to encapsulate logic that can be reused in SQL queries or PL/SQL blocks.
This tutorial explains how to create, execute, and manage functions in Oracle Database 23ai. You will learn the syntax, parameter usage, exception handling, and integration with new features like JSON and vector operations.
Syntax of a Function in Oracle Database 23ai
Before creating a function, it is important to understand the syntax. Functions can include parameters, executable logic, error handling, and must always return a value.
CREATE [OR REPLACE] FUNCTION function_name
(parameter_name [IN | OUT | IN OUT] datatype [, ...])
RETURN return_datatype
IS
-- Local variable declarations
BEGIN
-- Executable statements
RETURN value;
EXCEPTION
-- Optional exception handling
WHEN exception_name THEN
-- Error handling statements
RETURN default_value;
END function_name;
/
- CREATE [OR REPLACE] FUNCTION: Creates or replaces an existing function.
- Parameters: Functions typically use
INparameters. UsingOUTorIN OUTis uncommon since functions must return a single value. - RETURN return_datatype: Defines the datatype of the value the function will return.
- RETURN value: Mandatory inside the function body to provide the result.
Creating a Simple Function
Functions can be used to calculate values dynamically. In this example, we create a function to calculate the annual salary of an employee by multiplying their monthly salary by 12.
Create a sample table
CREATE TABLE fnc_emp (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(10,2)
);
INSERT INTO fnc_emp VALUES (201, 'Clark', 'Kent', 7000);
INSERT INTO fnc_emp VALUES (202, 'Diana', 'Prince', 8000);
COMMIT;
Create the function
CREATE OR REPLACE FUNCTION fnc_annual_salary (
p_emp_id IN NUMBER
) RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary * 12
INTO v_salary
FROM fnc_emp
WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
/
- p_emp_id (IN): Input parameter; the employee ID used to fetch the monthly salary.
- The function multiplies the monthly salary by 12 to calculate the annual salary.
- RETURN v_salary ensures the function provides a numeric result.
Execute the function in SQL
SELECT fnc_annual_salary(202) AS annual_salary FROM dual;
Output:
ANNUAL_SALARY ------------- 96000
The function correctly calculated Diana’s annual salary as 96,000.
Executing a function using PL/SQL
Declare
n_sal number;
Begin
n_sal := fnc_annual_salary(202);
dbms_output.put_line('Salary: '||n_sal);
End;
Creating a Function with Multiple Parameters
Functions can also accept multiple parameters. Here we write a function to calculate bonus by applying a percentage increase to an employee’s salary.
CREATE OR REPLACE FUNCTION fnc_calculate_bonus (
p_salary IN NUMBER,
p_percent IN NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN p_salary * p_percent / 100;
END;
/
- p_salary (IN): The salary amount provided by the caller.
- p_percent (IN): Bonus percentage to be applied.
- The function returns a calculated bonus value.
Execute the function
SELECT fnc_calculate_bonus(7000, 15) AS bonus FROM dual;
Output:
BONUS ----- 1050
This shows how multiple input parameters can be used inside a function to compute a result.
Creating a Function with Exception Handling
Functions should handle errors gracefully, especially when working with queries. Here is an example where we fetch the salary of an employee but handle the case where the employee ID does not exist.
CREATE OR REPLACE FUNCTION fnc_get_salary (
p_emp_id IN NUMBER
) RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary
INTO v_salary
FROM fnc_emp
WHERE employee_id = p_emp_id;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1; -- Indicates employee not found
WHEN OTHERS THEN
RETURN -99; -- Indicates unexpected error
END;
/
- If the employee exists, the salary is returned.
- If the employee ID does not exist,
-1is returned. - For other errors,
-99is returned.
Execute the function
SELECT fnc_get_salary(205) AS salary_check FROM dual;
Output:
SALARY_CHECK ------------ -1
This demonstrates how exception handling inside functions helps manage errors without failing queries.
Using Functions with JSON in Oracle Database 23ai
Oracle Database 23ai introduces a native JSON data type. Functions can be created to process JSON data directly. This is useful when you need to extract values from JSON documents.
Create a table with JSON
CREATE TABLE fnc_customers (
customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_data JSON
);
INSERT INTO fnc_customers (customer_data) VALUES (
JSON('{"name": "Tony", "email": "tony@stark.com", "tier": "Platinum"}')
);
COMMIT;
Create the JSON function
CREATE OR REPLACE FUNCTION fnc_get_customer_tier (
p_customer_id IN NUMBER
) RETURN VARCHAR2
IS
v_tier VARCHAR2(50);
BEGIN
SELECT c.customer_data.tier.string()
INTO v_tier
FROM fnc_customers c
WHERE c.customer_id = p_customer_id;
RETURN v_tier;
END;
/
- p_customer_id (IN): Input parameter to identify the customer row.
- The function extracts the
tierproperty from the JSON column. - RETURN v_tier gives the tier string back to the caller.
Execute the JSON function
SELECT fnc_get_customer_tier(1) AS customer_tier FROM dual;
Output:
CUSTOMER_TIER ------------- Platinum
This shows how Oracle 23ai functions can work with JSON natively without conversion functions.
Using Functions with Vector Data in Oracle Database 23ai
Oracle Database 23ai includes support for vector data types, enabling AI-based similarity search within the database. You can write functions that accept vectors and return similarity scores or document IDs.
Create a vector table
CREATE TABLE fnc_documents (
doc_id NUMBER PRIMARY KEY,
content CLOB,
embedding VECTOR(4)
);
INSERT INTO fnc_documents VALUES (
1,
'Oracle 23ai adds vector search support.',
TO_VECTOR('[0.15, 0.88, -0.41, 0.62]')
);
INSERT INTO fnc_documents VALUES (
2,
'Functions in Oracle 23ai are powerful for developers.',
TO_VECTOR('[0.14, 0.89, -0.40, 0.60]')
);
COMMIT;
Create a vector similarity function
CREATE OR REPLACE FUNCTION fnc_vector_similarity (
p_doc_id IN NUMBER,
p_query_vec IN VECTOR
) RETURN BINARY_DOUBLE
IS
v_distance BINARY_DOUBLE;
BEGIN
SELECT embedding <-> p_query_vec
INTO v_distance
FROM fnc_documents
WHERE doc_id = p_doc_id;
RETURN v_distance;
END;
/
- p_doc_id (IN): The document to compare.
- p_query_vec (IN): The input vector provided for similarity search.
- The function calculates and returns the distance score. A smaller distance means more similarity.
Execute the function
SELECT fnc_vector_similarity(2, TO_VECTOR('[0.13, 0.90, -0.42, 0.59]')) AS sim_score
FROM dual;
Output:
SIM_SCORE --------- 0.01
This demonstrates how functions in Oracle 23ai can integrate vector AI search directly in SQL.
Managing Functions
Like procedures, functions can be recompiled, altered, or dropped when needed.
-- Recompile a function ALTER FUNCTION fnc_annual_salary COMPILE; -- Drop a function DROP FUNCTION fnc_calculate_bonus;
Functions can also be called inside SQL queries, unlike procedures, making them more flexible for computations within SELECT statements.
Conclusion
Functions in Oracle Database 23ai provide a powerful way to encapsulate reusable logic that always returns a value. With proper syntax understanding, use of parameters, and exception handling, you can create robust functions that integrate seamlessly with applications.
Oracle Database 23ai further enhances functions by allowing them to process JSON and vector data directly, enabling developers to build modern, AI-powered solutions within the database itself.
