daily pastebin goal
58%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
  2.  
  3. --SHOW ERRORS;
  4. DROP TABLE Plebs CASCADE CONSTRAINTS;
  5. DROP TABLE Elita CASCADE CONSTRAINTS;
  6. DROP TABLE Konta CASCADE CONSTRAINTS;
  7.  
  8. DROP TYPE KOT_T FORCE;
  9. DROP TYPE PLEBS_T FORCE;
  10. DROP TYPE ELITA_T FORCE;
  11. DROP TYPE KONTO_T FORCE;
  12. DROP TYPE INCYDENT_T FORCE;
  13.  
  14. --===================================================TWORZENIE TABEL=========================================================
  15.  
  16.  
  17.  
  18. CREATE TABLE Plebs (
  19.     pseudo VARCHAR2(15) CONSTRAINT plebs_pseudo_pk PRIMARY KEY,
  20.     CONSTRAINT plebs_pseudo_fk FOREIGN KEY(pseudo) REFERENCES Kocury(pseudo)
  21. );
  22.  
  23. CREATE TABLE Elita (
  24.     pseudo VARCHAR2(15) CONSTRAINT elita_psuedo_pk PRIMARY KEY,
  25.     plebs_pseudo VARCHAR2(15) CONSTRAINT elita_plebs_pseudo_fk REFERENCES Plebs(pseudo),
  26.     CONSTRAINT elita_pseudo_fk FOREIGN KEY(pseudo) REFERENCES Kocury(pseudo)
  27. );
  28.  
  29. DROP SEQUENCE seq_plebs_id;
  30. CREATE SEQUENCE seq_plebs_id;
  31.  
  32. CREATE TABLE Konta (
  33.     id NUMBER(10) DEFAULT seq_plebs_id.NEXTVAL CONSTRAINT konta_id_pk PRIMARY KEY,
  34.     pseudo VARCHAR2(15) CONSTRAINT konta_pseudo_fk REFERENCES Elita(pseudo),
  35.     data_wprowadzenia DATE DEFAULT SYSDATE CONSTRAINT konta_data_wpr_nn NOT NULL,
  36.     data_usuniecia DATE,
  37.     CONSTRAINT konta_pseudo_nn CHECK(pseudo IS NOT NULL)
  38. );
  39.  
  40. --===================================================INSERTY=========================================================
  41. --======================PLEBS==========================
  42. INSERT INTO Plebs VALUES ('LOLA');
  43. INSERT INTO Plebs VALUES ('ZOMBI');
  44. INSERT INTO Plebs VALUES ('MALA');
  45. INSERT INTO Plebs VALUES ('PLACEK');
  46. INSERT INTO Plebs VALUES ('RURA');
  47. INSERT INTO Plebs VALUES ('SZYBKA');
  48. INSERT INTO Plebs VALUES ('LASKA');
  49. INSERT INTO Plebs VALUES ('UCHO');
  50. INSERT INTO Plebs VALUES ('MALY');
  51. INSERT INTO Plebs VALUES ('MAN');
  52. INSERT INTO Plebs VALUES ('DAMA');
  53. INSERT INTO Plebs VALUES ('PUSZYSTA');
  54. INSERT INTO Plebs VALUES ('ZERO');
  55. COMMIT;
  56.  
  57. --======================ELITA==========================
  58. INSERT INTO Elita VALUES ('TYGRYS', 'LOLA');
  59. INSERT INTO Elita VALUES ('LYSY', NULL);
  60. INSERT INTO Elita VALUES ('RAFA', 'MAN');
  61. INSERT INTO Elita VALUES ('ZOMBI', 'PUSZYSTA');
  62. INSERT INTO Elita VALUES ('KURKA', 'ZERO');
  63. COMMIT;
  64.  
  65. --======================KONTA==========================
  66. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2007-12-05', '2009-03-25');
  67. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2011-02-01', '2012-02-01');
  68. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2017-07-29', NULL);
  69. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2017-07-29', NULL);
  70. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2017-07-29', NULL);
  71. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2018-09-03', '2018-09-04');
  72. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2019-01-17', '2019-01-18');
  73. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2003-04-01', '2004-03-01');
  74. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2004-05-12', '2007-11-13');
  75. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('TYGRYS', '2005-11-02', '2005-11-03');
  76. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('LYSY', '2006-08-16', '2011-03-16');
  77. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('LYSY', '2006-08-16', '2012-08-13');
  78. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('LYSY', '2008-08-15', NULL);
  79. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('LYSY', '2009-08-15', NULL);
  80. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('LYSY', '2012-08-15', '2013-08-16');
  81. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2008-10-15', NULL);
  82. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2009-10-15', NULL);
  83. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2016-12-24', '2017-12-24');
  84. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2017-12-24', '2018-12-24');
  85. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2018-12-24', NULL);
  86. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('RAFA', '2019-01-01', '2019-01-06');
  87. INSERT INTO Konta (pseudo, data_wprowadzenia, data_usuniecia) VALUES ('ZOMBI', '2007-03-16', '2018-03-18');
  88. COMMIT;
  89.  
  90. --===================================================WIDOKI=========================================================
  91. --kocury, plebs, elita, konta, wrogowie_kocurow
  92.  
  93. --======================KOCURY==========================
  94. CREATE OR REPLACE TYPE KOT_W AS OBJECT (
  95.     imie VARCHAR2(15),
  96.     plec VARCHAR2(1),
  97.     pseudo VARCHAR2(15),
  98.     funkcja VARCHAR2(10),
  99.     szef REF KOT_W,
  100.     w_stadku_od DATE,
  101.     przydzial_myszy NUMBER(3),
  102.     myszy_extra NUMBER(3),
  103.     nr_bandy NUMBER(2),
  104.    
  105.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2,
  106.     MEMBER FUNCTION Przydzial_calk RETURN NUMBER,
  107.     MEMBER FUNCTION Staz RETURN NUMBER,
  108.     MEMBER FUNCTION getSzef RETURN KOT_W,
  109.     PRAGMA RESTRICT_REFERENCES(DEFAULT,RNDS,WNDS,RNPS,WNPS),
  110.     PRAGMA RESTRICT_REFERENCES(Staz,WNDS,RNPS,WNPS),
  111.     PRAGMA RESTRICT_REFERENCES(getSzef,WNDS,RNPS,WNPS)
  112. );
  113.  
  114. CREATE OR REPLACE TYPE BODY KOT_W AS
  115.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2 IS
  116.     BEGIN
  117.         RETURN pseudo;
  118.     END;
  119.  
  120.     MEMBER FUNCTION Przydzial_calk RETURN NUMBER IS
  121.     BEGIN
  122.         RETURN NVL(przydzial_myszy, 0) + NVL(myszy_extra, 0);
  123.     END Przydzial_calk;
  124.    
  125.     MEMBER FUNCTION Staz RETURN NUMBER IS
  126.     BEGIN
  127.         RETURN SYSDATE - self.w_stadku_od;
  128.     END Staz;
  129.    
  130.     MEMBER FUNCTION getSzef RETURN KOT_W IS
  131.     szefO KOT_W;
  132.     BEGIN
  133.         SELECT DEREF(self.szef) INTO szefO FROM DUAL;
  134.         RETURN szefO;
  135.     END getSzef;
  136. END;
  137.  
  138.  
  139. CREATE OR REPLACE FORCE VIEW Kocury_OID OF KOT_W
  140. WITH OBJECT IDENTIFIER (pseudo) AS
  141. SELECT imie, plec, pseudo, funkcja, MAKE_REF(Kocury_OID, szef) szef, w_stadku_od, przydzial_myszy, myszy_extra, nr_bandy
  142. FROM Kocury;
  143.  
  144. --======================PLEBS==========================
  145. CREATE OR REPLACE TYPE PLEBS_W AS OBJECT (
  146.     pseudo VARCHAR2(15),
  147.     kot REF KOT_W,
  148.    
  149.     MEMBER FUNCTION getKot RETURN KOT_W,
  150.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2,
  151.     PRAGMA RESTRICT_REFERENCES(DEFAULT,WNDS,RNPS,WNPS)
  152. );
  153.  
  154. CREATE OR REPLACE TYPE BODY PLEBS_W AS
  155.     MEMBER FUNCTION getKot RETURN KOT_W IS
  156.     kotO KOT_W;
  157.     BEGIN
  158.         SELECT DEREF(self.kot) INTO kotO FROM DUAL;
  159.         RETURN kotO;
  160.     END getKot;
  161.    
  162.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2 IS
  163.     BEGIN
  164.         RETURN getKot().pseudo;
  165.     END;
  166. END;
  167.  
  168. CREATE OR REPLACE FORCE VIEW Plebs_OID OF PLEBS_W
  169. WITH OBJECT IDENTIFIER (pseudo) AS
  170. SELECT pseudo, MAKE_REF(Kocury_OID, pseudo) kot
  171. FROM Plebs;
  172.  
  173. --======================ELITA==========================
  174.  
  175. CREATE OR REPLACE TYPE ELITA_W AS OBJECT (
  176.     pseudo VARCHAR(2),
  177.     kot REF KOT_W,
  178.     sluga REF PLEBS_W,
  179.    
  180.     MEMBER FUNCTION getKot RETURN KOT_W,
  181.     MEMBER FUNCTION getSluga RETURN PLEBS_W,
  182.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2,
  183.     PRAGMA RESTRICT_REFERENCES(DEFAULT,WNDS,RNPS,WNPS)
  184. );
  185.  
  186. CREATE OR REPLACE TYPE BODY ELITA_W AS
  187.     MEMBER FUNCTION getKot RETURN KOT_W IS
  188.     kotO KOT_W;
  189.     BEGIN
  190.         SELECT DEREF(self.kot) INTO kotO FROM DUAL;
  191.         RETURN kotO;
  192.     END getKot;
  193.    
  194.     MEMBER FUNCTION getSluga RETURN PLEBS_W IS
  195.     slugaO PLEBS_W;
  196.     BEGIN
  197.         SELECT DEREF(self.sluga) INTO slugaO FROM DUAL;
  198.         RETURN slugaO;
  199.     END getSluga;
  200.    
  201.     MAP MEMBER FUNCTION Porownaj RETURN VARCHAR2 IS
  202.     BEGIN
  203.         RETURN getKot().pseudo;
  204.     END;
  205. END;
  206.  
  207. CREATE OR REPLACE VIEW Elita_OID OF ELITA_W
  208. WITH OBJECT IDENTIFIER (pseudo) AS
  209. SELECT pseudo, MAKE_REF(Kocury_OID, pseudo), MAKE_REF(Plebs_OID, plebs_pseudo)
  210. FROM Elita;
  211.  
  212. SELECT * FROM Elita_OID;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top