PL/SQL is a block structured language. Everything written in PL/SQL are divided in logical blocks. Each blocks has three parts declaration sections, executable sections and exception handling sections.
Declaration Section: This is an optional section. This section contains declaration of variables, constants, cursors, subprograms and other elements necessary in program. DECLARE is the keyword that indicates the starts of declaration section.
Executable Section: Executable section is the main part of the program. This section start with the keyword BEGIN and end with keyword END. All the executable statements are placed inside this section. This section should not be empty, it should contains at least null command to indicate nothing should be execute.
Exception Handling Section: Exception handling section starts with the keyword EXCEPTION. It handles all the exception raises while executing the statements in executable section. It is also an optional section.
A typical PL/SQL block looks like following.
DECLARE <declaration section> BEGIN <executable section> EXCEPTION <exception handling section> END;
Here is the hello world program written in PL/SQL block.
BEGIN DBMS_OUTPUT.PUT_LINE( 'Hello World!' ); EXCEPTION
Following is the same hello world program. This time a variable is declared which size is 100 bytes. This variable contains string “Hello World!” and is being used to show output. It also handles exception.
DECLARE message VARCHAR2 (100) := 'Hello World!'; BEGIN DBMS_OUTPUT.PUT_LINE(message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Exception Occurred!' ); END;
Blocks can be nested. Following is the same program but this time we are using nested blocks.
DECLARE message_one VARCHAR2 (100) := 'Hello'; BEGIN DECLARE message_two VARCHAR2 (100) := message_one || ' World!'; BEGIN DBMS_OUTPUT.PUT_LINE(message_two); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Exception Occurred!' ); END;
There are many tools available to run PL/SQL programs. SQLPLUS is one of the tool to run PL/SQL program provided by Oracle. To execute the above programs in SQLPLUS first connect with database using the command CONNECT database_name. After providing password you will be connected with database_name. You should run SET SERVEROUTPUT ON command to see output on screen. Then type the program in SQLPLUS command prompt at the end type slash(/) to tell SQLPLUS to execute. When executed you will see Hello World! written on the screen.
Following figure shows executing the hello world example in SQL*PLUS.