Scribd Logo
Upload

Welcome to Scribd!

  • 11 views

    Functions in PLSQL

    Uploaded by

    YOUR LIFE
    The document discusses PL/SQL functions. It provides the syntax for creating a PL/SQL function, which is similar to a procedure but must return a value. It includes examples of simple functions that add two numbers, find the maximum of two values, and calculate a factorial recursively. It also demonstrates how to call a function and drop an existing function.

    Copyright:

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

    Functions in PLSQL

    Uploaded by

    YOUR LIFE
    11 views10 pages
    The document discusses PL/SQL functions. It provides the syntax for creating a PL/SQL function, which is similar to a procedure but must return a value. It includes examples of simple functions that add two numbers, find the maximum of two values, and calculate a factorial recursively. It also demonstrates how to call a function and drop an existing function.

    Original Title

    FUNCTIONS IN PLSQL.pptx

    Copyright

    © © All Rights Reserved

    Available Formats

    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 PL/SQL function, which is similar to a procedure but must return a value. It includes examples of simple functions that add two numbers, find the maximum of two values, and calculate a factorial recursively. It also demonstrates how to call a function and drop an existing function.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    11 views10 pages

    Functions in PLSQL

    Uploaded by

    YOUR LIFE
    The document discusses PL/SQL functions. It provides the syntax for creating a PL/SQL function, which is similar to a procedure but must return a value. It includes examples of simple functions that add two numbers, find the maximum of two values, and calculate a factorial recursively. It also demonstrates how to call a function and drop an existing function.

    Copyright:

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

    FUNCTIONS IN PLSQL

    NAME : Manobalan P
    ROLL NO. : 22ITU088
    DEPARTMENT: Computer Technology
    COURSE : B.Sc(IT)-2nd Year
    SUBJECT : RDBMS
    SUBJECT CODE : 22ITU302
    SUBMITTED TO : Mrs.S.BHUVANESWARI
    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 [parameter


    s]
    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:
    •Function_name: specifies the name of the function.
    •[OR REPLACE] option allows modifying an existing function.
    •The optional parameter list contains name, mode and types of
    the parameters.
    •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.


    •RETURN clause specifies that data type you are going to return
    from the function.
    •Function_body contains the executable part.
    •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.
    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 v
    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.

    Customers
    Id Name Department Salary
    1 alex web developer 35000
    2 ricky program 45000
    developer
    3 mohan web designer 35000
    4 dilshad database 44000
    manager
    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