PL/SQL Block Structure - LearnHowToCode SarkariResult.com Interview Questions and Answers LearnHowToCodeOnline
PL/SQL block structure

PL/SQL Block Structure

In this tutorial, you will learn about the PL/SQL block structure and how to write and execute the first PL/SQL block in SQL*PLUS.

Introducing PL/SQL block structure and anonymous block

PL/SQL program units organize the code into blocks. A block without a name is known as an anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the Oracle database.
An anonymous block is an only one-time use and useful in certain situations such as creating test units. The following illustrates anonymous block syntax:
[DECLARE]
   Declaration statements;
BEGIN
   Execution statements;
  [EXCEPTION]
      Exception handling statements;
END;
/

Let’s examine the PL/SQL block structure in greater detail.


PL/SQL Block Structure
PL/SQL Block Structure

The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is mandatory and the others are optional.
  • The declaration section allows you to define data types, structures, and variables. You often declare variables in the declaration section by giving them names, data types, and initial values.
  • The execution section is required in a block structure and it must have at least one statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements inside the execution section.
  • The exception handling section is starting with the EXCEPTIONkeyword. The exception section is the place that you put the code to handle exceptions. You can either catch or handle exceptions in the exception section.
Notice that the single forward slash (/) is a signal to instruct SQL*Plus to execute the PL/SQL block.
  • SQL*Plus is an Oracle database client tool that executes PL/SQL statements and outputs the query’s results. SQL*Plus provides administrators and programmers with command-line interface to work with Oracle database. SQL*Plus is commonly referred as SQLPLUS.

PL/SQL block structure example

Let’s take a look at the simplest PL/SQL block that does nothing.
BEGIN
   NULL;
END;
If you execute the above anonymous block in SQL*Plus you will see that it issues a message saying:
PL/SQL procedure successfully completed.
Because the NULL statement does nothing.
To display database’s output on the screen, you need to:
  • First, use the SET SERVEROUTPUT ON command to instruct SQL*Plus to echo database’s output after executing the PL/SQL block. The SET SERVEROUTPUT ONis SQL*Plus command, which is not related to PL/SQL.
  • Second, use the DBMS_OUTPUT.PUT_LINE procedure to output a string on the screen.
The following example displays a message Hello PL/SQL on a screen using SQL*Plus:
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;
/
In this example, you just use the execution part to execute code. You will learn how to declare variablesand handle exceptions in the next tutorials.

Exercise on anonymous block structure

Now, it is your turn to create a PL/SQL block and execute it in SQL*Plus that display a greeting message “Hello World” on the screen.
First, you need to login to the Oracle database via SQL*Plus by providing a username and password as shown the following picture.
SQL*Plus Login
Figure 1. SQL*Plus Login

Second, type the following code into the SQL*Plus and execute it as the following picture:

PL/SQL Hello World


Figure 2.PL/SQL Hello World

Congratulation, you’ve finished the first PL/SQL program!

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.

0 comments:

Featured post

Political Full Forms List

Acronym Full Form MLA Member of Legislative Assembly RSS Really Simple Syndication, Rashtriya Swayamsevak Sangh UNESCO United Nations E...

Powered by Blogger.