DUAL表的内部功能? [英] Internal Functionality of DUAL table?

问题描述

如果我们创建DUAL表，本地数据库会受到干扰吗?

Will local database get disturb if we create DUAL table ?

请建议我吗?

create table DUAL
(
      x varchar2(1)
);

推荐答案

如果我们创建DUAL表，本地数据库会受到干扰吗?

Will local database get disturb if we create DUAL table ?

是的，当然会发生奇怪的事情. DUAL由SYS拥有. SYS拥有数据字典，因此DUAL是数据字典的一部分.您永远不会通过SQL修改数据字典.

Yes, of course weird things can and will happen. DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever.

第一个问题是"如何保证自己的DUAL表中只有一行"?

这可以追溯到原始文章 Self- Steven Feuerstein 管理PL/SQL ，其中他解释了"使用您自己的DUAL表".但是，那是当DUAL表容易出现这种情况的时候.

This goes back to the original article Self-Managing PL/SQL by Steven Feuerstein where he explains "Use Your Own DUAL Table". But, that was back then when DUAL table was prone to such things.

但是，在最近的发行版中，DUAL表结构已变得很健壮，并且您最多只能拥有一行.这是一个证明:

However, in the recent releases, the DUAL table structure has been made robust and you cannot have more than single row ever. Here is a proof:

SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> insert into dual select * from dual;

1 row created.

SQL> select * from dual;

D
-
X

我知道，很少有人会争辩说我们可以使用触发器或ROWNUM = 1用自己的DUAL表处理一行，但是，您很快就会意识到缺点.从10g起，根本没有必要，因为DUAL表现在是一种内存结构，并且您不能如上所示向其添加一行.

I know, few would argue that we can handle one row with our own DUAL table using a trigger or ROWNUM =1, however, you will soon realize the cons. It is simply not necessary from 10g on wards, as the DUAL table is now a memory structure and you cannot add a row to it as demonstrated above.

假设您已经创建了自己的DUAL表，并且正在PL/SQL代码中使用对DUAL表的调用来获取USER，SYSDATE，SYSTIMESTAMP等.

Imagine a situation where you have created your own DUAL table, and you are using the call to DUAL table in your PL/SQL code to get the USER, SYSDATE, SYSTIMESTAMP etc.

这是从Oracle数据库随附的stdbody.sql文件中获取的代码:

This is the code taken from the stdbody.sql file delivered with Oracle Database:

1 FUNCTION USER
 2   RETURN VARCHAR2
 3 IS
 4   c VARCHAR2 (255);
 5 BEGIN
 6   SELECT USER
 7    INTO c
 8    FROM SYS.DUAL;
 9
10   RETURN c;
11 END;

如果您自己的DUAL表中有多个行，则每次调用PL/SQL代码中的USER函数都会失败，并显示 TOO_MANY_ROWS 错误.

If you ever have more than one row in your own DUAL table, every call to the USER function in your PL/SQL code will fail with TOO_MANY_ROWS error.

底线:早在10g天之前，所有有关使用自己的DUAL表的讨论都是有意义的. DUAL表现在是一个健壮的内存结构，不允许在其中添加一行.因此，使用您自己的DUAL表而不是SYS.DUAL没有任何意义.

Bottomline : All the discussion about using your own DUAL table made sense back then before 10g days. The DUAL table is now a robust memory structure and doesn't allow to add a row to it. So, makes no sense to use your own DUAL table rather than the SYS.DUAL.

这篇关于DUAL表的内部功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！