· 10 years ago · Apr 05, 2016, 12:39 AM
1set serveroutput on;
2
3DELETE FROM ORDERS_PRODUCTS;
4DELETE FROM ORDERS_STATUS;
5DELETE FROM ORDERS;
6DELETE FROM CUSTOMERS;
7DELETE FROM PRODUCTS;
8DELETE FROM WAREHOUSE;
9DELETE FROM EMAILS_QUEUE;
10
11DECLARE
12 order_id NUMBER(2);
13BEGIN
14-- DODAJ KLIENTOW
15 add_customer('Owner','Owner','Owner@owner.com','owner');
16 add_customer('Andrzej','Kowalski','akowalski@gmail.com','andrzejsSecretPass');
17 add_customer('Mariusz','Andrzejewski','mandzrejowski@gmail.com','haslo');
18 add_customer('Agata','Suchecka','asuchecka@gmail.com','innehaslo');
19 add_customer('Janusz','Jankowski','jjankowski@gmail.com','cgfdgfdsgdf');
20 add_customer('Stefan','Zak','szak@gmail.com','somePass');
21 add_customer('Ula','Nowakowska','unowakowska@gmail.com','nowakowskaPass');
22
23 DBMS_output.put_line('');
24
25 add_warehouse('dabrowskiego#1','dabrowskiego 255 Lodz');
26 add_warehouse('dabrowskiego#2','dabrowskiego 205 Lodz');
27 add_warehouse('lodowa#1','lodowa 3 Lodz');
28
29 DBMS_output.put_line('');
30
31 add_product('koszulka',1,1);
32 add_product('koszulka',2,2);
33 add_product('koszulka',1,3);
34 add_product('koszulka',2,1);
35 add_product('koszulka',1,2);
36 add_product('bluza',7,1);
37 add_product('bluza',15,2);
38 add_product('bluza',5,2);
39 add_product('bluza',13,3);
40 add_product('bluza',21,2);
41 add_product('bluza',14,3);
42 add_product('spodnie',21,3);
43 add_product('spodnie',12,1);
44 add_product('spodnie',11,2);
45 add_product('buty',28,3);
46 add_product('buty',34,2);
47 add_product('rekawiczki',1,1);
48 add_product('rekawiczki',1,2);
49
50 DBMS_output.put_line('');
51
52 IF (ARE_CREDENTIALS_CORRECT('akowalski@gmail.com','andrzejsSecretPass') = 1) THEN
53 DBMS_output.put_line('andrzejsSecretPass to poprawne haslo uzytkownika akowalski@gmail.com');
54 END IF;
55 IF (ARE_CREDENTIALS_CORRECT('mandzrejowski@gmail.com','zlehaslo') = 0) THEN
56 DBMS_output.put_line('zlehaslo to niepoprawne haslo uzytkownika mandzrejowski@gmail.com');
57 END IF;
58
59 DBMS_output.put_line('');
60
61 add_order(3,order_id,2);
62 add_product_to_order(order_id,1);
63 add_product_to_order(order_id,1);
64 add_product_to_order(order_id,3);
65 add_product_to_order(order_id,2);
66
67 DBMS_output.put_line('');
68ENd;
69
70
71--BEGIN
72--dbms_scheduler.stop_job('CHECK_PRODUCTS_JOB');
73--dbms_scheduler.run_job('CHECK_PRODUCTS_JOB',TRUE);
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89--------------------------------------------------------
90-- File created - Monday-April-04-2016
91--------------------------------------------------------
92--------------------------------------------------------
93-- DDL for Table CUSTOMERS
94--------------------------------------------------------
95
96 CREATE TABLE "RMSBD"."CUSTOMERS"
97 ( "ID" VARCHAR2(4000 BYTE),
98 "NAME" VARCHAR2(4000 BYTE),
99 "SURNAME" VARCHAR2(4000 BYTE),
100 "EMAIL" VARCHAR2(4000 BYTE),
101 "PASSWORD" VARCHAR2(4000 BYTE)
102 ) SEGMENT CREATION IMMEDIATE
103 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
104 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
105 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
106 TABLESPACE "USERS" ;
107--------------------------------------------------------
108-- DDL for Table EMAILS_QUEUE
109--------------------------------------------------------
110
111 CREATE TABLE "RMSBD"."EMAILS_QUEUE"
112 ( "ID" NUMBER,
113 "HTML" VARCHAR2(800 BYTE),
114 "DESTINATION" VARCHAR2(70 BYTE)
115 ) SEGMENT CREATION IMMEDIATE
116 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
117 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
118 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
119 TABLESPACE "USERS" ;
120--------------------------------------------------------
121-- DDL for Table ORDERS
122--------------------------------------------------------
123
124 CREATE TABLE "RMSBD"."ORDERS"
125 ( "ID" NUMBER(6,0),
126 "CUSTOMER_ID" NUMBER(6,0),
127 "TYPE" NUMBER DEFAULT 2
128 ) SEGMENT CREATION IMMEDIATE
129 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
130 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
131 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
132 TABLESPACE "USERS" ;
133
134 COMMENT ON COLUMN "RMSBD"."ORDERS"."TYPE" IS '1- incoming 2-outcoming';
135--------------------------------------------------------
136-- DDL for Table ORDERS_PRODUCTS
137--------------------------------------------------------
138
139 CREATE TABLE "RMSBD"."ORDERS_PRODUCTS"
140 ( "ID" NUMBER,
141 "ORDER_ID" NUMBER,
142 "PRODUCT_ID" NUMBER
143 ) SEGMENT CREATION IMMEDIATE
144 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
145 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
146 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
147 TABLESPACE "USERS" ;
148--------------------------------------------------------
149-- DDL for Table ORDERS_STATUS
150--------------------------------------------------------
151
152 CREATE TABLE "RMSBD"."ORDERS_STATUS"
153 ( "ID" NUMBER,
154 "NAME" VARCHAR2(20 BYTE),
155 "ESTABLISH_TIME" DATE,
156 "ORDER_ID" NUMBER
157 ) SEGMENT CREATION IMMEDIATE
158 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
159 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
160 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
161 TABLESPACE "USERS" ;
162--------------------------------------------------------
163-- DDL for Table PRODUCTS
164--------------------------------------------------------
165
166 CREATE TABLE "RMSBD"."PRODUCTS"
167 ( "ID" NUMBER,
168 "NAME" VARCHAR2(20 BYTE),
169 "WEIGHT" NUMBER,
170 "WAREHOUSE_ID" NUMBER,
171 "PRICE" NUMBER
172 ) SEGMENT CREATION IMMEDIATE
173 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
174 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
175 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
176 TABLESPACE "USERS" ;
177--------------------------------------------------------
178-- DDL for Table WAREHOUSE
179--------------------------------------------------------
180
181 CREATE TABLE "RMSBD"."WAREHOUSE"
182 ( "ID" NUMBER(*,0),
183 "NAME" VARCHAR2(50 BYTE),
184 "ADRESS" VARCHAR2(50 BYTE)
185 ) SEGMENT CREATION IMMEDIATE
186 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
187 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
188 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
189 TABLESPACE "USERS" ;
190REM INSERTING into RMSBD.CUSTOMERS
191SET DEFINE OFF;
192Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('1','Owner','Owner','Owner@owner.com','579233B2C479241523CBA5E3AF55D0F50F2D6414');
193Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('2','Andrzej','Kowalski','akowalski@gmail.com','8BBDC4B5F61A5A705B3518DC040ADA37713403CC');
194Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('3','Mariusz','Andrzejewski','mandzrejowski@gmail.com','FF12BBD8C907AF067070211D87BDF098BE17375B');
195Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('4','Agata','Suchecka','asuchecka@gmail.com','7549B188CC4392E4FFD486E64E40EA2235CDED2F');
196Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('5','Janusz','Jankowski','jjankowski@gmail.com','D7E01E54E3E905B6AF66E65B4C1316658183C87E');
197Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('6','Stefan','Zak','szak@gmail.com','D209C5D1176C46535028011E82A5DE574065A32A');
198Insert into RMSBD.CUSTOMERS (ID,NAME,SURNAME,EMAIL,PASSWORD) values ('7','Ula','Nowakowska','unowakowska@gmail.com','D60CFC3338266031FC203582250A585FB4AF7809');
199REM INSERTING into RMSBD.EMAILS_QUEUE
200SET DEFINE OFF;
201Insert into RMSBD.EMAILS_QUEUE (ID,HTML,DESTINATION) values (4,'<html>czesc ! twoje zamowienie zostalo zlozone','Owner@owner.com');
202Insert into RMSBD.EMAILS_QUEUE (ID,HTML,DESTINATION) values (5,'<html>czesc ! twoje zamowienie zostalo zlozone','Owner@owner.com');
203Insert into RMSBD.EMAILS_QUEUE (ID,HTML,DESTINATION) values (2,'<html>czesc ! twoje zamowienie zostalo zlozone','Owner@owner.com');
204Insert into RMSBD.EMAILS_QUEUE (ID,HTML,DESTINATION) values (3,'<html>czesc ! twoje zamowienie zostalo zlozone','Owner@owner.com');
205Insert into RMSBD.EMAILS_QUEUE (ID,HTML,DESTINATION) values (1,'<html>czesc ! twoje zamowienie zostalo zlozone','mandzrejowski@gmail.com');
206REM INSERTING into RMSBD.ORDERS
207SET DEFINE OFF;
208Insert into RMSBD.ORDERS (ID,CUSTOMER_ID,TYPE) values (4,1,1);
209Insert into RMSBD.ORDERS (ID,CUSTOMER_ID,TYPE) values (5,1,1);
210Insert into RMSBD.ORDERS (ID,CUSTOMER_ID,TYPE) values (2,1,1);
211Insert into RMSBD.ORDERS (ID,CUSTOMER_ID,TYPE) values (3,1,1);
212Insert into RMSBD.ORDERS (ID,CUSTOMER_ID,TYPE) values (1,3,2);
213REM INSERTING into RMSBD.ORDERS_PRODUCTS
214SET DEFINE OFF;
215Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (9,3,2);
216Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (10,3,3);
217Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (11,4,1);
218Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (12,4,2);
219Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (13,4,3);
220Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (14,5,1);
221Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (15,5,2);
222Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (16,5,3);
223Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (5,2,1);
224Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (6,2,2);
225Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (7,2,3);
226Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (8,3,1);
227Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (1,1,1);
228Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (2,1,1);
229Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (3,1,3);
230Insert into RMSBD.ORDERS_PRODUCTS (ID,ORDER_ID,PRODUCT_ID) values (4,1,2);
231REM INSERTING into RMSBD.ORDERS_STATUS
232SET DEFINE OFF;
233Insert into RMSBD.ORDERS_STATUS (ID,NAME,ESTABLISH_TIME,ORDER_ID) values (4,'PREPARING',to_date('04-APR-16','DD-MON-RR'),4);
234Insert into RMSBD.ORDERS_STATUS (ID,NAME,ESTABLISH_TIME,ORDER_ID) values (5,'PREPARING',to_date('04-APR-16','DD-MON-RR'),5);
235Insert into RMSBD.ORDERS_STATUS (ID,NAME,ESTABLISH_TIME,ORDER_ID) values (2,'PREPARING',to_date('04-APR-16','DD-MON-RR'),2);
236Insert into RMSBD.ORDERS_STATUS (ID,NAME,ESTABLISH_TIME,ORDER_ID) values (3,'PREPARING',to_date('04-APR-16','DD-MON-RR'),3);
237Insert into RMSBD.ORDERS_STATUS (ID,NAME,ESTABLISH_TIME,ORDER_ID) values (1,'PREPARING',to_date('04-APR-16','DD-MON-RR'),1);
238REM INSERTING into RMSBD.PRODUCTS
239SET DEFINE OFF;
240Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (1,'koszulka',1,1,null);
241Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (2,'koszulka',2,2,null);
242Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (3,'koszulka',1,3,null);
243Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (4,'koszulka',2,1,null);
244Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (5,'koszulka',1,2,null);
245Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (6,'bluza',7,1,null);
246Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (7,'bluza',15,2,null);
247Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (8,'bluza',5,2,null);
248Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (9,'bluza',13,3,null);
249Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (10,'bluza',21,2,null);
250Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (11,'bluza',14,3,null);
251Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (12,'spodnie',21,3,null);
252Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (13,'spodnie',12,1,null);
253Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (14,'spodnie',11,2,null);
254Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (15,'buty',28,3,null);
255Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (16,'buty',34,2,null);
256Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (17,'rekawiczki',1,1,null);
257Insert into RMSBD.PRODUCTS (ID,NAME,WEIGHT,WAREHOUSE_ID,PRICE) values (18,'rekawiczki',1,2,null);
258REM INSERTING into RMSBD.WAREHOUSE
259SET DEFINE OFF;
260Insert into RMSBD.WAREHOUSE (ID,NAME,ADRESS) values (1,'dabrowskiego#1','dabrowskiego 255 Lodz');
261Insert into RMSBD.WAREHOUSE (ID,NAME,ADRESS) values (2,'dabrowskiego#2','dabrowskiego 205 Lodz');
262Insert into RMSBD.WAREHOUSE (ID,NAME,ADRESS) values (3,'lodowa#1','lodowa 3 Lodz');
263--------------------------------------------------------
264-- DDL for Index ORDERS_OUTCOMING_ID_UINDEX
265--------------------------------------------------------
266
267 CREATE UNIQUE INDEX "RMSBD"."ORDERS_OUTCOMING_ID_UINDEX" ON "RMSBD"."ORDERS" ("ID")
268 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
269 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
270 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
271 TABLESPACE "USERS" ;
272--------------------------------------------------------
273-- DDL for Index CUSTOMERS_ID_UINDEX
274--------------------------------------------------------
275
276 CREATE UNIQUE INDEX "RMSBD"."CUSTOMERS_ID_UINDEX" ON "RMSBD"."CUSTOMERS" ("ID")
277 PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
278 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
279 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
280 TABLESPACE "USERS" ;
281--------------------------------------------------------
282-- DDL for Index MAGAZINES_PK
283--------------------------------------------------------
284
285 CREATE UNIQUE INDEX "RMSBD"."MAGAZINES_PK" ON "RMSBD"."WAREHOUSE" ("ID")
286 PCTFREE 10 INITRANS 2 MAXTRANS 255
287 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
288 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
289 TABLESPACE "USERS" ;
290--------------------------------------------------------
291-- DDL for Index ORDERS_STATUS_PK
292--------------------------------------------------------
293
294 CREATE UNIQUE INDEX "RMSBD"."ORDERS_STATUS_PK" ON "RMSBD"."ORDERS_STATUS" ("ID")
295 PCTFREE 10 INITRANS 2 MAXTRANS 255
296 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
297 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
298 TABLESPACE "USERS" ;
299--------------------------------------------------------
300-- DDL for Index PRODUCT_PK
301--------------------------------------------------------
302
303 CREATE UNIQUE INDEX "RMSBD"."PRODUCT_PK" ON "RMSBD"."PRODUCTS" ("ID")
304 PCTFREE 10 INITRANS 2 MAXTRANS 255
305 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
306 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
307 TABLESPACE "USERS" ;
308--------------------------------------------------------
309-- DDL for Index ORDER_PRODUCTS_PK
310--------------------------------------------------------
311
312 CREATE UNIQUE INDEX "RMSBD"."ORDER_PRODUCTS_PK" ON "RMSBD"."ORDERS_PRODUCTS" ("ID")
313 PCTFREE 10 INITRANS 2 MAXTRANS 255
314 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
315 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
316 TABLESPACE "USERS" ;
317--------------------------------------------------------
318-- DDL for Index EMAILS_QUEUE_PK
319--------------------------------------------------------
320
321 CREATE UNIQUE INDEX "RMSBD"."EMAILS_QUEUE_PK" ON "RMSBD"."EMAILS_QUEUE" ("ID")
322 PCTFREE 10 INITRANS 2 MAXTRANS 255
323 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
324 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
325 TABLESPACE "USERS" ;
326--------------------------------------------------------
327-- Constraints for Table CUSTOMERS
328--------------------------------------------------------
329
330 ALTER TABLE "RMSBD"."CUSTOMERS" MODIFY ("SURNAME" NOT NULL ENABLE);
331 ALTER TABLE "RMSBD"."CUSTOMERS" MODIFY ("NAME" NOT NULL ENABLE);
332 ALTER TABLE "RMSBD"."CUSTOMERS" MODIFY ("ID" NOT NULL ENABLE);
333--------------------------------------------------------
334-- Constraints for Table PRODUCTS
335--------------------------------------------------------
336
337 ALTER TABLE "RMSBD"."PRODUCTS" ADD CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("ID")
338 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
339 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
340 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
341 TABLESPACE "USERS" ENABLE;
342 ALTER TABLE "RMSBD"."PRODUCTS" MODIFY ("NAME" NOT NULL ENABLE);
343 ALTER TABLE "RMSBD"."PRODUCTS" MODIFY ("ID" NOT NULL ENABLE);
344--------------------------------------------------------
345-- Constraints for Table ORDERS_STATUS
346--------------------------------------------------------
347
348 ALTER TABLE "RMSBD"."ORDERS_STATUS" ADD CONSTRAINT "ORDERS_STATUS_PK" PRIMARY KEY ("ID")
349 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
350 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
351 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
352 TABLESPACE "USERS" ENABLE;
353 ALTER TABLE "RMSBD"."ORDERS_STATUS" MODIFY ("ESTABLISH_TIME" NOT NULL ENABLE);
354 ALTER TABLE "RMSBD"."ORDERS_STATUS" MODIFY ("NAME" NOT NULL ENABLE);
355 ALTER TABLE "RMSBD"."ORDERS_STATUS" MODIFY ("ID" NOT NULL ENABLE);
356--------------------------------------------------------
357-- Constraints for Table ORDERS
358--------------------------------------------------------
359
360 ALTER TABLE "RMSBD"."ORDERS" ADD CONSTRAINT "TYPE_CONSTRAINT" CHECK (TYPE IN (1, 2)) ENABLE;
361 ALTER TABLE "RMSBD"."ORDERS" MODIFY ("CUSTOMER_ID" NOT NULL ENABLE);
362 ALTER TABLE "RMSBD"."ORDERS" MODIFY ("ID" NOT NULL ENABLE);
363--------------------------------------------------------
364-- Constraints for Table ORDERS_PRODUCTS
365--------------------------------------------------------
366
367 ALTER TABLE "RMSBD"."ORDERS_PRODUCTS" ADD CONSTRAINT "ORDER_PRODUCTS_PK" PRIMARY KEY ("ID")
368 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
369 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
370 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
371 TABLESPACE "USERS" ENABLE;
372 ALTER TABLE "RMSBD"."ORDERS_PRODUCTS" MODIFY ("PRODUCT_ID" NOT NULL ENABLE);
373 ALTER TABLE "RMSBD"."ORDERS_PRODUCTS" MODIFY ("ORDER_ID" NOT NULL ENABLE);
374 ALTER TABLE "RMSBD"."ORDERS_PRODUCTS" MODIFY ("ID" NOT NULL ENABLE);
375--------------------------------------------------------
376-- Constraints for Table EMAILS_QUEUE
377--------------------------------------------------------
378
379 ALTER TABLE "RMSBD"."EMAILS_QUEUE" ADD CONSTRAINT "EMAILS_QUEUE_PK" PRIMARY KEY ("ID")
380 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
381 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
382 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
383 TABLESPACE "USERS" ENABLE;
384 ALTER TABLE "RMSBD"."EMAILS_QUEUE" MODIFY ("ID" NOT NULL ENABLE);
385--------------------------------------------------------
386-- Constraints for Table WAREHOUSE
387--------------------------------------------------------
388
389 ALTER TABLE "RMSBD"."WAREHOUSE" ADD CONSTRAINT "MAGAZINES_PK" PRIMARY KEY ("ID")
390 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
391 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
392 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
393 TABLESPACE "USERS" ENABLE;
394 ALTER TABLE "RMSBD"."WAREHOUSE" MODIFY ("ID" NOT NULL ENABLE);
395--------------------------------------------------------
396-- DDL for Trigger NEW_ORDER_STATUS
397--------------------------------------------------------
398
399 CREATE OR REPLACE TRIGGER "RMSBD"."NEW_ORDER_STATUS"
400AFTER INSERT ON ORDERS
401FOR EACH ROW
402DECLARE
403auto_incremnted_id NUMBER(6);
404BEGIN
405 SELECT COUNT(*) + 1
406 INTO auto_incremnted_id FROM ORDERS_STATUS;
407 INSERT INTO orders_status (id,NAME,order_id) VALUES(auto_incremnted_id,'PREPARING',:new.ID);
408 DBMS_output.put_line('TRIGGER: dodano nowy status zamowienia (PREPARING) dla zamowienia ' || :new.ID);
409END;
410/
411ALTER TRIGGER "RMSBD"."NEW_ORDER_STATUS" ENABLE;
412--------------------------------------------------------
413-- DDL for Trigger SEND_CONFIRMATION_ORDER
414--------------------------------------------------------
415
416 CREATE OR REPLACE TRIGGER "RMSBD"."SEND_CONFIRMATION_ORDER"
417BEFORE INSERT ON ORDERS
418FOR EACH ROW
419DECLARE
420 user_email varchar2(25);
421BEGIN
422 Select CUSTOMERS.EMAIL INTO user_email FROM CUSTOMERS WHERE CUSTOMERS.ID = :new.customer_id;
423 SEND_EMAIL('<html>czesc ! twoje zamowienie zostalo zlozone', user_email);
424END;
425/
426ALTER TRIGGER "RMSBD"."SEND_CONFIRMATION_ORDER" ENABLE;
427--------------------------------------------------------
428-- DDL for Trigger UPDATE_STATUS_DATE
429--------------------------------------------------------
430
431 CREATE OR REPLACE TRIGGER "RMSBD"."UPDATE_STATUS_DATE"
432BEFORE INSERT OR UPDATE
433ON ORDERS_STATUS
434FOR EACH ROW
435DECLARE
436BEGIN
437 :new.ESTABLISH_TIME := sysdate;
438 DBMS_output.put_line('TRIGGER: zaktualizowano date statusu zamowienia nr ' || :new.id);
439END;
440/
441ALTER TRIGGER "RMSBD"."UPDATE_STATUS_DATE" ENABLE;
442--------------------------------------------------------
443-- DDL for Function ARE_CREDENTIALS_CORRECT
444--------------------------------------------------------
445
446 CREATE OR REPLACE FUNCTION "RMSBD"."ARE_CREDENTIALS_CORRECT"
447(
448 USER_EMAIL IN VARCHAR2
449, USER_PASSWORD IN VARCHAR2
450) RETURN NUMBER AS
451
452hashedPass RAW(20);
453is_match NUMBER(1);
454BEGIN
455 hashedPass := SYS.dbms_crypto.hash(
456 src => UTL_I18N.STRING_TO_RAW(USER_PASSWORD),
457 typ => sys.dbms_crypto.hash_sh1
458 );
459 is_match:=0;
460 SELECT COUNT(customers.id) INTO is_match from customers where customers.email = USER_EMAIL AND customers.password=hashedPass;
461 if (is_match = 1) then
462 DBMS_output.put_line('FUNCTION: podane haslo ' || USER_PASSWORD || '(' || hashedPass || ' zgadza sie z zaszyfrowanym');
463 else
464 DBMS_output.put_line('FUNCTION: podane haslo ' || USER_PASSWORD || '(' || hashedPass || ' nie zgadza sie z zaszyfrowanym' );
465 end if;
466 return is_match;
467END ARE_CREDENTIALS_CORRECT;
468
469/
470--------------------------------------------------------
471-- DDL for Function GET_INCOMING_ORDERS_VALUE
472--------------------------------------------------------
473
474 CREATE OR REPLACE FUNCTION "RMSBD"."GET_INCOMING_ORDERS_VALUE" RETURN NUMBER AS
475total_price NUMBER;
476BEGIN
477
478select SUM(price) into total_price from ORDERS_PRODUCTS
479inner join products on products.id = orders_products.product_id;
480select SUM(price) into total_price from ORDERS_PRODUCTS
481inner join products on products.id = orders_products.product_id
482where exists (select 1 from orders where orders_products.order_id = orders.id and orders.type=1);
483
484
485DBMS_output.put_line('FUNCTION: Calkowita wartosc produktow incoming (zamowionych do magazynu) to' || total_price);
486
487return total_price;
488END GET_INCOMING_ORDERS_VALUE;
489
490/
491--------------------------------------------------------
492-- DDL for Function GET_OUTCOMING_ORDERS_VALUE
493--------------------------------------------------------
494
495 CREATE OR REPLACE FUNCTION "RMSBD"."GET_OUTCOMING_ORDERS_VALUE" RETURN VARCHAR2 AS
496total_price NUMBER;
497BEGIN
498select SUM(price) into total_price from ORDERS_PRODUCTS
499inner join products on products.id = orders_products.product_id
500where exists (select 1 from orders where orders_products.order_id = orders.id and orders.type=2);
501
502DBMS_output.put_line('FUNCTION: Calkowita wartosc produktow outcoming (zamowionych z magazynu) to' || total_price);
503
504return total_price;
505END GET_OUTCOMING_ORDERS_VALUE;
506
507/
508--------------------------------------------------------
509-- DDL for Procedure ADD_CUSTOMER
510--------------------------------------------------------
511set define off;
512
513 CREATE OR REPLACE PROCEDURE "RMSBD"."ADD_CUSTOMER"
514(
515 NAME IN customers.name%TYPE
516, SURNAME IN customers.surname%TYPE
517, EMAIL IN customers.email%TYPE
518, PASSWORD IN VARCHAR2
519) IS
520
521lastEmptyId NUMBER;
522hashedPass RAW(20);
523
524BEGIN
525 hashedPass := SYS.dbms_crypto.hash(
526 src => UTL_I18N.STRING_TO_RAW(password),
527 typ => sys.dbms_crypto.hash_sh1
528 );
529 SELECT COUNT(*)
530 INTO lastEmptyId
531 from customers;
532 DBMS_output.put_line('PROCEDURE: zaszyfrowano haslo uzytkownika ' || PASSWORD || ' -> ' || hashedPass);
533 INSERT INTO customers (customers.ID,customers.NAME,customers.SURNAME,customers.EMAIL,customers.password) VALUES (lastEmptyId+1,NAME,SURNAME,email,hashedPass);
534 DBMS_output.put_line('PROCEDURE: stworzono uzytkownika ' || NAME || ' ' || surname);
535END ADD_CUSTOMER;
536
537/
538--------------------------------------------------------
539-- DDL for Procedure ADD_ORDER
540--------------------------------------------------------
541set define off;
542
543 CREATE OR REPLACE PROCEDURE "RMSBD"."ADD_ORDER"
544(
545 CUSTOMER_ID IN NUMBER
546, ORDER_ID OUT NUMBER
547, ORDER_TYPE IN orders.type%TYPE
548) AS
549BEGIN
550 SELECT COUNT(*) + 1
551 INTO ORDER_ID FROM ORDERS;
552 INSERT INTO ORDERS (id,customer_id,type) VALUES(ORDER_ID,CUSTOMER_ID,ORDER_TYPE);
553 DBMS_output.put_line('PROCEDURE: dodano nowe zamowienie id :' || ORDER_ID || ' dla uzytkownika ' || CUSTOMER_ID || ' o typie' || ORDER_TYPE);
554END ADD_ORDER;
555
556/
557--------------------------------------------------------
558-- DDL for Procedure ADD_PRODUCT
559--------------------------------------------------------
560set define off;
561
562 CREATE OR REPLACE PROCEDURE "RMSBD"."ADD_PRODUCT"
563(
564 PRODUCT_NAME IN VARCHAR2
565, PRODUCT_WEIGHT IN VARCHAR2
566, WAREHOUSE_ID IN VARCHAR2
567) AS
568
569auto_incremnted_id NUMBER(6);
570BEGIN
571 SELECT COUNT(*) + 1
572 INTO auto_incremnted_id
573 FROM PRODUCTS;
574 INSERT INTO PRODUCTS (PRODUCTS.id,PRODUCTS.name,PRODUCTS.weight,PRODUCTS.warehouse_id)
575 VALUES(auto_incremnted_id,PRODUCT_NAME,PRODUCT_WEIGHT,WAREHOUSE_ID);
576 DBMS_output.put_line('PROCEDURE: dodano produkt ' || PRODUCT_NAME || ' do magazynu nr ' || WAREHOUSE_ID);
577END ADD_PRODUCT;
578
579/
580--------------------------------------------------------
581-- DDL for Procedure ADD_PRODUCT_TO_ORDER
582--------------------------------------------------------
583set define off;
584
585 CREATE OR REPLACE PROCEDURE "RMSBD"."ADD_PRODUCT_TO_ORDER"
586(
587 ORD_ID IN NUMBER
588, PROD_ID IN NUMBER
589) AS
590auto_incremnted_id NUMBER(6);
591BEGIN
592 SELECT COUNT(*) + 1
593 INTO auto_incremnted_id FROM ORDERS_PRODUCTS;
594 INSERT INTO ORDERS_PRODUCTS (id,ORDERS_PRODUCTS.ORDER_ID,ORDERS_PRODUCTS.PRODUCT_ID) VALUES(auto_incremnted_id,ORD_ID,PROD_ID);
595 DBMS_output.put_line('PROCEDURE: dodano nowy produkt :' || PROD_ID || ' do zamowienia ' || ORD_ID);
596END ADD_PRODUCT_TO_ORDER;
597
598/
599--------------------------------------------------------
600-- DDL for Procedure ADD_WAREHOUSE
601--------------------------------------------------------
602set define off;
603
604 CREATE OR REPLACE PROCEDURE "RMSBD"."ADD_WAREHOUSE"
605(
606 WAREHOUSE_NAME IN VARCHAR2
607, WAREHOUSE_ADDR IN VARCHAR2
608) AS
609
610auto_incremnted_id NUMBER(6);
611BEGIN
612 SELECT COUNT(*) + 1
613 INTO auto_incremnted_id
614 FROM WAREHOUSE;
615 INSERT INTO WAREHOUSE (WAREHOUSE.id,WAREHOUSE.name,WAREHOUSE.adress)
616 VALUES(auto_incremnted_id,WAREHOUSE_NAME,WAREHOUSE_ADDR);
617 DBMS_output.put_line('PROCEDURE: dodano magazyn ' || WAREHOUSE_NAME || ' o adresie ' || WAREHOUSE_ADDR);
618END ADD_WAREHOUSE;
619
620/
621--------------------------------------------------------
622-- DDL for Procedure CHECK_MISSING_PRODUCTS
623--------------------------------------------------------
624set define off;
625
626 CREATE OR REPLACE PROCEDURE "RMSBD"."CHECK_MISSING_PRODUCTS" AS
627products_incoming_amount NUMBER;
628products_outcoming_amount NUMBER;
629generated_order_id NUMBER;
630BEGIN
631 SELECT COUNT(*) into products_incoming_amount from orders where orders.type=1;
632 SELECT COUNT(*) into products_outcoming_amount from orders where orders.type=2;
633
634 DBMS_OUTPUT.PUT_LINE('PROCEDURE-SHEDULER: Produkty incoming: ' || products_incoming_amount || '. Produkty outcoming: ' || products_outcoming_amount);
635 if products_incoming_amount-products_outcoming_amount < 3 then
636 DBMS_output.put_line('PROCEDURE-SHEDULER: Wykryto niedobor produktow zamaiwanie nowych!');
637 ADD_ORDER(1,generated_order_id,1);
638 FOR i IN 1..3 LOOP
639 ADD_PRODUCT_TO_ORDER(generated_order_id,i);
640 END LOOP;
641 end if;
642
643END CHECK_MISSING_PRODUCTS;
644
645/
646--------------------------------------------------------
647-- DDL for Procedure ORDER_MISSING_PRODUCTS
648--------------------------------------------------------
649set define off;
650
651 CREATE OR REPLACE PROCEDURE "RMSBD"."ORDER_MISSING_PRODUCTS" AS
652BEGIN
653 DBMS_OUTPUT.PUT_LINE('Ordering missing products');
654-- add_product('wygernowany',21,1);
655END ORDER_MISSING_PRODUCTS;
656
657/
658--------------------------------------------------------
659-- DDL for Procedure SEND_EMAIL
660--------------------------------------------------------
661set define off;
662
663 CREATE OR REPLACE PROCEDURE "RMSBD"."SEND_EMAIL"
664(
665 html IN VARCHAR2 ,
666 DESTINATION IN VARCHAR2
667) AS
668auto_incremnted_id NUMBER(6);
669BEGIN
670 SELECT COUNT(*) + 1
671 INTO auto_incremnted_id
672 FROM EMAILS_QUEUE;
673 INSERT INTO EMAILS_QUEUE (EMAILS_QUEUE.id,EMAILS_QUEUE.html,EMAILS_QUEUE.DESTINATION)
674 VALUES(auto_incremnted_id,html,DESTINATION);
675 DBMS_OUTPUT.put_line('wysylanie email do ' || DESTINATION || ' o zawartosci ' || html);
676END SEND_EMAIL;
677
678/
679
680
681
682
683
684
685
686
687
688
689
690
691--END;