oracle存double报无效数字,数据库报“ORA-01722: 无效数字”错误的解决办法-Oracle

报错信息：ORA-01722: 无效数字

问题SQL:

SELECT A.*,

B.FUND_CODE AS FUND_CODE,

B.FUND_FULLNAME AS Fund_Fullname

FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B

WHERE A.ENTITY_ID=B.FUND_ID

/*

此处A.ENTITY_ID的类型为VARCHAR;

B.FUND_ID的类型为NUMBER;

两者类型不匹配所以报错。

*/

更改后正确SQL:

SELECT A.*,

B.FUND_CODE AS FUND_CODE,

B.FUND_FULLNAME AS Fund_Fullname

FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B

WHERE A.ENTITY_ID=TO_CHAR(B.FUND_ID)

/*

类型转换函数to_char()

*/

拓展：

将日期型转换为字符串TO_CHAR()

SELECT

TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS AM')

FROM DUAL

将数字型转换为字符串TO_CHAR()

SELECT

TO_CHAR(123.45678,'$99999.999')

FROM DUAL;

将字符换转换为日期TO_DATE()

SELECT

TO_DATE ('20100913', 'YYYY-MM-DD')

FROM DUAL;

将字符串转换为数字TO_NUMBER()

SELECT

TO_NUMBER('01')

FROM DUAL;