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
| create table QUESTIONNAIRE(
idquestionnaire varchar2(5)
constraint K2 primary key,
titre varchar2(50),
duree number(3)
);
create table QUESTION (
idquestion number(5) constraint K3 primary key,
refquestionnaire varchar2(5),
text varchar2(100),
typereponse varchar2(1),
constraint KE3 foreign key (refquestionnaire)
references QUESTIONNAIRE(idquestionnaire)
);
create table REPONSE(
refquestion number(5),
text varchar2(100),
OuiNon number(1),
constraint KE4 foreign key (refquestion )
references QUESTION(idquestion)
);
create table PREREQUIS(
refquestionnaire varchar2(5),
refquestionnairerequis varchar2(5),
constraint KE5 foreign key (refquestionnaire)
references QUESTIONNAIRE(idquestionnaire) ,
constraint KE6 foreign key (refquestionnairerequis)
references QUESTIONNAIRE(idquestionnaire)
);
create table CONTRAINTDATE(
refquestionnaire varchar2(5),
datedebut date,
periodicite number(3),
unite number(3),
constraint KE7 foreign key (refquestionnaire)
references QUESTIONNAIRE(idquestionnaire)
);
CREATE OR REPLACE TRIGGER Del_Question
Before delete ON Questionnaire
FOR EACH ROW
BEGIN
Delete from Question where Question.RefQuestionnaire = :OLD.IdQuestionnaire;
Delete from PreRequis where PreRequis.RefQuestionnaire = :OLD.IdQuestionnaire;
Delete from CONTRAINTDATE where CONTRAINTDATE.RefQuestionnaire = :OLD.IdQuestionnaire;
END;
/
CREATE OR REPLACE TRIGGER Del_Reponse
Before delete ON Question
FOR EACH ROW
BEGIN
Delete from Reponse where Reponse.RefQuestion = :OLD.IdQuestion;
END;
/
CREATE SEQUENCE auto_RefQuestion START WITH 1 INCREMENT BY 1 MAXVALUE 99999;
Create or replace Function add_question (RefQuest VarChar2,TextQ VarChar2,TypeQ Varchar2)
Return number as
pragma autonomous_transaction;
V_seq integer;
Begin
Insert into Question (idquestion,refquestionnaire,text,typereponse)
values (auto_RefQuestion.nextVal, RefQuest,TextQ,TypeQ)
return idQuestion into v_seq;
commit;
Return (v_seq);
End;
/
Create or replace Function add_session (Login VarChar2,Questionnaire VarChar2)
Return number as
pragma autonomous_transaction;
v_seq number;
Begin
Insert into SessionQCM(idsession,reflogin, refQuestionnaire,DateSession)
values (auto_RefSession.nextVal, Login,Questionnaire, SysDate)
return IdSession into V_Seq;
commit;
Return (v_seq);
End;
/ |
Partager