oracle数字与字符的那点事

最近同事写了一个SQL如下

select crorg_unid               AS crorgUnid,
       crorg_uuid               AS crorgUuid,
       crorg_level_code         AS crorgLevelCode,
       crorg_parent_uuid        AS crorgParentUuid,
       crorg_full_name          AS crorgFullName,
       crorg_short_name         AS crorgShortName,
       crorg_num                AS crorgNum,
       crorg_cract_leader_uuid  AS crorgCractLeaderUuid,
       crorg_cract_admin_uuid   AS crorgCractAdminUuid,
       crorg_address            AS crorgAddress,
       crorg_phone              AS crorgPhone,
       crorg_type               AS crorgType,
       crorg_level              AS crorgLevel,
       crorg_status             AS crorgStatus,
       crorg_ord                AS crorgOrd,
       crorg_cdate              AS crorgCdate,
       crorg_udate              AS crorgUdate,
       crorg_cract_creater_uuid AS crorgCractCreaterUuid,
       crorg_memo               AS crorgMemo,
       crorg_pinyin             AS crorgPinyin,
       crorg_pp_nature          AS crorgPpNature
  from core_organization aa
  join core_account bb on bb.cract_crorg_uuid = aa.crorg_uuid
 where cract_uuid = '0C7B11AD887D4567AD25EEA79152812E'
union all
select dd.*
  from (select crorg_unid               AS crorgUnid,
               crorg_uuid               AS crorgUuid,
               crorg_level_code         AS crorgLevelCode,
               crorg_parent_uuid        AS crorgParentUuid,
               crorg_full_name          AS crorgFullName,
               crorg_short_name         AS crorgShortName,
               crorg_num                AS crorgNum,
               crorg_cract_leader_uuid  AS crorgCractLeaderUuid,
               crorg_cract_admin_uuid   AS crorgCractAdminUuid,
               crorg_address            AS crorgAddress,
               crorg_phone              AS crorgPhone,
               crorg_type               AS crorgType,
               crorg_level              AS crorgLevel,
               crorg_status             AS crorgStatus,
               crorg_ord                AS crorgOrd,
               crorg_cdate              AS crorgCdate,
               crorg_udate              AS crorgUdate,
               crorg_cract_creater_uuid AS crorgCractCreaterUuid,
               crorg_memo               AS crorgMemo,
               crorg_pinyin             AS crorgPinyin,
               crorg_pp_nature          AS crorgPpNature
          from core_organization
         where (select crorg_level_code
                  from core_organization aa
                  join core_account bb on bb.cract_crorg_uuid = aa.crorg_uuid
                 where cract_uuid = '0C7B11AD887D4567AD25EEA79152812E') like crorg_level_code || '%'
           and crorg_type = 1
         order by length(crorg_level_code) desc) dd
 where rownum < 2

虽然语句很复杂,但我今天要说的一个问题其实只与其中的一个字段有关系,所以SQL 不是重要的,就是那个crorg_type它在数据库的定义中是一个字符型,这里写成数字型,在有些情况下是可以的,就是当你数据库中的所有记录的crorg_type取值都为数字时,只要有一条记录取值不是数字都会出现非数字字符的异常,所以还是要严格按照数据类型来写SQL,不要说当前可行,就数字字符都无所谓,从出错来看,数据库内部应该是将数据库中的字符数据转换为数字进行比较,如果存在非数字型字符就没有办法转换,就会报错.后写所以以后写SQL还是严格按类型来,是字符就添加引号,不要因为是数字就不加引号