函数 存储过程动态sql-Davidsolomon的博客
delimiter $$CREATE PROCEDURE `test_pro`(as_tablename varchar(40), as_tableschema varchar(40))
SQL SECURITY INVOKER
BEGIN
DECLARE fetchOK int default 1;
DECLARE ls_colname varchar(40);
DECLARE ls_return varchar(1000) default '';
DECLARE cur_code cursor for select column_name from information_schema.columns where table_name=as_tablename and table_schema=as_tableschema;
DECLARE continue handler for not found
BEGIN
set fetchOK=0;
commit;
END;
open cur_code;
allcodes:loop
fetch cur_code into ls_colname;
if fetchOK then
set ls_return=concat(ls_return, 'new.', ls_colname, ',');
else
leave allcodes;
end if;
end loop allcodes;
close cur_code;
set ls_return=concat('insert into ', as_tableschema, ',', as_tablename, ' values(', substr(ls_return,1,length(ls_return)-1), ')');
select ls_return as sqlcmd;
END;
$$
delimiter ;
页:
[1]