Cursor Example Void worthRanges(){ int i,digits,counts[15]; EXEC SOL BEGIN DECLARE SECTION; int worth;char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE execCursor CURSOR FOR SELECT netWorth FROM MovieExec; EXEC SQL OPEN execCursor; while (1){EXEC SQL FETCH FROM execCursor INTO worth; if(NO MORE_TUPLES)BREAK; else..... ) EXEC SOL CLOSE execCursor;
Cursor Example Void worthRanges() { int i,digits, counts[15]; EXEC SQL BEGIN DECLARE SECTION; int worth; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE execCursor CURSOR FOR SELECT netWorth FROM MovieExec; EXEC SQL OPEN execCursor ; while (1) { EXEC SQL FETCH FROM execCursor INTO :worth; if (NO_MORE_TUPLES) BREAK; else ….. } EXEC SQL CLOSE execCursor ; … }
More about cursor: The order in which tuples are fetched from the relation can be specified. The effect of changes to the relation that the cursor ranges over can be limited. The motion of the cursor through the list of tuples can be varied
More about cursor: The order in which tuples are fetched from the relation can be specified. The effect of changes to the relation that the cursor ranges over can be limited. The motion of the cursor through the list of tuples can be varied
Modification by cursor With Where clause WHERE CURRENT ed by the name of the cursor. Define NO_MORE_TUPLES !(strc L OPEN execCursor; mp(SQLSTATE,”02000) EC SQL FETCH FROM execCursor INTO exeeme,:execAddr,certNo,worth; if (NO_MORE_TUPLES)BREAK; IF (WORTH 1000) EXEC SQL DELETE FROM MovieExec WHERE CURRENT OF execCursor; else EXEC SQL CLOSE execCursor;
Modification by cursor With Where clause WHERE CURRENT OF followed by the name of the cursor. e.g. ….. EXEC SQL OPEN execCursor; while (1) { EXEC SQL FETCH FROM execCursor INTO :execName,:execAddr,:certNo,:worth; if (NO_MORE_TUPLES) BREAK; IF (WORTH < 1000) EXEC SQL DELETE FROM MovieExec WHERE CURRENT OF execCursor; else ….. EXEC SQL CLOSE execCursor; Define NO_MORE_TUPLES !(strc mp(SQLSTATE, ”02000 ”))
Protecting against concurrent updates EXEC SQL DECLARE execCursor INSENSITIVE CURSOR FOR SELECT netWorth FROM MovieExec; The SQL system will guarantee that changes to relation MovieExec made between one opening and closing of execCursor will not affect the set of tuples fetched. Insensitive cursors could be expensive,systems spend a lot of time to manage data access
Protecting against concurrent updates EXEC SQL DECLARE execCursor INSENSITIVE CURSOR FOR SELECT netWorth FROM MovieExec; The SQL system will guarantee that changes to relation MovieExec made between one opening and closing of execCursor will not affect the set of tuples fetched. Insensitive cursors could be expensive, systems spend a lot of time to manage data access
Scrolling Cursors EXEC SOL DECLARE execCursor SCROLL CURSOR FOR MovieExec; The cursor may be used in a manner other than moving forward in the order of tuples. Follow FETCH by one of several options that tell where to find the desired tuple.Those options are NEXT,PRIOR,FIRST,LAST and so on
Scrolling Cursors EXEC SQL DECLARE execCursor SCROLL CURSOR FOR MovieExec; The cursor may be used in a manner other than moving forward in the order of tuples. Follow FETCH by one of several options that tell where to find the desired tuple. Those options are NEXT, PRIOR, FIRST, LAST and so on