create procedure select_course
(IN pn varchar(15),IN sn int,OUT result int)
DEGIN
IF (select count(*) from scores where sno=sn)>(select slimits from subjects where sno=sn) THEN set result=0;
ELSE
IF EXISTS(select grade from scores where sno=sn and pno=pn)
THEN
set result=1;
ELSE
IF (select count(*) from scores where pno=pn and sno in (select sno from subjects where verifyflag=1))>=3 THEN
result=2;
ELSE insert into scores(pno,sno) values(pn,sn);
set result=3;
END IF
END IF
END IF
END;
求各位帮忙分析一下这段代码有无错误的地方,这是sqlserver2000的版本
create proc select_course
@pn varchar(15),@sn int,@result int output
as
begin
if(select count(*) from scores where sno=@sn)>(select slimits from subjects where sno=@sn)
set @result=0
else
if exists(select grade from scores where sno=@sn and pno=@pn) set @result=1
else
if(select count(*) from scores where pno=@pn and sno in (select sno from subjects where verifyflag=1))>=3
set @result=2
else
begin
insert into scores(pno,sno) values(@pn,@sn)
set @result=3
end
end
GO
我从网上看到2000的版本,想改成mysql版本,不知有没有写错,求给位看看
(IN pn varchar(15),IN sn int,OUT result int)
DEGIN
IF (select count(*) from scores where sno=sn)>(select slimits from subjects where sno=sn) THEN set result=0;
ELSE
IF EXISTS(select grade from scores where sno=sn and pno=pn)
THEN
set result=1;
ELSE
IF (select count(*) from scores where pno=pn and sno in (select sno from subjects where verifyflag=1))>=3 THEN
result=2;
ELSE insert into scores(pno,sno) values(pn,sn);
set result=3;
END IF
END IF
END IF
END;
求各位帮忙分析一下这段代码有无错误的地方,这是sqlserver2000的版本
create proc select_course
@pn varchar(15),@sn int,@result int output
as
begin
if(select count(*) from scores where sno=@sn)>(select slimits from subjects where sno=@sn)
set @result=0
else
if exists(select grade from scores where sno=@sn and pno=@pn) set @result=1
else
if(select count(*) from scores where pno=@pn and sno in (select sno from subjects where verifyflag=1))>=3
set @result=2
else
begin
insert into scores(pno,sno) values(@pn,@sn)
set @result=3
end
end
GO
我从网上看到2000的版本,想改成mysql版本,不知有没有写错,求给位看看
-> (IN pn varchar(15),IN sn int,OUT result int)
-> DEGIN
-> IF (select count(*) from scores where sno=sn)>(select slimits from subjec
ts where sno=sn) THEN set result=0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'IF (s
elect count(*) from scores where sno=sn)>(select slimits from subjects wher' at
line 4
出了这个问题,我刚学mysql没多久,不太懂啊
delimiter //
create procedure select_course
(IN pn varchar(15),IN sn int,OUT result int)
BEGIN
IF (select count(*) from scores where sno=sn)>(select slimits from subjects where sno=sn) THEN set result=0;
ELSE
IF EXISTS(select grade from scores where sno=sn and pno=pn) THEN
set result=1;
ELSE
IF (select count(*) from scores where pno=pn and sno in (select sno from subjects where verifyflag=1))>=3 THEN
result=2;
ELSE
insert into scores(pno,sno) values(pn,sn);
set result=3;
END IF;
END IF;
END IF;
END
//
delimiter;
create procedure select_course
(IN pn varchar(15),IN sn int,OUT result int)
BEGIN
IF (select count(*) from scores where sno=sn)>(select slimits from subjects where sno=sn) THEN set result=0;
ELSE
IF EXISTS(select grade from scores where sno=sn and pno=pn) THEN
set result=1;
ELSE
IF (select count(*) from scores where pno=pn and sno in (select sno from subjects where verifyflag=1))>=3 THEN
set result=2;
ELSE
insert into scores(pno,sno) values(pn,sn);
set result=3;
END IF;
END IF;
END IF;
END
//
delimiter;改好了,新手不容易啊