Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article contains information about PL/SQL Functions, and examples.
What is Function in Oracle?
Oracle PL/SQL functions structure are structures that can store commands under a name in the database and can be run again if needed as in the procedure structure.
What is the Difference Between Function and Stored procedure in Oracle?
The difference of the functions from the procedures is that they have to return values.
The keywords to be used when creating the function are as follows.
1 2 3 4 5 6 7 | CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] data_type [, ...])] RETURN return_data_type {IS | AS} BEGIN -- Commands END [function_name]; |
An example of creating a function with Oracle PL / SQL is as follows.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE function SUM_Function ( S1 IN PLS_INTEGER, S2 IN PLS_INTEGER ) RETURN PLS_INTEGER IS BEGIN RETURN S1 + S2; END; |
We can run the functions as below;
1 2 3 | BEGIN DBMS_OUTPUT.put_line(SUM_Function(10, 20)); END; |
SYS.all_objects table can be used to get information about the created functions.
1 | SELECT * FROM SYS.all_objects WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = 'USER_NAME'; |
The following command is used to delete a function.
1 | DROP FUNCTION function_name; |
1 | DROP FUNCTION SUM_Function; |
The reason for using the functions is to perform operations where the functions in Oracle are insufficient.
PL SQL Function Example with parameters
The function that converts the first letter in a text to uppercase can be written as follows.
1 2 3 4 5 6 7 8 | CREATE OR REPLACE function CONVERT_FIRST_LETTER_TO_UPPERCASE ( MYTEXT IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN UPPER(SUBSTR(MYTEXT, 1, 1)) || LOWER(SUBSTR(MYTEXT, 2)); END; |
You can run the function as below;
1 2 3 4 | BEGIN DBMS_OUTPUT.put_line(CONVERT_FIRST_LETTER_TO_UPPERCASE('HELLO!')); DBMS_OUTPUT.put_line(CONVERT_FIRST_LETTER_TO_UPPERCASE('hello!')); END; |
In addition, functions can be used for calculating a continuously used process (such as VAT calculation).
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about functions at docs.oracle.com
