create or replace procedure p_deal_order_complete_info(
                                                       V_ORDER_ID           IN DEAL_ORDER.ORDER_ID%TYPE, --订单ID
                                                       V_ORDER_TYPE         IN DEAL_ORDER.ORDER_TYPE%TYPE, --订单类型
                                                       V_prov_code          IN DEAL_ORDER.Prov_Code%TYPE, --省编码
                                                       V_city_code          IN DEAL_ORDER.city_code%TYPE, --市编码
                                                       V_order_status       IN DEAL_ORDER.order_status%TYPE, --订单状态
                                                       V_channel_id         IN DEAL_ORDER.channel_id%TYPE, --渠道
                                                       V_begin_date         IN DEAL_ORDER.Create_Date%TYPE, --创建时间From （DATE）
                                                       V_end_date           IN DEAL_ORDER.Create_Date%TYPE, --创建时间To （DATE）
                                                       v_syscursor          out sys_refcursor
                                                       )
is
/* 过程、函数简要描述信息
    **********************************************************
    *  存储过程名  : p_deal_order_complete_info
    *  建立日期      : 2012-10-26
    *  作者              : abc
    *  模块              : 交易管理
    *  功能描述      : 导出订单信息
    *  输入参数      ： 订单ID
                        订单类型
                        地区
                        订单状态
                        渠道
                        创建时间From （DATE）
                        创建时间To （DATE）
    *
    *
    *  输出参数      ：<订单ID  --订单表
                       订单类型
                       地区
                       订单状态
                       金额
                       渠道
                       创建时间
                       备注
                       客户名称 --　用户管理表,通过订单表的用户id，与此表关联，可以得到客户名称和身份证号码
                       身份证号码
                       账户名称
                      
                       支付方式　－－付款信息,通过订单表的付款id与此表关联，可以得到此
                       收货人
                       联系号码
                       手机号码
                       物流公司   --不知源于哪个表
                       物流编号　　－－源于哪个表啊，订单表的
                       送货人　－－不知是哪个表
                       送货人联系电话　－－同上
                      
                       订单明细ID　　－－订单明细表
                       销售品ID
                       销售品名称
                       销售品类型
                       价格
                       数量
                      
                       选择项ID　　－－订单用户选择记录表　，通过订明明细id与订明明细表关联
                       选择项类型
                       选择项名称>集合
    *  源表：        　DEAL_ORDER　订单表
    *  目标表：
    *  备注:
                        ①字符类型入参为空时，不做为查询条件。
                          数值类型入参为-1时，不做为查询条件。
                        ②订单ID、地区提供模糊查询功能。
    *------------------------------------------------------------
    *  修改历史
    *  序号    日期              修改人      修改原因
    *  1       2012-10-26        abc      创建
    *------------------------------------------------------------
  */
  v_sql      varchar2(5000);
  V_PAR_DATE VARCHAR2(30); -- 操作日期
  V_PRO_NAME VARCHAR2(40); -- 存储过程名
  V_ERR_CODE VARCHAR2(50); -- 错误代码
  V_ERR_MSG  VARCHAR2(3000); -- 错误信息
begin
  V_ERR_MSG  := '游标初始化失败';
  V_PRO_NAME := 'p_deal_order_complete_info';
  V_PAR_DATE := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MM:SS');
  PLOG.INFO(V_PRO_NAME, '开始运行，日期: ' || V_PAR_DATE);
  --主体逻辑
     --若无任何参数
    if V_ORDER_ID is null and V_ORDER_TYPE is null and
       V_prov_code is null and V_city_code is null and V_order_status is null and
       V_channel_id is null and V_begin_date is null and V_end_date is null then
              open v_syscursor for 'select d.order_id,d.order_type,
                                           d.Prov_Code,d.city_code,
                                           d.order_status,d.ORDER_PRICE,
                                           d.channel_id,d.CREATE_DATE,
                                           d.ORDER_REMARK,uinfo.REALNAME,
                                           uinfo.CARDNUM,uinfo.username,
                                           dpay.PAY_TYPE,dpay.SEND_CUSNAME,
                                           dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
                                           de.ORDER_DETAIL_ID,
                                           de.OFFER_ID,de.OFFER_TITLE,
                                           de.OFFER_TYPE,de.PRICE,
                                           de.QTY,dc.CHOOSE_ID,
                                           dc.CHOOSE_TYPE,dc.CHOOSE_NAME
                                          
                                          
                                    from DEAL_ORDER d,
                                         DEAL_ORDER_DETAIL de,
                                         DEAL_ORDER_USER_CHOOSE dc
                                         DEAL_PAY_INFO dpay,
                                         USER_INFO uinfo
                                    where d.order_id=de.order_id               and
                                          de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and 
                                          d.PAY_ID=dpay.PAY_ID                 and
                                          d.USER_ID=uinfo.USERID';
 else
        --如下的sql刚开始一直编译不通过，后来把此sql单拿出为到ue,然后再复制回来即可，我想可能是什么特殊字符导致不能编译通过，还有一法，就是把如下的sql慢慢接着步步测试，直到完成测试成功
     v_sql :='select                       d.order_id,d.order_type,
                                           d.Prov_Code,d.city_code,
                                           d.order_status,d.ORDER_PRICE,
                                           d.channel_id,d.CREATE_DATE,
                                           d.ORDER_REMARK,uinfo.REALNAME,
                                           uinfo.CARDNUM,uinfo.username,
                                           dpay.PAY_TYPE,dpay.SEND_CUSNAME,
                                           dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
                                           de.ORDER_DETAIL_ID,
                                           de.OFFER_ID,de.OFFER_TITLE,
                                           de.OFFER_TYPE,de.PRICE,
                                           de.QTY,dc.CHOOSE_ID,
                                           dc.CHOOSE_TYPE,dc.CHOOSE_NAME
                                    from DEAL_ORDER d,
                                         DEAL_ORDER_DETAIL de,
                                         DEAL_ORDER_USER_CHOOSE dc
                                         DEAL_PAY_INFO dpay,
                                         USER_INFO uinfo
                                    where d.order_id=de.order_id               and
                                          de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and 
                                          d.PAY_ID=dpay.PAY_ID                 and
                                          d.USER_ID=uinfo.USERID and ';
                               
    
  --判断是否输入店铺ID
      --判断是否输入订单ID
      if V_ORDER_ID is not null then
        v_sql := v_sql || ' d.ORDER_ID like '||chr(39)||'%'|| V_ORDER_ID ||'%'||chr(39)||' and ';
      end if;
      --判断是否输入订单类型
      if V_ORDER_TYPE is not null then
        v_sql := v_sql || 'd.ORDER_TYPE=' || V_ORDER_TYPE || ' and ';
      end if;
      --判断是否输入省编码
      if V_prov_code is not null then
        v_sql := v_sql || 'd.Prov_Code=' ||chr(39)||V_prov_code ||chr(39)|| ' and ';
      end if;
      --判断是否输入市编码
      if V_city_code is not null then
        v_sql := v_sql || 'd.city_code=' || chr(39)||V_city_code ||chr(39)|| ' and ';
      end if;
      --判断是否输入订单状态
      if V_order_status is not null then
        v_sql := v_sql || 'd.order_status=' ||chr(39)|| V_order_status||chr(39)||' and ';
      end if;
      --判断是否输入渠道
      if V_channel_id is not null then
        v_sql := v_sql || 'd.channel_id=' ||chr(39)||V_channel_id||chr(39)|| ' and ';
      end if;
      --判断是否输入创建时间From
      if V_begin_date is not null then
        v_sql := v_sql || 'd.Create_Date >=  ' || V_begin_date || ' and ';
      end if;
      --判断是否输入创建时间To
      if V_end_date is not null then
        v_sql := v_sql || 'd.Create_Date <= ' || V_end_date || ' and ';
      end if;

     --and 1=1非常好，我之前是没有想到，我哪个招还要把最后的and用substr截掉，这个就不用了，很好，以为备记
      v_sql    := v_sql || 'and 1=1';
  open v_syscursor for v_sql;
 end if;
 
  V_ERR_MSG := V_PRO_NAME || '游标打开失败 ';
  PLOG.INFO(V_PRO_NAME, '过程结束,结束日期: ' || V_PAR_DATE);
  /*数据异常记录信息后回滚*/

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    V_ERR_CODE := SQLCODE;
    PLOG.ERROR(V_PRO_NAME, V_ERR_CODE, V_ERR_MSG || '  ' || SQLERRM);
end p_deal_order_complete_info;