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
|
set serveroutput on
create or replace trigger trigger_1
before insert on exemplaire
for each row
begin
dbms_output.put_line('insertion du tuple '||:new.numero);
end;
/
select trigger_name from user_triggers;
drop trigger trigger1;
show errors
insert into exemplaire values (5200,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
select trim(to_char(sysdate,'day')) from dual;
create or replace trigger trigger_2
before insert on exemplaire
for each row
begin
if trim(to_char(sysdate,'day')) = 'monday'
then raise_application_error(-20100,'insertion impossible le lundi');
end if ;
end;
/
insert into exemplaire values (5201,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
drop trigger trigger_2;
create table t1 (numero number(4), date_insertion date, usager varchar(20), typeOperation varchar(12));
create or replace trigger trigger_3
after insert or update or delete on exemplaire
for each row
begin
if inserting
then
insert into t1 values (:new.numero,sysdate,user,'insertion');
elsif updating
then
insert into t1 values (:new.numero,sysdate,user,'modification');
elsif deleting
then
insert into t1 values (:old.numero,sysdate,user,'suppression');
end if;
end;
/
insert into exemplaire values (6202,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
update exemplaire set prix = 100 where numero = 6202;
delete from exemplaire where numero =6202;
-- programme principal
-- curseur implicite
declare
num number(4);
isbn_livre varchar(20);
begin
select numero, isbn into num, isbn_livre from exemplaire where numero =#
dbms_output.put_line(' exemplaire '||num||' '||isbn_livre);
exception when no_data_found then dbms_output.put_line('inexistant');
end;
/
-- curseur explicite
declare
cursor c is select numero, isbn from exemplaire;
begin
for c_t in c
loop
dbms_output.put_line(' exemplaire '||c_t.numero||' '||c_t.isbn);
end loop;
exception when no_data_found then dbms_output.put_line('inexistant');
end;
/
|
