OCP1z0-047 :2013-07-29 视图――别名

好，接下来我们来做测试，先登录到oe用户，查相关的表。

[html]
gyj@OCM> conn oe/oe 
Connected. 
oe@OCM> select table_name from tabs; 
 
TABLE_NAME 
------------------------------ 
PRODUCT_REF_LIST_NESTEDTAB 
SUBCATEGORY_REF_LIST_NESTEDTAB 
PROMOTIONS 
ORDERS 
PRODUCT_DESCRIPTIONS 
WAREHOUSES 
PRODUCT_INFORMATION 
ORDER_ITEMS 
CUSTOMERS 
INVENTORIES 
 
10 rows selected. 

gyj@OCM> conn oe/oe
Connected.
oe@OCM> select table_name from tabs;

TABLE_NAME
------------------------------
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB
PROMOTIONS
ORDERS
PRODUCT_DESCRIPTIONS
WAREHOUSES
PRODUCT_INFORMATION
ORDER_ITEMS
CUSTOMERS
INVENTORIES

10 rows selected.

一、答案A，很明显是错的，视图的字段与表的字段的个数不一样，操作如下报错：

[html]
oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date)  
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id) 
  3  GROUP BY o.order_id,o.order_date; 
CREATE OR REPLACE VIEW ord_vu(order_id,order_date) 
                              * 
ERROR at line 1: 
ORA-01730: invalid number of column names specified 
 
在CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)添加一列CT,操作如下就没问题： 
 
oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)  
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id) 
  3  GROUP BY o.order_id,o.order_date; 
 
View created. 

oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date)
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
  3  GROUP BY o.order_id,o.order_date;
CREATE OR REPLACE VIEW ord_vu(order_id,order_date)
                              *
ERROR at line 1:
ORA-01730: invalid number of column names specified

在CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)添加一列CT,操作如下就没问题：

oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
  3  GROUP BY o.order_id,o.order_date;

View created.

二、答案B是正确的，把视图定义的列名去掉，视图默认这些列名来自select中的显示的列，操作如下：

[html]
oe@OCM> CREATE OR REPLACE VIEW ord_vu  
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id) 
  3  GROUP BY o.order_id,o.order_date; 
 
View created. 
 
oe@OCM> select * from ord_vu; 
 
  ORDER_ID ORDER_DATE                                                                  NO OF ITEMS 
---------- --------------------------------------------------------------------------- ----------- 
      2354 15-JUL-08 08.18.23.234567 AM                                                         13 
      2361 14-NOV-07 05.34.21.986210 AM                                                          9 
      2363 24-OCT-07 07.49.56.346122 AM                                                          9 
      2367 28-JUN-08 11.53.32.335522 AM                                                          8 
省略结果。。。。。。。。。。。。。。。 

oe@OCM> CREATE OR REPLACE VIEW ord_vu
  2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
  3  GROUP BY o.order_id,o.order_date;

View created.

oe@OCM> select * from ord_vu;

  ORDER_ID ORDER_DATE                                                                  NO OF ITEMS
---------- --------------------------------------------------------------------------- -----------
      2354 15-JUL-08 08.18.23.234567 AM                                                         13
      2361 14-NOV-07 05.34.21.986210 AM        &nbs

补充：软件开发 , C++ ,

上一个：[leetcode]Spiral Matrix
下一个：hdu-4607-Park Visit