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 中的相关子查询
