create procedure sp_createtemp2(p_titleid varchar(40),p_islogicid varchar(40))
begin
DECLARE no_more int(1) default 0;
DECLARE v_surid varchar(40);
DECLARE v_titid varchar(40);
DECLARE v_claID varchar(40);
DECLARE v_surans text;
DECLARE v_suropi text;
DECLARE v_surtyp int(2);
DECLARE v_cretim varchar(20);
DECLARE v_editim varchar(20);
DECLARE v_surlev int(11);
DECLARE v_surord double;
DECLARE v_suropilev int(11);
DECLARE v_surpag int(10);
DECLARE v_surkey varchar(200);
DECLARE v_surwei int(20);
DECLARE v_suriso int(1);
DECLARE v_surisl int(1);
DECLARE cur1 cursor for selecet * from survey_list where title_id=p_titleid order by survey_order asc;
DECLARE continue handler for not found set no_more=1;
set no_more=0;
/*create temporary table*/
create temporary table if not exists tbl_temp7(
survey_id varchar(40) NOT NULL,
title_id varchar(40) DEFAULT NULL,
class_id varchar(40) DEFAULT NULL,
survey_answer text,
survey_opion text,
survey_type int(2) DEFAULT NULL,
creat_time varchar(20) DEFAULT NULL,
editor_time varchar(20) DEFAULT NULL,
survey_lever int(11) DEFAULT NULL,
survey_order double DEFAULT NULL,
survey_opion_leve int(11) DEFAULT NULL,
survey_page int(10) DEFAULT NULL,
survey_keyword varchar(200) DEFAULT NULL,
survey_weishu int(20) DEFAULT NULL,
survey_isogic int(1) DEFAULT 0,
survey_islb int(1) DEFAULT NULL
);
open cur1;
repeat
fetch cur1 into v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl;
if v_claID is not null and v_claID<>'' then
if p_islogicid=v_claID then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
if end;
else
if v_suriso=0 then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
else
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
set no_more=1;
if end;
if end;
UNTIL no_more END REPEAT;
CLOSE cur1;
select * from tbl_temp7;
truncate tbl_temp7;
end//
tbl_temp7和survey_list表结果相同
DECLARE cur1 cursor for selecet * from survey_list where title_id=p_titleid order by survey_order asc;这一行报错为啥?
begin
DECLARE no_more int(1) default 0;
DECLARE v_surid varchar(40);
DECLARE v_titid varchar(40);
DECLARE v_claID varchar(40);
DECLARE v_surans text;
DECLARE v_suropi text;
DECLARE v_surtyp int(2);
DECLARE v_cretim varchar(20);
DECLARE v_editim varchar(20);
DECLARE v_surlev int(11);
DECLARE v_surord double;
DECLARE v_suropilev int(11);
DECLARE v_surpag int(10);
DECLARE v_surkey varchar(200);
DECLARE v_surwei int(20);
DECLARE v_suriso int(1);
DECLARE v_surisl int(1);
DECLARE cur1 cursor for selecet * from survey_list where title_id=p_titleid order by survey_order asc;
DECLARE continue handler for not found set no_more=1;
set no_more=0;
/*create temporary table*/
create temporary table if not exists tbl_temp7(
survey_id varchar(40) NOT NULL,
title_id varchar(40) DEFAULT NULL,
class_id varchar(40) DEFAULT NULL,
survey_answer text,
survey_opion text,
survey_type int(2) DEFAULT NULL,
creat_time varchar(20) DEFAULT NULL,
editor_time varchar(20) DEFAULT NULL,
survey_lever int(11) DEFAULT NULL,
survey_order double DEFAULT NULL,
survey_opion_leve int(11) DEFAULT NULL,
survey_page int(10) DEFAULT NULL,
survey_keyword varchar(200) DEFAULT NULL,
survey_weishu int(20) DEFAULT NULL,
survey_isogic int(1) DEFAULT 0,
survey_islb int(1) DEFAULT NULL
);
open cur1;
repeat
fetch cur1 into v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl;
if v_claID is not null and v_claID<>'' then
if p_islogicid=v_claID then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
if end;
else
if v_suriso=0 then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
else
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
set no_more=1;
if end;
if end;
UNTIL no_more END REPEAT;
CLOSE cur1;
select * from tbl_temp7;
truncate tbl_temp7;
end//
tbl_temp7和survey_list表结果相同
DECLARE cur1 cursor for selecet * from survey_list where title_id=p_titleid order by survey_order asc;这一行报错为啥?
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 'selec
et * from survey_list where title_id=p_titleid order by survey_order asc;
D' at line 20
mysql>另外
if end; -> end ifcreate procedure sp_createtemp2(p_titleid varchar(40),p_islogicid varchar(40))
begin
DECLARE no_more int(1) default 0;
DECLARE v_surid varchar(40);
DECLARE v_titid varchar(40);
DECLARE v_claID varchar(40);
DECLARE v_surans text;
DECLARE v_suropi text;
DECLARE v_surtyp int(2);
DECLARE v_cretim varchar(20);
DECLARE v_editim varchar(20);
DECLARE v_surlev int(11);
DECLARE v_surord double;
DECLARE v_suropilev int(11);
DECLARE v_surpag int(10);
DECLARE v_surkey varchar(200);
DECLARE v_surwei int(20);
DECLARE v_suriso int(1);
DECLARE v_surisl int(1);
DECLARE cur1 cursor for select * from survey_list where title_id=p_titleid order by survey_order asc;
DECLARE continue handler for not found set no_more=1;
set no_more=0;
/*create temporary table*/
create temporary table if not exists tbl_temp7(
survey_id varchar(40) NOT NULL,
title_id varchar(40) DEFAULT NULL,
class_id varchar(40) DEFAULT NULL,
survey_answer text,
survey_opion text,
survey_type int(2) DEFAULT NULL,
creat_time varchar(20) DEFAULT NULL,
editor_time varchar(20) DEFAULT NULL,
survey_lever int(11) DEFAULT NULL,
survey_order double DEFAULT NULL,
survey_opion_leve int(11) DEFAULT NULL,
survey_page int(10) DEFAULT NULL,
survey_keyword varchar(200) DEFAULT NULL,
survey_weishu int(20) DEFAULT NULL,
survey_isogic int(1) DEFAULT 0,
survey_islb int(1) DEFAULT NULL
);
open cur1;
repeat
fetch cur1 into v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl;
if v_claID is not null and v_claID <>'' then
if p_islogicid=v_claID then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
end if;
else
if v_suriso=0 then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
else
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
set no_more=1;
end if;
end if;
UNTIL no_more END REPEAT;
CLOSE cur1;
select * from tbl_temp7;
truncate tbl_temp7;
end//
create procedure sp_createtemp2(p_titleid varchar(40),p_islogicid varchar(40))
begin
DECLARE no_more int(1) default 0;
DECLARE v_surid varchar(40);
DECLARE v_titid varchar(40);
DECLARE v_claID varchar(40);
DECLARE v_surans text;
DECLARE v_suropi text;
DECLARE v_surtyp int(2);
DECLARE v_cretim varchar(20);
DECLARE v_editim varchar(20);
DECLARE v_surlev int(11);
DECLARE v_surord double;
DECLARE v_suropilev int(11);
DECLARE v_surpag int(10);
DECLARE v_surkey varchar(200);
DECLARE v_surwei int(20);
DECLARE v_suriso int(1);
DECLARE v_surisl int(1);
DECLARE cur1 cursor for SELECT * from survey_list where title_id=p_titleid order by survey_order asc;
DECLARE continue handler for not found set no_more=1;
set no_more=0;
/*create temporary table*/
create temporary table if not exists tbl_temp7(
survey_id varchar(40) NOT NULL,
title_id varchar(40) DEFAULT NULL,
class_id varchar(40) DEFAULT NULL,
survey_answer text,
survey_opion text,
survey_type int(2) DEFAULT NULL,
creat_time varchar(20) DEFAULT NULL,
editor_time varchar(20) DEFAULT NULL,
survey_lever int(11) DEFAULT NULL,
survey_order double DEFAULT NULL,
survey_opion_leve int(11) DEFAULT NULL,
survey_page int(10) DEFAULT NULL,
survey_keyword varchar(200) DEFAULT NULL,
survey_weishu int(20) DEFAULT NULL,
survey_isogic int(1) DEFAULT 0,
survey_islb int(1) DEFAULT NULL
);
open cur1;
repeat
fetch cur1 into v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl;
if v_claID is not null and v_claID <>'' then
if p_islogicid=v_claID then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
END IF;
else
if v_suriso=0 then
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
else
insert into tbl_temp7 values(v_surid,v_titid,v_claID,v_surans,v_suropi,v_surtyp,v_cretim,v_editim,v_surlev,v_surord,v_suropilev,v_surpag,v_surkey,v_surwei,v_suriso,v_surisl);
set no_more=1;
END IF;
END IF;
UNTIL no_more END REPEAT;
CLOSE cur1;
select * from tbl_temp7;
truncate tbl_temp7;
end