Oracle PL/SQL Functions

Yusuf SEZER

4 years ago

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.

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];

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.

CREATE OR REPLACE function SUM_Function (
    S1 IN PLS_INTEGER,
    S2 IN PLS_INTEGER
)
RETURN PLS_INTEGER
IS
BEGIN
    RETURN S1 + S2;
END;

CREATE OR REPLACE function SUM_Function (

S1 IN PLS_INTEGER,

S2 IN PLS_INTEGER

)

RETURN PLS_INTEGER

BEGIN

RETURN S1 + S2;

END;

We can run the functions as below;

BEGIN
    DBMS_OUTPUT.put_line(SUM_Function(10, 20));
END;

BEGIN

DBMS_OUTPUT.put_line(SUM_Function(10, 20));

END;

SYS.all_objects table can be used to get information about the created functions.

SELECT * FROM SYS.all_objects WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = 'USER_NAME';

1	SELECT * FROM SYS.all_objects WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = 'USER_NAME';

The following command is used to delete a function.

DROP FUNCTION function_name;

1	DROP FUNCTION function_name;

DROP FUNCTION SUM_Function;

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.

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;

CREATE OR REPLACE function CONVERT_FIRST_LETTER_TO_UPPERCASE (

MYTEXT IN VARCHAR2

)

RETURN VARCHAR2

BEGIN

RETURN UPPER(SUBSTR(MYTEXT, 1, 1)) || LOWER(SUBSTR(MYTEXT, 2));

END;

You can run the function as below;

BEGIN
    DBMS_OUTPUT.put_line(CONVERT_FIRST_LETTER_TO_UPPERCASE('HELLO!'));
    DBMS_OUTPUT.put_line(CONVERT_FIRST_LETTER_TO_UPPERCASE('hello!'));
END;

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.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about functions at docs.oracle.com