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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216
| CREATE OR REPLACE PROCEDURE randriano."rand_CREATETAB" (v_tab varchar2)
is
v_cdef varchar2(256);
pragma autonomous_transaction;
begin
EXECUTE IMMEDIATE
'CREATE TABLE "' || upper(v_tab) ||
'" ( Equipment varchar2(38) PRIMARY KEY)';
end;
/
CREATE OR REPLACE TRIGGER randriano."TRIGGER_AFINS_TABLE4" AFTER
INSERT ON randriano.rand_TABLE4 FOR EACH ROW Declare
v_Name varchar2 (256);
v_Type int;
v_TableName varchar2 (512);
v_DEFTableName varchar2 (512);
v_ColumnName varchar2 (512);
v_ColumnType varchar2 (512);
v_ColumnTypeChar varchar2 (512);
v_ColumnPrecision int;
v_ColumnHasPrecision number(1);
v_IsNullable varchar2(25);
v_Default varchar2 (256);
v_ColumnDEFAULT varchar2 (4000);
v_fk_name varchar2(30);
v_namefk varchar2(23);
v_fk_name1 varchar2(30);
v_namefk1 varchar2(14);
v_cdef varchar2(256);
BEGIN
PKG_UPDATEAVAINS.v_compt := PKG_UPDATEAVAINS.v_compt+1;
select :new.name CLE into v_name from dual rand_TABLE4;
select :new.type CLE into v_type from dual rand_TABLE4;
v_TableName := 'rand_TOOL_'||v_Name;-- in total upper case
-- begin if
IF (v_Type = 1) then
v_DEFTableName := 'rand_KAISER1';
end if;
IF (v_Type = 2) then
v_DEFTableName := 'rand_KAISER2';
end if ;
IF (v_Type = 4)then
v_DEFTableName := 'rand_KAISER3';
end if;
IF (v_Type = 8)then
v_DEFTableName := 'rand_KAISER4';
end if ;
IF (v_Type = 16) then
v_DEFTableName := 'rand_KAISER5';
end if ;
IF (v_Type = 32)then
v_DEFTableName:= 'rand_KAISER6';
end if ;
IF (v_Type = 64) then
v_DEFTableName := 'rand_KAISER7';
end if ;
IF (v_Type = 128) then
v_DEFTableName := 'rand_KAISER8';
end if ;
IF (v_Type = 256) then
v_DEFTableName := 'rand_KAISER9';
end if ;
--end if
--create table class
v_tablename := substr(v_tablename,1,30);
rand_createtab(v_TableName);
-- dbt create fk
v_namefk := rand_substr(v_name);
v_fk_name := 'FK_Cls_' || v_namefk ;
if v_type = 2 then
null;
else
rand_ADDFKFTABCLASS(v_TableName,v_fk_name);
end if;
-- end create fk
declare
cursor rand_cursor is
select column_name,data_length,type_name,data_type,nullable,data_default from all_tab_columns ,all_types,all_tables
where all_tables.table_name = all_tab_columns.table_name and
all_tab_columns.table_name =v_DEFTableName and
(data_default is null or data_default is not null) and
all_tab_columns.data_type = all_types.typecode order by (column_id);
v_colhint varchar2(50);
begin
open rand_cursor ; --dbt curseur pour verif des colonnes
LOOP
fetch rand_cursor INTO v_ColumnName,v_ColumnPrecision,v_ColumnType,v_ColumnTypeChar,v_IsNullable,v_Default;
EXIT when rand_cursor%NOTFOUND;
IF ( LOWER(v_ColumnType) = 'varchar2' OR
LOWER(v_ColumnType) = 'char2' OR
LOWER(v_ColumnType) = 'float' ) then
v_ColumnHasPrecision := 1;
else
v_ColumnHasPrecision :=0;
end if;
v_TableName:=UPPER(v_TableName);
v_ColumnName:=UPPER(v_ColumnName);
select RTRIM('%rand_SCHEMA%.','.') into v_colhint from dual;
Declare-- declaration de curseur to verify col
CURSOR c_col is
select column_name from all_tab_columns where owner = v_colhint and table_name = v_TableName and column_name = v_ColumnName;
v_namecol varchar2 (265);
begin
open c_col ;
fetch c_col INTO v_namecol;
if v_namecol is null then --dbt test col
IF (v_ColumnHasPrecision = 1)then
v_cdef := v_ColumnName||' '||' '||v_ColumnType||' '||CONCAT(CONCAT(CHR(40), v_ColumnPrecision),CHR(41));
rand_ADDCOLFTABCLASS(v_TableName,v_cdef);
else
v_cdef := v_ColumnName||' '||' '||v_ColumnType;
rand_ADDCOLFTABCLASS(v_TableName,v_cdef);
End if ;
IF(v_Default IS NOT NULL)then--si y a valeur par défaut
rand_ADDDEFAULTCLS(v_TableName,v_ColumnName,(v_Default));
End if ;
end if;--fin test col
close c_col;
end ;-- fin section curseur pour verif col
IF (v_IsNullable = 'N') then --dbt test null
IF (v_ColumnHasPrecision = 1) then
--v_cdef := v_ColumnName||' '||' '||v_ColumnType||' '||CONCAT(CONCAT(CHR(40), v_ColumnPrecision),CHR(41))||' '|| 'NOT NULL' ;
v_cdef := v_ColumnName||' '||' '||v_ColumnType||' '||CONCAT(CONCAT(CHR(40), v_ColumnPrecision),CHR(41));
rand_ALTCOLFTABCLASS(v_TableName,v_cdef);
else
v_cdef := v_ColumnName||' '||' '||v_ColumnType||' '||'NOT NULL';
rand_ALTCOLFTABCLASS(v_TableName,v_cdef);
end if;
end if;--end if test null
END LOOP;
CLOSE rand_cursor;--fin curseur pour recup des colonnes
end;
IF (v_Type = 2) then
v_namefk1 := rand_substr2(v_name);
v_fk_name1 := 'FK_Cls_' || v_namefk1 || '_IDplugmap' ;
rand_ADDFK2FTABCLASS(v_TableName,v_fk_name1);
end if;
PKG_UPDATEAVAINS.v_compt :=0;
end; |
Partager