Script : maj_sequences.sql

Utilisation :  sqlplus  User/Password@instance  @maj_sequences.sql CodeCours Version Cadre

set serveroutput on

set linesize 132
set pagesize 132
— Trois variable passe au programme sql : ${CodeCourtMaj} ${Version} ${NumOrdre}
— set autoprint off
— set autotrace off
— afin d’éliminer la ligne :Procédure PL/SQL terminée avec succès.
set FEEDBACK off
— afin d’éliminer la verification des variables
set VERIFY off
—
Declare
— Declaration des variables
MaRequete         varchar2(2000) ;
n                 PLS_INTEGER;
EcartSequence     number;
increment_initial user_sequences.INCREMENT_BY%TYPE  ;
MaxValSeq         user_sequences.LAST_NUMBER%TYPE  ;
MaxValCol         number;
type rc           is ref cursor;
l_rc              rc;
l_dummy           number;
Begin
DBMS_OUTPUT.ENABLE( 10000000 ) ;
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________________________________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Recuperation des noms de tables, de colonnes et de sequences pour &&1 version &&2 &&3 ‘);
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________________________________________________________’);
n := 0;
FOR seq_req IN (SELECT * FROM MG_SEQUENCE where upper(NomVersion) = upper(‘&&2’)
and upper(NUMOUTIL) = upper(‘&&3’)
and upper(NomApplication) = upper(‘&&1’)
) LOOP
n := n + 1;
EcartSequence := 0;
MaRequete := ‘select LAST_NUMBER  from user_sequences where  SEQUENCE_NAME = ‘ ||  » » || seq_req.NomSequence ||  » » ;
—    DBMS_OUTPUT.PUT_LINE (MaRequete);
EXECUTE IMMEDIATE MaRequete into MaxValSeq;
MaRequete := ‘select nvl(max(‘ || seq_req.NomColonne || ‘),0) +1 from ‘ || seq_req.NomTable ;
—    DBMS_OUTPUT.PUT_LINE (MaRequete);
EXECUTE IMMEDIATE MaRequete into MaxValCol;
—
MaRequete := ‘select (select LAST_NUMBER  from user_sequences where  SEQUENCE_NAME = ‘ ||  » » || seq_req.NomSequence ||  » » || ‘) – ( select nvl(max(‘
|| seq_req.NomColonne || ‘),0) +1 from ‘ || seq_req.NomTable || ‘) from dual’ ;

—    DBMS_OUTPUT.PUT_LINE (MaRequete);
EXECUTE IMMEDIATE MaRequete into EcartSequence;
if  n < 10 then
DBMS_OUTPUT.PUT_LINE (‘– 0’ || n || ‘ ‘ || rpad(seq_req.NomSequence || ‘=’ || seq_req.NomTable || ‘ ‘ || seq_req.NomColonne,55)  || ‘  MaxValSeq=’ ||
MaxValSeq || ‘    MaxValCol=’ || MaxValCol || ‘  Ecart = ‘ || EcartSequence);
else
DBMS_OUTPUT.PUT_LINE (‘– ‘ || n || ‘ ‘ || rpad(seq_req.NomSequence || ‘=’ || seq_req.NomTable  || ‘ ‘ ||seq_req.NomColonne,55)  || ‘  MaxValSeq=’ || M
axValSeq || ‘    MaxValCol=’ || MaxValCol || ‘  Ecart = ‘ || EcartSequence);
end if;
if    EcartSequence < 0 then
— DBMS_OUTPUT.PUT_LINE (‘– ‘ || rpad(seq_req.NomTable,25) || ‘ On Traite la sequence car:  ‘ || EcartSequence || ‘< zero’);
EcartSequence  := -1 * EcartSequence ;
— _____________________________________________________
— generation code pour maj sequence : ‘ || seq_req.NomSequence );
— _____________________________________________________
increment_initial := 0 ;
DBMS_OUTPUT.PUT_LINE (‘___________________________’);
DBMS_OUTPUT.PUT_LINE (‘Modification de la sequence’);
DBMS_OUTPUT.PUT_LINE (‘—————————‘);
MaRequete := ‘select INCREMENT_BY  from user_sequences where  SEQUENCE_NAME = ‘ ||  » » || seq_req.NomSequence ||  » » ;
DBMS_OUTPUT.PUT_LINE (MaRequete);
EXECUTE IMMEDIATE MaRequete into increment_initial;
DBMS_OUTPUT.PUT_LINE (EcartSequence || ‘ ‘ || increment_initial );
— on modifi l’increment by à la valeur maximale
MaRequete := ‘alter sequence ‘ || seq_req.NomSequence || ‘ INCREMENT BY ‘ || MaxValCol ;
EXECUTE IMMEDIATE MaRequete;
— on appelle la sequence afin de declancher l’increment maximal
open  l_rc for ‘select ‘ || seq_req.NomSequence || ‘.nextval from dual’;
fetch l_rc into l_dummy;
close l_rc;
— on remet l’increment by initial
MaRequete := ‘alter sequence ‘ || seq_req.NomSequence || ‘ INCREMENT BY ‘ || increment_initial;
EXECUTE IMMEDIATE MaRequete;
DBMS_OUTPUT.PUT_LINE (‘– _____________________________________________________’);
elsif EcartSequence = 0 then
DBMS_OUTPUT.PUT_LINE (‘– ‘ || ‘ pas de traitement car: ‘ ||  EcartSequence || ‘ = zero’);
else
DBMS_OUTPUT.PUT_LINE (‘– ‘ || ‘ pas de traitement car: ‘ ||  EcartSequence || ‘ > zero’);
end if;
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________________________________________________________’);
End loop ;
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);
DBMS_OUTPUT.PUT_LINE (‘– Nombre de Sequence traiter :’ || n);
DBMS_OUTPUT.PUT_LINE (‘– ______________________________________’);

DBMS_OUTPUT.PUT_LINE (‘–‘);
DBMS_OUTPUT.PUT_LINE (‘– fin du traitement de mise a jour des sequences de &&1 en version &&2 &&3 ‘);
DBMS_OUTPUT.PUT_LINE (‘–‘);
End ;
/
exit

script de création de la table MG

— Creation des sequences
DROP TABLE MG_SEQUENCE;
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 2 ROLLBACK
CREATE TABLE  MG_SEQUENCE
(
NomVersion VARCHAR2(7),
NumOutil VARCHAR2(2),
NomApplication VARCHAR2(2),
NomSequence VARCHAR2(30),
NomTable VARCHAR2(30),
NomColonne VARCHAR2(30)
)
;