Advances a cursor to the next row of its result table and
retrieves the values from that row. When used with a list
cursor, the FETCH statement places the cursor on a specified
position within a list and retrieves a portion of that list.
When embedded in precompiled host language programs, the FETCH
statement assigns the values from the row to host parameters. In
interactive SQL, the FETCH statement displays the value of the
row on the terminal screen.
1 – Environment
You can use the FETCH statement:
o In interactive SQL (except for the USING DESCRIPTION clause)
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
FETCH -+---------------->-----------------+-+- <cursor-name> ++
+> fetch-orientation-clause FROM --+ +> <parameter> --+|
+--------------------------<----------------------------+
++------------------->---------------------------+----->
+--> INTO -+-+-> <parameter> ------------+-+-+--+
| | +-> <qualified-parameter> --+ | |
| | +-> <variable> -------------+ | |
| +--------- , <------------------+ |
+--> USING DESCRIPTOR <descriptor-name> -----+
fetch-orientation-clause =
-----+--> NEXT --------------------------------------+--->
+--> PRIOR --------------------------------------+
+--> FIRST --------------------------------------+
+--> LAST ---------------------------------------+
+--> RELATIVE ----> simple-value-expression -----+
+--> ABSOLUTE ----> simple-value-expression -----+
3 – Arguments
3.1 – cursor-name
Specifies the name of the cursor from which you want to retrieve
a row. Use a parameter if the cursor referred to by the cursor
name was declared at run time with a dynamic DECLARE CURSOR
statement. Specify the parameter used for the cursor name in
the dynamic DECLARE CURSOR statement.
You can use a parameter to refer to the cursor name only when the
FETCH statement is accessing a dynamic cursor.
3.2 – fetch FROM
Specifies the specific segment of the list cursor to fetch. These
options are available only if you specified the SCROLL option in
the DECLARE CURSOR statement. The choices are:
o NEXT
Fetches the next segment of the list cursor. This is the
default.
o PRIOR
Fetches the segment immediately before the current segment of
the list cursor.
o FIRST
Fetches the first segment of the list cursor.
o LAST
Fetches the last segment of the list cursor.
o RELATIVE simple-value-expression
Fetches the segment of the list cursor indicated by the value
expression. For example, relative -4 would fetch the segment
that is four segments prior to the current segment.
o ABSOLUTE simple-value-expression
Fetches the segment of the list cursor indicated by the value
expression. For example, absolute 4 would fetch the fourth
segment of the list cursor.
3.3 – INTO
Syntax options:
INTO parameter | INTO qualified-parameter | INTO variable
Specifies a list of parameters, qualified parameters (host
structures), or variables to receive the values SQL retrieves
from the row of the cursor's result table. The number of
parameters or variables in the list must be the same as the
number of values in the row. (If any of the parameters is a host
structure, SQL counts the number of parameters in that structure
when it compares the number of host parameters in the INTO clause
with the number of values in the row.)
The data types of parameters and variables must be compatible
with the values of the corresponding column of the cursor row.
3.4 – parameter
Specifies the name of the cursor from which you want to retrieve
a row. Use a parameter if the cursor referred to by the cursor
name was declared at run time with a dynamic DECLARE CURSOR
statement. Specify the parameter used for the cursor name in
the dynamic DECLARE CURSOR statement.
You can use a parameter to refer to the cursor name only when the
FETCH statement is accessing a dynamic cursor.
3.5 – simple-value-expression
Specifies either a positive or negative integer, or a numeric
module language or host language parameter.
3.6 – USING_DESCRIPTOR
Specifies the name of a descriptor that corresponds to an SQLDA.
If you use the INCLUDE statement to insert the SQLDA into your
program, the descriptor name is simply SQLDA.
An SQLDA is a collection of host language variables used only in
dynamic SQL. In a FETCH statement, the SQLDA points to a number
of parameters SQL uses to store values from the row. The number
of parameters must match the number of columns in the row.
The data types of parameters must be compatible with the values
of the corresponding column of the cursor row.
4 – Examples
Example 1: Using a FETCH statement embedded in a PL/I program
This program fragment uses embedded DECLARE CURSOR, OPEN, and
FETCH statements to retrieve and print the names and departments
of managers. The FETCH statement fetches the rows of the result
table and stores them in the parameters :FNAME, :LNAME, and
:DNAME.
/* Declare the parameters: */
BEGIN DECLARE SECTION
DCL ID CHAR(3);
DCL FNAME CHAR(10);
DCL LNAME CHAR(14);
END DECLARE SECTION
/* Declare the cursor: */
EXEC SQL DECLARE MANAGER CURSOR FOR
SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID ;
/* Open the cursor: */
EXEC SQL OPEN MANAGER;
/* Start a loop to process the rows of the cursor: */
DO WHILE (SQLCODE = 0);
/* Retrieve the rows of the cursor
and put the value in parameters: */
EXEC SQL FETCH MANAGER INTO :FNAME, :LNAME, :DNAME;
/* Print the values in the parameters: */
.
.
.
END;
/* Close the cursor: */
EXEC SQL CLOSE MANAGER;
Example 2: Using a FETCH statement to display segments in a
column of data type LIST
This interactive example uses a table cursor to retrieve a
row that contains a list from the RESUMES table. The OPEN
statement positions the cursor on the first segment of the list
in the RESUME column, and subsequent FETCH statements retrieve
successive segments of that list.
SQL> DECLARE TBLCURSOR2 CURSOR FOR SELECT EMPLOYEE_ID, RESUME
cont> FROM RESUMES;
SQL> DECLARE LSTCURSOR2 LIST CURSOR FOR SELECT RESUME
cont> WHERE CURRENT OF TBLCURSOR2;
SQL> OPEN TBLCURSOR2;
SQL> FETCH TBLCURSOR2;
00164
SQL> OPEN LSTCURSOR2;
SQL> FETCH LSTCURSOR2;
RESUME
This is the resume for 00164
SQL> FETCH LSTCURSOR2;
RESUME
Boston, MA
SQL> FETCH LSTCURSOR2;
RESUME
Oracle Corporation
SQL> FETCH LSTCURSOR2;
RESUME
%RDB-E-STREAM_EOF, attempt to fetch past end of record stream
SQL> CLOSE LSTCURSOR2;
SQL> SELECT * FROM RESUMES;
EMPLOYEE_ID RESUME
00164 72:2:3
1 row selected
SQL> CLOSE TBLCURSOR2;
SQL> COMMIT;
Example 3: Using a scrollable list cursor to fetch list data
This C program demonstrates the use of scrollable list cursors to
read list data from the sample personnel database using the FETCH
statement. The list data being read is from the RESUME column of
the RESUMES table in personnel. Note that the RESUME is divided
into three segments in this order:
1. A line including the employee's name: "This is the resume for
Alvin Toliver"
2. A line stating where the employee lives: "Boston, MA"
3. A line stating where the employee works: "Oracle Corporation"
#include stdio
#include descrip
/* Declare parameters for error handling by including the SQLCA. */
EXEC SQL INCLUDE SQLCA;
/* Error-handling section. */
dump_error( )
{
short errbuflen;
char errbuf[ 1024 ];
struct dsc$descriptor_s errbufdsc;
errbufdsc.dsc$b_class = DSC$K_CLASS_S;
errbufdsc.dsc$b_dtype = DSC$K_DTYPE_T;
errbufdsc.dsc$w_length = 1024;
errbufdsc.dsc$a_pointer = &errbuf;
if (SQLCA.SQLCODE != 0)
{
printf( "SQLCODE = %d\n", SQLCA.SQLCODE );
SQL$GET_ERROR_TEXT( &errbufdsc, &errbuflen );
errbuf[ errbuflen ] = 0;
printf("%s\n", &errbuf );
}
}
main()
{
/* Attach to the personnel database. */
EXEC SQL DECLARE ALIAS FILENAME personnel;
/* Declare variables. */
short two_s;
long two_l;
char blob[8];
char emp_id[6];
char seg2[ 81 ];
/* Declare a table cursor. */
exec sql declare resumes_cursor table cursor for
select employee_id, resume from resumes where employee_id = '00164';
/* Declare a read-only scrollable list cursor to fetch the RESUME column. */
exec sql declare resume_list_cursor read only scrollable list cursor for
select resume where current of resumes_cursor;
/* Open the table cursor. */
exec sql open resumes_cursor;
dump_error();
/* Place the first value in the table cursor (00164) into the emp_id parameter,
and the resume data into the blob parameter. */
exec sql fetch resumes_cursor into :emp_id, :blob;
dump_error();
/* Open the scrollable list cursor. */
exec sql open resume_list_cursor;
dump_error();
/* Begin to use the FETCH statement to read desired lines from the resume.
If an attempt is made to retrieve a segment that is out of range, the
program prints an error message.
*/
exec sql fetch last from resume_list_cursor into :seg2;
printf("FETCH LAST segment returned: %s\n", seg2 );
dump_error();
exec sql fetch next from resume_list_cursor into :seg2;
printf("FETCH NEXT segment returned: %s\n", seg2 );
dump_error();
exec sql fetch first from resume_list_cursor into :seg2;
printf("FETCH FIRST segment returned: %s\n", seg2 );
dump_error();
exec sql fetch next from resume_list_cursor into :seg2;
printf("FETCH NEXT segment returned: %s\n", seg2 );
dump_error();
exec sql fetch next from resume_list_cursor into :seg2;
printf("FETCH NEXT segment returned: %s\n", seg2 );
dump_error();
exec sql fetch relative -2 from resume_list_cursor into :seg2;
printf("FETCH RELATIVE -2 segment returned: %s\n", seg2 );
dump_error();
exec sql fetch first from resume_list_cursor into :seg2;
printf("FETCH FIRST segment returned: %s\n", seg2 );
dump_error();
exec sql fetch relative 2 from resume_list_cursor into :seg2;
printf("FETCH RELATIVE 2 segment returned: %s\n", seg2 );
dump_error();
exec sql fetch last from resume_list_cursor into :seg2;
printf("FETCH LAST segment returned: %s\n", seg2 );
dump_error();
exec sql fetch prior from resume_list_cursor into :seg2;
printf("FETCH PRIOR segment returned: %s\n", seg2 );
dump_error();
exec sql fetch ABSOLUTE 1 from resume_list_cursor into :seg2;
printf("FETCH ABSOLUTE 1 segment returned: %s\n", seg2 );
dump_error();
exec sql fetch relative 2 from resume_list_cursor into :seg2;
printf("FETCH RELATIVE 2 segment returned: %s\n", seg2 );
dump_error();
two_s = 2;
exec sql fetch ABSOLUTE :two_s from resume_list_cursor into :seg2;
printf("FETCH ABSOLUTE :two_s segment returned: %s\n", seg2 );
dump_error();
two_l = 2;
exec sql fetch ABSOLUTE :two_l from resume_list_cursor into :seg2;
printf("FETCH ABSOLUTE :two_1 segment returned: %s\n", seg2 );
dump_error();
exec sql fetch RELATIVE :two_l from resume_list_cursor into :seg2;
printf("FETCH RELATIVE :two_1 segment returned: %s\n", seg2 );
dump_error();
exec sql rollback;
}
The following example shows the output from the program:
FETCH LAST segment returned: Oracle Corporation
FETCH NEXT segment returned: Oracle Corporation
SQLCODE = 100
%SQL-W-NOTFOUND, No rows were found for this statement
FETCH FIRST segment returned: This is the resume for Alvin Toliver
FETCH NEXT segment returned: Boston, MA
FETCH NEXT segment returned: Oracle Corporation
FETCH RELATIVE -2 segment returned: This is the resume for Alvin Toliver
FETCH FIRST segment returned: This is the resume for Alvin Toliver
FETCH RELATIVE 2 segment returned: Oracle Corporation
FETCH LAST segment returned: Oracle Corporation
FETCH PRIOR segment returned: Boston, MA
FETCH ABSOLUTE 1 segment returned: This is the resume for Alvin Toliver
FETCH RELATIVE 2 segment returned: Oracle Corporation
FETCH ABSOLUTE :two_s segment returned: Boston, MA
FETCH ABSOLUTE :two_1 segment returned: Boston, MA
FETCH RELATIVE :two_1 segment returned: Boston, MA
SQLCODE = -1
%RDB-F-SEGSTR_EOF, attempt to fetch past the end of a segmented string
-RDMS-E-FETRELATIVE, fetch relative (2) causes reference out of range 1..3