pkg_student.bdy

这个是用oracle和delphi开发的学生信息管理系统 包括oracle的存储过程和源码

create or replace package body pkg_student is
--登陆********************************************************************************
procedure   prc_login(v_username  varchar2,v_pwd  varchar2,ResultCursor   out   rc_class)
IS  
icount number(2);  
--flag   char(1);                        
BEGIN
    select   count(*) into icount from users where username = v_username;
    if icount = 0 then
       open ResultCursor   for
       select '用户名不存在' as "op_type" from dual;
    else
    begin 
       select count(*) into icount from users where username = v_username and password=v_pwd;
       if icount = 0 then
          open ResultCursor   for
          select '密码错误' as "op_type" from dual;
       else
           begin 
                 select count(*) into icount from users where username = v_username and password=v_pwd and user_level = 'A';
                 if icount = 0 then 
                    open ResultCursor for
                    select '一般用户暂时不能登陆' as "op_type" from dual;
                 else
                    open ResultCursor for
                    select '欢迎进入选课系统' as "op_type" from dual;
                 end if;
           end;
       end if;
    end;
    end if; 
END ;

--按学号查询学生信息********************************************************************************
procedure prc_query_student(p_sno varchar2,ResultCursor   out   rc_class)
is
  begin
         open  ResultCursor  for 
         select sno,sname,sage,sdept from student where sno=p_sno;       
End;
--学生信息管理********************************************************************************
procedure prc_student(s_no varchar2,s_name varchar2,s_age number,s_dept varchar2,a int,ResultCursor   out   rc_class)
is
scount number(2);
begin
--添加
  if(a=1)then
       select count(*) into scount from student where student.sno=s_no;
       if scount>0 then
          open ResultCursor   for
          select '该学号已存在' as "s_type" from dual;
           --异常处理
          insert into log(log_id,log_table,log_dml,log_date,log_username) values(seq_log_id.nextval,'LOG','INSERT',sysdate,user); 
       else
          insert into student (sno,sname,sage,sdept) values(s_no,s_name,s_age,s_dept);
          open ResultCursor   for
          select '添加成功' as "s_type" from dual;
       end if;        
  end if;
   --修改
   if(a=2) then
         update student set student.sname=s_name,student.sage=s_age,student.sdept=s_dept where student.sno=s_no;
         open ResultCursor   for
          select '修改成功' as "s_type" from dual;    
   end if;
   --删除
   if(a=3) then 
         select count(*) into scount from student where student.sno=s_no;
         if scount=0 then
          open ResultCursor   for
          select '不存在该学号' as "s_type" from dual; 
 
          else
               delete from student where sno=s_no;
               open ResultCursor   for
          select '删除成功' as "s_type" from dual;
           end if;      
   end if;
   end;
--按课程号查询课程信息********************************************************************************
procedure prc_query_course(c_cno varchar2,ResultCursor   out   rc_class)
is
  begin
         open  ResultCursor  for 
         select cno,cname,credit from course where cno=c_cno;       
End;
--课程信息管理********************************************************************************
procedure prc_course(c_no varchar2,c_name varchar2,c_credit number,b int,ResultCursor   out   rc_class)
is
ccount number(2);
begin
--添加
  if(b=1)then
       select count(*) into ccount from course where course.cno=c_no;
       if ccount>0 then
          open ResultCursor   for
          select '该课程号已存在' as "c_type" from dual;
 
       else
          insert into course (cno,cname,credit) values(c_no,c_name,c_credit);
          open ResultCursor   for
          select '添加成功' as "c_type" from dual;
       end if;        
  end if;
   --修改
   if(b=2) then
         update course set course.cname=c_name,course.credit=c_credit where course.cno=c_no;
         open ResultCursor   for
          select '修改成功' as "c_type" from dual;    
   end if;
   --删除
   if(b=3) then 
         select count(*) into ccount from course where course.cno=c_no;
         if ccount=0 then
          open ResultCursor   for
          select '不存在该课程号' as "c_type" from dual; 
 
          else
               delete from course where course.cno=c_no;
               open ResultCursor   for
          select '删除成功' as "c_type" from dual;
           end if;      
   end if;
   end;
--选课********************************************************************************
procedure prc_insert_sc(sc_cno varchar2,sc_sno varchar2,sc_grade number,sc_time date,ResultCursor   out   rc_class)
is
sccount number(2);
begin

   if(sc_sno is null)then
         open  ResultCursor  for 
       select '请输入学号' as "sc_type" from dual;
   return;
   end if;
    if(sc_cno is null)then
         open  ResultCursor  for 
       select '请输入课程号' as "sc_type" from dual;
   return;
   end if;
  
  select count(*) into sccount from sc where sc.sno=sc_sno and sc.cno=sc_cno; 
                  
   if(sccount=1)
   then
          insert into log(log_id,log_table,log_dml,log_date,log_username) values(seq_log_id.nextval,'LOG','sclog',sysdate,user); 
          update sc set sc.grade=sc_grade,sc.exam_date=sc_time where sc.sno=sc_sno and sc.cno=sc_cno;
          open  ResultCursor  for 
       select '选课成功' as "sc_type" from dual;
   end if;
   if(sccount=0)
   then
          insert into sc values(sc_sno,sc_cno,sc_grade,sc_time);
          open  ResultCursor  for 
       select '选课成功' as "sc_type" from dual;
    end if;
    
   end;
   --用户查询
   procedure prc_query_users(u_name varchar2,ResultCursor   out   rc_class   )
   is
   begin
   open  ResultCursor  for 
         select users.username,users.password,users.user_level from users where users.username=u_name;
   end;
   --用户管理
procedure prc_users(u_uname varchar2,u_pwd varchar2,u_level varchar,e int,ResultCursor   out   rc_class)
   is
   ucount integer;
   begin
   if(e=1)then
              if(u_level='A')then
               open  ResultCursor  for 
                      select '不能添加管理员' as "u_type" from dual;
                      return;
                      end if;
             select count(*) into ucount from users where users.username=u_uname;
             if(ucount=0) then
                     insert into users values(u_uname,u_pwd,u_level);
                      open  ResultCursor  for 
                       select '添加成功' as "u_type" from dual;
             else
             insert into log(log_id,log_table,log_dml,log_date,log_username) values(seq_log_id.nextval,'LOG','user',sysdate,user); 
               open  ResultCursor  for 
                       select '此用户名已存在' as "u_type" from dual;
            end if ;  
         end if;
      if(e=2)then
          update users set password=u_pwd,user_level=u_level where username=u_uname;
                  open  ResultCursor  for 
                       select '修改成功' as "u_type" from dual;
         end if;
        if(e=3)then  
               select count(*) into ucount from users where users.username=u_uname;
         if(ucount>0)then
               delete from users where username=u_uname;
              open  ResultCursor  for 
                       select '删除成功' as "u_type" from dual;
                       else
                       open  ResultCursor  for 
                       select '请输入要删除的项' as "u_type" from dual;
                       
         
          end if;
          end if;
   
   
   end;

end pkg_student;
/