【题库】Oracle开发类笔试面试题集

http://www.cnblogs.com/shenxiaolin/p/5517834.html

一、涉及内容

　　游标的创建与应用

二、具体操作

（一）填空题

　　1.PL/SQL 程序块主要包含3个部分：声明部分、（执行部分）、异常处理部分。

　　2.自定义异常必须使用（RAISE ）语句引发。

（二）选择题

　　1.下列哪一个不是BOOLEAN变量可能的取值？（D ）

　　　　A.TRUE B.FALSE C.NULL D.BLANK

　　2.请查看以下IF语句：

Declare sal number:=500;
     comm number;

Begin

If sal <100 then Comm :=0;
   Elsif sal <600 then Comm: =sal*0.1;

Elsif sal <1000 then Comm: =sal*0.15;

 Else Comm: =sal*0.2; End if; End;

　　在执行了以上语句之后，变量comm 的结果应是：（B ）

　　A.0 B.50 C.75 D.100

　　3．在以下哪些语句中可以包含WHERE CURRENT OF 子句？（CE）

　　　　A.OPEN B.FETCH C.DELETE D.SELECT E.UPDATE F.CURSOR

　　4.在异常和oracle错误之间建立关联时，应该在哪个部分完成？（A）

　　　　A.定义部分 B.执行部分 C.异常处理部分

　　5.只能存在一个值的变量是哪种变量？（B ）

　　　　A.游标 B.标量变量 C.游标变量 D.记录变量

（三）编程题

　　1.编写程序计算并输出1~100的和。

　　方案一：

　　语句：

Declare v_sum integer:=0; begin for i in 1..100 loop
      v_sum := v_sum+i; end loop;
   dbms_output.put_line(‘1~100的和为：’|| v_sum); end; /

　　截图：

　　方案二：

　　语句：

Declare v_sum integer; begin v_sum := 100*101/2;
    dbms_output.put_line(‘1~100的和为：’|| v_sum); end; /

　　截图：

　　2.分别使用显式游标和隐式游标逐行输出scott.emp表中的员工姓名和工资。

　　（一）使用显式游标

　　语句：

DECLARE CURSOR emp_cursor01 IS SELECT ename,sal FROM scott.emp;
   emp_record emp_cursor01%ROWTYPE; BEGIN OPEN emp_cursor01 ;
   LOOP FETCH emp_cursor01 INTO emp_record; EXIT WHEN emp_cursor01%NOTFOUND;
     dbms_output.put_line('ename:'||emp_record.ename||',sal:'||emp_record.sal); END LOOP;
   dbms_output.put_line('row count:'||emp_cursor01%rowcount); CLOSE emp_cursor01; END;

　　截图：

　　（二）使用隐式游标

　　语句：

BEGIN FOR emp_record IN (SELECT ename,sal FROM scott.emp)
   LOOP
     dbms_output.put_line('ename:'||emp_record.ename||',sal:'||emp_record.sal); END LOOP; END;

　　截图：

（补充练习）

1、创建一个表top_dogs，包含两列：name varchar2(25) 和 salary NUMBER(11,2)，用来存放员工姓名和工资。

语句：

create table top_dogs
 (name varchar2(25),
  salary NUMBER(11,2));

截图：

2、创建一个PL/SQL块，查询出工资水平前n名的员工。具体要求如下：

（1）通过替代变量读取n的值。

（2）通过循环从emp表中获取工资在前n名的员工的姓名和工资

（3）将得到的员工姓名和工资写入top_dogs表。

（4）如果有多名员工的工资相同，则每人都占n名中的一个名额。

（5）测试特殊情况，例如n=0或者n大于员工总数。

（6）每次向top_dogs 表中写入数据前，先清空该表中的数据。

方案一：

语句：

DECLARE CURSOR emp_cursor(n number) IS SELECT ename,sal FROM scott.emp ORDER BY sal DESC;
   v_n integer := &n; BEGIN delete from top_dogs; FOR rec IN emp_cursor(v_n)  LOOP IF emp_cursor%ROWCOUNT <=v_n THEN insert into top_dogs values(rec.ename,rec.sal);
         dbms_output.put_line('ename: '||rec.ename||' sal:'||rec.sal);
      ELSIF v_n<=0 or v_n>emp_cursor%ROWCOUNT then dbms_output.put_line('error!'); ELSE EXIT; --退出循环 END IF; END LOOP; END; /

截图：

n=3以及n=4:

测试n=0以及n=100的情况：

方案二：

语句：

DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp ORDER BY sal DESC;
   v_n integer := &n; BEGIN delete from top_dogs; FOR rec IN emp_cursor
LOOP Exit when emp_cursor%notfound; Exit when emp_cursor%ROWCOUNT>v_n; insert into top_dogs values(rec.ename,rec.sal); -- dbms_output.put_line('ename: '||rec.ename||'  sal:'||rec.sal); END LOOP; END; /

截图：

输入n:2

输入n:3

Select * from top_dogs

输入n:0

Select * from top_dogs

输入n:100

Select * from top_dogs

3、在上题的基础上，如果员工工资相同（并列第几名），需要将前n名的员工全部输出。

语句：

DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp ORDER BY sal DESC;
   v_n integer := &n;
   v_sal scott.emp.sal%type; BEGIN delete from top_dogs; FOR rec IN emp_cursor
LOOP Exit when emp_cursor%notfound; Exit when emp_cursor%ROWCOUNT>v_n and v_sal<>rec.sal;
        V_sal:= rec.sal; insert into top_dogs values(rec.ename,rec.sal); -- dbms_output.put_line('ename: '||rec.ename||'  sal:'||rec.sal); END LOOP; END;

输入n 的值：2

Select * from top_dogs

有三条记录

截图：

4、使用游标查询dept表的部门编号和名称，将其部门编号传递给另一个用于查询emp表的游标（查询emp表的员工姓名，工作，雇佣日期，工资）。

语句：

Declare Cursor dept_cur is select deptno,dname from scott.dept; Cursor emp_cur (c_deptno scott.emp.deptno%type) is select ename,job,hiredate,sal from scott.emp where deptno=c_deptno;
   Emp_rec emp_cur%rowtype; Begin For dept_rec in dept_cur
   Loop Open emp_cur(dept_rec.deptno);
     Loop Fetch emp_cur into Emp_rec; Exit when emp_cur%notfound;
       Dbms_output.put_line('ename:'|| Emp_rec.ename||' job:'|| Emp_rec.job||' hiredate:'|| Emp_rec.hiredate||' sal:'|| Emp_rec.sal); End loop; Close emp_cur; End loop; End; /

截图：

5、为emp表增加一个列stars，类型为VARCHAR2(100)。使用游标更新stars列：创建一个PL/SQL块，根据员工的工资计算他能获得的星号“*”数量，每100美元奖励一个星号，按四舍五入处理。并根据员工所获得的星号数量n，形成由n个星号组成的字符串，写入emp表的stars列。

（1）为emp表增加一个列stars，类型为VARCHAR2(100)。

（2）使用游标更新stars列：

语句：

Declare v_stars scott.emp.stars%type;
    v_num number:=0; Cursor c1 is select empno,sal,stars from scott.emp; Begin For emp_rec in c1
    Loop
      v_stars:=''; exit when c1%notfound;
      v_num:=round(emp_rec.sal/100); for i in 1..v_num
    loop
      v_stars:= v_stars||'*'; end loop; update scott.emp set stars= v_stars where empno=emp_rec.empno; end loop; end;

截图：

Oracle数据库——触发器的创建与应用

一、涉及内容

1.理解触发器的概念、作用和类型。

2.练习触发器的创建和使用。

二、具体操作

（实验）

1.利用触发器对在scott.emp表上执行的DML操作进行安全性检查，只有scott用户登录数据库后才能向该表中执行DML操作。（第1题中，user是系统函数，返回当前用户。字符串中使用两个单引号表示一个单引号。）

要求：分别以system用户和scott用户对emp 表执行DML操作，试验触发器的运行效果。

（1）在scott用户下创建触发器

语句：

create or replace trigger tri_dm1
 before insert or update or delete on scott.emp begin if user <>'SCOTT' then raise_application_error(-20001,'You don''t have access to modify this table.'); end if; end; /

截图：

（2）以system 用户连接，并对emp表执行DML操作

语句：

conn system/orcl1234; insert into scott.emp(empno,ename) values(8888,'shenxiao');

截图：

（3）以scott用户连接，并对emp表执行DML操作

语句：

conn scott/tiger; insert into scott.emp(empno,ename) values(8888,'shenxiao');

截图：

2.利用触发器进行表和备份表之间的同步复制。

（1）在scott 用户下创建scott.emp 表的复本 employee。

语句：

conn scott/tiger;

create table employee as select * from scott.emp;

截图：

（2）在scott用户下创建能实现scott.emp和employee 两表之间同步复制的DML触发器。

语句：

create or replace trigger duplicate_emp
 after update or insert or delete on scott.emp for each row begin if inserting then insert into employee values (:new.empno,:new.ename,:new.job,:new.mgr,
                                   :new.hiredate,:new.sal,:new.comm,:new.deptno);
   elsif deleting then delete from employee where empno=:old.empno; else update employee set empno=:new.empno,ename=:new.ename,job=:new.job,
                         mgr=:new.mgr,hiredate=:new.hiredate,sal=:new.sal,comm=:new.comm,
                          deptno=:new.deptno where empno=:old.empno; end if; end; /

截图：

（3）对scott.emp表进行插入、删除和更新操作。

删除：delete from scott.emp where empno=7934;

插入：

Insert into scott.emp(empno,ename,job,sal) values(1111,'zhangsan','ANALYST',2900)

更新：

update scott.emp set sal=3900 where empno=1111;

（4）查询scott.emp表和employee表中插入、删除和更新的记录。

语句：select * from scott.emp;

select * from employee；

截图：

3.建立触发器，对scott.emp 表进行DML操作时的时间、用户进行日志记录。

（1）建立日志表emp_log。

语句：create table emp_log(who varchar2(30),when date,oper varchar2(10));

截图：

（2）在emp表上建立语句级触发器，将对emp表执行的操作记录到emp_log表中。

语句：

 create or replace trigger dm1_log
 after insert or update or delete on scott.emp declare oper emp_log.oper%type; begin if inserting then oper:='insert';
    elsif deleting then oper:='delete'; else oper:='update'; end if; insert into emp_log values(user,sysdate,oper); end; /

截图：

（3）对scott.emp 执行DML操作，查看emp_log 表中的数据。

语句：

insert into scott.emp(empno) values(1112); select * from emp_log;

截图：

（习题）

1.简述Oracle 数据库中触发器的类型及触发条件。

答：触发器的类型及触发条件如下表所示：

按划分类型	触发器的类型	触发条件
按照触发的时间	BEFORE触发器，指事前触发器	在触发语句执行前触发器被触发
	AFTER触发器，指事后触发器	在触发语句执行以后触发器被触发
	INSTEAD OF触发器，指替代触发器	触发语句被触发器操作替代
按照触发的事件	DML触发器	对表或视图执行DML操作时触发的触发器
	DDL触发器	在数据库中执行DDL操作时触发的触发器
	用户事件触发器	与用户执行的DCL操作或LOGON/LOGOFF操作相关的触发器
	系统事件触发器	是指由数据库系统事件触发的触发器

其中，DML触发器，按照触发时DML操作影响的记录多少，又可分为：

行级触发器：DML语句每操作一行，行级触发器就会被调用一次

语句级触发器：DML语句不论影响多少行数据，语句级触发器只被调用一次

DDL触发器又可以分为：

数据库级DDL触发器：数据库中任何用户执行了相应的DDL操作该类触发器都被触发。

用户级DDL触发器：只有在创建触发器时指定方案的用户执行相应的DDL操作时触发器才被触发，其他用户执行该DDL操作时触发器不会被触发。

2.描述一个触发器的组成部分及其作用。

答：在Oracle系统中，触发器包括以下几个组成部分：

组成部分	作用
1.触发器名称	触发器名是在创建触发器为触发器起的名称。一般包括：触发器执行的时间、执行的操作、涉及的表、涉及的列等。
2.触发语句	触发语句是导致Oracle执行触发器操作的诱因，它包括对触发时间、触发事件和触发对象的定义。只有用户对数据库执行的操作满足触发语句中定义的所有内容后，触发器才有可能被系统自动调用。
3.触发限制条件	触发限制条件是决定触发器是否被系统自动调用的另一个因素。当用户的操作满足触发语句时，触发器不一定被调用，此时，系统还要检查触发器中是否定义了触发限制条件，如果存在，还要检查当前的操作是否满足限制条件。
4.触发器操作	触发器操作是触发器的主体，是被系统自动执行的PL/SQL 程序块。当触发语句和触发限制条件都满足时，系统将自动执行触发器操作部分的代码。

3.简述替代触发器的作用。

答：

创建触发器时若选择了INSTEAD OF子句，那么该触发器就是替代触发器。替代触发器只能建立在视图上不能建立在表上。用户在视图上执行的DML操作将被替代触发器中的操作代替。

替代触发器主要解决对不可更新视图执行更新操作时带来的问题。在定义视图时，如果视图中没有选择基础表的主键咧，或者视图中的数据来自多个基础表，那么用户将无法对这样的视图直接执行插入、修改、删除操作。这种情况下，用户可以针对是视图创建一个替代触发器，将对视图的更新操作转换为对基础表的操作。

Oracle数据库——SQL高级查询

一、涉及内容

　　1.掌握SELECT语句的多表连接查询。

　　2.掌握SELECT语句的子查询。

二、具体操作

（一）根据Oracle数据库scott方案下的emp表和dept表，完成下列操作：

1.查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dname from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno where job='CLERK';

2.查询所有部门及其员工信息，包括那些没有员工的部门。

select * from scott.emp t1 right join scott.dept t2 on t1.deptno=t2.deptno

3.查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

select * from scott.emp t1 left join scott.dept t2 on t1.deptno=t2.deptno

4.查询在SALES部门工作的员工的姓名信息。

用子查询实现:

select * from scott.emp where deptno=(select deptno from scott.dept where dname='SALES')

用连接查询实现:

select * from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno where t2.dname='SALES';

注意两种实现方式，在行和列上的变化。

5.查询所有员工的姓名及其直接上级的姓名。

select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno;

6.查询入职日期早于其上级领导的所有员工的信息。

select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.hiredate<t2.hiredate

7.查询从事同一种工作但不属于同一部门的员工信息。

select t1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from scott.emp t1 cross join scott.emp t2 where t1.job=t2.job and t1.deptno <>t2.deptno

8.查询10号部门员工及其领导的信息。

select t1.ename as 员工姓名,t2.ename 经理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.deptno=10;

9.使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。

select * from scott.emp where sal>2500 union select * from scott.emp where job='ANALYST'

10.通过INTERSECT集合运算，查询工资大于2500，并且工作为ANALYST的雇员信息。

select * from scott.emp where sal>2500 intersect select * from scott.emp where job='ANALYST

11.使用MINUS集合查询工资大于2500，但工作不是ANALYST的雇员信息。

select * from scott.emp where sal>2500 minus select * from scott.emp where job='ANALYST';

12.查询工资高于公司平均工资的所有员工信息。

select * from scott.emp where sal>(select avg(sal) from scott.emp)

13.查询与SMITH员工从事相同工作的所有员工信息。

select * from scott.emp where job=(select job from scott.emp where ename='SMITH')

14.查询工资比SMITH员工工资高的所有员工信息。

select * from scott.emp where sal>(select sal from scott.emp where ename='SMITH')

15.查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。

select ename,sal from scott.emp where sal>all(select sal from scott.emp where deptno=30)

16.查询部门人数大于5的部门的员工信息。

select * from scott.emp where deptno in (select deptno from scott.emp group by deptno having count(*)>5);

17.查询所有员工工资都大于2000的部门的信息。

select * from scott.dept where deptno in(select deptno from scott.emp group by deptno having min(sal)>2000)

18.查询人数最多的部门信息。

select * from scott.dept where deptno in (select deptno from (select deptno,count(*) as 人数 from scott.emp group by deptno) where 人数=(select max(人数) from(select deptno,count(*) as 人数 from scott.emp group by deptno)));

19.查询至少有一个员工的部门信息。

select * from scott.dept where deptno in(select deptno 　　　　　　　　　　from scott.emp
　　　　　　　　　　group by deptno
　　　　　　　　　　having count(*)>=1)

20.查询工资高于本部门平均工资的员工信息。

select * from scott.emp e where sal>(select avg(sal)
 　　　　　　from scott.emp 　　　　　　group by deptno
　　　　　　having e.deptno=deptno);

21.查询工资高于本部门平均工资的员工信息及其部门的平均工资。

select * from((select * from scott.emp e where sal>(select avg(sal) from scott.emp group by deptno having e.deptno=deptno)) t1 inner join (select avg(sal),deptno from scott.emp group by deptno) t2 on t1.deptno=t2.deptno);

22.查询每个员工的领导所在部门的信息。

select * from scott.dept where deptno in(select distinct deptno
　　　　　　　　　　from scott.emp
　　　　　　　　　　where empno in(select distinct mgr
　　　　　　　　　　　　　　　　　　　　from scott.emp));

23.查询平均工资低于2000的部门及其员工信息。

select * from scott.emp t1,scott.dept t2 where t1.deptno=t2.deptno and t1.deptno in(select deptno
        　　　　　　　　　　　　　　　　　　　　　　from scott.emp
　　　　　　　　　　　　　　　　　　　　　　　　　　　　group by deptno
　　　　　　　　　　　　　　　　　　　　　　　　　　　　having avg(sal)<2000)

（二）习题

1.如果需要将雇员表中的所有行连接到雇员表中的所有行，则应创建哪种类型的连接？（B）

A.等值连接 B.笛卡尔乘积 C.内连接 D.外连接

2.如果需要从顾客表和订单表中查询所有顾客及其下达的所有订单，并且要求查询结果中先按顾客所在公司名称的升序排列，再按订单金额的降序排列。应执行以下哪条语句？（B）

A.SELECT c.顾客标识,c.公司名称,o.订单日期,o.顾客标识,o.金额

FROM 顾客 c,订单 o

WHERE c.顾客标识=o.顾客标识

ORDER BY 金额 DESC, 公司名称;

B. SELECT c.顾客标识,c.公司名称,o.订单日期,o.顾客标识,o.金额

FROM 顾客 c,订单 o

WHERE c.顾客标识=o.顾客标识

ORDER BY公司名称,金额 DESC;

C. SELECT c.顾客标识,c.公司名称,o.订单日期,o.顾客标识,o.金额

FROM 顾客 c,订单 o

WHERE c.顾客标识=o.顾客标识

ORDER BY公司名称,金额;

D. SELECT c.顾客标识,c.公司名称,o.订单日期,o.顾客标识,o.金额

FROM 顾客 c,订单 o

WHERE c.顾客标识=o.顾客标识

ORDER BY公司名称 ASC,金额 DESC;

3.评估以下SQL语句：

SELECT e.雇员标识,e.姓氏,e.名字,d.部门名称

FROM 雇员 e,部门 d

WHERE e.部门标识=d.部门标识

AND 雇员.部门标识>5000

ORDER BY 4;

哪个字句的语法有错误？（E）

A. SELECT e.雇员标识,e.姓氏,e.名字,d.部门名称

B. FROM 雇员 e,部门 d

C. WHERE e.部门标识=d.部门标识

D. AND 雇员.部门标识>5000

E. ORDER BY 4;

4.评估以下语句：

SELECT 部门标识,AVG(薪金)

FROM 雇员

WHERE 职务标识<> 69 879

GROUP BY 部门标识

HAVING AVG(薪金)>35 000

ORDER BY部门标识;

哪些子句限制了返回结果？请选择两个正确答案。（BD）

A. SELECT 部门标识,AVG(薪金)

B. WHERE 职务标识<> 69 879

C. GROUP BY 部门标识

D. HAVING AVG(薪金)>35 000

5.在SELECT语句中各个子句的正确顺序是什么？（C）

A. SELECT

FROM

WHERE

ORDER BY

GROUP BY

HAVING

B. SELECT

FROM

HAVING

ORDER BY

WHERE

GROUP BY

C. SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

D. SELECT

FROM

WHERE

HAVING

ORDER BY

GROUP BY

6.以下哪个运算符可用于多行子查询？（A）

A.IN B.<> C.= D.LIKE

7.假设数据库中有顾客表和订单历史记录表。其中，顾客表中包括：客户标识NUMBER（5）、姓名VARCHAR2（25）、信贷限额NUMBER（8,2）、开户日期（DATE）；订单历史记录表中包括：订单标识NUMBER（5）、客户标识NUMBER（5）、订单日期（DATE）、总计NUMBER（8,2）。以下哪种方案需要使用子查询来返回需要的结果？（D）

A.需要显示每个顾客账户下的开户日期

B.需要显示顾客下达订单的各个日期

C.需要显示在特定日期下达的所有订单

D.需要显示与编号为25950的订单的下达日期相同的所有订单

8.如果希望在报表中显示成本值高于所有产品平均成本的产品名称，应使用以下哪些SELECT语句？（B）

A. SELECT 产品名称 FROM 产品 WHERE 成本>（SELECT AVG（成本） FROM 产品）；

B. SELECT 产品名称 FROM 产品 WHERE 成本> AVG（成本）；

C. SELECT AVG（成本）, 产品名称 FROM 产品 WHERE 成本> AVG（成本）GROUP BY 产品名称；

D. SELECT 产品名称 FROM（SELECT AVG（成本） FROM 产品） WHERE 成本> AVG（成本）；

9.如果单行子查询返回了空值且使用了等于比较运算符，外部查询会返回什么结果？（B）

A.不返回任何行 B.返回表中的所有行

C.返回空值 D.返回错误

10.如果需要创建包含多行子查询的SELECT语句，可以使用哪个（些）比较运算符？（A）

A.IN、ANY和ALL B.LIKE

C.BETWEEN…AND… D.=、< 和 >

select ename,dname from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno where job='CLERK';

Oracle数据库—— 事务处理与并发控制

一、涉及内容

　　1.理解事务的概念和几个特性。

　　2.熟练掌握事务管理命令的使用。

　　3.理解并发操作的概念和数据库锁的类型。

二、具体操作

（12.5 实验）

1. 分析以下代码，说出代码中的哪些部分体现了事务的语句级原子性、过程级原子性和事务级原子性。

create table book
(bid number(4) CONSTRAINT pk_bid PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE,
 bname varchar2(30),
 bprice number(4,1)); insert into book values(1001,'Oracle 10g 数据库开发',35.4); insert into book values(1001,'Java 程序设计',40); begin insert into book values(1002,'Java 程序设计',40); insert into book values(1002,'计算机英语',28); end; insert into book values(1003,'计算机英语',28); commit;

答：

（1）体现语句级原子性：上述代码中的每一条语句均能体现语句级原子性。每条语句本身也是最小级别的事务，该语句要么完全执行成功，要么完全失败，并且它不会影响其他语句的执行。

（2）体现过程级原子性：

以下这个匿名块：

begin

insert into book values(1002,'Java 程序设计',40);

insert into book values(1002,'计算机英语',28);

end;

这里PL/SQL匿名过程块也当作是语句，当作一个整体，过程中的所有代码要么都执行成功，要么都执行失败，并且不影响过程外的其他语句。

（3）体现事务级原子性：

上述代码中的所有语句及匿名块，即整个事务中的所有语句和匿名块都当作一个整体，一个事务。用户在提交或回滚事务时，要么所有语句都执行，要么都失败。

事务级原子性中包含了语句级原子性和过程级原子性，整个事务中的语句或匿名块首先受语句级原子性和过程级原子性的影响。

2. 数据库的DDL命令作为一个独立的事务执行，若以下代码在Sql*Plus的窗口1执行，那么在Sqlplus的窗口2中能够看到哪些新数据？

insert into scott.emp(empno,sal) values(1001,3000); insert into scott.emp(empno,sal) values(1002,3000); create table t1(id number); insert into scott.emp(empno,sal) values(1003,3000);

在Sql*Plus的窗口1执行上述代码：

在Sqlplus的窗口2中查看scott.emp(empno,sal)：

如上图所示，在Sqlplus的窗口2只能查看到在Sqlplus的窗口1中执行代码的create 之前的插入的数据。

3. 使用ROLLBACK可以将事务回滚到某个保存点，分析以下代码执行的结果。

insert into scott.emp(empno) values(1011);
SAVEPOINT aa; insert into scott.emp(empno) values(1012); ROLLBACK to aa; insert into scott.emp(empno) values(1013); commit;

答：执行结果截图如下：

因为使用了ROLLBACK，可将事务回滚到某个保存点。故上述代码执行完后只能看到插入的1011和1013两条记录的数据。

4. 利用第一题中创建的book表，分析以下两段代码的执行结果有何不同？

代码段一：

insert into book values(1011,null,null); insert into book values(1011,null,null); commit;

执行结果截图：

代码段二：

set CONSTRAINT pk_bid deferred; insert into book values(1011,null,null); insert into book values(1011,null,null); commit;

执行结果截图：

答：代码段一中插入两条一样的记录，违反主键约束，插入重复的记录失败。代码段二中，设置了约束延迟生效，插入重复的记录暂时成功，但是当提交事务时检查约束，违反了主键约束，整个事务被回滚，两条看似成功的insert命令都失败了。

5.按照以下要求书写代码并执行，根据实验结果说明事务在不同隔离级别下的特点。

打开3个Sqlplus窗口，在窗口1中向scott.emp表中插入一行新数据，在窗口2中设置事务的隔离属性是READ ONLY，在窗口3中设置事务的隔离属性是READ WRITE。在窗口1提交插入命令的前后，观察窗口2和窗口3 中能否看到这条新数据。尝试在窗口2和窗口3中分别向scott.emp表中插入新数据，看看能否成功。

（1）在窗口1中向scott.emp表中插入一行新数据：

（2）在窗口2中设置事务的隔离属性是READ ONLY；

（3）在窗口3中设置事务的隔离属性是READ WRITE；

（4）窗口1提交前，观察窗口2和窗口3 中均不能看到这条新数据；

（5）窗口1提交后，观察窗口2还是不能看到；而窗口3 中可以看到这条新数据；

（6）在窗口2向scott.emp表中插入新数据，不能成功。

（7）在窗口3中向scott.emp表中插入新数据，可以插入成功。

（12.6 习题）

（一）填空题

1.事务的ACID特性包括（原子性）、（一致性）、（隔离性）、（持久性）。

2.在设置事务隔离层时，需要使用关键字（SET TRANSACTION）。

3.在众多的事务控制语句中，用来撤销事务操作的语句是（ROLLBACK），用来持久化事务对数据库操作的语句是（COMMIT）。

4.对表执行INSERT命令时系统自动加（RX）锁，执行CREATE命令时系统自动加（S）锁，执行ALTER命令时系统自动加（X）锁。

（二）简答题

1.哪些情况发生后事务将终止？

答：Oracle中的事务终止会发生在：

（1）用户使用COMMIT命令显示提交事务。

（2）用户使用ROLLBACK命令回滚整个事务。

（3）用户执行了一条DDL语句。（如create\drop\alter）

(4) 用户正常断开了与Oracle的连接，这时用户当前的事务将被自动提交。

（5）用户进程意外被终止，这时用户当前的事务被回滚。

（6）用户关闭SQL*PLUS会话时，默认使用ROLLBACK回滚事务。

2.数据库的并发操作会带来哪些问题？

答：数据库的并发操作会带来以下问题：
（1）丢失更新。

（2）错读（脏读）。

（3）不一致的分析（不可重复读）。

（4）幻读。

3.要建立一个名为savepint1的保存点，应使用哪个语句？

答：应使用语句：SAVEPOINT savepint1；

Oracle数据库——体系结构

一、涉及内容

1.了解数据库的物理存储结构和逻辑存储结构

二、具体操作

1.分别使用SQL 命令和OEM 图形化工具查看本地数据库的物理文件，并使用OEM 工具在现有的users 表空间中添加user02.dbf 数据文件。（要求查看的物理文件包括：数据文件、日志组、控制文件。）

（1）使用SQL 命令查看本地数据库的物理文件（物理文件包括：数据文件、日志组、控制文件。）

使用DOS下的命令启动DBConsole服务

使用sql*plus，system用户登录：

查看数据文件：

（永久静态数据文件）

（永久动态数据文件）

（临时静态数据文件）

（临时动态数据文件）

查看日志组文件：

查看控制文件：

（2）使用OEM 图形化工具查看本地数据库的物理文件（物理文件包括：数据文件、日志组、控制文件。）

使用：http://localhost:1158/em 登录本机OEM工具，如下图所示：

登录界面如下：

查看数据文件：

查看日志组文件：

查看控制文件：

（3）使用OEM 工具在现有的users 表空间中添加user02.dbf 数据文件

2.使用OEM 图形化工具查看本地数据库的表空间，并尝试创建一个新的表空间及其数据文件。

（1）查看本地数据库的表空间

（2）创建一个新的表空间及其数据文件。

3.填空题

（1）Oracle数据库的物理存储结构主要包括4类文件，分别是（数据）文件、（日志）文件、（控制）文件、（初始化参数）文件。其中（数据）文件是存储用户数据的地方，（控制）文件存储了数据库的结构，（初始化参数）文件在启动数据库时第一个被访问。

（2）数据库的逻辑存储结构从大到小包括（表空间）、（段）、（盘区）、（数据块）。其中（盘区）是磁盘空间的最小分配单元，（数据块）是数据存取的最小单元。

（3）一个表空间物理上对应一个或多个（数据）文件。表空间中的某个（段）可以被包含在两个数据文件中，但是它里边的每个（盘区）只能属于一个数据文件。

（4）Oracle的进程结构包括（用户进程）、（服务进程）、（后台进程）。当在客户端运行一个程序或Oracle工具时，系统将为用户运行的应用程序建立一个（用户）进程，在服务端（服务）进程将为它服务。

（5）在SGA中（数据）缓冲区是存储用户最新使用过的数据，（重做日志）缓冲区是循环使用。

（6）DBWR进程负责将（数据缓冲区内修改过的）数据写入（数据文件）中。

（7）LGWR进程负责将（重做日志缓冲区内的日志）信息写入（磁盘上的日志文件）中。

（8）ARCn进程负责将（重做日志文件中的日志条目）信息写入（指定的归档日志设备）中，只有数据库工作在（归档）日志模式下该进程才起作用。

（9）数据库服务的工作模式分为：专用服务器模式和（共享服务器模式）两种，在（专用服务器）模式中用户进程和服务进程是一对一的，在（共享服务器）模式中用户进程和服务进程是一对多的，Dnnn进程在（共享服务器）模式中起作用。

Oracle数据库——SQL基本查询

一、涉及内容

　　1.掌握SELECT 语句的基本语法。

　　2.掌握常用函数的使用。

二、具体操作

（一）使用Scott方案下的emp表和dept表，完成以下操作：

1.查询部门编号是20的员工信息。

2.查询工作为CLERK的员工号、员工名和部门号。

3.查询奖金COMM高于工资SAL的员工信息。

4.查询奖金高于工资20%的员工信息。

5.查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作是CLERK的员工信息。

6.查询工作不是MANAGER和CLERK，并且工资大于或等于2000的员工信息。

7．查询有奖金的员工信息。

8.查询所有员工的人数和他们的平均工资。

9．查询没有奖金或奖金低于100的员工信息。

10.查询最近两年入职的员工信息。

11.查询工龄大于或等于10年的员工信息。

12.查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

13.查询员工名正好为6个字母的员工信息。

14.查询员工名字中不包含字母S的员工。

15.查询员工姓名的第二个字母为M的员工信息。

16．查询所有员工姓名的前三个字符。

17．查询所有员工的姓名，如果包含字母s，则用S替换。

18．查询员工的姓名和入职日期，并按入职日期从先到后进行排序。

19.显示所有员工的姓名、工作、工资，按工作降序排序，若工作相同则按工资升序排序。

20.显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序。

21.查询每个部门中的员工数量、平均工资和平均工作年限。

22.查询各个部门的人数及平均工资。

23.查询各个工作的最低工资，并输出最低工资低于3000的工作名称。

24．查询各个部门中不同工种的最高工资。

25.统计各个工种的员工人数与平均工资。

（二）习题

1.select语句

select length(电子邮件)from雇员；

将显示什么结果？（C）

A.雇员表中最长的电子邮件地址

B.雇员表中每个雇员的电子邮件地址

C.雇员表中“电子邮件”列中的每个值的字符数

D.“电子邮件”列中允许使用最多的字符数

2.若雇员表包含以下列：姓氏varchar2(20)、名字varchar2（20）、聘用日期date、计算月份number（3），那么下列的select语句？

Select聘用日期+计算月份from雇员；

返回的值属于那种数据类型？（A）

A. date B. number C. datetime D. integer

3.假设有雇员表结构同上，以下哪条sql语句会正确地显示部门90中所有的雇员的姓氏和聘用的周数？（A）

A.select姓氏，（sysdate-聘用日期）/7as周from雇员where部门标识=90；

B.select姓氏，（sysdate-聘用日期）/7display周from雇员where部门标识=90；

C.select 姓氏，周数 from 雇员 where 部门标识=90；

D.select姓氏，（sysdate-聘用日期）as周from雇员where部门标识=90；

4．假设产品表中包括价格number（7，2）列,对于下面的语句？

Select nvl(10/价格，‘0’)from产品；

如果“价格”列中包含空值，将会出现什么情况？（A）

A.该语句将失败，因为值不能被0除

B. 将显示0值

C.将显示10值

D.该语句将失败，因为值不能被空值除

5.如果需要从雇员表中查询部门10（销售部）中薪金不足25000美元的雇员信息，应执行以下哪个查询语句才能完成此任务？（C）

A. select 姓氏，名字，薪金from雇员 where 薪金>25000and 部门标识=10；

B. select 姓氏，名字，薪金from雇员 where 薪金=25000and 部门标识=10；

C. select 姓氏，名字，薪金from雇员 where 薪金<25000and 部门标识=10；

D. select 姓氏，名字，薪金from雇员 where 薪金<25000or 部门标识=10；

6.评估以下select语句：

Select min(聘用日期)，部门标识from雇员group By部门标识；

将显示哪个值？（A）

A. 每个部门中最早的聘用日期

B. 雇员表中最早的聘用日期

C. 雇员表中最晚的聘用日期

D. 包含空值的雇员表中的聘用日期

7，以下哪个说法正确的解释了无法执行以下sql语句的原因？（B）

Select部门标识“部门”，avg（薪金）“平均值” from 雇员group　by部门；

Ａ.　无法对薪金求平均值，因为并不是所有的数值都能被平分

Ｂ.　不能在group by　字句中使用列别名

Ｃ.　group by　子句中必须有要分组的内容

Ｄ.　部门表中没有列出部门标识

８．应使用以下哪个统计函数来显示雇员表中的最高薪金值？（C）

Ａ.avg Ｂ.count　Ｃ.max　Ｄ.min

９．统计函数将针对（）返回一个值，并在计算过程中（）空值．（A）

Ａ.行集，忽略　B.每行，忽略　　Ｃ.行集，包括　　Ｄ.每行，包括

10.可对数据类型为ｄａｔｅ的列使用以下哪个统计函数？（B）

Ａ．AVG　Ｂ．MAX　　　Ｃ．STDDEV　Ｄ．SUM　

Oracle数据库——用户、方案的创建与管理

一、涉及内容

1.掌握用户、方案与权限的基本概念。

2.熟练掌握用户操作的相关命令。

二、具体操作

（一）选择题：

1.关于方案的描述下列哪一项不正确？（C）

　　A.表或索引等对象一定属于某一个方案

　　B.在oracle 数据库中，方案与数据库用户是一一对应

　　C.一个表可以属于多个方案

　　D.一个方案可以拥有多个表

2.下列哪个对象属于方案对象？（C ）

　　A.数据段

　　 B.盘区

　　 C.表

　　 D.表空间

3.以下哪个命令用来连接Oracle 数据库？（B ）

　　A.CREATE

　　B.CONNECT

　　C.ALTER

　　D.SELECT

（二）简答题：

1.简要介绍方案与用户之间的关系？

答：Oracle用户，通俗的讲就是访问Oracle数据库的“人”。每个用户都有一个口令和相应的权限。而方案是一系列逻辑数据结构或对象的集合,可以理解为命名空间或包。一个方案只能够被一个数据库用户拥有，并且方案的名称与这个用户的名称相同，当创建新用户时，系统自动创建该用户的方案。该用户创建的方案对象默认被保存在自己的方案中。

2.说明在CREATE USER命令中各个选项的作用是什么？哪些是必须有的？

答：CREATE USER命令中各个选项如下：

（1）CREATE USER username ---用户名

（2）IDENTIFIED BY password---口令

（3）[DEFAULT TABLESPACE tablespace ]--- 缺省的表空间,该用户创建的对象默认存放在这里。

（4）[TEMPORARY TABLESPACE tablespace] ---该用户使用的缺省的临时表空间

（5）[QUOTA [integer K[M]][UNLIMITED] ON tablespace_name ---用户在该表空间中允许使用k[m]字节

（6）[PROFILES profile_name] ---概要文件的名字。对用户能够使用的资源进行限制。

（7）[PASSWORD EXPIRE] ---立即将口令设成过期状态，用户在登录进入前必须修改口令。

（8）[ACCOUNT LOCK or ACCOUNT UNLOCK] ---用户是否被加锁。

其中，（1）CREATE USER username 和（2）IDENTIFIED BY password是必须有的，其余是可选的。

（三）操作题：

1.创建用户ora_temp,密码为tem。

先使用system/orcl1234 连接到SQL*Plus ，使用以下语句创建用户

2.将用户ora_temp密码改为ora。

3.用户ora_temp帐号锁定。

4.将用户ora_temp帐号解锁。

5.授予用户ora_temp建立会话的权限（create session）。

6.使用ora_temp身份连接数据库。

7.断开ora_temp用户的连接，使用system身份连接到数据库。

8.删除ora_temp用户。

9.使用OEM工具为内置用户账户scott解锁，查看scott用户方案的所有表。

　　（1）在浏览器中输入URL地址为：http://localhost:1158/em 进入OEM登录界面

　　（2）在“管理”界面找到“用户”，点击打开用户

　　（3）进入“用户”后，选择“scott”用户

（4）编辑“scott”用户信息，状态为“未锁定”，而后点击“应用”保存。

（5）在“管理”选项卡，点击进入“表”后，点击选择方案，选择“scott”

（6）选择“scott”,

（7）查看scott用户方案的所有表

Oracle数据库——表的创建与管理

一、涉及内容

　　1.掌握使用OEM工具创建、修改和删除表。

　　2.掌握使用SQL语句创建、修改和删除表。

　　3.掌握使用SQL语句插入、修改和删除数据。

　　4.理解各种约束的作用，并能够使用OEM工具和SQL命令为表创建各种约束。

二、具体操作

（一）分别使用OEM和SQL语句完成下面的内容。

1.创建表并添加相应的约束。要求：

（1）创建名为student（学生信息）的表，表中各列要求如下：

字段名称	字段类型	大小	说明
sno	CHAR	10	主键
sname	VARCHAR	8
sex	CHAR	2	默认值为男，只能输入男或者女
birthday	DATE
sdept	CHAR	20

使用 OEM：

使用SQL语句：

（2）创建名为course（课程信息）的表，表中各列要求如下：

字段名称	字段类型	大小	说明
cno	CHAR	10	主键
cname	CHAR	30	唯一性
ccredit	NUMBER	3

使用OEM：

使用SQL：

（3）创建名为score（学生成绩）的表，表中各列要求如下：

字段名称	字段类型	大小	取值范围	说明
sno	CHAR	10	数据来自学生信息表	主键
cno	CHAR	10	数据来自学生课程表	主键
grade	NUMBER	3	0~100

使用OEM：

使用SQL:

2.增加、修改和删除字段，要求：

（1）给student表增加一个memo（备注）字段，类型为VARCHAR2（200）。

（2）将memo 字段的类型修改为VARCHAR2（300）。

（3）删除memo字段。

3.向表中添加数据、更新数据、删除数据，并验证约束。要求：

（1）使用INSERT INTO 命令向三个表中分别插入若干行数据，验证主键约束、唯一约束以及默认约束。

向student 表插入数据：

查看已经插入数据的学生表：

向course 表插入数据并查看：

向score 表插入数据并查看数据：

（2）使用UPDATE 命令更新数据，验证外键约束。

（3）使用DELETE 命令删除数据。

4.删除表。要求：

（1）使用OEM工具删除表。

使用OEM工具创建student表

创建主键约束：

删除学生表：

（2）利用DROP TABLE 语句删除表。

（二）习题

1.以下关于insert语句的values子句的说法哪一个是正确的？ (AB)

A、如果没有指定字段的列表，则这些值必须按照表中列的顺序列出

B、insert 语句中的values子句是可选的

C、在values子句中，字符、日期和数字数据必须用单引号引起来

D、要在values子句中指定一个空值，可使用字符串（“”）

2.为人力资源部门设计表，此表必须用一列来包含每个雇员的聘用日期，应该为此列指定以下哪一种数据类型 (B)

A、char

B、date

C、timestamp

D、interval year to month

3.如果莫一列用于存储多大4GB的二进制数据，则应该定义为那种数据类型？ (C)

A、long

B、number

C、blob

D、longraw

4.需要删除student表中所有数据、该表的结构以及与该表相关的索引，应使用下面哪一条语句（A）

A、drop table

B、truncate table

C、alter table

D、delete table

5.以下关于创建表的说法哪个是正确的？(B)

A、使用create table 语句时，随时会在当前用户方案中创建表

B、如果create table 语句中没有明确包含某个方案，则会在当前用户方案中创建表

C、如果create table 语句中没有明确包含某个方案，create table 语句则会失效

D、如果create table 语句中明确包含某一个方案，但是该方案不存在，则会创建该方案

6.以下关于列的说法哪个是正确的？ (C)

A、不可以增大char列的宽度

B、如果列包含非空数据，则可以修改列的数据类型

C、可以将char 数据类型的列转换为varchar2数据类型

D、可以将data数据类型列转换为varchar2数据类型

7.以下关于not null 约束条件的说法哪个是正确的？ (A)

A、必须在列级定义not null约束条件

B、可以在列级或表级定义not null约束条件

C、not null约束条件要求列包含字母数字值

D、not null约束条件要求列并不能包含字母数字值

8.以下关于foreign key约束条件的说法哪个正确？(C)

A、自动为foreign key约束条件创建索引

B、foreign key约束条件允许受约束的列包含存在与父表的主键或特殊键列中的值

C、foreign key约束条件要求在将某个值添加到受约束的列之前检查允许的值列表

D、foreign key列可以具有与其引用的主键列不同的数据类型

9.Oracle允许你在子表中创建foreign key约束条件之前，父表应当先具备什么条件？ (B)

A、在父表的主键列已经存在foreign key约束条件

B、在父表中必须存在primary key 或unique约束条件

C、在父表中必须存在索引

D、在父表中必须存在check约束条件

10．需要对雇员表的雇员标识列添加primary key 约束条件，应该使用一下哪条alter table 语句（A）

A、alter table 雇员 add primary key（雇员标识）；

B、alter table 雇员 add constraint primary key（雇员标识）；

C、alter table 雇员 modify 雇员标识 primary key

D、alter table 雇员 modify constraint primary key（雇员标识）；

Oracle数据库—— PL/SQL基础编程

一、涉及内容

　　1. 掌握PL/SQL程序块的结构，理解并熟悉各种变量的应用。

二、具体操作

（一）使用system用户登录SQL*PLUS，使用SQL语句创建用户：u_你的姓名首字母（例如：u_zs），密码为： t_你的学号后三位（例如：t_165）。并授予新用户连接数据库的权限，然后以新用户连接数据库。

1. 创建用户u_sxl

2. 授予新用户u_sxl连接数据库的权限

3. 以新用户u_sxl连接数据库

（二）使用scott用户连接数据库，使用SQL语句创建表dep_你的学号后三位（例如：dep_165）

注意：创建表的同时要创建约束

列名数据类型列的说明

dno integer 系编号(主键约束)

dname char(12) 系名(唯一约束)

1. 使用scott用户连接数据库(先修改一下密码)

2. 创建表dep_120

（三）使用SQL语句创建表stu_你的学号后三位（例如：stu_165）

注意：创建表的同时要创建约束

列名数据类型列的说明

sno integer 学号(主键约束)

sname Varchar2(20) 姓名(非空约束)

sage integer 年龄(check约束：在12~99岁之间)

sdep integer 系编号(外键约束)

创建的表格如下所示：

（四）使用scott方案下的emp表和dept表完成下列查询。（注意：每道题只能使用一条SQL语句实现）

1. 查询20号部门的所有员工的姓名、部门名称（要求：使用NATURAL JOIN）

2. 查询部门地点（LOC）、员工姓名。如果某部门没有员工，也要显示部门地点。（要求：使用左外连接）

3. 查询30号部门的员工奖金（COMM）的合计值。

4. 查询每个员工的姓名和他顶头上司（MGR）的姓名（要求：使用自身连接）

5. 查询月薪低于2100元，并且所在部门编号是30的员工编号和月薪。（要求：使用INTERSECT）

6. 查询平均工资最高的两个部门的部门编号。（要求：使用rownum伪列）

7. 查询与ALLEN的所在部门和雇用年份相同的所有员工的姓名。（要求：使用多列子查询）

8. 按工作岗位（job）分组，查询每种岗位的员工个数

9. 按工作岗位（job）分组，查询每种岗位的薪水的最低值。

10. 将“SALES”部门的员工薪水增加15% （注意：需要使用两张表）

11. 查询平均月薪高于2800元的部门名称、员工姓名、部门平均月薪。

12. 从emp表中删除所在部门位于“BOSTON”的员工的信息。（注意：需要使用两张表）

（五）编写PL/SQL程序块，每道题目使用一个程序块实现。

1、创建并执行一个PL/SQL程序块，通过替代变量由键盘输入两个数值x和y，用第一个数x乘以第二个数y再除以10取整（小数四舍五入），将结果存入变量：v_你的姓名首字母（例如：v_zs），然后输出到屏幕上。

2、创建并执行一个PL/SQL程序块，查询scott方案的emp表中的最高薪水，将其存入变量：v_你的姓名首字母（例如：v_zs）中，然后在屏幕上显示。

3、创建并执行一个PL/SQL程序块，计算scott方案中emp表的某个雇员的年薪，雇员的编号通过替代变量由键盘输入，年薪等于月薪sal乘以12个月再加上奖金comm，使用NVL函数处理空值。将计算出的年薪存入变量：v_你的姓名首字母（例如：v_zs）中，然后将结果输出到屏幕上。

4、创建并执行一个PL/SQL程序块，向dept表中添加一行数据，用替代变量输入部门的编号和名称，部门位置为NULL。

5、创建并执行一个PL/SQL程序块，更新部门位置的值，用替代变量输入部门的编号和新的部门位置。

6、创建并执行一个PL/SQL程序块，删除第4题添加的部门，用替代变量输入部门的编号。

Oracle数据库——索引、视图、序列和同义词的创建

一、涉及内容

　　1.理解索引的概念和类型。

　　2.掌握创建索引的命令。

　　3.理解视图的概念和优点。

　　4.理解可更新视图应具备的特点。

　　5.掌握创建一般视图和可更新视图的命令。

　　6.理解序列和同义词的概念和作用。

　　7.掌握序列的创建与应用。

　　8.掌握同义词的创建与应用。

二、具体操作

（实验）

1.在数据库中创建Student表，包括学号Id、姓名Name、性别Sex、班级编号Class_id。利用该表创建如下索引：

（1）在Id字段上创建唯一的B树索引。

（2）在Name字段上创建普通的B树索引。

（3）在Sex 字段上创建位图索引。

语句：

--建表： create table Student
 (Id char(6),
  Name varchar2(8),
  Sex char(2),
  Class_id char(4)); create unique index index_id on Student(Id)； create index index_name on Student(Name); create bitmap index index_sex on Student(Sex);

截图：

2.利用scott.emp 表创建视图并完成以下操作：

（1）创建简单视图。

语句：

conn scott/tiger; grant insert,update,delete on emp to system;

conn system/orcl1234; create or replace VIEW v_emp as select empno,ename,job,hiredate,deptno from scott.emp;

截图：

（2）查看视图的结构。

语句：SQL> desc v_emp;

截图：

（3）从视图中查询数据。

语句：SQL> select * from v_emp where deptno=10;

截图：

（4）查看视图中各字段的可更新性。

语句：select * from USER_UPDATABLE_COLUMNS where table_name='V_EMP';

截图：

（5）向视图中增加新数据。

语句：insert into v_emp values(1234,'JACK','CLERK','29-4月-1963',10);

截图：

（6）利用视图修改数据。

语句：update v_emp set ename='Mark' where empno=1234;

截图：

（7）利用视图删除数据。

语句：delete from v_emp where empno=1234;

截图：

3.利用Scott.emp 创建只读视图。

语句：

create or replace VIEW v_emp_readonly as select empno,ename,job,hiredate,deptno from scott.emp with read only;

截图：

对视图执行删除操作，测试视图的只读性：

语句：delete from v_emp_readonly where empno=7782;

截图：

4.利用Scott.emp创建具有With check option 选项的检查视图。

语句：

create or replace VIEW v_emp_check as select empno,ename,job,hiredate,deptno from scott.emp where deptno=10 with check option;

截图：

创建检查视图，通过视图对数据源表执行插入和修改操作。

语句：

insert into v_emp_check values(1235,'JACK','CLERK','29-4月-1963',20);

截图：

部门编号20 不符合Where 条件，插入失败。

语句：update v_emp_check set deptno=20 where deptno=10;

截图：

修改后的新数据不符合where 条件，修改失败。

5.利用Scott方案下的emp表和Dept表创建连接视图，查询部门编号为10和30的部门及雇员信息。

创建视图：

语句：

create or replace VIEW v_dept_emp as select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal from scott.dept a,scott.emp b where a.deptno=b.deptno and a.deptno in (10,30);

截图：

利用视图查询表中的数据：

查看视图中个字段的可更新性：

语句：select * from USER_UPDATABLE_COLUMNS where table_name='V_DEPT_EMP';

截图：

（习题）

1.简述索引有哪些类型，并说明什么情况下适合建立反向键索引，什么情况下适合建立位图索引。

答：常见的索引类型包括B树索引﹑位图索引﹑反向键索引﹑基于函数的索引﹑全局索引和局部索引等。反向键索引是一种特殊类型的B树索引，特别适合基于有序数列建立的索引。

当列的值是可以枚举（也就是说列的值差异性较小）的时候适合建立位图索引。

2.简述视图的概念以及利用视图操作数据的优点。

答：

视图的概念：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，用户可以像使用普通表一样对视图执行各种DML操作，如SELECT、insert、update、delete。但是，视图并不在数据库中真正存储有数据，它的数据来自于定义视图的查询所引用的表，而且这些数据是在使用视图时动态生成的。因此视图在数据库中只对应着一个SELECT语句的定义，可以从一个表或多个表中查询。对视图的各种操作实际上是对SELECT语句中数据源表的操作，当数据源中的数据发生变化时，视图的查询结果也会发生变化。

利用视图操作数据的优点：

（1）简化数据操作

包含的数据较少

将对多个表的操作简化为对一个视图的操作

（2）增强数据的安全性

可以将安全性控制到任意的数据子集

（3）定制数据

不同的级别的用户可以看到不同用途的数据集

（4）合并与分割数据

（5）利用视图修改源表

3.简述可更新视图应具有哪些特点。

答：

（1）创建视图时不能选择WITH READ ONLY选项。

（2）视图中的非计算列或非聚合运算，即数据源表中的原始字段，才可以被更新。

（3）视图的定义中SELECT语句不能包含DISTINCT关键字。

（4）视图的定义中SELECT语句不能包含集合操作如，UNION、INTERSECT等。

（5）视图的定义中SELECT语句不能包含GROUP BY子句和HAVING子句。

（6）用户必须对视图的数据源表具有显示的操作权限才可以。

（7）只有在视图中可见的行和列才可能被修改或删除。

4.操作题：

（1）建立一个表myEMP ，表结构和表中数据与scott.emp相同。

语句：

create table myEMP as select * from scott.emp;

截图：

（2）在myEMP表中建立基于字段empno的唯一索引。

语句：create unique index index_empno on myEMP(empno);

截图：

（3）建立一个视图myV_emp，视图包括myEMP表中的empno、ename、sal，并按sal从大到小排列。

语句：

Create or replace VIEW myV_emp As Select empno,ename,sal From myEMP Order by sal desc;、

截图：

（实验）

1.使用新建序列的值，为新表填充字段值。

（1）创建一张新表。

（2）创建序列id_seq。

（3）将序列的值插入到新表中。

2.以scott用户登录数据库，并在数据库中创建emp表的同义词s_e。

（1）以system 用户登录后授予scott用户创建同义词的权限。

（2）以scott用户登录，创建公有同义词。

（3）以System用户登录，利用同义词来操作原表。

（P199 10.4 习题）

1.简述同义词和序列的概念。

答：

同义词的概念：

同义词是表、索引、视图等方案对象的一个别名，不占据任何实际的存储空间，只在数据字典中保存其定义。

序列的概念：

序列可在当前方案下产生一系列唯一数字，可以用这些数字产生表的主键值，也可以参与其他运算。序列也可以在多用户并发环境中使用，为所有用户生成不重复的顺序数字，而且不需要任何额外的I/O开销。

2.简述使用同义词的好处。

答：同义词可以简化原数据库对象的名称，方面用户对数据库对象的引用。

3.序列常用的两个运算符是什么？各代表什么意义？

答：序列常用的两个运算符是NEXTVAL和CURRVAL。NEXTVAL将返回序列生成的下一个值，而CURRVAL将返回序列的当前值。第一次应用序列时，需要使用NEXTVAL，返回的是初始值。而以后再使用NEXTVAL运算符时，会使序列自动增加INCREMENT BY后面定义的值。

create or replace VIEW v_emp_check

select empno,ename,job,hiredate,deptno

from scott.emp

where deptno=10

with check option;

About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。