mysql存储过程实例

 

create or replace p_test()

BEGIN
DECLARE  v_name varchar(100) CHARACTER SET gbk;
DECLARE  v_zhiguan varchar(100) CHARACTER SET gbk;
DECLARE  v_time1 varchar(100) CHARACTER SET gbk;
DECLARE  v_time2 varchar(100) CHARACTER SET gbk;
DECLARE  v_month varchar(100) CHARACTER SET gbk;
DECLARE  v_road varchar(100) CHARACTER SET gbk;
DECLARE  v_address varchar(100) CHARACTER SET gbk;
DECLARE  v_nowaddress varchar(100) CHARACTER SET gbk;
DECLARE  v_source varchar(100) CHARACTER SET gbk;
DECLARE  v_demo varchar(100) CHARACTER SET gbk;
DECLARE  v_type varchar(100) CHARACTER SET gbk;

 

declare b int default 0;
DECLARE  test_cursor cursor for select DISTINCT  name,zhiguan  from table001;
DECLARE  test_cursor2 cursor for select  name,zhiguan,time1,time2,month1,road,address,nowaddress,source,demo,type1  from table001 where name =v_name and zhiguan=v_zhiguan;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

open test_cursor;
fetch test_cursor into v_name,v_zhiguan;

 while b<>1 do
open test_cursor2;
fetch test_cursor2 into v_name,v_zhiguan,v_time1,v_time2,v_month,v_road,v_address,v_nowaddress,v_source,v_demo,v_type;
close test_cursor2;
insert into table001_copy(name,zhiguan,time1,time2,month1,road,address,nowaddress,source,demo,type1)
values(v_name,v_zhiguan,v_time1,v_time2,v_month,v_road,v_address,v_nowaddress,v_source,v_demo,v_type);

fetch test_cursor into v_name,v_zhiguan;
end while;
close test_cursor;

 

END