存储过程返回结果集

/*------------------------------------------------------------------\
  |  AUTHOR  : yapi
  |  CREATED : 2009-11-23
  |  PURPOSE : 2A1_SQL:报关价格
  \------------------------------------------------------------------*/
  PROCEDURE query_pips_2a1(  p_datefrom varchar2,
                              p_dateto varchar2,
                              p_result_cursor OUT SYS_REFCURSOR) IS
  v_sql varchar2(4000);
  v_scode varchar(10);
  BEGIN
  v_scode:='SCS_BW';
  v_sql := 'Select
                    hst.this_doc_id,
                    hst.this_doc_generation_date_time,
                    lst.doc_ref_prop_doc_id,                   
                    lst.doc_ref_line_no,
                    substr(lst.prop_product_id, 1, instr(lst.prop_product_id, ''/'') - 1) tprop_product_id,
                    lst.price_association_description,
                    lst.glb_currency_code,
                    lst.monetary_amount,
                    lst.sample_order_quantity,
                    decode(hst.processing_status,
                            ''E'',
                            ''处理异常'',
                            ''UNSENT'',
                            ''已更新价格未发送DHL'',
                            ''SENT'',
                            ''已发送DHL'',
                            NULL,
                            ''尚未更新价格'') t_processing_status
          FROM hw_2a1_header_send_ti hst, hw_2a1_line_send_ti lst
        where hst.this_doc_id = lst.this_doc_id
          and hst.source_code = ''' || v_scode || '''';
    IF p_datefrom IS NOT NULL and p_dateto IS NOT NULL
    THEN
      v_sql := v_sql || ' and hst.created_date >= to_date(''' || p_datefrom || ''',''yyyy-mm-dd'')';
      v_sql := v_sql || ' and hst.created_date < (to_date(''' || p_dateto || ''',''yyyy-mm-dd'')+1)';
    END IF;
       OPEN p_result_cursor FOR v_sql;
  END query_pips_2a1;