oracle正则表达式regexp

insert into t_dc_order_center_detail (ACCEPT_DATE, CITY_CODE, CHANNEL_CODE, TRADE_ID, PAY_MODE, PAPAY_DEPAR, PAY_STATE, PAY_DATE, PAY_ORDERID, PAY_FEE, ORDER_FEE, ORDER_SOURCE, ADVANCE_PAY, SHIP_MODE, SHIPPING_ID, TRADE_STATE, TRADE_TYPE_CODE, PRODUCT_ID, PRODUCT_NAME, SERIAL_NUMBER, ACCOUT_ID, ACCOUT_NAME, TRADE_STAFF_ID, TRADE_STAFFNAME, DEVELOP_NAME, DEVELOP_ID, REMARK, USER_TYPE, CUST_NAME, PSPT_ID, INVALID_DATE, INPUT_DATE, ORDER_TYPE, RELATION_ORDERS, PROPT_CODE, PROP_VALUE, IMPORT_DATE, RESV1, RESV2, RESV3, RESV4, RESV5) select ACCEPT_DATE, CITY_CODE, CHANNEL_CODE, TRADE_ID, PAY_MODE, PAPAY_DEPAR, PAY_STATE, PAY_DATE, PAY_ORDERID, PAY_FEE, ORDER_FEE, ORDER_SOURCE, ADVANCE_PAY, SHIP_MODE, SHIPPING_ID, TRADE_STATE, TRADE_TYPE_CODE, PRODUCT_ID, PRODUCT_NAME, SERIAL_NUMBER, ACCOUT_ID, ACCOUT_NAME, TRADE_STAFF_ID, TRADE_STAFFNAME, DEVELOP_NAME, DEVELOP_ID, REMARK, USER_TYPE, CUST_NAME, PSPT_ID, INVALID_DATE, INPUT_DATE, ORDER_TYPE, RELATION_ORDERS, PROPT_CODE, PROP_VALUE, IMPORT_DATE, RESV1, RESV2, DECODE((SELECT DEVELOP_ID FROM DUAL WHERE REGEXP_LIKE(DEVELOP_ID,''''(WX|KF|DX|ZW) '''')),'',''''2'''',''''3'''' )) RESV3, RESV4, RESV5 from t_dc_order_center where ACCEPT_DATE=' || '''' || v_ddate || ''''; -- insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; commit; v_sql := 'INSERT INTO T_DC_CB_BS_TRADE_DETAIL ( TRADE_ID, SUBSCRIBE_ID, BPM_ID, TRADE_TYPE_CODE, IN_MODE_CODE, PRIORITY, SUBSCRIBE_STATE, NEXT_DEAL_TAG, PRODUCT_ID, BRAND_CODE, USER_ID, CUST_ID, USECUST_ID, ACCT_ID, USER_DIFF_CODE, NET_TYPE_CODE, SERIAL_NUMBER, CUST_NAME, ACCEPT_DATE, ACCEPT_MONTH, TRADE_STAFF_ID, TRADE_DEPART_ID, TRADE_CITY_CODE, TRADE_EPARCHY_CODE, TERM_IP, EPARCHY_CODE, CITY_CODE, OLCOM_TAG, EXEC_TIME, FINISH_DATE, OPER_FEE, FOREGIFT, ADVANCE_PAY, INVOICE_NO, FEE_STATE, FEE_TIME, FEE_STAFF_ID, CANCEL_TAG, CANCEL_DATE, CANCEL_STAFF_ID, CANCEL_DEPART_ID, CANCEL_CITY_CODE, CANCEL_EPARCHY_CODE, CHECK_TYPE_CODE, CHK_TAG, AUDIT_TAG, AUDIT_BATCH_NO, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_PHONE, ACTOR_CERTNUM, CONTACT, CONTACT_PHONE, CONTACT_ADDRESS, REMARK, IF_MAINTENANCE, ORDER_ID, SUB_ORDER_ID, MAIN_DISCNT_CODE, PRODUCT_SPEC, STANDARD_KIND_CODE, IMPORT_DATE, SYS_TYPE ) select TRADE_ID, SUBSCRIBE_ID, BPM_ID, TRADE_TYPE_CODE, IN_MODE_CODE, PRIORITY, SUBSCRIBE_STATE, NEXT_DEAL_TAG, PRODUCT_ID, BRAND_CODE, USER_ID, CUST_ID, USECUST_ID, ACCT_ID, USER_DIFF_CODE, NET_TYPE_CODE, SERIAL_NUMBER, CUST_NAME, ACCEPT_DATE, ACCEPT_MONTH, TRADE_STAFF_ID, TRADE_DEPART_ID, TRADE_CITY_CODE, TRADE_EPARCHY_CODE, TERM_IP, EPARCHY_CODE, CITY_CODE, OLCOM_TAG, EXEC_TIME, FINISH_DATE, OPER_FEE, FOREGIFT, ADVANCE_PAY, INVOICE_NO, FEE_STATE, FEE_TIME, FEE_STAFF_ID, CANCEL_TAG, CANCEL_DATE, CANCEL_STAFF_ID, CANCEL_DEPART_ID, CANCEL_CITY_CODE, CANCEL_EPARCHY_CODE, CHECK_TYPE_CODE, CHK_TAG, AUDIT_TAG, AUDIT_BATCH_NO, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_PHONE, ACTOR_CERTNUM, CONTACT, CONTACT_PHONE, CONTACT_ADDRESS, REMARK, IF_MAINTENANCE, ORDER_ID, SUB_ORDER_ID, MAIN_DISCNT_CODE, PRODUCT_SPEC, STANDARD_KIND_CODE, IMPORT_DATE, ''BSS'' FROM T_DC_BSS_TRADE where to_char(ACCEPT_DATE,''YYYYMMDD'')=' || '''' || v_ddate || ''''; --insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; v_sql := 'INSERT INTO t_dc_cb_bs_trade_detail (USER_ID, USER_DIFF_CODE, USECUST_ID, TRADE_TYPE_CODE, TRADE_STAFF_ID, TRADE_ID, TRADE_EPARCHY_CODE, TRADE_DEPART_ID, TRADE_CITY_CODE, TERM_IP, SUB_ORDER_ID, SUBSCRIBE_STATE, SUBSCRIBE_ID, STANDARD_KIND_CODE, SERIAL_NUMBER, REMARK, PROVINCE_CODE, PRODUCT_SPEC, PRODUCT_ID, PRIORITY, ORDER_ID, OPER_FEE, OLCOM_TAG, NEXT_DEAL_TAG, NET_TYPE_CODE, MAIN_DISCNT_CODE, IN_MODE_CODE, INVOICE_NO, IMPORT_DATE, IF_MAINTENANCE, FOREGIFT, FINISH_DATE, FEE_TIME, FEE_STATE, FEE_STAFF_ID, EXEC_TIME, CUST_NAME, CUST_ID, CONTACT_PHONE, CONTACT_ADDRESS, CONTACT, CHK_TAG, CHECK_TYPE_CODE, CANCEL_TAG, CANCEL_STAFF_ID, CANCEL_EPARCHY_CODE, CANCEL_DEPART_ID, CANCEL_DATE, CANCEL_CITY_CODE, BRAND_CODE, BPM_ID, AUDIT_TAG, AUDIT_BATCH_NO, ADVANCE_PAY, ACTOR_PHONE, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_CERTNUM, ACCT_ID, ACCEPT_MONTH, ACCEPT_DATE, SYS_TYPE ) SELECT USER_ID, USER_DIFF_CODE, USECUST_ID, TRADE_TYPE_CODE, TRADE_STAFF_ID, TRADE_ID, TRADE_EPARCHY_CODE, TRADE_DEPART_ID, TRADE_CITY_CODE, TERM_IP, SUB_ORDER_ID, SUBSCRIBE_STATE, SUBSCRIBE_ID, STANDARD_KIND_CODE, SERIAL_NUMBER, REMARK, PROVINCE_CODE, PRODUCT_SPEC, PRODUCT_ID, PRIORITY, ORDER_ID, OPER_FEE, OLCOM_TAG, NEXT_DEAL_TAG, NET_TYPE_CODE, MAIN_DISCNT_CODE, IN_MODE_CODE, INVOICE_NO, IMPORT_DATE, IF_MAINTENANCE, FOREGIFT, FINISH_DATE, FEE_TIME, FEE_STATE, FEE_STAFF_ID, to_date(EXEC_TIME,''YYYY-MM-DD HH24:MI:SS''), CUST_NAME, CUST_ID, CONTACT_PHONE, CONTACT_ADDRESS, CONTACT, CHK_TAG, CHECK_TYPE_CODE, CANCEL_TAG, CANCEL_STAFF_ID, CANCEL_EPARCHY_CODE, CANCEL_DEPART_ID, CANCEL_DATE, CANCEL_CITY_CODE, BRAND_CODE, BPM_ID, AUDIT_TAG, AUDIT_BATCH_NO, ADVANCE_PAY, ACTOR_PHONE, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_CERTNUM, ACCT_ID, ACCEPT_MONTH, ACCEPT_DATE, ''CBSS'' FROM t_dc_cbss_trade where to_char(ACCEPT_DATE,''YYYYMMDD'')=' || '''' || v_ddate || ''''; -- insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; commit; end; commit; result_code := '0000'; result_info := '存储过程执行完成'; exception when others then result_code := '9999'; result_info := '存储过程执行出错，错误如下:' || sqlerrm; dbms_output.put_line('存储过程执行出错，错误如下:' || sqlerrm); rollback; end;