***Welcome to ashrafedu.blogspot.com * * * This website is maintained by ASHRAF***

Posts

    Wednesday, 20 March 2024

    Procedures and Functions

    procedures or function is a group or set of SQL and PL/SQL statements that perform a specific task.

    A function and procedure is a named PL/SQL Block which is similar. 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.

    Procedures:

    A procedure is a named PL/SQL block which performs one or more specific task.  A procedure has a header and a body.

    The header consists of the name of the procedure and the parameters or variables passed to the procedure. 

    The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is named for repeated usage.

    Parameters to procedures can be passes in three ways:

    Parameters

    Description

    IN type

    These types of parameters are used to send values to stored procedures.

    OUT type

    These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.

    IN OUT type

    These types of parameters are used to send values and get values from stored procedures.

    A procedure may or may not return any value.

    Syntax:

    CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}    <Datatype>,…) 

    IS

      Declaration section<variable, constant> ;

    BEGIN

      Execution section

    EXCEPTION

      Exception section 

    END

    There are two ways to execute a procedure :

    • From the SQL prompt : EXECUTE [or EXEC] procedure_name;
    • from embedded SQl by call statement: call procedure-name;

    Functions:

    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.

     Syntax:

    CREATE [OR REPLACE] FUNCTION function_name [parameters] 

     RETURN return_datatype;  {IS, AS}

     Declaration_section <variable,constant> ;

     BEGIN  

        Execution_section

        Return return_variable;  

     EXCEPTION

       exception section  

        Return return_variable; 

     END;

    A function can be executed in the following ways.

    • As a part of a SELECT statement : SELECT emp_details_func FROM dual;
    • In a PL/SQL Statements like,  :  dbms_output.put_line(emp_details_func);

    Procedures VS Functions:

    • A function MUST return a value
    • A procedure cannot return a value
    • Procedures and functions can both return data in OUT and IN OUT parameters
    • The return statement in a function returns control to the calling program and returns the results of the function
    • The return statement of a procedure returns control to the calling program and cannot return a value
    • Functions can be called from SQL, procedure cannot
    • Functions are considered expressions, procedure are not

    No comments:

    Post a Comment