java2db

PL/SQL Functions



PL/SQL Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions also can receive or return zero or more values through their parameter lists.
A function must have at least one RETURN value statement in the execution section.The RETURN clause in the function header specifies the datatype of the returned value.

The major difference between a procedure and a function is, function must always return a value, but procedure may or may not return a value.

Syntax:

CREATE [OR REPLACE] FUNCTION [Function Name] (Parameter List)
RETURN [Data type]
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC | PARALLEL_ENABLED | PIPELINES]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
[Declaration Statements];
BEGIN
[Executable Statements];
RETURN [Value] ;
EXCEPTION
[Exception handlers]
END [Function Name];

Example:

 

PL/SQL Functions PL/SQL Functions  call:

A function can be called anywhere that an expression of the same type can be used.  

  In an assignment statement : Assign a function return value to a same type variable using assignment operator(:=)

  To set a default value : Set , function return type as a default value to the same type variable  with DEFAULT keyword.

  In a Boolean expression: It is possible to check a Boolean condition by using function return value.

In a SQL statement: Execute a SQL statement  by using function return value, in a where clause condition like bellow.

 

PL/SQL Functions Parameters:
Procedures, functions and cursors may have a parameter list.This list contains one or more parameters that allow you to pass information back and forth between the subprogram and the calling program.Each parameter is defined by its name , datatype , mode, and optional default value.Functions can accept parameters in all three modes (IN,OUT and IN OUT).

 

PL/SQL FunctionsReturn Type:

The header section defines the return type of the function.The return datatype can be any of the oracle datatype like varchar , number etc.
The execution and exception section both should return a value which is of the datatype defined in the header section.

 

PL/SQL Functions Restrictions on calling PL/SQL functions from SQL expressions :

Unlike procedures , a stored function can be called from SELECT statements , provided it must not violate the database purity levels. These rules are as follows.
  A function called from a SELECT statement cannot contain DML statements.
  A function called from a UPDATE or DELETE statement on a table cannot query (SELECT) or perform

….transaction (DMLs) on the same table.
  A function called from SQL expressions cannot contain the TCL (COMMIT or RPLLBACK) command or

….the DDL (CREATE or ALTER) command.
  The return type of the function must be a valid SQL data type.




Related Posts :
PL/SQL Arrays and nested tables ( Collections )
IF THEN ELSE and CASE in PL/SQL
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Variables
   [will not be published]



^ <