PL/SQL function

PL/SQL function and procedures

PL/SQL function and procedures – PL/SQL allows you to define functions and procedures. These are similar to functions and procedures defined in any other language, and they are defined as one PL/SQL block.

PL/SQL Function

The syntax for defining a function is as follows:

FUNCTION name [(argument-list)] RETURN data-type {IS, AS}
Variable-declarations
BEGIN
Program-code
[ EXCEPTION
error-handling-code]
END;

In this syntax,
name: The name you want to give the function.
argument-list :  List of input and/or output parameters for the functions.
data-type: The data type of the function’s return value.
Variable-declarations: Where you declare any variables that are local to the function.
program-code: Where you write PL/SQL statements that make up the function.
error-handling-code: Where you write error handling routine.

Notice that the function block is similar to the PL/SQL block that we discussed earlier. The keyword DECLARE has been replaced by FUNCTION header, which names the
Function, describes the parameter and indicates the return type.
Function can be called by using name(argument list)
Example:
FUNCTION check(b_exp in BOOLEAN,
True_number in NUMBER,
False_number in NUMBER)
RETURN NUMBER IS
BEGIN
IF b_exp THEN RETURN true_number;
ELSE
RETURN false_number;
END IF;
END;The above function can be called as follows.
Check ( 2 >1,1,0)

Check ( 5=0,1,0)

PL/SQL Procedures

The declaration of procedures is almost identical to that function and syntax is given below.

PROCEDURE name [(argument list)] {IS, AS}
Variable declaration
BEGIN
Program code
[EXCEPTION
Error handling code]
END;

Here name is the name that you want to give the procedure and all other are similar to function declaration. Procedure declaration resembles a function declaration except that there is no data type and key word PROCEDURE is used instead of FUNCTION.
Ex: PROCEDURE SWAPN (a in out number, B in out number)

Is
Temp_num   NUMBER;
BEGIN
Temp_num:=A;
A : = B;
B : = temp_num;
END;
The above procedure can be called as follows.
Swapn (3,4);

Swapn (-6,7);

Leave a Reply

Your email address will not be published.