Oracle中模拟SQL中的isnull函数

旧城等待， 2023-10-08 09:03 13阅读 0赞

程序从MS SQL移植到ORACLE，面临大面积的SQL语句修改，其中用的最多的莫非isnull,虽然oracle中有nvl ,nullif, is null等函数，但却没有isnull。自己写一个吧，但是因为类似ISNULL(),NVL()的函数入参和返回值的数据类型都并不确定，要如何定义类型？姑且用varchar2吧：请看下面测试代码

--创建isnull函数
    create or replace function isnull(i_obj in varchar2, i_obj2 in varchar2)
    return varchar2
    is
    begin
      return nvl(i_obj, i_obj2);
    end isnull;
    
    --测试建表语句
    CREATE TABLE tUsers(
         UserName VARCHAR2(10),
              AGE NUMBER,
            stime date
     ); 
     
    --插入测试数据 
     INSERT INTO tUsers(UserName,age) VALUES(null,22);
     INSERT INTO tUsers(UserName,age,stime)VALUES('你好123',18,SYSDATE);
     INSERT INTO tUsers(UserName,age) VALUES(null,26);
     INSERT INTO tUsers(UserName,age,stime)VALUES('好123',38,SYSDATE);
     INSERT INTO tUsers(UserName,age) VALUES(null,24);
     INSERT INTO tUsers(UserName,age,stime)VALUES('你123',28,SYSDATE);
     
     --查看建的表结构
     SELECT table_name, column_name, data_type,DATA_LENGTH,COLUMN_ID FROM USER_TAB_COLUMNS WHERE table_name =upper('tusers')
    --查看测试数据
    select * from tUsers
     
     --测试语句
     select * from tUsers where isnull(age,0)=0  --查询所有age字段为null的记录，成功!
     select username,isnull(age,0),stime from tUsers  --如果age字段为Null则返回number型0，成功!
     select username,isnull(age,'未知年纪'),stime from tUsers  --如果age字段为Null则返回VARCHAR型'未知年纪',成功!
     select username,age,isnull(stime,'未知时间') from tUsers  --如果stime字段为Null则返回VARCHAR型'未知时间',成功!
     --如果stime字段为Null则返回VARCHAR型'未知时间',否则返回格式化后的stime, 成功!
     select username,age,case when isnull(stime,' ')=' ' then '未知时间' else to_char(stime,'yyyy-mm-dd hh24:mi:ss') end as sstime from tUsers 
     --如果stime字段为Null则返回VARCHAR型'未知时间',否则返回stime,不成功!
     select username,age,case when isnull(stime,' ')=' ' then '未知时间' else stime end as sstime from tUsers  
     select username,age,nvl(stime,'未知时间') from tUsers  --NVL判断如果stime字段为Null则返回VARCHAR型'未知时间',不成功!
    
    --删除测试表和数据
    DROP TABLE tusers;

通过测试数据可以看出，自定义isnull函数入参定义为varchar2类型， 基本常用的数据类型都可以实现隐式转换，其中包括varchar,varchar2,nvarchar2,number，date等。