OCP課程12：SQL之建立其他模式物件
課程目標：
- 建立簡單和複雜檢視
- 從檢視獲取資料
- 建立、維護及使用序列
- 建立和維護索引
- 建立私有和公有同義詞
資料庫物件：
1、檢視
檢視的優勢：
- 遮蔽對敏感資訊的訪問
- 簡化複雜的查詢
- 根據不同的使用者需求建立不同的檢視
- 資料的獨立性，透過檢視實現介面的功能
簡單檢視和複雜檢視
- 簡單檢視是指檢視資料來源於一張表，不包含函式及分組，可以進行DML操作，對簡單檢視進行操作，實際上就是對基表進行操作，構成檢視的表叫基表
- 複雜檢視是指檢視來源於一張表或者多張表，可能包含分組和函式，不一定能進行DML操作
![clipboard[1]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913201XxTi.png "clipboard[1]")
建立檢視的語法：
![clipboard[2]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913202VHba.png "clipboard[2]")
（1）建立簡單檢視
例子：使用人員表裡面80部門的人員資訊建立一個檢視
SQL> create view empvu80
2 as
3 select employee_id,last_name,salary from employees where department_id=80;
View created.
查詢檢視結構
SQL> desc empvu80;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
例子：在子查詢中使用列別名建立檢視
SQL> create view salvu50
2 as
3 select employee_id id_number,last_name name,salary*12 ann_salary
4 from employees where department_id=50;
View created.
SQL> desc salvu50;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_NUMBER NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(25)
ANN_SALARY NUMBER
與使用子查詢建立表類似，如果子查詢裡面有表示式或者函式，必須要用別名，不然要報錯，或者在檢視後面帶上欄位。
查詢檢視與查詢表一樣，都是使用select語句，而且效能沒有影響
例子：檢視查詢檢視的執行計劃
SQL> conn / as sysdba
Connected.
SQL> set autotrace on
SQL> select * from hr.salvu50;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 855 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 45 | 855 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
執行計劃與單獨執行子查詢一樣
SQL> select employee_id id_number,last_name name,salary*12 ann_salary
2 from hr.employees where department_id=50;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 855 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 45 | 855 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
使用create or replace view修改檢視，修改後源檢視的許可權保留，不需要再進行授權，而刪除重建需要重新進行授權。
例子：修改前面建立的檢視empvu80
SQL> create or replace view empvu80(id_number,name,sal,department_id)
2 as
3 select employee_id,first_name||' '||last_name,salary,department_id
4 from employees where department_id=80;
View created.
（2）建立複雜檢視
例子：建立一個包含組函式，資料來自於employees表和departments表的複雜檢視，顯示每個部門的部門名稱，最小薪水，最大薪水和平均薪水
SQL> create or replace view dept_sum_vu(name,minsal,maxsal,avgsal)
2 as
3 select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
4 from employees e join departments d
5 on e.department_id=d.department_id
6 group by d.department_name;
View created.
（3）檢視上面進行DML操作的規則
- 在簡單檢視上面可以進行DML操作
- 檢視如果包含組函式，group by子句，distinct關鍵字，rownum偽列，不能刪除檢視裡面的記錄
- 檢視如果包含組函式，group by子句，distinct關鍵字，rownum偽列，表示式定義的列，不能修改檢視裡面的資料
- 檢視如果包含組函式，group by子句，distinct關鍵字，rownum偽列，表示式定義的列以及存在於基表（沒有定義預設值）但是不在檢視中的非空列，不能透過檢視增加資料
例子：透過簡單檢視刪除資料
SQL> delete from empvu80 where id_number=177;
1 row deleted.
例子：透過包含組函式的複雜檢視刪除資料會報錯
SQL> delete from dept_sum_vu where name='IT';
delete from dept_sum_vu where name='IT'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
例子：透過包含表示式的檢視修改相關資料會報錯
SQL> update empvu80 set name='aaa' where id_number=179;
update empvu80 set name='aaa' where id_number=179
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
例子：建立的檢視不包含基表的非空欄位，且非空欄位沒有預設值，則透過檢視進行插入資料會報錯
先建立一個基表，包含非空欄位
SQL> create table emp as select employee_id,last_name,salary,job_id from employees where 1=0;
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
JOB_ID NOT NULL VARCHAR2(10)
再建立一個檢視，不包含所有非空欄位
SQL> create view v_emp as
2 select employee_id,salary from emp;
View created.
SQL> desc v_emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
SALARY NUMBER(8,2)
插入資料包錯
SQL> insert into v_emp values(100,1000);
insert into v_emp values(100,1000)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMP"."LAST_NAME")
修改表定義，為非空欄位增加預設值
SQL> alter table emp modify(last_name default 'stone',job_id default 'IT');
Table altered.
再次插入資料
SQL> insert into v_emp values(100,1000);
1 row created.
查詢基表確認，已正確插入了
SQL> select * from emp;
EMPLOYEE_ID LAST_NAME SALARY JOB_ID
----------- ------------------------- ---------- ----------
100 stone 1000 IT
（4）使用with check option語句
使用with check option語句增加約束，可以確保使用者的DML操作只能在檢視的範圍內進行（就是不能超出where條件）。不然，使用者修改了資料導致該資料不滿足建立檢視子查詢的where條件，則下次查詢就看不到了。
例子：不使用with check option語句和使用with check option語句的區別
先建立一個檢視不使用with check option語句
SQL> create or replace view empvu20 as
2 select employee_id,last_name,salary,department_id from employees where department_id=20;
View created.
SQL> select * from empvu20;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
201 Hartstein 13000 20
202 Fay 6000 20
對其中一條記錄進行修改，將部門編號修改為10
SQL> update empvu20 set department_id=10 where employee_id=202;
1 row updated.
再次查詢，剛才修改的記錄看不到了
SQL> select * from empvu20;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
201 Hartstein 13000 20
為了避免這種情況，使用with check option語句建立檢視
SQL> create or replace view empvu20 as
2 select employee_id,last_name,salary,department_id from employees where department_id=20
3 with check option constraint empvu20_ck;
View created.
再來進行修改就會報錯
SQL> update empvu20 set department_id=10 where employee_id=201;
update empvu20 set department_id=10 where employee_id=201
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
（5）使用with read only語句
使用with check option語句將檢視設定為只讀，執行DML操作會報錯
例子：建立一個只讀的檢視
SQL> create or replace view empvu10(employee_number,employee_name,job_title) as
2 select employee_id,last_name,job_id from employees
3 where department_id=10
4 with read only;
View created.
執行刪除操作報錯
SQL> delete from empvu10;
delete from empvu10
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
（6）刪除檢視
刪除檢視的語法：
![clipboard[3]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_14499132037MTf.png "clipboard[3]"){kind=small}
刪除檢視不會刪除基表的資料
SQL> drop view empvu80;
View dropped.
2、序列
- 自動產生唯一數字
- 是一個共享的物件
- 可以用於生成主鍵的值
- 替換程式程式碼
- 使用快取提高效率
（1）建立序列
建立序列的語法：
![clipboard[4]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913203tBbG.png "clipboard[4]")
例子：建立一個序列，增長10，從120開始，最大達到9999，沒有cache，沒有迴圈
SQL> create sequence dept_deptid_seq
2 increment by 10
3 start with 120
4 maxvalue 9999
5 nocache
6 nocycle;
Sequence created.
（2）使用序列
使用序列需要使用nextval和currval偽列
- nextval返回下一個可用的序列值
- currval返回序列的當前值
- 在當前會話中，如果要使用currval，需要先使用nextval
可以在以下情況使用nextval和currval
- 在select statement的select list中（不能在子查詢的select list）
- 在insert statement的子查詢select list中
- 在insert statement的value語句中
- 在update statement的set語句中
不可以在以下情況使用nextval和currval
- 檢視的select list
- 帶有distinct關鍵字的select statement
- 帶有group by、having、order by語句的select statement
- 在select、delete、update的子查詢
- 在create table或者alter table語句的default expression中
例子：序列的使用
先使用currval報錯
SQL> select dept_deptid_seq.currval from dual;
select dept_deptid_seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence DEPT_DEPTID_SEQ.CURRVAL is not yet defined in this session
先使用nextval，獲取下一個可用的序列值
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
120
再使用currval，取上一次nextval取的值
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
120
新開一個會話視窗，使用currval報錯
SQL> conn hr/hr
SQL> select dept_deptid_seq.currval from dual;
select dept_deptid_seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence DEPT_DEPTID_SEQ.CURRVAL is not yet defined in this session
還是需要先使用nextval才能使用currval
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
130
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
130
再在此會話中執行兩次nextval
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
140
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
150
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
150
而先前會話中的currval保持不變
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
120
在先前會話再次執行nextval，就應該是160了
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
160
例子：使用序列生成的值作為表的主鍵
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
270
SQL> insert into departments(department_id,department_name,location_id)
2 values(dept_deptid_seq.nextval,'Support',2500);
1 row created.
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
280
（3）快取序列值
為了提高訪問速度，在建立序列的時候使用cache關鍵字指定快取序列的個數，但是這有可能會導致序列出現gap，因為在系統崩潰後，快取的序列都會被清除。
序列不能回滾，在實際當中，如果使用序列進行了插入，最後沒有提交，而是回滾，那麼中間這些序列值就沒有了，也會出現了gap。
SQL> rollback;
Rollback complete.
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
280
如果一個序列多個表都在使用，對單個表來說，也會出現gap，所以在實際當中，我們一般為一個表建立一個序列。
（4）修改序列
可以修改序列的增長值，最大值，最小值，迴圈選項，快取選項，但是不能修改起始值，如果不設定最小值，預設為1。
修改注意事項：
- 要有修改的許可權
- 隻影響後續的序列
- 修改起始值只能刪除重建
例子：修改序列
SQL> alter sequence dept_deptid_seq
2 increment by 20
3 maxvalue 300
4 nocache
5 nocycle;
Sequence altered.
超過最大值報錯
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
290
SQL> select dept_deptid_seq.nextval from dual;
select dept_deptid_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence DEPT_DEPTID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be
instantiated
修改序列為可以迴圈
SQL> alter sequence dept_deptid_seq
2 cycle;
Sequence altered.
超過最大值後從起始值開始，預設為1。
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
1
（5）刪除序列
例子：刪除序列
SQL> drop sequence dept_deptid_seq;
Sequence dropped
3、索引
- 是一個模式物件
- 用於提高查詢的速度
- 快速定位資料，減少物理I/O
- 和表獨立的，是針對某個欄位來建索引，建立和刪除索引對基表無影響，但是刪除表後，表的索引也會被刪除
- 由Oracle server自動維護
（1）建立索引
建立索引的2種方式
- 自動建立，當建立主鍵約束或者唯一約束的時候自動建立一個唯一索引
- 手工建立，建立非唯一索引提高訪問速度
建立索引的語法：
![clipboard[5]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913204Fsw0.png "clipboard[5]")
例子：在人員表的last_name欄位建立一個索引
SQL> create index emp_last_name_idx on employees(last_name);
Index created.
建立索引的注意事項：
![clipboard[6]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913205Q3NC.png "clipboard[6]")
例子：透過檢視執行計劃，看是否使用了索引
SQL> explain plan for
2 select * from employees where last_name='Abel';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 350933353
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_LAST_NAME_IDX | 1 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='Abel')
14 rows selected.
如果在查詢的時候，使用了表示式，則不會用到索引
SQL> explain plan for
2 select * from employees where last_name||''='Abel';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("LAST_NAME"||''='Abel')
13 rows selected.
這是遮蔽索引的一種方法，建立索引後不想用，又不想刪除，如果是數字型別，可以加一個0，如果是字元型別，拼接一個空，那麼就可以遮蔽這個欄位對索引的使用。
（3）監控索引的使用
建立索引後，可以透過monitoring usage監控索引的使用，然後透過v$object_usage檢視檢視在監控的這一段時間內，是否有用到該索引，如果沒有用到，就可以將其刪除，提高更新插入的效能。
監控索引
SQL> alter index emp_last_name_idx monitoring usage;
Index altered.
已經在監控，但是還沒有使用
SQL> select index_name,monitoring,used from v$object_usage;
INDEX_NAME MON USE
------------------------------ --- ---
EMP_LAST_NAME_IDX YES NO
查詢一次
SQL> select last_name from employees where last_name='Abel';
LAST_NAME
-------------------------
Abel
已經使用索引了
SQL> select index_name,monitoring,used from v$object_usage;
INDEX_NAME MON USE
------------------------------ --- ---
EMP_LAST_NAME_IDX YES YES
SQL> alter index emp_last_name_idx nomonitoring usage;
Index altered.
停止監控
SQL> select index_name,monitoring,used from v$object_usage;
INDEX_NAME MON USE
------------------------------ --- ---
EMP_LAST_NAME_IDX NO YES
（4）刪除索引
索引不能修改，只能刪除重建，刪除索引需要是索引的所有者或者有DROP ANY INDEX許可權。
刪除索引的語法：
![clipboard[7]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913206O5Jq.png "clipboard[7]")
例子：刪除索引
SQL> drop index emp_last_name_idx;
Index dropped.
4、同義詞
同義詞是資料庫模式物件的一個別名，經常用於簡化物件訪問和提高物件訪問的安全性。在使用同義詞時，Oracle資料庫將它翻譯成對應模式物件的名字。與檢視類似，同義詞並不佔用實際儲存空間，只有在資料字典中儲存了同義詞的定義。在Oracle資料庫中的大部分資料庫物件，如表、檢視、同義詞、序列、儲存過程、包等等，都可以根據實際情況為他們定義同義詞。
同義詞的分類
- 公用Oracle同義詞：由一個特殊的使用者組Public所擁有。顧名思義，資料庫中所有的使用者都可以使用公用同義詞。公用同義詞往往用來標示一些比較普通的資料庫物件，這些物件往往大家都需要引用。
- 私有Oracle同義詞：它是跟公用同義詞所對應，他是由建立他的使用者所有。當然，這個同義詞的建立者，可以透過授權控制其他使用者是否有權使用屬於自己的私有同義詞。
同義詞作用
- 如果沒有同義詞，當操作其他使用者的表時，必須透過user名.object名的形式，採用了同義詞之後就可以不加所有者名稱，還要注意使用者是否有這個許可權。
- 如果建立的表的名字很長，可以為這個表建立一個同義詞來簡化引用。
建立同義詞的語法：
![clipboard[8]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913207zMZm.png "clipboard[8]")
例子：建立及刪除一個同義詞
SQL> create synonym d_sum for dept_sum_vu;
Synonym created.
SQL> drop synonym d_sum;
Synonym dropped.
5、相關習題
（1） Evaluate the CREATE TABLE statement: CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15))? Which statement is true regarding the PROD_ID_PK constraint?
A.It would be created only if a unique index is manually created first.
B.It would be created and would use an automatically created unique index.
C.It would be created and would use an automatically created nonunique index.
D.It would be created and remains in a disabled state because no index is specified in the command.
答案：B
（2）Evaluate the following CREATE SEQUENCE statement:CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE The sequence SEQ1 has generated numbers up to the maximum limit of 200.You issue the following SQL statement: SELECT seq1.nextval FROM dual . What is displayed by the SELECT statement?
A.1
B.10
C.100
D.an error
答案：A
（3）View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. Which CREATE VIEW statement would create the view successfully?
A.CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 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) GROUP BY o.order_id,o.order_date?
B.CREATE OR REPLACE VIEW ord_vu 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) GROUP BY o.order_id,o.order_date?
C.CREATE OR REPLACE VIEW ord_vu 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) GROUP BY o.order_id,o.order_date?
D.CREATE OR REPLACE VIEW ord_vu 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) GROUP BY o.order_id,o.order_date WITH CHECK OPTION?
答案：B
（4）View the Exhibit and examine the structure of the ORDERS table: The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint. Evaluate the following statement: INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS WHERE order_total = 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101)? What would be the outcome of the above INSERT statement?
![clipboard[9]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913208c4rH.png "clipboard[9]")
A.It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
B.It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
C.It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D.It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
答案：C
（5）Which two statements are true regarding views ?(Choose two.)
A.A simple view in which column aliases have been used cannot be updated.
B.A subquery used in a complex view definition cannot contain group functions or joins.
C.Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
D.Rows added through a view are deleted from the table automatically when the view is dropped.
E.The OR REPLACE option is used to change the definition of an existing view without dropping and re?-creating it.
F.The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
答案：CE
（6）Which statements are correct regarding indexes? (Choose all that apply.)
A.When a table is dropped, the corresponding indexes are automatically dropped.
B.For each DML operation performed, the corresponding indexes are automatically updated.
C.Indexes should be created on columns that are frequently referenced as part of an expression.
D.A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique
index.
答案：ABD
（7）View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). Which two CREATE INDEX statements are valid? (Choose two.)
![clipboard[10]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_1449913209JlLU.png "clipboard[10]")
A.CREATE INDEX ord_idx ON ord(ord_no);
B.CREATE INDEX ord_idx ON ord_items(ord_no);
C.CREATE INDEX ord_idx ON ord_items(item_no);
D.CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty);
答案：BC
（8）Which two statements are true about sequences created in a single instance database? (Choose two.)
A.The numbers generated by a sequence can be used only for one table.
B.DELETE would remove a sequence from the database.
C.CURRVAL is used to refer to the last sequence number that has been generated.
D.When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
E.When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
答案：ＣＤ
（9）The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord；
A.CREATE SYNONYM ord FOR orders；This command is issued by OE.
B.CREATE PUBLIC SYNONYM ord FOR orders； This command is issued by OE.
C.CREATE SYNONYM ord FOR oe.orders；This command is issued by the database administrator.
D.CREATE PUBLIC SYNONYM ord FOR oe.orders；This command is issued by the database administrator.
答案：D
（10）Which statement is true regarding synonyms?
A.Synonyms can be created for tables but not views.
B.Synonyms are used to reference only those tables that are owned by another user.
C.A public synonym and a private synonym can exist with the same name for the same table.
D.The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
答案：C
（11）Evaluate the following command: CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2))；You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name,job_id FROM employees WHERE job_id LIKE 'SA_%' WITH CHECK OPTION；Which statements are true regarding the above view? (Choose all that apply.)
A.It allows you to insert details of all new staff into the EMPLOYEES table.
B.It allows you to delete the details of the existing sales staff from the EMPLOYEES table.
C.It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.
D.It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.
答案：BD
（12）Evaluate the following SQL statement: CREATE INDEX upper_name_idx ON product_information(UPPER(product_name))； Which query would use the UPPER_NAME_IDX index ？
A.SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254；
B.SELECT UPPER(product_name) FROM product_information ；
C.SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'Cable') ；
D.SELECT product_id, UPPER(product_name) FROM product_information WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000 ；
答案：C
（13）View the Exhibit and examine the structure of the ORD table. Evaluate the following SQL
statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, '25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
答案：A
（14）ORD is a private synonym for the OE.ORDERS table. The user OE issues the following command: DROP SYNONYM ord；Which statement is true regarding the above SQL statement？
A.Only the synonym would be dropped.
B.The synonym would be dropped and the corresponding table would become invalid.
C.The synonym would be dropped and the packages referring to the synonym would be dropped.
D.The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
答案：A
（15）View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. Which view can have all the DML operations performed on it？
![clipboard[11]](http://img.blog.itpub.net/blog/attachment/201512/12/28536251_144991321066HK.png "clipboard[11]")
A.CREATE VIEW V1 AS SELECT order_id, product_id FROM order_items；
B.CREATE VIEW V4(or_no, or_date, cust_id) AS SELECT order_id, order_date, customer_id FROM orders WHERE order_date < '30-mar-2007' WITH CHECK OPTION ；
C.CREATE VIEW V3 AS SELECT o.order_id, o.customer_id, i.product_id FROM orders o, order_items i WHERE o.order_id=i.order_id；
D.CREATE VIEW V2 AS SELECT order_id, line_item_id, unit_price*quantity total FROM order_items ；
答案：B
（16）ORD is a private synonym for the OE.ORDERS table. The user OE issues the following command: DROP SYNONYM ord；Which statement is true regarding the above SQL statement？
A.Only the synonym would be dropped.
B.The synonym would be dropped and the corresponding table would become invalid.
C.The synonym would be dropped and the packages referring to the synonym would be dropped.
D.The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
答案：A
（17）Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. A subquery used in a complex view definition cannot contain group functions or joins.
C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
D. Rows added through a view are deleted from the table automatically when the view is dropped.
E. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view
答案：CE
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-1871039/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OCP課程15：SQL之管理模式物件SQL模式物件
- OCP課程13：SQL之使用資料字典檢視管理物件SQL物件
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- OCP課程56：管理II之SQL調優SQL
- OCP課程21：SQL之正規表示式SQL
- OCP課程9：SQL之使用集合運算子SQL
- OCP課程8：SQL之使用子查詢SQL
- OCP課程6：SQL之使用組函式SQL函式
- OCP課程16：SQL之處理大資料SQL大資料
- OCP課程5：SQL之使用單行函式SQL函式
- OCP課程4：SQL之限制和排序資料SQL排序
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- OCP課程14：SQL之控制使用者訪問SQL
- OCP課程19：SQL之使用子查詢檢索資料SQL
- OCP課程20：SQL之分層查詢SQL
- OCP課程7：SQL之多表查詢SQL
- OCP課程18：SQL之管理不同時區下的資料SQL
- OCP課程3：SQL之使用SELECT語句檢索資料SQL
- OCP課程27：管理Ⅰ之管理ASM例項ASM
- OCP課程39：管理Ⅰ之移動資料
- OCP課程58：管理II之自動任務
- OCP課程24：管理Ⅰ之資料庫安裝資料庫
- OCP課程42：管理II之核心概念和工具
- OCP課程54：管理II之管理記憶體記憶體
- OCP課程45：管理II之備份設定
- OCP課程17：SQL之透過分組相關資料產生報告SQL
- OCP課程60：管理Ⅰ之管理資料庫空間資料庫
- OCP課程61：管理II之複製資料庫資料庫
- OCP課程26：管理Ⅰ之管理資料庫例項資料庫
- OCP課程50：管理II之診斷資料庫資料庫
- 物件建立模式物件模式
- OCP課程23：管理Ⅰ之資料庫體系結構資料庫
- OCP課程48：管理II之使用RMAN執行恢復
- OCP課程51：管理II之使用閃回技術1
- OCP課程52：管理II之使用閃回技術2
- OCP課程53：管理II之使用閃回資料庫資料庫
- Java物件建立模式Java物件模式
- OCP課程47：管理II之還原和恢復任務