SHARE
TWEET

Untitled

a guest May 23rd, 2019 167 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. The first step of this project is to create a database for a videogame store, online based. However, the way the database was conceived allows it to also be used for a real, physically based store. The products sold at our shop are videogames of different genres which are usually supplied to us by the developers at a standard price (the buying price from the Games table, the price at which we buy the product from our suppliers). In order to turn a profit, the price at which we are selling a certain product (in our case, the price from the LIST_ORDERS table) must be higher than the acquisition cost or at least equal with in order to at least break even.
  2.  
  3. The economic part of the project consists in managing two kind of prices, quantities (the acquisition price and the selling prices) but also operations such as calculating total values of orders by taking into account the variable quantities demanded by the client.
  4.  
  5. It is to be noted that the price at which one videogame is sold to a customer may differ slightly from customer to customer. Because of discount periods and regional prices, different customers may pay different prices at specific moments of time (the date at which the order was placed).  Therefore, these prices are not constant in time.
  6.  
  7. The majority of the products and their corresponding suppliers are real and are present on the market as of today.
  8. Relationships in the table
  9. A country can “contain” one or more suppliers (or even no suppliers at all). Each country must belong to one continent
  10.  
  11. A client can order multiple videogames in one single order, using the “LIST_ORDERS” table which memorizes the quantities ordered from each product.
  12. One supplier can develop more videogames, which are uniquely identified by the ID “game_code”. One product can be supplied by one unique supplier.
  13. A client can place multiple orders, at different moments of time (dates) or can add other products on an already existing order. The products place in one order will be all delivered at the same time.
  14. Conceptual schema of the database
  15.  
  16. Creation of the “Country” table
  17. CREATE TABLE Country
  18. (
  19. id_country number(5),
  20. country_name varchar2(30),
  21. continent varchar2(30)
  22. );
  23.  
  24. ALTER TABLE Country
  25. ADD CONSTRAINT COUNTRY_PK primary key (id_country);
  26.  
  27.  
  28. ALTER TABLE Country
  29. ADD CONSTRAINT ID_COUNTRY_ISNN check(id_country IS NOT NULL);
  30.  
  31. ALTER TABLE Country
  32. ADD CONSTRAINT CONTINENT_ISNN check (continent IS NOT NULL);
  33.  
  34.  
  35. Creation of table “Suppliers”
  36. CREATE TABLE Suppliers
  37. (
  38. supplier_code number(5) NOT NULL,
  39. supplier_name varchar2(25) NOT NULL,
  40. address varchar2(30),
  41. email varchar2(40),
  42. phonenumber number(10),
  43. id_country number(5)
  44. );
  45.  
  46.  
  47. ALTER TABLE Suppliers
  48. ADD CONSTRAINT SUPPLIERS_PK primary key (supplier_code);
  49.  
  50. ALTER TABLE Suppliers
  51. ADD CONSTRAINT SUPPLIERS_FK foreign key (id_country) references COUNTRY (ID_COUNTRY);
  52.  
  53.  
  54. Creation of GAMES table (ACTS like a PRODUCTS table)
  55. CREATE TABLE Games
  56. (
  57. game_code number(5) NOT NULL,
  58. supplier_code number(5) NOT NULL,
  59. name varchar2(30) NOT NULL,
  60. genre varchar2(10),
  61. description varchar2(40),
  62. buying_price number(8, 2)
  63. );
  64.  
  65. ALTER TABLE Games
  66. ADD CONSTRAINT Games_PK primary key (game_code);
  67.  
  68. ALTER TABLE Games
  69. ADD CONSTRAINT Games_FK foreign key (supplier_code) references Suppliers (supplier_code);
  70.  
  71.  
  72. Creation of Clients Table
  73. CREATE TABLE Clients
  74. (
  75. id_client number(6) NOT NULL,
  76. firstname varchar(20),
  77. lastname varchar(20),
  78. phonenumber number(10),
  79. owned_games number(2),
  80. email varchar2(40),
  81. email2 varchar2(30),
  82. birthdate DATE
  83. );
  84. ALTER TABLE Clients
  85. DROP COLUMN email2;
  86.  
  87. ALTER TABLE Clients
  88. MODIFY (owned_games number(5));
  89.  
  90. ALTER TABLE Clients
  91. ADD CONSTRAINT Clients_PK primary key (id_client);
  92.  
  93. ALTER TABLE Clients
  94. ADD CONSTRAINT ownedgames_MAX CHECK (owned_games<=15000);
  95.  
  96. ALTER TABLE Clients
  97. ADD CONSTRAINT firstname_NN CHECK (firstname IS NOT NULL);
  98.  
  99. ALTER TABLE Clients
  100. ADD CONSTRAINT lastname_NN CHECK (lastname IS NOT NULL);
  101.  
  102.  
  103. Creation of Orders table
  104. CREATE TABLE Orders
  105. (
  106. order_nr number(5) NOT NULL,
  107. order_date DATE,
  108. payment_method varchar2(20),
  109. id_client number(6),
  110. order_status varchar2(20)
  111. );
  112.  
  113.  
  114. ALTER TABLE Orders
  115. ADD CONSTRAINT Order_NR_PK primary key (order_nr);
  116.  
  117. ALTER TABLE Orders
  118. ADD CONSTRAINT ID_CLIENT_FK foreign key(id_client) REFERENCES Clients (id_client);
  119.  
  120. ALTER TABLE Orders
  121. ADD CONSTRAINT DATE_NN check (order_date IS NOT NULL);
  122.  
  123. ALTER TABLE Orders
  124. ADD CONSTRAINT ID_CLIENT_NN CHECK (id_client IS NOT NULL);
  125.  
  126. ALTER TABLE ORDERS
  127. ADD CONSTRAINT payment_possibilities CHECK (payment_method IN ('CASH', 'CREDIT CARD', 'PAYPAL', 'EWALLET'));
  128.  
  129. CREATE TABLE dropped_table
  130. (
  131. name varchar2(2)
  132. );
  133.  
  134. DROP TABLE dropped_table PURGE;
  135.  
  136.  
  137. Creation of List_Orders table
  138. CREATE TABLE List_Orders
  139. (
  140. order_nr number(5) NOT NULL CONSTRAINT PK_LIST primary key,
  141. game_code number(5) NOT NULL,
  142. price number(8,2),
  143. quantity number(4)
  144. );
  145.  
  146. ALTER TABLE List_Orders
  147. ADD CONSTRAINT LIST_FK foreign key (game_code) references Games(game_code);
  148.  
  149. ALTER TABLE List_Orders
  150. ADD CONSTRAINT List_C_FK foreign key(order_nr) references Orders(order_nr);
  151.  
  152.  
  153.  
  154.  
  155. B) Exercises with DLL and DML (insert, update, delete, merge etc)
  156. B1. WITHOUT SQL BLOCKS
  157. B2. WITH SQL BLOCKS
  158. B1. WITHOUT SQL BLOCKS
  159. 1)  -- Sa se creeze o tablea noua numita PRODUCT1 care va avea aceeasi structura ca tabela GAMES si care va contine informatii despre produsele de la furnizorul cu codul 3010
  160. CREATE TABLE PRODUCT1
  161. AS
  162. SELECT * FROM Games
  163. WHERE supplier_code=3010;
  164.  
  165.  
  166.  
  167. 2)-- Sa se creeze o tabela denumita PRODUCT2 cu aceeasi structura ca a tabelei GAMES
  168. Sa se insereze apoi inregistrari continand produsele cu preturile cuprinse intre 20 si 100 lei
  169.  
  170. CREATE TABLE PRODUCT2
  171. AS
  172. SELECT * FROM Games
  173. WHERE 5=1;
  174.  
  175. INSERT INTO PRODUCT2
  176. SELECT * FROM Games
  177. WHERE buying_price BETWEEN 20 and 100;
  178.  
  179.  
  180. 3) Sa se elimine restrictia care ne interzice ca prenumele clientului sa nu fie nul (tabela CLIENTS)
  181. ALTER TABLE Clients
  182. DROP CONSTRAINT FIRSTNAME_NN;
  183.  
  184. 4)
  185. Sa se adauge o coloana noua denumita STOCK_PIECES la tabela GAMES
  186.  
  187. ALTER TABLE Games
  188. ADD stock_pieces number(20);
  189.  
  190. 5) Sa se stearga  coloana stock_pieces anterior create
  191. ALTER TABLE Games
  192. DROP COLUMN stock_pieces
  193.  
  194. 6) Sa se modifice campul supplier_code din tabela GAMES pentru articolul care are codul 4004, dandu-i valoarea 3005 (screenshot cu tabelele inainte si dupa modificare)
  195.  
  196. UPDATE Games
  197. SET supplier_code=3005 where game_code=4004;
  198.  
  199.  
  200. 7) Sa se modifice data comenzii (din tabela ORDERS) a produselor din 2010 cu data actuala
  201. UPDATE Orders set order_date = sysdate where extract(year FROM order_date)=2010;
  202.  
  203.  
  204. 8) Sa se modifice campurile price si quantity din tabela LIST_ORDERS pentru order_nr 1001 si 1004 incat pretul sa creasca
  205. cu 10% si cantitatea sa creasca cu 1
  206. UPDATE LIST_ORDERS
  207. SET price=price*1.1, quantity=quantity+1
  208. WHERE order_nr IN (1001, 1004);
  209.  
  210.  
  211. 9)
  212. Sa se modifice order_status in tabela ORDERS din awaiting in done pentru comenzile din ani mai mici ca 2010
  213. UPDATE Orders
  214. set order_status='DONE' where extract (year FROM order_date)<2010;
  215.  
  216.  
  217. 10) Sa se actualizeze pretul jocurilor care au pretul si genul (genre) jocului de cod 4003 doar pentru produsele care au fost
  218. comandate intr-o cantitate mai mare de 2
  219. UPDATE Games
  220. SET (buying_price, genre)=(SELECT buying_price, genre FROM Games WHERE game_code=4003)
  221. WHERE game_code IN (SELECT game_code FROM LIST_ORDERS WHERE quantity=2);
  222.  
  223.  
  224. 11) Sa se actualizeze numarul de jocuri detinute (owned_games) a clientilor cu nume de familie (lastname) care incep cu litera G cu numarul de jocuri detinute de clientul cu ID 215
  225.  
  226.  
  227. 12) Sa se stearga din LIST_ORDERS inregistrarile pentru comenzile in cantitate =8 care au minim 10 luni de
  228. la data in care au fost comandate
  229. DELETE FROM LIST_ORDERS
  230. WHERE quantity=8 AND order_nr IN (SELECT order_nr FROM Orders WHERE months_between(sysdate, order_date)=10);
  231.  
  232.  
  233. EXEMPLU MERGE
  234. 13) Sa se actualizeze tabela anterior creata, PRODUCT2 incat buying_price sa ia valoarea pretului mediu din tabela LIST_ORDERS. Mai apoi se vor actualiza datele prin compararea PRODUCT2 cu Games astfel incat datele celor doua tabele sa fie identice
  235.  
  236. UPDATE PRODUCT2
  237. SET buying_price=(SELECT avg(price) FROM LIST_ORDERS);
  238.  
  239. MERGE INTO PRODUCT2
  240. USING Games
  241. ON (product2.game_code=games.game_code)
  242. WHEN MATCHED THEN UPDATE
  243. SET PRODUCT2.buying_price=games.buying_price
  244. WHEN NOT MATCHED THEN INSERT
  245. (PRODUCT2.buying_price, PRODUCT2.game_code, PRODUCT2.supplier_code, product2.name, product2.genre, PRODUCT2.description)
  246. values (games.buying_price, games.game_code, games.supplier_code, games.name, games.genre, games.description);
  247.  
  248.  
  249.  
  250.  
  251.  
  252. B2. WITH SQL BLOCKS
  253. 1)--Sa se creeze tabela JocuriCOPIE intr-un bloc SQL
  254. BEGIN
  255. EXECUTE IMMEDIATE 'CREATE TABLE jocuriCOPIE AS SELECT * FROM games WHERE 1=0';
  256. end;
  257. /
  258.  
  259.  
  260. 2)--Sa se adauge in tabela JocuriCOPIE intregistrari din tabela Games
  261. DECLARE
  262. v_game_code games.game_code%TYPE;
  263. v_supplier_code games.supplier_code%TYPE;
  264. v_name games.name%TYPE;
  265. v_genre games.genre%TYPE;
  266. v_description games.description%TYPE;
  267. v_buying_price games.buying_price%TYPE;
  268.  
  269. BEGIN
  270.  
  271. SELECT game_code, supplier_code, name, genre, description, buying_price INTO v_game_code, v_supplier_code, v_name, v_genre, v_description, v_buying_price
  272. FROM games WHERE game_code=4001;
  273. INSERT INTO JocuriCOPIE (game_code, supplier_code, name, genre, description, buying_price) VALUES (v_game_code, v_supplier_code, v_name, v_genre, v_description, v_buying_price);
  274. DBMS_OUTPUT.PUT_LINE('Au fost adaugate la tabela JocuriCOPIE jocurile solicitate');
  275. END;
  276. /
  277.  
  278. SELECT * FROM JocuriCOPIE;
  279.  
  280. 3)--Sa se adauge coloana "An_aparitie" in tabela JocuriCOPIE
  281. DECLARE
  282. V_COMMAND VARCHAR2(200);
  283. BEGIN
  284. V_COMMAND:='ALTER TABLE JOCURICOPIE ADD (AN_APARTIE NUMBER (4))';
  285. DBMS_OUTPUT.PUT_LINE (V_COMMAND);
  286. EXECUTE IMMEDIATE V_COMMAND;
  287. END;
  288. /
  289. select * from JocuriCOPIE;
  290.  
  291. 4)--Sa se seteze anul aparitiei '2013' pentru toate jocurile din tabela JocuriCOPIE
  292.  
  293. BEGIN
  294. UPDATE JocuriCOPIE
  295. SET AN_APARTIE=2013;
  296. END;
  297. /
  298. select * from JOCURICOPIE;
  299.  
  300.  
  301.  
  302. 5 )--Sa se stearga din tabela JocuriCOPIE toate inregistrarile cu genul jocului continand cuvantul "Actiune"
  303.  
  304. DECLARE
  305. BEGIN
  306. DELETE FROM JocuriCOPIE WHERE initcap(genre) LIKE 'ACTION%';
  307. ROLLBACK;
  308. END;
  309. /
  310. SELECT* FROM JocuriCOPIE;
  311.  
  312. C) Exercises with decision and loop control structures (IF, CASE, LOOP, FOR)
  313. 1) --Sa se afiseze pretul modificat pentru un produs cu cod introdus de la tastatura (pentru screenshot, s-a introdus cod 4001)
  314. DECLARE
  315. v_pret games.buying_price%TYPE;
  316. BEGIN
  317. SELECT buying_price into v_pret FROM games WHERE game_code=&cod;
  318. dbms_output.put_line('Pretul inainte de modificare era: '|| v_pret);
  319. IF v_pret < 600 THEN
  320.          v_pret:=1.5*v_pret;
  321. ELSIF v_pret BETWEEN  6000 AND 1200 THEN
  322.          v_pret:=1.25*v_pret;
  323. ELSE
  324.            v_pret:=1.05*v_pret;
  325. END IF;
  326. DBMS_OUTPUT.PUT_LINE('Pretul modificat este ' || v_pret);
  327. END;
  328.  
  329. 2)--Sa se modifice cantitate de jocuri comandate a comenzii cu id introdus de la tastatura (pentru exemplificare, s-a introdus cod 1011)
  330.  
  331. DECLARE
  332. v_cantitate list_orders.quantity%TYPE;
  333. BEGIN
  334. SELECT quantity into v_cantitate FROM list_orders WHERE order_nr=&cod;
  335. dbms_output.put_line ('Cantitatea comanda initiala pentru aceasta comanda: '||v_cantitate);
  336.  
  337. v_cantitate:=CASE WHEN v_cantitate < 2 THEN v_cantitate*4
  338.                   WHEN v_cantitate BETWEEN 3 AND 4 THEN v_cantitate*3
  339.                   ELSE v_cantitate*2 END;
  340. dbms_output.put_line('Cantitatea finala pentru aceasta comanda: '||v_cantitate);
  341. end;
  342. /
  343.  
  344.  
  345. 3)--Sa se afiseze ordonat jocurile cu codurile in intervalul 4001-4015 cat timp pretul de furnizare (buying price) este mai mare decat media preturilor
  346. DECLARE
  347. v_buying_price games.buying_price%TYPE;
  348. v_avg_price v_buying_price%TYPE;
  349. i number(4):=4001;
  350.  
  351. BEGIN
  352.  
  353. SELECT AVG(buying_price) INTO v_avg_price FROM games;
  354. dbms_output.put_line('Pretul mediu de achizitie: '||v_avg_price);
  355. LOOP
  356. SELECT buying_price INTO v_buying_price FROM games WHERE game_code=i;
  357. dbms_output.put_line('Jocul cu cod '||i||' are pretul de furnizare: '||v_buying_price);
  358. i:=i+1;
  359. EXIT WHEN v_buying_price>v_avg_price OR i>4015;
  360. END LOOP;
  361. END;
  362. /
  363.  
  364. --Sa se afiseze ordonat clientii cu id-uri intre 201 si 215 atat timp cat numarul de jocuri detinute este mai mic decat media totala
  365.  
  366. DECLARE
  367. v_owned_games clients.owned_games%TYPE;
  368. v_avg_owned_games v_owned_games%TYPE;
  369.  
  370. BEGIN
  371.  
  372. SELECT avg(owned_games) into v_avg_owned_games FROM clients;
  373. dbms_output.put_line('Clientii firmei detin in medie urmatorul nr de jocuri: ' || v_avg_owned_games);
  374. FOR i in 201..215 LOOP
  375. SELECT owned_games into v_owned_games FROM clients where id_client=i;
  376. dbms_output.put_line('Clientul cu cod '||i||' detine urmatorul nr de jocuri: '||v_owned_games );
  377. EXIT when v_owned_games  < v_avg_owned_games;
  378. END LOOP;
  379. END;
  380. /
  381.  
  382. CURSORS
  383. A)  IMPLICIT
  384. --Sa se stearga din tabela GamesCopy (copie cu tot cu date a tabelei Games) toate randurile care au codurile cuprinse intre 4001 si 4005 si sa se numere randurile sterse
  385.  
  386. DECLARE
  387. v_nr number(2);
  388. v_contor number(2);
  389. BEGIN
  390. v_contor:=0;
  391. FOR i IN 4001..4005 LOOP
  392. DELETE FROM GamesCopy WHERE game_code=i;
  393. v_nr:=SQL%ROWCOUNT;
  394. v_contor:=v_contor+v_nr;
  395. END LOOP;
  396. DBMS_OUTPUT.PUT_LINE('S-au sters '||v_contor||' randuri');
  397. END;
  398. /
  399.  
  400.  
  401.  
  402.  
  403. 2)--Sa se modifice email-ul de contact al furniorului cu cod 3007 (exista) si apoi 9999 (nu exista)
  404. BEGIN
  405.  
  406. UPDATE Suppliers SET email='contact@valve.com' WHERE supplier_code=9999;
  407. IF SQL%NOTFOUND THEN
  408. DBMS_OUTPUT.PUT_LINE('Nu exista niciun furnizor cu codul specificat!');
  409. END IF;
  410. END;
  411. /
  412.  
  413.  
  414.  
  415. B)  EXPLICIT
  416. 1)--Sa se afiseze pretul si cantitatea comenzilor pentru jocul cu cod 4002 (folosing un cursor explicit)
  417.  
  418. DECLARE
  419. CURSOR com_cursor IS SELECT order_nr, price, quantity FROM list_orders WHERE game_code=4001;
  420. com_id list_orders.order_nr%TYPE;
  421. com_price list_orders.price%TYPE;
  422. com_quantity list_orders.quantity%TYPE;
  423. BEGIN
  424. dbms_output.put_line('Lista cu comenzile pentru jocul cu cod 4002: ');
  425. OPEN com_cursor;
  426. LOOP
  427. FETCH com_cursor INTO com_id, com_price, com_quantity;
  428. EXIT WHEN com_cursor%NOTFOUND;
  429. dbms_output.put_line('Comanda cu nr '||com_id||' are pretul: '|| com_price || ' si cantitatea ' || com_quantity);
  430. END LOOP;
  431. CLOSE com_cursor;
  432. END;
  433. /
  434.  
  435. --Rezolvare alternative (putem folosi o variabila de tip %ROWTYPE)
  436. DECLARE
  437. CURSOR com_cursor IS SELECT order_nr, price, quantity FROM list_orders WHERE game_code=4001;
  438. com_rec com_cursor%ROWTYPE;
  439. BEGIN
  440. dbms_output.put_line('Lista cu comenzile pentru jocul cu cod 4002: ');
  441. OPEN com_cursor;
  442. LOOP
  443. FETCH com_cursor INTO com_rec;
  444. EXIT WHEN com_cursor%NOTFOUND;
  445. dbms_output.put_line('Comanda cu nr '||com_rec.order_nr||' are pretul: '|| com_rec.price || ' si cantitatea ' || com_rec.quantity);
  446. END LOOP;
  447. CLOSE com_cursor;
  448. END;
  449. /
  450. 2)--Sa se incarce in tabela "JOCURIABCD' primele 3 jocuri (numele si descrierea)
  451.  
  452. CREATE TABLE JOCURIABCD
  453. ( nume varchar2(30), descriere varchar2(80) );
  454.  
  455. DECLARE
  456. v_nume Games.name%TYPE;
  457. v_descriere Games.description%TYPE;
  458. CURSOR c IS SELECT name, description FROM games;
  459.  
  460. BEGIN
  461. OPEN c;
  462. FOR i IN 1..3 LOOP
  463. FETCH C into v_nume, v_descriere;
  464. INSERT INTO JOCURIABCD VALUES (v_nume, v_descriere);
  465. END LOOP;
  466. CLOSE c;
  467. END;
  468. /
  469. SELECT * FROM JOCURIABCD;
  470.  
  471. 3) --Sa se afiseze top 5 clienti in functie de numarul de jocuri detinut
  472. DECLARE
  473. CURSOR C IS SELECT firstname, lastname, owned_games FROM Clients
  474. ORDER BY owned_games DESC;
  475. v_client c%ROWTYPE;
  476. BEGIN
  477. IF NOT c%ISOPEN then
  478. OPEN c;
  479. END IF;
  480.  
  481. LOOP
  482. FETCH c into v_client;
  483. EXIT WHEN c%NOTFOUND OR C%ROWCOUNT>10;
  484. dbms_output.put_line(C%ROWCOUNT || ' Nume: ' || v_client.lastname || ' Prenume: ' || v_client.firstname || ' Nr jocuri detinute: ' || v_client.owned_games);
  485. END LOOP;
  486. dbms_output.put_line('Numar de linii: ' || c%ROWCOUNT);
  487. close c;
  488. END;
  489. /
  490.  
  491.  
  492. MANAGING EXCEPTION
  493. A)  IMPLICIT
  494. 1)--Sa se afiseze tara cu codul 9999 si sa se trateze eroarea daca niciun angajat cu acest cod nu exista in tabela
  495.  
  496. DECLARE
  497. v_countryname country.country_name%TYPE;
  498.  
  499. BEGIN
  500. SELECT country_name INTO v_countryname FROM country
  501. WHERE id_country=9999;
  502. DBMS_OUTPUT.PUT_LINE(v_countryname);
  503.  
  504. EXCEPTION
  505. WHEN NO_DATA_FOUND THEN
  506. dbms_output.put_line('Nu exista nicio tara cu acest ID!');
  507. END;
  508. /
  509.  
  510. 2)--Sa se afiseze pretul de aprovizionare (buying_price) pentru jocurile de gen "RPG". Daca exista mai multe jocuri cu acest gen, sa se trateze eroarea
  511.  
  512. DECLARE
  513.  
  514. v_gen games.genre%TYPE;
  515.  
  516. BEGIN
  517. SELECT buying_price INTO v_gen FROM games
  518. WHERE genre='RPG';
  519. DBMS_OUTPUT.PUT_LINE('Genul acesta are pret de cumparare: ' ||v_gen);
  520.  
  521. EXCEPTION
  522. WHEN TOO_MANY_ROWS THEN
  523. DBMS_OUTPUT.PUT_LINE('Exista mai multe jocuri de acest gen, nu unul singur!');
  524. END;
  525. /
  526.  
  527. 3)—Sa se trateze exceptia unui cursor deschid de doua ori
  528. DECLARE
  529. Cursor c is SELECT supplier_code, supplier_name, address FROM suppliers order by supplier_code asc;
  530. BEGIN
  531. OPEN c;
  532. for r in c loop
  533. EXIT WHEN c%rowcount>3;
  534. dbms_output.put_line(r.supplier_code || ' ' || r.supplier_name || ' ' || r.address);
  535. END LOOP;
  536. EXCEPTION
  537. WHEN CURSOR_ALREADY_OPEN THEN
  538. DBMS_OUTPUT.PUT_LINE('Cursorul a mai fost deschid in acest bloc deja!');
  539. END;
  540. /
  541.  
  542. B)  Explicit
  543.  
  544. 1)--Sa se trateze eroarea care apare cand nu s-au inserat in tabela list_orders toate campurile necesare/se incalca restrictia
  545. DECLARE
  546. exceptie_la_insertie EXCEPTION;
  547. PRAGMA EXCEPTION_INIT (exceptie_la_insertie, -98765);
  548.  
  549. BEGIN
  550. INSERT INTO country (id_country, country_name, continent) VALUES (1234, 1234, null);
  551. EXCEPTION
  552. WHEN exceptie_la_insertie THEN
  553. DBMS_OUTPUT.PUT_LINE('Nu ati introdus corect campurile/unul din campuri are restrictie NOT NULL!');
  554. dbms_output.put_line('Codul asociat acestei erori este:'||sqlcode|| ' cu mesajul :' || sqlerrm);
  555. END;
  556. /
  557. 2)  –Sa se interzica efectuarea unei actiuni dupa ora 12
  558. DECLARE
  559. exceptie_custom EXCEPTION;
  560. v_var games.name%TYPE;
  561. BEGIN
  562.  
  563. SELECT name INTO v_var FROM games WHERE game_code=4018;
  564.  
  565.  
  566. IF to_number(to_char(SYSDATE, 'HH24'))>=12 THEN
  567. RAISE exceptie_custom;
  568. ELSE
  569. dbms_output.put_line('The game is called: ' || v_var);
  570. END IF;
  571.  
  572.  
  573.  
  574. EXCEPTION
  575. WHEN exceptie_custom THEN
  576. dbms_output.put_line('Aceasta operatiune se poate realiza doar '||' in timpul programului');
  577. dbms_output.put_line('Momentan este ora '||TO_CHAR(SYSDATE, 'HH24'));
  578. END;
  579. /
  580. 3)  –Sa se trateze exceptia pentru cautarea unui continent inexistent
  581. DECLARE
  582. inexistent_continent EXCEPTION;
  583. PRAGMA EXCEPTION_INIT (inexistent_continent, -9754);
  584.  
  585. BEGIN
  586. UPDATE COUNTRY
  587. SET country_name='Generic Country'
  588. WHERE CONTINENT LIKE 'X%';
  589.  
  590. IF SQL%NOTFOUND THEN
  591. RAISE inexistent_continent;
  592. END IF;
  593.  
  594. EXCEPTION
  595. WHEN inexistent_continent THEN
  596. DBMS_OUTPUT.PUT_LINE('Continent inexistent!');
  597. dbms_output.put_line('Codul asociat acestei erori este:'||sqlcode|| ' cu mesajul :' || sqlerrm);
  598. END;
  599. /
  600.  
  601. FUNCTIONS
  602. 1)--Sa se afiseze valoarea totala a comenzilor date in anul 2018
  603.  
  604. CREATE OR REPLACE FUNCTION get_value_for_year (p_year number)
  605. RETURN NUMBER IS
  606.  
  607. v_valoare number;
  608.  
  609. BEGIN
  610. SELECT sum(l.price*l.quantity) into v_valoare FROM LIST_ORDERS l, Orders o
  611. WHERE l.order_nr=o.order_nr AND EXTRACT (year from order_date)=p_year;
  612. return v_valoare;
  613. END;
  614. /
  615.  
  616. SET SERVEROUTPUT ON
  617. DECLARE
  618. totalvalue number;
  619. BEGIN
  620. totalvalue:=get_value_for_year(2018);
  621.  dbms_output.put_line('Valoarea pentru anul solicitat este: ' || totalvalue);
  622. END;
  623. /
  624.  
  625.  
  626. 2)--Construiti o functie care ia id-ul unui client si returneaza valoarea totala a comenzilor de jocuri pentru clientul cu id specificat
  627. CREATE OR REPLACE function get_total_value_for_id(id_primit number)
  628. RETURN NUMBER IS
  629.  
  630. valoare number;
  631. BEGIN
  632. SELECT sum(l.price*l.quantity) total_value into valoare
  633. FROM Orders o, list_orders l
  634. WHERE o.order_nr=l.order_nr and o.id_client=id_primit;
  635. return valoare;
  636. END;
  637. /
  638.  
  639. DECLARE
  640. totalvaloare number;
  641. BEGIN
  642. totalvaloare:=get_total_value_for_id(201);
  643.  dbms_output.put_line('Valoarea pentru id-ul solicitat este: ' || totalvaloare);
  644. END;
  645. /
  646.  
  647.  
  648. PROCEDURES
  649.  
  650. 1)--Sa se creeze o procedura care mareste numarul de jocuri detinut de un client cu un anumit numar de telefon cu un numar arbitrar de unitati
  651.  
  652. CREATE OR REPLACE PROCEDURE modify_phonenumber_ownedgames(p_phonenumber IN clients.phonenumber%TYPE, cantitate_de_adaugat IN number)
  653. IS
  654. v_owned_games clients.owned_games%TYPE;
  655. BEGIN
  656. SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
  657. dbms_output.put_line('Clientul detine un numar de jocuri de '||v_owned_games);
  658.  
  659. UPDATE Clients
  660. SET owned_games=owned_games+cantitate_de_adaugat
  661. WHERE phonenumber=p_phonenumber;
  662.  
  663. SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
  664. dbms_output.put_line('Clientul detine dupa modificare un numar de jocuri de '||v_owned_games);
  665. END;
  666. /
  667. show errors;
  668.  
  669.  
  670.  
  671. CALL modify_phonenumber_ownedgames(754123875, 9);
  672.  
  673.  
  674.  
  675. 2) – --Sa se creeze o procedura care primeste id-ul unui client si returneaza numele si numarul de telefon al acestuia
  676. CREATE OR REPLACE PROCEDURE get_name_and_phone(p_id_client IN clients.id_client%TYPE, p_firstname OUT clients.firstname%TYPE, p_phonenumber OUT clients.phonenumber%TYPE)
  677. IS
  678. BEGIN
  679. SELECT firstname, phonenumber INTO p_firstname, p_phonenumber FROM CLIENTS
  680. WHERE id_client=p_id_client;
  681. DBMS_OUTPUT.PUT_LINE(' Clientul '||p_firstname||' are numarul de telefon: '||p_phonenumber);
  682. END;
  683. /
  684.  
  685. SET SERVEROUTPUT ON
  686. DECLARE
  687. v_nume clients.firstname%TYPE;
  688. v_phonenumber clients.phonenumber%TYPE;
  689. BEGIN
  690. get_name_and_phone(201, v_nume, v_phonenumber);
  691. END;
  692. /
  693.  
  694.  
  695.  
  696. PACKAGE
  697. A)  Declaration of the package
  698. create or replace PACKAGE pachet_proiect IS
  699.  
  700. PROCEDURE modify_phonenumber_ownedgames
  701. (p_phonenumber IN clients.phonenumber%TYPE,
  702. cantitate_de_adaugat IN number);
  703. --END modify_phonenumber_ownedgames;
  704.  
  705. PROCEDURE get_name_and_phone
  706. (p_id_client IN clients.id_client%TYPE,
  707. p_firstname OUT clients.firstname%TYPE,
  708. p_phonenumber OUT clients.phonenumber%TYPE);
  709.  
  710. --END get_name_and_phone;
  711.  
  712. FUNCTION get_value_for_year
  713. (p_year number)
  714. RETURN NUMBER;
  715. --END get_value_for_year;
  716.  
  717. function get_total_value_for_id
  718. (id_primit number)
  719. RETURN NUMBER;
  720. --END et_total_value_for_id;
  721.  
  722. exceptie exception;
  723.  
  724. END;
  725. /
  726. ----------------------------------
  727. create or replace PACKAGE BODY pachet_proiect IS
  728.  
  729. PROCEDURE modify_phonenumber_ownedgames(p_phonenumber IN clients.phonenumber%TYPE, cantitate_de_adaugat IN number)
  730. IS
  731. v_owned_games clients.owned_games%TYPE;
  732. BEGIN
  733. SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
  734. dbms_output.put_line('Clientul detine un numar de jocuri de '||v_owned_games);
  735.  
  736. UPDATE Clients
  737. SET owned_games=owned_games+cantitate_de_adaugat
  738. WHERE phonenumber=p_phonenumber;
  739.  
  740. SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
  741. dbms_output.put_line('Clientul detine dupa modificare un numar de jocuri de '||v_owned_games);
  742. END;
  743.  
  744.  
  745.  
  746. PROCEDURE get_name_and_phone(p_id_client IN clients.id_client%TYPE, p_firstname OUT clients.firstname%TYPE, p_phonenumber OUT clients.phonenumber%TYPE)
  747. IS
  748. BEGIN
  749. SELECT firstname, phonenumber INTO p_firstname, p_phonenumber FROM CLIENTS
  750. WHERE id_client=p_id_client;
  751. DBMS_OUTPUT.PUT_LINE(' Clientul '||p_firstname||' are numarul de telefon: '||p_phonenumber);
  752. END;
  753.  
  754.  
  755. FUNCTION get_value_for_year (p_year number)
  756. RETURN NUMBER IS
  757.  
  758. v_valoare number;
  759.  
  760. BEGIN
  761. SELECT sum(l.price*l.quantity) into v_valoare FROM LIST_ORDERS l, Orders o
  762. WHERE l.order_nr=o.order_nr AND EXTRACT (year from order_date)=p_year;
  763. return v_valoare;
  764. END;
  765.  
  766.  
  767. function get_total_value_for_id(id_primit number)
  768. RETURN NUMBER IS
  769.  
  770. valoare number;
  771. BEGIN
  772. SELECT sum(l.price*l.quantity) total_value into valoare
  773. FROM Orders o, list_orders l
  774. WHERE o.order_nr=l.order_nr and o.id_client=id_primit;
  775. return valoare;
  776. END;
  777.  
  778.  
  779.  
  780.  
  781. END;
  782. /
  783.  
  784. B)  USAGE OF PACKAGE
  785. DECLARE
  786.  
  787. totalvaloare number(10);
  788. v_numecerut varchar(30);
  789. v_phonenumber number(10);
  790. valoareAn number(10);
  791. BEGIN
  792. totalvaloare:=pachet_proiect.get_total_value_for_id(201);
  793. dbms_output.put_line('Valoare totala pentru id cerut ' || totalvaloare);
  794. pachet_proiect.get_name_and_phone(201, v_numecerut, v_phonenumber);
  795. valoareAN:=pachet_proiect.get_value_for_year(2018);
  796. dbms_output.put_line('Valoare totala pentru anul cerut ' || totalvaloare);
  797. pachet_proiect.modify_phonenumber_ownedgames(754123875, 1);
  798. END;
  799. /
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