Oracle-PLSQL Functions

• A storage function is a named PL/SQL block. It can accept parameters and can be called. Generally, a function is used to calculate a value.
• A function has a similar structure with a process, but a function must return a value to the main call environment. On the contrary, a process can return no return value or multiple values to its calling environment.
• Like a process, a function has a head, a declaration part, an execution part, and an exception handling part. There must be a return clause in the function header and at least one return statement in the execution part.
• Functions can be stored in the database as a solution object for repeated execution.
• A function can be called as an SQL expression or a part of a PL/SQL expression.

Function Syntax: Create [or replace] function function_name <br/> [(parameter1 [mode1] datatype1, <br/> parameter2 [mode2] datatype2, <br/>...)] <br/> return datatype <br/> is | as <br/> PL/SQL block; <br/> PL/SQL
Each block must have at least one Return Statement

Syntax description

Parameter description

Function_name function name

Parameter: name of a PL/SQL variable. Its value is passed to the function.

The type of the mode parameter. It can be an in, out, or inout parameter. Generally, the in parameter is used.

Data Type of the datatype Parameter

Data Type of the returndatatype Return Value

PL/sqlblock defines the program body of the action to be executed by the function

Procedure:

1. Enter the text of the create function statement in an editor and save it as an SQL script file.
2. Run the script file that stores the source code and compile the function.
3. Use show errors to view compilation errors

Create or replace function get_sal <br/> (p_id in EMP. empno % Type) <br/> return number <br/> is <br/> v_sal EMP. sal % Type: = 0; <br/> begin <br/> select Sal <br/> into v_sal <br/> from EMP <br/> where empno = p_id; <br/> return v_sal; <br/> end get_sal; <br/>/<br/>Create and call a function in SQL * Plus

Variable g_sal number

Exec: g_sal: = get_sal (7369)

Print g_sal

Location of the User-Defined Function called

• SELECT statement
In the list, • In the where and having clauses, • In the start with, order by, and groupby clauses • In the values clause of the insert statement • In the set clause of the update statement

Select empno, tax (SAL)

From EMP

Where tax (SAL)> (selectmax (tax (SAL ))

From EMP

Where deptno = 30)

Order by tax (SAL) DESC;

SlaveSQLRestrictions on function calls

To call a function from an SQL expression, the user-defined function must:

• Only accept in parameters • only accept valid SQL data types, not special PL/SQL types

As a parameter

• The returned data type is a valid SQL data type, not a special PL/SQL

Type

• Functions called in the update or delete statement of a table cannot query or update the same table. • from SQL
The function called in the statement cannot contain the statement used to end the transaction.

Function deletion:

Drop function function_name <br/>

Procedures and functions: