Oracle

DUAL表

一.DUAL表是什么

1：DUAL表是Oracle提供的最小的工作表，是sys用户下的一张内部表，所有用户都可以使用DUAL名称访问，无论什么时候这个表总是存在。
2：DUAL表是一个单行单列的虚拟表，这个表只有1列DUMMY，数据类型为VARCHAR2(1),不论执行什么操作（不要删除记录），它都只有一条记录——“X”，Oracle有内部逻辑保证DUAL表中只有一条数据
3：DUAL表主要用来选择系统变量或求一个表达式的值
4：最常见的一个简单的例子SELECT sysdate FROM daul
Oracle的SELECT语法的限制为 SELECT * | [column1 [AS alias1], column2 [AS alias2]] FROM table
所以没有表名就没有办法查询，而时间日期并不存放在任何表中，于是这个dual虚拟表的概念就被引入了。
5：dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中。

二.dual表的作用

1：查看当前连接用户

select user from dual;
SYSTEM

2：查看当前日期、时间

select sysdate from dual;
2007-1-24 1

3：日期转换

select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
2007-01-24 15:02:47

4：当作计算器用

select 1+2 from dual;
3

5：创建查看序列值

create sequence aaa increment by 1 start with 1;
select aaa.nextval from dual;
1
select aaa.currval from dual;
1
DUAL就是个一行一列的表，如果你往里执行insert,delete,truncate操作，就会导致很多程序出问题。结果也因sqlplus、pl/sql dev等工具而异。假我们插入想dual插入一条数据，那会是什么结果呢？
insert into dual values(‘Y’);
select from dual;
DUMMY
X
Y
select sysdate from dual;
SYSDATE
2004-12-15
2004-12-15

这个时候返回的是两条记录，会引起问题。在通过使用select sysdate into v_sysdate from dual来获取时间或者其他信息的存储过程来说，ORACLE会抛出TOO_MANY_ROWS(ORA-01422)异常。

因此，需要保证在DUAL表内有且仅有一条记录，不能把DUAL表的UPDATE，INSERT，DELETE权限随意释放出去，这样对于系统是很危险的。

三：对DUAL表执行了drop操作怎么办

如果不小心把dual删除了，是可以恢复的。网上有很多资料。

DUAL表可以执行插入、更新、删除操作，还可以执行drop操作，但是不要去执行drop操作，否则会使系统不能用，数据库起不了报Database startup crashes with ORA-1092错误。可以通过执行以下步骤来进行恢复。可以用sys用户登陆。

SQL> create pfile=’d:pfile.bak’ from spfile

SQL> shutdown immediate

在d:pfile.bak文件中最后加入一条：

replication_dependency_tracking = FALSE

重新启动数据库：

SQL> startup pfile=’d:pfile.bak’

SQL> create table “sys”.”DUAL”

[an error occurred while processing this directive]

常用函数

字符函数

-- 大小写控制函数, upper() 转大写  lower() 转小写
select lower('Hello World') 转小写,upper('Hello World') 转大写 from dual;
-- 查询员工姓名，以小写的形式显示
select lower(ename) from emp;
-- initcap()  首字母大写
select initcap('hello') 首字母大写 from dual;
-- 查询所有员工姓名，首字母大写显示
select initcap(ename) from emp;
-- concat()  字符连接函数，等同于||
select concat('hello','world') from dual;
-- 在所有的员工名字之前，加上Dear，并且首字母大写
select 'Dear'||initcap(ename) from emp;
select concat('Dear',initcap(ename)) from emp;
-- substr()  求大的字符串中某段小字符串
select substr('Hello World',3) from dual;
select substr('Hello World',3,4) from dual;
-- 显示所有员工的姓名的第一个字母
select substr(ename,0,1) from emp;
select substr(sname,0,1) from student;
-- instr()在大字符串中，找到小字符串的位置
select instr('HelloWorld','ll') from dual;
-- length()  和 lengthb()  字符数和字节数
select length('中国') 字符数, lengthb('中国') 字节数 from dual;
select length('china') 字符数, lengthb('china') 字节数 from dual;
-- 找出姓名是3个汉字的学生
select * from student where length(sname)=3;
-- lpad() 左填充 rpad() 右填充
select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;
-- trim() 去掉字符串前后指定的字符
select trim('H' from 'Hello WorldH') from dual;
select trim('*' from '****Hello World****') from dual;
-- replace() 替换函数
select replace ('Hello World','l','*') from dual;

数学函数

-- round() 四舍五入 trunc() 截取  mod()求余  floor()向下取整 ceil()向上取整
select round(32.365),trunc(44.567),mod(40,3) from dual;
select round(32.365,2),round(32.365,-1) from dual;
select trunc(44.567,2) from dual;
select floor(3.5),ceil(3.1) from dual;

日期函数

-- 显示当前的日期时间
select sysdate from dual;
-- 显示昨天、今天、明天，+1表示 加一天，-1表示减一天
select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;
-- 两个日期相减，得到的结果是相差的天数，日期不能相加
-- 查询员工的工龄
select empno,ename,trunc(sysdate-hiredate) from emp;
select empno,ename,trunc((sysdate-hiredate)/30),trunc((sysdate-hiredate)/365) from emp;
-- months_between()  返回两个日期之间相差的月数
select empno,ename,trunc((sysdate-hiredate)/30) 方式1,
                    trunc(months_between(sysdate,hiredate)) 方式2 from emp;
-- add_months() ：在指定日期后加上月份
select add_months(sysdate,1) 下个月 from dual;
-- last_day:返回某个日期的当月的最后一天
select last_day(sysdate) from dual;
-- 计算这个月还剩下多少天
select last_day(sysdate)-sysdate from dual;
-- 在emp表中，计算出哪些员工是他入职当月的最后3天入职的
select * from emp where last_day(hiredate)-hiredate <= 3;
-- next_day 下一个离当前日期最近的某个日期
select next_day(sysdate,'星期三') from dual;
-- 对日期进行截取,四舍五入
select trunc(sysdate+200,'month') 月,
        trunc(sysdate+200,'year'),
        round(sysdate+200,'year') from dual;

转换函数

-- 将字符串转成日期格式
-- 查询81-9-28日入职的员工信息
select * from emp where hiredate = to_date('1981-9-28','yyyy-mm-dd');
-- 将日期转成字符串
select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual;
-- 将字符串转成数字格式  to_number
select '0001111222' from dual;
select to_number('0001112222') from dual;

通用函数

-- nvl(值1，值2) 当值1为空时，返回值2
-- 查询所有员工的姓名、工资和奖金，如果没有奖金，显示0
select ename,sal,nvl(comm,0) 奖金 from emp; 
-- 求所有员工的全部工资(工资+奖金),如果奖金为空，按500计算
select ename,sal+nvl(comm,500) 全部工资 from emp;
-- nvl2(值1，值2，值3) 当值1为空，返回值3，否则返回值2
-- 求所有员工的全部工资，奖金在原来的基础上加1000
select ename,sal+nvl2(comm,comm+1000,1000) 全部工资 from emp;
-- nullif(值1，值2) 如果值1=值2，返回null，否则返回值1
select nullif('abc','abc') from dual;
select nullif('abc','abcde') from dual;
 -- coalesce(值1，值2，值3....) 返回列表中第一个不为空的值
 select coalesce(55,null,33) from dual;
 select coalesce(null,null,33) from dual;

TopN查询

按照某一个指标进行倒序或者正序排列，并取其中的N项
Oracle中，提供了一个rownum伪列，是系统顺序分配从查询返回的行的边行
返回的第一行分配1，第二行是2，依次类推，这个伪字段可以用于限制查询返回的总行数
rownum不能以任何表的名称作为前缀

-- 年龄最大的4个同学
 select * from student order by sage desc;
 select rownum,s.* from (select * from student order by sage desc) s where rownum <=4;

rownum的几种情况

1.rownum对于等于某值的查询条件

比如，想查询学生表的第一条学生的信息，可以使用rownum=1作为条件，但是查第二条的时候，使用rownum=2是查不到数据，因为rownum从1开始，对于1以上的自然数在和rownum做等于判断的时候，系统就默认是结果为false(假)
所以，无法查到rownum=n(n>1的自然数)

-- 查询第一个学生
select * from student where rownum =1;
select * from student where rownum =2;  -- 查询不到数据

2.rownum对于大于某值的查询条件

比如，如果想找到从第二行记录以后的记录，使用rownum>1，是查不到数据的
rownum认为 在做大于比较的时候，超过1的值都是false
那如果想查询第二行以后的数据，可以使用子查询的方式来实现
注意：子查询中的rownum，必须显式的查询出来，且需要取别名，这个时候，子查询中的rownum就被当做一个普通的字段来当做条件。

-- 查询第5行以后的数据
select rownum no,s.* from student s;
select * from (select rownum no,s.* from student s) where no >=5;

3.rownum对于小于某值的查询条件

rownum对于小于某个值(大于1)的条件默认都是成立的，可以查到数据

-- 查询表中的前6行数据
select * from student where rownum <=6;

4.使用rownum查询某个区间的数据，必须要使用子查询

-- 查询第二行和第四行之间的数据
select rownum no,s.* from student s where rownum <=4;
select * from (select rownum no,s.* from student s where rownum <=4)
        where no >=2;

5.rownum和排序

rownum是在取数据的时候，就产生的序号，所以，在对指定的数据去排序的时候，需要注意顺序问题
如果想rownum按照排序后的数据顺序排序，那这个时候也需要使用子查询

-- 查询年龄由大到小排序的，第四至第八位学生信息。
select * from student  order by sage desc ;
select rownum no, s.* from (select * from student  order by sage desc) s
      where rownum <=8;
select * from (select rownum no, s.* from (select * from student  order by sage desc) s
      where rownum <=8) where no >=4;

分页查询

当一个表中的数据量特别大的时候，如果一次性显示给用户，会造成页面过于庞大，体验很差，所以，一般会使用分页查询解决这个问题
Oracle中分页：使用rownum关键字,分页查询会明确的知道一页有多少条数据，分多少页
语法： 每页显示m条数据，查询n页数据
select from (select rownum r,e. from 要分页的表 e where rownum <=mn) t where r > mn-m

-- 查询员工表信息的前五条数据，第一页   m=5,n=1
select * from (select rownum r,e.* from emp e where rownum <=5) t where r > 0;
-- 查询员工表信息的6-10条数据,第二页  m=5,n=2
select * from (select rownum r,e.* from emp e where rownum <=10) t where r > 5;
-- 查询员工表11-15条数据，第三页
select * from (select rownum r,e.* from emp e where rownum <=15) t where r > 10;