Oralce

创建用户，赋权和收回权利

-- 创建用户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;