How to use procedure in Oracle PL/SQL?

-





What is Procedure in Oracle/PL/SQL?

Procedure: Procedure is named PL/SQL block. Which is used to solve particular task. It is stored into database that's why it is also called as stored procedure.


Stored procedure: When we are using create or replace in front of procedure then it is saved into database.So it is called
stored procedure. Procedure is used to improve performance of our application because it has one time compilation process.
Procedure has two parts.
a)Procedure Specification: In this section we create procedure name.
b)Procedure Body: In this section we specify actual task.

      Create or replace Procedure Procedure_Name(Parameter_Name  [Mode] datatype)    // Procedure specification
       is/as   
       variable,cursor,exception declaration .                         // Procedure Body started to end.
      Begin
      ---------
     Exception
     -------------
     End Procedure_Name;

Example: Entering dept_no from table and getting employee name and his dept_no.

create or replace procedure P1(V_dept_no number)
is
cursor c1 is select * from Emp where dept_no=v_dept_no;
i emp%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_Line(i.ename||' '||i.deptno);
end loop;
close c1;
end;
/
How to Execute a Procedure--> 

 Method 1:>
                      exec Procedure_name(actual_parameters);
Method 2:>
                  By anonymous block;
     begin
      procedure_name(actual_parameters);
    end;

Note:->  After creating procedure if you get any error so to check error. You have to use following command.
              show errors;
Example: Use dept_no and access employees names and their salaries.

 create or replace procedure p1(V_dept_no number)
is
cursor c1 is select * from emp where dept_no=v_dept_no;
i emp%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_Line(i.ename||' '||i.sal);
end loop;
close c1;

 Note:   IF you want to view code of procedure then we will use user_procedures,user_source datadictionary;

select text from user_source where name='PROCEDURE_NAME'; // Always we put capital letters in where class condition in data dictionary.

Mode: Oracle provided three modes. They are as follows.
a) In
b)Out
c)In out

in: In is a mode which is used with procedure formal parameters. By default in is used with parameters. It is used to pass values into formal parameter it behaves like a constant.
Syntax : Parameter_name in datatype

program: Passing dept_no in formal parameter with in mode and getting employees name and their salary and dept_no;

create or replace procedure p1(v_dept_no in number)
is
cursor c1 is select * from emp where dept_no=v_dept_no;
i emp%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||' '||i.sal||' '||i.dept_no);
end loop;
close c1;
end;
/
b) OUT: OUT is used to return value from procedure, here you have to specify OUT keyword explicitly.
    Syntax:  Parameter_name out datatype

create or replace procedure p2(a in number,b out number)
is 
begin
b:=a*a;
dbms_output.put_line(b);
end;
Note: Whenever we use out mode then procedure will only execute with two methods.
     1)Bind variable
                    variable z number;
                      exec p2(3,:z);
      2)Anonymous block
                 declare
                 c number(10);
                 begin
                 p2(3,c);
                 end;
   Program 3=> Write a program pass employee dept_no through in parameter and return employee salary and employee name through out parameter.     

 Nocopy (or) pass by value, pass by reference: Nocopy keyword along with out mode to increase performance of out parameter.we are using function based programming  Then all programming language supports two types of passing parameter mechanism, these are..

a) pass by value: In this method copy of data sent into calling program.Out parameter uses pass by values method , for this reason there is D-Grade performance . To overcome this problem we will use pass by reference method. By default in mode use pass by  reference.

b) pass by reference: In this method actual data sent into calling program. We use nocopy keyword along with out mode , From this out parameter gives fast performance.

Syntax: Column_name out nocopy varchar2

create or replace procedure p2(a in number,b out nocopy number)
is 
begin
b:=a*a;
dbms_output.put_line(b);
end;
/
Note: Whenever we use out mode then procedure will only execute with two methods.
     1)Bind variable
                    variable z number;
                      exec p2(3,:z);
      2)Anonymous block
                 declare
                 c number(10);
                 begin
                 p2(3,c);
                 end;


in out : Whenever you want to pass value into procedure and also return value from procedure by a single parameter . Then we use in out mode. From this mode you pass value and return value from only one parameter.

Syntax:  a in out number

Note: Here data type of passing value and returning value both belong to same data type otherwise oracle returns an error.
Sol: Create or replace procedure p1(a in out number)
          is
          begin
          a:=a*a;
          end;
     
                           Execution:1>  By bind variable: variable z number;
                                                         exec :z:=3;
                                                         exec p1(:z);
                                                         print z;
                                                   2> by anonymous block
                                                        declare
                                                        z number(10)=&no; //  (or) z number(10):=5;
                                                        begin
                                                        z:=z*z;
                                                        dbms_Output.put_line(z);
                                                        end;

Execution of in parameter:>There are three  execution methods of 'in parameter' in Oracle.

1) Positional Notation: Here you assign values into actual parameters in sequence order of corresponding formal parameter.
 example:
          Create procedure p1(a in number, b in varchar2, c in number);
          exec p1(1,'gg',3)     // valid
          exec p1('gg',1,3)    // invalid
                                 
2)Named notation: In named notation you can assign the values in actual paramters without any order .
        example:
           Create procedure p1(a in number, b in varchar2, c in number);
                 exec p1(b=>'gg', a=> 3, c=>1)     // valid
                                         
3)Mixed notation:  It is combination of positional notation and mixed notation.
 
       Note: First you use positional notation then use named notation.
                       example: Create procedure p1(a in number, b in varchar2, c in number);
                                exec p1(2,c=>4, b=> 'gg')     // valid
                                exec p1(c=>4,b=>'gg',2)      // invalid  // Read note carefully
       

Autonomous transaction:This transaction is independent transaction. This is not affected while main program( when autonomous transaction program is being executed in another procedure) is using rollback or commit.We declare autonomous transaction in declare section of the pl/sql block.

SYNTAX:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
......
COMMIT; // MANDATORY
EXCEPTION
......
END;
/

Exception handling in procedure:
Case 1:  We handling inner procedure exception into outer block.

Create or replace procedure p1
is
v_ename varchar2(20);
v_sal number(20);
begin
select ename,sal into v_ename,v_sal from emp where dept_no=20;
dbms_output.put_Line(v_ename||' '||v_sal);
end;
/

Create or replace procedure p2
is
begin
p1;
Exception
when others then
dbms_output.put_line('inner procedure error');
end;
/

Execution: exec p2;
           Inner procedure error
Case 2: We handling inner procedure error in inner procedure exception block.

Inner procedure:

Create or replace procedure p1
is
v_ename varchar2(20);
v_sal number(20);
begin
select ename,sal into v_ename,v_sal from emp where dept_no=20;
dbms_output.put_Line(i.ename||' '||i.sal);
exception
when too_many_rows then
dbms_output.put_Line('Fetching too many rows from select into clause');
end;
/


Outer procedure:

Create or replace procedure p2
is
begin
p1;
Exception
when others then
dbms_output.put_line('inner procedure error');
end;
/
Execution: exec p2;
           fetching too many rows from select into clause.



ACCESSIBLE BY CLAUSE: Accessible by clause was introduced by oracle 12c. You want to restrict your procedure in order to access for some procedure then
we use accessible by clause. This clause uses procedures and only those procedures are allowed to access your procedure.
This clause is used only in body specification.

Syntax: 
Create or replace procedure procedure_name(formal paramters)
accessible by (procedure1,procedrure2....)
is/as
......
begin
...
exception
....
end;
/   













Comments

Post a Comment

Popular posts from this blog

How to use a function in Oracle PL/SQL?

-
Image
What is a Function in Oracle PL/SQL Function : The PL/SQL Function is very identical to PL/SQL Procedure.The big dissension between procedure and a function, a function essential always return a value, but procedure may or may not return a value.     Functions is also used to solve a particular task like procedure. Function always returns a value.Oracle provided us a facility to create our own functions to solve any task.   How To Create a function    Syntax: Create or replace function p1 (formal parameters) return datatype is variable,cursor, user_defined exception  begin ...... return expression; // Do not use dbms_output.put_line() exception ....... end; _________________________________________________________________________________ Execution: Method 1:   Select function_name from dual; Method 2: By anonymous block           Syntex:           declare ...
Read more
-
What is trigger? TRIGGER : -->  SQL Trigger is an event-driven action or database callback function which is invoked automatically when an INSERT, UPDATE, and DELETE statement is performed on a specified table. The main tasks of triggers are like enforcing business rules, validating input data, and keeping an audit trail. Usage of Triggers: 1. Triggers are used for enforcing business rules. 2. Validating input data. 3.Generating a unique value for a newly-inserted row in a different file. 4.Write to other files for audit trail purposes. 5. Query from other files for cross-referencing purposes. 6. Used to access system functions. 7. Replicate data to different files to achieve data consistency. Advantages of using triggers Triggers make the application development faster. Because the database stores triggers, you do not have to code the trigger actions into each database application. Define a trigger once and you can reuse it for many applications that use...
Read more