動態返回結果的檢視

最簡單的例子就是Oracle的資料字典檢視，比如USER_DB_LINKS。

對於USER_DB_LINKS檢視的定義的固定的，但是每個使用者訪問得到的結果是不同的，就是由於USER_DB_LINKS檢視中使用了變數：

SQL> select text     
  2  from dba_views
  3  where view_name = 'USER_DB_LINKS';

TEXT
--------------------------------------------------------------------------------
select l.name, l.userid, l.password, l.host, l.ctime
from sys.link$ l
where l.owner# = userenv('SCHEMAID')

由於使用了USERENV函式獲取SCHEMAID資訊，使得不同的使用者登陸獲得的方案ID不同，從而使得不同的使用者訪問USER_DB_LINKS檢視獲得的結果不同。

這裡可以利用USER模仿一個類似的例子：

SQL> create table t_user            
  2  as select *
  3  from dba_users;

Table created.

SQL> create view my_user
  2  as select *
  3  from t_user
  4  where username = user;

View created.

SQL> grant select on my_user to public;

Grant succeeded.

SQL> show user
USER is "TEST"
SQL> select username, account_status
  2  from my_user;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
TEST                           OPEN

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select username, account_status
  2  from test.my_user;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                            OPEN

SQL> conn u1/u1
Connected.
SQL> select username, account_status
  2  from test.my_user;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
U1                             OPEN

除了USER變數之外，還可以使用類似SYSDATE之類的變數來重建動態檢視：

SQL> create table t_date
  2  (id number,
  3  time date);

Table created.

SQL> insert into t_date
  2  select rownum, sysdate + rownum/86400 
  3  from dba_objects;

70858 rows created.

SQL> commit;

Commit complete.

SQL> create view v_date
  2  as select *     
  3  from t_date
  4  where time = sysdate;

View created.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from v_date;

        ID TIME
---------- -------------------
        43 2009-08-17 16:40:53

SQL> select * from v_date;

        ID TIME
---------- -------------------
        48 2009-08-17 16:40:58

SQL> select * from v_date;

        ID TIME
---------- -------------------
        55 2009-08-17 16:41:05

除了這些變數外，還可以利用DBMS_RANDOM包來構造動態的結果：

SQL> create table t_id (id number);

Table created.

SQL> insert into t_id                       
  2  select rownum               
  3  from dba_objects
  4  where rownum <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create view v_random as
  2  select * from t_id
  3  where id =
  4  (select round(dbms_random.value(1, 10000)) from dual);

View created.

SQL> select * from v_random;

        ID
----------
      8258

SQL> select * from v_random;

        ID
----------
      3827

SQL> select * from v_random;

        ID
----------
      7970

SQL> select * from v_random;

        ID
----------
      6750

合理的利用這些變數建立檢視，可以建立很多有趣的物件。