第三次实验反思

最新推荐文章于 2026-01-21 12:44:23 发布

原创最新推荐文章于 2026-01-21 12:44:23 发布 · 837 阅读

21 ·

本内容遵循CC 4.0 BY-SA版权协议

《数据库系统原理》

一、实验目的

3.1–SQL的视图功能

理解视图的作用，并能根据实际需求用SQL创建视图
对基本表按需定义视图，并理解可以通过视图实现对基本表的操作

3.2–游标操作

通过ORACLE环境下的游标操作，理解SQL的游标概念
综合应用SQL的DML命令，通过游标操作数据库

二、实验内容

3.1–SQL的视图功能代码实现

/*一、用create view语句定义视图*/
--<1> 建立信息系学生的视图，然后向其中插入一计算机学生数据，分别查看视图和基本表
drop view is_student;
create view is_student as select * from student where sdept = 'is';
insert into is_student values('s8', 'ssn8', 'f', 20, 'cs');
select * from student;
select * from is_student;
delete from student where sno = 's8'; --执行完就把加入的数据删掉，防止再次运行出现bug
--<2> 建立信息系学生的视图，并要求修改和插入操作时仍需保证该视图只有信息系的学生
    --，然后插入一计算机学生的数据
drop view is_student2;
create view is_student2 as select * from student where sdept = 'is'
    with check option;
insert into is_student2 values('s8', 'ssn8', 'f', 20, 'cs');
select * from student;
select * from is_student2;
delete from student where sno = 's8'; --执行完就把加入的数据删掉，防止再次运行出现bug
--<3> 建立信息系选修了一号课程并且成绩在90分以上的学生的视图
drop view is_c1_ber;
create view is_c1_ber as select * from sc where cno = 'c01' and grade > 90 
    and sno in(select sno from student where sdept = 'is');
select * from is_c1_ber;
--<4>将学生的学号及他的平均成绩定义为一个视图S-G
select * from sc;
drop view st_avg;
create view st_avg(sno, gavg) as select sno, avg(grade) from sc group by sno;
select * from st_avg;
--<5>在sc中成绩在相应科目平均成绩之上的元组定义成一个视图GOOD-SC
drop view good_sc;
drop view cno_avg;
create view cno_avg(cno, g_avg) as select cno, avg(grade) from sc group by cno;
    --过渡表，用来存储各课程的平均成绩
select * from cno_avg;
create view good_sc as select sc.sno, sc.cno, sc.grade from sc, cno_avg 
    where sc.cno = cno_avg.cno and grade > g_avg;
select * from good_sc;
/*二、通过视图对基本表进行操作*/
--<1>在信息系学生的视图中找出年龄小于20岁的学生
select * from is_student where sage < 20;
--<2>查询信息系选修了一号课程且成绩在90分以上的学生
select * from is_c1_ber where cno = 'c01' and grade > 90;
--<3>在S-G视图中查询平均成绩在90分以上的学生学号和平均成绩
select sno, gavg from st_avg where gavg > 90;
--<4>创建CS-KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩（保证对该视
    --图的修改都要符合专业名为‘计算机’这个条件）
drop view CS_KC;
create view CS_KC as select * from sc where sno in
    (select sno from student where sdept = 'cs') with check OPTION;
select * from CS_KC;
--<5>查找计算机专业的学生学号和选修的课程号
select sno, cno from CS_KC; 
--<6>向S-G视图中插入一元组（操作结果如何？解释）
insert into st_avg values('s8', 85);
select * from st_avg;

3.2–游标操作代码实现

/*<1>从员工表中取出某一部门的员工姓名和工资，存入TEMP表中*/
drop table temp;
create table temp(
    fname varchar(20),
    lname varchar(25),
    sal number(8,2)
);
--①用简单循环控制(例一）
declare
    v_deptno employees.department_id%type:=&p_deptno;
    v_fname employees.first_name%type;
    v_lname employees.last_name%type;
    v_sal employees.salary%type;
    cursor c_fls is select first_name,last_name,salary 
        from employees where department_id=v_deptno;
begin
    open c_fls;
    loop
        fetch c_fls into v_fname,v_lname,v_sal;
            exit when c_fls % notfound ;
        insert into temp values (v_fname,v_lname,v_sal);
    end loop;
    close c_fls;
    commit;
end;
select * from temp;
--②用游标的for循环（例二）
declare
    v_deptno employees.department_id%type:=&p_deptno;
    cursor c_cour is select first_name, last_name, salary
        from employees where department_id=v_deptno;
begin
    for c_record in c_cour loop
        insert into temp 
            values(c_record.first_name, c_record.last_name, c_record.salary);
    end loop;
    commit; 
end;
select * from temp;
/*<2>查询employees表某部门的雇员情况，如果某雇员的工资小于800，则将其工资设为800（例四）*/
declare
    v_deptno employees.department_id%type:=&p_deptno;
    cursor c_cur is select employee_id, job_id, salary from employees 
        where department_id=v_deptno for update of salary;
begin
    for c_record in c_cur loop
        if c_record.salary < 800 then
            update employees set salary = 800 where current of c_cur;
        end if;
    end loop;
    commit;
end;
/*<3>为职工长10%的工资，从最低工资开始长，最后工资总额限制在50万元以内（例五）*/
declare
    cursor cur is select employee_id, salary from employees 
        order by salary for update of salary;
    per_sal employees.salary%type;
    sum_sal employees.salary%type;
    per_id employees.employee_id%type;
begin
    open cur;
    select sum(salary) into sum_sal from employees;
    while sum_sal < 500000 loop
        fetch cur into per_id, per_sal;
            exit when cur % notfound;
        update employees set salary = salary*1.1 where current of cur;
        sum_sal:= sum_sal + per_sal*0.1;
    end loop;
    close cur;
    commit;
end;

/*<1>将每个学生学科中的最高分对应的数据存入scmax表中*/
drop table scmax;
create table scmax(
    msno char(5),
    mcno char(3),
    mgrade number(3,0)
);
declare
    c_sno sc.sno%TYPE;
    c_cno sc.cno%type;
    c_gra sc.grade%type;
    cursor c_sc is select * from sc where grade in
        (select max(grade) from sc scx group by sno having scx.sno=sc.sno);
begin
    open c_sc;
    loop
        fetch c_sc into c_sno,  c_cno, c_gra;
            exit when c_sc % notfound;
        insert into scmax values(c_sno, c_cno, c_gra);
    end loop;
    close c_sc;
    commit;
end;
/*<2>对于学生选课表sc，创建相同的结构的新表sc1，并将sc数据全部加入到sc1，
    然后通过游标对sc1表进行更新：成绩为：80~100 更新为5；60~80 更新为3；低于60分更新为0;*/
drop table sc1;
create table sc1(
    sno char(5),
    cno char(3),
    grade number(3,0)
);
declare
    sn sc.sno%type;
    cn sc.cno%type;
    gr sc.grade%type;
    cursor c_s is select sno, cno, grade from sc for update of grade;
begin
    open c_s;
    loop
        fetch c_s into sn, cn, gr;
            exit when c_s % notfound;
        if gr >= 80 and gr < 100 then
            insert into sc1 values(sn,cn,5);
        end if;
        if gr >= 60 and gr < 80 then
            insert into sc1 values(sn,cn,3);
        end if;
        if gr < 60 and gr > 0 then
            insert into sc1 values(sn,cn,0);
        end if;
    end loop;
    commit;
end;

三、实验反思

1、执行create view语句时显示权限不足

执行该语句的用户的权限不足，需要用管理员（system用户）给该用户赋予权限

--创建视图权限（单有这句无法成功）
grant create  view to user_name; 
--授予查询权限 
grant select any table to user_name; 
--授予权限 
grant select any dictionary to user_name;

2、创建view时加不加with check option的区别：

<1>不加with check option:

create view v_stu1 as select * from student where sdept = 'is';
insert into v_stu1 values('s8', 'ssn8', 'f', 20, 'cs');
select * from student;
select * from v_stu1;

此时显示加入成功

<2>加with check option：

create view v_stu2 as select * from student where sdept = 'is'
    with check option;
insert into v_stu2 values('s8', 'ssn8', 'f', 20, 'cs');
select * from student;
select * from v_stu2;

此时显示

ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

说明，加了with check option后，对创建的view进行了限制，后续对该视图的操作需要满足该视图的谓词条件。

3、对于定义view说明字段的问题

在第一部分第（4）题，代码

create view fvi4 as select sno, avg(grade) from sc group by sno;

此时运行会出现：

ORA-00998: must name this expression with a column alias

以我的理解，视图的创建默认是和后面的select基于的表的字段一样，又取决于select的信息，对于本题，select 得到的信息和表的字段不一样，所以在创建过程中就会报错。

解决方案是在视图名字后面声明字段：

create view fvi4(sno, gavg) as select sno, avg(grade) from sc group by sno;

4、对于视图的数据更新问题

第三部分第（6）题，插入元组如下：

insert into st_avg values('s8', 85);

会出现：

SQL 错误: ORA-01733: 此处不允许虚拟列
01733. 00000 -  "virtual column not allowed here"

由此，视图中的数据更新，能且仅能当该视图为简单视图（即为表中数据的简单查询）；

如果视图中出现以下情况时，视图不可更新。

（1）聚合函数；

（2）DISTINCT关键字；

（3）GROUP BY子句；

（4）ORDER BY子句；

（5）HAVING子句；

（6）UNION运算符；

（7）位于选择列表中的子查询；

（8）FROM子句中包含多个表；

（9）SELECT语句中引用了不可更新视图；

（10）WHERE子句中的子查询，引用FROM子句中的表；

（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）

5、PL/SQL问题（待解决）

在进行例一时，发现总会遇到这样的错误：

ORA-06550: 第 18 行, 第 1 列: 
PLS-00103: 出现符号 "SELECT"
06550. 00000 -  "line %s, column %s:\n%s"

其代码如下：

--例1：用简单循环控制从员工表中取出某一部门的员工姓名和工资，存入TEMP表中
drop table temp;
create table temp(
    fname varchar(20),
    lname varchar(25),
    sal number(8,2)
);
declare
    v_deptno employees.department_id%type:=&p_deptno;
    v_fname employees.first_name%type;
    v_lname employees.last_name%type;
    v_sal employees.salary%type;
    cursor c_fls is select first_name,last_name,salary 
        from employees where department_id=v_deptno;
begin
    open c_fls;
    loop
        fetch c_fls into v_fname,v_lname,v_sal;
            exit when c_fls % notfound ;
        insert into temp values (v_fname,v_lname,v_sal);
    end loop;
    close c_fls;
    commit;
end;
select * from temp;

经过百度和询问后，发现是PL/SQL的问题。（PL/SQL是一种程序语言，叫做过程化SQL语言）。

PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头）和异常处理部分（以exception开头）。在执行部分(从begin到end）中，不能使用单纯的select，要使用select into语句。

目前的解决办法是：保证select和之前的语句不同时执行。

6、SQL中的分支语句

①if–else分支语句

if status 1 then
    [begin]
    code 1;
    [end]
else
    [begin]
    code 2;
    [end]
end if;

②case–when分支语句

case x 
    when a then b
    when A then B
    ……
else code 1;
end case;

7、select from与select into from与insert into select语句区别

①select from语句

select value1, value2 from table;

此语句是查询语句，执行时要求要查询的表和值均要存在。如果表和值不存在会报错。

②select into from

select value1,value2 into table2 from table1;

此语句常用来复制表结构和表中数据，相比于select from语句，不仅仅是查询，还多了建立表的过程。执行时要求目标表（table2）不存在，因为该语句会自动创建一个表。

③insert into select

insert into table2(field1,field2,...) select value1,value2,... from table1;

此语句也用来复制表结构和表中数据。此语句在执行时要求目标表（table2）存在，并且相应字段（field1，field2……）也存在；还要注意Table2的主键约束，如果Table2有主键且不为空，则 field1， field2…中必须包括主键。