EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL procedure
 

PLSQL procedure

Updated March 31, 2023

PLSQL procedure

 

 

Introduction to PLSQL procedure

PL/ SQL procedure is the block of code written for the purpose of reuse when required and has certain business logic written inside it, which may involve conditional execution or repetitive code execution in it. PLSQL procedure is stored in Oracle databases as the schema object and the block where it is stored in the named block. The main usage of the PLSQL procedures is done for reusing a particular business logic again and again as it encapsulates these logical statements in it. In this article, we will learn about the syntax, usage, and implementation of PLSQL procedures, along with the help of certain examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax 

The general syntax of creating a procedure in PLSQL is as shown below –

CREATE [ OR REPLACE] PROCEDURE name of procedure (list of parameters)
IS
[ statements to be declared]
BEGIN
[ statements to be executed]
EXCEPTION
[ exception handler used for handling the exceptions]
END [name of the procedure]

The terminologies used above are explained one by one in the below section –

PLSQL header for procedure

Along with the name of the procedure, we can also optionally specify the list of parameters used for input. Each of the individual parameters declared inside the parenthesis can be either OUT/ IN or INOUT parameter. These are called the modes of parameters. This mode helps us specify whether the parameter we have created is used for writing to or reading from the procedure. Let us see one by one the usage of each of the modes –

OUT – The parameter having the OUT mode can only be used for writing the values to it, and the value is used further for returning. So even though while calling the procedure, they will be completely ignored as they don’t take the values outside the procedure.

IN – This type of mode is used for specifying that the parameter value is only in the read mode and cannot be modified further inside the PL/ SQL procedure. However, even though you cannot modify it, we can still access and use the value of this parameter inside the procedure in PL/ SQL.

IN/ OUT – When a parameter is given the mode of INOUT, it will be allowed for reading as well as modification and writing by the PL/ SQL procedure.

The important point to be considered here is that when we make the use of the OR REPLACE optional clause, it will lead to the replacement of the current existing procedure in the PL/ SQL database, which will overwrite it and there is a possibility of losing the old procedure.

PL/ SQL body of the procedure

The body of the procedure is divided into three different parts, which include the declaration, execution, and, finally, handling of exceptions. The second part of that procedure which is executable parts is compulsory while the other two are optional. Also, it is important to specify at least one statement in the executable part. Let us understand each of the parts in detail –

Declarative part – In this part of the body, we can declare all the constants, cursors, and variables. This part of the body does not begin with the keyword DECLARE as in the case of other anonymous blocks.

Executable part – The main business logic and the operations for processing are carried out and written in this part of the body of the procedure. There might be a possibility that this part can contain only a single NULL statement it.

Exception Handling – In this part of the body, we try to handle those exceptions that might arise while executing the code of the executable part.

Example of PLSQL procedure

Let us learn the implementation of the PL/ SQL stored procedures with the help of some examples –

Let us look at the example that demonstrates how the cursor

can be used in PL/ SQL to retrieve the information about a particular entry in the table. Consider that we have a table called customer details which stores the details of the customers. To check the contents of the table, we can fire the following query in SQL –

SELECT * FROM [customers_details];

The output of the execution of the above query statement is as shown below, showing the contents of the table customer details –

PLSQL procedure output 1

To retrieve the details of the table in such a way that a particular customer’s first name is retrieved and the contact details showing its mobile number is retrieved, we can create a procedure in PL/ SQL. In this procedure, we will retrieve the customer details table’s results with its f_name and mobile number fields.

CREATE OR REPLACE PROCEDURE displayContact(
in_customer_id NUMBER
)
IS
customerInfo customers_details%ROWTYPE;
BEGIN
-- Get the mobile numbers of the customers
SELECT *
INTO customerInfo
FROM customers_details
WHERE customer_id = in_customer_id ;
-- display the infomation of the customer name and its mobile number
dbms_output.put_line( '<' || customerInfo.f_name || ' ' ||
customerInfo.mobile_number ||'>' );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;

To run the above process, you need to click on the run button of the SQL editor you are using, after which you can see the created procedure with the name displayContact present inside the node of the database.

In order to execute the above-stored procedure for a particular value of customer id, you can make the use of the following syntax of execution –

EXECUTE name of the procedure (arguments that are to be passed)

In order to call the above procedure, we can make the use of the following query statement any number of times for any customer id as the argument –

EXECUTE displayContact(101);

The execution of the above query statement gives out the following output displaying the mobile number and first name of the customer having customer id as 101.

PLSQL procedure output 2

Conclusion

The PL/ SQL stored procedures are a single unit that contains the business logic written inside it and which can also involve multiple data manipulation and retrieval of database values in its statements. This procedure can be given call multiple times.

Recommended Articles

We hope that this EDUCBA information on “PLSQL procedure” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PLSQL Interview Questions
  2. Triggers in PL/SQL
  3. Loops in PL/SQL
  4. PL/SQL Data Types

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW