6.1 事务处理
创建一个只包含10部门雇员的一张临时表
CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10;
然后删除编号是7782的一条信息
DELETE FROM emp10 WHERE empno=7782;
从当前窗口查询,可以看到数据已经被删除,但是如果开启第二个SQLplus窗口查询就会发现,这条数据还在,证明没有删除,这就是Oracle中的事务处理的概念。
事务处理:就是保证数据操作的完整性。所有的操作要么同时成功,要么同时失败。
在Oracle中对于每一个连接到数据库的窗口(SQLplus)连接之后实际上都会与数据库的连接建立一个session,即每一个连接到数据库上的用户都表示创建了一个session。
一个session对数据库所做的修改,不会立刻反应到数据库的真实数据之上,是允许回滚的,当一个session提交所有的操作之后,数据库才真正的做出修改。
在数据库的操作中提供了以下的两个主要命令完成事务的处理:
提交事务:commit;
回滚事务:rollback;
在当前窗口commit;提交事务后,第二个窗口内查询数据表,empno为7782的数据被删除
在当前窗口rollback;回滚事务后,则被删除的记录恢复
死锁:一个session如果更新了数据库中的记录,其他session是无法立刻更新的,要等待对方提交之后才允许更新。
比如,在当前窗口删除empno为7782的数据,但是没有提交,则第二个窗口此时对emp10数据表进行操作会一直等待,直到在当前窗口提交事务。同理,在第二个窗口操作完成之后以同样需要提交事务之后,当前窗口才可以对该数据表继续进行操作。
6.2 视图(重点)
功能:视图就是封装了一条复杂的查询语句
创建视图的语法:
CREATE VIEW 名称 AS 子查询;
此时的子查询就表示一条非常复杂的语句
范例:建立一个视图,包含了全部的20部门的雇员信息
CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;
ORA-01031:权限不足
说明scott没有这个权限,所以以超级管理员的身份登录,赋予scott这个权限
CONN sys/change_on_install AS SYSDBA
GRANT CREATE VIEW TO scott;
授权成功
CONN scott/tiger
这样就可以创建视图了:
CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;
视图已创建。
创建视图后就可以像查表那样对视图进行查询操作
SELECT * FROM empv20;
此时是通过视图找到的20部门的所有数据,也就是说可以使用视图包装需要的查询语句。
此时,视图中只包含了四个字段的信息,如果现在希望多包含一个,只能重新建立视图
CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;
ORA-00955:名称已由现有对象使用
此时,执行创建视图的语句之后,提示此名称已经存在,证明视图是无法重名的,那么此时只能先删除在重新建立视图
删除视图的语法:
DROP VIEW 视图名称;
DROP VIEW empv20;
CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;
视图已创建
但是如果所有的代码都这样去写会很麻烦,想要修改视图,就必须先删除视图,所以Oracle中为了方便用户修改视图,提供了一个替换的命令,所以完整的视图创建语法是:
CREATE OR REPLACE VIEW 视图名称 AS 子查询
使用以上的语法,再更改视图的时候就不用先删除了,系统会为用户自动进行删除及重建
CREATE OR REPLACE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;
视图可以封装复杂查询,那么下面封装一个之前已经存在的一个复杂查询
范例:要求查出部门名称、部门员工数、部门的平均工资、部门的最低收入雇员的姓名
SELECT d.dname,ed.c,ed.a,e.ename
FROM dept d,emp e,(
SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min
FROM emp
GROUP BY deptno) ed
WHERE d.deptno=ed.deptno AND e.sal=ed.min;
如果在开发中每次都写这么长的语句,很不方便,因此将其建立成视图,以方便用户查询。以后直接查询视图就可以得到之前的结果。
CREATE OR REPLACE VIEW myemp AS
SELECT d.dname,ed.c,ed.a,e.ename
FROM dept d,emp e,(
SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min
FROM emp
GROUP BY deptno) ed
WHERE d.deptno=ed.deptno AND e.sal=ed.min;
SELECT * FROM myempv;
范例:创建一个只包含20部门雇员的部门信息
CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20;
SELECT * FROM empv20;
下面进行更新视图的操作。在视图中是不应该包含真实数据的,而且在此程序中,创建的视图实际是存在创建条件的,此条件是deptno=20。如果此时把视图中的7369的部门编号修改为30。
范例:修改视图中的7369的部门编号
UPDATE empv20 SET deptno=30 WHERE empno=7369;
SELECT * FROM empv20;
SELECT * FROM emp
此时,已经提示视图更新成功,但是重新查询视图发现视图中已经没有7369这个雇员了,然而emp表中的7369雇员的部门编号已经修改为30了。这样做明显不合适,因为创建视图的时候是有条件的,你一旦修改之后,此条件就被破坏了,所以在创建视图的时候,SQL提供了两个重要的参数:
WITH CHECK OPTION:不能更新视图的创建条件
范例:创建视图时使用WITH CHECK OPTION(首先回退,撤销刚才的修改)
rollback;
SELECT * FROM empv20;
UPDATE empv20 SET deptno=30 WHERE empno=7369;
ORA-01402:视图WITH CHECK OPTION where子句违规
创建条件不能更新,那么其他字段呢?
范例:7369雇员姓名修改为"史密斯"
UPDATE empv20 SET ename= '史密斯' WHERE empno=7369;
SELECT * FROM empv20;
SELECT * FROM emp;
是视图本身作用还是用来查询的,所以不应该允许更改,所以可以使用第二个参数:
WITH READ ONLY:视图只读
范例:创建只读视图
CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp
WHERE deptno=20
WITH READ ONLY;
UPDATE empv20 SET ename='史密斯' WHERE empno=7369;
ORA-01733:此处不允许虚拟列
6.3 序列和同义词
1)序列(重点)
在很多数据库系统中都存在一个自动增长的列,如果要想在Oracle中完成自动增长只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
1、创建序列
创建序列的语法格式为:
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n|NOMAXVALUE]
[MINVALUE n|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
1)INCREMENT BY用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表Oracle序列的值是按照此步长递减的。
2)START WITH 定义序列的初始值(即产生的第一个值),默认为1。
3)MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增Oracle序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
4)MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是10的26次方;对于递增序列,最小值是1。
5)CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
6)CACHE(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。
范例:创建一个myseq的序列,验证自动增长的操作
CREATE SEQUENCE mysql;
序列创建完成以后,所有的自动增长应该有用户自己处理,所以在序列中提供了以下两种操作:
nextVal:取得序列下一个内容
currVal:取得序列的当前内容
范例:建立一张表,以验证序列的操作
CREATE TABLE testsql(
next NUMBER,
curr NUMBER
);
下面向表中添加数据,需要手工使用序列
范例:使用序列
INSERT INTO testseq(next,curr) VALUES (mysql.nextval,myseq.currval);
将以上的语句执行五次,观察序列变化
SELECCT * FROM testseq;
NEXT CURR
---------------- -------------------
1 1
2 2
3 3
4 4
5 5
从结果中发现,nextVal的内容式中在进行自动增长的操作,而curr使用取出当前操作的序列结果。也就是说现在这种序列,每次增长的幅度是1,那么也可以修改增长幅度。使用以下参数:
每次增长幅度:INCREMENT BY 长度;
范例:重新建立序列(先删,后建)
DROP SEQUENCE mysql;
序列已删除
CREATE SEQUENCE mysql INCREMENT BY 2;
序列已创建
序列已经正常创建,现在重建testseq表,并插入数据
DROP TABLE testsql;
表已删除
CREATE TABLE testsql(
next NUMBER,
curr NUMBER
);
表已创建
INSERT INTO testsql(next,curr) VALUES (myseq.nextval,myseq.currval);
已创建1行。
SELECCT * FROM testseq;
NEXT CURR
---------------- -------------------
1 1
3 3
5 5
7 7
9 9
增幅变成了2。默认情况序列是从1开始的,可以使用START WITH指定
DROP SEQUENCE mysql;
序列已删除。
CREATE SEQUENCE mysql INCREMENT BY 2 START WITH 10;
序列已创建
SELECCT * FROM testseq;
NEXT CURR
---------------- -------------------
10 10
12 12
14 14
16 16
18 18
还可以设定最大最小值,是否是循环
范例:创建一个序列,让其取值固定在1、3、5、7、9循环
CREATE SEQUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 1 CACHE 2 CYCLE;
序列已创建。
得到结果:
SELECCT * FROM testseq;
NEXT CURR
---------------- -------------------
1 1
3 3
5 5
7 7
9 9
1 1
3 3
5 5
7 7
9 9
1 1
从实际来看序列使用最多的语法:CREATE SEQUENCE 序列
2、序列删除
DROP SEQUENCE 序列名;
2)同义词
之前用过这样的语句:SELECT SYSDATE FROM dual;
dual是一个虚拟表,那么这个表是在哪里定义的呢?
现在是有system连接数据库,看看此表示是否属于system用户
CONN system/manager
SELECT * FROM tab WHERE tname= 'dual';
未选定行
通过system用户没有查到这个表,下面试试超级管理员:
CONN sys/change_on_install AS SYSDBA;
SELECT * FROM tab WHERE tname= 'dual';
在sys用户下存在此表,此表在sys下,但是scott用户可以直接通过表名称访问,那么正常情况下要访问不同用户的表需要使用"用户名.表名称"。实际上这就是同义词的作用,同义词可以让其他用户通过一个名称方便的访问"用户名.表名称"。
创建同义词的语法:
CREATE [PUBLIC] SYNONYM 同义词名称 FOR 用户名(模式名).表名称;
范例:将scott.emp定义emp的同义词
CREATE SYNONYM emp FOR scott.emp;
这样就可以在sys用户的情况下直接查询emp表了
如果要在非sys用户的情况下直接查询emp表,需要创建公共同义词
CREATE PUBLIC SYNONYM emp FOR scott.emp;
SELECT * FROM emp;
SHOW user;
USER为"SYS"
删除同义词:
DROP SYNONYM 同义词名称;
DROP SYNONYM emp;
同义词已删除。
SELECT * FROM emp;
ORA-00942:表或视图不存在
注意:此种特性只适用于Oracle数据库。