How to Call Function in Oracle PL/SQL?

Function in Oracle

Stored functions can be called by name and are named chunks of PL/SQL statements that are kept in the Oracle database. Functions, like procedures, let you store complex application logic and business rules inside the database, which facilitates code consistency and maintenance. The functionality of SQL can be effectively expanded using them, enabling you to tailor it to your own requirements.

Functions must have a single value returned to the environment from which they are called, and they are made to work with data items. From straightforward scalars like numbers and texts to intricate structures like collections or object types, this return value can be of almost any PL/SQL datatype. The SELECT list and WHERE clause are two examples of SQL statements that can use functions, similar to Oracle’s built-in functions like SUBSTR and TO_CHAR.

Creating Functions

A standalone stored function is created with the CREATE FUNCTION statement. Although the whole syntax for establishing a function is extensive, it essentially entails specifying the function’s name, parameters, and the datatype of the value it will return.

The syntax for CREATE FUNCTION is as follows:

CREATE [OR REPLACE] FUNCTION [schema.]function
  [( argument [ IN | OUT | IN OUT ] [NOCOPY] datatype
  [, argument [ IN | OUT | IN OUT ] [NOCOPY] datatype]... )]
  RETURN datatype
  [{ invoker_rights_clause | DETERMINISTIC | parallel_enable_clause } [ ... ]]
{ { AGGREGATE | PIPELINED } USING [schema.]implementation_type | [PIPELINED] { IS | AS } { pl/sql_function_body | call_spec }};

Key components of the CREATE FUNCTION syntax

OR REPLACE: This optional statement lets you recreate the function if it already exists while keeping any EXECUTE privileges that have been applied to it intact.
schema.function: Specifies the name of the function. If schema is omitted, the function is created in your current schema. To create a function in another user’s schema, you need the CREATE ANY PROCEDURE system privilege; for your own schema, CREATE PROCEDURE is sufficient.
argument: The parameters that the function accepts are defined by the argument. A datatype, a mode (IN, OUT, or IN OUT), and a name are assigned to every argument.
- IN: Specifies that a value is passed into the function and cannot be changed by the function. This is the default mode if none is specified. For functions called from SQL statements, all parameters must be IN mode.
- OUT: Specifies that the parameter is used to pass a value back from the function to the calling environment. Inside the function, an OUT parameter acts like an uninitialised variable. Functions should generally return values via the RETURN clause rather than OUT parameters to maintain clarity.
- IN OUT: Specifies that the parameter is used to pass a value into the function, which can then be modified and passed back out to the caller. Similar to OUT parameters, IN OUT parameters are generally discouraged for functions.
- NOCOPY: A hint to Oracle to pass the variable value back to the user as quickly as possible, potentially improving performance for large IN OUT parameters.
RETURN datatype: The obligatory RETURN datatype clause indicates the datatype of the value that the function will return. Oracle obtains the length, accuracy, and scale from the calling environment; the datatype is unable to provide these.

An example of a construct FUNCTION would be to construct a function called OVERDUE_CHARGES that, using a fictitious BOOKSHELF_CHECKOUT table, determines an individual’s overdue book charges.

-- Assume BOOKSHELF_CHECKOUT table exists for this example.
-- First, let's create a dummy table and insert some values for demonstration.
-- This part of the code provides context for the function.
-- Output will be 'Table created.' and '2 rows inserted.'

CREATE TABLE BOOKSHELF_CHECKOUT (
    Name VARCHAR2(50),
    CheckoutDate DATE,
    ReturnedDate DATE
);

INSERT INTO BOOKSHELF_CHECKOUT (Name, CheckoutDate, ReturnedDate) VALUES ('FRED FULLER', TO_DATE('01-JAN-2023', 'DD-MON-YYYY'), TO_DATE('20-JAN-2023', 'DD-MON-YYYY'));
INSERT INTO BOOKSHELF_CHECKOUT (Name, CheckoutDate, ReturnedDate) VALUES ('JANE DOE', TO_DATE('05-FEB-2023', 'DD-MON-YYYY'), TO_DATE('10-FEB-2023', 'DD-MON-YYYY'));

COMMIT;

-- Now, the function definition.
-- Output will be 'Function created.'

CREATE OR REPLACE FUNCTION OVERDUE_CHARGES (aName IN VARCHAR2)
RETURN NUMBER
IS
    owed_amount NUMBER(10,2);
BEGIN
    SELECT SUM(((ReturnedDate-CheckoutDate) -14)*0.20)
    INTO owed_amount
    FROM BOOKSHELF_CHECKOUT
    WHERE Name = aName AND (ReturnedDate-CheckoutDate) > 14; -- Only sum if overdue (>14 days)
    RETURN(owed_amount);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle cases where no matching data is found
        RETURN 0;
END;
/

The Clause

The function’s datatype is specified by the RETURN clause in the function header (for example, RETURN NUMBER). Since a function is required to return a value by definition, it must contain this clause.

The RETURN statement (e.g., RETURN(owed_amount);) is used within the executable body of the function to indicate the actual value that is returned to the caller environment. When a RETURN statement is used, control returns to the calling code with the supplied value and the function ends instantly. A function may have more than one RETURN statement, but each call will only execute one of them.

Using a single RETURN statement as the function’s final executable statement and allocating the result to a specific variable (such as return_value) throughout the function’s logic is a widely accepted recommended practice. In the event that a function runs through without running into a RETURN statement or raising an exception, Oracle will raise an ORA-06503: Function returned without value error.

Key Difference Functions vs. Procedures

The return behaviour of functions and procedures is where they diverge most significantly. The RETURN clause of a function is specifically made to return a single value to the caller. Procedures, on the other hand, are not required to return any value. Although a procedure may have a RETURN statement, it only stops the process and returns control it does not explicitly return a value.

How they are employed is determined by one essential difference:

Functions are usually employed, like built-in operators or functions, as part of an expression inside a larger SQL statement or PL/SQL block. They work with data and give back an output that can be used in the expression.
Procedures can be isolated executable statements that carry out one or more tasks, like complicated business logic execution or data manipulation (INSERT, UPDATE, DELETE). They are known as comprehensive instructions.

Additionally, when called from SQL statements, functions often limit their parameters to IN mode, but procedures are allowed to transfer data in both directions use IN, OUT, and IN OUT parameters.

Calling Programs: Procedures and Functions

The secret to making good use of these stored objects is knowing how to summon them.

Calling Procedures

Procedures are executable, stand-alone statements.

From SQL*Plus: From SQL*Plus, you can use the EXECUTE command, followed by the name of the procedure and any parenthetically enclosed arguments.
From within other PL/SQL programs (procedures, functions, packages, triggers): By passing any necessary parameters, you merely refer to the operation by its name.
Calling packaged procedures: When a procedure is a component of a package, you need to use dot notation to indicate both the procedure name and the package name.

Calling Functions

Since functions return a value and are usually employed inside expressions, they are called differently.

In SQL expressions: Functions in SQL expressions can be utilised directly in the WHERE clause or column list of a SELECT query.
Assigning to a variable in PL/SQL: The assignment operator (:=) in PL/SQL allows you to assign a function’s return value to a variable.
From within other PL/SQL programs: Functions are called analogous to how they are used in SQL statements, as part of an expression.
Calling packaged functions: Packaged functions, like packaged procedures, need the package name to be provided.
Referencing remote functions: Database linkages allow functions to be called from distant databases.

Parameter Association: There are two techniques to link formal parameters to actual parameters when using arguments to invoke procedures or functions:

Positional notation: In positional notation, arguments are provided in the order that the procedure or function declares them. This is the most widely used approach.
Named notation: This makes arguments easier to read, especially for programs with a lot of parameters or optional parameters, by explicitly stating the formal parameter name. To connect the real argument to the formal parameter name, use =>. It is possible to combine positional and named notation; but, once named notation is used, it must be used for all further inputs.

Your Oracle database applications’ stored functions and procedures can be efficiently created, used, and managed if you comprehend these ideas.

Page Content

Tutorials