PL/SQL學習筆記(六)
2024-07-21 02:33:32
供稿：網友
序列：自動產生的唯一值；
一個共享的對象；
典型的用法是作為主鍵的值；
insert into 給主鍵提供值。
替代了應用的代碼；
通過將序列CACHE（預先生成一部分序列號，放入到內存中）到內存中，可以加速對序列的訪問。 CREATE SEQUENCE sequence_name
[increment by n]//步長
[start with n]//起始點
[maxvalue n/nomaxvalue]//遞增
[minvalue n/nominvalue]//遞減
[cycle/nocycle]//循環
[cache n/nocache];//n為預先生成序列號的個數，默認為20。查詢序列：
user_sequences
last_number 序列將要產生的下一個號是多少；select sequence_name,min_value,max_value,increment_by last_number from user_sequences;偽列：NEXTVAL 引用下一個可用的序列值，不同的用戶每次引用都會獲得一個唯一的值。
CURRVAL 得到當前的值（剛被領走的號）。
在CURRVAL執行前必須先通過NEXTVAL得到一個初始的值。序列名.NEXTVAL/CURRVAL
序列發生間隙是正常的，保證唯一即可。
序列的修改：
ALTER SEQUENCE sequence_name
increment by
maxvalue
cycle
cache;start with 不能修改。刪除序列：drop SEQUENCE sequence_name;
索引： 一個方案中的對象；
被Oracle服務器用來加速對表的查詢；
通過使用快速路徑訪問方法快速定位數據；
與表獨立存放；
被ORACLE服務器使用和維護。一定是 WHERE 條件的才有可能使用索引。
手動創建索引:
CREATE INDEX index_name on table_name (col_name);考慮創建索引的情況：
*、包含了大量不同值的列；
*、包含了大量空值的列；
*、一個或者多個列經常被一起出現在 WHERE 條件中或者作為連接的條件出現；
*、表的數據量很大，而且對表的查詢經常是得到表中數據的2%到4%（少量數據）.
不應該創建索引的情況：
*、一個很小的表；
*、列很少被用于查詢的條件；
*、表上的大多數查詢是得到大量數據的；
*、表中的數據經常發生變動；
*、要被索引的列被作為條件表達式的一部分。
查看：
user_indexes得到索引的定義和唯一性。user_ind_columns 得到索引的名稱，表名和列名。
select ic.index_name,ic.column_name,ic.column_position,
ic.uniquenes
from user_indexes ix,user_ind_columns ic
where ic.index_name=ix.index_name and ic.table_name='table_name';刪除索引：
DROP INDEX index_name;
為了刪除索引，必須擁有索引或者擁有DROP ANY INDEX 權限。 控制用戶的訪問數據庫的安全性系統安全性：
系統權限(system PRivilege)，獲得訪問數據庫的能力。
超過一百個
創建新用戶：
CREATE USER user_name IDENTIFIED BY passWord;
刪除用戶
刪除表
授予權限：
GRANT priv_list TO user/public/role(角色);
grant create session ,create table,create sequence to user_name;
在授予建表權限的同時也應該賦予存儲空間。
分配配額：ALTER USER user_name QUOTA nM ON space_name;
數據安全性：
對象權限(object privilege)，獲得維護數據庫的能力。
每種對象的權限都不相同。
對象的所有者擁有對象的所有權限。
對象的所有者可以將自己的對象權限賦予其他人。 GRANT object_priv_list [(col_list)]
ON owner.object TO user/role/public
[WITH GRANT OPTION];--將權限授予用戶的同時，該用戶也擁有了授予其他用戶對象權限的功能。
（及聯授予）會導致及聯移除。 移除權限：
REVOKE priv_list/all ON object FROM user;
方案：數據庫對象的集合，包括表、視圖、序列.....。
角色：
CREATE ROLE role_name;
GRANT priv_list TO role_name;
GRANT role_name TO user_list/role_list;修改口令：
方法 （1）ALTER USER user_name IDENTIFIED BY password;
（2）password + 回車
USER_SYS_PRIVS 當前用戶的系統權限。
USER_ROLE_PRIVS 當前用戶的角色權限。
USER_tab_privs_made 用戶對象被授予的他人的信息。
使用集合操作UNION select employee_id,job_id from employees
union
select employee_id,job_id from job_history;兩個表的并集，但不顯示重復行。
執行的時候要先排序再剔重，所以結果集是有序的。union all也是兩個表的并集，而且顯示重復行。
語法同上。intersectselect employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;
minusselect employee_id,job_id from employees
minus
select employee_id,job_id from job_history;e-j=e-e與j的交集；
j-e=j-j與e的交集；注：
在select 列表中的表達式必須有同樣的數目和類型。 匹配 SELECT 語句
select employee_id,job_id,salary from employees
union
select employee_id,job_id,0 from job_history; 括號可以用拉修改序列的執行順序。ORDER BY 子句：
只能在整個集合的最后出現；
可以按照第一個 SELECT 語句中的列名，別名或者位置號排序。
GROUP BY 子句的增強CUBE 操作符的 GROUP BY 在 GROUP BY 子句中使用 ROLLUP 或者 CUBE 來產生分組小計；
ROLLUP 分組產生包括規則的分組結果和小計的結果的組合；
GROUP BY [ROLLUP]（col_name_list）
ROLLUP:
a ab abc abc
ab ab
a a a
all all allCUBE 分組產生包括 ROLLUP 產生的結果和交叉分組小計。
cube:a ab abca ab abc
all a ab
b ac
all bc
a
b
c
allGROUPING 函數
參數一定是在CUBE或ROLLUP里進行分組排序的字段或表達式之一。通過1或0來判定結果集中的空值是由于本身列的值是空的，還是由于使用CUBE或ROLLUP產生的空值。
1 代表是由于分組產生的空值，沒有參與分組。
0 代表是由于列本身產生的空值，參與了分組，但分組中沒有包含它。GROUPING SETS可以使用 GROUPING SETS 在同一個語句中定義多個組集。只需要訪問一次基表。
不需要寫很復雜的UNION語句。
GROUPING SETS 子句中組合的元素越多，語句的執行性能就越好。group by GROUPING SETS((abc),(ab),(bc),(a),(b))
組合列：是一個列的組合，在分組計算時被作為一個單元處理。
高級子查詢成對子查詢：
行內視圖的性能比成對子查詢的性能高。相關子查詢：
主查詢的字段在子查詢里做條件（特征）。
主查詢先執行，取出第一條數據，把該數據傳入子查詢做比較，返回查詢結果給主查詢，主查詢根據這個結果再做查詢
依次類推
直到主查詢中沒有可查詢列為止。
EXISTS操作符EXISTS 操作符測試子查詢的結果是否存在；
返回 TRUE 或 FALSE
查詢機制：
假如一個子查詢找到了結果：在內部子查詢中不在繼續執行
條件被設為TRUE假如一個子查詢沒有找到結果：
條件被設為FALSEselect col_list from table_name tab_aliaswhere exists (select 'x' from table_name where col=tab_alias.col);
用的是相關子查詢NO EXISTS操作符和NOT IN 相對應，速度要快，性能好。UPDATE 中的相關子查詢update emp e
set department_name in(select d.department_name from departments d where e.department_id=d.department_id);delete 中的相關子查詢
