Description of Oracle PL/SQL Language

PL/SQL stands for “Procedural Language extension to SQL”. PL/SQL language are used for relational databases. It’s Oracle Corporation’s standard data access language. PL/SQL works as a block structure for executable units of code that a result maintaining and debugging code is made easier. PL/SQL language provides all the procedural Constructs that are available in any 3GL (Third-Generation Language).

 

PL/SQL Block Structure:
==================

**DECLARE (optional)
Start with the DECLARE keyword and ends when the executable section starts.Here is used deffirent kinds of variables, cursors, and user defined exceptions.

**BEGIN (mandatory)
This section start with the BEGIN keyword. At least one statement is required for this section. SQL and PL/SQL startments are used in this part.

**EXCEPTION(optional)
Start with the EXCEPTION keyword. This section mainly used to handel for errors and abnormal conditions when is arise in the executable section.

**END;(mandatory)
All PL/SQL blocks must finish with an END statement and END is terminated with a semicolon.

 

PL/SQL Block Types:

–>Procedures
–>Functions
–>Anonymous blocks

 

Example of PL/SQL block:

SET SERVEROUTPUT ON
DECLARE
   v_lname         VARCHAR(20);
BEGIN
   SELECT last_name
   INTO v_lname
   FROM employees
   WHERE employee_id = 120;
   DBMS_OUTPUT.PUT_LINE('Last name of the employee is' || v_lname);
END;
/

 

NOTE: Use SET SERVEROUTPUT ON before running the PL/SQL block to enable output in SQL Developer And use DBMS_OUTPUT.PUT_LINE to display the output of block or statement. DBMS_OUTPUT.PUT_LINE is a predefined Oracle package.