VARCHAR2转换TIMESTAMP

最新推荐文章于 2023-03-23 11:44:20 发布

Joycesjr

最新推荐文章于 2023-03-23 11:44:20 发布

阅读量5.6k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/ljsjr/article/details/8833211

在编写存储过程时，遇到VARCHAR2类型字段与TIMESTAMP类型字段不匹配导致的错误。本文分享了进行数据类型转换的测试及解决过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天写存储过程的时候，遇到一期表和二期表几个字段是数据类型不一致而导致的错误。

下面是转换测试的结果：

---------------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select  sysdate from dual;

SYSDATE
--------------
06-1月 -13

 

SQL> select to_timestamp('2013-01-06 12:00:00.1','yyyy-mm-dd hh24:mi:ss.ff') as aa from dual;

AA
---------------------------------------------------------------------------
06-1月 -13 12.00.00.100000000 下午

 

SQL> select to_timestamp_tz('2013-01-06 12:00:00.1','yyyy-mm-dd hh24:mi:ss.ff') as aa from dual;

AA
---------------------------------------------------------------------------
06-1月 -13 12.00.00.100000000 下午 +08:00

 

-------------------------------------------------------------------------------

 MERGE INTO DEAL_CHARGE_RETURN TT                                           
  USING DEAL_RECHARGE_RESPONSE_TMP TMP                                       
  ON (TT.ID = TMP.ID)                                                        
  WHEN MATCHED THEN                                                          
    UPDATE                                                                   
       SET                         
           TT.TRANSACTIONID = TMP.TRANSACTIONID,                            
           TT.REQTIME         = TO_TIMESTAMP(TMP.REQTIME,'yyyy-mm-dd hh24:mi:ss.ff'),                                 
           TT.CHARGE_ORDER_ID = TMP.C0_ORDERID,                              
           TT.C1_SERNUM       = TMP.C1_SERNUM,                               
           TT.C2_CODE         = TMP.C2_CODE,                                 
           TT.C3_INORDERNO    = TMP.C3_INORDERNO,                            
           TT.MOBILE          = TMP.C4_MOBILE,                               
           TT.C5_TXNAMT       = TMP.C5_TXNAMT,                               
           TT.RESULT          = TMP.C6_RETURNCODE,                           
           TT.RES_CODE        = TMP.C7_RESPONSECODE,                         
           TT.HMAC            = TMP.HMAC,                                    
           TT.CTIME           = TO_TIMESTAMP(TMP.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss.ff'),                            
           TT.STATUS          = TMP.REMARK,                                  
           TT.REQ_XML         = TMP.REMARK2,                                 
           TT.RES_TEXTINFO    = TMP.REMARK3                                                                                                       
  WHEN NOT MATCHED THEN                                                      
    INSERT                                                                   
      (TT.ID,                                                                
       TT.TRANSACTIONID,                                                     
       TT.REQTIME,                                                           
       TT.CHARGE_ORDER_ID,                                                   
       TT.C1_SERNUM,                                                         
       TT.C2_CODE,                                                           
       TT.C3_INORDERNO,                                                      
       TT.MOBILE,                                                            
       TT.C5_TXNAMT,                                                         
       TT.RESULT,                                                            
       TT.RES_CODE,                                                          
       TT.HMAC,                                                              
       TT.CTIME,                                                             
       TT.STATUS,                                                            
       TT.REQ_XML,                                                           
       TT.RES_TEXTINFO)                                                      
    VALUES                                                                   
      (TMP.ID,                                                               
       TMP.TRANSACTIONID,                                                    
       TO_TIMESTAMP(TMP.REQTIME,'yyyy-mm-dd hh24:mi:ss.ff'),                                                          
       TMP.C0_ORDERID,                                                       
       TMP.C1_SERNUM,                                                        
       TMP.C2_CODE,                                                          
       TMP.C3_INORDERNO,                                                     
       TMP.C4_MOBILE,                                                        
       TMP.C5_TXNAMT,                                                        
       TMP.C6_RETURNCODE,                                                    
       TMP.C7_RESPONSECODE,                                                  
       TMP.HMAC,                                                             
       TO_TIMESTAMP(TMP.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss.ff'),                                                      
       TMP.REMARK,                                                           
       TMP.REMARK2,                                                          
       TMP.REMARK3);