11.1 How do I…Issue single record queries with implicit cursors? ProblemI want to select one record into a variable within my PL/SQL routine. Also, I don’t want to explicitly step through the OPEN, FETCH, and CLOSE steps that must be specified with an explicit cursor. How do I issue single record queries with implicit cursors?
Technique
If a PL/SQL routine uses a SELECT statement but does not explicitly define a cursor, that SELECT statement is considered an implicit cursor. For more information on explicit cursors, see How-To 11.2. After the implicit cursor has run, you can find additional information about the results of the query. The cursor attributes are SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT. They are fully covered in How-To 11.3.
With an implicit cursor, the query must return exactly one row. The values of the cursor attributes always refer to the most recently executed SQL statement. If no rows are returned, or more than one row is returned, then an error is returned, and control of the flow moves to the exception portion of the PL/SQL routine.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP11_1.SQL, shown in Figure 11.1, creates the sample table used in this How-To.
2. The DEPT11 sample table contains two columns, DEPT_NO and DEPT_NAME, and is populated with three records. Run the file to create the sample table with its data.
SQL> START CHP11_1
Table created.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
SQL>
CHP11_2.SQL, shown in Figure 11.2, has a PL/SQL routine that queries the DEPT11 table for a single record.
3. Run CHP11_2.SQL to execute the PL/SQL routine that uses an implicit cursor.
SQL> START CHP11_2.sql
The Department Name is Marketing
PL/SQL procedure successfully completed.
How It Works
Steps 1 and 2 create the DEPT11 table used throughout this chapter and populate it with sample data. Step 3 runs CHP11_2.SQL, which executes a PL/SQL routine that uses an implicit cursor. Line 1 has the SET ECHO ON SERVEROUTPUT ON statement, which enables Oracle to pass text back to the SQL*Plus screen. Without setting this, the user will not have messages from the PL/SQL routine displayed. Lines 2 and 3 declare a variable, DEPARTMENT_NAME, for the PL/SQL routine that will be assigned the value for the department name. Line 4 contains the BEGIN statement, which is necessary for the PL/SQL construct. Lines 5-7 contain the cursor. The cursor is simply a SELECT statement with an additional INTO clause, which assigns the DEPT_NAME to the DEPARTMENT_NAME variable. Line 8 gives a message back to the user account, using the DBMS_OUTPUT.PUT_LINE procedure. Line 9 contains the END statement, also necessary for the PL/SQL construct.
Comments
Implicit cursors work well for queries that return exactly one row. In our example, if one additional record is added to the DEPT11 table, with DEPT_NO=1, then the cursor has returned more than one row, which causes a TOO_MANY_ROWS error. To use implicit cursors, you must be sure that at no point in the future will the query return more than one record. Explicit cursors allow for multiple records to be returned and add greater flexibility to PL/SQL routines (for information on using explicit cursors, see How-To 11.2). INSERT and UPDATE statements automatically use implicit cursors.