Oracle 返回结果集 sys_refcursor

1     --说明：某个平台ID下某个店铺ID所有“等待买家付款”，“等待卖家发货,即买家已付款”,"货到付款"的订单结果集 2     --作者：杨斌 3     --日期：2012-08-29 4  5     Procedure PRO_QUERY_TRADES_BY_SHOPID( 6       prm_shop_id                 varchar2,     --店铺ID 7       prm_platform_id             varchar2,     --平台ID 8       out_trades       out   sys_refcursor,     --返回订单结果集 9 10       out_error_row   out           number,     --错误行11       out_error_msg   out          varchar2     --错误信息12     )13     IS14       str_sql varchar2(4000);--定义查询SQL语句变量15     BEGIN16       out_error_row :=0;--错误行17       out_error_msg :='';--错误信息18 19       --查询订单信息20       str_sql := 'select TID,SHOP_ID,PT_ID,STATUS_ID,FROM_ID,SHIP_ID,TYPE,BUYER_ID,BUYER_NAME,NUM,IS_POST,'||21                  'PAYMENT_TIME,TOTAL_FEE,TRADE_FEE,DEAL_FEE,PAYMENT,DISCOUNT_FEE,POST_FEE,SHIPPING_TYPE,' ||22                  'BUYER_MEMO,SELLER_MEMO,CREATE_TIME,MODIFIED_TIME,END_TIME,PAYMAENT_TYPE,BUYER_ALIPAY_NO,'||23                  'RECEIVER_NAME,RECEIVER_STATE,RECEIVER_CITY,RECEIVER_DISTRICT,RECEIVER_ADDRESS,RECEIVER_ZIP,'||24                  'RECEIVER_MOBILE,RECEIVER_PHONE,AVAILABLE_CONFIRM_FEE,RECEIVED_PAYMENT,INVOICE_INFO,POINT_FEE,'||25                  'SELLER_RECVRE_FUND,BUYER_RECVRE_FUND,BUYER_OBTAIN_POINT_FEE,IS_MERGE_ORDER,WAREHOUSE,AIRLINES,'||26                  'URGENT_TASK,URGENT_TASK_NAME,PROMOTION_NAME,PROMOTION_DISCOUNT_FEE,GIFT_ITEM_NAME,GIFT_ITEM_ID,'||27                  'GIFT_ITEM_NUM,PROMOTION_DESC,PROMOTION_ID,TO_ERP '||28                  'from t_xs_trade where pt_id = '''||prm_platform_id||''' and shop_id = '''||29                   prm_shop_id ||'''  and   status_id in ('''||ST_WAIT_BUYER_PAY||''','''||ST_WAIT_SELLER_SEND_GOODS||''') or (TYPE = '''||TRADE_TYPE_COD||''' and pt_id = '''||prm_platform_id||''' and shop_id = '''||30                   prm_shop_id ||''' )' ;31       out_error_row :=2;--错误行32       dbms_output.put_line(str_sql);33       open out_trades for str_sql;34       out_error_row :=1000;--错误行35     exception36       when others then37         out_error_msg := '数据库错误:' || sqlerrm;38 39     END PRO_QUERY_TRADES_BY_SHOPID;