delimiter//
create procedure create_reader(par1 varchar(20),par2 varchar(12),par3 varchar(20))
begin
declare m int;
if par3='学生' then
set m=(select count(*) from stu where stuID=par2);
if m=1 then
update bcard
set bcID=par1,name=stu.name,sex=stu.sex,b_num=5,che_time=current_date(),var_per=4,pos=par3,posID=stu.stuID
inner join stu on stuID=par2;
else select '学校无此学生档案' as 警告;
end if;
else if par3='教师' then
set m=(select count(*) from tec where teID=par2);
if m=1 then
update bcard
set bcID=par1,name=tec.name,sex=tec.sex,b_num=10,che_time=current_date(),var_per=20,pos=par3,posID=par2
inner join tec on stuID=par2;
else select '学校无此教师档案' as 警告;
end if;
else
set m=(select count(*) from others where oID=par2);
if m=1 then
update bcard
set bcID=par1,name=others.name,sex=others.sex,b_num=10,che_time=current_date(),var_per=20,pos=par3, posID=par2
inner join others on oID=par2;
else select '学校无此人档案' as 警告;
end if;
end if;end;
//
create procedure create_reader(par1 varchar(20),par2 varchar(12),par3 varchar(20))
begin
declare m int;
if par3='学生' then
set m=(select count(*) from stu where stuID=par2);
if m=1 then
update bcard
set bcID=par1,name=stu.name,sex=stu.sex,b_num=5,che_time=current_date(),var_per=4,pos=par3,posID=stu.stuID
inner join stu on stuID=par2;
else select '学校无此学生档案' as 警告;
end if;
else if par3='教师' then
set m=(select count(*) from tec where teID=par2);
if m=1 then
update bcard
set bcID=par1,name=tec.name,sex=tec.sex,b_num=10,che_time=current_date(),var_per=20,pos=par3,posID=par2
inner join tec on stuID=par2;
else select '学校无此教师档案' as 警告;
end if;
else
set m=(select count(*) from others where oID=par2);
if m=1 then
update bcard
set bcID=par1,name=others.name,sex=others.sex,b_num=10,che_time=current_date(),var_per=20,pos=par3, posID=par2
inner join others on oID=par2;
else select '学校无此人档案' as 警告;
end if;
end if;end;
//
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'
mysql>
mysql> create procedure create_reader(par1 varchar(20),par2 varchar(12),par3 var
char(20))
-> begin
-> declare m int;
-> if par3='学生' then
-> set m=(select count(*) from stu where stuID=par2);
-> if m=1 then
-> update bcard
-> set bcID=par1,name=stu.name,sex=stu.sex,b_num=5,che_time=current_date
(),var_per=4,pos=par3,posID=stu.stuID
-> inner join stu on stuID=par2;
-> else select '学校无此学生档案' as 警告;
-> end if;
-> else if par3='教师' then
-> set m=(select count(*) from tec where teID=par2);
-> if m=1 then
-> update bcard
-> set bcID=par1,name=tec.name,sex=tec.sex,b_num=10,che_time=current_dat
e(),var_per=20,pos=par3,posID=par2
-> inner join tec on stuID=par2;
-> else select '学校无此教师档案' as 警告;
-> end if;
-> else
-> set m=(select count(*) from others where oID=par2);
-> if m=1 then
-> update bcard
-> set bcID=par1,name=others.name,sex=others.sex,b_num=10,che_time=curre
nt_date(),var_per=20,pos=par3, posID=par2
-> inner join others on oID=par2;
-> else select '学校无此人档案' as 警告;
-> end if;
-> end if;
->
-> end;
-> //
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 'inner
join stu on stuID=par2;
else select '学校无此学生档案' as 警告;
end if' at line 9
mysql>MYSQL 中不支持这种update 的语法。改成 update a inner join b on a.id=b.id set a.col=b.col