EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Block Structure

PL/SQL Block Structure

Updated April 5, 2023

PL_SQL Block Structure

Introduction to PL/SQL Block Structure

The following article provides an outline for PL/SQL Block Structure. PL/ SQL block structure 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 the repetitive code execution in it. PL/SQL block is in oracle databases as the schema object and the block where it is is he named block. The main usage of the PL/SQL blocks is done for reusing a particular business logic again and again as it encapsulates these logical statements in it.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax of PL/SQL Block Structure

The general syntax of creating a block in PL/SQL is as shown below:

Declare
[ statements to be declared]
BEGIN
[ statements to be executed]
EXCEPTION
[ exception handler used for handling the exceptions]
END [name of the block]

The terminologies used above are explained below:

  • PL/SQL header for block: There are three different blocks involved while writing a block structure in Pl/ SQL. These blocks involve declaration block, execution block and exception handling block. Out of the three blocks specified here, it is compulsory to specify the execution block while the remaining two are optional in nature. A block which is not provided any name is called as Anonymous block in PL/SQL.
  • PL/SQL body of the block: The body of the block is divided into three different parts which include the declaration, execution and finally handling of exceptions. The second part of that block which are 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 see each of the part in detail:

  • Declarative part: In this part of the body we can declare all the constants, cursors and the variables. This part of the body does not begins with the keyword DECLARE as in case of other anonymous blocks.
  • Executable part: The main business logic and the operations for processing are carried our and written in this part of the body of block. There might be a possibility that this part can contain only a single NULL statement in it.
  • Exception handling: In this part of the body, we try to handle those exceptions that might arise while executing the code of executable part.

Types of Blocks in PL/SQL

The PL/SQL block structures are broadly classified into two type which are namely:

  • Anonymous Blocks: This are the blocks which do not have any name associated with the block. The most disadvantage of the PL/ SQL anonymous blocks is that this block can be used only once as they are not stored anywhere in the oracle database.
  • Named Blocks: This are generally the procedures and functions which can be executed and used again. This are reusable block structures in PL/SQL. Along with the name of the procedure, we can also optionally specify the list of parameters used for input. Each of the individual parameter declared inside the parenthesis can be either OUT/ IN or INOUT parameter. This are called as the modes of parameters. This mode helps us to specify whether the parameter that we have created is used for writing to or reading from the procedure.

Example of PL/SQL Block Structure

Given below is the example mentioned:

Let us have a look at the example which demonstrates us the usage of 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.

Code:

SELECT * FROM [customers_details];

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

Output:

PLSQL Block Structure 1

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

Code:

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 procedure that is the named block in PL/SQL, you need to click on the run button the SQL editor you are using after which you can see the created block with the name displayContact present inside the node of the database.

In order to execute the above block for a particular value of customer id, you can make the use of following syntax of execution.

Code:

EXECUTE name of procedure/named block (arguments that are to be passed)

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

Code:

EXECUTE displayContact(101);

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

Output:

displaying the mobile number

Conclusion

The block structure is divided into three different blocks which are declaration block, execution block and exception handling block. It is compulsory to specify the execution block. The PL/SQL executable block 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. These blocks can be given call multiple times if they are named blocks. The anonymous blocks can be executed only once as they are not stored anywhere in the oracle database.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Block Structure” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Triggers in PL/SQL
  2. PL/SQL Collections
  3. PL/SQL Data Types
  4. Loops in PL/SQL
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW