过程、函数和触发器编程练习题（1）

过程、函数和触发器编程练习题

1、编写一个存储过程，能通过“类型名称”直接从商品信息表中获取对应类型的商品数据
create or replace procedure p1(tn goodstype.typename%type)
is
tid goodstype.typeid%type;
cursor myc is select * from goods where typeid=tid;
cur myc%rowtype;
begin
select typeid into tid from goodstype where typename=tn;
  open myc;
  loop
    fetch myc into cur;
    exit when myc%notfound;
    dbms_output.put_line(cur.gid);
  end loop;
  close myc;
  exception
  when no_data_found then
  dbms_output.put_line('not data');
end;

begin
  p1('手机');
end;

2、创建一个存储过程，从student表查询指定系别的学生资料，包括学号、姓名、性别和系别字段
create or replace procedure p2(dp s.department%type)
is
cursor cur is select * from s where department=dp;
rec cur%rowtype;
begin
  open cur;
  loop
    fetch cur into rec;
    exit when cur%notfound;
    dbms_output.put_line(rec.sid||' '||rec.sname||' '||rec.sex||' '||rec.department);
  end loop;
  close cur;
end;

begin
p2('信息系');
end;

--创建一个存储过程，实现如下功能：存在不及格情况的学生的选课情况，包括学号、姓名、性别、课程号、课程名、成绩和系别
create or replace procedure p3
is
cursor cur is select s.sid,sname,sex,c.cid,cname,grade,department from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and grade<60;
rc cur%rowtype;
begin
open cur;
loop
fetch cur into rc;
exit when cur%notfound;
dbms_output.put_line(rc.sid||' '||rc.sname||' '||rc.sex||' '||rc.cid||' '||rc.cname||' '||rc.grade||' '||rc.department);
end loop;
end;

begin
p3();
end;

--4、创建一个存储过程，查询某一门课程的考试总分，以out模式返回
create or replace procedure p4(p_cname c.cname%type,s out sc.grade%type)
is
  p_cid sc.cid%type;
begin
  select cid into p_cid from c where cname=p_cname;
  select sum(grade) into s from sc where cid=p_cid;
end;
declare
s sc.grade%type;
begin
p4('数学',s);
dbms_output.put_line(s);
end;

--5、将第4题改为创建一个函数，功能相同
create or replace function f5(f_cname c.cname%type) return number
is
  f_cid sc.cid%type;
  s number;
begin
  select cid into f_cid from c where cname=f_cname;
  select sum(grade) into s from sc where cid=f_cid;
  return s;
end;

begin
dbms_output.put_line(f5('数学'));
end;

--6、创建存储函数，实现如下功能：输入学号，根据该学生选课的平均分显示提示信息：平均分大于等于90，显示“该生成绩优秀”，平均分小于90但大于等于80，显示“该生成绩良好”，平均分小于80但大于等于60，显示“该生成绩合格”，小于60，则显示“该生成绩不合格”。
create or replace function f6(f_sid sc.sid%type) return varchar2
is
  str varchar2(30);
  avg_grade number;
  
begin
  
    select avg(grade) into avg_grade from sc where sid=f_sid;
    case 
    when avg_grade>=90 then str:='该生成绩优秀';
    when avg_grade>=80 then str:='该生成绩良好';
    when avg_grade>=60 then str:='该生成绩合格';
    else str:='该生成绩不合格';
    end case;
    return str;
end;

begin 
  dbms_output.put_line(f6(103));
end;

--创建一个触发器，当student表中的学号变更时，同时修改sc表中相应学生的学号信息；
create or replace trigger t8 after update on s for each row
begin
  update sc set sid=:new.sid where sid=:old.sid;
end;
update s set sid=111 where sid=100