接上一张,表名tablename是传入存储过程的变量,如何建立游标呢
Mysql
DECLARE cur CURSOR FOR SELECT id FROM v_proc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @select = concat('create VIEW v_proc as SELECT * FROM ', tablename);
PREPARE stm FROM @select;
EXECUTE stm;
DEALLOCATE PREPARE stm;
--下面就跟正常游标一样使用了
Sqlserver
DECLARE @StrSql varchar(255);
set @StrSql= N'DECLARE mycursor1 CURSOR FOR (SELECT val from '+@tablename+')';
exec(@StrSql)
Oracle
--oracle比较复杂,因为变量需要在IS/AS下面就声明,但是声明变量的时候又不能拼接字符串所以我们先这锚定声明
AS
v_sql varchar2(512);
type refcur_type is ref cursor;
cur refcur_type;
begin
v_sql:= 'select avg(val) from '||tablename;
open cur for v_sql;
loop
fetch .........
--后面就跟正常游标一样