这个存储过程写的好！

create or replace package body pkg_bba_rcv_pre is
  function lf_get_value(p_xml in xmltype, p_string in varchar2)
    return varchar2 is
  begin
    return p_xml.extract(p_string).getstringval();
  exception
    when others then
      return null;
  end;
  --收货预测处理类
  procedure pre_route(p_xml_param  in clob, --所有参数集合在xml中
                      p_out_cursor out sys_refcursor, --返回游标
                      p_out_number in out number, --返回数字型
                      p_out_string in out varchar2, --返回字符串
                      p_out_xml    out clob, --返回xml数据
                      p_status     out varchar2, --执行状态 OK/FAILE
                      p_message    out varchar2 --执行信息
                      ) is
  
    l_xml_param xmltype;
    l_function  varchar2(500);
    l_op_by     varchar2(100);
  
    p_pre_id               varchar2(255); -- 预测ID
    p_rcv_type             varchar2(255); -- 收货类型:(Local / Import)
    p_supplier_name        varchar2(255); -- 供应商名称
    p_po_number            varchar2(255); -- 订单号
    p_invoice              varchar2(255); -- 发票
    p_arrived_date         varchar2(255); -- 到达时间
    p_estimated_date       varchar2(255); -- 预计车辆达到时间
    p_item_process         varchar2(1000);
    p_boxes_qty            varchar2(255); -- 箱子数量
    p_trucks_qty           varchar2(255); -- 车辆数量
    p_state                varchar2(255); -- 状态
    p_remark               varchar2(255); -- 对外型号
    p_deliver_by           varchar2(255); -- 送货人
    p_deliver_date         varchar2(255); -- 送货时间
    p_estimated_date_begin varchar2(255); -- 时间
    p_estimated_date_end   varchar2(255); -- 时间
    p_asn_no               varchar2(255); -- ASN号
    p_asn_state            varchar2(255); -- ASN状态
    p_reg_flag             varchar2(255);
    v_sql                  varchar2(2000);
    v_condition            varchar2(2000);
  
  begin
    p_status     := 'FAIL';
    p_message    := 'Unknown error!';
    p_out_number := -1;
    p_out_string := 'X';
    p_out_xml    := 'X';
    open p_out_cursor for
      select null from dual where 1 = 0;
  
    l_xml_param := xmltype.createxml(p_xml_param);
    l_op_by     := lf_get_value(l_xml_param, '/ROOT/OP_BY/text()');
    l_function  := lf_get_value(l_xml_param, '/ROOT/FUNCTION/text()');
    case l_function
      when 'PRE_SELECT_INFO' then
        /*XML_PARAM 格式要求：
        <ROOT>
        <FUNCTION>PRE_SELECT_INFO</FUNCTION>
        <OP_BY>实际用户名</OP_BY>
        <PARAM>
        <P_PRE_ID>P_PRE_ID</P_PRE_ID>
        <P_INVOICE>P_INVOICE</P_INVOICE>
        <P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
        <P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
        <P_ESTIMATED_DATE_BEGIN>P_ESTIMATED_DATE_BEGIN</P_ESTIMATED_DATE_BEGIN>
        <P_ESTIMATED_DATE_END>P_ESTIMATED_DATE_END</P_ESTIMATED_DATE_END>
        <p_ASN_NO>10000<p_ASN_NO>
        <p_ASN_STATE>1<p_ASN_STATE>
        <P_REG_FLAG>Y<P_REG_FLAG>
        </PARAM>
        </ROOT>
        */
        begin
          p_pre_id               := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_PRE_ID/text()');
          p_invoice              := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_INVOICE/text()');
          p_supplier_name        := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_SUPPLIER_NAME/text()');
          p_rcv_type             := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_RCV_TYPE/text()');
          p_po_number            := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_PO_NUMBER/text()');
          p_estimated_date_begin := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_ESTIMATED_DATE_BEGIN/text()');
          p_estimated_date_end   := lf_get_value(l_xml_param,
                                                 '/ROOT/PARAM/P_ESTIMATED_DATE_END/text()');
        
          p_asn_no    := lf_get_value(l_xml_param,
                                      '/ROOT/PARAM/P_ASN_NO/text()');
          p_asn_state := lf_get_value(l_xml_param,
                                      '/ROOT/PARAM/P_ASN_STATE/text()');
          p_reg_flag  := lf_get_value(l_xml_param,
                                      '/ROOT/PARAM/P_REG_FLAG/text()');
        
          v_sql := 'SELECT pre_id, rcv_type, supplier_name, po_number, invoice, estimated_date, boxes_qty,ITEM_PROCESS, trucks_qty, state, arrived_date, remark, create_by, create_date, last_update_by, last_update_date, deliver_by,asn_no,asn_state
,reg_flag,deliver_date FROM lmes_bba_rcv_reg_pre where 1=1 ';
          if p_pre_id is not null then
            v_condition := v_condition || ' and pre_id=' || p_pre_id;
          end if;
          if p_invoice is not null then
            v_condition := v_condition || ' and invoice=''' || p_invoice || '''';
          end if;
          if p_supplier_name is not null then
            v_condition := v_condition || ' and supplier_name like ''' ||
                           p_supplier_name || '''';
          end if;
        
          if p_rcv_type is not null then
            v_condition := v_condition || ' and rcv_type=''' || p_rcv_type || '''';
          end if;
          if p_po_number is not null then
            v_condition := v_condition || ' and po_number=''' ||
                           p_po_number || '''';
          end if;
          if p_estimated_date_begin is not null then
            v_condition := v_condition ||
                           ' and ESTIMATED_DATE >= to_date(''' ||
                           p_estimated_date_begin || ''',''mm/dd/yyyy'')';
          end if;
        
          if p_estimated_date_end is not null then
            v_condition := v_condition ||
                           ' and ESTIMATED_DATE <= to_date(''' ||
                           p_estimated_date_end || ''',''mm/dd/yyyy'') ';
          end if;
        
          if p_asn_no is not null then
            v_condition := v_condition || ' and asn_no=' || p_asn_no;
          end if;
        
          if p_asn_state is not null then
            v_condition := v_condition || ' and asn_state=' || p_asn_state;
          end if;
        
          if p_reg_flag is not null then
            v_condition := v_condition || ' and reg_flag=''' || p_reg_flag || '''';
          end if;
        
          v_sql := v_sql || v_condition;
        
          open p_out_cursor for v_sql;
        
          p_status  := 'OK';
          p_message := 'Success';
        exception
          when others then
            begin
              p_status  := 'FAIL';
              p_message := substr(sqlcode || sqlerrm, 1, 200);
            end;
        end;
      
    /*XML_PARAM 格式要求：
    <ROOT>
    <FUNCTION>PRE_INSERT_INFO</FUNCTION>
    <OP_BY>实际用户名</OP_BY>
    <PARAM>
    <P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
    <P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
    <P_PO_NUMBER>P_PO_NUMBER</P_PO_NUMBER>
    <P_INVOICE>P_INVOICE</P_INVOICE>
    <P_ESTIMATED_DATE>P_ESTIMATED_DATE</P_ESTIMATED_DATE>
    <P_BOXES_QTY>P_BOXES_QTY</P_BOXES_QTY>
    <P_TRUCKS_QTY>P_TRUCKS_QTY</P_TRUCKS_QTY>
    <P_STATE>P_STATE</P_STATE>
    <P_REMARK>P_REMARK</P_REMARK>
    <P_DELIVER_BY>P_DELIVER_BY</P_DELIVER_BY>
    <P_DELIVER_DATE>P_DELIVER_DATE</P_DELIVER_DATE>
    </PARAM>
    </ROOT>
    */
      when 'PRE_INSERT_INFO' then
      
        begin
        
          p_rcv_type       := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_RCV_TYPE/text()');
          p_supplier_name  := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_SUPPLIER_NAME/text()');
          p_po_number      := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_PO_NUMBER/text()');
          p_invoice        := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_INVOICE/text()');
          p_estimated_date := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_ESTIMATED_DATE/text()');
          p_boxes_qty      := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_BOXES_QTY/text()');
          p_trucks_qty     := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_TRUCKS_QTY/text()');
          p_state          := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_STATE/text()');
          p_remark         := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_REMARK/text()');
          p_deliver_by     := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_DELIVER_BY/text()');
          p_deliver_date   := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_DELIVER_DATE/text()');
          p_item_process   := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_ITEM_PROCESS/text()');
          insert into lmes_bba_rcv_reg_pre
            (pre_id,
             rcv_type,
             supplier_name,
             po_number,
             invoice,
             estimated_date,
             boxes_qty,
             item_process,
             trucks_qty,
             state,
             arrived_date,
             remark,
             create_by,
             create_date,
             last_update_by,
             last_update_date,
             deliver_by,
             deliver_date)
          values
            (lmes_bba_rcv_reg_pre_s.nextval,
             p_rcv_type,
             p_supplier_name,
             p_po_number,
             p_invoice,
             to_date(p_estimated_date, 'mm/dd/yyyy hh24:mi:ss'),
             p_boxes_qty,
             p_item_process,
             p_trucks_qty,
             p_state,
             null,
             p_remark,
             l_op_by,
             sysdate,
             '',
             null,
             p_deliver_by,
             to_date(p_deliver_date, 'mm/dd/yyyy hh24:mi:ss'));
          commit; --提交
          p_status  := 'OK';
          p_message := 'Success';
        exception
          when others then
            begin
              rollback; --回滚
              p_status  := 'FAIL';
              p_message := substr(sqlcode || sqlerrm, 1, 200);
            end;
        end;
      when 'PRE_UPDATE_ARRIVED' then
        /* --XML_PARAM 格式要求：
        <ROOT>
        <FUNCTION>PRE_UPDATE_ARRIVED</FUNCTION>
        <OP_BY>实际用户名</OP_BY>
        <PARAM>
        <P_PRE_ID>P_PRE_ID</P_PRE_ID>
        <P_STATE>P_STATE</P_STATE>
        </PARAM>
        </ROOT>
        */
        begin
          p_pre_id := lf_get_value(l_xml_param,
                                   '/ROOT/PARAM/P_PRE_ID/text()');
          p_state  := lf_get_value(l_xml_param,
                                   '/ROOT/PARAM/P_STATE/text()');
          /*
          UPDATE LMES_BBA_RCV_REG_PRE
          SET STATE = P_STATE,
          ARRIVED_DATE = SYSDATE,
          LAST_UPDATE_BY = L_OP_BY,
          LAST_UPDATE_DATE = SYSDATE
          WHERE PRE_ID IN F_SPLIT(P_PRE_ID,',') ;*/
        
          if p_pre_id is null then
            p_status  := 'FAIL';
            p_message := 'PRE_ID can be not null';
            return;
          end if;
          v_sql := ' UPDATE LMES_BBA_RCV_REG_PRE SET STATE = ''' || p_state ||
                   ''', ARRIVED_DATE = SYSDATE, LAST_UPDATE_BY =''' ||
                   l_op_by ||
                   ''', LAST_UPDATE_DATE = SYSDATE WHERE NVL(state,'' '')<> ''ARRIVED'' and PRE_ID IN (' ||
                   p_pre_id || ')';
        
          execute immediate v_sql;
          commit; --提交
          p_status  := 'OK';
          p_message := 'Success';
        exception
          when others then
            begin
              rollback; --回滚
              p_status  := 'FAIL';
              p_message := substr(sqlcode || sqlerrm, 1, 200);
            end;
        end;
      
      when 'PRE_UPDATE_INFO' then
        /* --XML_PARAM 格式要求：
        <ROOT>
        <FUNCTION>PRE_UPDATE_INFO</FUNCTION>
        <OP_BY>实际用户名</OP_BY>
        <PARAM>
        <P_PRE_ID>P_PRE_ID</P_PRE_ID>
        <P_RCV_TYPE>P_RCV_TYPE</P_RCV_TYPE>
        <P_SUPPLIER_NAME>P_SUPPLIER_NAME</P_SUPPLIER_NAME>
        <P_PO_NUMBER>P_PO_NUMBER</P_PO_NUMBER>
        <P_INVOICE>P_INVOICE</P_INVOICE>
        <P_ESTIMATED_DATE>P_ESTIMATED_DATE</P_ESTIMATED_DATE>
        <P_BOXES_QTY>P_BOXES_QTY</P_BOXES_QTY>
        <P_TRUCKS_QTY>P_TRUCKS_QTY</P_TRUCKS_QTY>
        <P_ARRIVED_DATE>P_ARRIVED_DATE</P_ARRIVED_DATE>
        <P_STATE>P_STATE</P_STATE>
        <P_REMARK>P_REMARK</P_REMARK>
        </PARAM>
        </ROOT>
        */
        begin
          p_pre_id         := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_PRE_ID/text()');
          p_rcv_type       := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_RCV_TYPE/text()');
          p_supplier_name  := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_SUPPLIER_NAME/text()');
          p_po_number      := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_PO_NUMBER/text()');
          p_invoice        := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_INVOICE/text()');
          p_estimated_date := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_ESTIMATED_DATE/text()');
          p_boxes_qty      := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_BOXES_QTY/text()');
          p_trucks_qty     := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_TRUCKS_QTY/text()');
          p_item_process   := lf_get_value(l_xml_param,
                                           '/ROOT/PARAM/P_ITEM_PROCESS/text()');
          /*P_ARRIVED_DATE := LF_GET_VALUE(L_XML_PARAM,
          '/ROOT/PARAM/P_ARRIVED_DATE/text()');*/
          p_state  := lf_get_value(l_xml_param,
                                   '/ROOT/PARAM/P_STATE/text()');
          p_remark := lf_get_value(l_xml_param,
                                   '/ROOT/PARAM/P_REMARK/text()');
          update lmes_bba_rcv_reg_pre
             set pre_id         = p_pre_id,
                 rcv_type       = p_rcv_type,
                 supplier_name  = p_supplier_name,
                 po_number      = p_po_number,
                 invoice        = p_invoice,
                 estimated_date = to_date(p_estimated_date,
                                          'mm/dd/yyyy hh24:mi:ss'),
                 boxes_qty      = p_boxes_qty,
                 item_process   = p_item_process,
                 trucks_qty     = p_trucks_qty,
                 state          = p_state,
                 --ARRIVED_DATE = P_ARRIVED_DATE,
                 remark           = p_remark,
                 last_update_by   = l_op_by,
                 last_update_date = sysdate
           where pre_id = p_pre_id;
          commit; --提交
          p_status  := 'OK';
          p_message := 'Success';
        exception
          when others then
            begin
              rollback; --回滚
              p_status  := 'FAIL';
              p_message := substr(sqlcode || sqlerrm, 1, 200);
            end;
        end;
      when 'PRE_DELETE_INFO' then
        /* --XML_PARAM 格式要求：
        <ROOT>
        <FUNCTION>PRE_DELETE_INFO</FUNCTION>
        <OP_BY>实际用户名</OP_BY>
        <PARAM>
        <P_PRE_ID>P_PRE_ID</P_PRE_ID>
        </PARAM>
        </ROOT>
        */
      
        begin
          p_pre_id := lf_get_value(l_xml_param,
                                   '/ROOT/PARAM/P_PRE_ID/text()');
          delete lmes_bba_rcv_reg_pre where pre_id = p_pre_id;
          commit; --提交
          p_status  := 'OK';
          p_message := 'Success';
        exception
          when others then
            begin
              rollback; --回滚
              p_status  := 'FAIL';
              p_message := substr(sqlcode || sqlerrm, 1, 200);
            end;
        end;
      else
        p_message := 'Undefined FUNCTION:' || l_function;
    end case;
  end;
end pkg_bba_rcv_pre;