【安博培训技术】Oracle4 事务和数据库对象20130910

Oracle4 事务和数据库对象


回顾
SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符
SQL 函数可大致分为：
单行函数：日期、字符、数字、转换、其他
聚合函数
分析函数


目标
事务
使用同义词
使用序列
理解并使用视图
理解并使用索引


事务管理
例如，银行转帐问题：
    假定资金从帐户A转到帐户B，至少需要两步：
帐户A的资金减少
然后帐户B的资金相应增加
CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY     --当前余额
)
GO
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney    
       CHECK(currentMoney>=1)
GO
INSERT INTO bank(customerName,currentMoney)
        VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)


事务控制
事务是最小的工作单元，作为一个整体进行工作
事务的原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
保证事务的整体成功或失败，称为事务控制
用于事务控制的语句有：
COMMIT - 提交并结束事务处理
ROLLBACK -  撤销事务中已完成的工作
SAVEPOINT – 标记事务中可以回滚的点


数据库对象简介 
Oracle 数据库对象又称模式对象
数据库对象是逻辑结构的集合，最基本的数据库对象是表
其他数据库对象包括：
数据库对象 　
同义词 序列 视图 索引


同义词
同义词是现有对象的一个别名。
简化SQL语句
隐藏对象的名称和所有者
提供对对象的公共访问
同义词共有两种类型：
私有同义词 公有同义词
私有同义词只能在其模式内访问，且不能与当前模式的对象同名。
[说明]使用情况：频繁引用
创建条件：在自身的模式创建私有同义词，用户需具有create synonym系统权限
      在其它用户模式创建私有同义词，用户需具有create any synonym系统权限
私有同义词
CREATE SYNONYM emp FOR SCOTT.emp;
emp SCOTT.emp的别名
SCOTT 模式名
emp 表名


公有同义词
CREATE PUBLIC SYNONYM syn_emp FOR SCOTT.emp;
syn_emp 同义词名称


私有同义词：只能被当前模式的用户访问
共有同义词：需有create public synonym权限
replace：若同义词已存在，替换该同义词


创建或替换现有的同义词
CREATE OR REPLACE SYNONYM syn_emp FOR SCOTT.emp;
OR REPLACE 替换现有的同义词


删除同义词
SQL> DROP SYNONYM emp; 
SQL> DROP PUBLIC SYNONYM syn_emp; 
同义词TAB：当前用户所拥有的表和视图对象
本地对象和共有对象重名时，本地对象优先
数据字典视图：user_synonyms、user_tables、user_views
通过这些视图查看当前模式下的数据对象


序列
序列是用于生成唯一、连续序号的对象
序列可以是升序的，也可以是降序的
使用CREATE SEQUENCE语句创建序列
SQL> CREATE SEQUENCE seq_toys
START WITH 10
INCREMENT BY 10
MAXVALUE 2000
MINVALUE 10
NOCYCLE
CACHE 10;
指定内存中预先分配的序号数 
[思路]联系SqlServer中的标示列，引出Oracle中的序列，然后通过例子展示在Oracle中如何实现标示列的功能
increament by：根据增量的正负确定升降序
默认最大值：增序10的27次方，降序-1
cache：预选分配一组序号，用完后再生成一组


访问序列
通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
SQL> INSERT INTO toys (toyid, toyname, toyprice) 
     VALUES (seq_toys.NEXTVAL, ‘TransFormer’, 500);
SQL> INSERT INTO toys
     VALUES ( toys_seq.NEXTVAL, ’Plane’, 75);
指定序列的下一个值
SQL> SELECT seq_toys.CURRVAL FROM dual;
检索序列的当前值


更改和删除序列
使用ALTER SEQUENCE语句修改序列，
不能更改序列的START WITH参数
SQL> ALTER SEQUENCE seq_toys MAXVALUE 5000 CYCLE;
使用DROP SEQUENCE语句删除序列
SQL> DROP SEQUENCE seq_toys;


视图
视图以经过定制的方式显示来自一个或多个表的数据
视图可以视为“虚拟表”或“存储的查询”
创建视图所依据的表称为“基表”
视图的优点有：
提供了另外一种级别的表安全性
隐藏的数据的复杂性
简化的用户的SQL命令
隔离基表结构的改变
通过重命名列，从另一个角度提供数据


创建视图 3-1
CREATE VIEW stud_view
AS SELECT studno, studname, subno
FROM stud_details;
[初始化表及数据]
Create table sub_details(
  subno int primary key,
  subname varchar2(20)
);
Create table stud_details(
  studno int primary key,
  studname  varchar2(20) not null,
  studmarks int,
  subno int references sub_details(subno)
);
Insert into sub_details values(2,'English');
Insert into sub_details values(4,'Maths');
Insert into sub_details values(5,'Science');
Insert into stud_details values(1,'Rob',45,2);
Insert into stud_details values(2,'James',33,4);
Insert into stud_details values(3,'Jesica',40,4);


创建视图 3-2
创建视图的语法：
 CREATE [OR REPLACE] [FORCE] VIEW
    view_name [(alias[, alias]...)] 
  AS select_statement
  [WITH CHECK OPTION]
  [WITH READ ONLY];
[说明] 
force：无论基表是否存在都创建视图
with read only：不能通过视图修改数据


创建视图 3-3
使用 WITH CHECK OPTION 选项创建视图
CREATE OR REPLACE VIEW v_stu AS
SELECT * FROM t_student WHERE f_class = 1
WITH CHECK OPTION CONSTRAINT chk_class1;
使用 ORDER BY 子句创建视图
CREATE OR REPLACE VIEW v_stusorted AS
SELECT * FROM t_student ORDER BY f_class;
创建带有错误的视图
CREATE FORCE VIEW v_temp AS
SELECT * FROM t_temp;
[说明] 
with check option：若没有此选项，则可将符合条件的记录（在视图中）改为视图中不可见的记录
如果选择特定的列来创建视图，则通过该视图只能更新这些列（与表的近似之处）


联接视图 2-1
CREATE VIEW v_stud AS
SELECT studno, studname, studmarks, subname
FROM stud_details, sub_details 
WHERE stud_details.subno=sub_details.subno;


联接视图 2-2
创建外联接视图
CREATE VIEW v_coursecount AS
SELECT stu.f_id, f_name, count(f_courseid) as coursecount
FROM   t_student stu, t_grade gra
WHERE  stu.f_id = gra.f_stuid(+)
GROUP BY stu.f_id;


SELECT stu.f_id, f_name, count(f_courseid) as coursecount
FROM   t_student stu left join t_grade gra 
         on (stu.f_id = gra.f_stuid)
GROUP BY stu.f_id, f_name;
注意外连接的写法：from T1 left join T2 on T1.f=T2.f  <=>  from T1,T2 where T1.f=T2.f(+)


视图上的DML语句
在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
视图上的DML语句有如下限制：
只能修改一个底层的基表
如果修改违反了基表的约束条件，则无法更新视图
如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图
如果视图包含伪列或表达式，则将无法更新视图
[说明]说明视图上的DML语句为什么有如下限制，并适当举例2-3种情况
[强调]视图上的DML语句并非修改了视图中的数据，而是通过对视图的操作最终修改的是基表的数据，这一点一定要明确
如何利用视图对基表进行写操作（一般不通过视图修改数据）
类似于表的地方以及不同
只要修改能够确定的对应于唯一的基表数据，修改就是可行的


键保留表
stud_details 为什么是键保留表？
因为 studno 既是stud_details 中的主键，也是联接结果中的主键 
[思路]
如何保证对视图的修改最后影射到唯一的基表数据 
键保留表：基表的主键亦是视图的主键
可修改键保留表中的值（只有键保留表才能使用DML）
思考：Oracle相比于SQLServer的高明之处（明确，规范，近乎死板）


视图中的函数
视图中可以使用单行函数、分组函数和表达式
CREATE VIEW item_view AS 
SELECT itemcode, LOWER(itemdesc) item_desc
FROM itemfile; 
使用DROP VIEW语句删除视图
SQL> DROP VIEW toys_view; 


索引 3-1
索引是与表相关的一个可选结构
用以提高 SQL 语句执行的性能
减少磁盘I/O
使用 CREATE INDEX 语句创建索引
在逻辑上和物理上都独立于表的数据
Oracle 自动维护索引
[说明]重点讲解什么是索引，索引的机制，这对查询优化很重要
[思路]如何才能更快的查找到需要的纪录有序索引索引的存储及原理


索引 3-2
索引有各种类型，除了标准索引外，还有一些特殊类型的索引：
唯一索引 位图索引 组合索引 基于函数的索引 反向键索引


索引 3-3
创建标准索引
SQL> CREATE INDEX x_name ON t_student (f_name)
     TABLESPACE index_tbs;
重建索引
SQL> ALTER INDEX x_name REBUILD
删除索引
SQL> DROP INDEX x_name; 
[说明]掌握索引的基本用法
Rebuild性能优于Drop Index和Create Index语句重建索引


唯一索引
唯一索引确保在定义索引的列中没有重复值
Oracle 自动在表的主键列上创建唯一索引
使用CREATE UNIQUE INDEX语句创建唯一索引
SQL> CREATE UNIQUE INDEX x_id
     ON itemfile (f_id);


组合索引
组合索引是在表的多个列上创建的索引
索引中列的顺序是任意的
如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度
SQL> CREATE INDEX x_comp
     ON t_student(f_name, f_birth);


反向键索引
反向键索引反转索引列键值的每个字节
通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字
SQL> CREATE INDEX rev_index 
     ON t_student (f_birth) REVERSE;
SQL> ALTER INDEX rev_index REBUID NOREVERSE;
[说明]反向键索引和位图索引需了解其使用的范围、如何使用，原理稍加讲解即可，不可深入
索引基于有序数，数据过于密集的情况（参看知识点文档Oracle_06Reverse Index.doc）


位图索引
位图索引适合创建在低基数列上
位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
减少响应时间
节省空间占用
SQL> CREATE BITMAP INDEX bit_index
     ON t_student (f_sex);
[说明]适用范围：某列值重复较多
位图索引中使用每个键值的位图，而不使用Rowid列表，位图中的每个位对应一个可能的Rowid
[说明]更为详细的内容请参考知识点文档：Oracle_07Bitmap Index.txt


基于函数的索引
基于一个或多个列上的函数或表达式创建的索引
表达式中不能出现聚合函数
不能在LOB类型的列上创建
创建时必须具有 QUERY REWRITE 权限
SQL> CREATE INDEX x_lowercase
     ON toys (LOWER(toyname));
SQL> SELECT toyid FROM toys
     WHERE LOWER(toyname)='doll';
例如按照姓名（英文）查询的情况


获取索引的信息
与索引有关的数据字典视图有：
USER_INDEXES － 用户创建的索引的信息
USER_IND_PARTITIONS － 用户创建的分区索引的信息
USER_IND_COLUMNS － 与索引相关的表列的信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
     FROM USER_IND_COLUMNS
     ORDER BY INDEX_NAME, COLUMN_POSITION;
[说明] 常用的数据字典要讲明白，还有其它一些，比如：user_tables,user_views等等


总结
同义词是现有数据库对象的别名
序列用于生成唯一、连续的序号
视图是基于一个或多个表的虚拟表
索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能
索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引