PL/SQL Create Procedure
Syntax for creating store 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.
CREATE OR REPLACE PROCEDURE procedure_name
[<Parameter_name> [IN, OUT, IN OUT] datatype ]
Creating a stored procedure.
1. Create table of person
create table Person(id number(10) primary key, name varchar2(100));
2. Create Procedure
User will get the following output message:
Create or replace procedure "INSERTPERSON"
(id IN NUMBER,
name IN VARCHAR2)
insert into Person values(id,name);
The three Important parts of procedure are:
1. Declarative part
2. Executable 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.
3. Exception Handling 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.
Following are the three ways used to pass parameters to procedure.
1. IN parameter
- 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.
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.
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.
PROCEDURE squareNum(x IN OUT number) IS
x := x * x;
dbms_output.put_line(' Square of (2): ' | | a);
Square of (2): 4
PL/SQL Drop ProcedureSyntax:
DROP PROCEDURE procedure_name
DROP PROCEDURE Employee_salary