oracle 中 dual 详解数据库

基本上oracle引入dual为的就是符合语法
1. 我们先从名称来说，dual不是缩写词，本身就是完整的单词。dual名词意思是对数，做形容词时是指二重的，二元的。
2. Oracle中的dual表是一个单行单列的虚拟表。
3. Dual表是oracle与数据字典一起自动创建的一个表，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据 X , Oracle有内部逻辑保证dual表中永远只有一条数据。 4. Dual表主要用来选择系统变量或求一个表达式的值。
5. 更具体的说，我们见过这样一个简单的例子： 
  SELECT sysdate FROM daul
  而Oracle的SELECT语法的限制为:
SELECT * | [column1 [AS alias1], column2 [AS alias2]] FROM table

所以，没有表名就没有办法查询，而时间日期并不存放在任何表中，于是这个dual虚拟表的概念就被引入了。

1、DUAL表的用途
Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中
查看当前连接用户
SQL select user from dual;
USER

SYSTEM
查看当前日期、时间
SQL select sysdate from dual;
SYSDATE

2007-1-24 1
SQL select to_char(sysdate, yyyy-mm-dd hh24:mi:ss ) from dual;
TO_CHAR(SYSDATE, YYYY-MM-DDHH2

2007-01-24 15:02:47
当作计算器用
SQL select 1+2 from dual;
1+2
-
3
查看序列值
SQL create sequence aaa increment by 1 start with 1;
SQL select aaa.nextval from dual;
NEXTVAL
-
1
SQL select aaa.currval from dual;
CURRVAL
-
1

2、关于DUAL表的测试与分析
DUAL就是个一行一列的表，如果你往里执行insert、delete、truncate操作，就会导致很多程序出问题。结果也因sql*plus、pl/sql dev等工具而异。
查看DUAL是什么OBJECT
DUAL是属于SYS schema的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用.
SQL select owner, object_name , object_type from dba_objects where object_name like %DUAL%
OWNER OBJECT_NAME OBJECT_TYPE
-
SYS DUAL TABLE
PUBLIC DUAL SYNONYM

查看表结构，只有一个字段DUMMY，为VARCHAR2(1)型
SQL desc dual
Name Type Nullable Default Comments
-
DUMMY VARCHAR2(1) Y

DUAL表的结构：
create table SYS.DUAL
(
DUMMY VARCHAR2(1)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 16K
next 16K
minextents 1
maxextents 505
pctincrease 50
);

/*
很是困惑，ORACLE为什么要用VARCHAR(1)型，用CHAR(1)难道不好么？从这样的表结构来看，DUAL表设计的目的就是要尽可能的简单，以减少检索的开销。
还有，DUAL表是建立在SYSTEM表空间的，第一是因为DUAL表是SYS这个用户建的，本来默认的表空间就是SYSTEM；第二，把这个可能经常被查询的表和用户表分开来存放，对于系统性能的是有好处的。
有了创建了表、创建了同义词还是不够的。DUAL在SYS这个Schema下面，因此用别的用户登录是无法查询这个表的，因此还需要授权：
grant select on SYS.DUAL to PUBLIC with grant option;
将Select 权限授予公众。
接下来看看DUAL表中的数据，事实上，DUAL表中的数据和ORACLE数据库环境有着十分重要的关系（ORACLE不会为此瘫痪，但是不少存储过程以及一些查询将无法被正确执行）。
*/

查询行数
在创建数据库之后，DUAL表中便已经被插入了一条记录。个人认为：DUMMY字段的值并没有什么关系，重要的是DUAL表中的记录数
SQL select count(*) from dual;
COUNT(*)
-
1

SQL select * from dual;
DUMMY

X

插入数据，再查询记录，只返回一行记录
SQL insert into dual values ( Y );
1 row created.
SQL commit;
Commit complete.
SQL insert into dual values ( X );
1 row created.
SQL insert into dual values ( Z );
1 row created.
SQL commit;
Commit complete.
SQL select count(*) from dual;
COUNT(*)
-
4
SQL select * from dual;
DUMMY

X

/*
假我们插入一条数据，DUAL表不是返回一行，而是多行记录，那会是什么结果呢？
SQL insert into dual values( Y );
1 行 已插入
SQL commit;
提交完成
SQL select * from dual;
DUMMY

X
Y
SQL 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权限随意释放出去，这样对于系统是很危险的
*/

把表截掉
SQL truncate table dual;
Table truncated.
SQL select count(*) from dual;
COUNT(*)
-
0
SQL select * from dual;
no rows selected
SQL select sysdate from dual;
no rows selected

试着把DUAL表中的数据删除，看看会出现什么结果：
SQL delete from dual;
1 行 已删除
SQL select * from dual;
DUMMY

SQL select sysdate from dual;
SYSDATE

/*
我们便取不到系统日期了。因为，sysdate是个函数，作用于每一个数据行。现在没有数据了，自然就不可能取出系统日期。
这个对于很多用
select sysdate into v_sysdate from dual;
这种方式取系统时间以及其他信息的存储过程来说是致命的，因为，ORACLE会马上抛出一个NO_DATA_FOUND（ORA-01403）的异常，即使异常被捕获，存储过程也将无法正确完成要求的动作。
*/

对于DELETE操作来说，ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录.当然这写内部操作是不可见的
不管表内有多少记录（没有记录除外）,ORACLE对于每次DELETE操作都只删除了一条数据。
SQL select count(*) from dual;
COUNT(*)
-
2
SQL delete from dual;
1 行 已删除
SQL commit;
提交完成
SQL select count(*) from dual;
COUNT(*)
-
1

/*
附: ORACLE关于DUAL表不同寻常特性的解释
There is internalized code that makes this happen. Code checks that ensurethat a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product.
The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1).
This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other
prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception.
So DUAL should ALWAYS have 1 and only 1 row
*/

DUAL表可以执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，数据库起不了，会报Database startup crashes with ORA-1092错误。

3、如果DUAL表被“不幸”删除后的恢复：
用sys用户登陆。
创建DUAL表。
授予公众SELECT权限（SQL如上述，但不要给UPDATE，INSERT，DELETE权限）。
向DUAL表插入一条记录（仅此一条）： insert into dual values( X );
提交修改。
用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”
( “DUMMY” varchar2(1) )
pctfree 10 pctused 4;
SQL insert into dual values(‘X’);
SQL commit;
SQL Grant select on dual to Public;
授权成功。

SQL select * from dual;
D

X

SQL shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL startup
ORACLE 例程已经启动。

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL

OK， 下面就可以正常使用了。   最后在啰嗦一下，因为今天突然要用到查每个月最后一天的需要，所以上网查到Oracle里面有一个直接的函数就可以做到，后来我突然又想怎么没有最开始的一天的函数呢，可能是我没有细心找吧 总之不管那么多了，自己就随便写了一下，下面把个SQL写出来： select to_char((last_day(to_date(to_char(sysdate, mm )-1, mm ))+1), yyyy-mm-dd ) as startday,to_char(last_day(sysdate), yyyy-mm-dd ) as lastday from dual;

里面有些to_char其实是没有用的，但是为了转换成一致的日期格式，所以还是用了，写在此处主要是为了记忆，本人记性比较差.

 

引用原文：http://yesican.blog.51cto.com/700694/269814

 

写博客是为了记住自己容易忘记的东西，另外也是对自己工作的总结，文章可以转载，无需版权。希望尽自己的努力，做到更好，大家一起努力进步！

如果有什么问题，欢迎大家一起探讨，代码如有问题，欢迎各位大神指正！

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4695.html

oracle