przykladowa

DROP TABLE Pacjenty  CASCADE CONSTRAINTS;
DROP TABLE Lekarze  CASCADE CONSTRAINTS;
DROP TABLE Spec CASCADE CONSTRAINTS;
DROP TABLE Lek_spec CASCADE CONSTRAINTS;
DROP TABLE Badania CASCADE CONSTRAINTS;
DROP TABLE Lekarstwa CASCADE CONSTRAINTS;
DROP TABLE Choroby CASCADE CONSTRAINTS;
DROP TABLE Karta_Pacjenta CASCADE CONSTRAINTS;
DROP TABLE Sale CASCADE CONSTRAINTS;
DROP TABLE Termin_wizytu CASCADE CONSTRAINTS;
DROP TABLE x CASCADE CONSTRAINTS;

CREATE TABLE x(b DATE);


CREATE TABLE Pacjenty (
Id_pacjenta NUMBER(2) CONSTRAINT pacj_pk PRIMARY KEY,
Imie VARCHAR2(25) NOT NULL,
Nazwisko VARCHAR2(30) NOT NULL,
Wiek NUMBER NOT NULL CONSTRAINT Wiek CHECK((Wiek>=0) AND (Wiek<=125)),
Gender VARCHAR2(10) NOT NULL,
Miasto VARCHAR2(20) NOT NULL,
Ulica VARCHAR2(20) NOT NULL,
Dom VARCHAR2(10) NOT NULL,
Pesel CHAR(11) NOT NULL CONSTRAINT pacj_uni UNIQUE
);

CREATE TABLE Lekarze (
Id_lekarza NUMBER CONSTRAINT lek_pk PRIMARY KEY,
Imie VARCHAR2(25) NOT NULL,
Nazwisko VARCHAR2(30) NOT NULL,
Miasto VARCHAR2(20) NOT NULL,
Ulica VARCHAR2(30) NOT NULL,
Dom VARCHAR2(10) NOT NULL,
Pesel CHAR(11) NOT NULL CONSTRAINT lek_uni UNIQUE
);

CREATE TABLE Spec (
Id_spec NUMBER CONSTRAINT spec_pk PRIMARY KEY,
Specj VARCHAR2(50) NOT NULL
);

CREATE TABLE Lek_spec (
Id_lekarza NUMBER NOT NULL,
Id_spec NUMBER NOT NULL,
CONSTRAINT tab_to_lek FOREIGN KEY (Id_lekarza) REFERENCES Lekarze(Id_lekarza),
CONSTRAINT tab_to_spec FOREIGN KEY (Id_spec) REFERENCES Spec(Id_spec)
);

CREATE TABLE Badania (
Id_badania NUMBER CONSTRAINT bad_pk PRIMARY KEY,
Badanie VARCHAR2(50) NOT NULL
);

CREATE TABLE Lekarstwa (
Id_lekarstw NUMBER CONSTRAINT lekarstwa_pk PRIMARY KEY,
Lekarstwo VARCHAR2(50) NOT NULL
);

CREATE TABLE Choroby (
Id_Choroby NUMBER CONSTRAINT Choroby_pk PRIMARY KEY,
Choroba VARCHAR2(50) NOT NULL
);

CREATE TABLE Karta_Pacjenta (
Id_karta NUMBER CONSTRAINT karta_pk PRIMARY KEY,
Id_pacjenta NUMBER NOT NULL,
Id_badania NUMBER NOT NULL,
Id_lekarstw NUMBER,
Id_Choroby NUMBER NOT NULL,
Objawy VARCHAR(55),
CONSTRAINT Kar_pac FOREIGN KEY (Id_pacjenta) REFERENCES Pacjenty(Id_pacjenta),
CONSTRAINT Kar_badnia FOREIGN KEY (Id_badania) REFERENCES Badania(Id_badania),
CONSTRAINT Kar_lekarstw FOREIGN KEY (Id_lekarstw) REFERENCES Lekarstwa(Id_lekarstw),
CONSTRAINT Kar_choroby FOREIGN KEY (Id_Choroby) REFERENCES Choroby(Id_Choroby)
);

CREATE TABLE Sale (
Id_Sale NUMBER CONSTRAINT sala_pk PRIMARY KEY,
Numer_s NUMBER NOT NULL,
Widzial VARCHAR(100)
);

CREATE TABLE Termin_wizytu(
Id_ternimu  NUMBER CONSTRAINT termin_pk PRIMARY KEY,
Id_lekarza NUMBER NOT NULL,
Id_karta NUMBER NOT NULL,
Id_pacjenta NUMBER NOT NULL,
Id_Sale NUMBER NOT NULL,
data_t DATE NOT NULL,
CONSTRAINT ter_lek FOREIGN KEY (Id_lekarza) REFERENCES Lekarze(Id_lekarza),
CONSTRAINT ter_karta FOREIGN KEY (Id_pacjenta) REFERENCES Karta_Pacjenta(Id_karta),
CONSTRAINT ter_pac FOREIGN KEY (Id_pacjenta) REFERENCES Pacjenty(Id_pacjenta),
CONSTRAINT ter_sale FOREIGN KEY (Id_Sale) REFERENCES Sale(Id_Sale)
);


DELETE FROM Spec;
DELETE FROM Lekarze;
DELETE FROM Lek_spec;
DELETE FROM Pacjenty;
DELETE FROM Badania;
DELETE FROM Lekarstwa;
DELETE FROM Choroby;
DELETE FROM Karta_Pacjenta;
DELETE FROM Sale;
DELETE FROM Termin_wizytu;
DELETE FROM x;

INSERT INTO x VALUES(SYSDATE);

INSERT INTO Spec VALUES(1,'Anestezjologia i intensywna terapia');
INSERT INTO Spec VALUES(2,'Audiologia i foniatria');
INSERT INTO Spec VALUES(3,'Chirurgia og?lna');
INSERT INTO Spec VALUES(4,'Dermatologia i wenerologia');
INSERT INTO Spec VALUES(5,'Diagnostyka laboratoryjna');
INSERT INTO Spec VALUES(6,'Epidemiologia');
INSERT INTO Spec VALUES(7,'Genetyka kliniczna');
INSERT INTO Spec VALUES(8,'Kardiologia');
INSERT INTO Spec VALUES(9,'Mikrobiologia lekarska');
INSERT INTO Spec VALUES(10,'Neonatologia');
INSERT INTO Spec VALUES(11,'Neurologia');
INSERT INTO Spec VALUES(12,'Pediatria');
INSERT INTO Spec VALUES(13,'Psychiatria');
INSERT INTO Spec VALUES(14,'Psychiatria dzieci i m?odzie?y');
INSERT INTO Spec VALUES(15,'Urologia');

--seq--------------------------------------------------------


DROP SEQUENCE lekarzy_sec;
DROP SEQUENCE Lek_spec_sec;
DROP SEQUENCE bad_sec;
DROP SEQUENCE chor_sec;
DROP SEQUENCE lekarstwa_sec;
DROP SEQUENCE pac_sec;
DROP SEQUENCE karta_sec;
DROP SEQUENCE sala_sec;
DROP SEQUENCE termin_sec;

CREATE SEQUENCE lekarzy_sec;
CREATE SEQUENCE Lek_spec_sec;
CREATE SEQUENCE bad_sec;
CREATE SEQUENCE chor_sec;
CREATE SEQUENCE lekarstwa_sec;
CREATE SEQUENCE pac_sec;
CREATE SEQUENCE karta_sec;
CREATE SEQUENCE sala_sec;
CREATE SEQUENCE termin_sec;




--add badania
CREATE OR REPLACE PROCEDURE add_badania (bad Badania.Badanie%TYPE)
AS
BEGIN
INSERT INTO Badania VALUES( bad_sec.NEXTVAL,bad);
END;
/

EXECUTE add_badania('Morfologia krwi');
EXECUTE add_badania('Retikulocyty');
EXECUTE add_badania('Fibrynogen');
EXECUTE add_badania('Badanie ogólne moczu');
EXECUTE add_badania('Białko w moczu z dobowej zbiórki');
EXECUTE add_badania('Badanie kału na resztki pokarmowe');
EXECUTE add_badania('Test na obecność lamblii');
EXECUTE add_badania('Narkotyki w moczu');
EXECUTE add_badania('Grupa krwi');
EXECUTE add_badania('Test do wykrywania mononukleozy (wirus EVB)');
EXECUTE add_badania('Badanie bakteriologiczne (wszystkie materiały)');
EXECUTE add_badania('Badanie jałowości Sporal S lub A');
EXECUTE add_badania('Kwas moczowy');
EXECUTE add_badania('Alkohol we krwi');
EXECUTE add_badania('Glukoza');

--add chorobe
CREATE OR REPLACE PROCEDURE add_choroba (chor Choroby.Choroba%TYPE)
AS
BEGIN
INSERT INTO Choroby  VALUES( chor_sec.NEXTVAL,chor);
END;
/

EXECUTE add_choroba('awd');


--add lek
CREATE OR REPLACE PROCEDURE add_lekarstwa (lekarstwo Lekarstwa.Lekarstwo%TYPE)
AS
BEGIN
INSERT INTO Lekarstwa  VALUES( lekarstwa_sec.NEXTVAL,lekarstwo);
END;
/
--add sale
CREATE OR REPLACE PROCEDURE add_sale (sala Sale.Numer_s%TYPE, widz  Sale.Widzial%TYPE )
AS
BEGIN
INSERT INTO Sale  VALUES( sala_sec.NEXTVAL,sala, widz );
END;
/

EXECUTE add_sale(10,'A');
EXECUTE add_sale(12,'A');
EXECUTE add_sale(13,'A');
EXECUTE add_sale(10,'B');

EXECUTE add_lekarstwa('qwe');

CREATE OR REPLACE PROCEDURE add_lekarza (imie Lekarze.Imie%TYPE,nazw Lekarze.Nazwisko%TYPE,
miasto Lekarze.Miasto%TYPE,street Lekarze.Ulica%TYPE, House Lekarze.DOM%TYPE, pesel Lekarze.Pesel%TYPE,
special NUMBER)
AS
BEGIN
INSERT INTO Lekarze VALUES(lekarzy_sec.NEXTVAL,imie,nazw,miasto,street,House,pesel);
INSERT INTO Lek_spec VALUES(lekarzy_sec.currval,special);
END;
/


EXECUTE   DBMS_SCHEDULER.DROP_JOB('data_job',TRUE);
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'data_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'UPDATE x SET d = SYSDATE;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Time checl'
  );
END;
/

CREATE OR REPLACE PROCEDURE add_pacjent (imie Pacjenty.Imie%TYPE,nazw Pacjenty.Nazwisko%TYPE, wiek Pacjenty.Wiek%TYPE,gen Pacjenty.Gender%TYPE,
miasto Pacjenty.Miasto%TYPE,street Pacjenty.Ulica%TYPE, House Pacjenty.DOM%TYPE, pesel Pacjenty.Pesel%TYPE,
choroba NUMBER,bad NUMBER,objawy STRING)
AS
BEGIN
INSERT INTO Pacjenty VALUES(pac_sec.NEXTVAL,imie,nazw,wiek,gen,miasto,street,House,pesel);
INSERT INTO Karta_Pacjenta VALUES(karta_sec.NEXTVAL,pac_sec.currval,bad,choroba,choroba,objawy);
END;
/
--add termin
CREATE OR REPLACE PROCEDURE add_termin(lekarz NUMBER,karta NUMBER, pacj NUMBER,  sala NUMBER,dat VARCHAR)
AS
BEGIN
INSERT INTO Termin_wizytu VALUES(termin_sec.NEXTVAL,lekarz,karta,pacj,sala,to_date(dat,'yyyy/mm/dd:hh:mi:ssam'));
END;
/
EXECUTE add_pacjent ('MISHA','Chur',41,'Male','Kielce','1000lecia','17a','13239612412',1,4,'SOPLi');

EXECUTE add_lekarza ('Bogdan','Moskin','Kielce','1000lecia','17a','13234612412',6);

EXECUTE add_termin(1,1,1,2,'2017/05/31:12:00:00AM');

CREATE OR REPLACE PROCEDURE add_lekaRZ_scec(lekarz NUMBER,spec NUMBER)
AS
BEGIN
INSERT INTO Lek_spec VALUES(lekarz,spec);
END;
/
CREATE OR REPLACE PROCEDURE add_kart_onPac(pac NUMBER,choroba NUMBER,bad NUMBER,o STRING)
AS
BEGIN
INSERT INTO Karta_Pacjenta VALUES(karta_sec.NEXTVAL,pac,bad,choroba,choroba,o);
END;
/

EXECUTE add_kart_onPac(1,1,6,'th');

EXECUTE add_lekaRZ_scec(1,4);
EXECUTE add_lekaRZ_scec(1,7);

CREATE OR REPLACE VIEW Lekarze_view AS SELECT O.Imie,O.Nazwisko,O.Miasto,O.Ulica,O.Dom,O.Pesel, S.Specj
FROM Lekarze O
JOIN Lek_spec L
ON O.Id_lekarza = L.Id_lekarza
JOIN Spec S
ON S.Id_spec = L.Id_spec;

CREATE OR REPLACE VIEW Pac_view AS SELECT
p.Imie, p.Nazwisko, p.Wiek, p.Gender, p.Miasto, p.Ulica,p.Dom,
p.Pesel
FROM Pacjenty P;

CREATE OR REPLACE VIEW Pac_c_view AS SELECT
p.Id_pacjenta,p.Imie, p.Nazwisko,B.BADANIE, C.CHOROBA,L.LEKARSTWO
FROM Pacjenty P
JOIN Karta_Pacjenta K
ON P.Id_pacjenta = K.Id_pacjenta
JOIN Badania B
ON B.ID_BADANIA = K.ID_BADANIA
JOIN Choroby C
ON C.ID_CHOROBY = K.ID_CHOROBY
JOIN Lekarstwa L
ON L.ID_LEKARSTW = K.ID_LEKARSTW;



CREATE OR REPLACE TRIGGER dataTr AFTER UPDATE ON x
DECLARE
CURSOR XB IS SELECT * FROM x;
CURSOR q IS SELECT * FROM Termin_wizytu;
tmp XB%ROWTYPE;
BEGIN
OPEN q;
OPEN XB;
FETCH XB INTO tmp;
FOR w IN q Loop
IF w.data_t = tmp.b THEN
  DELETE FROM Termin_wizytu WHERE data_t = tmp.b;
END IF;

END LOOP;
END dataTr;
/


SELECT * FROM Lekarze_view;
SELECT * FROM Pac_view;
SELECT * FROM Pac_c_view;


------------------------------------------------------------------------
INSERT INTO Choroby VALUES(1,'Angina');
INSERT INTO Choroby VALUES(2,'Borelioza z Lyme');
INSERT INTO Choroby VALUES(3,'Chlamydioza');
INSERT INTO Choroby VALUES(4,'Choroba Huntingtona');
INSERT INTO Choroby VALUES(5,'Choroba Parkinsona');
INSERT INTO Choroby VALUES(6,'Cukrzyca typu 1');
INSERT INTO Choroby VALUES(7,'Depresja');
INSERT INTO Choroby VALUES(8,'Eozynofilie płucne');
INSERT INTO Choroby VALUES(9,'Fibromialgia');
INSERT INTO Choroby VALUES(10,'Gorączka trzydniowa');
INSERT INTO Choroby VALUES(11,'Gruźlica przełyku');
INSERT INTO Choroby VALUES(12,'Grypa');
INSERT INTO Choroby VALUES(13,'Grzybica skóry');
INSERT INTO Choroby VALUES(14,'Hemochromatoza');
INSERT INTO Choroby VALUES(15,'Hemoroidy');
INSERT INTO Choroby VALUES(16,'Jaskra');
INSERT INTO Choroby VALUES(17,'Kamica nerkowa');
INSERT INTO Choroby VALUES(18,'Łuszczyca');
INSERT INTO Choroby VALUES(19,'Migrena');
INSERT INTO Choroby VALUES(20,'Odra');

-----------------------------------------------------------------------------
INSERT INTO Lekarstwa VALUES(1,'Acatar');
INSERT INTO Lekarstwa VALUES(2,'Acodin');
INSERT INTO Lekarstwa VALUES(3,'Bactrim');
INSERT INTO Lekarstwa VALUES(4,'Baikaderm');
INSERT INTO Lekarstwa VALUES(5,'Calcipiryna');
INSERT INTO Lekarstwa VALUES(6,'Dasselta');
INSERT INTO Lekarstwa VALUES(7,'Egistrozol');
INSERT INTO Lekarstwa VALUES(8,'Etadron');
INSERT INTO Lekarstwa VALUES(9,'Forcid');
INSERT INTO Lekarstwa VALUES(10,'Hydrochlorothiazidum');
INSERT INTO Lekarstwa VALUES(11,'Montespir');
INSERT INTO Lekarstwa VALUES(12,'Morfeo');
INSERT INTO Lekarstwa VALUES(13,'Padolten');
INSERT INTO Lekarstwa VALUES(14,'Palin');
INSERT INTO Lekarstwa VALUES(15,'Rhinophenazol');
INSERT INTO Lekarstwa VALUES(16,'Rhodiola');
INSERT INTO Lekarstwa VALUES(17,'Talion');
INSERT INTO Lekarstwa VALUES(18,'Talvosilen');
INSERT INTO Lekarstwa VALUES(19,'Zavedos');
INSERT INTO Lekarstwa VALUES(20,'Zomig');