在下面的PLSQL触发器中，为什么将所有内容都在数据透视代码中转换为AS VARCHAR2？

debugcn 发表于 Dev

咖啡因

我正在尝试把这个触发器包住头。我在这里正在研究触发代码：

 INSERT INTO ONLINE_PROFILE_HISTORY.ONLINE_ACCOUNT_AVH
      SELECT ONLINE_PROFILE_HISTORY.ONLINE_ACCOUNT_AVH_SEQ.NEXTVAL
                 ,tmpVar
                 ,O.ATTR_NM
                 ,O.ATTR_OLD_VLU
                 ,CAST(NULL AS CHAR) AS ATTR_NEW_VLU 
    FROM (SELECT :OLD.ONL_ACCT_ID 
                           ,CAST(:OLD.ONL_ACCT_INTL_ID AS VARCHAR2(1024)) ONL_ACCT_INTL_ID
                           ,CAST(:OLD.ONL_ACCT_EXT_ID AS VARCHAR2(1024)) AS ONL_ACCT_EXT_ID
                           ,CAST(:OLD.LCKED_TS AS VARCHAR2(1024)) AS LCKED_TS
                           ,CAST(:OLD.DISABLED_TS AS VARCHAR2(1024)) AS DISABLED_TS
                           ,CAST(:OLD.LST_SCSFL_LOGIN_TS AS VARCHAR2(1024)) AS LST_SCSFL_LOGIN_TS
                          /* etc etc*/ 
                           ,CAST(:OLD.VLDT_SCRT_QUES_FAILURE_CNT AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_FAILURE_CNT
                           ,CAST(:OLD.VLDT_SCRT_QUES_LST_FAILURE_TS AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_LST_FAILURE_TS
                           ,CAST(:OLD.VLDT_SCRT_QUES_SUCCESS_TS AS VARCHAR2(1024)) AS VLDT_SCRT_QUES_SUCCESS_TS
                           ,CAST(:OLD.DLTD_TS AS VARCHAR2(1024)) AS DLTD_TS
                           ,CAST(:OLD.TRMS_CONDS_ACPTED_HOST_ADDR_NM AS VARCHAR2(1024)) AS TRMS_CONDS_ACPTED_HOST_ADDR_NM
                  FROM dual)
               UNPIVOT (ATTR_OLD_VLU FOR
                      ATTR_NM IN (
                      ONL_ACCT_INTL_ID
                      ,ONL_ACCT_EXT_ID
                      ,LCKED_TS
                      ,DISABLED_TS
                      ,VLDT_TOKEN_VLU
                      ,VLDT_TOKEN_KEY_NM
                      ,VLDTD_TS
                      ,VLDT_TOKEN_CREATE_TS
                      ,PRIM_BILLING_ACCT_SRC_ID
                      ,PRIM_BILLING_ACCT_SRC_ID_2
                      ,PRIM_BILLING_ACCT_SRC_SYS_CD
                      ,TRMS_CONDS_VER_NUM
                      ,TRMS_CONDS_ACPTED_TS
                      ,PSWD_FMT_NM
                      ,VLDT_SCRT_QUES_FAILURE_CNT
                      ,VLDT_SCRT_QUES_LST_FAILURE_TS
                      ,VLDT_SCRT_QUES_SUCCESS_TS
                      ,DLTD_TS
                      ,TRMS_CONDS_ACPTED_HOST_ADDR_NM
                     ))  O;

为什么我们正在做

 CAST(:OLD.VLDTD_TS AS VARCHAR2(1024)) AS VLDTD_TS

在那里吗？我通常了解枢轴是如何工作的，但不确定为什么每一列都像这样进行转换。

诺埃尔

UNPIVOT将跨几列的数据转换为单列。如果这些列的数据类型不同，将导致错误。

ORA-01790: expression must have same datatype as corresponding expression

SQL小提琴

create table mytable(
    id_ number,
    col1_ varchar2(10),
    col2_ number,
    col3_ date
);

insert into mytable values(1, 'asd',32,date'2014-03-04');
insert into mytable values(2, 'qwe',16,date'2014-02-11');

select *
from mytable
unpivot(val_ for col_name in (
  col1_,col2_,col3_)
        );

ORA-01790: expression must have same datatype as corresponding expression : select * from mytable unpivot(val_ for col_name in ( col1_,col2_,col3_) )

这就是为什么在取消透视之前应将所有列转换为相同的数据类型的原因。

SQL小提琴

select *
from (
  select
  id_,
  col1_,
  to_char(col2_) col2_,
  to_char(col3_,'dd-mm-yyyy') col3_
  from mytable
  )
unpivot(
  val_ for col_name_ in (
    col1_, col2_, col3_
    )
  )

结果：

| ID_ | COL_NAME_ |       VAL_ |
|-----|-----------|------------|
|   1 |     COL1_ |        asd |
|   1 |     COL2_ |         32 |
|   1 |     COL3_ | 04-03-2014 |
|   2 |     COL1_ |        qwe |
|   2 |     COL2_ |         16 |
|   2 |     COL3_ | 11-02-2014 |

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。