Skip to Main Content
Oracle PL/SQL Language Pocket Reference, 4th Edition
book

Oracle PL/SQL Language Pocket Reference, 4th Edition

by Steven Feuerstein, Bill Pribyl, Chip Dawes
October 2007
Intermediate to advanced content levelIntermediate to advanced
178 pages
2h 50m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Language Pocket Reference, 5th Edition

Oracle PL/SQL Language Pocket Reference, 5th Edition

Steven Feuerstein, Bill Pribyl, Chip Dawes

Publisher Resources

ISBN: 9780596514044Errata Page