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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
|
/*
Description : création de la table de transo RDD_TRANSCO_NIR
*/
CREATE OR REPLACE FUNCTION cp_tab_transco RETURN NUMBER IS
requete VARCHAR2(1000);
BEGIN
requete := 'DROP TABLE RDD_TRANSCO_NIR;';
EXECUTE IMMEDIATE requete;
requete := 'DROP TABLE RDD_TRANSCO_NIR_RJT;';
EXECUTE IMMEDIATE requete;
requete := '
CREATE TABLE RDD_TRANSCO_NIR (
NUMCMR VARCHAR2(3) NULL,
NIR VARCHAR2(13) NULL,
CLE VARCHAR2(2) NULL,
TYPE1 VARCHAR2(1) NULL,
RANG VARCHAR2(1) NULL,
SEQ_ASS_AYD VARCHAR2(7) NULL,
AUTRE_MEMBRE VARCHAR2(2) NULL,
NUM_INTERNE_BDU VARCHAR2(13) NULL);';
EXECUTE IMMEDIATE requete;
-- création d'un index
requete := 'CREATE INDEX ITRS ON RDD_TRANSCO_NIR(NUMCMR, NIR, TYPE1, RANG);';
EXECUTE IMMEDIATE requete;
requete := '
CREATE TABLE RDD_TRANSCO_NIR_RJT (
NUMCMR VARCHAR2(3) NULL,
NIR VARCHAR2(13) NULL,
CLE VARCHAR2(2) NULL,
TYPE1 VARCHAR2(1) NULL,
RANG VARCHAR2(1) NULL,
SEQ_ASS_AYD VARCHAR2(7) NULL,
AUTRE_MEMBRE VARCHAR2(2) NULL,
NUM_INTERNE_BDU VARCHAR2(13) NULL,
DATE_RJT TIMESTAMP NULL,
LIB_RJT VARCHAR(100) NULL);';
EXECUTE IMMEDIATE requete;
COMMIT;
RETURN NULL;
END cp_tab_transco; |
Partager