oracle数据库基本操作四——视图、授权控制与事务处理

4． SQL视图的定义与操纵：

例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。

create view student_cs as select sno,sname,ssex,sage,sdept,sclass from student where sdept = 'CS' with check option;

例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。

create view student_gr as select sno,avg(score) as avgsc from score group by sno;

例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。

select * from student_cs where sage > 19;

例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。

select * from student_gr where avgsc > 88;

例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。

insert into student_cs(sno,sname,sdept,sclass,ssex,sage) values('96006','张然','CS','02','男',19);

例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。

update student_cs set sage = sage + 1;

例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。

update student_gr set avgsc = avgsc + 2;

//ORA-01732: 此视图的数据操纵操作非法

　　出现错误原因，在创建视图时，没有在末尾加上with check option ;

例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。

delete from student_cs where sno = '96006';

例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。

drop view student_gr;

//正确答案应该是  delete from student_gr;

　　用delect 删除不了student_gr, 原因:在创建视图时，没有在末尾加上with check option ;

例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。

 drop view student_cs;

 //drop view student_gr;

5． SQL数据控制语句：

例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。

grant select on student to sno_74276,sno_74278 with grant option;

　　若没有用户需创建用户，方法为：

创建用户：create user 用户名 identified by 密码;
解锁：alter user 用户名 account unlock;　
赋权：grant sysdba to 用户名;

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。

grant all on teach to sno_74276 with grant option;

例5-3： (授权) 给所有用户授予Score表上的SELECT权限。

 grant select on score to public ;

例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。

conn sno_74276/s123
select * from sunc.student;//可以查询
select * from sunc.course;//不允许查询未赋权表或者视图
delete from sunc.student where sname = '**晨';//权限不足
select * from sunc.score;//可以访问
......

例5-5： (收回授权) 收回上面例子中的所有授予的权限。

revoke select on student from sno_74276,sno_74278;
revoke all on teach from sno_74276;
revoke select on score from public;

6． SQL事务处理：

例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

update course set ctime = 80 where cname = '程序设计';
update course set ctime = 70 where cname = '微机原理';
rollback;

　　update 修改ctime之后，数据确实被修改了,但是并没有用commit进行提交，所以在使用rollback事务回退时，会返回原来的数据;

例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

update course set ctime = 80 where cname = '程序设计';
update course set ctime = 70 where cname = '微机原理';
commit;//事务提交
rollback;//事务回退

在进行事务提交之后修改后的数据被保存下来，在使用事务回退，并不会返回最初始的值；

本次操作中出现的错误及解决方法：

1、ORA-01045: user SNO_74276 lacks CREATE SESSION privilege; logon denied

分析：创建User时没有给赋上create session权限

解决方法：grant create session,resource to sno_74276;

2、SP2-0640: 未连接

解决方法：用sysdba 登录如 conn sys as sysdba/s123;