1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| CREATE PROCEDURE WELL_LOGGIN(out P_well_schema varchar(60))
BEGIN
DECLARE fin_c BOOLEAN DEFAULT 0;
DECLARE VC_SCHEMA varchar(60);
declare P_well_schema varchar(60);
DECLARE VC_WELL_SCHEMA Varchar(30);
DECLARE VC_REQUEST Varchar(1000);
DECLARE c CURSOR FOR SELECT DISTINCT table_schema FROM information_schema.COLUMNS WHERE table_name = 'WELL'AND column_name ='WELL_SCHEMA';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_c := 1;
OPEN c;
FETCH c INTO VC_SCHEMA;
WHILE (NOT fin_c) DO
IF VC_REQUEST IS NOT NULL THEN
SET VC_REQUEST := concat(VC_REQUEST ,'UNION');
END IF;
SET VC_REQUEST = concat( VC_REQUEST ,' (select WELL_SCHEMA from ', VC_SCHEMA ,
'.WELL where Well_status = 3) ');
END WHILE;
SET @req := concat ('select into @PS_well_schema WELL_SCHEMA from (',VC_REQUEST,')');
PREPARE etat FROM @req;
EXECUTE etat;
DEALLOCATE PREPARE etat;
set P_well_schema:=@PS_well_schema;
END; |
Partager