这是我换了一种方式写的存储过程,但是调用的时候有问题?CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto`(in strsql varchar(200) character set gbk,in icount int,in hfzt varchar(200) character set gbk) BEGIN declare cnt int default 0; declare i int default 0; while i < icount do set @stmt = concat('select id into @c_id from customer where ',strsql,' order by id asc limit ',i,',1'); select @stmt; prepare s1 from @stmt;
execute s1; deallocate prepare s1; set @stmt = NULL; set @num = 0; set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@c_id,')'); select @stmt2; prepare s1 from @stmt2; execute s1; deallocate prepare s1; set @stmt2 = NULL; set i = i + 1; set @c_id = NULL; end while;
select @stmt2;和select @c_id都是空,游标的话我也尝试过,但是好像也不行CREATE DEFINER=`root`@`%` PROCEDURE `AddHFKHJL`(in strsql varchar(200) character set gbk,in hfzt varchar(200) character set gbk) BEGIN declare cnt int default 0; declare i int default 0; declare cId int default 0; declare stop int default 0; DECLARE cur CURSOR FOR select id from customer where strsql order by id asc; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1; open cur; fetch cur into cId; select @cId; while stop <> 1 do
set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@cId,')'); prepare s1 from @stmt2; execute s1; deallocate prepare s1; set @stmt2 = NULL; set i = i + 1; set @c_id = NULL;
fetch cur into cId; end while; close cur; END;下面这里的动态游标有问题,不知道怎么用 DECLARE cur CURSOR FOR select id from customer where strsql order by id asc; strsql 是传入的参数
CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto`(in strsql varchar(200) character set gbk,in icount int,in hfzt varchar(200) character set gbk) BEGIN declare cnt int default 0; declare i int default 0; while i < icount do set @stmt = concat('select id into @c_id from customer where ',strsql,' order by id asc limit ',i,',1'); select 1,@stmt; prepare s1 from @stmt;
execute s1; select 2,@c_id; deallocate prepare s1; set @stmt = NULL; set @num = 0; set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@c_id,')'); select 3,@stmt2; select 4,@c_id; prepare s1 from @stmt2; execute s1; deallocate prepare s1; set @stmt2 = NULL; set i = i + 1; set @c_id = NULL; end while;
1、 select id into @c_id from customer where c_sex='男' order by id asc limit 0,1 2、 NUll 3、 null 4、 null
select 1,@stmt; 结果:select id into @c_id from customer where c_sex='男' order by id asc limit 0,1 select 2,@c_id; 结果:NUll select 3,@stmt2; 结果:null select 4,@c_id; 结果:null
select @stmt; 直接执行@stmt select id into @c_id from customer where c_sex='男' order by id asc limit 0,1; select @c_id;c_id为1 结果是对的
这样吧,贴出你的 create table , insert into 等语句,这样别人可以直接在自己的机器上创建和你一样的环境进行调试。
declare stmt varchar(4000);
set @v_sql = 传入的select语句;
prepare stmt from @v_sql;
execute stmt;不知道mysql能否支持动态游标。如果不能,则另写一存储过程来处理这个查询结果吧。
你说的“另写一存储过程来处理这个查询结果吧”是怎么个处理法??
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
BEGIN
declare cnt int default 0;
declare i int default 0;
while i < icount
do
set @stmt = concat('select id into @c_id from customer where ',strsql,' order by id asc limit ',i,',1');
select @stmt;
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
set @num = 0;
set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@c_id,')');
select @stmt2;
prepare s1 from @stmt2;
execute s1;
deallocate prepare s1;
set @stmt2 = NULL;
set i = i + 1;
set @c_id = NULL;
end while;
END;
调用
call sp_generate_auto('c_sex="男"',10,'jjjh');stmt语句中的@c_id变量一直都是空,会导致下面的stmt2中插入语句出错,我是不知道怎么获取stmt中的@c_id变量用于下面保存
select id into @c_id from customer where c_sex='男' order by id asc limit 0,1
BEGIN
declare cnt int default 0;
declare i int default 0;
declare cId int default 0;
declare stop int default 0;
DECLARE cur CURSOR FOR select id from customer where strsql order by id asc;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into cId;
select @cId;
while stop <> 1 do
set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@cId,')');
prepare s1 from @stmt2;
execute s1;
deallocate prepare s1;
set @stmt2 = NULL;
set i = i + 1;
set @c_id = NULL;
fetch cur into cId;
end while;
close cur; END;下面这里的动态游标有问题,不知道怎么用
DECLARE cur CURSOR FOR select id from customer where strsql order by id asc;
strsql 是传入的参数
BEGIN
declare cnt int default 0;
declare i int default 0;
while i < icount
do
set @stmt = concat('select id into @c_id from customer where ',strsql,' order by id asc limit ',i,',1');
select 1,@stmt;
prepare s1 from @stmt;
execute s1;
select 2,@c_id;
deallocate prepare s1;
set @stmt = NULL;
set @num = 0;
set @stmt2 = concat('insert into hfztjl (hfzt,hfkhID) values ("',hfzt,'",',@c_id,')');
select 3,@stmt2;
select 4,@c_id;
prepare s1 from @stmt2;
execute s1;
deallocate prepare s1;
set @stmt2 = NULL;
set i = i + 1;
set @c_id = NULL;
end while;
END;贴一下结果
要插入的表结构:
CREATE TABLE `hfztjl` (
`Id` int(11) NOT NULL auto_increment,
`hfzt` varchar(255) NOT NULL default '' COMMENT '回访主题',
`hfsj` datetime default NULL COMMENT '回访时间',
`hfkhID` varchar(255) NOT NULL default '' COMMENT '回访客户ID',
`hfbz` bit(1) default NULL COMMENT '回访标志,用来标志是否已经回访给客户',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select id into @c_id from customer where c_sex='男' order by id asc limit 0,1
2、
NUll
3、
null
4、
null
结果:select id into @c_id from customer where c_sex='男' order by id asc limit 0,1
select 2,@c_id;
结果:NUll
select 3,@stmt2;
结果:null
select 4,@c_id;
结果:null
select id into @c_id from customer where c_sex='男' order by id asc limit 0,1;
select @c_id;c_id为1
结果是对的
CREATE TABLE `customer` (
`Id` int(11) NOT NULL auto_increment,
`c_typeid` int(11) default NULL,
`c_Id` varchar(225) default NULL,
`c_name` varchar(225) default NULL,
`c_sex` varchar(255) default NULL,
`c_IDNum` varchar(255) default NULL,
`c_birthday` varchar(255) default NULL,
`c_birthplace` varchar(255) default NULL,
`c_mobile1` varchar(255) default NULL,
`c_guishudi1` varchar(255) default NULL,
`c_cardtype1` varchar(255) default NULL,
`c_email` varchar(255) default NULL,
`c_mobile2` varchar(255) default NULL,
`c_guishudi2` varchar(255) default NULL,
`c_cardtype2` varchar(255) default NULL,
`c_qq` varchar(255) default NULL,
`c_duties` varchar(255) default NULL,
`c_company` varchar(255) default NULL,
`c_companyaddress` varchar(255) default NULL,
`c_companytel` varchar(255) default NULL,
`c_portraiture` varchar(255) default NULL,
`c_creditid` int(11) default NULL,
`c_companyweb` varchar(255) default NULL,
`c_highschoolid` varchar(255) default NULL,
`c_graduate` varchar(255) default NULL,
`c_major` varchar(255) default NULL,
`c_interest` varchar(255) default NULL,
`c_homephone` varchar(255) default NULL,
`c_homeaddress` varchar(255) default NULL,
`c_postcode` varchar(255) default NULL,
`c_receipts` varchar(255) default NULL,
`c_res` varchar(255) default NULL,
`c_category` int(11) default NULL,
`c_contactnumber` varchar(255) default NULL,
`c_cluesuorce` varchar(255) default NULL,
`c_integral` varchar(255) default NULL,
`c_area` varchar(255) default NULL,
`c_civilstate` varchar(255) default NULL,
`c_childstate` varchar(255) default NULL,
`c_parents` varchar(255) default NULL,
`c_coinkind` varchar(255) default NULL,
`c_memberlv` int(4) default NULL,
`c_msn` varchar(255) default NULL,
`c_abbreviation` varchar(255) default NULL,
`c_companypost` varchar(255) default NULL,
`c_belongmember` varchar(255) default NULL,
`c_busyreg` varchar(255) default NULL,
`c_orgstrutcode` varchar(255) default NULL,
`c_legalperson` varchar(255) default NULL,
`c_legalpersontype` varchar(50) default NULL,
`c_flag` varchar(255) default NULL,
`c_legalpersoncode` varchar(255) default NULL,
`c_credentialsissue` varchar(255) default NULL,
`c_taxpayercode` varchar(255) default NULL,
`c_establishingdate` varchar(255) default NULL,
`c_operatingperiod` varchar(255) default NULL,
`c_biztype` varchar(255) default NULL,
`c_busyregaddress` varchar(255) default NULL,
`c_busyscope` varchar(255) default NULL,
`c_isrhpj` varchar(255) default NULL,
`c_islogout` varchar(255) default NULL,
`c_country` varchar(50) default NULL,
`c_province` varchar(50) default NULL,
`c_city` varchar(50) default NULL,
`c_builddate` varchar(50) default NULL,
`isdel` int(4) default NULL,
`cy_power` int(4) default NULL,
`c_maintrade` varchar(50) default NULL,
`c_childtrade` varchar(50) default NULL,
`c_totalintegral` varchar(50) default NULL,
`c_usableintegral` varchar(50) default NULL,
`c_usedableintegral` varchar(50) default NULL,
`c_regcapital` varchar(50) default NULL,
`c_regcionkind` varchar(50) default NULL,
`c_reccapital` varchar(50) default NULL,
`c_reccionkind` varchar(50) default NULL,
`c_retdate` varchar(50) default NULL,
`c_operatingperiodend` varchar(50) default NULL,
`c_offices` varchar(50) default NULL,
`allCategoryName` varchar(200) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;表2
CREATE TABLE `hfztjl` (
`Id` int(11) NOT NULL auto_increment,
`hfzt` varchar(255) NOT NULL default '' COMMENT '回访主题',
`hfsj` datetime default NULL COMMENT '回访时间',
`hfkhID` varchar(255) NOT NULL default '' COMMENT '回访客户ID',
`hfbz` bit(1) default NULL COMMENT '回访标志,用来标志是否已经回访给客户',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into customer(c_Id,c_name,c_sex) values('23456423','afaad','男');
insert into customer(c_Id,c_name,c_sex) values('23424524','afasjd','男');