[大数据]Oracle从零开始6——数据库管理

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数据库。