Oracle第八课

Oracle第八课

一、序列(sequence)

1. 创建序列

   -- 创建一个默认序列
   create sequence seq1;
   -- 创建一个特别定制的序列
   create sequence SEQ1
   minvalue 1 -- 最小值
   maxvalue 9999999999999999999999999999 -- 最大值
   start with 1 -- 开始值
   increment by 1 -- 步长
   cache 20; -- 缓存
   -- 将序列改为循环使用
   ALTER SEQUENCE seq1 CYCLE;
   -- 缓存的最大值公式：(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

2. 序列的两个属性

   -- 序列的下一个值
   SELECT seq1.nextval FROM dual;
   -- 当前序列的值
   SELECT seq1.currval FROM dual;

3. 删除序列

   drop sequence seq1;

4. 序列的应用

   -- 插入数据时
   INSERT INTO t_emp2(ID,NAME) VALUES(seq1.nextval,'name'||seq1.currval);

二、视图

• 定义

  1. 视图是虚表，不是真实存在的表和数据
  2. 物理存在（真实存在），逻辑存在（不是真实的，抽象的）
  3. 视图的关键字：view
  4. 它在数据库以SQL的形式存在，一个视图其实是一个SQL语句的存储，这个SQL语句的名称就是视图的名称
  5. vw_emp一般vw是视图的前缀，vw_emp是一个SQL的存储。v_emp也可以，但是一些变量也以v_开头

• 优点：把SQL存储起来，运行方便.

  可以有效的保护数据，对权限加以控制

• 视图是依赖一些表的，而这些表不能给程序员开放，不能访问，如果直接访问是不可以的，但是DBA可以建一个视图，给程序员开放视图的权限，这样就可以间接来访问受限的表。视图只能查询，不能修改；这样就避免的误修改数据的风险。（敏感表名程序员可能是未知，就算知道，因为权限问题无法访问），在列或字段上做纵向的保护

  假设表中有1w条数据，因为数据的敏感性，用视图只显示其中若干条记录，这样的情况是列都显示，但是在行上有限制。

  一个视图背后是由若干张表组成的SQL，这个SQL是很复杂的，对程序员开放不合理，建上视图后，只要一个简单的视图名称就可以方便访问。

  构成视图的SQL中涉及的表是视图的基表。

• 视图的创建

  • 简单视图（可以往里面插入数据）

    create or replace view v_emp as select * from emp;

  • 复杂视图，多表

    CREATE OR REPLACE VIEW v_emp_dept AS SELECT empno,dname FROM emp e, dept d WHERE e.deptno=d.deptno;
    -- 查看视图
    SELECT * FROM v_emp_dept;

  • force：不存在或无权限也能创建视图

    -- 创建视图
    CREATE OR REPLACE FORCE VIEW v_force AS SELECT * FROM sunwukong;
    CREATE OR REPLACE FORCE VIEW v_t1 AS SELECT * FROM user22.t1;
    -- 分配权限，能够查看此视图
    grant select on t1 to scott;
    -- 查看视图
    SELECT * FROM v_force;
    SELECT * FROM v_t1;
    -- 收回权限
    revoke select on t1 from scott;

  • with check option插入时检查是否符合条件

    -- 创建带约束的视图
    CREATE OR REPLACE FORCE VIEW v_test AS 
    SELECT * FROM emp WHERE empno>7788
    WITH CHECK OPTION;
    -- 测试数据
    INSERT INTO v_test(empno) VALUES(7788);

  • with read only 只能查看视图

    CREATE OR REPLACE FORCE VIEW v_test AS 
    SELECT * FROM emp
    WITH READ ONLY;

• 物化视图：有数据，数据可能是加工过的，可能来自多个表，也可能是某表中的一部分

  数据来源于不同表或单表，经过横向或纵向处理，是一个视图窗口。

  原因：普通视图在执行SQL时要消耗性能，因为他们是一个SQL，物化视图执行时只查询自己的数据（不再进行计算）

  分类：ON DEMAND 、ON COMMIT

  在需要时（在查询视图时）再从基表中更新数据到物化同步（物化视图的同步）

  在更新视图的同时，同步物化视图，物化视图始终是最新的。（维护数据效率低）

• SQL优化：索引和物化视图

三、规范

• 关键字总大写，列名和表名小写

四、应用

• 查询出每门课都大于80分的学生姓名

  SELECT * FROM t_score WHERE NAME NOT IN(
  (SELECT DISTINCT NAME FROM t_score WHERE score<90));

• 学生表 如下:

  自动编号 学号 姓名 课程编号 课程名称 分数

  1 2005001 张三 0001 数学 69 2 2005002 李四 0001 数学 89 3 2005001 张三 0001 数学 69

  删除除了自动编号不同, 其他都相同的学生冗余信息

五、作业

• 一个家庭有父亲，母亲，儿子（若干），女儿（若干），要求找出家庭中所有的孩子都考上大学的家庭，并显示出父亲，母亲的姓名