· 6 years ago · May 25, 2019, 05:42 PM
1DROP TABLE Product CASCADE CONSTRAINTS
2/
3DROP TABLE OrderDetails CASCADE CONSTRAINTS
4/
5DROP TABLE Customer CASCADE CONSTRAINTS
6/
7DROP TABLE OrderT CASCADE CONSTRAINTS
8/
9DROP TABLE ShopCart CASCADE CONSTRAINTS
10/
11DROP TABLE ShopCartDetails CASCADE CONSTRAINTS
12/
13
14CREATE TABLE Product(
15ID Int NOT NULL PRIMARY KEY,
16Name varchar(255) NOT NULL,
17Image varchar(55) NOT NULL,
18Description varchar(255) NOT NULL,
19Price Number(6,2) NOT NULL,
20Brand varchar(255) NOT NULL,
21Quantity varchar(255) NOT NULL,
22Categorie varchar(255) NOT NULL
23)
24/
25
26CREATE TABLE Customer(
27ID Int NOT NULL PRIMARY KEY,
28Fname varchar(255) NOT NULL,
29Lname varchar(255) NOT NULL,
30Username varchar(255) NOT NULL,
31Password varchar(255) NOT NULL,
32Email varchar(255) NOT NULL
33)
34/
35
36CREATE TABLE OrderDetails(
37ID Int NOT NULL PRIMARY KEY,
38OrderDate date NOT NULL,
39Image varchar(255) NOT NULL,
40ProductQuantity Int NOT NULL,
41Price Number(6,2) NOT NULL,
42ProductID Int NOT NULL,
43OrderID Int NOT NULL
44)
45/
46
47CREATE TABLE OrderT(
48ID Int NOT NULL PRIMARY KEY,
49OrderNumber Int NOT NULL,
50OrderTotalPrice Int NOT NULL,
51CustomerID Int NOT NULL
52)
53/
54
55CREATE TABLE ShopCart(
56ID Int NOT NULL PRIMARY KEY,
57ProducID Int NOT NULL,
58CustomerID Int NOT NULL,
59ProductQuantity Int NOT NULL,
60ProductPrice Number(6,2) NOT NULL
61)
62/
63
64CREATE TABLE ShopCartDetails(
65ID Int NOT NULL PRIMARY KEY,
66CustomerID Int NOT NULL,
67ProductID Int NOT NULL
68)
69/
70
71
72DECLARE
73TYPE varr IS VARRAY(1000) OF varchar(255);
74 lista_nume varr := varr('Ababei','Acasandrei','Adascalitei','Afanasie','Agafitei','Agape','Aioanei','Alexandrescu','Alexandru','Alexe','Alexii','Amarghioalei','Ambroci','Andonesei','Andrei','Andrian','Andrici','Andronic','Andros','Anghelina','Anita','Antochi','Antonie','Apetrei','Apostol','Arhip','Arhire','Arteni','Arvinte','Asaftei','Asofiei','Aungurenci','Avadanei','Avram','Babei','Baciu','Baetu','Balan','Balica','Banu','Barbieru','Barzu','Bazgan','Bejan','Bejenaru','Belcescu','Belciuganu','Benchea','Bilan','Birsanu','Bivol','Bizu','Boca','Bodnar','Boistean','Borcan','Bordeianu','Botezatu','Bradea','Braescu','Budaca','Bulai','Bulbuc-aioanei','Burlacu','Burloiu','Bursuc','Butacu','Bute','Buza','Calancea','Calinescu','Capusneanu','Caraiman','Carbune','Carp','Catana','Catiru','Catonoiu','Cazacu','Cazamir','Cebere','Cehan','Cernescu','Chelaru','Chelmu','Chelmus','Chibici','Chicos','Chilaboc','Chile','Chiriac','Chirila','Chistol','Chitic','Chmilevski','Cimpoesu','Ciobanu','Ciobotaru','Ciocoiu','Ciofu','Ciornei','Citea','Ciucanu','Clatinici','Clim','Cobuz','Coca','Cojocariu','Cojocaru','Condurache','Corciu','Corduneanu','Corfu','Corneanu','Corodescu','Coseru','Cosnita','Costan','Covatariu','Cozma','Cozmiuc','Craciunas','Crainiceanu','Creanga','Cretu','Cristea','Crucerescu','Cumpata','Curca','Cusmuliuc','Damian','Damoc','Daneliuc','Daniel','Danila','Darie','Dascalescu','Dascalu','Diaconu','Dima','Dimache','Dinu','Dobos','Dochitei','Dochitoiu','Dodan','Dogaru','Domnaru','Dorneanu','Dragan','Dragoman','Dragomir','Dragomirescu','Duceac','Dudau','Durnea','Edu','Eduard','Eusebiu','Fedeles','Ferestraoaru','Filibiu','Filimon','Filip','Florescu','Folvaiter','Frumosu','Frunza','Galatanu','Gavrilita','Gavriliuc','Gavrilovici','Gherase','Gherca','Ghergu','Gherman','Ghibirdic','Giosanu','Gitlan','Giurgila','Glodeanu','Goldan','Gorgan','Grama','Grigore','Grigoriu','Grosu','Grozavu','Gurau','Haba','Harabula','Hardon','Harpa','Herdes','Herscovici','Hociung','Hodoreanu','Hostiuc','Huma','Hutanu','Huzum','Iacob','Iacobuta','Iancu','Ichim','Iftimesei','Ilie','Insuratelu','Ionesei','Ionesi','Ionita','Iordache','Iordache-tiroiu','Iordan','Iosub','Iovu','Irimia','Ivascu','Jecu','Jitariuc','Jitca','Joldescu','Juravle','Larion','Lates','Latu','Lazar','Leleu','Leon','Leonte','Leuciuc','Leustean','Luca','Lucaci','Lucasi','Luncasu','Lungeanu','Lungu','Lupascu','Lupu','Macariu','Macoveschi','Maftei','Maganu','Mangalagiu','Manolache','Manole','Marcu','Marinov','Martinas','Marton','Mataca','Matcovici','Matei','Maties','Matrana','Maxim','Mazareanu','Mazilu','Mazur','Melniciuc-puica','Micu','Mihaela','Mihai','Mihaila','Mihailescu','Mihalachi','Mihalcea','Mihociu','Milut','Minea','Minghel','Minuti','Miron','Mitan','Moisa','Moniry-abyaneh','Morarescu','Morosanu','Moscu','Motrescu','Motroi','Munteanu','Murarasu','Musca','Mutescu','Nastaca','Nechita','Neghina','Negrus','Negruser','Negrutu','Nemtoc','Netedu','Nica','Nicu','Oana','Olanuta','Olarasu','Olariu','Olaru','Onu','Opariuc','Oprea','Ostafe','Otrocol','Palihovici','Pantiru','Pantiruc','Paparuz','Pascaru','Patachi','Patras','Patriche','Perciun','Perju','Petcu','Pila','Pintilie','Piriu','Platon','Plugariu','Podaru','Poenariu','Pojar','Popa','Popescu','Popovici','Poputoaia','Postolache','Predoaia','Prisecaru','Procop','Prodan','Puiu','Purice','Rachieru','Razvan','Reut','Riscanu','Riza','Robu','Roman','Romanescu','Romaniuc','Rosca','Rusu','Samson','Sandu','Sandulache','Sava','Savescu','Schifirnet','Scortanu','Scurtu','Sfarghiu','Silitra','Simiganoschi','Simion','Simionescu','Simionesei','Simon','Sitaru','Sleghel','Sofian','Soficu','Sparhat','Spiridon','Stan','Stavarache','Stefan','Stefanita','Stingaciu','Stiufliuc','Stoian','Stoica','Stoleru','Stolniceanu','Stolnicu','Strainu','Strimtu','Suhani','Tabusca','Talif','Tanasa','Teclici','Teodorescu','Tesu','Tifrea','Timofte','Tincu','Tirpescu','Toader','Tofan','Toma','Toncu','Trifan','Tudosa','Tudose','Tuduri','Tuiu','Turcu','Ulinici','Unghianu','Ungureanu','Ursache','Ursachi','Urse','Ursu','Varlan','Varteniuc','Varvaroi','Vasilache','Vasiliu','Ventaniuc','Vicol','Vidru','Vinatoru','Vlad','Voaides','Vrabie','Vulpescu','Zamosteanu','Zazuleac');
75 lista_prenume_fete varr := varr('Adina','Alexandra','Alina','Ana','Anca','Anda','Andra','Andreea','Andreia','Antonia','Bianca','Camelia','Claudia','Codrina','Cristina','Daniela','Daria','Delia','Denisa','Diana','Ecaterina','Elena','Eleonora','Elisa','Ema','Emanuela','Emma','Gabriela','Georgiana','Ileana','Ilona','Ioana','Iolanda','Irina','Iulia','Iuliana','Larisa','Laura','Loredana','Madalina','Malina','Manuela','Maria','Mihaela','Mirela','Monica','Oana','Paula','Petruta','Raluca','Sabina','Sanziana','Simina','Simona','Stefana','Stefania','Tamara','Teodora','Theodora','Vasilica','Xena');
76 lista_prenume_baieti varr := varr('Adrian','Alex','Alexandru','Alin','Andreas','Andrei','Aurelian','Beniamin','Bogdan','Camil','Catalin','Cezar','Ciprian','Claudiu','Codrin','Constantin','Corneliu','Cosmin','Costel','Cristian','Damian','Dan','Daniel','Danut','Darius','Denise','Dimitrie','Dorian','Dorin','Dragos','Dumitru','Eduard','Elvis','Emil','Ervin','Eugen','Eusebiu','Fabian','Filip','Florian','Florin','Gabriel','George','Gheorghe','Giani','Giulio','Iaroslav','Ilie','Ioan','Ion','Ionel','Ionut','Iosif','Irinel','Iulian','Iustin','Laurentiu','Liviu','Lucian','Marian','Marius','Matei','Mihai','Mihail','Nicolae','Nicu','Nicusor','Octavian','Ovidiu','Paul','Petru','Petrut','Radu','Rares','Razvan','Richard','Robert','Roland','Rolland','Romanescu','Sabin','Samuel','Sebastian','Sergiu','Silviu','Stefan','Teodor','Teofil','Theodor','Tudor','Vadim','Valentin','Valeriu','Vasile','Victor','Vlad','Vladimir','Vladut');
77 list_branduri varr := varr('CYMA','Lancer Tactical','CYBERGUN','UMAREX','UK ARMS','AGM','APS','Redwolf Airsoft','Systema','Tokyo Marui','Classic Army','ICS','GG','KWA','AK AIRSOFT','GP');
78 list_letters varr := varr('A','B','C','D','E','F','G','H','I','J','K','M','N','O','P','R','T','S','U','V','X','Y','Z');
79 list_product_name varr := varr('Compact','CZ75-Auto','Tec-9','Five-SeveN','Glock-18','R8 Revolver','Dual Berettas');
80 lista_categorii varr := varr('Pistoale','Pusti cu luneta','Shotgun','Mass destruction');
81 v_nume VARCHAR(255);
82 v_prenume VARCHAR(255);
83 v_prenume1 VARCHAR(255);
84 v_prenume2 VARCHAR(255);
85 v_user varchar(255);
86 v_password varchar(255);
87 v_email varchar(255);
88 v_productName varchar(255) ;
89 v_random_letter varchar(255);
90 v_bookname varchar(255);
91 v_price number(6,2);
92 v_image varchar(255);
93 v_description varchar(255) ;
94 v_product_quantity int;
95 v_product_brand varchar(255);
96 v_categorie varchar(255);
97
98BEGIN
99DBMS_OUTPUT.PUT_LINE('Inserare 1000000 de linii in tabela Customer...');
100 FOR v_i in 1..100000 LOOP
101 v_nume := lista_nume(TRUNC(DBMS_RANDOM.VALUE(0, LISTA_NUME.COUNT))+1);
102 IF (DBMS_RANDOM.VALUE(0,100)<50) THEN
103 v_prenume1 := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.count))+1);
104 LOOP
105 v_prenume2 := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.count))+1);
106 exit when v_prenume1<>v_prenume2;
107 END LOOP;
108 ELSE
109 v_prenume1 := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.count))+1);
110 LOOP
111 v_prenume2 := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.count))+1);
112 exit when v_prenume1<>v_prenume2;
113 END LOOP;
114 END IF;
115
116 IF (DBMS_RANDOM.VALUE(0,100)<60) THEN
117 IF LENGTH(v_prenume1 || ' ' || v_prenume2) <= 20 THEN
118 v_prenume := v_prenume1 || ' ' || v_prenume2;
119 END IF;
120 else
121 v_prenume := v_prenume1;
122 END IF;
123 v_user := 'user' || v_i;
124 v_password := 'password' || v_i;
125 v_email := v_user || '@gmail.com';
126
127 insert into Customer values(v_i,v_nume, v_prenume, v_user, v_password,v_email);
128end loop;
129 DBMS_OUTPUT.PUT_LINE('Customer is done...');
130commit;
131
132DBMS_OUTPUT.PUT_LINE('Inserare linii in tabela Products...');
133
134 for v_i in 1..1000000 loop
135 v_random_letter := list_letters(TRUNC(DBMS_RANDOM.VALUE(0,list_letters.count))+1);
136 v_productName := list_product_name(TRUNC(DBMS_RANDOM.VALUE(0,list_product_name.count))+1) || ' - ' || v_random_letter;
137 --v_productName := 'x';
138 v_image := 'a';
139 v_description := 'b';
140 v_price := round(DBMS_RANDOM.VALUE(0,1000),2);
141 v_product_brand := list_branduri(TRUNC(DBMS_RANDOM.VALUE(0,list_branduri.count))+1);
142 v_product_quantity := DBMS_RANDOM.VALUE(0,100);
143 v_categorie := lista_categorii(TRUNC(DBMS_RANDOM.VALUE(0,lista_categorii.count))+1);
144 insert into Product values(v_i ,v_productName, v_image, v_description, v_price, v_product_brand ,v_product_quantity,v_categorie);
145
146 end loop;
147 DBMS_OUTPUT.PUT_LINE('Done with Products...');
148 commit;
149 end;
150 /
151select * from shopcart
152select * from product order by id desc
153SELECT FNAME FROM (select * from CUSTOMER ORDER BY ID DESC) WHERE ROWNUM = 1;