包

create or replace package SN
is
   procedure insert_info(Psno sc.sno%type,
                         Pcno sc.cno%type,
                         Pgrade sc.grade%type,
                         IsSuccess out number);
   procedure insert_info(Psname student.sname%type,
                         Pcno sc.cno%type,
                         Pgrade sc.grade%type,
                         Mess out varchar);
   procedure drop_info(Psno student.sno%type,
                       IsOk out number);
   --procedure drop_table;
end SN;

 

create or replace package body SN
is
--只用输入学号来判断，如果此学号存在就更新对应值+1，不存在就设置为1
  procedure add_sno(Psno in varchar)
  is
  num1 number; --用来判断表是否存在
  num2 number; --用来判断学生是否存在
  type c_type is ref cursor;
  c1 c_type;
  c_sno sc.sno%type;
  c_num int;
  begin
    
    select count(*) into num1 from user_tables where table_name = 'SC_NUMBER';--判断表是否存在
    if num1 = 0 then--不存在则建表并动态插入数据
      execute immediate
      'create table SC_Number(sno varchar(10) primary key,count_nums int)';
      open c1 for select sno,count(*) from sc group by sno;
      loop
        fetch c1 into c_sno,c_num;
        exit when c1%notfound;
        execute immediate
        'insert into SC_Number values (:1,:2)'
        using c_sno,c_num;
      end loop;
      close c1;
      commit;
    end if;
    
   execute immediate
   'select count(*) from SC_Number where trim(sno) = :Psno'
   into num2
   using Psno;--判断是否存在于SC_Number表中
   if num2 = 0 then--不存在新建
      execute immediate
      'insert into SC_Number values (:1,1)'
      using Psno;
      commit;
   else--存在就直接修改
      execute immediate
      'update SC_Number set count_nums =count_nums+1 where sno = :Psno'
      using Psno;
      commit;
   end if;
  end add_sno;
  
--以IsSuccess区别函数重载，0插入，1修改，2姓名重复，3表示没有课程活人物
   procedure insert_info(Psno sc.sno%type,
                         Pcno sc.cno%type,
                         Pgrade sc.grade%type,
                         IsSuccess out number)
   is
   p_sno student.sno%type;
   p_cno course.cno%type;
   p_num number;
   begin
    select sno into p_sno from student where sno = Psno; -- 判断学生是否存在
    select cno into p_cno from course where cno = Pcno;  --判断课程是否存在
    select count(*) into p_num from sc where cno=p_cno and sno=p_sno; --判断是否有选课
    if p_num=0 then --没有选课则插入
      insert into sc values(p_sno,p_cno,pgrade);
      commit;
      add_sno(p_sno);
      IsSuccess := 0;
      --dbms_output.put_line('插入成功');
    else     --选课就修改
      update sc set grade = pgrade where sno = p_sno and cno = p_cno;
      commit;
      IsSuccess := 1;
      --dbms_output.put_line('修改成功');
    end if;
    exception
      when TOO_MANY_ROWS then
        IsSuccess := 2;
        --dbms_output.put_line('姓名重复');
       when others then  --学生或课程不存在时出现no_data_found异常
         IsSuccess := 3;
         --dbms_output.put_line('未找到姓名或课程');
   end insert_info;
   
--以Mess区别重载
   procedure insert_info(Psname student.sname%type,
                         Pcno sc.cno%type,
                         Pgrade sc.grade%type,
                         Mess out varchar)
   is
   p_sno student.sno%type;
   p_cno course.cno%type;
   p_num number;
   begin
    select sno into p_sno from student where sname = Psname; -- 判断学生是否存在
    select cno into p_cno from course where cno = Pcno;  --判断课程是否存在
    select count(*) into p_num from sc where cno=p_cno and sno=p_sno; --判断是否有选课
    if p_num=0 then --没有选课则插入
      insert into sc values(p_sno,p_cno,pgrade);
      commit;
      add_sno(p_sno);
      Mess:='插入成功';
      --dbms_output.put_line('插入成功');
    else     --选课就修改
      update sc set grade = pgrade where sno = p_sno and cno = p_cno;
      commit;
      Mess:='修改成功';
      --dbms_output.put_line('修改成功');
    end if;
    exception
      when TOO_MANY_ROWS then
        Mess:='姓名重复';
        --dbms_output.put_line('姓名重复');
       when others then  --学生或课程不存在时出现no_data_found异常
         Mess:='未找到姓名或课程';
         --dbms_output.put_line('未找到姓名或课程');
   end insert_info;

--删除传入学号所对应的相关信息,返回数字1表示删除成功，0表示没有此人
   procedure drop_info(Psno student.sno%type,
                       IsOk out number)
   is
   p_sno student.sno%type;
   num1 int;
   begin
     select sno into p_sno from student where sno = Psno; -- 判断学生是否存在
     select count(*) into num1 from user_tables where table_name = 'SC_NUMBER';--判断表是否存在
     if num1=0 then
       IsOk := 1;
     else
       execute immediate 'delete from sc where sno = :p_sno'
       using p_sno;
       execute immediate 'delete from student where sno = :p_sno'
       using p_sno;
       execute immediate 'delete from SC_Number where sno = :p_sno'
       using p_sno;
       IsOk := 1;
     end if;
     exception
       when no_data_found then
         IsOk := 0;
   end drop_info;
end SN;