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:
Start with the DECLARE keyword and ends when the executable section starts.Here is used deffirent kinds of variables, cursors, and user defined exceptions.
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.
Start with the EXCEPTION keyword. This section mainly used to handel for errors and abnormal conditions when is arise in the executable section.
All PL/SQL blocks must finish with an END statement and END is terminated with a semicolon.
PL/SQL Block Types:
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.