oracle 10g 数据类型从Integer转换成number-CSDN博客

解决Oracle数据库中因类型转换导致查询结果异常的问题，涉及integer与number类型的比较差异。

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

上月28号收到一个开发同事报问题，他们在一个数据做查询的时候结果明明存在就是查不出来，数据库的版本是10.2.0.4。

select J.EMPLID,
       J.EFFDT,
       J.EFFSEQ,
       CLS.EFFDT,
       CLS.EFFSEQ,
       CLS.PAIC_EMP_CLASS,
       CLS.PAIC_EMP_CLASS1,
       CLS.PAIC_EMP_CLASS2
from ps_PAIC_JOB_BV j, ps_paic_emp_class cls
where j.empl_rcd = 0
   and j.emplid = cls.emplid
   and j.empl_rcd = cls.empl_rcd
   and j.effdt = cls.effdt
   and j.effseq = cls.effseq
   and j.business_unit = cls.business_unit
   and j.effdt = (select max(j1.effdt)
                    from ps_PAIC_JOB_BV j1
                   where j.emplid = j1.emplid
                     and j.empl_rcd = j1.empl_rcd
                     and j1.effdt <= sysdate)
   and j.effseq = (select max(j2.effseq)
                     from ps_PAIC_JOB_BV j2
                    where j.emplid = j2.emplid
                      and j.empl_rcd = j2.empl_rcd
                      and j.effdt = j2.effdt) /**/
   AND CLS.FLAG = 'Y'
   AND J.BUSINESS_UNIT = 'PA003'
   and cls.paic_emp_class = '0001'
-- and cls.paic_emp_class1 = '0001'
   --and cls.paic_emp_class2 = '0001' /* */
    AND J.EMPLID = '28300000942'

看到这个问题，首先的思路是先看看sql的逻辑，发现sql中主要是ps_PAIC_JOB_BV j和ps_paic_emp_class cls的链接以及j表的自链接，自链接应该不会有什么问题，那么问题应该出现在j和cls表链接的地方。对这两个表关联的地方逐个去测试发现j.empl_rcd = 0 and j.emplid = cls.emplid加上结果就不出来了，不加就有结果，但是从结果中看他们确实是有empl_rcd 字段都为0的数据存在。那问题就是j和cls表的这个字段类型不一样啦，去desc时傻眼了，发现字段类型是一样的，都是integer。把sql改造下to_char(j.empl_rcd) = '0' and to_char(j.emplid) = to_char(cls.emplid)，执行后结果出来了！汗了

到底怎么回事呢？把这两个表的这个值分别保存到不同的表中，再去看他们结果，发现问题了，请看下面两个结果虽然都是0，单它们的数据类型不一样。

SQL> create table test1 as select j.empl_rcd from ps_PAIC_JOB_BV j, ps_paic_emp_class cls
2   where to_char(j.empl_rcd) = '0'
3     --and j.EMPL_RCD=cls.empl_rcd
4     and to_char(j.empl_rcd) = to_char(cls.empl_rcd)
5     and j.emplid = cls.emplid
6     and j.effdt = cls.effdt
7     and j.effseq = cls.effseq
8     and j.business_unit = cls.business_unit
9     and j.effdt = (select max(j1.effdt)
10                      from ps_PAIC_JOB_BV j1
11                     where j.emplid = j1.emplid
12                       and j.empl_rcd = j1.empl_rcd
13                       and j1.effdt <= sysdate)
14     and j.effseq = (select max(j2.effseq)
15                       from ps_PAIC_JOB_BV j2
16                      where j.emplid = j2.emplid
17                        and j.empl_rcd = j2.empl_rcd
18                        and j.effdt = j2.effdt) /**/
19     AND CLS.FLAG = 'Y'
20     AND J.BUSINESS_UNIT = 'PA003'
21     and cls.paic_emp_class like '0001'
22     --and cls.paic_emp_class1 = '0001'
23     --and cls.paic_emp_class2 = '0001' /* */
24      AND J.EMPLID = '28300000942';

Table created

SQL>

Table created

SQL> select * from test1;

EMPL_RCD
----------
         0

SQL>
SQL> create table test2 as select cls.empl_rcd from ps_PAIC_JOB_BV j, ps_paic_emp_class cls
2   where to_char(j.empl_rcd) = '0'
3     --and j.EMPL_RCD=cls.empl_rcd
4     and to_char(j.empl_rcd) = to_char(cls.empl_rcd)
5     and j.emplid = cls.emplid
6     and j.effdt = cls.effdt
7     and j.effseq = cls.effseq
8     and j.business_unit = cls.business_unit
9     and j.effdt = (select max(j1.effdt)
10                      from ps_PAIC_JOB_BV j1
11                     where j.emplid = j1.emplid
12                       and j.empl_rcd = j1.empl_rcd
13                       and j1.effdt <= sysdate)
14     and j.effseq = (select max(j2.effseq)
15                       from ps_PAIC_JOB_BV j2
16                      where j.emplid = j2.emplid
17                        and j.empl_rcd = j2.empl_rcd
18                        and j.effdt = j2.effdt) /**/
19     AND CLS.FLAG = 'Y'
20     AND J.BUSINESS_UNIT = 'PA003'
21     and cls.paic_emp_class like '0001'
22     --and cls.paic_emp_class1 = '0001'
23     --and cls.paic_emp_class2 = '0001' /* */
24      AND J.EMPLID = '28300000942';

Table created

SQL> select * from test2;

                               EMPL_RCD
---------------------------------------
                                      0

SQL> select * from test1, test2 where test1.empl_rcd=test2.empl_rcd;       同样是empl_rcd列，查询结果列长度不一样，肯定是数据类型不一致导致的

EMPL_RCD                                EMPL_RCD
---------- ---------------------------------------
         0                                       0
下面说明的确不一样

SQL> desc test1
Name     Type   Nullable Default Comments
-------- ------ -------- ------- --------
EMPL_RCD NUMBER Y

SQL> desc test2
Name     Type    Nullable Default Comments
-------- ------- -------- ------- --------
EMPL_RCD INTEGER

SQL>

虽然他们数据类型不一样，但是值是一样的啊为什么结果不相等呢，难道那个变成number类型的0是浮点数，查了下length都是1，快崩溃了，到底是怎么回事呢，为什么j表的数据会desc显示为integer，但是实际值缺是number型的呢

再查了下j表，原来ps_PAIC_JOB_BV不是一张表而是一个视图，具体定义如下

CREATE OR REPLACE VIEW SYSADM.PS_PAIC_JOB_BV AS

SELECT

EMPLID, EMPL_RCD, EFFDT, EFFSEQ, PER_ORG, DEPTID, JOBCODE, POSITION_NBR, SUPERVISOR_ID, HR_STATUS, APPT_TYPE,

MAIN_APPT_NUM_JPN, POSITION_OVERRIDE, POSN_CHANGE_RECORD, EMPL_STATUS, ACTION, ACTION_DT, ACTION_REASON, LOCATION, TAX_LOCATION_CD,

JOB_ENTRY_DT, DEPT_ENTRY_DT, POSITION_ENTRY_DT, SHIFT, REG_TEMP, FULL_PART_TIME, COMPANY, PAYGROUP, BAS_GROUP_ID, ELIG_CONFIG1,

ELIG_CONFIG2, ELIG_CONFIG3, ELIG_CONFIG4, ELIG_CONFIG5, ELIG_CONFIG6, ELIG_CONFIG7, ELIG_CONFIG8, ELIG_CONFIG9, BEN_STATUS, BAS_ACTION,

COBRA_ACTION, EMPL_TYPE, HOLIDAY_SCHEDULE, STD_HOURS, STD_HRS_FREQUENCY, OFFICER_CD, EMPL_CLASS, SAL_ADMIN_PLAN, GRADE,

GRADE_ENTRY_DT, STEP, STEP_ENTRY_DT, GL_PAY_TYPE, ACCT_CD, EARNS_DIST_TYPE, COMP_FREQUENCY, COMPRATE, CHANGE_AMT, CHANGE_PCT,

ANNUAL_RT, MONTHLY_RT, DAILY_RT, HOURLY_RT, ANNL_BENEF_BASE_RT, SHIFT_RT, SHIFT_FACTOR, CURRENCY_CD, BUSINESS_UNIT, SETID_DEPT,

SETID_JOBCODE, SETID_LOCATION, SETID_SALARY, REG_REGION, DIRECTLY_TIPPED, FLSA_STATUS, EEO_CLASS, FUNCTION_CD, TARIFF_GER,

TARIFF_AREA_GER, PERFORM_GROUP_GER, LABOR_TYPE_GER, SPK_COMM_ID_GER, HOURLY_RT_FRA, ACCDNT_CD_FRA, VALUE_1_FRA, VALUE_2_FRA, VALUE_3_FRA,

VALUE_4_FRA, VALUE_5_FRA, CTG_RATE, PAID_HOURS, PAID_FTE, PAID_HRS_FREQUENCY, UNION_FULL_PART, UNION_POS, MATRICULA_NBR,

SOC_SEC_RISK_CODE, UNION_FEE_AMOUNT, UNION_FEE_START_DT, UNION_FEE_END_DT, EXEMPT_JOB_LBR, EXEMPT_HOURS_MONTH,

WRKS_CNCL_FUNCTION, INTERCTR_WRKS_CNCL, CURRENCY_CD1, PAY_UNION_FEE, UNION_CD, BARG_UNIT, UNION_SENIORITY_DT, ENTRY_DATE, LABOR_AGREEMENT,

EMPL_CTG, EMPL_CTG_L1, EMPL_CTG_L2, SETID_LBR_AGRMNT, WPP_STOP_FLAG, LABOR_FACILITY_ID, LBR_FAC_ENTRY_DT, LAYOFF_EXEMPT_FLAG,

LAYOFF_EXEMPT_RSN, GP_PAYGROUP, GP_DFLT_ELIG_GRP, GP_ELIG_GRP, GP_DFLT_CURRTTYP, CUR_RT_TYPE, GP_DFLT_EXRTDT, GP_ASOF_DT_EXG_RT,

ADDS_TO_FTE_ACTUAL, CLASS_INDC, ENCUMB_OVERRIDE, FICA_STATUS_EE, FTE, PRORATE_CNT_AMT, PAY_SYSTEM_FLG, BORDER_WALKER, LUMP_SUM_PAY,

CONTRACT_NUM, JOB_INDICATOR, WRKS_CNCL_ROLE_CHE, BENEFIT_SYSTEM, WORK_DAY_HOURS, REPORTS_TO, FORCE_PUBLISH, JOB_DATA_SRC_CD, ESTABID,

SUPV_LVL_ID, SETID_SUPV_LVL, ABSENCE_SYSTEM_CD, POI_TYPE, HIRE_DT, LAST_HIRE_DT, TERMINATION_DT, ASGN_START_DT, LST_ASGN_START_DT,

ASGN_END_DT, LDW_OVR, LAST_DATE_WORKED, EXPECTED_RETURN_DT, EXPECTED_END_DATE, AUTO_END_FLG, LASTUPDDTTM, LASTUPDOPRID,

PAIC_DOC_NO, EP_WEIGHT, HRS_ROW_ADD_DTTM, HRS_ROW_ADD_OPRID, HRS_ROW_UPD_DTTM, HRS_ROW_UPD_OPRID

FROM PS_JOB WHERE BUSINESS_UNIT <> 'PA001' AND BUSINESS_UNIT <> 'PA011'

UNION