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
|
DECLARE
v_xml XMLTYPE;
BEGIN
--events
v_XML := XMLTYPE(bfilename('FICHIER_IN_FB', 'events.xml'), nls_charset_id('AL32UTF8'));
MERGE INTO EVT_EVENTS t
USING
(
SELECT x.*
FROM XMLTABLE
(
'events'
passing v_xml
columns EV_ID VARCHAR2(32) PATH '@id',
EV_TITLE VARCHAR(30) PATH 'title',
EV_SUBTITLE VARCHAR2(30) PATH 'subTitle',
EV_SHORT_DESCRIPTION VARCHAR2(1000) PATH 'shortDescription',
EV_IS_WITHOUT_SESSION varchar2(30) PATH 'isWithoutSession',
EV_IS_TICKET_MANDATORY varchar2(50) PATH 'isTicketHolderMandatory',
EV_IS_CLIENT_INFORMATION varchar2(50) PATH 'isClientInformationMandatory',
EV_STATUS varchar2(5) PATH 'status',
EV_CREATION_DATE varchar2(30) PATH 'creationDate',
EV_MODIFICATION_DATE varchar2(30) PATH 'modificationDate',
EV_ORGANIZATION varchar2(30) PATH 'organization',
EV_MIN_PRICE varchar2(30) PATH 'minPrice',
EV_MAX_PRIZE varchar2(30) PATH 'maxPrice',
EV_PLACE_ID varchar2(30) PATH 'place/@id'
)x
) src
ON (t.EV_ID = src.EV_ID)
WHEN MATCHED THEN
UPDATE SET
t.EV_TITLE = src.EV_TITLE,
t.EV_SUBTITLE = src.EV_SUBTITLE ,
t.EV_SHORT_DESCRIPTION = src.EV_SHORT_DESCRIPTION,
t.EV_IS_WITHOUT_SESSION = src.EV_IS_WITHOUT_SESSION,
t.EV_IS_TICKET_MANDATORY = src.EV_IS_TICKET_MANDATORY,
t.EV_IS_CLIENT_INFORMATION = src.EV_IS_CLIENT_INFORMATION,
t.EV_STATUS = src.EV_STATUS,
t.EV_CREATION_DATE = src.EV_CREATION_DATE,
t.EV_MODIFICATION_DATE = src.EV_MODIFICATION_DATE,
t.EV_ORGANIZATION = src.EV_ORGANIZATION,
t.EV_MIN_PRICE = src.EV_MIN_PRICE ,
t.EV_MAX_PRIZE = src.EV_MAX_PRIZE,
t.EV_PLACE_ID = src.EV_PLACE_ID,
t.ev_current_date = sysdate
WHEN NOT MATCHED THEN
INSERT (t.EV_TITLE,
t.EV_SUBTITLE,
t.EV_SHORT_DESCRIPTION,
t.EV_IS_WITHOUT_SESSION,
t.EV_IS_TICKET_MANDATORY,
t.EV_IS_CLIENT_INFORMATION,
t.EV_STATUS,
t.EV_CREATION_DATE,
t.EV_MODIFICATION_DATE,
t.EV_ORGANIZATION,
t.EV_MIN_PRICE,
t.EV_MAX_PRIZE,
t.EV_PLACE_ID,
t.ev_current_date)
VALUES (src.EV_TITLE,
src.EV_SUBTITLE,
src.EV_SHORT_DESCRIPTION,
src.EV_IS_WITHOUT_SESSION,
src.EV_IS_TICKET_MANDATORY,
src.EV_IS_CLIENT_INFORMATION,
src.EV_STATUS,
src.EV_CREATION_DATE,
src.EV_MODIFICATION_DATE,
src.EV_ORGANIZATION,
src.EV_MIN_PRICE,
src.EV_MAX_PRIZE,
src.EV_PLACE_ID,
sysdate
);
end; |
