TheFan1968

MariaDB: Procedure using dynamic outfile name

Jan 19th, 2017
311
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.96 KB | None | 0 0
  1. CREATE DEFINER=`wendelp`@`%` PROCEDURE `test`()
  2.     LANGUAGE SQL
  3.     NOT DETERMINISTIC
  4.     CONTAINS SQL
  5.     SQL SECURITY DEFINER
  6.     COMMENT ''
  7. BEGIN
  8. declare var_filename,var_titel, var_endung,var_dok varchar(100);
  9. declare var_dokument longtext;
  10. declare var_done int default false;
  11.  
  12. declare export_cur cursor for
  13. select distinct
  14.     doc.Titel, doc.Dokument, doc.Dateiendung, vov.Key_DOK
  15.     from VOV_MED vov
  16.     join DOK_MED doc
  17.         on doc.Key_DOK = vov.Key_DOK
  18.     where vov.Typ in (1,7,9,11,12,13,14,15,16,17,18,19)
  19.         and vov.Key_DOK > 0;
  20.  
  21. declare continue handler for not found set var_done= true;
  22.  
  23. open export_cur;
  24. read_loop:loop
  25.     fetch export_cur into var_titel,var_dokument,var_endung,var_dok;
  26.         if var_done then
  27.             leave read_loop;
  28.         end if;
  29.        
  30.         set @sql = concat('select ''', var_dokument, ''' into outfile ''/opt/abdamed/', replace(var_titel,'/','_'),'_',var_dok,'.txt','''' );
  31.         prepare psql from @sql;
  32.  
  33.         execute psql;
  34.        
  35.        
  36. end loop;
  37. close export_cur;
  38.  
  39.  
  40.  
  41. END
Advertisement
Add Comment
Please, Sign In to add comment