Functions in PL/SQL

What is a Function in PL/SQL?
---------------------------------------

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is,
a function must always return a value, but a procedure may or may not return a value.

Return Type: The header section defines the return type of the function.
The return datatype can be any of the oracle datatype like varchar, number,boolean,date,record,object etc.


CREATE FUNCTION fact (n NUMBER)
 RETURN NUMBER IS
    returnVal NUMBER;
 BEGIN
    IF (n = 1) THEN
       returnVal := 1;
    ELSE
       returnVal := n*fact(n-1);
    END IF;
    RETURN returnVal;
 END;
 /


How to execute a PL/SQL Function?
----------------------------------------------

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

Variable_name:=  Function_name;

If Variable_name is of datatype varchar2 we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT Function_name  FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(Function_name);

This line displays the value returned by the function.



Difference between Procedure and function:
---------------------------------------------------------

Functions MUST return a value, procedures don't need to.

You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
 For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error. However an autonomous transaction function can.

You cannot call a procedure in a SQL query.


How can one search PL/SQL code for a string/ key value?
----------------------------------------------------------------------------

SELECT type, name, line
  FROM   user_source
 WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%');

Is there a limit on the size of a PL/SQL block?
------------------------------------------------------------

select * from dba_object_size where name = 'procedure_name'


Examine the query:
-------------------------

select round(119.05,-1) from dual             ---round the left side value of the decimal point


A forward declaration Meaning
------------------------------------------

DECLARE
    v_TempVal BINARY_INTEGER := 5;

      PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);

      PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('A(' || p_Counter || ')');
        IF p_Counter > 0 THEN
         B(p_Counter);
         p_Counter := p_Counter - 1;
       END IF;
     END A;

     PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
     BEGIN
       DBMS_OUTPUT.PUT_LINE('B(' || p_Counter || ')');
       p_Counter := p_Counter - 1;
       A(p_Counter);
     END B;
     BEGIN
      B(v_TempVal);
    END;


Ur's
AmarAlam