Oracle第八课课后作业

Oracle第八课课后作业

一、作业

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

   -- 创建双亲表
   CREATE TABLE family(
   ID number(2) PRIMARY key,
   father VARCHAR(8),
   mother VARCHAR(8));
   -- 若已有序列，先删除序列
   DROP SEQUENCE seq1;
   -- 创建序列
   CREATE SEQUENCE seq1 MINVALUE 0 START WITH 0;
   -- 插入数据
   INSERT INTO family VALUES(seq1.nextval,&f,&m);
   SELECT * FROM family;
   -- 创建孩子表
   CREATE TABLE f_child(
   ID NUMBER(2) PRIMARY KEY,
   NAME VARCHAR(8) NOT NULL,
   sex NUMBER(1) DEFAULT 0 NOT NULL,
   college CHAR(2) NOT NULL,
   pre_family NUMBER(2) CONSTRAINT fk_fm REFERENCES family(ID));
   -- 创建序列
   CREATE SEQUENCE seq2 MINVALUE 0 START WITH 0;
   -- 插入数据
   INSERT INTO f_child(ID,NAME,college,pre_family)  VALUES(seq2.nextval,&n,&c,&p);
   SELECT * FROM f_child;
   -- 找出家庭中所有的孩子都考上大学的家庭，并显示出父亲，母亲的姓名
   SELECT * FROM family WHERE ID IN (
   SELECT DISTINCT pre_family FROM f_child WHERE pre_family NOT IN (
   (SELECT DISTINCT pre_family FROM f_child WHERE college='否'))
   );

2. 学生表 如下:

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

   1 2005001 张三 0001 数学 69

   2 2005002 李四 0001 数学 89

   3 2005001 张三 0001 数学 69

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

   CREATE TABLE student(ID NUMBER(2) PRIMARY KEY,
   SID VARCHAR(7),
   NAME VARCHAR(6),
   cid VARCHAR(4),
   cname VARCHAR(6),
   score NUMBER(2));
   DROP TABLE student;
   -- 创建序列
   CREATE SEQUENCE sq MINVALUE 0 START WITH 0;
   -- 插入数据
   INSERT INTO student VALUES(sq.nextval,&SID,&n,&cid,&cname,&s);
   SELECT * FROM student;
   -- 删除除了自动编号不同，其他都相同的学生冗余信息
   DELETE FROM student WHERE SID IN
   -- 找到相同学生的记录
   (SELECT SID FROM student GROUP BY SID HAVING COUNT(SID)>1) 
   AND ROWID NOT IN
   -- 保留rowid小的，删除大的 
   (SELECT MIN(ROWID) FROM student GROUP BY SID HAVING COUNT(SID)>1);

二、序列

• 创建序列

  -- 创建序列：minvalue:最小值，maxvale：最大值，start with：开始值，increment：步长，cache：缓存
  -- 缓存有最大值限制：
  (最大值-最小值)/步长的绝对值，向上取整
  create sequence seq1
  minvalue 1
  maxvalue 9999999999
  start with 1
  increment by 1
  cache 20;
  -- 将序列改为循环使用:cycle
  alter sequence seq1 cycle;

• 序列的两个属性

  -- 下一个值
  select seq1.nextval from dual;
  -- 当前值
  select seq1.currval from dual;

• 删除序列

  drop sequence seq1;

三、视图

1. 引入视图的原因

   -- 有些表不能给程序员开放，DBA可以建一个视图，给程序员开放视图的权限，这样就可以间接来访问受限的表。
   -- 建立视图可以避免误修改数据的风险，对数据进行横向或纵向的保护

2. 作用

   • 把SQL存储起来，运行方便

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

3. 特点

   • 视图是虚表，逻辑存在的（不是真实的，抽象的）

   • 试图关键字：view

   • 以SQL的形式存在

     create view v_emp as
     select * from emp;

   • 视图的定义：vw或v

4. 建立视图

   -- 建立视图，若视图已经存在，就进行更新
   create or replace view v_emp as
   select * from emp;
   ​
   -- 创建带约束的视图:with check option
   CREATE OR REPLACE FORCE VIEW v_test AS 
   SELECT * FROM emp WHERE empno>7788
   WITH CHECK OPTION;
   ​
   -- 创建只能查看的视图
   CREATE OR REPLACE FORCE VIEW v_test AS 
   SELECT * FROM emp
   WITH READ ONLY;
   ​
   -- 视图涉及多个表
   CREATE OR REPLACE VIEW v_emp_dept AS SELECT empno,dname FROM emp e, dept d WHERE e.deptno=d.deptno;
   ​
   -- force:不存在权限也能创建视图
   CREATE OR REPLACE FORCE VIEW v_t1 AS SELECT * FROM user22.t1;
   -- 分配权限，能够查看此视图
   grant select on t1 to scott;
   -- 收回权限
   revoke select on t1 from scott;
   ​
   -- 查看视图
   SELECT * FROM v_emp_dept;

5. 物化视图

   • 引入原因

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

   • 分类：ON DEMAND 、ON COMMIT

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

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

6. SQL优化：索引和物化视图

四、PLSQL编程规则

1. 字符串不使用双引号，用单引号

2. 每行代码以分号结尾

3. dbms_output是数据库定义的程序包，类似于Java中的类，put_line是输出信息，还有一个函数put,也是输出，但是信息在缓存中不输出，当put_line时把缓存中的数据一起输出。

4. 定义变量，选择语句