精通oracle10gplsql编程学习笔记

3.2 定义并使用变量
-- 编写plsql程序时,若临时存储数值。必须要定义变量和常量；若在应用环境和子程序之间传递
-- 数据，必须要为子程序指定参数。而在plsql程序中定义变量、常量和参数时,则必须要为它们
-- 指定Plsql数据类型。在编写plsql程序时，可以使用标量(scalar)类型、复合(composite)类型
-- 参照(reference)类型和lob(Large Object)类型等四种类型。\

3.2.1 标量类型
-- 标量变量是指只能存放单个数值的变量。当编写plsql程序时，最常用的变量就是标量变量。当
-- 定义标量变量时,必须要指定标量数据类型.

4.4 数据分组
-- 在开发数据库应用程序时,经常需要统计数据库中的数据。当执行数据统计时，需要将表中
-- 的数据分成几个组，最终统计每个组的数据结果。假设用户经常需要统计不同部门的雇员
-- 总数、雇员的平均工资、雇员的工资总计，并且希望最终生成统计报表
-- 在关系数据库中,数据分组是通过group by子句、分组函数以及having子句共同实现的。其中
-- group by子句用于指定要分组的列(例如deptno),而分组函数则用于显示统计结果(如count,
-- avg,sum等),而having子句则用于限制分组显示结果.

4.4 数据分组
-- 在开发数据库应用程序时,经常需要统计数据库中的数据。当执行数据统计时，需要将表中
-- 的数据分成几个组，最终统计每个组的数据结果。假设用户经常需要统计不同部门的雇员
-- 总数、雇员的平均工资、雇员的工资总计，并且希望最终生成统计报表
-- 在关系数据库中,数据分组是通过group by子句、分组函数以及having子句共同实现的。其中
-- group by子句用于指定要分组的列(例如deptno),而分组函数则用于显示统计结果(如count,
-- avg,sum等),而having子句则用于限制分组显示结果.

-- 4.4.1 分组函数
-- 分组函数用于统计统计表的数据.与单行函数不同,分组函数作用于多行,并返回一个结果,
-- 所以有时也被称为多行函数。一般情况下，分组函数要与group by子句结合使用。在使用
-- 分组函数时，如果忽略了group by子句,那么会汇总所有行,并产生一个结果。oracle数据库
-- 提供了大量的分组函数,在这里给大家介绍最常用的七个分组函数:
-- max: 该函数用于取得列或表达式的最大值,它适用于任何数据类型
-- min: 该函数用于取得列或表达式的最小值,它适用于任何数据类型
-- avg: 该函数用于取得列或表达式的平均值,它只适用于数字类型
-- sum: 该函数用于取得列或表达式的总和,它只适用于数字类型。
-- count: 该函数用于取得总计行数
-- variance: 该函数用于取得列或表达式的方差，并且该函数只适用于数字类型。当只有一行
-- 数据时，其返回值为0;当存在多行数据时,方差时按照如下的公式计算取得：
-- (sum(expr)2-sum(expr)2/(count(expr))/(count(expr)-1)
-- stddev: 该函数用于取得列或表达式的标准偏差，并且该函数只适用于数字类型。当只有一
-- 行数据时,其返回值为0;当存在多行数据时,oracle按照方差的平方根来计算标准偏差。
-- 当使用分组函数时,分组函数只能出现在选择列表、order by和having子句中,而不能出现在
-- where和group by子句中。另外，使用分组函数还有以下一些注意事项:
-- a.当使用分组函数时,除了函数count(*)之外,其他分组函数都会忽略null行.
-- b.当执行select语句时,如果选择列表同时包含列、表达式和分组函数，那么这些列和表达式
-- 必须出现在group by字句中.
-- c.当使用分组函数时,在分组函数中可以指定all和distinct选项。其中all是默认选项，该
-- 选项表示统计所有行数据(包括重复行)：如果指定distinct，则只会统计不同行值.

4.4.2 group by和having
-- group by子句用于对查询结果进行分组统计,而having子句则用于限制分组显示结果。注意，
-- 如果在选择列表中同时包含有列、表达式和分组函数，那么这些列和表达式必须出现在group
-- by子句中。使用group by和having子句的语法如下：
-- select column,group_function from table
-- [where condition] [group by group_by_expression]
-- [having group_condition];
-- 如上所示，column用于指定选择列表中的列或表达式；group_function用于指定分组函数；
-- condition用于指定条件子句;group_by_expression用于指定分组表达式；group_condition
-- 用于指定排除分组结果的条件.下面以示例说明使用group by子句和having子句的方法.
-- 示例一:使用group by进行单列分组
-- 单列分组是指在group by子句中使用单个列生成分组统计结果。当进行单列分组时，会基于
-- 列的每个不同值生成一个数据统计结果。下面以显示每个部门的平均工资和最高工资为例，
-- 说明使用group by进行单列分组的方法。示例如下：
-- select deptno,avg(sal),max(sal) from emp group by deptno;
-- 示例二:使用group by进行多列分组
-- 多列分组是指在group by子句中使用两个或两个以上的列生成分组统计结果。当进行多列
-- 分组时，会基于多个列的不同值生成数据统计结果。下面以显示每个部门每种岗位的平均
-- 工资和最高工资为例，说明使用group by进行多列分组的方法。示例如下：
-- select deptno,job,avg(sal),max(sal) from emp
-- group by deptno,job;
-- 示例三：使用having子句限制分组显示结果
-- having子句用于限制分组统计结果，并且having子句必须跟在group by子句后面。下面以
-- 显示平均工资低于2000的部门号、平均工资及最高工资为例，说明使用having子句的方法
-- 示例如下：
-- select deptno,avg(sal),max(sal) from emp
-- group by deptno
-- having avg(sal)<2000;
-- 当执行数据统计时,读者一定要正确的使用group by子句、where子句和分组函数，以避免
-- 不必要的错误。使用group by子句、where子句和分组函数有以下一些注意事项：
-- a.分组函数只能出现在选择列表、having子句和order by子句中
-- b.如果在select语句中同时包含有group by,having以及order by子句,则必须将order by
-- 子句放在最后。默认情况下，当使用group by子句统计数据时,会自动按照分组列的升序
-- 方式显示统计结果。通过使用order by子句，可以改变数据分组的排序方式，示例如下：
-- select deptno,avg(sal),max(sal) from emp
-- group by deptno order by avg(sal);
-- 如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在group by
-- 子句中，否则会显示错误信息。如下所示：
-- select deptno,job,avg(sal) from emp
-- group by deptno;
-- 当限制分组显示结果时,必须要使用having子句,而不能在where字句中使用分组函数限制
-- 分组显示结果，否则会显示错误信息，如下所示：
-- select deptno,avg(sal) from emp
-- where sum(sal) >1000 group by deptno;


4.5 连接查询
-- 连接查询是指基于两个或两个以上表或视图的查询。在实际应用中，查询单个表可能
-- 无法满足应用程序的实际需求(例如显示sales部门位置以及雇员名),在这种情况下就
-- 需要进行连接查询(dept和emp表).在使用连接查询时,应注意以下事项:
-- a.当使用连接查询时,必须在from子句后指定两个或两个以上的表
-- b.当使用连接查询时,应该在列名前加表名作为前缀。但是，如果不同表之间的列名不同
-- ，那么不需要在列名前加表名作为前缀；如果在不同表之间存在同名
-- 列，那么在列名之前必须要加上表名作为前缀。否则会因为列的二义性而报错，如下所
-- 示：
-- select deptno,dname,ename from dept,emp
-- where dept.deptno=emp.deptno;
-- c.当使用连接查询时,必须在where子句中指定有效的连接条件(在不同表的列之间进行
-- 连接)。如果不指定连接条件，或者指定了无效的连接条件，那么会导致生成笛卡尔积
-- (x*y).
-- 当进行连接查询时,使用表别名可以简化连接查询语句。当指定表别名时，别名应该跟
-- 在表名后面。不使用表别名和使用表别名的示例如下:

4.5.1 相等连接
-- 相等连接是指使用相等比较符(=)指定连接条件的连接查询，这种连接查询主要用于检索
-- 主从表之间的相关数据。使用相等连接的语法如下：
-- select table1.column,table2.column from table1,table2 where
-- table1.column1=table2.column;
-- 如上所示，当使用相等连接时，必须要使用等值比较符指定连接条件。下面以示例说明
-- 使用相等连接的方法。
-- 示例一：使用相等连接执行主从查询
-- 下面以显示所有雇员名称、工资及其所在的部门名称为例，说明使用相等连接的方法。
-- 示例如下：
-- select e.ename,e.sal,d.dname from emp e,dept d
-- where e.deptno=d.deptno;
-- 示例二：使用and指定其他条件
-- 当使用相等连接时，会显示满足连接条件的所有数据。为了在执行相等连接的同时指定
-- 其他连接条件，可以在where子句中使用and操作符。下面以显示部门10的部门名、雇员
-- 名及其工资为例，说明使用and操作符指定其他条件的方法。示例如下：
-- select d.dname,e.ename,e.sal from emp e,dept d
-- where e.deptno=d.deptno and d.deptno=10;

4.5.2 不等连接
-- 不等连接是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不
-- 等连接主要用于在不同表之间显示特定范围的信息。salgrade表存放着工资级别信息(
-- 例如5级工资范围为3001-9999，4级工资范围为2001-3000等等).下面以显示所有雇员的
-- 名称、工资及其工资级别为例，说明使用不等连接的方法。示例如下：
-- select a.ename,a.sal,b.grade from emp a,salgrade b
-- where a.sal between b.losal and b.hisal;


4.5.3 自连接
-- 自连接是指在同一张表之间的连接查询，它主要用在自参照表上显示上下级关系或者层次关
-- 系。自参照表是指在不同列之间具有参照关系或主从关系的表。例如，emp表包含有empno
-- (雇员号)和mgr(管理者号)列，二者之间就具有参照关系。
-- 根据empno列和mgr列的对应关系，可以确定雇员jones,blake和clark得管理者为king.为了
-- 显示雇员及其其管理者之间的对应关系，可以使用自连接。因为自连接是在同一张表之间
-- 的连接，所以必要要定义表列名。下面以显示black雇员的上级领导为例，说明使用自连接
-- 的方法。示例如下:
-- select manager.ename from emp manaer,emp worker
-- where manager.empno=worker.mgr
-- and worker.ename ='BLAKE';

4.5.4 内连接和外连接.sql
-- 内连接用于返回满足连接条件的记录；而外连接则是内连接的扩展，它不仅会返回满足连接
-- 条件的所有记录，而且还会返回不满足连接条件的记录。在oracle9i之前，连接语法都是在
-- where子句中指定的；从oracle9i开始，还可以在from 子句中指定连接语法。语法如下：
-- select table1.column1,table2.column2
-- from table1 [inner|left|right|full] join table2 on table1.column1=table2.column2
-- inner join表示内连接；left join表示左外连接；right join表示右外连接；full join
-- 表示完全外连接；on子句用于指定连接条件。注意，如果使用from子句指定内外连接，则必
-- 须要使用on子句指定连接条件：如果使用(+)操作符指定外连接，则必须使用where子句指定
-- 连接条件。
-- 1.内连接
-- 内连接用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时如果没有指定
-- 任何连接操作符，那么这些连接查询都属于内连接。下面以显示部门10的部门名及其雇员
-- 名为例，说明使用内连接的方法。示例如下：
-- select a.dname,b.ename from dept a,emp b
-- where a.deptno=b.deptno and a.deptno=10;
-- 另外，当执行连接查询时，通过在from子句中指定inner join选项，也可以指定内连接，
-- 示例如下：
-- select a.dname,b.ename from dept a inner join emp b
-- on a.deptno=b.deptno and a.deptno=10;
-- 从oracle9i开始，如果主表的主键列和从表的外部键列名称相同，那么还可以使用natural
-- join关键字自动执行内连接操作。示例如下：
-- select dname,ename from dept natural join emp;
-- 2.左外连接
-- 左外连接是通过指定left[outer] join选项来实现的。当使用左外连接时，不仅会返回满足
-- 连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表的其他行。下面以
-- 显示部门10部门名、雇员名，以及其他部门名为例，说明使用左外连接的方法。示例如下：
-- select a.dname,b.ename from dept a left join emp b
-- on a.deptno=b.deptno and a.deptno=10;
-- 3.右外连接
-- 右外连接是通过指定right[outer]join选项来实现的。当使用右外连接，不仅会返回满足连
-- 接条件的所有行，而且还会返回不满足连接条件的连接操作符右边表的其他行。下面以显示
-- 部门10的部门名、雇员名以及其他雇员名为例，说明使用右外连接的方法。示例如下：
-- select a.dname,b.ename from dept a right join emp b
-- on a.deptno=b.deptno and a.deptno=10;
-- 4.完全外连接
-- 完全外连接是通过指定full[outer]join选项来实现的.当使用完全外连接时,不仅会返回
-- 满足连接条件的所有行,而且还会返回不满足连接条件的所有其他行。下面以显示部门10
-- 的部门名、雇员名以及其他部门名和其他雇员名为例，说明使用完全外连接的方法。示例
-- 如下：
-- select a.dname,b.ename from dept a full join emp b
-- on a.deptno=b.deptno and a.deptno=10;
-- 5.使用(+)操作符
-- 在oracle9i之前，当执行外连接时，都是使用连接操作符(+)来完成的。尽管可以使用操作
-- 符(+)执行外连接操作，但oracle9i开始oracle建议使用outer join执行外连接。使用(+)
-- 操作符执行外连接的语法如下:
-- select table1.column1,table2.column2 from table1,table2
-- where table1.column1(+)=table2.column2;
-- 当使用(+)操作符执行外连接时,必须将该操作符放在显示较少行(完全满足连接条件行)的
-- 一端。当使用(+)操作符时，必须要注意以下事项：
-- a.(+)操作符只能出现在where子句中，并且不能与outer join语法同时使用。
-- b.当使用(+)操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条
-- 件中都包含(+)操作符
-- c.（+）操作符只适用于列，不能用在表达式上
-- d.(+)操作符不能与or和in操作符一起使用
-- e.(+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
-- (1)使用(+)操作符执行左外连接
-- 当使用左外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的
-- 左边表的其他行，因为(+)操作符要放在行数较少的一端，所以在where子句中应该将该
-- 操作符放在右边表的一端。下面以显示部门10的部门名、雇员名及其其他部门名为例，说明
-- 使用(+)操作执行左外连接的方法。示例如下：
-- select a.dname,b.ename from dept a,emp b
-- where a.deptno=b.deptno(+) and b.deptno(+)=10;
-- (2)使用(+)操作符执行右外连接
-- 当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的
-- 右边表的其他行。因为(+)操作符要放在行数较少的一端，所以在where子句中应该将该
-- 操作符放在左边表的一端。下面以显示部门10的部门名、雇员名以及其他雇员名为例，说
-- 明使用(+)操作执行右外连接的方法。示例如下：
-- select a.dname,e.ename from dept a ,emp b
-- where a.deptno(+)=b.deptno and a.deptno(+)=10;


4.6 子查询
-- 子查询是指嵌入在其他sql语句中的select语句，也称为嵌套查询。注意，挡在DDL语句中引用
-- 子查询时，可以带有order by 子句；但是当在where子句、set子句中引用子查询时，不能带
-- 有order by子句。子查询具有以下一些作用;
-- a.通过在insert或create table语句中使用子查询，可以将源表数据插入到目标表中
-- b.通过在create view或create materialized view中使用子查询，可以定义视图或实体化
-- 视图所对应的select语句。
-- c.通过在update语句中使用子查询可以修改一列或多列数据。
-- d.通过在where，having，start with子句中使用子查询，可以提供条件值
-- 根据子查询返回结果的不同，子查询又被分为单行子查询、多行子查询和多列子查询。
-- a.单行子查询-返回一行数据；
-- b.多行子查询-返回多行数据；
-- c.多列子查询-返回多列数据；

4.6.1 单行子查询.sql
-- 单行子查询是指只返回一行数据的子查询语句。当在where子句中引用单行子查询时，可以
-- 使用单行比较符(=，>,<,>=,<=,<>).下面以显示scott同事的姓名、工资和部门号为例，说明
-- 单行子查询的使用方法。示例如下：
-- select ename,sal,deptno from emp where deptno=
-- (select deptno from emp whre ename='SCOTT');

4.6.2 多行子查询
-- 多行子查询是指返回多行数据的子查询语句。当在where子句中使用多行子查询时，必须要
-- 使用多行比较符(in,all,any).他们的作用如下：
-- a.in-匹配于子查询结果的任一个值即可
-- b.all-必须要符合子查询结果的所有值
-- c.any-只要符合子查询结果的任一个值即可。
-- 注意，all和any操作符不能单独使用，而只能与单行比较符(=,<,>,<=,>=,<>)结合使用。
-- 1.在多行子查询中使用in操作符
-- 当在多行子查询中使用in操作符时，会处理匹配于子查询任一个值的行。下面以显示匹配于
-- 部门10岗位的雇员名、岗位、工资、部门号为例，说明在多行子查询中使用in操作符的方法
-- 示例如下:
-- select ename,job,sal,deptno from emp where job in
-- (select distinct job from emp where deptno=10);
-- 2.在多行子查询中使用all操作符
-- all操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果。下面以
-- 显示高于部门30的所有雇员工资的雇员名、工资和部门号为例，说明在多行子查询中使用all
-- 操作符的方法。示例如下：
-- select ename,sal,deptno from emp whre sal>all
-- (select sal from emp where deptno=30);
-- 3.在多行子查询中使用any操作符
-- any操作符必须要与单行操作符结合使用，并且返回行只需匹配于子查询的任一个结果即可，
-- 下面以显示高于部门30的任意员工工资的雇员名、工资和部门号为例，说明在多行子查询
-- 中使用any操作符的方法，示例如下:
-- select ename,sal,deptno from emp where sal>any
-- (select sal from emp where deptno=30);

4.6.3 多列子查询
-- 单行子查询是指子查询只返回单列单行数据，多行子查询是指子查询只返回单列多行数据，
-- 二者都是针对单列而言的。而多列子查询则是指返回多列数据的子查询语句，当多列子查询
-- 返回单行数据时，在where子句中可以使用单行比较符，当多列子查询返回多行数据时，在
-- where子句中必须使用多行比较符(in,all,any).下面以显示与scott部门和岗位完全相同的
-- 所有雇员为例，说明使用多列子查询的方法示例如下：
-- select ename,job,sal,deptno from emp where (deptno,job)=
-- (select deptno,job from emp where ename='SCOTT');
-- 在使用子查询比较多个列的数据时，既可以使用成对比较，也可以使用非成对比较。其中，
-- 成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配
-- 当进行成对比较时，要求工资和补助必须同时匹配；而执行非成对比较时，只要工资匹配于
-- 工资列表中的某一个、补助匹配于补助列表中的某一个就可以了。下面通过示例说明成对
-- 比较和非成对比较的区别。在执行子查询之前，请大家先执行以下语句更新雇员clark的工资
-- 和补助：update emp set sal=1500,comm=300 where ename='CLERK';
-- 1.成对比较示例:
-- select ename,sal,comm,deptno from emp
-- where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno=30);
-- 2.非成对比较示例
-- 执行非成对比较时，必须要使用多个多行子查询来实现。下面以显示工资匹配于部门30工资
-- 列表、补助匹配于部门30补助列表的所有雇员为例，说明进行非成对比较的方法。示例如下
-- select ename,sal,comm,deptno from emp
-- where sal in(select sal from emp where deptno=30)
-- and nvl(comm,-1) in
-- (select nvl(comm,-1) from emp where deptno=30);

4.6.4 其他子查询
-- 在where子句中除了可以使用单行子查询、多行子查询以及多列子查询外，还可以使用相关
-- 子查询，另外在from子句、DML语句、DDL语句中也可以使用子查询。
-- 1.相关子查询
-- 相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过exists谓词来实现的
-- 下面以显示工作在“New York”的所有雇员为例，说明相关子查询的使用方法。示例如下：
-- select ename,job,sal,deptno from emp where exists
-- (select 1 from dept where dept.deptno=emp.deptno and dept.loc='NEW　YORK');
-- 如上所示，当使用exists谓词时，如果子查询存在返回结果，则条件为true；如果子查询没有
-- 返回结果，则条件为false。
-- 2.在from 子句中使用子查询
-- 当在from子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图。注意，
-- 当在from子句中使用子查询时，必须要给子查询指定别名。下面以显示高于部门平均工资的
-- 雇员信息为例，说明在from子句中使用子查询的方法。示例如下:
-- select ename,job,sal from emp,
-- (select deptno,avg(sal) avgsal from emp group by deptno) dept
-- where emp.deptno=dept.deptno and sal>dept.avgsal;
-- 3.在DML语句中使用子查询
-- 子查询不仅适用于select语句，也适用于任何DML语句，下面举例说明在DML语句中使用子
-- 查询的方法。
-- (1)在insert语句中使用子查询
-- 通过在insert语句中引用子查询，可以将一张表的数据装载到另一张表中。下面以将emp表
-- 的数据装载到employee表中为例，说明在insert语句中使用子查询的方法。示例如下:
-- insert into employee(id,name,title,salary) select empno,ename,job,sal from emp;
-- (2)在update语句中使用子查询
-- 当在update语句中使用子查询时，既可以在where子句中引用子查询(返回未知条件值),也可
-- 以在set子句中使用子查询(修改列数据).下面以将smith同岗位的雇员工资和补助更新为与
-- smith的工资和补助完全相同为例，说明在update语句中使用子查询的方法。示例如下:
-- update emp set (sal,comm)=
-- (select sal,comm from emp where ename='SMITH')
-- where job=(select job from emp where ename='SMITH');
-- (3)在delete语句中使用子查询
-- 在delete语句中使用子查询时，可以在where子句中引用子查询返回未知条件值。下面以
-- 删除sales部门的所有雇员为例，说明在delete的where子句中使用子查询的方法，示例
-- 如下:
-- delete from emp where deptno=
-- (select deptno from dept where dname='sales');
-- 4.在DDL语句中使用子查询
-- 除了可以在select,insert,update,delete语句中使用子查询外，也可以在DDL语句中使用
-- 子查询。注意，当在select和DML语句中使用子查询时，where子句和set子句的子查询语句
-- 不能含有order by 子句；但在DDL语句中使用子查询时，子查询可以包含order by子句。
-- 下面通过示例说明在DDL语句中使用子查询的方法。
-- (1)在create table语句中使用子查询
-- 通过在create table中使用子查询，可以在建立新表的同时复制表中的数据，下面以建立
-- new_emp表，并将emp表的数据复制到该表为例，说明在create table语句中使用子查询的
-- 方法。示例如下：
-- create table new_emp(id,name,sal,job,deptno) as
-- select empno,ename,sal,job,deptno from emp;
-- (2)在create view语句中使用子查询
-- 建立视图时，必须指定视图所对应的子查询语句。下面以建立视图dept10,说明在create
-- view语句中使用子查询的方法。示例如下:
-- create or replace view dept_10 as
-- select empno,ename,job,sal,deptno from emp
-- where deptno=10 order by empno;
-- (3)在create materialized view语句中使用子查询
-- 建立实体化视图时，必须要指定实体化视图所对应的sql语句，并且该SQL语句将来可以用
-- 于查询重写。下面以建立实体化视图summary_emp为例，说明在create materialized view
-- 语句中使用子查询的方法。示例如下:
-- create materialized view summary_emp as
-- select deptno,job,avg(sal) avgsal,sum(sal) sumsal
-- from emp group by cube(deptno,job);


4.7 合并查询
-- 为了合并多个select语句的结果，可以使用集合操作符union,union all,intersect和minus.
-- 语法如下:
-- select 语句1 [union|union all|intersect|minus|] select语句2...
-- 这些集合操作符具有相同的优先级，当同时使用多个操作符时，会按照从左到右的方式引用
-- 这些集合操作符。当使用集合操作符时，必须确保不同查询的列个数和数据类型都要匹配。
-- 另外，使用集合操作符有以下一些限制:
-- a.对于lob,varray和嵌套表列来说，集合操作符时无效的
-- b.对于long列来说，union,intersect,minus操作符时无效的
-- c.如果选择列表包含了表达式，则必须要为其指定列别名
-- 1.union
-- union操作符用于获取两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
-- 并且会以第一列的结果进行排序。下面以显示工资高于2500的雇员和岗位为"manager"的雇员
-- 为例，说明使用该操作符的方法。示例如下:
-- select ename,sal,job from emp where sal>2500
-- union
-- select emame,sal,job from emp where job='MANAGER';
-- 2.union all
-- union all操作符用于获取两个结果集的并集。但与union操作符不同，该操作符不会取消
-- 重复值，而且也不会以任何列进行排序。下面以显示工资高于2500的雇员和岗位为"MANAGER"
-- 的雇员为例，说明使用该操作符的方法。示例如下:
-- select ename,sal,job from emp where sal>2500
-- union all
-- select ename,sal,job from emp where job='MANAGER';
-- 3.intersect
-- intersect操作符用于获取两个结果集的交集。当使用该操作符时，只会显示同时存在于
-- 两个结果集中的数据，并且会以第一列进行排序。下面以显示工资高于2500并且岗位为
-- "MANAGER"的雇员为例，说明使用该操作符的方法。示例如下:
-- select ename,sal,job from emp where sal>2500
-- intersect
-- select ename,sal,job from emp where job='MANAGER';
--4.minus
-- minus操作符用于获取两个结果集的差集，当使用该操作符时，只会显示在第一个结果集中
-- 存在，在第二个结果集中不存在的数据，并且会以第一列进行排序。下面以显示工资高于
-- 2500但岗位不是"MANAGER"的雇员为例，说明使用该操作符的方法。示例如下:
-- select ename,sal,job from emp where sal>2500
-- minus
-- select ename,sal,job from emp where job='MANAGER';

4.8 其他复杂查询
-- 在实际应用中，除了经常会用到数据分组、连接查询、子查询以及合并查询之外，在特定
-- 情况下还可以使用其他复杂查询语句，包括层次查询，倒叙查询等等。本节介绍这些复杂
-- 查询的作用及其使用方法
-- 1.层次查询
-- 当表具有层次结构数据时，通过使用层次查询可以更直观的显示数据结果，并显示其数据
-- 之间的层次关系。
-- 假设某层次查询返回12行，则根行(第一行)应该是层次最高的行；而第2，7，9行是根行的
-- 下一级行，它们具有相同层次；第3，4行(相同层次)为第二行的下一级行，一次类推。在
-- 层次查询中，伪列level可以用于返回层次，根行层次为1,第二级行层次为2，类此类推。层
-- 次查询子句的语法如下:
-- start with-condition-connect-by-conditon
-- start with：用于指定层次查询的行
-- connect by:用于指定父行和子行之间的关系。在condition表达式中，必须使用prior引用
-- 父行。语法如下：
-- ...prior expr=expr或...expr=prior expr
-- emp表时具有层次结构数据的表，其中empno列对应于雇员号，而mgr列对应于管理者编号。
-- 通过使用层次查询，可以显示雇员之间的上下级关系。下面以显示除"CLERK"外所有其他
-- 雇员的上下级关系为例，说明使用层次查询的方法。示例如下：
-- col ename format a15
-- col job format a15
-- select lpad(' ',3*(level-1))||ename enmae,
-- lpad(' ',3*(level-1))||job job from emp
-- where job<>'CLERK' start with mgr is null
-- connect by mgr=prior empno;
-- 2.使用case表达式
-- 为了在sql语句中使用if...then...else语法，可以使用case表达式。通过case表达式，可以
-- 避免调用过程来完成条件分支操作。case表达式的语法如下:
-- case-simple case expression-else clause-end
-- 当使用case表达式时，使用when子句可以指定条件语句。下面以显示雇员名、工资以及工资
-- 级别为例，说明使用case表达式的方法。示例如下:
-- select ename,sal,case when sal>3000 then 3
-- when sal>2000 then 2 else 1 end grade
-- from emp where deptno=10;
-- 3.倒叙查询
-- 4.使用with子句重用子查询
-- 对于多次使用相同子查询的复杂查询语句来说，用户可能会将查询语句分成两条语句执行
-- 第一条语句将子查询结果存放到临时表，第二条查询语句使用临时表处理数据，从oracle9i
-- 开始，通过with子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务
-- 从而避免了使用临时表
-- 示例一：显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和(两次使用相同
-- 子查询)
-- select ename,sum(sal) as dept_total from emp,dept
-- where emp.deptno=dept.deptno group by dname
-- having sum(sal)>
-- (select sum(sal)*1/3 from emp,dept
-- where emp.deptno=dept.deptno);
-- 示例二：显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和(使用with子句
-- 重用子查询)
-- with summary as(
-- select dname,sum(sal) as dept_total from emp,dept
-- where emp.deptno=dept.deptno group by dname)
-- select dname,dpet_total from summary where dept_total>
-- (select sum(dept_total)*1/3 from summary);


第5章 sql函数.sql
-- sql函数包括单行函数和多行函数，其中单行函数是指输入一行输出也是一行的函数；多行
-- 函数也被称为分组函数，它会根据输入的多行数据输出一个结果。sql函数不仅可以在sql
-- 语句中引用，也可以在plsql块中引用。大多数单行函数都可以直接在plsql块内引用，但
-- 多行函数不能由plsql块直接引用，而只能在plsql块的内嵌sql语句中引用。本章将详细
-- 介绍oracle所提供的sql函数，这些函数包含了许多oracle10g和oracle9i新增加的函数。
-- 在学习了本章之后，读者应该：
-- a.在sql语句和plsql块中使用数字函数
-- b.在sql语句和plsql语句中使用字符函数
-- c.在sql语句和plsql语句中使用日期函数
-- d.在sql语句和plsql语句中使用转换函数
-- e.在sql语句和plsql语句中使用oracle10g新增加的集合函数
-- f.在sql语句中使用分组函数
-- g.了解并掌握oracle10g、oracle9i新增加的sql函数
5.1 数字函数.sql
-- 数字函数的输入参数和返回值都是数字型，并且多数函数精确到38位。函数cos、cosh、
-- exp,ln,log,sqrt,sin,sinh,tan和tanh精确到36位，而函数acos、asin、atan和atan2则
-- 精确到30位。这些数字函数不仅可以在sql语句中引用，也可以直接在plsql块中引用。下面
-- 详细介绍oracle所提供的各种数字函数
-- 1.abs(n):该函数用于返回数字n的绝对值。示例如下：
-- declare
-- v_abs number(6,2);
-- begin
-- v_abs :=abs(&no);
-- dbms_output.put_line('绝对值:'||v_abs);
-- end;
-- 2.acos(n):该函数用于返回数字n的反余弦值，输入值的范围是-1，1，输出值的单位为弧度
-- 示例如下：
-- select acos(.3),acos(-.3) from dual;
-- 3.asin(n):该函数用于返回数字n的反正弦值，输入值的范围是-1，1，输出值的单位为弧度
-- 示例如下：
-- declare
--  v_asin number(6,2);
-- begin
--  v_asin :=asin(0.8);
--  dbms_output.put_line('0.8的反正弦值:'||v_asin);
-- end;
-- 4.atan(n):该函数用于返回数字n的反正切值，输入值可以是任何数字，输出值的单位为弧度
-- 示例如下：
-- select atan(10.3),atan(-20.3) from dual;
-- 5.atan2(n,m):该函数用于返回数字n除以数字m的反正切值。输入值除了m不能0以外，可以是
-- 任何数字(m不能为0)，输出值的单位为弧度，示例如下：
-- declare
--  v_atan2 number(6,2);
-- begin
--  v_atan2 :=atan2(19,3);
--  dbms_output.put_line('19/3的反正切值:'||v_atan2);
-- end;
-- 6.ceil(n):该函数用于返回大于等于数字n的最小整数，示例如下：
-- select ceil(15),ceil(15.1) from dual;
-- 7.cos(n):该函数用于返回数字n(以弧度表示的角度值)的余弦值
-- declare
--  v_cos number(6,2);
-- begin
--  v_cos :=cos(60);
--  dbms_output.put_line('60的余弦值:'||v_cos);
-- end;
-- 8.cosh(n):该函数用于返回数字n的双曲余弦值。示例如下：
-- select cosh(0) "-的双曲余弦值" from dual;
-- 9.exp(n):该函数用于返回e的n次幂(e=2.71828183...).示例如下：
-- declare
--  v_exp number(6,2);
-- begin
--  v_exp :=exp(4);
--  dbms_output.put_line('e的4次幂:'||v_exp);
-- end;
-- 10.floor(n):该函数用于返回小于等于数字n的最大整数。示例如下:
-- select floor(15),floor(15.1) from dual;
-- 11.ln(n):该函数用于返回数字n的自然对数,其中数字n必须大于0.示例如下:
-- declare
--  v_ln number(6,2);
-- begin
--  v_ln :=ln(4);
--  dbms_output.put_line('4的自然对数:'||v_ln);
-- end;
-- 12.log(m,n):该函数用于返回以数字m为底的数字n的对数,数字m可以是除0和1以外的任何
-- 正整数。，数字n可以是任何正整数。示例如下:
-- select log(2,8),log(10,100) from dual;
-- 13.mod(m,n):该函数用于取得两个数字相除后的余数。如果数字n为0。则返回结果为m。
-- 示例如下:
-- declare
--  v_mod number(6,2);
-- begin
--  v_mod :=mod(10,3);
--  dbms_output.put_line('10除3的余数:'||v_mod);
-- end;
-- 14.power(m,n):该函数用于返回数字m的n次幂，底数m和指数n可以是任何数字。但如果数字
-- m为负数，则数字n必须是正数。示例如下:
-- select power(-2,3),power(2,-1) from dual;
-- 15.round(n，[m]):该函数用于执行四舍五入运算；如果省略m，则四舍五入至整数位；如果
-- m是负数，则四舍五入到小数点前m位;如果m是正数，则四舍五入至小数点后m位。示例如下:
-- declare
--  v_round number(6,2);
-- begin
--  v_round :=round(&no,1);
--  dbms_output.put_line(‘四舍五入到小数点后1位:‘||v_round);
-- end;
-- 16.sign(n):该函数用于检测数字的正负。如果数字n小于0，则函数的返回值为-1;如果
-- 数字n等于0，则函数的返回值为0；如果数字n大于0，则函数的返回值为1.
-- select sign(-10),sign(0),sign(20) from dual;
-- 17.sin(n):该函数用于返回数字n(以弧度表示的角)的正弦值.示例如下:
-- declare
--  v_sin number(6,2);
-- begin
--  v_sin :=sin(0.3);
--  dbms_output.put_line('0.3的正弦值:’||v_sin);
-- end;
-- 18.sinh(n):该函数用于返回数字n的双曲正弦值。示例如下:
-- select sinh(.5) from dual;
-- 19.sqrt(n):该函数用于返回数字n的平方根，并且数字n必须大于等于0.示例如下:
-- declare
--  v_sqrt number(6,2);
-- begin
--  v_sqrt :=sqrt(10);
--  dbms_output.put_line('10的平方根:'||v_sqrt);
-- end;
-- 20.tan(n):该函数用于返回数字n(以弧度表示的角)的正切值，示例如下:
-- select tan(45*3.14159265359/180) from dual;
-- 21.tanh(n):该函数用于返回数字n的双曲正切值.示例如下:
-- declare
--  v_tanh number(6,2);
-- begin
--  v_tanh :=tanh(10);
--  dbms_output.put_line('10的双曲正切值:'||v_tanh);
-- end;
-- 22.trunc(n,[m]):该函数用于截取数字.如果省略数字m，则将数字的小数部分截去；如果
-- 数字m是正数，则将数字n截取至小数点后的第m位；如果数字m是负数，则将数字n截取至
-- 小数点的前m位。示例如下：
-- select trunc(45.926),trunc(45.926,1),trunc(45.926,-1) from dual;

5.2 字符函数
-- 字符函数的输入参数为字符类型，其返回值时字符类型或数字类型。字符函数既可以在sql
-- 语句中使用，也可以直接在plsql块中引用。下面详细介绍oracle所提供的字符函数，以及在
-- sql语句和plsql块中使用这些字符函数的方法
-- 1.ascii(char):该函数用于返回字符串首字符的ascii码值，示例如下:
-- select ascii('a') "a",ascii('A') "A" from dual;
-- 2.chr(n):该函数用于将ascii码值转变为字符。示例如下:
-- declare
--  v_chr varchar2(10);
-- begin
--  v_chr :=chr(56);
--  dbms_output.put_line('ascii码为56的字符:'||v_chr);
-- end;
-- 3.concat:该函数用于连接字符串，其作用与连接操作符(||)完全相同。示例如下：
-- select concat('Good','Morning') from dual;
-- 4.initcap(char):该函数用于将字符串中每个单词的首字符大写，其他字符小写，单词之间
-- 用空格和非字母字符分割。示例如下：
-- declare
--  v_initcap varchar2(10);
-- begin
--  v_initcap :=initcap('my word');
--  dbms_output.put_line('首字符大写:'||v_initcap);
-- end;
-- 5.instr(char1,char2[,n[,m]]):该函数用于取得字串在字符串中的位置，其中数字n为起始
-- 搜索位置，数字m为字串出现次数。如果数字n为负数，则从尾部开始搜索；数字m必须为正
-- 整数，并且n和m得默认值为1.示例如下:
-- select instr('morning','n') from dual;
-- 6.length（char):该函数用于返回字符串的长度。如果字符串的类型为char,则其长度包括
-- 所有的后缀空格；如果char是null,则返回null.示例如下:
-- declare
--  v_len int;
-- begin
--  v_len :=length('my word');
--  dbms_output.put_line('字符串的长度'||v_len);
-- end;
-- 7.lower(char):该函数用于将字符串转换为小写格式.示例如下:
-- select lower('SQL introduction') from dual;
-- 8.lpad(char1,n,char2):该函数用于在字符串char1的左端填充字符串char2,直至字符串
-- 长度为n，char2的默认值为空格。如果char1长度大于n,则该函数返回char1左端的n个
-- 字符。示例如下
-- declare
--  v_lpad varchar2(10);
-- begin
--  v_lpad :=lpad('aaaa',10,'*);
--  dbms_output.put_line('在字符串左端添加字符'||v_lpad);
-- end;
-- 9.ltrim(char1[,set]):该函数用于去掉字符串char1左端所包含的set中的任何字符。oracle
-- 从左端第一个字符开始扫描，逐一去掉在set中出现的字符，当遇到不是set中的字符时
-- 终止，然后返回剩余结果。示例如下；
-- select ltrim('morning','m',ltrim('morning','or') from dual;
-- 10.nls_initcap(char,'nls_param')：该函数用于将字符串char的首字符大写,其他字符
-- 小写，其中char用于指定nchar或nvarchar2类型字符串，其前面加上n，用单引号括起来，
-- nls_param的格式为"nls_sort=sort"，用于指定特定语言特征。示例如下：
-- declare
--   v_nls_initcap nchar2(10);
-- begin
--   v_nls_initcap :=nls_initcap('my word');
--   dbms_output.put_line('首字符大写'||v_nls_initcap);
-- end;
-- 11.nls_lower(char,'nls_param'):该函数用于将字符串转变为小写，其中nls_param的
-- 格式为"nls_sort=sort",用于指定特定语言特征。示例如下:
-- select nls_lower(n'SQL') from dual;
-- 12.nls_sort(char,'nls_param'):该函数用于按照特定语言的要求进行排序，其中nls_param
-- 的格式为"nls_sort=sort"，用于指定特定语言特征。示例如下:
-- select * from test order by nls_sort(name,'nls_sort=xDanish');
-- 13.nls_upper(char,'nls_param'):该函数用于将字符串转变为大写，其中nls_param的
-- 格式为“nls_sort=sort”,用于指定特定语言特征。示例如下:
-- delcare
--  v_upper varchar2(10);
-- begin
--  v_upper :=nls_upper('my word','nls_sort=XGERMAN');
--  dbms_output.put_line('字符串大写:'||v_upper);
-- end;
-- 14.regexp_replace(source_string,pattern[,replace_string[,position[,occurrence
-- [,match_parameter]]]])：该函数是oracle10g新增加的函数，它扩展了函数replace
-- 的功能，并且该函数用于按照
-- 特定表达式的规则替换字符串。其中,source_string用于指定源字符表达式，pattern用于
-- 指定规则表达式，replace_string用于指定替换字符串，position用于指定起始搜索位置
-- occurrence用于指定替换出现的第n个字符串，match_parameter用于指定默认匹配操作的
-- 文本串。示例如下:
-- select regexp_replace(country_name,'(.)','\l ') "regexp_replace" from dual;
-- 15.replace(char,search_string[,replacement_string]):该函数用于将字符串的子串
-- 替换为其他子串。如果replacement_string为null,则会去掉指定子串；如果search_string
-- 为null,则返回原有字符串。示例如下:
-- select replace('缺省值为10','缺省','默认') from dual;
-- 16.rpad(char1,n,char2):该函数用于在字符串char1的右端填充字符串char2,直至字符
-- 的总长度为n,char2的默认值为空格。如果char1长度大于n，则该函数返回char1左端的n
-- 个字符。示例如下:
-- declare
--  v_rpad varchar2(10);
-- begin
--  v_rpad :=rpad('aaaa',10,'*');
--  dbms_output.put_line('在右端添加字符:'||v_rpad);
-- end;
-- 17.rtrim(char[,set]):该函数用于去掉字符串char右端所包含的set中的任何字符。
-- oracle从右端第一个字符开始扫描，逐一去掉在set中出现的字符，当遇到不是set中
-- 的字符时终止，然后返回剩余结果。示例如下:
-- select rtrim('moring','ing') from dual;
-- 18.soundex(char):该函数用于返回字符串的语音表示，使用该函数可以比较发音相同的
-- 字符串。示例如下:
-- select soundex('ship'),soundex('sheep') from dual;
-- 19.substr(char,m[,n]):该函数用于取得字符串的子串，其中数字m是字符开始位置，数字
-- n是子串的长度，如果m为0,则从首字符开始；如果m是负数，则从尾部开始。示例如下:
-- declare
--  v_subs varchar2(10);
-- begin
--  v_subs :=substr('morning',1,3);
--  dbms_output.put_line('字符串的子串:'||v_subs);
-- end;
-- 20.translate(char,from_string,to_string):该函数用于将字符串char的字符按照from
-- _string和to_string得对应关系进行转换。示例如下:
-- select translate('2KRW229','0123456789ABCDEFGHIGKLMNOPQRSTUVWSYZ',
-- '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "trans" from dual;
-- 21.trim(charfrom string):该函数用于从字符串的头部、尾部或两端截断特定字符，
-- 参数char为要截去的字符，string是源字符串。示例如下:
-- declare
--  v_source varchar2(20) :='ABCDEFGHJAB');
--  v_trim varchar2(20);
-- begin
--  v_trim :=trim('a' from v_source);
--  dbms_output.put_line(v_trim);
-- end;
-- 22.upper(char):该函数用于将字符串转换为大写格式.示例如下:
-- select upper('sql') from dual;


5.4 转换函数
-- 转换函数用于将数值从一种数据类型转换为另一种数据类型.在某些情况下，oracle server
-- 会隐含地转换数据类型。但在编写应用程序时，为了防止出现编译错误，如果数据类型不同
-- 那么应该使用转换函数进行类型转换。下面详细介绍oracle所提供的各种转换函数，以及在
-- sql语句和plsql块中使用这些转换函数的方法。
-- 1.asciistr(string):该函数是oracle9i新增加的函数，用于将任意字符集的字符串转变为数据
-- 库字符集的ascii字符串。示例如下:
-- select asciistr('中国') from dual;
-- 2.bin_to_num(expr[,expr]...):该函数是oracle9i新增加的函数，用于将位向量值转变为
-- 实际的数字值。示例如下:
-- select bin_to_num(1,0,1,1,1) from dual;
-- 3.cast(expr as type_name):该函数用于将一个内置数据类型或集合类型转变为另一个内置
-- 数据类型或集合类型。示例如下：
-- declare
--  v_cast varchar2(20);
-- begin
--  v_cast :=cast(sysdate as varchar2);
--  dbms_output.put_line('转换结果'||v_cast);
-- end;
-- 4.chartorowid(char):该函数用于将字符串值转变为rowid数据类型，但字符串值必须符合
-- rowid格式。示例如下:
-- select chartorowid('AAADdlAAAfAAABSAA/') from dual;
-- 5.compose(string):该函数是oracle9i新增加的函数，用于将输入字符串转变为unicode字符
-- 串值.示例如下:
-- select compose('O'||unistr('\0308')) from dual;
-- 5.convert(char,dest_char_set,source_char_set):该函数用于将字符串从一个字符集转变
-- 为另一个字符集。示例如下:
-- declare
--  v_convert varchar2(20);
-- begin
--  v_convert :=convert('中国’,'US7ASCII','WE8ISO8859P1');
--  dbms_output.put_line('转换结果'||v_convert);
-- end;
-- 6.decompose(string):该函数是oracle9i新增加的函数，用于分解字符串并返回相应的
-- unicode字符串.示例如下:
-- select decompose('chateaus') from dual;
-- 7.hextoraw(char)：该函数用于将十六进制字符串转变为raw数据类型，示例如下:
-- declare
--  v_raw raw(20);
-- begin
--  v_raw :=hextoraw('AB56FA2C');
--  dbms_output.put_line('转换结果'||v_raw);
-- end;
-- 8.rawtohex(raw):该函数是oracle9i新增加的函数，用于将数值转变为十六进制字符串
-- declare
--  v_char varchar2(20);
-- begin
--  v_char :=rawtohex('AB56FA2C');
--  dbms_output.put_line('转换结果'||v_char);
-- end;
-- 9.to_char(character):该函数用于将nchar,nvarchar2,clob,和nclob数据转变为数据库
-- 字符集数据当用于nchar,nvarchar2和nclob类型时，字符串用单引号括起来，前面加上n
-- declare
--  v_char varchar2(20);
-- begin
--  v_char :=to_char(n'中华人民共和国');
--  dbms_output.put_line('转换结果'||v_char);
-- end;
-- 10.to_char(date[,fmt[,nls_param]]):该函数用于将日期值转变为字符串，其中fmt用于
-- 指定日期格式，nls_param用于指定nls参数.示例如下:
-- select to_char(sysdate,'YYYY-MM-DD') from dual;
-- 11.to_char(n[,fmt[,nls_param]]):该函数用于将数字值转变为varchar2数据类型。
-- declare
--  v_char varchar2(20);
-- begin
--  v_char :=to_char(-10000,'L99G999D99MI');
--  dbms_output.put_line('转换结果'||v_char);
-- end;
-- 12.to_clob(char):该函数时oracle9i新增加的函数，用于将字符串转变为clob类型。char
-- 参数使用nchar,nvarchar2和nclob类型时,字符串需用单引号括起来，且在前面加上n。
-- select to_clob(n'中华人民共和国’） from dual;
-- 13.to_date(char[,fmt[,nls_param]]):该函数用于将符合特定日期格式的字符串转变为
-- date类型的值.
-- declare
--  v_date date;
-- begin
--  v_date :=to_date('2010-11-24','YYYY-MM-DD');
--  dbms_output.put_line('转换结果'||v_date);
-- end;
-- 14.to_multi_byte(char):该函数用于将单字节字符串转变为多字节字符串。
-- declare
--  v_multi varchar2(10);
-- begin
--  v_multi :=to_multi_byte('abcd');
--  dbms_output.put_line('转换结果'||v_multi);
-- end;
-- 15.to_nchar(character):该函数用于将字符串由数据库字符集转变为民族字符集。
-- select to_nchar('伟大的祖国') from dual;
-- 16.to_nchar(datetime,[fmt[,nls_param]]):该函数用于将日期时间值转变为民族字符集
-- 的字符串。
-- declare
--  v_nchar nvarchar2(30);
-- begin
--  v_nchar :=to_nchar(sysdate,'YYYY-MM-DD');
--  dbms_output.put_line('转换结果'||v_nchar);
-- end;
-- 17.to_nchar(number):该函数用于将数字值转变为民族字符集的字符串.示例如下:
-- select to_nchar(10) from dual;
-- 18.to_nclob(clob_column|char):该函数用于将clob列或字符串转变为nclob类型.
-- declare
--  v_nclob nclob;
-- begin
--  v_nclob :=to_nclob('伟大的祖国');
--  dbms_output.put_line('转换结果'||v_nclob);
-- end;
-- 19.to_number(char,[,fmt[,nls_param]]):该函数用于将符合特定数字格式的字符串转变
-- 为数字值。
-- select to_number('RMB1000.00','L99999D99') from dual;
-- 20.unistr(string):该函数是oracle9i新增加的函数。用于输入字符串并返回相应的
-- unicode字符。示例如下:
-- declare
--  v_unicode varchar2(10);
-- begin
--  v_unicode :=unistr('\00D6');
--  dbms_output.put_line('unicode字符'||v_unicode);
-- end;


5.5 集合函数
-- 从oracle10g开始,为了扩展集合类型(嵌套表和varray)得功能,oracle新增加了几个新的
-- 集合函数。下面介绍这些集合函数
-- 1.cardinality(nested_table)：该函数是oracle10g新增加的函数。用于返回嵌套表的实际
-- 元素个数。
-- select product_id,cardinality(ad_textdocs_ntab) from print_media;
-- 2.collect(column):该函数是oracle10g新增加的函数，用于根据输入列和被选择行建立
-- 嵌套表结果.
-- create type phone_book_t as table of phone_list_typ;

5.6 其他单行函数
-- 除了单行数字、字符、日期和转换函数之外，oracle还提供了一些其他的单行函数。下面详细介绍
-- 如何在sql语句和plsql语句中使用这些单行函数
-- 1.bfilename('directory','filename'):该函数用于初始化bfile定位符，其中directory是
-- 与OS路径相关的directory对象，filename时OS文件的名称。
-- declare
--  v_content varchar2(100);
--  v_bfile bfile;
--  amount int;
--  offset int :=-1;
-- begin
--  v_bfile :=bfilename('USER_DIR','a.txt');
--  amount :=dbms_lob.getlength(v_bfile);
--  dbms_lob.fileopen(v_bfile);
--  dbms_lob.read(v_bfile,amount,offset,v_content);
--  dbms_lob.fileclose(v_bfile);
--  dbms_output.put_line(v_content);
-- end;
-- 2.coalesce(expr1[,expr2][,expr3]...):该函数是oracle9i新增加的函数，用于返回表达式
-- 中第一个not null表达式的结果。示例如下:
-- declare
--  v_expr1 int;
--  v_expr2 int :=100;
--  v_expr3 int :=1000;
--  v_nn int;
-- begin
--  v_nn :=coalesce(v_expr1,v_expr2,v_expr3);
--  dbms_output.put_line(v_nn);
-- end;
-- 3.decode(expr,search1,result[,search2,result2,...][default]):该函数用于返回匹配于
-- 特定表达式的结果。如果search1匹配于expr,则返回result1;如果search2匹配于expr,则返回
-- result2,依此类推；如果如果没有任何匹配关系。则返回default.示例如下:
-- select deptno,ename,sal,
-- decode(deptno,10,sal*1.2,20,sal*1.1,sal) "new salary"
-- from emp order by deptno;
-- 4.depth(n):该函数是oracle9i新增加的函数，用于返回xml方案中under_path路径所对应的
-- 相对层数。其中参数n用于指定相对层数。示例如下:
-- select path(1),depth(2) from resource_view
-- where under_path(res,'/sys/schemas/OE',1)=1
-- and under_path(res,'sys/schemas/OE',2)=1;
-- 5.dump（expr,return_fmt):该函数用于返回表达式所对应的数据类型代码、长度以及内部
-- 表示格式，其中return_fmt用于指定返回格式(8:八进制符号，10：十进制符号,16:十六进制
-- 符号，17：单字符).注意，该函数只能在sql语句中使用。示例如下:
-- select dump('hello',1016) from dual;
-- 6.empty_blob():该函数用于初始化blob变量，示例如下:
-- declare
--  v_lob blob;
-- begin
--  v_lob :=empty_blob();
-- end;
-- 7.empty_clob();该函数用于初始化clob变量，示例如下:
-- update person set resume=empty_clob() where id=1;
-- 8.existnode(XMLType_instance,Xpath_string):该函数是oracle9i新增加的函数，用于确定
-- XML节点路径是否存在，返回0表示节点不存在，返回1表示节点存在。示例如下:
-- select existnode(VALUE(p),'/PurchaseOrder/User') from xmltable p;
-- 10.extract(XMLType_instance,Xpath_string):该函数是oracle9i新增加的函数，用于返回
-- XML节点路径下的相应内容。示例如下:
-- select extract(value(p),'/PurchseOder/User') from xmltable p;
-- 11.extractvalue(xmltype_instance,xpath_string):该函数是oracle9i新增加的函数，
-- 用于返回xml节点路径下的值。示例如下:
-- select extractvalue(value(p),'/PurchaseOrder/User') from xmptable p;
-- 12.greatest(expr1[,expr2]):该函数用于返回列表表达式expr1,expr2中值最大的一个.
-- 在比较之前，expr2等项会被隐含地转换为expr1的数据类型。示例如下:
-- select greatest('black','blank','back') from dual;
-- 13.least(expr[,expr]):该函数用于返回列表表达式expr1,expr2,...中值最小的一个.在
-- 比较之前，expr2等项会被隐含地转换为expr1的数据类型。示例如下:
-- select least('black','blank','back') from dual;
-- 14.nls_charset_decl_len(byte_count,charset_id):该函数用于返回字节数在特定字符集
-- 中占用的字符个数。示例如下:
-- select nls_charset_decl_len(200,nls_charset_id('ZHS16GBKFIXED')) from dual;
-- 15.nls_charset_id(text)：该函数用于返回字符集的id号。示例如下:
-- select nls_charset_id('ZHS16GBKFIXED') from dual;
-- 16.nls_charset_name(number):该函数用于返回特定id号所对应的字符集名。示例如下:
-- select nls_charset_name(852) from dual;
-- 17.nullif(expr1,expr2):该函数是oracle9i新增加的函数，用于比较表达式expr1和expr2
-- 如果二者相等，则返回null,否则返回expr1。示例如下:
-- declare
--  v_expr1 int :=100;
--  v_expr2 int :=100;
-- begin
--  if nullif(v_expr1,v_expr2) is null then
--    dbms_output.put_line('二者相等');
--  else
---   dbms_output.put_line(’二者不等');
--  end if;
-- end;
-- 18.nvl(expr1,expr2)：该函数用于将null转变为实际值。如果expr1是null,则返回expr2;
-- 如果expr1不是null，则返回expr1。参数expr1和expr2可以是任意数据类型，但二者数据
-- 类型必须要匹配。示例如下:
-- select ename,sal,comm,sal+nvl(comm,0) salary from emp where deptno=30;
-- 19.nvl2(expr1,expr2,expr3):该函数是oracle9i新增加的函数，它也用于处理null
-- 如果expr1不是null,则返回expr2,如果expr1是空，则返回expr3.参数expr1可以是
-- 任何数据类型，而expr2和expr3可以是除long之外的任何数据类型。示例如下:
-- select ename,sal,comm,nvl2(comm,sal+comm,sal) salary from emp where deptno=30;
-- 20.path(correctin_integer):该函数是oracle9i新增加的函数，用于返回特定xml
-- 资源所对应的相对路径。示例如下：
-- select path(1),depth(2) from resource_view
-- where under_path(res,'/sys/schemas/OE',1)=1
-- and under_path(res,'/sys/schemas/OE',2)=1;
-- 21.sys_connect_by_path(column,char):该函数是oracle9i新增加的函数，(只适用
-- 于层次查询)，用于返回从根到节点的列值路径。示例如下:
-- select lpad('  ',2*level-1)||
-- sys_connect_by_path(ename,'/') "path"
-- from emp start with ename='SCOTT'
-- connect by prior empno=mgr;
-- 22.sys_context('context','attribute'):该函数用于返回应用上下文的特定属性
-- 值，其中context为应用上下文，而attribute则用于指定属性名。示例如下
-- select sys_context('userenv','session_user') "数据库用户"，
-- sys_context('userenv','os_user') "OS用户" from dual;
-- 23.sys_dburigen:该函数是oracle9i新增加的函数。根据列或属性生成类型为dburitype的
-- url.
-- 24.sys_extract_utc(datetime_with_timezone):在日期时间函数一节中已说明
-- 25.sys_guid:该函数用于生成类型为raw的16字节的惟一标识符，每次调用该函数都会生成
-- 不同的raw数据。示例如下:
-- select sys_guid() from dual;
-- 26.sys_typeid(object_type_value):该函数用于返回惟一的类型id值。示例如下
-- select name,sys_typeid(value(p)) "type_id" from person p;
-- 27.sys_xmlagg(expr[,fmt]):该函数是oracle9i新增加的函数，用于汇总所有xml文档，并
-- 生成一个xml文档。示例如下
-- select sys_xmlagg(sys_xmlgen(ename)) from emp where deptno=10;
-- 28.uid:该函数用于返回当前会话所对应的用户id号。示例如下
-- declare
--  v_uid int;
-- begin
--  v_uid :=uid;
--  dbms_output.put_line('会话用户id号:'||v_uid);
-- end;
-- 29.updatexml(xmltype_instance,xpath_string,value_expr)：该函数是oracle9i新增加
-- 的函数，用户更新特定xmltype实例相应节点路径的内容。
-- update xmltable p set p=updatexml(value(p),'/PurchaseOrder/User/text()','SCOTT');
-- 30.user：该函数用户返回当前会话所对应的数据库用户名。示例如下:
-- declare
--  v_user varchar2(10);
-- begin
--  v_user :=user;
--  dbms_output.put_line('会话用户:'||v_user);
-- end;
-- 31.userenv(parameter):该函数用于返回当前会话上下文的属性信息，其中parameter有
-- 以下取值。
-- isdba:如果用户具有dba权限，则返回true;否则返回false;
-- language:返回当前会话的语言、地区和字符集
-- termunal：返回当前会话所在终端的os标识符
-- client_info:返回由包dbms_application_info所存储的用户会话信息(最长64字节)
-- select userenv('language') from dual;
-- 32.vsize(expr):该函数用于返回oracle9i内部存储expr的实际字节数。如果expr是null,
-- 则该函数返回null.注意。该函数只能在sql语句中使用。示例如下:
-- select ename,vsize(ename) from emp where deptno=10;
-- 33.xmlagg(xmltype_instance[order by sort_list]):该函数是oracle9i新增加的函数。用
-- 于汇总多个xml块。并生成xml文档。示例如下：
-- select xmlagg(xmlelement("employee",ename||' '||sal)) from emp where deptno=10;
-- 34.xmlcolattval

5.7 分组函数
-- 分组函数也称为多行函数，它会根据输入的多行数据返回一个结果。分组函数主要用于执行
-- 数据统计或数据汇总操作，并且分组函数只能出现在select语句的选择列表、order by子句
-- 和having子句中.注意，分组函数不能直接在plsql语句中引用，而只能在内嵌select语句中
-- 使用。下面详细介绍oracle所提供的各种分组函数，以及在sql语句中引用这些分组函数的
-- 方法。
-- 1.avg([all[distinct]expr]):该函数用于计算平均值.示例如下:
-- declare
--  v_avg number(6,2);
-- begin
--  select avg(sal) into v_avg from emp;
--  dbms_output.put_line('雇员平均工资:'||v_avg);
-- end;
-- 2.corr(expr1,expr2):该函数用于返回成对数值的相关系数，其数值使用表达式"covar_pop"
-- (expr1,expr2)(stddev_pop(expr1)*stddev_pop(expr2))"获得。
-- 3.count[all|distinct]expr):该函数用于返回总计行数。示例如下:
-- select count(distinct sal) from emp;
-- 4.covar_pop(expr1,expr2):该函数用于返回成对数字的协方差，其数值表达式取得。
-- 5.first:该函数是oracle9i新增加的函数，不能单独使用，必须与其他分组函数结合使用。
-- 通过使用该函数，可以取得排序等级的第一级，然后使用分组函数汇总该等级的数据。
-- select min(sal) keep(dense_rank first order by comm desc) "补助最高级别雇员的最低工资"
-- ,max(sal) keep(dense_rank first order by comm desc) "补助最高级别雇员的最高工资"
-- from emp;
-- 6.group_id();该函数是oracle9i新增加的函数，用于区分分组结果中的重复行。示例如下:

5.8 对象函数
-- 对象函数用于操纵ref对象。ref对象实际是指向对象类型数据的指针，为了操纵ref对象,必须
-- 要使用对象函数。在介绍如何使用这些对象函数之前，首先建立示例对象表，并为它们插入
-- 数据。示例如下:
-- create type cust_address_typ_new as object
-- (street_address varchar2(40),
-- postal_code varchar2(10),
-- city varchar2(30)
-- ,state_province varchar2(10),
-- country_id char(2));
-- create table address_table of cust_address_typ_new;
-- create table customer_address(
-- add_id number,
-- address ref cust_address_typ_new scope is address_table);
-- insert into address_table values('1 Fist','G45 EV8','Paris','CA','US');
-- insert into customer_address select 999,ref(a) from address_table a;
-- 1.deref(expr):该函数用于返回参照对象expr所引用的对象实例。示例如下:
-- select deref(address).city from customer_address;
-- 2.make_ref(object_table_view,key):该函数可以基于对象视图的一行数据或基于对象表
-- (存在基于主键的对象标识符)的一行数据建立ref.示例如下
-- select make_ref(oc_inventories,3003) from dual;
-- 3.ref(expr):该函数用于返回对象所对应的ref值.示例如下
-- select ref(e) from address_table e;
-- 4.reftohex(expr):该函数用于将ref值转变为十六进制字符串。示例如下:
-- select reftohex(ref(e)) from address_table e;
-- 5.value(expr):该函数用于返回行对象所对应的对象实例数据，其中expr用于指定行对象的
-- 别名.示例如下
-- select value(e).city from address_table e;