-
Notifications
You must be signed in to change notification settings - Fork 0
/
Tema_Lab4.sql
63 lines (55 loc) · 12.9 KB
/
Tema_Lab4.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
DROP TABLE VACANTA;
CREATE TABLE VACANTA(id NUMBER,id_student NUMBER,destinatie VARCHAR(60),data_plecarii DATE,cazare VARCHAR2(80),estimare_pret NUMBER(12,6),numar_persoane VARCHAR2(2));
CREATE OR REPLACE PROCEDURE genereaza_aleatoriu_tabela(p_numar_randuri IN INT)
IS
TYPE locatii_disponibile IS VARRAY(180) OF VARCHAR2(60);
locuri_vacanta locatii_disponibile:=locatii_disponibile(
'King Edward Point','Kathmandu','Port-aux-Français','Moscow','Gaborone','Road Town','Colombo'
,'Athens','Guatemala City','Hanoi','Athens','Beirut','Apia','Jakarta','Marigot','Montevideo'
,'Chisinau','Paramaribo','Tirana','Panama City','Belgrade','Athens','Andorra la Vella','Taipei'
,'Freetown','Dhaka','Lisbon','Kuala Lumpur','La Paz','Paramaribo','Bujumbura','Tunis','Baghdad','Rangoon'
,'Astana','Freetown','Canberra','Diego Garcia','Bangkok','Bern','Dili','Windhoek','Honiara','Nicosia','West Island'
,'Tunis','Moroni','Conakry','Hanoi','Harare','Washington','Grand Turk','Ouagadougou','Bridgetown','Tehran','Damascus'
,'Saint John','Dushanbe','Dili','Maseru','Oslo','Saint John','Port-au-Prince','Plymouth','Rome','La Paz','Ouagadougou','Port Moresby'
,'Ashgabat','Windhoek','Longyearbyen','Nuuk','Lome','Lisbon','Pristina','Roseau','Gibraltar','Praia','Melekeok','Antananarivo','Basseterre','Madrid'
,'New Delhi','Dakar','Chisinau','Moroni','Mogadishu','Addis Ababa','Monaco','Tegucigalpa','Singapore','Paramaribo','Majuro','Dhaka','Kigali'
,'Kingston','Belgrade','Baghdad','Tegucigalpa','Valletta','Windhoek','Jerusalem','Vilnius','Phnom Penh','Willemstad','Avarua','Saipan','Lome'
,'Santiago','Hargeisa','Yaounde','Bridgetown','N"Djamena','Abuja','Amman','Tegucigalpa','Dushanbe','Bangkok','Amman','Niamey','Santiago'
,'Moscow','Brasilia','Hamilton','London','Mbabane','Sanaa','Yerevan','Diego Garcia','Riga','Maputo','Port-Vila','Amsterdam'
,'Mogadishu','Diego Garcia','Kathmandu','Brussels','Dushanbe',
'The Valley','Longyearbyen','Papeete','Mexico City','Kinshasa'
,'Yamoussoukro','Castries','Brazzaville','Algiers','Rabat','Hanoi','Amman'
,'Bolivia (Plurinational State of)','Bosnia and Herzegovina','Azerbaijan','Djibouti','Botswana'
,'Azerbaijan','Haiti','Czechia','Liechtenstein','Djibouti','Ethiopia','Bangladesh','Mali','Colombia'
,'Guinea-Bissau','Somalia','Solomon Islands','Togo','India','Brazil','Denmark','Malawi','Luxembourg','Tajikistan','Luxembourg'
,'Bahamas','Congo, Democratic Republic of the','Serbia','Syrian Arab Republic','Saint Vincent and the Grenadines');
TYPE locatii_cazare IS VARRAY(180) OF VARCHAR2(80);
cazare_disponibila locatii_cazare:=locatii_cazare(
'89101 Kenda Mill South Chinashire IL 76999','Apt. 826 284 Evelyn Estate West Dotstad ME 17345-3811','Suite 965 58473 Lore Knoll Marjoriemouth MA 33450','651 Katy View Bodeport GA 84371-7632','70013 O-Connell Garden West Darylmouth TX 32372-1876','8474 Hoeger Green New Kileytown MD 40573-2016','76679 Lenora Street Nitzschefurt GA 56085-3854','Apt. 241 85659 Winter Heights Handmouth IN 06054-0049','Apt. 735 422 Lynch Junction Ebertmouth UT 18954-3337','Suite 189 47710 O-Conner Tunnel Samathastad NE 94356','90281 Morissette Stravenue Rooseveltville CT 36715','Apt. 793 64272 Anabel Lodge Lake Hilarioland ID 36613-2968','Suite 195 432 Wintheiser Grove East Terry OK 86813','3845 Kohler Gardens Kiehnchester OR 11209','48330 Lockman Ports New Fermina FL 68552','64959 O-Connell Center Andreport WI 46212','Suite 409 0016 Yuko Shoals Stromanmouth MA 15287','5249 Ligia Knoll Brekkechester AR 04379-8401','2636 Lemke Summit South Winford MO 50950','463 Ashley Well McLaughlinhaven ID 50618-7469','14023 Bret Tunnel Port Ulyssesburgh WV 40810-5662','Suite 248 866 Homenick Green Lancehaven KS 74127-7460','899 Donnelly Fall Hammeschester ME 62379-9833','084 Georgia Falls Port Michalfort NY 59243-8697','Suite 237 9522 Lynch Bridge South Tyron AL 26664','Suite 048 734 Bartell Bypass South Waylonfurt WA 64568-4605','Suite 730 30248 Thomasena Court East Emiliehaven KY 40498','Suite 628 10557 Connie Green West Mathew OK 87451-9495','468 Harris Rest Cartwrightside MS 98974-5158','94686 Lesch Roads South Gilberte NV 65745','930 Schmitt Loop Faheystad SD 61686','Suite 775 77369 Nader Canyon Hyonstad NV 10990-7189','335 Berneice Landing Stewartbury VT 26160-1088','Suite 701 83347 Spinka Lodge Ondrickaville DE 83241','Suite 686 70061 Caridad Crossroad South Ashleyfurt IL 76181','411 Feeney Keys North Elmerstad NV 59974-4855','02801 Reginald Spurs Hilllborough WI 02376','Apt. 147 254 Peter Key Nanetteton MA 62760-5106','Suite 398 9160 Rosalva Mills West Arleenshire AZ 85597','Apt. 743 0583 O-Hara Hill Thompsonshire UT 79082-1340','239 Raelene Path Kreigerport UT 88758-8392','11004 Marlin Orchard East Louiechester AK 60472','Apt. 385 99207 Nestor Alley West Perlaberg MN 45385-3201','Apt. 146 62234 Durgan Plaza Lake Starlaville NE 98362','Suite 117 015 Fadel Spur South Blancaland MN 68208','950 Homenick Overpass South Lakia MI 01274','64746 Jaskolski Turnpike Lake Griceldaborough KS 94519-8954','1029 Lesch Freeway West Mauro NM 85897-1308','Suite 722 3024 Kshlerin Course Boyerburgh OR 49240','50119 Dwight Knoll Rooseveltberg DE 23986-7922','5562 Ferry Mews Walterside VT 73204','Apt. 337 9669 Criselda Glens Hectorport CO 04191-4839','Apt. 334 22425 Hiram Valleys East Kenamouth CT 49339','Apt. 126 929 Rippin Meadows North Alvinmouth GA 07874-2936','Suite 194 59716 Von Plains Gaylenefurt MT 99396-5362','337 Nienow Cove Schmidtville DE 37796-2916','Apt. 952 0438 Dietrich Corner New Marilynshire OK 17798','Apt. 133 536 Kuphal Mountains Moenside VT 07392','52254 Earnest Hills South Arnette NM 50006-5748','32960 Walsh Viaduct West Dillon CA 73279-4160'
,'63124 Joaquin Crossing West Coraliestad IA 03890-6175','231 Doreatha Flat Lake Eddyport WV 19941','Suite 146 429 Roselee Plaza Bashirianchester AK 12087','Suite 275 372 Hayes Glens Lake Jesenialand OR 48275','4240 Runolfsson Union Mantetown MS 90939-5999','83502 Billy Shore Bretfurt NC 65150','82113 Wendy Grove Turnerberg ND 02354-1400','0158 Murphy Way South Breann IA 69369-8891','Suite 982 808 Jung Glen Nolanberg ID 97593-1789','Apt. 837 3878 MacGyver Mountain Crookstown WA 07700','Apt. 265 513 Koelpin Gardens West Danny WV 09764','542 Solomon Ramp Port Eddie SC 90739','918 Hettinger Field Dylanbury SD 07892','Apt. 667 7721 Kimberlee Grove Schoenview UT 17453-1331','273 Stoltenberg Cape Raquelfort NV 96444-9277','Suite 578 335 Theo Summit Port Rickie UT 87774-5023','8283 Thiel Road Howellville AR 25981-2094','Suite 982 0127 Warren Underpass North Terrance KY 06979','39591 Bess Meadows Rendaberg PA 83554-7862','Apt. 670 4149 Adams Meadows Hackettville SC 92457-1391','Apt. 441 750 Tremblay Mall Veronicaton CT 07817-8873','15516 Weimann Curve East Keren TX 60725','410 Marks Inlet North Michael VT 21468-7723','Apt. 678 7353 Turcotte Drive Lake Ruthanneview NJ 64569-1179','Apt. 860 53389 Indira Station North Leesaport IA 31104-0633','5384 Bednar Stravenue Derekberg CA 27825','6777 Lorinda Track Goodwinborough NH 49523-8598','802 Laveta Row Uptonport HI 35704-1574','4418 Grant Viaduct Rebecaview WA 46007-2685','29778 Flatley Place North Ozellahaven ME 58037','3030 Setsuko Meadows South Jestine NY 38748-9090','Suite 277 8685 Luis Row South Fleta IN 62488-0243','Apt. 877 657 Carlos Way Shenaborough WY 03863','Apt. 518 46404 Gulgowski Camp Stehrfort IN 28982','Apt. 554 779 Ilda Estate East Lesley MI 73117-1341','Suite 477 9671 Karina Court Smithamfort NY 54991','Suite 793 51951 Rice Turnpike South Jackieshire IA 78556-8760','26808 Gary Islands South Donnellfort WA 76520','307 Lionel Path Santiagoside TN 07261','1004 Kenton Expressway Linomouth TX 65755-0498','5162 Skiles Vista Katibury ND 35555-1804','Suite 252 41576 Dee Crescent Meaghanview MO 09404-8017'
,'2799 Amada Parkways East Faith MT 86250-7625','8741 Eartha Meadow West Gretchen ME 22289-5555','Apt. 540 740 Cormier Landing Ferminfort MI 01081-0912','4913 Glendora Dam North Marnishire ME 88112-3318','31708 King Valleys Shaundatown ND 96816','81039 August Junctions South Claude NV 03962-3047','Apt. 888 68635 Tyron Burg South Domingafort WI 43959','Suite 492 64277 Eusebio Ferry Kshlerinmouth AR 79050-9319','Suite 623 61807 Eldon Road Morarshire HI 32586','4532 Kurt Skyway New Hilary MA 21974-5768','Apt. 857 272 Emmett Island Lake Reid AR 25760-1688','67148 Schowalter Creek Powlowskimouth VA 52453-3807','Apt. 234 245 Lucio Cape Lebsackberg KY 25760-9107','88216 Christiansen Vista West Leland IN 16069-4176','Apt. 624 91752 Mohr Shoals Leoton NY 61057-0841','318 Marline Crossing East Raphaelmouth MN 46479','Suite 598 847 Christiansen Street Edgardobury CO 65626','51315 Hollis Streets Lake Inge SD 74592-9148','79059 Rowe River Ferdinandfort TN 12664','Suite 419 9001 Weber Estates Savannaberg IL 78770-6310','Apt. 106 192 Ali Road South Sheridan MT 61258-7473','221 Harvey Gateway Leanahaven AK 50285-2681','89786 Rutherford Motorway Candacehaven MA 77679'
,'777 Roger Ways East Martyburgh MS 63222-0746','Suite 116 75601 Leigh Falls Port Laurence TN 88068','04492 Socorro Mountain Brakushaven OK 02871','Suite 532 593 Stephan Lights Ryanport IA 85032','46852 Jimmy Alley Jakubowskimouth NY 73062','90411 Lubowitz Crest Leilaniborough NH 39114','315 Meggan Lights New Gigi IL 08403','76753 Digna Bypass Idachester KY 32855','3897 Orn Unions Port Vernon WV 60348','Suite 789 7538 Kautzer Common Lowefurt ND 19270-3486','Apt. 973 21794 Marina Drives East Ricky OR 79770-8939','12720 Kunde Island Terryside OH 14152-6979','1630 Upton Corner North Miguel UT 55630','446 Hayden Path North Austinshire IL 22726','69438 Robel Viaduct North Setsuko AZ 92768-0116','Suite 892 0706 Fisher Burgs Stantonland OR 54274','1245 Ernesto Plaza Raleighfort ID 31148','783 Rico Points Lockmanstad NE 02193','Suite 213 62897 Hammes Greens Jonesberg PA 59640','01576 Jaskolski Drive New Janetfort UT 75758','062 Roob Ports Lake Jaimeport KY 43440','Apt. 703 750 Pansy Lakes Abernathymouth MT 06235','45031 Sidney Run Roseleeshire PA 79673','Suite 841 74024 Mikki Mall South Randiton NE 49929-8745','Apt. 576 204 Sung Streets Lake Chau LA 57323','806 Gerri Bridge Bauchfurt OK 00520-1454'
,'3747 Corkery Plaza Lake Clement PA 16086-4632','Suite 808 58001 Donnell Cape Turnerborough IN 91027','479 Kuphal Loop West Carlottaburgh AL 82050','Apt. 820 487 Cormier Falls New Brittaniport WI 98173-8505','132 Heidenreich Dam Dagnybury DE 17274-3230','61945 Laraine Meadows Langoshton WY 68897','68752 Hettinger Bridge Desireestad HI 49789-9779','Apt. 967 78083 Sonia Well Kingshire IN 85677-3660','Suite 692 939 Kulas Curve East Bertie GA 94509-5865','7375 Denae Inlet South Bulahland NM 61401-6415','Suite 013 2415 Madelyn Port Terribury PA 77547','Apt. 143 228 Ankunding Lights North Ivonne HI 47107','5803 Natacha Ville Jefferyburgh OK 37637','4327 Strosin Spur Huelsbury RI 56209','Suite 309 787 Terry Crest Lake Chaunceybury UT 12903-0964','Suite 763 764 Lester Track Abdulview CA 16852-5573','917 Murray Fords O-Reillyview LA 01882-5403','45585 Vania Knolls Tressastad OK 88292-8795','3950 Reynaldo Ford New Joaquin WV 30871-2347','Apt. 994 7758 Beaulah Estate Feeneyborough AL 28857','171 Thaddeus Court West Laverne MS 55977','96913 Mack Expressway North Tuanfurt CA 86894-5955','777 Claudio Lane South Jayfurt TN 61441','51816 Rikki Viaduct Shieldsfort CA 59965','227 Vance Fork Bergstromburgh MT 13122-7699','Suite 256 02963 Champlin Plaza Mooreland MO 01029-0902','3823 Exie Plaza Lebsackhaven WY 51102','978 Brown Mountains New Kipborough PA 34225-2380','Suite 385 605 Elizbeth Shore East Sarihaven CA 81335');
v_id_ales STUDENTI.ID%TYPE;
v_destinatie_aleasa VACANTA.DESTINATIE%TYPE;
v_data_aleasa VACANTA.DATA_PLECARII%TYPE;
v_cazare VACANTA.CAZARE%TYPE;
v_estimare_pret VACANTA.ESTIMARE_PRET%TYPE;
v_numar_persoane VACANTA.NUMAR_PERSOANE%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Se incepe generarea!');
FOR v_iterator IN 1..p_numar_randuri
LOOP
SELECT id INTO v_id_ales FROM (SELECT id FROM STUDENTI ORDER BY DBMS_RANDOM.RANDOM) WHERE ROWNUM=1;
v_destinatie_aleasa:=locuri_vacanta(TRUNC(DBMS_RANDOM.VALUE(0,locuri_vacanta.COUNT)+1));
v_cazare:=cazare_disponibila(TRUNC(DBMS_RANDOM.VALUE(0,cazare_disponibila.COUNT)+1));
v_data_aleasa:=TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J'),TO_CHAR(DATE '2030-12-31','J'))),'J');
v_estimare_pret:=DBMS_RANDOM.VALUE(500,20000);
v_numar_persoane:=TRUNC(DBMS_RANDOM.VALUE(5,50));
INSERT INTO VACANTA VALUEs(v_iterator,v_id_ales,v_destinatie_aleasa,v_data_aleasa,v_cazare,v_estimare_pret,v_numar_persoane);
END LOOP;
END;
BEGIN
genereaza_aleatoriu_tabela(250);
END;
COMMIT;