oracle字符串操作

UPDATE ORDER
SET    INS_NENSHIKI = Substr(INS_NENSHIKI_NO, 1, 4)
                      || '年'
                      || To_number(Substr(INS_NENSHIKI_NO, 5, 2))
                      || '月～'
                      || Substr(INS_NENSHIKI_NO, 7, 4)
                      || '年'
                      || To_number(Substr (INS_NENSHIKI_NO, 11, 2))
                      || '月'
WHERE  INS_NENSHIKI IS NOT NULL
       AND INS_NENSHIKI != 'その他'
       AND Length(INS_NENSHIKI_NO) > 6;
commit;

主要是用了oracle的Substr函数和To_number函数，对字符串进行分割，之前用concat函数拼接，结果SQL运行的时候报错了，调查发现concat函数只能有两个参数，所以改用 || 进行字符串的拼接。

公司希望万一代码出错可以及时回滚，那就走一下整个流程吧：

/*备份：*/
CREATE TABLE ORDER_TEMP_TEMP AS SELECT * FROM ec_order;

/*修改：*/
UPDATE ORDER_TEMP
SET    INS_NENSHIKI = Substr(INS_NENSHIKI_NO, 1, 4)
                      || '年'
                      || To_number(Substr(INS_NENSHIKI_NO, 5, 2))
                      || '月～'
                      || Substr(INS_NENSHIKI_NO, 7, 4)
                      || '年'
                      || To_number(Substr (INS_NENSHIKI_NO, 11, 2))
                      || '月'
WHERE  INS_NENSHIKI IS NOT NULL
       AND INS_NENSHIKI != 'その他'
       AND Length(INS_NENSHIKI_NO) > 6;
commit;

/*回滚：*/
UPDATE ORDER_TEMP
SET    INS_NENSHIKI = (SELECT t1.INS_NENSHIKI AS NENSHIKI
                       FROM   ORDER_TEMP_TEMP t1
                       WHERE  t1.ORDER_ID = ORDER_TEMP.ORDER_ID
                              AND t1.INS_NENSHIKI IS NOT NULL
                              AND t1.INS_NENSHIKI != 'その他')
WHERE  EXISTS (SELECT 1
               FROM   ORDER_TEMP_TEMP t2
               WHERE  t2.ORDER_ID = ORDER_TEMP.ORDER_ID
                      AND t2.INS_NENSHIKI IS NOT NULL
                      AND t2.INS_NENSHIKI != 'その他');
commit;