-- 创建用户utest 密码为utest1
create user c##utest identified by utest1;
-- 赋权
grant connect to c##utest;
grant create table to c##utest;
grant insert on 表名 to c##utest;
grant delete on 表名 to c##utest;
grant update on 表名 to c##utest;
grant select on 表名 to c##utest;
-- 收回权限
revoke connect from c##utest;
revoke create table from c##utest;
revoke select on 表名 from c##utest;
revoke all on 表名 from c##utest;
-- 使用用户登录
connect c##utest/utest1
-- 创建视图
create or replace view view_owners1 as
select * from t_owners where ownertypeid=1
利用该视图进行查询:
select * from view_owners1 where addressid=1
修改表结构
-- 添加一列
alter table student add snumber char(10);
-- 修改一列
alter table student modify snumber int;
-- 删除一列
alter table student drop column snumber;
SQL语句复习
-- 1、查询每个学生的平均分,列出学生的学号,平均分(savg),并按平均分降序排列。
select sno,avg(grade) as savg
from SC
group by sno
order by savg desc;
-- 2、查询选课人数大于等于3人的课程,列出课程号,课程名,选课人数(scnt),并按课程号升序排列。
select SC.cno,cname,count(sno) as scnt
from Course,SC
where Course.cno=SC.cno
group by SC.cno,cname
having count(sno)>=3
order by SC.cno;
-- 3、查询选课人数最多的课程。列出课程号,课程名。
select Course.cno,cname
from Course,SC
where Course.cno=SC.cno
group by Course.cno,cname
having count(sno)>=all(select count(sno) from SC group by SC.cno);
-- 4、查询CS系选课人数最多的课程。列出课程号,课程名,CS系的选课人数(CScnt)。
select SC.cno,cname,count(Student.sno)as CScnt
from Course,SC,Student
where SC.sno=Student.sno and SC.cno=Course.cno and sdept='CS'
group by SC.cno,cname
having count(Student.sno)>=all(select count(Student.sno)from SC,Course,Student where SC.sno=Student.sno an
游标
-- 1、使用静态游标,遍历CS系选择了“数学”的学生,列出学生的学号,姓名,成绩(按学号升序排列)。
-- Created on 2023/11/9 by LENOVO
declare
-- Local variables here
--遍历CS系选择了“数学”的学生,列出学生的学号,姓名,成绩(按学号升序排列)。
cursor cur1 is select sc.sno,sname,grade from student,sc,course where sc.sno = student.sno and sc.cno = course.cno and sdept = 'cs' and cname = '数学' order by sc.sno;
begin
dbms_output.put_line(rpad('学号',10) || rpad('姓名',10) || rpad('成绩',10));
dbms_output.put_line(rpad('-',25,'-'));
-- Test statements here
for i in cur1 loop
dbms_output.put_line(rpad(i.sno,10) || rpad(i.sname,10) || rpad(i.grade,10));
end loop;
end;
/**2、使用静态游标,遍历所有CS系学生的选课信息,打印学号,姓名,系别,课程名,成绩(按学号和课程名升序排列)。(使用rpad对齐打印,学号,姓名,系别和课程号都各占10个长度,学生变了用横线区分开)打印格式要求如下:
学号 姓名 系别 课程名 成绩
============================================(一共45个等号)
001 张三 CS C语言 78
001 张三 CS 数据库 90
001 张三 CS 英语 80
--------------------------------------------(一共45个减号)
002 李四 CS 数学 85
002 李四 CS 英语 75 **/
declare
cursor cinfo is
select s.sno,sname,sdept,cname,grade from student s
join sc on s.sno = sc.sno
join course c on sc.cno = c.cno
where sdept = 'cs'
order by s.sno,cname;
lastname varchar(20);
begin
dbms_output.put_line(rpad('学号',10)||rpad('姓名',10)||rpad('系别',10)||rpad('课程名',10)||'成绩');
dbms_output.put_line(rpad('=',45,'='));
for i in cinfo loop
if cinfo%rowcount > 1 then
if i.sname != lastname then
dbms_output.put_line(rpad('-',45,'-'));
end if;
end if;
dbms_output.put_line(rpad(i.sno,10)||rpad(i.sname,10)||rpad(i.sdept,10)||rpad(i.cname,10)||i.grade);
lastname:=i.sname;
end loop;
end;
存储过程和函数
/**1、创建存储过程Ps1,传入课程名,查询该课程的选修情况(按学号升序排列),在存储过程中遍历游标,
打印选修情况。使用异常捕获来处理课程不存在的情况,打印格式如下(学号打印长度10,课程号打印长度10):**/
create or replace procedure Ps1(p_cname course.cname%type) is
cursor c1 is select sno,cno,grade from sc where cno in (select cno from course where cname = p_cname);
c_cno course.cno%type;
begin
select cno into c_cno from course where cname = p_cname;
dbms_output.put_line(rpad('学号',10)||rpad('课程号',10)||rpad('成绩',10));
dbms_output.put_line(rpad('-',25,'-'));
for i in c1 loop
dbms_output.put_line(rpad(i.sno,10)||rpad(i.cno,10)||i.grade);
end loop;
exception when no_data_found then
dbms_output.put_line('该课程不存在');
end Ps1;
-- 创建过程pDopT,删除sc_number表,返回相关信息。
create or replace procedure pDopT is
v_info VARCHAR2(200);
begin
execute immediate 'drop table SC_NUMBER';
v_info := '已删除SC_Number表';
-- 返回相关信息
DBMS_OUTPUT.PUT_LINE(v_info);
end;
-- Created on 2023/12/8 by 86188
declare
-- Local variables here
i integer;
t VARCHAR2(200);
begin
pDopT;
end;
触发器
-- 创建触发器tr_SC_Delete,规定不能删除数据库课程的选课信息。抛出的异常提示为:不能删除数据库课程的选课信息。
create or replace trigger tr_SC_Delete
before delete on SC
for each row
declare
tcno SC.cno%type;
begin
select cno into tcno from Course where cname='数据库';
if(:old.cno=tcno) then
raise_application_error(-20000,'不能删除数据库课程的选课信息。');
end if;
end;
/** 创建SC表上的触发器,维护冗余列的数据。在Student表中添加冗余列sumG列(学生的总成绩,类型为int,初值为null),并初始化sumG列数据。假设SC表中成绩不能为0(即Student表中的sumG总成绩不会为0).在触发器中维护这一冗余列,保证sumG列数据的正确。
测试要求:请务必在创建了触发器后,在Oracle系统下分别为SC表进行增删改操作,并查看对应的SumG列的数据是否正确。要测试的部分包括:
关于插入的测试
(1)为某位没有选课的同学选课,插入成绩为空的行,然后观察sumG的变化。
(2)为某位没有选课的同学选课,插入成绩不为空的行,然后观察sumG的变化。
(3)为总成绩部位空的同学选课,插入成绩不为空的行,然后观察sumG的变化。
关于删除的测试
(1)删除成绩为空的行,然后观察sumG的变化。
(2)删除成绩不为的行,然后观察sumG的变化。
(3)删除某一个同学的所有选课记录,然后观察sumG的变化。
关于修改的测试
(1)将一行的成绩从空修改为一份分数,然后观察sumG的变化。
(2)将一行的成绩从有分数修改空,然后观察sumG的变化。
(3)将某一个同学的学号修改为另一个学号(例如,将001改为020,其中020是未选课的同学),然后观察sumG的变化。 **/
create or replace trigger tr_SC_IDU
after insert or delete or update on sc
for each row
declare
tsumG int;
tsumG2 int;
begin
if inserting then
--插入成绩不为空的行
if(:new.grade is not null) then
select sumG into tsumG from Student where sno=:new.sno;
--没有选课的同学
if(tsumG is null) then
update Student set sumG=:new.grade where sno=:new.sno;
else
--总成绩不为空的同学选课
update Student set sumG=sumG+:new.grade where sno=:new.sno;
end if;
end if;
end if;
if deleting then
if(:old.grade is not null) then
update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
select sumG into tsumG from Student where sno=:old.sno;
if(tsumG=0) then
update Student set sumG=null where sno=:old.sno;
end if;
end if;
end if;
if updating then
if(:old.grade is not null) then
update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
select sumG into tsumG from Student where sno=:old.sno;
if(tsumG=0) then
update Student set sumG=null where sno=:old.sno;
end if;
end if;
if(:new.grade is not null) then
select sumG into tsumG from Student where sno=:new.sno;
--没有选课的同学
if(tsumG is null) then
update Student set sumG=:new.grade where sno=:new.sno;
else
--总成绩不为空的同学选课
update Student set sumG=sumG+:new.grade where sno=:new.sno;
end if;
end if;
end if;
end;