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.
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 ONcommand to instruct SQL*Plus to echo database’s output after executing the PL/SQL block. TheSET SERVEROUTPUT ONis SQL*Plus command, which is not related to PL/SQL. - Second, use the
DBMS_OUTPUT.PUT_LINEprocedure 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.
Figure 1. SQL*Plus Login
Second, type the following code into the SQL*Plus and execute it as the following picture:
Congratulation, you’ve finished the first PL/SQL program!
0 comments:
Post a Comment