Block of PL/SQL in Oracle DBMS

SQLSQLiteDatabaseDBMS

PL/SQL is a block structured language i.e the code of PL./SQL is written in the form of blocks. PL/SQL also contains the robustness, security and portability of the Oracle database.

Each block of PL/SQL contains the following subparts −

  • Declarations -  This section contains all the items that needs to be declared before the program such as variables, subprograms etc. This section contains the keyword DECLARE at its start. In general, Declarations is an optional subpart of the PL/SQL program.
  • Executable Commands -  This section of the PL/SQL code contains the executable statements. It contains BEGIN and END at its starting and ending. Executable commands is a compulsory section and it cannot be left blank. In case there are no executable statements, NULL is written to signify this.
  • Exception Handling -  This section contains the code to handle all the errors or exceptions that may arise during the course of the program.Exception handling section starts with the keyword EXCEPTION.

Structure of a PL/SQL Block

The basic structure of a PL/SQL block is

DECLARE
< Declarations section >
BEGIN
< Executable commands section >
EXCEPTION
< Exception handling section >
END;

Example

An example of a PL/SQL code is −

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT. PUT_LINE
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(‘Number”|| j );
END LOOP;
END;

This program displays the first ten natural numbers in order.

raja
Published on 03-Aug-2018 11:51:10
Advertisements