Scribd Logo
Upload

Welcome to Scribd!

  • 35 views

    PL Function

    Uploaded by

    giriraj
    The document discusses PL/SQL functions. It provides the syntax for creating a function, describes the components of a function like the return type and parameters. It then provides examples of simple functions, table functions, and recursive functions. It also shows how to call functions and remove existing functions.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    PL Function

    Uploaded by

    giriraj
    35 views5 pages
    The document discusses PL/SQL functions. It provides the syntax for creating a function, describes the components of a function like the return type and parameters. It then provides examples of simple functions, table functions, and recursive functions. It also shows how to call functions and remove existing functions.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses PL/SQL functions. It provides the syntax for creating a function, describes the components of a function like the return type and parameters. It then provides examples of simple functions, table functions, and recursive functions. It also shows how to call functions and remove existing functions.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    35 views5 pages

    PL Function

    Uploaded by

    giriraj
    The document discusses PL/SQL functions. It provides the syntax for creating a function, describes the components of a function like the return type and parameters. It then provides examples of simple functions, table functions, and recursive functions. It also shows how to call functions and remove existing functions.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    PL/SQL Function

    The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure and
    a function is, a function must always return a value, and on the other hand a procedure may or may
    not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function
    too.

    Syntax to create a function:

    1. CREATE [OR REPLACE] FUNCTION function_name [parameters]  
    2. [(parameter_name [IN | OUT | IN OUT] type [, ...])]  
    3. RETURN return_datatype  
    4. {IS | AS}  
    5. BEGIN  
    6.    < function_body >  
    7. END [function_name];  

    Here:

    o Function_name: specifies the name of the function.


    o [OR REPLACE] option allows modifying an existing function.
    o The optional parameter list contains name, mode and types of the parameters.
    o IN represents that value will be passed from outside and OUT represents that this parameter
    will be used to return a value outside of the procedure.

    The function must contain a return statement.

    o RETURN clause specifies that data type you are going to return from the function.
    o Function_body contains the executable part.
    o The AS keyword is used instead of the IS keyword for creating a standalone function.

    PL/SQL Function Example


    Let's see a simple example to create a function.

    114.6K
    Space of Bass bespoke speaker system - the art of sound

    1. create or replace function adder(n1 in number, n2 in number)    
    2. return number    
    3. is     
    4. n3 number(8);    
    5. begin    
    6. n3 :=n1+n2;    
    7. return n3;    
    8. end;    
    9. /    

    Now write another program to call the function.

    1. DECLARE    
    2.    n3 number(2);    
    3. BEGIN    
    4.    n3 := adder(11,22);    
    5.    dbms_output.put_line('Addition is: ' || n3);    
    6. END;    
    7. /    

    Output:

    Addition is: 33
    Statement processed.
    0.05 seconds

    Another PL/SQL Function Example


    Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function
    which will compute and return the maximum of two values.

    1. DECLARE  
    2.    a number;  
    3.    b number;  
    4.    c number;  
    5. FUNCTION findMax(x IN number, y IN number)   
    6. RETURN number  
    7. IS  
    8.     z number;  
    9. BEGIN  
    10.    IF x > y THEN  
    11.       z:= x;  
    12.    ELSE  
    13.       Z:= y;  
    14.    END IF;  
    15.   
    16.    RETURN z;  
    17. END;   
    18. BEGIN  
    19.    a:= 23;  
    20.    b:= 45;  
    21.   
    22.    c := findMax(a, b);  
    23.    dbms_output.put_line(' Maximum of (23,45): ' || c);  
    24. END;  
    25. /  

    Output:

    Maximum of (23,45): 45
    Statement processed.
    0.02 seconds

    PL/SQL function example using table


    Let's take a customer table. This example illustrates creating and calling a standalone function. This
    function will return the total number of CUSTOMERS in the customers table.

    Create customers table and have records in it.

    Customers

    Id Name Department Salary

    1 alex web developer 35000

    2 ricky program developer 45000

    3 mohan web designer 35000

    4 dilshad database manager 44000

    Create Function:

    1. CREATE OR REPLACE FUNCTION totalCustomers  
    2. RETURN number IS  
    3.    total number(2) := 0;  
    4. BEGIN  
    5.    SELECT count(*) into total  
    6.    FROM customers;  
    7.     RETURN total;  
    8. END;  
    9. /  

    After the execution of above code, you will get the following result.

    Function created.

    Calling PL/SQL Function:

    While creating a function, you have to give a definition of what the function has to do. To use a
    function, you will have to call that function to perform the defined task. Once the function is called,
    the program control is transferred to the called function.

    After the successful completion of the defined task, the call function returns program control back to
    the main program.

    To call a function you have to pass the required parameters along with function name and if function
    returns a value then you can store returned value. Following program calls the function
    totalCustomers from an anonymous block:

    1. DECLARE  
    2.    c number(2);  
    3. BEGIN  
    4.    c := totalCustomers();  
    5.    dbms_output.put_line('Total no. of Customers: ' || c);  
    6. END;  
    7. /  

    After the execution of above code in SQL prompt, you will get the following result.

    Total no. of Customers: 4


    PL/SQL procedure successfully completed.

    PL/SQL Recursive Function


    You already know that a program or a subprogram can call another subprogram. When a subprogram
    calls itself, it is called recursive call and the process is known as recursion.

    Example to calculate the factorial of a number


    Let's take an example to calculate the factorial of a number. This example calculates the factorial of a
    given number by calling itself recursively.

    1. DECLARE  
    2.    num number;  
    3.    factorial number;  
    4.   
    5. FUNCTION fact(x number)  
    6. RETURN number   
    7. IS  
    8.    f number;  
    9. BEGIN  
    10.    IF x=0 THEN  
    11.       f := 1;  
    12.    ELSE  
    13.       f := x * fact(x-1);  
    14.    END IF;  
    15. RETURN f;  
    16. END;  
    17.   
    18. BEGIN  
    19.    num:= 6;  
    20.    factorial := fact(num);  
    21.    dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);  
    22. END;  
    23. /  

    After the execution of above code at SQL prompt, it produces the following result.

    Factorial 6 is 720
    PL/SQL procedure successfully completed.

    PL/SQL Drop Function


    Syntax for removing your created function:

    If you want to remove your created function from the database, you should use the following syntax.

    1. DROP FUNCTION function_name;  

    You might also like