查了一下资料,要用预处理。 试了一下,通不过,不会用。 use st; DELIMITER $$ CREATE PROCEDURE 2in1() begin declare bm char(8); declare found boolean default true; declare bm_ cursor for select table_name from information_schema.tables where table_schema='st' limit 1,15000; declare continue handler for not found set found=false; open bm_;while found do fetch bm_ into bm; if found=true then begin declare rq_ date; declare found2 boolean default true; #declare rq_bcf cursor for select distinct(rq) from bm; SET @STMT=CONCAT("declare rq_bcf cursor for select distinct(rq) from ",bm,";");
PREPARE STMT FROM @STMT; EXECUTE STMT; declare continue handler for not found set found2=false; open rq_bcf; while found2 do fetch rq_bcf into rq_; end while; close rq_bcf; end; end if; end while; close bm_; end$$ delimiter ;
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1;The second example is similar, but supplies the text of the statement as a user variable: mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2 FROM @s; mysql> SET @a = 6; mysql> SET @b = 8; mysql> EXECUTE stmt2 USING @a, @b; +------------+ | hypotenuse | +------------+ | 10 | +------------+ mysql> DEALLOCATE PREPARE stmt2;
试了一下,通不过,不会用。
use st;
DELIMITER $$
CREATE PROCEDURE 2in1()
begin
declare bm char(8);
declare found boolean default true;
declare bm_ cursor for select table_name from information_schema.tables where table_schema='st' limit 1,15000;
declare continue handler for not found set found=false;
open bm_;while found do
fetch bm_ into bm;
if found=true then
begin
declare rq_ date;
declare found2 boolean default true;
#declare rq_bcf cursor for select distinct(rq) from bm; SET @STMT=CONCAT("declare rq_bcf cursor for select distinct(rq) from ",bm,";");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
declare continue handler for not found set found2=false;
open rq_bcf;
while found2 do
fetch rq_bcf into rq_;
end while;
close rq_bcf;
end;
end if;
end while;
close bm_;
end$$
delimiter ;
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;The second example is similar, but supplies the text of the statement as a user variable: mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;
而且是在一个declare cursor 里面。我头在点大啊。