Stored procedures use cursor variables to return result sets

Stored procedures use cursor variables to return result sets

1) Login sqlplus, the user is Scott, using the table EMP provided by the user

[SQL]

C:/users/administrator>sqlplus Scott/scott

2) write a function with the return value as a 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 WHERE 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;

3) Write a plsql block, call the function above to get the cursor variable returned

[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%notfound;

Dbms_output. Put_Line (' | | ') ==> ' | | Rec_emp. ENAME);

END LOOP;

END IF;

CLOSE Empcursor;

END;

4) A few notes:

① has three statements to control the use of cursor variables, namely open for, fetch, and close. Where Open is used for opening a cursor variable. Fetch is used to store data in a cursor variable in a variable. Close is used to close the cursor after the traversal is complete.

② When using cursors, you can specify the REF cursor as a strongly typed or weakly type (the way you add return rowtype after the definition), and if you specify a strong type, be careful to avoid type mismatch errors.

③ cursor variables can be passed between applications to the database server and can be passed inside the database. The application or database itself does not have a separate variable storage space, its cursor variable is actually a pointer to memory, in essence, the data is shared.

④ is able to apply four of the cursor's properties%found,%notfound,%isopen and%rowcount to the cursor variable to determine the state of the cursor variable.

⑤ can use a fetch to get one record at a time, or you can use fetch BULK COLLECT into to get one or more of them into one or more collection types at a time.

⑥ don't forget to close the cursor.

Ibatis Call

<resultmap type= "********. Advnushimikomiinputexceloutputdto "id=" Advnushimikomiinputexceloutputdto ">
</resultMap>
<select id= "Selectadvnushimikomiinputexcelsuminetmokocursor" parametertype= "map" statementtype= "CALLABLE" >
<! [cdata[
{#{resultlist, mode=out, Jdbctype=cursor, Javatype=java.sql.resultset, resultmap=advnushimikomiinputexceloutputdto } = Call Getsumimokocursor (
#{v_nengetsu,jdbctype=char,mode=in},
#{v_uriagebukacode,jdbctype=char,mode=in},
#{v_tantobukacode,jdbctype=char,mode=in},
#{v_gyosyumcode,jdbctype=char,mode=in},
#{v_advnushicode,jdbctype=char,mode=in}
)}
]]>
</select>

Interface

Advnushimikomiinputdao.selectadvnushimikomiinputexcelsuminetmokocursor (map);
List<advnushimikomiinputexceloutputdto> Advnushimikomiinputexcelsuminetmoko = (List< advnushimikomiinputexceloutputdto>) map.get ("Resultlist");

Stored procedures use cursor variables to return result sets