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 allselect 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还是严格按类型来,是字符就添加引号,不要因为是数字就不加引号