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; |
Problème de création d'une table par procédure
Répondre avec citation
Partager