ODAC

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).

Introduction

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.

Basic Code Structure

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;

Data Types

The PL/SQL language supports the following type categories:

Control Operators

Application Sample

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;

See also:

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback