PL/SQL - General Information
PL/SQL (Procedural Language/Structured Query Language, also known as Pretty Lazy/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. PL/SQL's general syntax resembles that of Ada. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. PL/SQL is available in Oracle Database (since version 7).
PL/SQL supports variables, conditions, loops and exceptions. Arrays are also supported, though in a somewhat unusual way, involving the use of PL/SQL collections. PL/SQL collections are a slightly advanced topic. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. PL/SQL program units (essentially code containers) can be compiled into the Oracle database. Programmers can thus embed PL/SQL units of functionality into the database directly. They also can write scripts containing PL/SQL program units that can be read into the database using the Oracle SQL*Plus tool. Once the program units have been stored into the database, they become available for execution at a later time. While programmers can readily embed Data Manipulation Language (DML) statements directly into their PL/SQL code using straight forward SQL statements, Data Definition Language (DDL) requires more complex "Dynamic SQL" statements to be written in the PL/SQL code. However, DML statements underpin the majority of PL/SQL code in typical software applications. In the case of PL/SQL dynamic SQL, early versions of the Oracle Database required the use of a complicated Oracle DBMS_SQL package library. More recent versions have however introduced a simpler "Native Dynamic SQL", along with an associated EXECUTE IMMEDIATE syntax. Oracle Corporation customarily extends package functionality with each successive release of the Oracle Database.
An application on PL/SQL consists of blocks (anonymous and named). A block can include nested blocks, aka subblocks. The general shape of a PL/SQL-block:
<<label>> DECLARE TYPE / item / FUNCTION / PROCEDURE declarations BEGIN Statements EXCEPTION EXCEPTION handlers END label;
The PL/SQL language supports the following type categories:
IF - THEN - END IF; IF - THEN - ELSE - END IF; IF - THEN - ELSIF - END IF; CASE - WHEN - THEN - END CASE;
LOOP - END LOOP; WHILE - LOOP - END LOOP; FOR - LOOP - END LOOP; EXIT; EXIT WHEN;
Example of a program that updates data in the table and displays the number of changed records in the console
DECLARE cnt NUMBER; BEGIN SELECT DEPTNO INTO cnt FROM DEPT WHERE DEPTNO = 10; UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO = 10; DBMS_OUTPUT.PUT_LINE('Rows in DEPT updated '||sql%rowcount); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Row for update in DEPT no found') ; WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.put_line('Query returm more that one row'); END;