The cursor is returned for the Oracle stored procedure. The query statement is spliced using dynamic SQL statements.

1. First create a custom package, which contains the declaration of the cursor variable

Create or replace package POLICE. pkg_package

As

Type type_cursor is ref cursor;

End;

2. Use of Stored Procedures

Create or replace procedure POLICE. DangXmList (lx in varchar2, begin1 date, end1 date, num varchar2, type1 varchar2, name1 varchar2, cur_arg out pkg_package.type_cursor)

Is

Mysql varchar2 (1000 );

Begin

Mysql: = select distinct * from v_p_dang_all where 1 = 1;

If lx <> then

Mysql: = mysql | and ALLKIND = | lx |;

End if;

If begin1 <> then

Mysql: = mysql | and INSERT_TIME = | begin1 |;

End if;

If begin1 <> then

Mysql: = mysql | and INSERT_TIME = | end1 |;

End if;

If type1 = xm then

Mysql: = mysql | and xm = | name1 |;

Else

Mysql: = mysql | and id_code = | name1 |;

End if;

Open cur_arg for mysql;

End;