MySQL Forums
Forum List  »  Stored Procedures

Re: Mysql 8 stored procedure
Posted by: Peter Brawley
Date: June 15, 2020 12:46PM

This script, with your error delimiters cleaned up, runs without error in 5.6 and 8.0 (though in 8.0 it elicits a warning about utf8 being a alias for utf8mb3 and will soon mean utf8mb4):

create schema if not exists zozo0615;
use zozo0615;
drop procedure if exists teszt;
delimiter go
CREATE PROCEDURE `teszt`()

BEGIN

DECLARE done INT default False;
DECLARE mField CHAR(20) default '';
DECLARE mFields TEXT; 

/* 
  This next tempcur declaration did work under 5.6 and sometimes work on 8.0, but sometimes give error 
  because fields name list arrive in alphabetical order. 
	But sometimes it improves without any program change under 8.0 .. and it work further all the time.
  DECLARE tempcur CURSOR FOR 
  SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = 'zozo0615' AND TABLE_NAME ='bizonylat_torzs_2031';

 This new tempcur declaration always work under 5.6 and under 8.0 also because of it contains  
 ORDER BY ORDINAL_POSITION  keywords
 */
DECLARE tempcur CURSOR FOR 
  SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = 'zozo0615' AND TABLE_NAME ='bizonylat_torzs_2031'
  ORDER BY ORDINAL_POSITION ;

/* variables create test table...etc*/
DECLARE CONTINUE HANDLER FOR NOT FOUND 
  SET done = TRUE;
SET mFields='';
SET mField='';
SET @lcCommand ='REPLACE INTO zozo0615.bizonylat_torzs_2031 (SELECT ';

DROP TABLE IF EXISTS zozo0615.bizonylat_torzs_2031;

CREATE TABLE zozo0615.bizonylat_torzs_2031 (
  PAZON bigint NOT NULL AUTO_INCREMENT,
  ADOSZAZON int NOT NULL DEFAULT '0',
  BANKAZON int NOT NULL DEFAULT '0',
  BOLTAZON int NOT NULL DEFAULT '0',
  TOMBPAZON int NOT NULL DEFAULT '0',
  USERDAZON int NOT NULL DEFAULT '0',
  USERDPAZON int NOT NULL DEFAULT '0',
  IRANY decimal(1,0) NOT NULL DEFAULT '0',
  BIZTIPUS int NOT NULL DEFAULT '0',
  PLATFORM decimal(2,0) NOT NULL DEFAULT '0',
  LOGOAZON int NOT NULL DEFAULT '0',
  EVSZAM decimal(4,0) NOT NULL DEFAULT '0',
  BIZSZAM decimal(12,0) NOT NULL DEFAULT '0',
  BIZTIPUSP char(4) NOT NULL DEFAULT '',
  TOMBJEL char(7) NOT NULL DEFAULT '',
  BIZFEJNEV varchar(50) NOT NULL DEFAULT '',
  REPORTFAJL varchar(30) NOT NULL DEFAULT '',
  INFORMACIO varchar(30) NOT NULL DEFAULT '',
  KIBDT date NOT NULL DEFAULT '1900-01-01',
  TELJDT date NOT NULL DEFAULT '1900-01-01',
  FIZHATDT date NOT NULL DEFAULT '1900-01-01',
  IGAZOLDT date NOT NULL DEFAULT '1900-01-01',
  ERVENYDT date NOT NULL DEFAULT '1900-01-01',
  FIZDT date NOT NULL DEFAULT '1900-01-01',
  EGYSZERU decimal(1,0) NOT NULL DEFAULT '0',
  ELOSZAMLA decimal(1,0) NOT NULL DEFAULT '0',
  VEGSZAMLA decimal(1,0) NOT NULL DEFAULT '0',
  AFAS decimal(1,0) NOT NULL DEFAULT '0',
  KATA decimal(1,0) NOT NULL DEFAULT '0',
  PFEL decimal(1,0) NOT NULL DEFAULT '0',
  MAGYAREURO decimal(1,0) NOT NULL DEFAULT '0',
  EXPORT decimal(1,0) NOT NULL DEFAULT '0',
  EUNBELULI decimal(1,0) NOT NULL DEFAULT '0',
  FOA decimal(1,0) NOT NULL DEFAULT '0',
  PLDOFF decimal(1,0) NOT NULL DEFAULT '0',
  PELDANY int NOT NULL DEFAULT '0',
  STORNO decimal(1,0) NOT NULL DEFAULT '0',
  STORNOZOTT decimal(1,0) NOT NULL DEFAULT '0',
  JOVAIRO decimal(1,0) NOT NULL DEFAULT '0',
  JOVAIRT decimal(1,0) NOT NULL DEFAULT '0',
  NYELV decimal(2,0) NOT NULL DEFAULT '0',
  PENZNEM char(3) NOT NULL DEFAULT '',
  ARFOLYAM decimal(15,4) NOT NULL DEFAULT '0.0000',
  FIZMOD char(15) NOT NULL DEFAULT '',
  FIZMODAZ int NOT NULL DEFAULT '0',
  ARSOR char(15) NOT NULL DEFAULT '',
  ARSORAZ int NOT NULL DEFAULT '0',
  FIZETVE decimal(1,0) NOT NULL DEFAULT '0',
  FIZOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  SZOVEGAZON int NOT NULL DEFAULT '0',
  KESZAZON int NOT NULL DEFAULT '0',
  KASSZAUSER int NOT NULL DEFAULT '0',
  AFAOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  FAFAOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  NETTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  FNETTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  BRUTTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  FBRUTTOOSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
  ELOBIZTIP int NOT NULL DEFAULT '0',
  ELOBIZAZON bigint NOT NULL DEFAULT '0',
  ELOBIZSZAM char(25) NOT NULL DEFAULT '',
  ELOBIZEVSZ decimal(4,0) NOT NULL DEFAULT '0',
  KOVBIZTIP int NOT NULL DEFAULT '0',
  KOVBIZAZON bigint NOT NULL DEFAULT '0',
  KOVBIZSZAM char(25) NOT NULL DEFAULT '',
  KOVBIZEVSZ decimal(4,0) NOT NULL DEFAULT '0',
  SMEGJEGYZ longtext ,
  HIVATKOZAS longtext ,
  ARTIZEDES decimal(1,0) NOT NULL DEFAULT '0',
  METIZEDES decimal(1,0) NOT NULL DEFAULT '0',
  ELOLEGN decimal(15,4) NOT NULL DEFAULT '0.0000',
  ELOLEGB decimal(15,4) NOT NULL DEFAULT '0.0000',
  PARTPAZON int NOT NULL DEFAULT '0',
  PNEV varchar(100) NOT NULL DEFAULT '',
  PADOSZAM varchar(17) NOT NULL DEFAULT '',
  PADOCSOP varchar(13) NOT NULL DEFAULT '',
  PEMAIL varchar(50) NOT NULL DEFAULT '',
  PNAVFORMAT decimal(1,0) NOT NULL DEFAULT '0',
  TAGSAGAZON int NOT NULL DEFAULT '0',
  TORSZAG char(3) NOT NULL DEFAULT '',
  TKORZET varchar(20) NOT NULL DEFAULT '',
  TIRSZ varchar(8) NOT NULL DEFAULT '',
  THELYSEG varchar(20) NOT NULL DEFAULT '',
  TCIM varchar(80) NOT NULL DEFAULT '',
  TKOZNEVE varchar(25) NOT NULL DEFAULT '',
  TKOZJELLEG varchar(10) NOT NULL DEFAULT '',
  TKOZSZAM varchar(10) NOT NULL DEFAULT '',
  TEPULET varchar(5) NOT NULL DEFAULT '',
  TLEPCSOHAZ varchar(5) NOT NULL DEFAULT '',
  TEMELET char(3) NOT NULL DEFAULT '',
  TAJTO char(3) NOT NULL DEFAULT '',
  LNEV varchar(100) NOT NULL DEFAULT '',
  LORSZAG char(3) NOT NULL DEFAULT '',
  LKORZET varchar(20) NOT NULL DEFAULT '',
  LIRSZ varchar(8) NOT NULL DEFAULT '',
  LHELYSEG varchar(20) NOT NULL DEFAULT '',
  LCIM varchar(80) NOT NULL DEFAULT '',
  OBIZTIP int NOT NULL DEFAULT '0',
  OBIZFAJTA int NOT NULL DEFAULT '0',
  OSZABSZOV int NOT NULL DEFAULT '0',
  OAFATIP int NOT NULL DEFAULT '0',
  ONETTO int NOT NULL DEFAULT '0',
  ESZAMEMAIL datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  ESZAMPDF datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  ESZAMCOPY datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  _SERVDATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  LINEEND int NOT NULL DEFAULT '0',
  MODBIZSZAM char(25) NOT NULL DEFAULT '',
  MODBIZELOZ char(25) NOT NULL DEFAULT '',
  MODOSITO decimal(1,0) NOT NULL DEFAULT '0',
  NAVJELENT decimal(1,0) NOT NULL DEFAULT '0',
  NAVAFA decimal(15,4) NOT NULL DEFAULT '0.0000',
  NAVID varchar(30) NOT NULL DEFAULT '',
  NAVMODSZ int NOT NULL DEFAULT '0',
  PRIMARY KEY (PAZON),
  KEY BIZTIPUS (BIZTIPUS),
  KEY TOMBPAZON (TOMBPAZON),
  KEY BIZSZAM (BIZSZAM),
  KEY PNEV (PNEV),
  KEY FIZDT (FIZDT),
  KEY ERVENYDT (ERVENYDT),
  KEY IGAZOLDT (IGAZOLDT),
  KEY FIZHATDT (FIZHATDT),
  KEY TELJDT (TELJDT),
  KEY KIBDT (KIBDT),
  KEY BOLTAZON (BOLTAZON)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8 ;

/*insert first row for test*/
REPLACE INTO  zozo0615.bizonylat_torzs_2031  (SMEGJEGYZ,HIVATKOZAS) VALUES ('','');

/* Start REPLACE into command generation */
SET done = False;
OPEN tempcur;
read_loop: LOOP
  FETCH tempcur INTO mField;
  IF done THEN
    LEAVE read_loop;
  END IF;
  IF (mFields!='') THEN
    SET mFields = CONCAT(mFields,',') ;    
  END IF;
  /* SET mFields = CONCAT(mFields,mField);        */
  SET mFields = CONCAT(mFields,IF((mField <> "PAZON"),mField,"0 as pazon"));        
END LOOP;
CLOSE tempcur;

SET @lcCommand = CONCAT(@lcCommand,mFields,' FROM zozo0615.bizonylat_torzs_2031) ');
/* END REPLACE into command generation */
/*SELECT @lcCommand; what is in it? */

/*EXECUTE the command*/
PREPARE stmt FROM @lcCommand;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;
go
delimiter ;
call teszt();

Options: ReplyQuote


Subject
Views
Written By
Posted
467
June 12, 2020 12:47AM
191
June 12, 2020 12:52AM
104
June 12, 2020 03:01AM
129
June 12, 2020 11:17AM
104
June 14, 2020 01:39AM
84
June 14, 2020 10:53AM
88
June 15, 2020 06:13AM
107
June 15, 2020 12:29PM
Re: Mysql 8 stored procedure
128
June 15, 2020 12:46PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.