PL/SQL Procedure

PL/SQL Create Procedure

  • A stored procedure is nothing but a named PL/SQL code block that is compiled and stored in one of the Oracle engine's system tables.
  • A procedure is a logically grouped set of SQL and PL/SQL statements, which performs a specific task.
Syntax for creating store procedure:

CREATE OR REPLACE PROCEDURE procedure_name
[<Parameter_name> [IN, OUT, IN OUT] datatype ]
IS
<variable> declarations;
<constant> declarations;
BEGIN
<Procedure_body>
END [procedure_name]


Example: Creating a stored procedure.

1. Create table of person

create table Person(id number(10) primary key, name varchar2(100));

2. Create Procedure

Create or replace procedure "INSERTPERSON"    
(id IN NUMBER,    
name IN VARCHAR2)    
is    
begin    
insert into Person values(id,name);    
end;    
/


User will get the following output message:
Procedure created.

The three Important parts of procedure are:

1. Declarative part
  • The declarative part may contain the declarations of constants, variables, exceptions, subprograms and cursors.
  • These objects are local to the procedure and become invalid once the procedure or function exists.
2. Executable part
  • The executable part is a PL/SQL block consisting of SQL and PL/SQL statements that assigns the values, control execution and manipulate data.
  • In this part the code is written to perform an expected action to execute the procedure.
3.  Exception Handling Part
  • In this part the code deals with exceptions that may be raised during the execution of code in the executable part.
  • Oracle exception handler can be redirected to the exception handling section of the procedure, where the procedure or function that determines the actual action should be carried out by Oracle exception handler.
Following are the three ways used to pass parameters to procedure.

1. IN parameter
It indicates that the parameter will accept a value from the user.

2. Out parameter
It indicates that the parameter will return a value to the user.   

3. IN OUT
It indicates that the parameter will either accept a value from the user or return a value to the user.

Example: Stored procedure using In and Out mode.
This procedure computes the square of a passed value.
This example shows how same parameter is used to accept a value and then return another result.

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END;
BEGIN
   a:= 2;
   squareNum(a);
   dbms_output.put_line(' Square of (2): ' | |  a);
END;
/


Output:
Square of (2): 4

PL/SQL Drop Procedure

Syntax:
DROP PROCEDURE procedure_name

For example:
DROP  PROCEDURE Employee_salary
▲