日期:2014-05-17  浏览次数:20730 次

Oracle包内数据过程的问题
SQL code
   procedure sc_numberbiao(use_sno char, use_number char) is
    have    number; --用于确定是否存在表SC_NUMBER
    cnumber number; --已有课程数量
    excp_number exception; --自定义异常,用于确认用户选课熟练是否达到已有课程上限
  begin
    select count(*) into cnumber from course ;
    if use_number > cnumber or use_number < 0 then
      raise excp_number;
    end if;
    select count(*) into have from user_tables where table_name = 'SC_NUMBER';
    if have <> 0 then
      execute immediate 'update sc_number set cnonumber = use_number where sno = use_sno';
      if sql%rowcount = 0 then
        execute immediate 'insert into sc_number values (trim(use_sno), trim(use_number))';
        dbms_output.put_line('插入学号为' || use_sno || '的学生选课门数为' ||
                             use_number);
      else
        dbms_output.put_line('更新学号为' || use_sno || '的学生选课门数为' ||
                             use_number);
      end if;
    else
      execute immediate 'create table sc_number(sno char(20),cnonumber char(3))';
      execute immediate 'insert into sc_number values (trim(use_sno), trim(use_number))';
      dbms_output.put_line('建表sc_number并且插入学号为' || use_sno || '的学生选课门数为' ||
                           use_number);
    end if;
    commit;
  exception
    when excp_number then
      dbms_output.put_line('现在共有' || cnumber || '门课程可供选择,用户输入' ||
                           use_number || '不合法');
  end sc_numberbiao;

这段代码是想实现建立过程,当传入学号和选课门数,首先判断SC_Number表是否存在,若不存在则创建该表格(包括学号和选修门数两列),将传入值插入或修改到SC_Number表中(该生不存在则插入,若存在则修改其选课门数)。
但是测试总有这样那样的问题,比如列在此处不允许啊之类的问题,有些语句不能使用也只能改成动态SQL了,求高手帮忙看下程序,到底是哪些地方出问题了,头都大了- -

------解决方案--------------------
参考:
procedure sc_numberbiao(use_sno char, use_number char) 
is
have number; --用于确定是否存在表SC_NUMBER
cnumber number; --已有课程数量
excp_number exception; --自定义异常,用于确认用户选课熟练是否达到已有课程上限
begin
select count(*) into cnumber from course ; 
if use_number > cnumber or use_number < 0 then
raise excp_number; 
end if; 
select count(*) into have from user_tables where table_name = 'SC_NUMBER'; 
if have <> 0 then
execute immediate 'update sc_number set cnonumber = :1 where sno = :2'
using use_number,use_sno; 
if sql%rowcount = 0 then
execute immediate 'insert into sc_number values (:1,:2)' using use_sno, use_number;
dbms_output.put_line('插入学号为' || use_sno || '的学生选课门数为' || use_number); 
else
dbms_output.put_line('更新学号为' || use_sno || '的学生选课门数为' || use_number); 
end if; 
else
execute immediate 'create table sc_number(sno char(20),cnonumber char(3))';
execute immediate 'insert into sc_number values (:1,:2)' using use_sno, use_number;
dbms_output.put_line('建表sc_number并且插入学号为' || use_sno || '的学生选课门数为' || use_number); 
end if; 
commit; 
exception when excp_number then
dbms_output.put_line('现在共有' || cnumber || '门课程可供选择,用户输入' || use_number || '不合法');
 end sc_numberbiao;