nvl函数的运用

jiawang · 发表于 2023-3-30 15:27:03

本帖最后由 jiawang 于 2023-3-30 15:45 编辑

NVL函数
作用：判断某个值是否为空值，若不为空值则输出，若为空值，返回指定值。

详细解释如下：
1、nvl()函数的格式如下：NVL(expr1,expr2);
2、含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例子：查询sys.user$表下面的password和spare4的值
select name,password,spare4 from sys.user$;

登录/注册后可看大图

66846425363d17967.png (62.48 KB, 下载次数: 241)

下载附件

2023-3-30 15:11 上传

可以看出有些用户password有值，spare4也有值

当我们想用户password没有值的时候保留spare4的值，可以使用nvl函数
SQL> select name, nvl(password,spare4) from sys.user$;

登录/注册后可看大图

713966425370cf0407.png (43.25 KB, 下载次数: 265)

下载附件

2023-3-30 15:15 上传

运行后，若password为空，则返回spare4的值，若password不为空值，则返回其自身。

注意：使用nvl函数的前提是查询到的结果不为空，就是说查询语句本身不能返回null，只有查询记录存在的情况下nvl函数才会对记录中的字段赋值，如果没有记录赋值则为空谈了！

批量生成创建用户的命令；
SQL> select ' alter user ' || NAME || ' identified by values ''' ||  nvl(b.password,b.spare4) || ''';'
  2  from sys.user$ b ,dba_users a
  3  where B.NAME = A.USERNAME;

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
alter user SYSTEM identified by values '2D594E86F93B17A1';
alter user SYS identified by values '8A8F025737A9097A';
alter user SYSBACKUP identified by values 'S:0000000000000000000000000000000000000000229CFFCF2E3971
1F43C6;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000016DCCDE28526BD82FA66481E84D24B8B';

alter user SYSRAC identified by values 'S:00000000000000000000000000000000000000001914850E4E50E1DD6
AF7;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000F679664D8CD59A1584F90F3539779851';

alter user OJVMSYS identified by values 'S:00000000000000000000000000000000000000001A989EF330631AC7

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
56D7;T:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000006CB0BA7F0D9B8E0560B68267636D5D63';

alter user SYSKM identified by values 'S:0000000000000000000000000000000000000000C0F585D26BC5CA0258
82;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000003444AB23942569D3CF1B135E5677FF26';

alter user OUTLN identified by values 'S:00000000000000000000000000000000000000000BE73A354C4081125B
9A;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000EF0236F490C9E04D932BE33489127045';

'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||NVL(B.PASSWORD,B.SPARE4)||''';'
----------------------------------------------------------------------------------------------------
alter user SYS$UMF identified by values 'S:00000000000000000000000000000000000000000D8C01CF538F2FA4
EEB7;T:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000F6BF6A5CB0FF7E4D0FF7CF3ACA55140D';

alter user SYSDG identified by values 'S:0000000000000000000000000000000000000000A723572098C0F47EB0
63;T:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000060B89ABC0E52011C36523825C2A6A385';

帐号		自动登录	找回密码
密码			注册

[PL/SQL] nvl函数的运用