ORACLE Unit05 视图、序列、索引 、 约束

视图

数据库对象之一
视图在SQL语句中体现的角色与表相同，但它并非一张真实存在的表，它对应的是一个查询语句的结果集。

创建一个查看10号部门员工信息的视图:

CREATE VIEW v_emp_dept10 AS SELECT ename,sal,job,deptno FROM emp WHERE deptno=10DESC v_emp_dept10SELECT * FROM v_emp_dept10

修改视图就是替换该视图对应的子查询。
使用CREATE OR REPLACE即可。
若视图不存在就创建，存在则替换。
视图对应的子查询的字段若有别名，则该视图对应的字段名就是这个别名。若子查询的字段含有函数或表达式，那么该字段必须给别名。

CREATE OR REPLACE VIEW v_emp_dept10 AS SELECT empno id,ename name,sal salary,job,deptno FROM emp WHERE deptno=10

对视图进行DML操作就是对视图数据来源的基础表的操作。

INSERT INTO v_emp_dept10 (id,name,salary,job,deptno) VALUES (1001,'JACK',3000,'CLERK',10)SELECT * FROM v_emp_dept10 SELECT * FROM emp

插入数据时，视图看不到的字段都插入基础表对应字段的默认值，所以不能违反基础表相应字段的约束条件，尤其是看不到的字段的非空约束，否则可能导致插入失败。

UPDATE v_emp_dept10 SET name='JACKSON' WHERE id=1001SELECT * FROM v_emp_dept10 SELECT * FROM empDELETE FROM v_emp_dept10 WHERE id=1001

对视图进行DML操作可能导致对基表数据”污染”
即:对视图进行DML操作的数据视图对其不可见，却修改了基表该数据。

INSERT INTO v_emp_dept10 (id,name,salary,job,deptno) VALUES (1001,'JACK',3000,'CLERK',20)SELECT * FROM v_emp_dept10 SELECT * FROM empUPDATE v_emp_dept10 SET deptno=20

为视图添加检查选项可以避免对视图进行DML操作时污染基表。

CREATE OR REPLACE VIEW v_emp_dept10 AS SELECT empno id,ename name,sal salary,job,deptno FROM emp WHERE deptno=10 WITH CHECK OPTION

为视图添加只读选项后，则不能对视图进行DML操作。

CREATE OR REPLACE VIEW v_emp_dept10 AS SELECT empno id,ename name,sal salary,job,deptno FROM emp WHERE deptno=10 WITH READ ONLYSELECT table_name FROM user_tables

视图根据对应的子查询不同，分为简单视图和复杂视图。当子查询中含有函数，表达式，分组去重，关联查询时，该视图就是一个复杂视图。复杂视图不能进行DML操作。

创建一个包含每个部门薪资情况的视图

CREATE VIEW v_emp_sal AS SELECT AVG(e.sal) avg_sal,SUM(e.sal) sum_sal,MIN(e.sal) min_sal,MAX(e.sal) max_sal,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dnameSELECT * FROM v_emp_sal

查看哪个员工的工资高于其部门平均工资?

SELECT e.ename,e.sal,e.deptno
FROM emp e,v_emp_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal

删除视图

DROP VIEW v_emp_dept10

删除视图中的数据会将基表中对应数据删除，但是删除视图本身不会对基表数据产生影响。

序列

序列也是数据库对象之一
作用是生成一系列数字。通常使用它为某表的主键字段提供值使用。

CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1

序列支持两个伪列:
NEXTVAL:获取序列下一个数字
CURRVAL:获取序列当前数字

NEXTVAL会导致序列发生步进，且序列是不能回退的。CURRVAL是获取序列最后一次生成的数字，新创建的序列至少在调用以此NEXTVAL后才可以使用CURRVAL。

SELECT seq_emp_id.CURRVAL FROM dualSELECT * FROM empINSERT INTO emp (empno,ename,sal,job,deptno) VALUES (seq_emp_id.NEXTVAL,'JACK',3000,'CLERK',10)

UUID的生成方式:

SELECT SYS_GUID() FROM dual

删除序列

DROP SEQUENCE seq_emp_id

CREATE TABLE employees1 (eid NUMBER(6) UNIQUE,name VARCHAR2(30),email VARCHAR2(50),salary NUMBER(7, 2),hiredate DATE,CONSTRAINT employees1_email_uk UNIQUE(email) );INSERT INTO employees1 (eid,name,email) VALUES (NULL,'JACK',NULL)SELECT * FROM employees1CREATE TABLE employees2 ( eid NUMBER(6) PRIMARY KEY, name VARCHAR2(30), email VARCHAR2(50), salary NUMBER(7, 2), hiredate DATE );
INSERT INTO employees2 (eid,name) VALUES (1,'JACK')