Define and call a function : Function Definition « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Function Definition

Define and call a function

   


SQL>
SQL>
SQL> -- A function block.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     temp  NUMBER;
  3
  4  FUNCTION iifn(boolean_expression IN BOOLEAN,
  5                true_number IN NUMBER,
  6                false_number IN NUMBER)
  7  RETURN NUMBER IS
  8  BEGIN
  9       IF boolean_expression THEN
 10         RETURN true_number;
 11       ELSIF NOT boolean_expression THEN
 12         RETURN false_number;
 13       ELSE
 14         RETURN NULL;
 15       END IF;
 16  END;
 17
 18  BEGIN
 19     DBMS_OUTPUT.PUT_LINE(iifn(2 > 1,1,0));
 20     DBMS_OUTPUT.PUT_LINE(iifn(2 > 3,1,0));
 21
 22     temp := iifn(null,1,0);
 23     IF temp IS NULL THEN
 24       DBMS_OUTPUT.PUT_LINE('NULL');
 25     ELSE
 26       DBMS_OUTPUT.PUT_LINE(temp);
 27     END IF;
 28  END;
 29  /
1
0
NULL

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>








Related examples in the same category

1.Define and use function in select clause
2.A stored function.
3.Use user-defined function in if statement
4.Recursive function
5.function with no return type
6.Recursive function Factorial
7.A local function
8.Recursive function 2
9.demonstrates the behavior of the DETERMINISTIC keyword.
10.Function to convert celsius to fahrenheit
11.Function to convert fahrenheit to celsius
12.A function is executed like any other SQL built-in function:
13.Count Employee from a function and return value back
14.How stored functions can be called from SQL
15.Raise exception from inner function