Procedures and Functions in Oracle

Procedures and Functions in Oracle

1.What is a procedure?

A.A procedure is a PL/SQL block which is compiled and permanently stored in the database for repeated execution.
Syntax:
create or replace procedure<procedure_Name>
IS
Begin
--------------
--------------
--------------
END;
  /

2.Types of Procedures

A.Procedure can have 3 types of parameters
   i.Inparameter
   ii.OutParameter
   iii.In Out parameter

3.Why we are using InParameter?

A.InParameter’s are used to accept values from the user.
Ex: create a procedure which accepts two numbers and display the num?
*Create or replace procedure ADD_NUM(A IN number, B IN number)
IS
C number;
Begin
C := A + B;
DBMS_OUTPUT.PUT_LINE(‘The sum is…’||C);
END;
  /

4.Query to see all the list of procedures:

Select object_NAME from user_objects where object_TYPE = ‘PROCEDURE’;

5.To see the source code of a procedure:
Select text from user_source where NAME = ‘ADD_NUM’;

6. Spool command:

A.This command is used to extract the character which we can see in SQL*PLUSE environment to a text file.

7. Out parameters:

out parameters are used to return the value to the user.
Ex:
Create a procedure which accepts two numbers and return the sum?
    Create or replace procedure ret_sum
                                                     (A IN number,B IN number, C OUT number)
    IS
    Begin
    C := A + B;
    END;
      /
8. What are the Steps to invoke procedures which are having out parameters:

A.Step 1: create bind variable
    SQL> variable N number
Step 2: execute the procedure
    SQL> EXEC Ret_sum(10,20,:N)
Step 3: print bind variable
    SQL>print N

9. IN out parameters:

A.These parameters are used to accept the value as well as to return the value to user.
Ex: create a procedure which accepts a number and return its square?
    Create of replace procedure ret_square1(A IN out number)
    IS
    Begin
    A := A * A;
    END;
      /
10.What is mean by Functions? and Syntax of functions?

A. A function is a named PL/SQL block which must and should return a value.
Syntax: create or replace function<FUNCTION_NAME>(VAR1 datatype, var2 datatype,…….,varn datatype);
Return datatype
IS
Begin
-------
-------
-------
END;
  /

11. Differences between procedure and functions?
Procedures:                                                                             
 

1.Procedures need not return any           
value can return one or more than              .
one value.
 

2.DML commands are allowed.                  
 

3.Can not be invoked from select               
statement.                                                        .

Functions :

 
1. Must and should return only one value.
 

2. DML commands are not allowed.
 

3. Can be invoked from select statement and expressions.

12. Which Query used to see list of all the functions?

A.Select object_Name from user_objects where object_type = ‘function’;
Procedures and functions are called sub programs

13. Give the structure of the procedure ?

A.PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

14. Explain how procedures and functions are called in a PL/SQL block?

Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

15. What are advantages of Stored Procedures?

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

0 comments:

Post a Comment