PL/SQL Procedure

Previously I have posted about PL/SQL Block structure , A piece of code that is organized in a properly defined sequence is called a block

PL/SQL provides two types of blocks

Function- A PL/SQL block that performs a task or set of tasks and returns a value

Procedure- A PL/SQL block that performs a task or set of tasks and may or may not return a value




The syntax for creating PL/SQL procedure is like this

CREATE [OR REPLACE] PROCEDURE procedure_name 
(parameter1 [IN | OUT | IN OUT] type, parameter2 [IN | OUT | IN OUT] type)

IS
 <<Declaration Section>>

BEGIN 

  <<Execution Section>> 

EXCEPTION

<<Exception Handling>>

END;

See this example of creating a simple procedure that prints the sum of two variables

--Header Section of Procedure
CREATE OR REPLACE PROCEDURE PROC_DEMO 
IS 
-- Declare variables 
a number := 10;
b number :=20;
c number;
BEGIN 
--Perform Calculation
c:=a+b;
   dbms_output.put_line('Sum of a and b is- '||c); 
END; 

And to run this procedure use following command

BEGIN 
PROC_DEMO;
END;

OR

EXECUTE PROC_DEMO;

See another example of a procedure that inserts data in Departments table of HR Schema

--Header Section with Input parameters
create or replace PROCEDURE PROC_INSERDEPT
 (p_deptid IN number,p_deptnm IN varchar2,p_mgrid IN number,
  p_locid IN number,p_sno IN number) IS
BEGIN 
-- Insert row in Departments table
insert into Departments values(p_deptid,p_deptnm,p_mgrid,p_locid,p_sno);
dbms_output.put_line('Department created successfully');
--If there is any error/exception
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('There is some problem in Department creation');
END; 

Execute this procedure with parameters

EXECUTE PROC_INSERDEPT(9999,'TEST DEPARTMENT',100,102,99);

And output is

Cheers :) Happy Learning