PL/SQL学习笔记(六)

　　 

序列：

自动产生的唯一值；
一个共享的对象；
典型的用法是作为主键的值；
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

也是两个表的并集，而且显示重复行。
语法同上。

intersect

select employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;

minus

select 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 all

CUBE 分组产生包括 ROLLUP 产生的结果和交叉分组小计。
cube:

a ab abc

a ab abc
all a ab
 b ac
 all bc
  a
  b
  c
  all

GROUPING 函数
参数一定是在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

如果一个子查询没有找到结果：
条件被设为FALSE

select col_list from table_name tab_alias

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

原文转自：http://www.ltesting.net