|
|
The last three letters in the name of the Oracle programming language are SQL. Since PL/SQL is
designed to extend the scope of SQL, it also integrates well with the SQL language itself.
This chapter covers the details of using SQL in your PL/SQL program units. In the last chapter,
you used a little SQL in the sample PL/SQL procedure. There is quite a bit more to working with
data that is accessed with SQL, including the use of cursors, special data types designed for cooperation
with the data types in your Oracle database, and some special types of program structures
that can be used to work with sets of data.
Basic SQL in PL/SQL
As shown in the last chapter, you use the INTO SQL keyword to retrieve values from the Oracle
database into variables in a PL/SQL program unit, such as the following:
SELECT COUNT(*) INTO empno FROM SCOTT.EMP;
In this example, the count of rows from this query would be placed into the empno variable, which
would have been declared prior to the execution of this statement. As described in the last chapter,
you could have declared the empno variable in the declaration section of the PL/SQL block or after
the keyword DECLARE.
This example will give you everything you need to access a single row result set for an SQL query.
Since SQL is a set-oriented language, however, you will frequently be working with more than one
row in a result set. The next section introduces you to the PL/SQL object that can help you work
with these sets of date.Cursors
SQL is a set-oriented language. That’s part of the power of SQL—you can not only return a set of rows as a
response to an SQL SELECT statement, but the number of rows that are returned can vary, depending on the
time the query is sent and the values included for conditional comparisons. (Think bind variables—please!)
But frequently you will also want to work with each row in the result set one at a time. To accomplish this,
you will usually use one of the LOOP constructs described previously and an entity known as a cursor.
A cursor acts logically as a pointer into a result set. You can move the cursor through the result set,
processing each row, until you determine you are at the end of the result set. There are three types of
syntax associated with cursors: creating the cursor, fetching with the cursor, and closing the cursor. In
addition, there are a number of attributes of a cursor you can use in your logical comparisons.
Creating a Cursor
You declare a cursor just like you would any other variable, with the following syntax in a declaration
section, or after the DECLARE keyword:
CURSOR cursor_name IS sql_statement;
cursor_name is the name of the cursor, and sql_statement is the SQL statement associated with the
cursor. When you declare a cursor, you are simply allocating memory for the cursor and associating it
with an SQL statement.
Keep in mind that a cursor is a special type of variable. You cannot assign values to it in your PL/SQL code.
There is a type of variable that can act as a cursor for different SQL statements, which is called a REF
CURSOR. This type of cursor is discussed later in this section.
Opening a Cursor
When a cursor is associated with an SQL SELECT statement, you cause the execution of the SQL in the
Oracle database by executing the cursor with the following syntax:
OPEN cursor [argument[, argument . . .]];
cursor is the name of the declared cursor, while the arguments that follow are any values that need to
be passed to the statement established for the cursor, such as bind variables.
Fetching Data
Once a cursor has been opened, you can retrieve the rows from the executed query with the following
syntax:
FETCH cursor INTO variable[, variable];The variables listed following the INTO keyword must match the columns returned from the query.
There is also a way to declare a variable that will automatically accept the values described in the query
using the %ROWTYPE data type, which is described later in this chapter.
Closing the Cursor
Like all variables, cursors take up memory, so for that reason, as well as logical consistency, you probably
want to close the cursor once you are done using it.
The syntax to close a cursor is as follows:
CLOSE cursor;
You must close a cursor before you can reopen it to run a query again. You might want to reopen a cursor
to rerun the query with different selection criteria passed to it.SapereOnLine |
|
|
|
|
|
