Oracle Database functions are powerful tools for streamlining operations and making code simpler and more maintainable. This article carefully explains the points where beginners often stumble and introduces many practical examples that are immediately useful in a professional setting.
Goals of this article:
- Understand the basics of functions.
- Grasp the differences between built-in functions and user-defined functions.
- Learn practical application examples and best practices for real-world use.
What is a Function?
A function is a reusable unit of code that performs a specific process and returns a result. It has the following characteristics:
- It accepts input (arguments) and performs calculations or operations.
- It returns a result (return value).
- It can be easily called from within SQL statements or PL/SQL.
For example, when calculating the length of a string, you can write it concisely using Oracle’s built-in function LENGTH:
SELECT LENGTH('Oracle Database') AS result FROM dual;
SQL> SELECT LENGTH('Oracle Database') AS result FROM dual;
RESULT
----------
15
1. Built-in Functions: Standard Features Even Beginners Can Use Easily
Oracle’s built-in functions are broadly divided into the following categories.
| Category | Key Function Examples | Purpose |
| String Manipulation | LENGTH, SUBSTR, UPPER | Get string length, extract substrings, convert to uppercase |
| Numeric Manipulation | ROUND, MOD, ABS | Rounding or calculating absolute values |
| Date Manipulation | SYSDATE, ADD_MONTHS | Get the current date or perform date calculations |
| Conversion Functions | TO_CHAR, TO_DATE | Data type conversion |
Below are specific examples of major built-in functions.
String Manipulation Examples
LENGTH: Gets the length of a string.
SELECT LENGTH('Oracle Database') AS length_result FROM dual;
SQL> SELECT LENGTH('Oracle Database') AS result FROM dual;
RESULT
----------
15
SUBSTR: Extracts a substring.
SELECT SUBSTR('Hello, Oracle!', 8, 6) AS substring_result FROM dual;
SQL> SELECT SUBSTR('Hello, Oracle!', 8, 6) AS substring_result FROM dual;
SUBSTR
------
Oracle
UPPER: Converts a string to uppercase.
SELECT UPPER('hello world') AS upper_result FROM dual;
SQL> SELECT UPPER('hello world') AS upper_result FROM dual;
UPPER_RESUL
-----------
HELLO WORLD
Date Manipulation Examples
SYSDATE: Current date and time.
SELECT SYSDATE AS current_date FROM dual;
SQL> SELECT SYSDATE AS current_date FROM dual;
CURRENT_D
---------
14-NOV-25
MONTHS_BETWEEN: Calculates the difference in months.
SELECT MONTHS_BETWEEN(TO_DATE('2025-12-31', 'YYYY-MM-DD'), SYSDATE) AS months_diff FROM dual;
SQL> SELECT MONTHS_BETWEEN(TO_DATE('2025-12-31', 'YYYY-MM-DD'), SYSDATE) AS months_diff FROM dual;
MONTHS_DIFF
-----------
1.54202434
Applied Example: Calculate days remaining until a specified date
SELECT TRUNC(TO_DATE('2025-12-31', 'YYYY-MM-DD') - SYSDATE) AS days_left FROM dual;
SQL> SELECT TRUNC(TO_DATE('2025-12-31', 'YYYY-MM-DD') - SYSDATE) AS days_left FROM dual;
DAYS_LEFT
----------
46
2. User-Defined Functions: Custom Logic for Practical Use
When built-in functions aren’t enough, you create your own user-defined functions using PL/SQL.
Creating a Basic Function
The following is an example of a function that calculates the tax-included price of a product.
Function Definition
CREATE OR REPLACE FUNCTION calc_taxed_price(
price NUMBER, -- Product price
tax_rate NUMBER DEFAULT 0.1 -- Consumption tax rate (default 10%)
) RETURN NUMBER IS
BEGIN
RETURN price * (1 + tax_rate);
END;
/
Usage Example
SELECT calc_taxed_price(1000) AS taxed_price FROM dual;
SQL> CREATE OR REPLACE FUNCTION calc_taxed_price(
2 price NUMBER,
3 tax_rate NUMBER DEFAULT 0.1
4 ) RETURN NUMBER IS
5 BEGIN
6 RETURN price * (1 + tax_rate);
7 END;
8 /
Function created.
SQL> SELECT calc_taxed_price(1000) AS taxed_price FROM dual;
TAXED_PRICE
-----------
1100
Applied Example: Changing the tax rate
SELECT calc_taxed_price(1000, 0.08) AS taxed_price FROM dual;
SQL> SELECT calc_taxed_price(1000, 0.08) AS taxed_price FROM dual;
TAXED_PRICE
-----------
1080
Practical Application Examples
Function to calculate a discounted price
CREATE OR REPLACE FUNCTION calc_discounted_price(
price NUMBER,
discount_rate NUMBER DEFAULT 0.2 -- Discount rate (default 20%)
) RETURN NUMBER IS
BEGIN
RETURN price * (1 - discount_rate);
END;
/
Conditional price calculation
CREATE OR REPLACE FUNCTION calc_final_price(
price NUMBER,
discount_rate NUMBER DEFAULT 0.2,
tax_rate NUMBER DEFAULT 0.1
) RETURN NUMBER IS
BEGIN
RETURN (price * (1 - discount_rate)) * (1 + tax_rate);
END;
/
Execution Example
SELECT calc_final_price(1000, 0.15, 0.08) AS final_price FROM dual;
SQL> CREATE OR REPLACE FUNCTION calc_final_price(
2 price NUMBER,
3 discount_rate NUMBER DEFAULT 0.2,
4 tax_rate NUMBER DEFAULT 0.1
5 ) RETURN NUMBER IS
6 BEGIN
7 RETURN (price * (1 - discount_rate)) * (1 + tax_rate);
8 END;
9 /
Function created.
SQL> SELECT calc_final_price(1000, 0.15, 0.08) AS final_price FROM dual;
FINAL_PRICE
-----------
918
3. Points to Note and Best Practices
Be Mindful of Performance
- Caution with SQL on large data: Overusing functions within SQL can degrade performance.
- Utilize Indexes: Indexes may not be used on columns where a function is applied. Consider a
FUNCTION-BASED INDEX.
Ensuring Security
- Privilege Management: Design functions to run with the minimum necessary privileges.
- Input Data Validation: Don’t forget to check input data to prevent SQL Injection.
Error Handling
Implement appropriate exception handling to prevent unintended errors.
CREATE OR REPLACE FUNCTION safe_division(
numerator NUMBER,
denominator NUMBER
) RETURN NUMBER IS
BEGIN
IF denominator = 0 THEN
RETURN NULL; -- Prevent zero division
ELSE
RETURN numerator / denominator;
END IF;
END;
/
4. Practical Function Usage Ideas
- Recording Audit Logs: Create a function to record user operations and automatically insert them into a log table.
- Processing Incorporating Dynamic SQL: Implement flexible logic that dynamically generates and executes SQL.
- Integrating Multiple Databases: Build a function that retrieves remote data by utilizing database links.
Summary
Oracle Database functions are an essential skill for streamlining data operations and expressing business logic concisely.
- Streamline basic operations by utilizing built-in functions.
- Implement custom logic by creating user-defined functions.
- Apply them in practice while considering performance and security.
By actually creating and using functions, your database operations can be significantly improved. As a next step, try creating and applying functions that fit your own business needs!
[reference]
Oracle Database SQL Language Reference, 19c
コメント