好，接下來我們來做測試，先登錄到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 9
2363 24-OCT-07 07.49.56.346122 AM 9
2367 28-JUN-08 11.53.32.335522 AM 8
省略結果。。。。。。。。。。。。。。。
三、答案C是錯的，在創建視圖時，對這種使用各種函數，或運算表達式的列，一定要起別名，如沒有別名視圖創建就會失敗，操作如下：
[html]
oe@OCM> CREATE OR REPLACE VIEW ord_vu
2 AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
3 GROUP BY o.order_id,o.order_date;
AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
把上面的視圖改成如下：
oe@OCM> CREATE OR REPLACE VIEW ord_vu
2 AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) CT 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
2 AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
3 GROUP BY o.order_id,o.order_date;
AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
把上面的視圖改成如下：
oe@OCM> CREATE OR REPLACE VIEW ord_vu
2 AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) CT 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.
四、答案D也是錯的，錯誤與答案C一個問題
[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
4 WITH CHECK OPTION;
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)
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
把上面的視圖改成如下：
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' CT FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
3 GROUP BY o.order_id,o.order_date
4 WITH CHECK OPTION;
View created.
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
4 WITH CHECK OPTION;
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)
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
把上面的視圖改成如下：
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' CT FROM orders o JOIN order_items i ON(o.order_id=i.order_id)
3 GROUP BY o.order_id,o.order_date
4 WITH CHECK OPTION;
View created.
正確答案：B
結總：
在創建視圖時，對這種使用各種函數，或運算表達式的列，一定要起別名，如沒有別名視圖創建就會失敗。
定義視圖的列名可以省略，來自SELECT定義中的列名，如果定義視圖的列名不省略，那個列的個數與SELECT定義中的列的個數要一致。
