WORKING WITH REF CURSOR
I create and populate the following table:
After opening a cursor variable, you can fetch the rows of the query result set with the same
The return type of the cursor variable must be compatible with the
You should use a strong cursor variable (declared with a TYPE that has a RETURN clause) whenever you are opening a cursor variable for a static query.
CREATE TABLE plch_tab (item VARCHAR2 (10)) / BEGIN INSERT INTO plch_tab VALUES ('Keyboard'); INSERT INTO plch_tab VALUES ('Mouse'); COMMIT; END; /I then create this function to fetch a row from a cursor variable and return the item:
CREATE OR REPLACE FUNCTION plch_getitem ( plch_cur IN SYS_REFCURSOR) RETURN plch_tab.item%TYPE IS lvretval plch_tab.item%TYPE; BEGIN FETCH plch_cur INTO lvretval; RETURN lvretval; END plch_getitem; /Now I need to write a block that will fetch the rows from the table and display both the count of rows fetched and the name of that last item fetched. Here is an almost complete version of this block:
DECLARE lvitem plch_tab.item%TYPE; test_cur SYS_REFCURSOR; BEGIN OPEN test_cur FOR SELECT * FROM plch_tab ORDER BY item; /*FETCH*/ DBMS_OUTPUT.put_line ('Count = ' || test_cur%ROWCOUNT); DBMS_OUTPUT.put_line ('Item = ' || NVL (lvitem, 'NOT SET')); CLOSE test_cur; END; /Which of the choices offer a replacement for the /*FETCH*/ so that after the resulting block is executed, the following output will be shown on the screen?
Count = 2 Item = Mouse
After opening a cursor variable, you can fetch the rows of the query result set with the same
FETCH
statement you would use with an explicit cursor. You can fetch from the cursor variable in the same block in which it was opened, but also from within any block in which the cursor variable can be referenced (for example, if you passed it as an argument to a subprogram).The return type of the cursor variable must be compatible with the
into_clause
of the FETCH
statement. If the cursor variable is strong, PL/SQL catches incompatibility at compile time. If the cursor variable is weak, PL/SQL catches incompatibility at run time, raising the predefined exception ROWTYPE_MISMATCH
before the first fetch.You should use a strong cursor variable (declared with a TYPE that has a RETURN clause) whenever you are opening a cursor variable for a static query.
Comments
Post a Comment