Database Reference
In-Depth Information
Note:
Note that the UPDATE statement in the PL/SQL block does not
throw an exception when the row searched for is not found, but it does set
the cursor SQL to being NOTFOUND.
24.4.2.2
Single-Row SELECT Implicit Cursor
When using an implicit cursor with a query, the query must return no more
than one row. If it returns multiple rows, the procedure will fail. You can
use an explicit cursor or a cursor FOR loop (see the next example) for que-
ries returning multiple rows.
For a single-row SELECT statement, the INTO clause must be used.
When selecting a single row in a PL/SQL block, the value or values
retrieved by the query are placed into variables declared within the PL/
SQL block, as shown following in the second example. In this example,
four variables are declared: one for each of the four columns in the
SELECT statement. The query uses the INTO clause to place each col-
umn value into each corresponding variable. The variables are then used to
construct a line that is displayed on the screen. See the result of the follow-
ing code in Figure 24.8.
SET SERVEROUTPUT ON;
DECLARE
VARTIST_ID ARTIST.ARTIST_ID%TYPE;
VNAME ARTIST.NAME%TYPE;
VCITY ARTIST.CITY%TYPE;
VSTATE_PROVINCE ARTIST.STATE_PROVINCE%TYPE;
BEGIN
SELECT ARTIST_ID,NAME,CITY,STATE_PROVINCE
INTO VARTIST_ID,VNAME,VCITY,VSTATE_PROVINCE
FROM ARTIST WHERE NAME = 'Chicago';
DBMS_OUTPUT.PUT_LINE('OUTPUT IS : '||TO_CHAR(VARTIST_ID)
||' '||VNAME||' '||VCITY||' '||VSTATE_PROVINCE);
END;
/
SET SERVEROUTPUT OFF;
24.4.2.3
Cursor FOR Loop Implicit Cursor
A cursor FOR loop is often used as a substitute for an explicit cursor,
thereby simplifying code. In the example shown following, note how the
