PL/SQL Procedure

Sharing is Caring
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] typeparameter2 [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

Related Posts

I’m an Oracle ACE, Blogger, Reviewer,
Technical Lead working on Oracle ADF

Leave a Reply

Your email address will not be published. Required fields are marked *