Oracle PL/SQL Language Pocket Reference, 4th Edition

Database Interaction

PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, MERGE, and SELECT) directly in PL/SQL programs. You also can execute Data Definition Language (DDL) statements through the use of dynamic SQL. In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.

Sequences in PLSQL

Sequences are frequently used to generate keys in an Oracle database. To assign a sequence’s NEXTVAL or CURRVAL to a PLSQL variable, select the pseudo-column from the table DUAL, like this:

SELECT my_sequence.NEXTVAL INTO my_variable FROM dual;

Starting with Oracle Database 11g, you can reference the sequence pseudo-columns anywhere in your programs in which a number expression can appear. For example:

my_variable := my_sequence.NEXTVAL;

Transaction Management

The Oracle database provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, specify ROLLBACK FORCE). A transaction begins with the first change to data and ends with either a COMMIT or a ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or multiple transactions can be in a single PL/SQL block. The PL/SQL-supported transaction statements include COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE, described in the following sections. ...