The stored procedure uses the cursor variable to return the result set.

The stored procedure uses the cursor variable to return the result set. 1) log on to SQLPLUS and use the table EMP www.2cto.com [SQL] C provided by SCOTT: \ Users \ Administrator> sqlplus scott/scott 2) Compile a FUNCTION and RETURN the cursor variable [SQL] CREATE OR REPLACE FUNCTION GETEMPCURSOR (PRM_CHOICE IN NUMBER) RETURN SYS_REFCURSOR IS EMPCURSOR SYS_REFCURSOR; begin if PRM_CHOICE = 1 then open empcursor for select * from emp where deptno = 10; ELSIF PRM_CHOICE = 2 then open empcursor for select * FROM EMP W Here deptno = 20; ELSIF PRM_CHOICE = 3 then open empcursor for select * from emp where deptno = 30; else open empcursor for select * from emp; end if; return empcursor; END; www.2cto.com 3) Compile a PLSQL block and call the above FUNCTION to obtain the returned cursor variable [SQL] DECLARE EMPCURSOR SYS_REFCURSOR; REC_EMP EMP % ROWTYPE; BEGIN EMPCURSOR: = GETEMPCURSOR (10 ); if empcursor % isopen then loop fetch empcursor into REC_EMP; exit when empcursor % N OTFOUND; DBMS_OUTPUT.PUT_LINE (''| '=>' | REC_EMP.ENAME); end loop; end if; close empcursor; END; www.2cto.com 4: ① There are three statements that can control the use of cursor variables: open for, FETCH, and CLOSE. OPEN is used to OPEN the cursor variable. FETCH is used to store data in the cursor variable to the variable. CLOSE is used to CLOSE the cursor after the traversal is complete. ② When using a CURSOR, you can specify ref cursor as a strong or weak type (add the return rowtype method after the definition). If it is specified as a strong type, avoid Type Mismatch Errors. ③ The cursor variable can be transferred between the database server and the database server. The application or database itself does not have a separate variable storage space. Its cursor variable is actually a pointer to the memory, which is essentially shared. ④ Apply the four attributes % FOUND, % NOTFOUND, % ISOPEN, and % ROWCOUNT of the cursor to the cursor variable to determine the state of the cursor variable. ⑤ You can use FETCH to retrieve one record at a time, or use fetch bulk collect into to retrieve one or more records at a time and put them in one or more Collection types. ⑥ Do not forget to close the cursor.