PL SQL Procedure

PL/SQL Procedure

The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.
The procedure contains a header and a body.
◦Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
◦Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.
How to pass parameters in procedure:
When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:
1.IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
2.OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3.INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
A procedure may or may not return any value.
PL/SQL Create Procedure
Syntax for creating procedure:


1.CREATE [OR REPLACE] PROCEDURE procedure_name 
2.    [ (parameter [,parameter]) ] 
3.IS 
4.    [declaration_section] 
5.BEGIN 
6.    executable_section 
7.[EXCEPTION 
8.    exception_section] 
9.END [procedure_name]; 
Create procedure example
In this example, we are going to insert record in user table. So you need to create user table first.
Table creation:


1.create table user(id number(10) primary key,name varchar2(100)); 
Now write the procedure code to insert record in user table.
Procedure Code:


1.create or replace procedure "INSERTUSER"   
2.(id IN NUMBER,   
3.name IN VARCHAR2)   
4.is   
5.begin   
6.insert into user values(id,name);   
7.end;   
8./      
Output:
Procedure created.

PL/SQL program to call procedure
Let's see the code to call above created procedure.


1.BEGIN   
2.   insertuser(101,'Rahul'); 
3.   dbms_output.put_line('record inserted successfully');   
4.END;   
5./   
Now, see the "USER" table, you will see one record is inserted.

ID
Name
101 Rahul
PL/SQL Drop Procedure
Syntax for drop procedure


1.DROP PROCEDURE procedure_name;  
Example of drop procedure


1.DROP PROCEDURE pro1; 
Next TopicPL/SQL Function