第2章 sql查询、sql函数与数据库对象

5、其它函数。

（1）空值替换函数。

格式：NVL(字段或变量，值)

作用：某个字段或变量的取值如果为null，可用该函数将null值替换成指定的值。 比如：如果level字段的值是null，则将其替换为0 SQL>select item , NVL(level,0) from tiemfile;

（2）NVL2函数。

格式：NVL2（表达式1，表达式2，表达式3）

作用：如果表达式1的值不是null，则返回表达式2的值，否则，返回表达式3的值。 比如：如果level字段有值，就取出并显示，否则，就显示maxlevel字段的值。 SQL>select item,NVL2( level , level , maxlevel) from item; （3）NULLIF

格式：NULLIF（表达式1，表达式2）

作用：如果表达式1和表达式2相等，则返回空值，否则，返回表达式1的值。 比如： SQL> select item, nullif ( level , maxlevel ) from item;

6、聚合函数。

Avg，min，max，sum，count 其用法与sql server完全相同。

7、分析函数。

（1）ROW_NUMBER

ROW_NUMBER为有序组中的每一行（查询结果返回的行）返回一个唯一的排序值，序号由order by子句指定，从1开始。

比如：从scott . emp表中按工资sal字段排序。 SQL>select ename,job,deptno,sal from scott .emp order by sal desc //这是通常的做法，虽然已经按sal字段降序排列，但没有显示序号。结果如下图1。 SQL>select ename,job,deptno,sal,row_number( ) over (order by sal desc) from scott .emp; //返回结果与上面不同的是，仅仅增加了一个序号。结果如下图2。

――――――――――――――图1―――――――――――――

――――――――――――――图2―――――――――――――

注意到，图2中第2名与第3名的工资完全相同，但名次不一样，所有员工都是按工资多少参与排序的。能不能将部门分组，每个部门内的员工按工资多少排序，而部门与部门的排序互不影响呢？请看下例。下例只是在上面的语句中增加了partition by deptno这个子句，表示按部门号进行分组，同一部门号的员工，按工资排序，不同部门号的员工互不影响。结果如下图3。

SQL>select ename,job,deptno,sal,row_number( ) over (partition by deptno order by sal desc) as sal_rank from scott . emp;

――――――――――――――图3―――――――――――――

（2）DENSE_RANK

计算一个值在一组有序行中的排名，排名是以1开始的连续整数。具有相同值的行排名相同，并且排名是连续的。而图3中同一部门的员工，如果工资sal相同，其排名是不同的。如果同一部门的员工，工资sal相同，其排名也相同，则要用到dense_rank。

比如：下列语句对于同一部门内工资相同的员工，其排名相同，且部门内所有员工的排名连续。结果如图4。 SQL>select ename,job,deptno,sal,dense_rank( ) over (partition by deptno order by sal desc) as sal_rank from scott.emp;

――――――――――――――图4―――――――――――――

七、同义词

同义词可以是数据库对象的一个别名，这些对象可以是表、视图、序列、过程、函数、程序包。通过使用同义词，用户可以访问其它模式的数据库对象而无需指定模式前缀。比如，用户user1想访问user2的表emp，必须使用user2 .emp来引用，如果user1创建了一个名为emp的同义词代表user2.emp，那么，user1就可以用该同义词象访问自己的表一样引用user2.emp表了。但同义词不能代替权限，在使用同义词之前要确保用户已得到访问对象的权限。上述user1对 user2的emp表创建了同义词，但他在创建前必须首先取得可以访问该表的权限。

同义词有两种类型，私有同义词和公有同义词。 1、私有同义词。

私有同义词只能被当前模式的用户访问。分为两种情况，一种是在自身模式内创建私有同义词，用户必须拥有create synonym系统权限；一种是在其它用户模式下创建私有同义词，用户必须拥有create any synonym系统权限。 创建私有同义词的语法如下： Create [or replace] synonym [schema.]synonym_name for [schema.]object_name; 其中：

or rplace：表示该同义词如果存在，就替换该同义词。 Synonym_name：表示要创建的同义词名称。 Object_name：要创建的同义词对象的名称。

比如：

假设系统中有两个用户模式：itjob和scott ，scott用户有表emp 。itjob用户需要频繁的引