Oracle requires that a package be defined. This package must contain a record type for the result set, and a cursor type that returns the record type. Create or replace package SPInfo asType SPRec is record ( pk integer, col1 varchar(100), col2 varchar(20)); Type SPCursor is ref cursor return SPRec; End SPInfo; Next, Oracle requires a stored procedure with the cursor type as the first parameter. Note that the download_cursor script only passes in two parameters, not three. For stored procedures returning result sets in Oracle, cursor types declared as parameters in the stored procedure definition define the structure of the result set, but do not define a true parameter as such. In this example, the stored procedure also adds the script to the MobiLink system table. Create or replace procedure DownloadMyTable( v_spcursor IN OUT SPInfo.SPCursor,v_last_dl_ts IN DATE,v_user_name IN VARCHAR ) AsBegin Open v_spcursor For select pk, col1, col2 from MyTable where last_modified > = v_last_dl_ts and employee = v_user_name; End;CALL ml_add_table_script ( 'v1', 'MyTable', 'download_cursor', '{CALL DownloadMyTable({ml s.last_table_download}, {ml s.username} )}');
精品推荐