MySQL Forums
Forum List  »  Stored Procedures

Re: Mysql 8 stored procedure
Posted by: Zoltan Szmutku
Date: June 12, 2020 12:52AM

The script code belows:

CREATE PROCEDURE `xp_bizadd`(IN `mptorzsazon` BIGINT)
BEGIN
DECLARE nTempRand BIGINT default round(rand()*1000000000000000000);
DECLARE nDefault INT default 1;
DECLARE lnRet INT default 0;
DECLARE countRow INT default 0;
DECLARE insRow INT default 0;
DECLARE done INT default False;
DECLARE llRet INT default True;
DECLARE mField CHAR(20) default '';
DECLARE mFields TEXT;
DECLARE mFieldR CHAR(20) default '';
DECLARE mFieldsR TEXT;
DECLARE mFieldA CHAR(20) default '';
DECLARE mFieldsA TEXT;
DECLARE mFieldE CHAR(20) default '';
DECLARE mFieldsE TEXT;
DECLARE pnTombazon INT default 0;
DECLARE pnBiztipus INT default 0;
DECLARE pnBoltazon INT default 0;
DECLARE pnEvszam INT default 0;
DECLARE pnVegszamla INT default 0;
DECLARE pnKovbiztip,pnJovairt, pnStornozott,pnStorno,pnJovairo,pnFizetve,pnKovBiztipUpdate INT default 0;
DECLARE pnEtorzsazon BIGINT default 0;
DECLARE pnEEVszam NUMERIC(4) default 0;
DECLARE pnKibdtTemp DATE default '0000-00-00';
DECLARE mirany,mraktar NUMERIC(1) default 0;
DECLARE maruazon,mraktarazon,mboltazon,mbiztipus INT default 0;
DECLARE mmennyiseg DOUBLE(8,3) default 0;
DECLARE pnMaxKibdt DATE default '0000-00-00';
DECLARE nuj DECIMAL(12,0) DEFAULT 0;
DECLARE pnBiztipusP,pnTombjel CHAR(10) default '';
DECLARE tempbiz CURSOR FOR SELECT * from bizonylat_szam WHERE tombpazon = (pnTombazon) AND biztipus=(pnBiztipus) AND evszam=(pnEvszam) FOR UPDATE;
DECLARE tempcur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = concat('bizonylat_torzs_',pnEvszam);
DECLARE tempcur2 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = concat('bizonylat_resz_',pnEvszam);
DECLARE tempcur3 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = concat('bizonylat_afa_',pnEvszam);
DECLARE tempcur4 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = concat('bizonylat_eloleg_',pnEvszam);
DECLARE temprow CURSOR FOR SELECT biztipus,irany,aruazon,raktarazon,mennyiseg FROM bizonylat_resz_temp WHERE ptorzsazon = mptorzsazon;
/*DECLARE temprow CURSOR FOR SELECT biztipus,irany,aruazon,raktarazon,mennyiseg FROM bizonylat_resz_temp WHERE ptorzsazon = nTempRand; */
/* temprow és tempeloleg még korábban használódik, nincs még kicserélve a kapcsolómező. Ha már ki lenne, akkor a másik sor kellene*/
DECLARE tempeloleg CURSOR FOR SELECT EEVSZAM,ETORZSAZON FROM bizonylat_eloleg_temp WHERE ptorzsazon = mptorzsazon;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET llRet = FALSE;

/*select nTempRand;*/

/*DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

GET DIAGNOSTICS CONDITION 1
@p2 = MESSAGE_TEXT;
SELECT @p2;
SET llRet=false;
END;*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
/*select mptorzsazon;*/
SELECT boltazon,tombpazon,biztipus,evszam,kibdt,vegszamla,biztipusp,tombjel FROM bizonylat_torzs_temp WHERE pazon=mptorzsazon INTO pnBoltazon,pnTombazon,pnBiztipus,pnEvszam,pnKibdtTemp,pnVegszamla,pnBiztipusp,pnTombjel;
SET llRet = (llRet AND (FOUND_ROWS() = 1));

IF (llRet) THEN
SET mFields='';
SET mFieldsR='';
SET mFieldsA='';
SET mFieldsE='';
SET @pnUjazon=0;
SET @cParancs='';
SET @pnElo=0;
SET @commitnuj = -100;
/* Ezek az utóbbiak session változók kell legyenek*/
ELSE
SET nuj=-10;
/* nincs meg a tárolandó fejléc*/
END IF;


IF (llRet) THEN

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,IF((mField <> "PAZON"),mField,"0 as pazon"));
ELSE
SET mFields = CONCAT(mFields,',',IF((mField <> "PAZON"),mField,"0 as pazon")) ;
END IF;
END LOOP;


SET done = False;
CLOSE tempcur;


IF (llRet) THEN
OPEN tempcur2;
ELSE
SET nuj=-1;
END IF;

IF (llRet) THEN

read_loop: LOOP
FETCH tempcur2 INTO mFieldR;

IF done THEN
LEAVE read_loop;
END IF;

IF (mFieldsR='') THEN
/* SET mFieldsR = CONCAT(mFieldsR,IF((mFieldR <> "PAZON"),mFieldR,"0 as pazon")) ; */
SET mFieldsR = CONCAT(mFieldsR,IF((mFieldR <> "PAZON"),IF((mFieldR<>"PTORZSAZON"),mFieldR, " @pnUjazon as ptorzsazon"),"0 as pazon")) ;
ELSE
/* SET mFieldsR = CONCAT(mFieldsR,',',IF((mFieldR <> "PAZON"),mFieldR,"0 as pazon")) ; */
SET mFieldsR = CONCAT(mFieldsR,',',IF((mFieldR <> "PAZON"),IF((mFieldR<>"PTORZSAZON"),mFieldR, " @pnUjazon as ptorzsazon"),"0 as pazon"));

END IF;

END LOOP;

SET done = False;
CLOSE tempcur2;

END IF;

IF (llRet) THEN
OPEN tempcur3;
END IF;

IF (llRet) THEN

SET done = False;

read_loop: LOOP
FETCH tempcur3 INTO mFieldA;

IF done THEN
LEAVE read_loop;
END IF;
IF (mFieldsA='') THEN
/* SET mFieldsA = CONCAT(mFieldsA,IF((mFieldA <> "PAZON"),mFieldA,"0 as pazon")) ; */
SET mFieldsA = CONCAT(mFieldsA, IF((mFieldA <> "PAZON"),IF((mFieldA<>"PTORZSAZON"),mFieldA, " @pnUjazon as ptorzsazon"),"0 as pazon"));
ELSE
SET mFieldsA = CONCAT(mFieldsA, ',',IF((mFieldA <> "PAZON"),IF((mFieldA<>"PTORZSAZON"),mFieldA, " @pnUjazon as ptorzsazon"),"0 as pazon"));
/* SET mFieldsA = CONCAT(mFieldsA,',',IF((mFieldA <> "PAZON"),mFieldA,"0 as pazon")) ; */
END IF;
END LOOP;

CLOSE tempcur3;
SET done = False;
/*SELECT mFieldsA,@mFieldA;*/
END IF;
END IF;

/* eloleg mezők*/
IF (llRet) THEN

OPEN tempcur4;

END IF;

IF (llRet) THEN

read_loop: LOOP
FETCH tempcur4 INTO mFieldE;

IF done THEN
LEAVE read_loop;
END IF;
IF (mFieldsE='') THEN
/* SET mFieldsE = CONCAT(mFieldsE,IF((mFieldE <> "PAZON"),mFieldE,"0 as pazon")) ; */
SET mFieldsE = CONCAT(mFieldsE, IF((mFieldE <> "PAZON"),IF((mFieldE<>"PTORZSAZON"),mFieldE, " @pnUjazon as ptorzsazon"),"0 as pazon"));
ELSE
/* SET mFieldsE = CONCAT(mFieldsE,',',IF((mFieldE <> "PAZON"),mFieldE,"0 as pazon")) ; */
SET mFieldsE = CONCAT(mFieldsE, ',' ,IF((mFieldE <> "PAZON"),IF((mFieldE<>"PTORZSAZON"),mFieldE, " @pnUjazon as ptorzsazon"),"0 as pazon"));

END IF;
END LOOP;

SET done = False;
CLOSE tempcur4;
/*SELECT mFieldE,mFieldsE;*/
END IF;

/* eloleg*/

IF (llRet) THEN
SET autocommit = 1;
/*SELECT mFields,mFieldsR,mFieldsA,mFieldsE;*/

START TRANSACTION;


OPEN tempbiz;
SET countRow = FOUND_ROWS();
/*select llRet;*/
/*CLOSE tempbiz; nem tudom megszunteti e az update-et, egyelőre nem zárom be, gondolom magától is bezáródik a végén.*/

IF (llRet) AND (pnBiztipus = 1) AND (pnVegszamla=1) THEN
/* Előlegek ellenőrzése, update fogása */
SET pnKovbiztipUpdate=1;
SET done = False;
OPEN tempeloleg;

read_loop: LOOP
FETCH tempeloleg INTO pnEEVszam,pnEtorzsazon;

IF done THEN
LEAVE read_loop;
END IF;

/* pnEEVSZAM fájlban lévő pnEtorzsazon azonosítójú sort ellenőrizni, updatelni*/
SET @cParancs = concat("SELECT pazon FROM bizonylat_torzs_",pnEEVSZAM," WHERE fizetve=1 AND (kovbiztip+jovairo+jovairt+stornozott+storno)=0 AND pazon=",pnEtorzsazon," into @pnElo FOR UPDATE ") ;
PREPARE stmtA FROM @cParancs;
EXECUTE stmtA;
SET llRet = (llRet AND (FOUND_ROWS()=1));
DEALLOCATE PREPARE stmtA;
SET done = False;
/*ha véletlenül a select üresre ment, akkor a cilusváltozó is megváltozik az INTO miatt . Ezt nem akarjuk, ezért itt visszaállítom*/
/*select llRet,@cParancs,FOUND_ROWS();*/
IF (llRet = False) THEN
SET pnKovbiztipUpdate=0;
SET nuj=-2;
LEAVE read_loop;
END IF;

END LOOP;

SET done = False;
CLOSE tempeloleg;
END IF;


IF (llRet) THEN
SELECT MAX(kibdt) FROM bizonylat_szam WHERE biztipus=pnBiztipus into pnMaxKibdt;
/*SELECT llRet,pnBiztipus,pnMaxKibdt,pnKibdtTemp,(pnKibdtTemp < pnMaxKibdt) as logika;*/

IF (llRet) THEN

IF (pnKibdtTemp < pnMaxKibdt) THEN
SET llRet = False;
SET nuj = -1;
END IF;
END IF;
/* select pnKibdtTemp,pnMaxKibdt,pnKibdtTemp<pnMaxKibdt AS ks,llRet;*/
END IF;

IF (llRet) THEN
/*select countrow;*/
IF(countRow = 1) THEN
UPDATE bizonylat_szam SET bizszam=bizszam+1,kibdt=pnKibdtTemp WHERE tombpazon = pnTombazon AND biztipus=pnBiztipus AND evszam=pnEvszam;
ELSE
/* select nDefault,pnTombazon,pnBiztipus,pnEvszam,pnKibdtTemp,llRet,ROW_COUNT();*/
REPLACE INTO bizonylat_szam (bizszam,tombpazon,biztipus,evszam,kibdt) VALUES (nDefault,pnTombazon,pnBiztipus,pnEvszam,pnKibdtTemp);
/* SELECT @cparancs;*/
END IF;

SET llRet = (llRet AND (ROW_COUNT() > 0)) ;

END IF;

IF (llRet) THEN
SELECT bizszam FROM bizonylat_szam WHERE tombpazon = (pnTombazon) AND biztipus=(pnBiztipus) AND evszam=(pnEvszam) into nuj ;
END IF;

SET llRet = ( llRet AND (nuj > 0));

/*SELECT llRet,@nuj;*/
END IF;

IF (llRet) then
UPDATE bizonylat_torzs_temp SET bizszam=nuj WHERE pazon=mptorzsazon ;
/*select nuj,llRet,mptorzsazon;*/
END IF;

/* készletkezelés kiszedve
IF (llRet) THEN
SET @done=false;
OPEN temprow;
SET insRow = FOUND_ROWS();

read_loop: LOOP
FETCH temprow INTO mbiztipus,mirany,maruazon,mraktarazon,mmennyiseg ;*/
/*select mbiztipus,mirany,maruazon,mraktarazon,mmennyiseg;*/
/*
IF done THEN
LEAVE read_loop;
END IF;

CASE
WHEN (mbiztipus=3) then
UPDATE raktar SET keszlet=keszlet+(mirany*mmennyiseg),foglalasf=foglalasf+(mirany*mmennyiseg) WHERE aruazon=maruazon AND boltazon=pnBoltazon AND raktarazon=mraktarazon;

WHEN (mbiztipus=2) then
UPDATE raktar SET keszlet=keszlet+(mirany*mmennyiseg),rendelesf=rendelesf+(mirany*mmennyiseg) WHERE aruazon=maruazon AND boltazon=pnBoltazon AND raktarazon=mraktarazon;

ELSE
UPDATE raktar SET keszlet=keszlet+(mirany*mmennyiseg),keszletf=keszletf+(mirany*mmennyiseg) WHERE aruazon=maruazon AND boltazon=pnBoltazon AND raktarazon=mraktarazon;

END CASE ;
*/
/*SELECT maruazon,pnBoltazon,mraktarazon,llRet, row_count();*/
/*
IF (NOT llRet OR (row_count()<1)) then
SET @done=true;
SET @llRet=false;
END If;

END LOOP;

CLOSE temprow;

END IF;*/

IF (llRet) THEN
/* lekérdezni a beillesztett fejléc sor új pazonját és kiírogatni a többi temp fájlok ptorzsazonba*/

/* A PREPARE meg az EXECUTE kukacos session változókat igényel - ezeket minden esetben inicializálni kell
és célszerű hogy minden procedúrában egyedi nevei legyenek */
SET @cParancs = concat("SELECT IFNULL(MAX(pazon),0)+1 FROM bizonylat_torzs_",pnEvszam," INTO @pnUjazon FOR UPDATE") ;
PREPARE stmt FROM @cParancs;
EXECUTE stmt;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt;

/*SELECT @cParancs,@pnUjazon,insRow;*/

SET llRet = (llRet AND (@pnUjazon > 0));

IF (llRet AND pnKovbiztipUpdate=1) THEN

SET done = False;
OPEN tempeloleg;

read_loop: LOOP
FETCH tempeloleg INTO pnEEVszam,pnEtorzsazon;

IF done THEN
LEAVE read_loop;
END IF;


/* pnEEVSZAM fájlban lévő pnEtorzsazon azonosítójú sort updatelni
kovbiztip=1
kovbizszam=ALLTRIM(biztipusp)+"-"+TRANSFORM(evszam)+"/"+TRANSFORM(bizszam)+"/"+ALLTRIM(tombjel)
kovbizazon= az új számla pazon-ja
kovbizevsz= evszam*/

SET @cParancs = concat("UPDATE bizonylat_torzs_",pnEEVSZAM," SET kovbiztip=1,kovbizevsz=",pnEvszam,",kovbizazon=",@pnUjazon,",kovbizszam='",pnBiztipusP,"-",pnEvszam,"-",nuj,pnTombjel,"' WHERE pazon=",pnEtorzsazon ) ;
PREPARE stmt FROM @cParancs;
EXECUTE stmt;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt;

/*select llRet,@cParancs;*/


END LOOP;


SET done = False;
CLOSE tempeloleg;

END IF;


IF (llRet) THEN
/*select * FROM bizonylat_torzs_temp WHERE pazon=@mptorzsazon;*/

SET @cParancs = concat("REPLACE INTO bizonylat_torzs_",pnEvszam," (SELECT ",mFields," FROM bizonylat_torzs_temp WHERE pazon=",mptorzsazon,")" ) ;
PREPARE stmt0 FROM @cParancs;
EXECUTE stmt0;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt0;
/*SELECT @cParancs,llRet,Row_count() ,"REPLACE INTO bizonylat_torzs_";*/

IF (llRet) THEN
/* UPDATE bizonylat_resz_temp SET ptorzsazon=@pnUjazon WHERE ptorzsazon=mptorzsazon ; */
UPDATE bizonylat_resz_temp SET ptorzsazon=nTempRand WHERE ptorzsazon=mptorzsazon ;
/* Beragadt relációs temp sorok miatt két lépcsőben kell kiírni az új azonosítót*/

END IF;

IF (llRet) THEN
/* UPDATE bizonylat_afa_temp SET ptorzsazon=@pnUjazon WHERE ptorzsazon=mptorzsazon ; */
UPDATE bizonylat_afa_temp SET ptorzsazon=nTempRand WHERE ptorzsazon=mptorzsazon ;
/* Beragadt relációs temp sorok miatt két lépcsőben kell kiírni az új azonosítót*/
END IF;

IF (llREt) THEN
/* UPDATE bizonylat_eloleg_temp SET ptorzsazon=@pnUjazon WHERE ptorzsazon=mptorzsazon ; */
UPDATE bizonylat_eloleg_temp SET ptorzsazon=nTempRand WHERE ptorzsazon=mptorzsazon ;
/* Beragadt relációs temp sorok miatt két lépcsőben kell kiírni az új azonosítót*/
END IF;

END IF;




IF (llRet ) THEN
/*SET @cParancs = concat("REPLACE INTO bizonylat_resz_",pnEvszam," (SELECT ",mFieldsR," FROM bizonylat_resz_temp WHERE ptorzsazon=",@pnUjazon,")") ;*/
SET @cParancs = concat("REPLACE INTO bizonylat_resz_",pnEvszam," (SELECT ",mFieldsR," FROM bizonylat_resz_temp WHERE ptorzsazon=",nTempRand,")") ;


PREPARE stmt2 FROM @cParancs;
EXECUTE stmt2;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt2;

/*select @cparancs,llRet;*/

/*SELECT * from bizonylat_resz_temp WHERE ptorzsazon=@pnUjazon;*/
END IF;


IF (llRet ) THEN
/*SET @cParancs = concat("REPLACE INTO bizonylat_afa_",pnEvszam," (SELECT ",mFieldsA," FROM bizonylat_afa_temp WHERE ptorzsazon=",@pnUjazon,")") ;*/
SET @cParancs = concat("REPLACE INTO bizonylat_afa_",pnEvszam," (SELECT ",mFieldsA," FROM bizonylat_afa_temp WHERE ptorzsazon=",nTempRand,")") ;

PREPARE stmt3 FROM @cParancs;
EXECUTE stmt3;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt3;

/*SELECT @cParancs,llRet,insRow;*/
END IF;


IF (llRet ) THEN
/*SET @cParancs = concat("REPLACE INTO bizonylat_eloleg_",pnEvszam," (SELECT ",mFieldsE," FROM bizonylat_eloleg_temp WHERE ptorzsazon=",@pnUjazon,")") ;*/

SET @cParancs = concat("REPLACE INTO bizonylat_eloleg_",pnEvszam," (SELECT ",mFieldsE," FROM bizonylat_eloleg_temp WHERE ptorzsazon=",nTempRand,")") ;

PREPARE stmt4 FROM @cParancs;
EXECUTE stmt4;
SET insRow = ROW_COUNT();
DEALLOCATE PREPARE stmt4;

/*SELECT @cParancs,llRet,insRow;*/
END IF;

END IF;

IF (llRet) then
/*SELECT "commit";*/

DELETE from bizonylat_torzs_temp WHERE pazon=mptorzsazon;
/* DELETE from bizonylat_resz_temp WHERE ptorzsazon=@pnUjazon;
DELETE from bizonylat_afa_temp WHERE ptorzsazon=@pnUjazon;
DELETE from bizonylat_eloleg_temp WHERE ptorzsazon=@pnUjazon; */
DELETE from bizonylat_resz_temp WHERE ptorzsazon=nTempRand;
DELETE from bizonylat_afa_temp WHERE ptorzsazon=nTempRand;
DELETE from bizonylat_eloleg_temp WHERE ptorzsazon=nTempRand;
commit ;
close tempbiz;


SET @cParancs = concat("SELECT bizszam FROM bizonylat_torzs_",pnEvszam," WHERE pazon=",@pnUjazon," INTO @commitnuj" ) ;
PREPARE stmt11 FROM @cParancs;
EXECUTE stmt11;
DEALLOCATE PREPARE stmt11;

SET llRet = (@commitnuj > 0) ;

IF ( llRet=false) THEN
SET nuj=-12;
END IF;

SELECT nuj AS sorszam;

ELSE
rollback ;
close tempbiz;
IF (nuj > 0) THEN
/*sorszám kérés után ment hibára - sorszámot kiüríteni*/
SET nuj = -11 ;
END IF;
SELECT nuj AS sorszam;
END IF;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
497
June 12, 2020 12:47AM
Re: Mysql 8 stored procedure
223
June 12, 2020 12:52AM
115
June 12, 2020 03:01AM
139
June 12, 2020 11:17AM
112
June 14, 2020 01:39AM
93
June 14, 2020 10:53AM
94
June 15, 2020 06:13AM
120
June 15, 2020 12:29PM
137
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.