set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[xxxxxx]
-- Add the parameters for the stored procedure here
@count int ,
@max_kecheng int , 最大课程数
@kecheng varchar(20) 课程名称
AS
BEGIN
select @count = count(*) from 选课表 wher 课程 = @kecheng group by 课程
select @max_kecheng = 最大课程 from 选课表 wher 课程 = @kecheng group by 最大课程
IF (@count<@max_kecheng)
BEGIN
--插入语句 END
END
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[xxxxxx]
-- Add the parameters for the stored procedure here
@count int ,
@max_kecheng int , 最大课程数
@kecheng varchar(20) 课程名称
AS
BEGIN
select @count = count(*) from 选课表 wher 课程 = @kecheng group by 课程
select @max_kecheng = 最大课程 from 选课表 wher 课程 = @kecheng group by 最大课程
IF (@count<@max_kecheng)
BEGIN
--插入语句 END
END
2)选题表,不用设置已选人数字段
3)触发器instead of实现超员则不插入数据,like this:CREATE OR REPLACE TRIGGER simo_tab_insert
INSTEAD OF INSERT ON v_simo_tab
BEGIN
declare
v_id number;
begin
select count(*) into v_id from simo_tab where name = :new.name;
dbms_output.put_line('Simo>>>>>>>>v_id:'||v_id);
if v_id>0 then
insert into simo_tab(id, name)
values (:NEW.id,:new.name);
end if;
end;
END;
create or replace function fun_st_course(crno in number, stno in number,stno in number) return integer is
PRAGMA AUTONOMOUS_TRANSACTION;
Result integer;
v_mx number;
v_cur number;
begin
select maxnum,course_curnum into v_mx,v_cur from tb_course where CourseNo=crno;
if v_mx>=v_cur+1 then
insert into tb_fin_course(CourseNo,StudentNo,TeacherNo) values (crno,stno,stno);
update tb_course set course_curnum=course_curnum+1 where CourseNo=crno;
commit;
Result:=1;
return(Result);
else
Result:=0;
return(Result);
end if;
end fun_st_course;