SELECT J.name, R.laugh_volume, C.name
FROM joke J, response R, comedian C
WHERE J.joke_id = R.joke_id
AND J.joker_id = C.joker_id;
BEGIN
END;
Here, the cursor does not act as a pointer into any actual table in the database. Instead, the cursor is a
pointer into the virtual table represented by the SELECT statement (SELECT is called a virtual table
because the data it produces has the same structure as a table rows and columns but it exists only
for the duration of the execution of the SQL statement). If the triple-join returns 20 rows, each row
containing the three columns in the preceding example, then the cursor functions as a pointer into
those 20 rows.
6.2.1 Types of Cursors
You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor.
Generally, there are two types of SQL that you can execute in PL/SQL: static and dynamic. SQL is
static if the content of the SQL statement is determined at compile time. A SQL statement is dynamic
if it is constructed at runtime and then executed.
Dynamic SQL is made possible in PL/SQL only through the use of the DBMS_SQL built-in package
(see
Appendix C, Built-In Packages). All other forms of SQL executed inside a PL/SQL program
represent static SQL; these forms of cursors are the focus of the remainder of this chapter.
Even within the category of static SQL, we have further differentiation. With the advent of PL/SQL
Release 2.3, you can choose between two distinct types of cursor objects:
Static cursor objects
These are the really static cursors of PL/SQL. The SQL is determined at compile time, and the
cursor always refers to one SQL statement, which is known at compile time. The examples
shown earlier in this chapter are static cursors.
Unless otherwise noted, any reference to "static cursor" refers to this sub-category of static (as
opposed to dynamic) cursors.
Cursor variables
You can declare a variable which references a cursor object in the database. Your variable
may refer to different SQL statements at different times (but that SQL is defined at compile
time, not run time).
The cursor variable is one of the newest enhancements to PL/SQL and will be unfamiliar to most
programmers. Cursor variables act as references to cursor objects. As a true variable, a cursor
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
variable can change its value as your program executes. The variable can refer to different cursor
objects (queries) at different times. You can also pass a cursor variable as a parameter to a procedure
or function. Cursor variables are discussed later in this chapter.
Static PL/SQL cursors have been available since PL/SQL Version 1. The static version of cursors
"hardcodes" a link between the cursor name and a SELECT statement. The static cursor itself comes
in two flavors: implicit and explicit.
PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement,
such as an INSERT or a SELECT that returns a single row.
You, the programmer, define your own explicit cursors in your code. You must use an explicit cursor
when you need to retrieve more than one row of data at a time through a SELECT statement. You can
then use the cursor to fetch these rows one at a time. The set of rows returned by the query associated
with an explicit cursor is called the active set or result set of the cursor. The row to which the explicit
cursor points is called the current row of the result set.
The bulk of this chapter is devoted to the management of static, explicit cursors. All information
about cursor variables is localized in
Section 6.12, "Cursor Variables". Any references to PL/SQL
cursors and cursor characteristics outside of that section will pertain to static cursors.
6.2.2 Cursor Operations
Regardless of the type of cursor, PL/SQL performs the same operations to execute a SQL statement
from within your program:
PARSE
The first step in processing an SQL statement is to parse it to make sure it is valid and to
determine the execution plan (using either the rule-based or cost-based optimizer).
BIND
When you bind, you associate values from your program (host variables) with placeholders
inside your SQL statement. For static SQL, the SQL engine itself performs these binds. When
you use dynamic SQL, you explicitly request a binding of variable values.
OPEN
When you open a cursor, the bind variables are used to determine the result set for the SQL
statement. The pointer to the active or current row is set to the first row. Sometimes you will
not explicitly open a cursor; instead the PL/SQL engine will perform this operation for you (as
with implicit cursors).
EXECUTE
In the execute phase, the statement is run within the SQL engine.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FETCH
If you are performing a query, the FETCH command retrieves the next row from the cursor's
result set. Each time you fetch, PL/SQL moves the pointer forward in the result set. When
working with explicit cursors, remember that if there are no more rows to retrieve, then
FETCH does nothing (it does not raise an error).
CLOSE
The CLOSE statement closes the cursor and releases all memory used by the cursor. Once
closed, the cursor no longer has a result set. Sometimes you will not explicitly close a cursor;
instead the PL/SQL engine will perform this operation for you (as with implicit cursors).
Figure 6.1 shows how some of these different operations are used to fetch information from the
database into your PL/SQL program.
Figure 6.1: Using cursor operations to fetch database information into your program
Previous: 6.1 Transaction
Management
Oracle PL/SQL
Programming, 2nd Edition
Next: 6.3 Implicit and
Explicit Cursors
6.1 Transaction Management
Book Index
6.3 Implicit and Explicit
Cursors
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 5.2 Sequential
Control Statements
Chapter 6
Next: 6.2 Cursors in PL/
SQL
6. Database Interaction and Cursors
Contents:
Transaction Management
Cursors in PL/SQL
Implicit and Explicit Cursors
Declaring Cursors
Opening Cursors
Fetching from Cursors
Column Aliases in Cursors
Closing Cursors
Cursor Attributes
Cursor Parameters
SELECT FOR UPDATE in Cursors
Cursor Variables
Working with Cursors
PL/SQL is tightly integrated with the Oracle database via the SQL language. From within PL/SQL,
you can execute any DML (data manipulation language) statements, including INSERTs, UPDATEs,
DELETEs, and, of course, queries. You can also join multiple SQL statements together logically as a
transaction, so that they are either saved ("committed" in SQL parlance) together or rejected in their
entirety (rolled back). This chapter examines the SQL statements available inside PL/SQL to manage
transactions. It then moves on to cursors, which give you a way to fetch and process database
information in your PL/SQL program.
6.1 Transaction Management
The Oracle RDBMS provides a very robust transaction model, as you might expect for a relational
database. You (or more precisely, your application code) determine what constitutes a transaction, the
logical unit of work that must be either saved together with a COMMIT statement or rolled back
together with a ROLLBACK statement. A transaction begins implicitly with the first SQL statement
issued since the last COMMIT or ROLLBACK (or with the start of a session).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PL/SQL provides the following statements for transaction management:
COMMIT
Saves all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
ROLLBACK
Erases all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
ROLLBACK TO SAVEPOINT
Erases all changes made since the specified savepoint was established.
SAVEPOINT
Establishes a savepoint, which then allows you to perform partial ROLLBACKs.
SET TRANSACTION
Allows you to begin a read-only or read-write session, establish an isolation level, or assign
the current transaction to a specified rollback segment.
LOCK TABLE
Allows you to lock an entire database table in the specified mode. This overrides the default
row-level locking usually applied to a table.
These statements are explained in more detail in the following sections.
6.1.1 The COMMIT Statement
When you COMMIT, you make permanent any changes made by your session to the database in the
current transaction. Once you commit, your changes will be visible to other Oracle sessions or users.
The syntax for the COMMIT statement is:
COMMIT [WORK] [COMMENT text];
The WORK keyword is optional and can be used to improve readability.
The COMMENT keyword specifies a comment which is then associated with the current transaction.
The text must be a quoted literal and can be no more than 50 characters in length. The COMMENT
text is usually employed with distributed transactions. This text can be handy for examining and
resolving in-doubt transactions within a two-phase commit framework. It is stored in the data
dictionary along with the transaction ID.
Note that COMMIT releases any row and table locks issued in your session, such as with a SELECT
FOR UPDATE statement. It also erases any savepoints issued since the last COMMIT or
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ROLLBACK.
Once you COMMIT your changes, you cannot roll them back with a ROLLBACK statement.
The following statements are all valid uses of the COMMIT:
COMMIT;
COMMIT WORK;
COMMIT COMMENT 'maintaining account balance'.
6.1.2 The ROLLBACK Statement
When you ROLLBACK, you undo some or all changes made by your session to the database in the
current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the
ROLLBACK gives you a way to erase mistakes you might have made, as in:
DELETE FROM orders;
"No, no! I meant to delete only the orders before May 1995!" No problem, just issue ROLLBACK.
From an application coding standpoint, ROLLBACK is important because it allows you to clean up
or restart from a "clean state" when a problem occurs.
The syntax for the ROLLBACK statement is:
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];
There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a
savepoint at which the ROLLBACK should stop.
The parameterless ROLLBACK undoes all outstanding changes in your transaction.
The ROLLBACK TO version allows you to undo all changes and release all acquired locks which
were issued since the savepoint identified by savepoint_name was marked (see the next section on the
SAVEPOINT statement for more information on how to mark a savepoint in your application).
The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or
variable name.
All of the following uses of ROLLBACK are valid:
ROLLBACK;
ROLLBACK WORK;
ROLLBACK TO begin_cleanup;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
All of the following uses of ROLLBACK are invalid:
ROLLBACK SAVEPOINT;
ORA-02181: invalid option to ROLLBACK WORK
Must use TO keyword before SAVEPOINT.
ROLLBACK WORK TO;
ORA-02182: save point name expected
Must specify savepoint name.
ROLLBACK TO SAVEPOINT 'favorite_movies';
ORA-03001: Unimplemented feature
Savepoint cannot be in quotes.
When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name
are erased. The savepoint to which you roll back is not, however, erased. This means that you can
restart your transaction from that point and, if necessary, roll back to that same savepoint if another
error occurs.
Immediately before you execute an INSERT, UPDATE, or DELETE, PL/SQL implicitly generates a
savepoint. If your DML statement then fails, a rollback is automatically performed to that implicit
savepoint. In this way, only that last DML statement is undone.
6.1.3 The SAVEPOINT Statement
SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker
allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after
that savepoint, but preserving any changes and locks which occurred before you marked the
savepoint.
The syntax for the SAVEPOINT statement is:
SAVEPOINT savepoint_name;
where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an
Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and
#, $, or _ ), but that you do not need to (nor can you) declare that identifier.
Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current
transaction, that savepoint is "moved" from its original position to the current point in the transaction,
regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are
executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new
SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently
marked savepoint.
6.1.4 The SET TRANSACTION Statement
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The SET TRANSACTION statement allows you to begin a read-only or read-write session, establish
an isolation level, or assign the current transaction to a specified rollback segment. This statement
must be the first SQL statement processed in a transaction and it can appear only once. This
statement comes in the following four flavors:
SET TRANSACTION READ ONLY;
This version defines the current transaction as read-only. In a read-only transaction, all subsequent
queries only see those changes which were committed before the transaction began (providing a read-
consistent view across tables and queries). This statement is useful when you are executing long-
running, multiple query reports and you want to make sure that the data used in the report is
consistent:
SET TRANSACTION READ WRITE;
This version defines the current transaction as read-write:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ
COMMITTED;
This version defines how transactions that modify the database should be handled. You can specify a
serializable or read-committed isolation level. When you specify SERIALIZABLE, a data
manipulation statement (update, insert, delete) which attempts to modify a table already modified in
an uncommitted transaction will fail. To execute this command, you must set the database
initialization parameter COMPATIBLE to 7.3.0 or higher.
If you specify READ COMMITTED, a DML which requires row-level locks held by another
transaction will wait until those row locks are released:
SET TRANSACTION USE ROLLBACK SEGMENT rollback_segname;
This version assigns the current transaction to the specified rollback segment and establishes the
transaction as read-write. This statement cannot be used in conjunction with SET TRANSACTION
READ ONLY.
6.1.5 The LOCK TABLE Statement
This statement allows you to lock an entire database table with the specified lock mode. By doing
this, you can share or deny access to that table while you perform operations against it. The syntax
for this statement is:
LOCK TABLE table_reference_list IN lock_mode MODE
[NOWAIT];
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
where table_reference_list is a list of one or more table references (identifying either a local table/
view or a remote entity through a database link), and lock_mode is the mode of the lock, which can
be one of the following:
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already been
locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table is
available (and there is no set limit on how long Oracle will wait). Locking a table never stops other
users from querying or reading the table.
The following LOCK TABLE statements show valid variations:
LOCK TABLE emp IN ROW EXCLUSIVE MODE;
LOCK TABLE emp, dept IN SHARE MODE NOWAIT;
LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE;
Now that you know the "macro" commands for managing transactions from within a PL/SQL
application, let's move on to cursors; you will use cursors (in one form or another) to create
transactions (i.e., specify the SQL statements which make up the transaction).
Previous: 5.2 Sequential
Control Statements
Oracle PL/SQL
Programming, 2nd Edition
Next: 6.2 Cursors in PL/
SQL
5.2 Sequential Control
Statements
Book Index
6.2 Cursors in PL/SQL
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 5.1 Conditional
Control Statements
Chapter 5
Conditional and Sequential
Control
Next: 6. Database
Interaction and Cursors
5.2 Sequential Control Statements
Certain PL/SQL control structures offer structured methods for processing executable statements in
your program. You use an IF statement to test a condition to determine which parts of the code to
execute. You use one of the LOOP variations (described in Chapter 7, Loops) to execute a section of
code more than once. In addition to these well-structured approaches to program control, PL/SQL
offers two other statements to handle out of the ordinary requirements for sequential processing:
GOTO and NULL. The GOTO statement allows you to perform unconditional branching to another
executable statement in the same execution section of a PL/SQL block. The NULL statement gives
you a way to tell the compiler to do absolutely nothing.
The following sections explain the implementation of the GOTO and NULL statements in PL/SQL.
As with other constructs in the language, use them with care and use them appropriately, and your
programs will be stronger for it.
5.2.1 The GOTO Statement
The GOTO statement performs unconditional branching to a named label. The general format for a
GOTO statement is:
GOTO label_name;
where label_name is the name of a label.
This GOTO label is defined in the program as follows:
<<label_name>>
You must surround the label name with double enclosing angle brackets (<< >>). In this case, the
label names a loop. This label can then be appended to the END LOOP statement, making the
termination of the loop more visible. You can also issue an EXIT statement for a particular labeled
loop from within another (enclosed) loop. Finally, you can GOTO that loop label, even though it was
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét