What is Pl/SQL?

Pl/SQL is stands for Procedural language, which is an extension of SQL.

Pl/SQL is combination of SQL statements with procedural programming features.

Pl/SQL is developed by Oracle.

What is Pl/SQL Block?

Pl/SQL programs are divided and written in logical block of code.

Pl/SQL block is consist of three parts.

1. Declaration: This section starts with the DECLARE keyword. Its an optional part.

2. Executable Commands: This block starts with BEGIN and ends with END keyword. Executable commands and business logic is written between these keywords. This is a mandatory part. This section should contain at least one executable command to successfully execute the block.

3. Exception Handling: This section starts with EXCEPTION keyword. This section is used to handle the exception occurred in the Executable block

What is the basic structure Pl/SQL Block?

DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions raised in executable part]
END;

Let's write our first Pl/SQL program.

#1  SET SERVEROUTPUT ON;
#2
#3  DECLARE
#4  VAR VARCHAR2(50) := 'Welcome To Pl/SQL Programming.';
#5
#6  BEGIN
#7  DBMS_OUTPUT.PUT_LINE(var);
#8  END;
#9  /
#10 SET SERVEROUTPUT OFF;

Executing the above code on SQL prompt will produce the output:

anonymous block completed
Welcome TO Pl/SQL Programming.

Code Explained

Line #1: SET SERVEROUTPUT ON tells the PL/SQL engine to print the arguments/value passed in the DBMS_OUTPUT.PUT_LINE(arguments/value) on the main console.

Line #3: DECALRE keyword indicate the start of the declaration part of the block.

Line #4: variable with name var is defined with data type VARCHAR2(size), := is an assignment operator for assigning values to the variable within single quotes. ; is used to indicate the termination of a statement.

Line #6,7,8: BEGIN and END keyword includes the executable part. DBMS_OUTPUT is a package and PUT_LINE is a procedure in that package which takes an argument/value which is stored internally inside a buffer in SGA(Shared Global Area) memory area up to 2000 bytes. However one should note that this buffer is only created when we use DBMS_OUTPUT package.

Line #9: "/" indicates the end of line.

Line #10: By setting the SET SERVEROUTPUT OFF tells the PL/SQL engine to not to print the output on console.

Explain Pl/SQL Architecture?

Pl/SQL Architecture consist of two parts PL/SQL Engine and PL/SQL Unit.

PL/SQL Engine: The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL units.

PL/SQL engine accepts as input any valid PL/SQL unit. The engine executes procedural statements, but sends SQL statements to the SQL engine in the database.

PL/SQL Unit: PL/SQL Unit can be any of the below.

PL/SQL block

FUNCTION

PACKAGE

PACKAGE BODY

PROCEDURE

TRIGGER

TYPE

TYPE BODY

PL/SQL Introduction