mysql 存储过程 触发器（3） 游标的使用

存储过程中如何返回数组 。

使用内存临时表实现 一例：

CREATE DEFINER = 'root'@'%' PROCEDURE `pro_delete_NodeDefinition`(in ID Varchar(128))
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
   declare flag  boolean default false;
   Declare  cur_NodeID  Varchar(128);
   declare   done INT DEFAULT 0; 
  declare   sqlres text;
  call recursive_select('node_definition','pkid','super_id',ID,null,'pkid',sqlres);
  set @sql=CONCAT(' insert into coolink_definition_tab  select pkid from node_definition where pkid IN(',sqlres,')');
  drop table  if exists coolink_definition_tab;
  CREATE TEMPORARY TABLE coolink_definition_tab(pkid varchar(256))  TYPE = HEAP;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  begin
        declare  cur   Cursor  for  Select n.pkid From coolink_definition_tab n ;
        declare CONTINUE handler for not found set flag =true;
        Open cur;
        While not flag  do
        Fetch cur Into cur_NodeID;
        delete from node_definition_authorization  where definition_id=cur_NodeID;
        delete from nodedef_processdef  where node_definition =cur_NodeID;
        delete from node_view  where nodefid=cur_NodeID;
        update NODE_DEFINITION set DELETE_MARK='1' , PKID=CONCAT(PKID,now())  where PKID=cur_NodeID;
        End while;
        Close cur;
        DROP TABLE IF EXISTS coolink_definition_tab;
  end;
 commit;
 END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11813230/viewspace-621582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11813230/viewspace-621582/