bazy danych ORACLE - objektowy sql procedury widoki

CREATE OR REPLACE TYPE typ_adresu AS OBJECT( ulica VARCHAR2(50), miasto VARCHAR2(50), kraj VARCHAR2(25), kod_pocztowy VARCHAR2(6) ); CREATE TYPE typ_listy AS VARRAY(10) OF VARCHAR2(20); CREATE TYPE typ_pracownika AS OBJECT( pracownik_id INTEGER, nazwisko VARCHAR2(50), imie VARCHAR2(50), adres typ_adresu, lista_doswiadczenia typ_listy, ORDER MEMBER FUNCTION -- metoda porównywania dwóch klientów kol_pracownika (x IN typ_pracownika) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES( kol_pracownika, WNDS, WNPS, RNPS, RNDS) ); CREATE OR REPLACE TYPE BODY typ_pracownika AS ORDER MEMBER FUNCTION kol_pracownika (x IN typ_pracownika) RETURN INTEGER IS BEGIN -- porównywanie klientów przez te wartość RETURN pracownik_id - x.pracownik_id; END; -- <0 oznacza SELF < x, =0 SELF = x, >0 oznacza SELF > x END; CREATE TYPE typ_projektow AS OBJECT ( projekt_id INTEGER, opis VARCHAR2(10), cena NUMBER, podatek NUMBER ); CREATE TYPE typ_pozycji AS OBJECT( pozycja_id INTEGER, ref_projektu Ref typ_projektow, -- referencja do projektu ilosc NUMBER); CREATE OR REPLACE TYPE lista_pozycji AS TABLE OF typ_pozycji;

CREATE or replace TYPE typ_zlecen AS OBJECT( zlecenie_id INTEGER, pracownik REF typ_pracownika, data_zlecenia DATE,

pozycje_zlecenia lista_pozycji, MAP MEMBER FUNCTION numer_zlecenia RETURN NUMBER , -- numer do porównań obiektów zamówień PRAGMA RESTRICT_REFERENCES( numer_zlecenia, WNDS, WNPS, RNPS, RNDS), -- bez zapisow i odczytow bazy danych MEMBER FUNCTION Suma RETURN NUMBER, -- suma wartosci pozycji PRAGMA RESTRICT_REFERENCES( Suma, WNDS, WNPS) -- metoda nie zapisuje do bazy danych, ale dokonuje odczytu ); CREATE OR REPLACE TYPE BODY typ_zlecen AS MEMBER FUNCTION Suma RETURN NUMBER IS i INTEGER; projekt typ_projektow; pozycja typ_pozycji; sumuj NUMBER := 0; cena NUMBER; BEGIN FOR i IN 1..SELF.pozycje_zlecenia.COUNT LOOP pozycja := SELF.pozycje_zlecenia(i); SELECT DEREF(pozycja.ref_projektu) INTO projekt FROM DUAL ; sumuj := sumuj + pozycja.ilosc * projekt.cena ; END LOOP; RETURN sumuj; END; MAP MEMBER FUNCTION numer_zlecenia RETURN NUMBER IS BEGIN -- podstawa porównań obiektów zamówień RETURN zlecenie_id; END; END; -- tworzenie tabel CREATE TABLE pracownik OF typ_pracownika (pracownik_id PRIMARY KEY); CREATE TABLE projekt OF typ_projektow (projekt_id PRIMARY KEY) ; CREATE TABLE zlecenia OF typ_zlecen ( zlecenie_id PRIMARY KEY, SCOPE FOR (pracownik) IS pracownik -- wartości w kolumnie Klient ograniczone do obiektów w tabeli Klienci )

NESTED TABLE pozycje_zlecenia STORE AS pozycje;

-- zmiana tabel ALTER TABLE pozycje ADD (SCOPE FOR (ref_projektu) IS projekt) ; CREATE UNIQUE INDEX unique_pozycje ON pozycje (NESTED_TABLE_ID, pozycja_id) ;

INSERT INTO projekt VALUES(1004,'Stream',2.00, 23); INSERT INTO projekt VALUES(1011, 'Traffic', 4.50, 23); INSERT INTO projekt VALUES(1534, 'Audience', 4234.00, 19); INSERT INTO projekt VALUES(1535, 'Prism', 5456.23, 19); INSERT INTO pracownik VALUES ( 1, 'Kowalski' , 'Jan', typ_adresu('Kaczki dziwaczki', 'Łomianki', 'Polska', '95054'), typ_listy('7652345') ); INSERT INTO pracownik VALUES ( 2, 'Braniewski' , 'Marcin', typ_adresu('Kaczora Donalda', 'Plonsk', 'Polska', '15054'), typ_listy('322345') );

INSERT INTO zlecenia SELECT 1001, REF(p), SYSDATE, lista_pozycji() FROM pracownik p WHERE p.pracownik_id= 1 ;

INSERT INTO THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 1001 ) SELECT 2001, REF(p), 12 FROM projekt p WHERE p.projekt_id = 1534;

INSERT INTO zlecenia SELECT 2001, REF(p), SYSDATE, lista_pozycji() FROM pracownik p WHERE p.pracownik_id= 2 ; INSERT INTO THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 2001 ) SELECT 02, REF(p), 12 FROM projekt p WHERE p.projekt_id = 1004;

INSERT INTO THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 2001 ) VALUES( Typ_pozycji(11, NULL, 2) ) ;

UPDATE THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 2001 ) tab -- Uzycie aliasu do wyniku spłaszczonego zapytania SET tab.ref_projektu = (SELECT REF(t) FROM projekt t WHERE t.projekt_id = 1011 ) WHERE tab.pozycja_id = 11 ;

INSERT INTO zlecenia SELECT 1, REF(p), Sysdate, lista_pozycji() FROM pracownik p WHERE pracownik_id = 1;

INSERT INTO THE(SELECT pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 1)

SELECT 1, REF(p), 2 FROM projekt p WHERE projekt_id = 1011;

INSERT INTO pracownik VALUES ( 3, 'dfgsdfgsg' , 'Jan', typ_adresu('Kaczki dziwaczki', 'Łomianki', 'Polska', '95054'), typ_listy('7652345') );

DECLARE ref_pracownika REF typ_pracownika; ref_projektu REF typ_projektow; BEGIN SELECT REF(k) INTO ref_pracownika FROM pracownik k WHERE k.Nazwisko = 'dfgsdfgsg'; SELECT REF(p) INTO ref_projektu FROM projekt p WHERE p.opis = 'Prism'; INSERT INTO zlecenia VALUES (typ_zlecen(3, ref_pracownika, Sysdate, lista_pozycji(typ_pozycji(1,ref_projektu,20)))); END; DELETE FROM zlecenia WHERE zlecenie_id = 1001 ;

SELECT z.zlecenie_id, z.pracownik.nazwisko, z.pracownik.imie, z.pracownik.adres.miasto FROM zlecenia z;

SELECT p.pozycja_id, p. ref_projektu.opis, p.ilosc FROM THE(SELECT pozycje_zlecenia FROM zlecenia WHERE zlecenie_id = 2001) p;

SELECT VALUE(z) FROM zlecenia z;

SELECT DEREF(z.pracownik), -- cała informacja o kliencie z.data_zlecenia, z.zlecenie_id, -- informacja o zamowieniu

z.pozycje_zlecenia -- informacja o pozycjach zamowienia FROM zlecenia z WHERE z.zlecenie_id = 2001 ;

SELECT z.zlecenie_id, z.Suma() FROM zlecenia z; SELECT z.pracownik.nazwisko, z.zlecenie_id, tab.pozycja_id, tab.ref_projektu.opis, tab.ilosc FROM THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 2001 ) tab, zlecenia z WHERE z.zlecenie_id= 2001;

SELECT z.pracownik.imie, z.zlecenie_id, tab.pozycja_id, tab.ref_projektu.opis, tab.ilosc FROM THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 2001 ) tab, zlecenia z WHERE z.zlecenie_id= 2001 UNION SELECT z.pracownik.imie, z.zlecenie_id, tab.pozycja_id, tab.ref_projektu.opis, tab.ilosc FROM THE ( SELECT z.pozycje_zlecenia FROM zlecenia z WHERE z.zlecenie_id = 1 ) tab, zlecenia z WHERE z.zlecenie_id= 1;

CREATE TABLE Pomo(nazwisko VARCHAR2(50), zlecenie_id INTEGER, pozycja_id INTEGER, opis VARCHAR2(10));

CREATE OR REPLACE PROCEDURE oblicz AS z zlecenia%ROWTYPE; k typ_pracownika;

Tab lista_pozycji; pozycja typ_pozycji; projekt typ_projektow; BEGIN DELETE Pomo; FOR z IN (SELECT * FROM zlecenia) LOOP SELECT DEREF(z.pracownik) INTO k FROM DUAL; Tab := z.pozycje_zlecenia; FOR i IN 1..Tab.COUNT LOOP pozycja :=tab(i); SELECT DEREF(pozycja.ref_projektu) INTO projekt FROM DUAL; INSERT INTO Pomo VALUES (k.nazwisko,z.zlecenie_id, tab(i).pozycja_id, projekt.opis); END LOOP; END LOOP; END; SELECT * FROM Pomo; CREATE OR REPLACE TYPE typ_zlecen_pracownikow AS OBJECT( licznik_zlecen INTEGER, kraj VARCHAR2(50) );

CREATE VIEW zlecenia_pracownikow OF typ_zlecen_pracownikow WITH OBJECT OID (kraj) AS SELECT COUNT(z.zlecenie_id), z.pracownik.adres.kraj FROM zlecenia z GROUP BY z.pracownik.adres.kraj;