PL/SQL (Procedural Language/Structured Query Language): PL/SQL is Oracle Corporation’s procedural language extension for SQL and Oracle corporation’s standard data access language for relational database. PL/SQL is available in Oracle Database since version 7. PL/SQL includes procedural language elements such as conditions and loops. You can declare constants and variables, procedures and functions, types and variables of those types, and triggers. You can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections.
PL/SQL Block Structure: A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. PP/SQL block structure like this…
DECLARE (optional) -- Variable, cursors, user defined exceptions. BEGIN (mandatory) -- SQL Statements -- PL/SQL Statements Exception (optional) -- Actions to perform when error occur END (mandatory)
Block type: There are three type of block make up a PL/SQL progarm. they are…
- Anonymous block: Anonymous block are unnamed blocks. These block are not stored in the database.
- Procedures: Procedure are a named PL/SQL block that are stored in the database and perform an action.
- Functions: Function similar to procedure, except that a function must return a value.
Test your output: Enable output in SQL*Plus with the this command – SET SERVEROUTPUT ON and use a predefined Oracle package and its procedure – DBMS_OUTPUT.PUT_LINE
Example:
SET SERVEROUTPUT ON
...
BEGIN
DBMS_OUTPUT.PUT_LINE ('Hello World!');
END;
/
Continue….