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