declare
cursor aa is
SELECT * FROM item_template WHERE name LIKE '%无形%' LIMIT 0, 1000;
begin
for aaa in aa loop
INSERT INTO creature_loot_template
(entry,
item,
ChanceOrQuestChance,
lootmode,
groupid,
mincountOrRef,
maxcount,
lootcondition,
condition_value1,
condition_value2)
VALUES
('15550',aaa.item , '1', '1', '0', '1', '1', '0', '0', '0');
end loop;
end;
cursor aa is
SELECT * FROM item_template WHERE name LIKE '%无形%' LIMIT 0, 1000;
begin
for aaa in aa loop
INSERT INTO creature_loot_template
(entry,
item,
ChanceOrQuestChance,
lootmode,
groupid,
mincountOrRef,
maxcount,
lootcondition,
condition_value1,
condition_value2)
VALUES
('15550',aaa.item , '1', '1', '0', '1', '1', '0', '0', '0');
end loop;
end;
VALUES
('15550',aaa.item , '1', '1', '0', '1', '1', '0', '0', '0');
------也没用找出来的结果啊?
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
CLOSE cur2;
END
INSERT INTO creature_loot_template
(entry,
item,
ChanceOrQuestChance,
lootmode,
groupid,
mincountOrRef,
maxcount,
lootcondition,
condition_value1,
condition_value2)
VALUES
('15550',aaa.item , '1', '1', '0', '1', '1', '0', '0', '0');就是这样红字的字段。谢谢啦
表:学生表student,和课程表course,选课表 choose ;create table student(id int,sno int);
insert into student values (1,1),(2,2),(3,3),(4,4),(5,5);create table course(id int,name varchar(10))
insert into course values (1,'语文'),(2,'数学'),(3,'英语'),(4,'社会'); create table choose (id int primary key auto_increment,student_sno int,course_name varchar(10));实现的效果是:选课表里面记录:每个学生都选择课程表里面的全部课程.DROP PROCEDURE IF EXISTS sp_choose;
delimiter //
create procedure sp_choose()
begin
declare a int;
declare b varchar(10) character set utf8;
declare done1,done2 int default 0;
declare cur_1 cursor for select name from course;
declare continue handler for not found set done1=1;
open cur_1;
repeat
fetch cur_1 into b;
if not done1 then
begin
declare cur_2 cursor for select sno from student;
declare continue handler for not found set done2=1;
open cur_2;
repeat
fetch cur_2 into a;
if not done2 then
insert into choose(student_sno,course_name) values (a,b);
end if;
until done2 end repeat;
close cur_2;
set done2=0;
end;
end if;
until done1 end repeat;
close cur_1;
end;//delimiter ;----------------------choose表显示:mysql> select * from choose;
+----+-------------+-------------+
| id | student_sno | course_name |
+----+-------------+-------------+
| 1 | 1 | 语文 |
| 2 | 2 | 语文 |
| 3 | 3 | 语文 |
| 4 | 4 | 语文 |
| 5 | 5 | 语文 |
| 6 | 1 | 数学 |
| 7 | 2 | 数学 |
| 8 | 3 | 数学 |
| 9 | 4 | 数学 |
| 10 | 5 | 数学 |
| 11 | 1 | 英语 |
| 12 | 2 | 英语 |
| 13 | 3 | 英语 |
| 14 | 4 | 英语 |
| 15 | 5 | 英语 |
| 16 | 1 | 社会 |
| 17 | 2 | 社会 |
| 18 | 3 | 社会 |
| 19 | 4 | 社会 |
| 20 | 5 | 社会 |+----+-------------+-------------+
item,
ChanceOrQuestChance,
lootmode,
groupid,
mincountOrRef,
maxcount,
lootcondition,
condition_value1,
condition_value2)
select '15550',item , '1', '1', '0', '1', '1', '0', '0', '0' FROM item_template WHERE name LIKE '%无形%' LIMIT 0, 1000;