5 files changed, 198 insertions, 0 deletions

diff --git a/sem_5/HLIN511_BDD/81r49qC4xiL._SL1500_.jpg b/sem_5/HLIN511_BDD/81r49qC4xiL._SL1500_.jpg
new file mode 100644
index 0000000..ca653b4
--- /dev/null
+++ b/sem_5/HLIN511_BDD/81r49qC4xiL._SL1500_.jpg
diff --git a/sem_5/HLIN511_BDD/tp4.sql b/sem_5/HLIN511_BDD/tp4.sql
new file mode 100644
index 0000000..e98fd7c
--- /dev/null
+++ b/sem_5/HLIN511_BDD/tp4.sql
@@ -0,0 +1,11 @@
+declare
+cursor c is select num_ab,count(num_ex) as nb_emprunt from Emprunt group by num_ab;
+begin
+for c_t in c
+loop
+insert into ab_nb values (c_t.num_ab,c_t.nb_emprunt);
+end loop;
+exception when no_data_found
+insert into ab_nb values (c_t.num_ab,-1);
+end;
+/
diff --git a/sem_5/HLIN511_BDD/trigger.sql b/sem_5/HLIN511_BDD/trigger.sql
new file mode 100644
index 0000000..3426185
--- /dev/null
+++ b/sem_5/HLIN511_BDD/trigger.sql
@@ -0,0 +1,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;
+/
+
+
+
+
+
+
+
+
+
+
diff --git a/sem_5/HLIN511_BDD/trigger0.sql b/sem_5/HLIN511_BDD/trigger0.sql
new file mode 100644
index 0000000..caef8e2
--- /dev/null
+++ b/sem_5/HLIN511_BDD/trigger0.sql
@@ -0,0 +1,92 @@
+set serveroutput on
+
+
+
+
+create or replace trigger trigger_1
+before insert on emprunt
+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;
+/
diff --git a/sem_5/HLIN511_BDD/xl_livre.png b/sem_5/HLIN511_BDD/xl_livre.png
new file mode 100644
index 0000000..f3b391d
--- /dev/null
+++ b/sem_5/HLIN511_BDD/xl_livre.png