Oracle 返回结果集 sys_refcursor

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     IS
14       str_sql varchar2(4000);--定义查询SQL语句变量
15     BEGIN
16       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     exception
36       when others then
37         out_error_msg := '数据库错误:' || sqlerrm;
38 
39     END PRO_QUERY_TRADES_BY_SHOPID;