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.

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.

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.

The majority of the products and their corresponding suppliers are real and are present on the market as of today.
Relationships in the table
A country can “contain” one or more suppliers (or even no suppliers at all). Each country must belong to one continent

A client can order multiple videogames in one single order, using the “LIST_ORDERS” table which memorizes the quantities ordered from each product.
One supplier can develop more videogames, which are uniquely identified by the ID “game_code”. One product can be supplied by one unique supplier.
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.
Conceptual schema of the database

Creation of the “Country” table
CREATE TABLE Country
(
id_country number(5),
country_name varchar2(30),
continent varchar2(30)
);

ALTER TABLE Country
ADD CONSTRAINT COUNTRY_PK primary key (id_country);


ALTER TABLE Country
ADD CONSTRAINT ID_COUNTRY_ISNN check(id_country IS NOT NULL);

ALTER TABLE Country
ADD CONSTRAINT CONTINENT_ISNN check (continent IS NOT NULL);


Creation of table “Suppliers”
CREATE TABLE Suppliers
(
supplier_code number(5) NOT NULL,
supplier_name varchar2(25) NOT NULL,
address varchar2(30),
email varchar2(40),
phonenumber number(10),
id_country number(5)
);


ALTER TABLE Suppliers
ADD CONSTRAINT SUPPLIERS_PK primary key (supplier_code);

ALTER TABLE Suppliers
ADD CONSTRAINT SUPPLIERS_FK foreign key (id_country) references COUNTRY (ID_COUNTRY);


Creation of GAMES table (ACTS like a PRODUCTS table)
CREATE TABLE Games
(
game_code number(5) NOT NULL,
supplier_code number(5) NOT NULL,
name varchar2(30) NOT NULL,
genre varchar2(10),
description varchar2(40),
buying_price number(8, 2)
);

ALTER TABLE Games
ADD CONSTRAINT Games_PK primary key (game_code);

ALTER TABLE Games
ADD CONSTRAINT Games_FK foreign key (supplier_code) references Suppliers (supplier_code);


Creation of Clients Table
CREATE TABLE Clients
(
id_client number(6) NOT NULL,
firstname varchar(20),
lastname varchar(20),
phonenumber number(10),
owned_games number(2),
email varchar2(40),
email2 varchar2(30),
birthdate DATE
);
ALTER TABLE Clients
DROP COLUMN email2;

ALTER TABLE Clients
MODIFY (owned_games number(5));

ALTER TABLE Clients
ADD CONSTRAINT Clients_PK primary key (id_client);

ALTER TABLE Clients
ADD CONSTRAINT ownedgames_MAX CHECK (owned_games<=15000);

ALTER TABLE Clients
ADD CONSTRAINT firstname_NN CHECK (firstname IS NOT NULL);

ALTER TABLE Clients
ADD CONSTRAINT lastname_NN CHECK (lastname IS NOT NULL);


Creation of Orders table
CREATE TABLE Orders
(
order_nr number(5) NOT NULL,
order_date DATE,
payment_method varchar2(20),
id_client number(6),
order_status varchar2(20)
);


ALTER TABLE Orders
ADD CONSTRAINT Order_NR_PK primary key (order_nr);

ALTER TABLE Orders
ADD CONSTRAINT ID_CLIENT_FK foreign key(id_client) REFERENCES Clients (id_client);

ALTER TABLE Orders
ADD CONSTRAINT DATE_NN check (order_date IS NOT NULL);

ALTER TABLE Orders
ADD CONSTRAINT ID_CLIENT_NN CHECK (id_client IS NOT NULL);

ALTER TABLE ORDERS
ADD CONSTRAINT payment_possibilities CHECK (payment_method IN ('CASH', 'CREDIT CARD', 'PAYPAL', 'EWALLET'));

CREATE TABLE dropped_table
(
name varchar2(2)
);

DROP TABLE dropped_table PURGE;


Creation of List_Orders table
CREATE TABLE List_Orders
(
order_nr number(5) NOT NULL CONSTRAINT PK_LIST primary key,
game_code number(5) NOT NULL,
price number(8,2),
quantity number(4)
);

ALTER TABLE List_Orders
ADD CONSTRAINT LIST_FK foreign key (game_code) references Games(game_code);

ALTER TABLE List_Orders
ADD CONSTRAINT List_C_FK foreign key(order_nr) references Orders(order_nr);




B) Exercises with DLL and DML (insert, update, delete, merge etc)
B1. WITHOUT SQL BLOCKS
B2. WITH SQL BLOCKS
B1. WITHOUT SQL BLOCKS
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
CREATE TABLE PRODUCT1
AS
SELECT * FROM Games
WHERE supplier_code=3010;



2)-- Sa se creeze o tabela denumita PRODUCT2 cu aceeasi structura ca a tabelei GAMES
Sa se insereze apoi inregistrari continand produsele cu preturile cuprinse intre 20 si 100 lei

CREATE TABLE PRODUCT2
AS
SELECT * FROM Games
WHERE 5=1;

INSERT INTO PRODUCT2
SELECT * FROM Games
WHERE buying_price BETWEEN 20 and 100;


3) Sa se elimine restrictia care ne interzice ca prenumele clientului sa nu fie nul (tabela CLIENTS)
ALTER TABLE Clients
DROP CONSTRAINT FIRSTNAME_NN;

4)
Sa se adauge o coloana noua denumita STOCK_PIECES la tabela GAMES

ALTER TABLE Games
ADD stock_pieces number(20);

5) Sa se stearga  coloana stock_pieces anterior create
ALTER TABLE Games
DROP COLUMN stock_pieces

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)

UPDATE Games
SET supplier_code=3005 where game_code=4004;


7) Sa se modifice data comenzii (din tabela ORDERS) a produselor din 2010 cu data actuala
UPDATE Orders set order_date = sysdate where extract(year FROM order_date)=2010;


8) Sa se modifice campurile price si quantity din tabela LIST_ORDERS pentru order_nr 1001 si 1004 incat pretul sa creasca
cu 10% si cantitatea sa creasca cu 1
UPDATE LIST_ORDERS
SET price=price*1.1, quantity=quantity+1
WHERE order_nr IN (1001, 1004);


9)
Sa se modifice order_status in tabela ORDERS din awaiting in done pentru comenzile din ani mai mici ca 2010
UPDATE Orders
set order_status='DONE' where extract (year FROM order_date)<2010;


10) Sa se actualizeze pretul jocurilor care au pretul si genul (genre) jocului de cod 4003 doar pentru produsele care au fost
comandate intr-o cantitate mai mare de 2
UPDATE Games
SET (buying_price, genre)=(SELECT buying_price, genre FROM Games WHERE game_code=4003)
WHERE game_code IN (SELECT game_code FROM LIST_ORDERS WHERE quantity=2);


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


12) Sa se stearga din LIST_ORDERS inregistrarile pentru comenzile in cantitate =8 care au minim 10 luni de
la data in care au fost comandate
DELETE FROM LIST_ORDERS
WHERE quantity=8 AND order_nr IN (SELECT order_nr FROM Orders WHERE months_between(sysdate, order_date)=10);


EXEMPLU MERGE
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

UPDATE PRODUCT2
SET buying_price=(SELECT avg(price) FROM LIST_ORDERS);

MERGE INTO PRODUCT2
USING Games
ON (product2.game_code=games.game_code)
WHEN MATCHED THEN UPDATE
SET PRODUCT2.buying_price=games.buying_price
WHEN NOT MATCHED THEN INSERT
(PRODUCT2.buying_price, PRODUCT2.game_code, PRODUCT2.supplier_code, product2.name, product2.genre, PRODUCT2.description)
values (games.buying_price, games.game_code, games.supplier_code, games.name, games.genre, games.description);





B2. WITH SQL BLOCKS
1)--Sa se creeze tabela JocuriCOPIE intr-un bloc SQL
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE jocuriCOPIE AS SELECT * FROM games WHERE 1=0';
end;
/


2)--Sa se adauge in tabela JocuriCOPIE intregistrari din tabela Games
DECLARE
v_game_code games.game_code%TYPE;
v_supplier_code games.supplier_code%TYPE;
v_name games.name%TYPE;
v_genre games.genre%TYPE;
v_description games.description%TYPE;
v_buying_price games.buying_price%TYPE;

BEGIN

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
FROM games WHERE game_code=4001;
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);
DBMS_OUTPUT.PUT_LINE('Au fost adaugate la tabela JocuriCOPIE jocurile solicitate');
END;
/

SELECT * FROM JocuriCOPIE;

3)--Sa se adauge coloana "An_aparitie" in tabela JocuriCOPIE
DECLARE
V_COMMAND VARCHAR2(200);
BEGIN
V_COMMAND:='ALTER TABLE JOCURICOPIE ADD (AN_APARTIE NUMBER (4))';
DBMS_OUTPUT.PUT_LINE (V_COMMAND);
EXECUTE IMMEDIATE V_COMMAND;
END;
/
select * from JocuriCOPIE;

4)--Sa se seteze anul aparitiei '2013' pentru toate jocurile din tabela JocuriCOPIE

BEGIN
UPDATE JocuriCOPIE
SET AN_APARTIE=2013;
END;
/
select * from JOCURICOPIE;



5 )--Sa se stearga din tabela JocuriCOPIE toate inregistrarile cu genul jocului continand cuvantul "Actiune"

DECLARE
BEGIN
DELETE FROM JocuriCOPIE WHERE initcap(genre) LIKE 'ACTION%';
ROLLBACK;
END;
/
SELECT* FROM JocuriCOPIE;

C) Exercises with decision and loop control structures (IF, CASE, LOOP, FOR)
1) --Sa se afiseze pretul modificat pentru un produs cu cod introdus de la tastatura (pentru screenshot, s-a introdus cod 4001)
DECLARE
v_pret games.buying_price%TYPE;
BEGIN
SELECT buying_price into v_pret FROM games WHERE game_code=&cod;
dbms_output.put_line('Pretul inainte de modificare era: '|| v_pret);
IF v_pret < 600 THEN
         v_pret:=1.5*v_pret;
ELSIF v_pret BETWEEN  6000 AND 1200 THEN
         v_pret:=1.25*v_pret;
ELSE
           v_pret:=1.05*v_pret;
END IF;
DBMS_OUTPUT.PUT_LINE('Pretul modificat este ' || v_pret);
END;

2)--Sa se modifice cantitate de jocuri comandate a comenzii cu id introdus de la tastatura (pentru exemplificare, s-a introdus cod 1011)

DECLARE
v_cantitate list_orders.quantity%TYPE;
BEGIN
SELECT quantity into v_cantitate FROM list_orders WHERE order_nr=&cod;
dbms_output.put_line ('Cantitatea comanda initiala pentru aceasta comanda: '||v_cantitate);

v_cantitate:=CASE WHEN v_cantitate < 2 THEN v_cantitate*4
                  WHEN v_cantitate BETWEEN 3 AND 4 THEN v_cantitate*3
                  ELSE v_cantitate*2 END;
dbms_output.put_line('Cantitatea finala pentru aceasta comanda: '||v_cantitate);
end;
/


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
DECLARE
v_buying_price games.buying_price%TYPE;
v_avg_price v_buying_price%TYPE;
i number(4):=4001;

BEGIN

SELECT AVG(buying_price) INTO v_avg_price FROM games;
dbms_output.put_line('Pretul mediu de achizitie: '||v_avg_price);
LOOP
SELECT buying_price INTO v_buying_price FROM games WHERE game_code=i;
dbms_output.put_line('Jocul cu cod '||i||' are pretul de furnizare: '||v_buying_price);
i:=i+1;
EXIT WHEN v_buying_price>v_avg_price OR i>4015;
END LOOP;
END;
/

--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

DECLARE
v_owned_games clients.owned_games%TYPE;
v_avg_owned_games v_owned_games%TYPE;

BEGIN

SELECT avg(owned_games) into v_avg_owned_games FROM clients;
dbms_output.put_line('Clientii firmei detin in medie urmatorul nr de jocuri: ' || v_avg_owned_games);
FOR i in 201..215 LOOP
SELECT owned_games into v_owned_games FROM clients where id_client=i;
dbms_output.put_line('Clientul cu cod '||i||' detine urmatorul nr de jocuri: '||v_owned_games );
EXIT when v_owned_games  < v_avg_owned_games;
END LOOP;
END;
/

CURSORS
A)	IMPLICIT
--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

DECLARE
v_nr number(2);
v_contor number(2);
BEGIN
v_contor:=0;
FOR i IN 4001..4005 LOOP
DELETE FROM GamesCopy WHERE game_code=i;
v_nr:=SQL%ROWCOUNT;
v_contor:=v_contor+v_nr;
END LOOP;
DBMS_OUTPUT.PUT_LINE('S-au sters '||v_contor||' randuri');
END;
/




2)--Sa se modifice email-ul de contact al furniorului cu cod 3007 (exista) si apoi 9999 (nu exista)
BEGIN

UPDATE Suppliers SET email='contact@valve.com' WHERE supplier_code=9999;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Nu exista niciun furnizor cu codul specificat!');
END IF;
END;
/



B)	EXPLICIT
1)--Sa se afiseze pretul si cantitatea comenzilor pentru jocul cu cod 4002 (folosing un cursor explicit)

DECLARE
CURSOR com_cursor IS SELECT order_nr, price, quantity FROM list_orders WHERE game_code=4001;
com_id list_orders.order_nr%TYPE;
com_price list_orders.price%TYPE;
com_quantity list_orders.quantity%TYPE;
BEGIN
dbms_output.put_line('Lista cu comenzile pentru jocul cu cod 4002: ');
OPEN com_cursor;
LOOP
FETCH com_cursor INTO com_id, com_price, com_quantity;
EXIT WHEN com_cursor%NOTFOUND;
dbms_output.put_line('Comanda cu nr '||com_id||' are pretul: '|| com_price || ' si cantitatea ' || com_quantity);
END LOOP;
CLOSE com_cursor;
END;
/

--Rezolvare alternative (putem folosi o variabila de tip %ROWTYPE)
DECLARE
CURSOR com_cursor IS SELECT order_nr, price, quantity FROM list_orders WHERE game_code=4001;
com_rec com_cursor%ROWTYPE;
BEGIN
dbms_output.put_line('Lista cu comenzile pentru jocul cu cod 4002: ');
OPEN com_cursor;
LOOP
FETCH com_cursor INTO com_rec;
EXIT WHEN com_cursor%NOTFOUND;
dbms_output.put_line('Comanda cu nr '||com_rec.order_nr||' are pretul: '|| com_rec.price || ' si cantitatea ' || com_rec.quantity);
END LOOP;
CLOSE com_cursor;
END;
/
2)--Sa se incarce in tabela "JOCURIABCD' primele 3 jocuri (numele si descrierea)

CREATE TABLE JOCURIABCD
( nume varchar2(30), descriere varchar2(80) );

DECLARE
v_nume Games.name%TYPE;
v_descriere Games.description%TYPE;
CURSOR c IS SELECT name, description FROM games;

BEGIN
OPEN c;
FOR i IN 1..3 LOOP
FETCH C into v_nume, v_descriere;
INSERT INTO JOCURIABCD VALUES (v_nume, v_descriere);
END LOOP;
CLOSE c;
END;
/
SELECT * FROM JOCURIABCD;

3) --Sa se afiseze top 5 clienti in functie de numarul de jocuri detinut
DECLARE
CURSOR C IS SELECT firstname, lastname, owned_games FROM Clients
ORDER BY owned_games DESC;
v_client c%ROWTYPE;
BEGIN
IF NOT c%ISOPEN then
OPEN c;
END IF;

LOOP
FETCH c into v_client;
EXIT WHEN c%NOTFOUND OR C%ROWCOUNT>10;
dbms_output.put_line(C%ROWCOUNT || ' Nume: ' || v_client.lastname || ' Prenume: ' || v_client.firstname || ' Nr jocuri detinute: ' || v_client.owned_games);
END LOOP;
dbms_output.put_line('Numar de linii: ' || c%ROWCOUNT);
close c;
END;
/


MANAGING EXCEPTION
A)	IMPLICIT
1)--Sa se afiseze tara cu codul 9999 si sa se trateze eroarea daca niciun angajat cu acest cod nu exista in tabela

DECLARE
v_countryname country.country_name%TYPE;

BEGIN
SELECT country_name INTO v_countryname FROM country
WHERE id_country=9999;
DBMS_OUTPUT.PUT_LINE(v_countryname);

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Nu exista nicio tara cu acest ID!');
END;
/

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

DECLARE

v_gen games.genre%TYPE;

BEGIN
SELECT buying_price INTO v_gen FROM games
WHERE genre='RPG';
DBMS_OUTPUT.PUT_LINE('Genul acesta are pret de cumparare: ' ||v_gen);

EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Exista mai multe jocuri de acest gen, nu unul singur!');
END;
/

3)—Sa se trateze exceptia unui cursor deschid de doua ori
DECLARE
Cursor c is SELECT supplier_code, supplier_name, address FROM suppliers order by supplier_code asc;
BEGIN
OPEN c;
for r in c loop
EXIT WHEN c%rowcount>3;
dbms_output.put_line(r.supplier_code || ' ' || r.supplier_name || ' ' || r.address);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursorul a mai fost deschid in acest bloc deja!');
END;
/

B)	Explicit

1)--Sa se trateze eroarea care apare cand nu s-au inserat in tabela list_orders toate campurile necesare/se incalca restrictia
DECLARE
exceptie_la_insertie EXCEPTION;
PRAGMA EXCEPTION_INIT (exceptie_la_insertie, -98765);

BEGIN
INSERT INTO country (id_country, country_name, continent) VALUES (1234, 1234, null);
EXCEPTION
WHEN exceptie_la_insertie THEN
DBMS_OUTPUT.PUT_LINE('Nu ati introdus corect campurile/unul din campuri are restrictie NOT NULL!');
dbms_output.put_line('Codul asociat acestei erori este:'||sqlcode|| ' cu mesajul :' || sqlerrm);
END;
/
2)	–Sa se interzica efectuarea unei actiuni dupa ora 12
DECLARE
exceptie_custom EXCEPTION;
v_var games.name%TYPE;
BEGIN

SELECT name INTO v_var FROM games WHERE game_code=4018;


IF to_number(to_char(SYSDATE, 'HH24'))>=12 THEN
RAISE exceptie_custom;
ELSE
dbms_output.put_line('The game is called: ' || v_var);
END IF;



EXCEPTION
WHEN exceptie_custom THEN
dbms_output.put_line('Aceasta operatiune se poate realiza doar '||' in timpul programului');
dbms_output.put_line('Momentan este ora '||TO_CHAR(SYSDATE, 'HH24'));
END;
/
3)	–Sa se trateze exceptia pentru cautarea unui continent inexistent
DECLARE
inexistent_continent EXCEPTION;
PRAGMA EXCEPTION_INIT (inexistent_continent, -9754);

BEGIN
UPDATE COUNTRY
SET country_name='Generic Country'
WHERE CONTINENT LIKE 'X%';

IF SQL%NOTFOUND THEN
RAISE inexistent_continent;
END IF;

EXCEPTION
WHEN inexistent_continent THEN
DBMS_OUTPUT.PUT_LINE('Continent inexistent!');
dbms_output.put_line('Codul asociat acestei erori este:'||sqlcode|| ' cu mesajul :' || sqlerrm);
END;
/

FUNCTIONS
1)--Sa se afiseze valoarea totala a comenzilor date in anul 2018

CREATE OR REPLACE FUNCTION get_value_for_year (p_year number)
RETURN NUMBER IS

v_valoare number;

BEGIN
SELECT sum(l.price*l.quantity) into v_valoare FROM LIST_ORDERS l, Orders o
WHERE l.order_nr=o.order_nr AND EXTRACT (year from order_date)=p_year;
return v_valoare;
END;
/

SET SERVEROUTPUT ON
DECLARE
totalvalue number;
BEGIN
totalvalue:=get_value_for_year(2018);
 dbms_output.put_line('Valoarea pentru anul solicitat este: ' || totalvalue);
END;
/


2)--Construiti o functie care ia id-ul unui client si returneaza valoarea totala a comenzilor de jocuri pentru clientul cu id specificat
CREATE OR REPLACE function get_total_value_for_id(id_primit number)
RETURN NUMBER IS

valoare number;
BEGIN
SELECT sum(l.price*l.quantity) total_value into valoare
FROM Orders o, list_orders l
WHERE o.order_nr=l.order_nr and o.id_client=id_primit;
return valoare;
END;
/

DECLARE
totalvaloare number;
BEGIN
totalvaloare:=get_total_value_for_id(201);
 dbms_output.put_line('Valoarea pentru id-ul solicitat este: ' || totalvaloare);
END;
/


PROCEDURES

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

CREATE OR REPLACE PROCEDURE modify_phonenumber_ownedgames(p_phonenumber IN clients.phonenumber%TYPE, cantitate_de_adaugat IN number)
IS
v_owned_games clients.owned_games%TYPE;
BEGIN
SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
dbms_output.put_line('Clientul detine un numar de jocuri de '||v_owned_games);

UPDATE Clients
SET owned_games=owned_games+cantitate_de_adaugat
WHERE phonenumber=p_phonenumber;

SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
dbms_output.put_line('Clientul detine dupa modificare un numar de jocuri de '||v_owned_games);
END;
/
show errors;



CALL modify_phonenumber_ownedgames(754123875, 9);



2) – --Sa se creeze o procedura care primeste id-ul unui client si returneaza numele si numarul de telefon al acestuia
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)
IS
BEGIN
SELECT firstname, phonenumber INTO p_firstname, p_phonenumber FROM CLIENTS
WHERE id_client=p_id_client;
DBMS_OUTPUT.PUT_LINE(' Clientul '||p_firstname||' are numarul de telefon: '||p_phonenumber);
END;
/

SET SERVEROUTPUT ON
DECLARE
v_nume clients.firstname%TYPE;
v_phonenumber clients.phonenumber%TYPE;
BEGIN
get_name_and_phone(201, v_nume, v_phonenumber);
END;
/



PACKAGE
A)	Declaration of the package
create or replace PACKAGE pachet_proiect IS

PROCEDURE modify_phonenumber_ownedgames
(p_phonenumber IN clients.phonenumber%TYPE,
cantitate_de_adaugat IN number);
--END modify_phonenumber_ownedgames;

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);

--END get_name_and_phone;

FUNCTION get_value_for_year
(p_year number)
RETURN NUMBER;
--END get_value_for_year;

function get_total_value_for_id
(id_primit number)
RETURN NUMBER;
--END et_total_value_for_id;

exceptie exception;

END;
/
----------------------------------
create or replace PACKAGE BODY pachet_proiect IS

PROCEDURE modify_phonenumber_ownedgames(p_phonenumber IN clients.phonenumber%TYPE, cantitate_de_adaugat IN number)
IS
v_owned_games clients.owned_games%TYPE;
BEGIN
SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
dbms_output.put_line('Clientul detine un numar de jocuri de '||v_owned_games);

UPDATE Clients
SET owned_games=owned_games+cantitate_de_adaugat
WHERE phonenumber=p_phonenumber;

SELECT owned_games INTO v_owned_games FROM Clients where phonenumber=p_phonenumber;
dbms_output.put_line('Clientul detine dupa modificare un numar de jocuri de '||v_owned_games);
END;



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)
IS
BEGIN
SELECT firstname, phonenumber INTO p_firstname, p_phonenumber FROM CLIENTS
WHERE id_client=p_id_client;
DBMS_OUTPUT.PUT_LINE(' Clientul '||p_firstname||' are numarul de telefon: '||p_phonenumber);
END;


FUNCTION get_value_for_year (p_year number)
RETURN NUMBER IS

v_valoare number;

BEGIN
SELECT sum(l.price*l.quantity) into v_valoare FROM LIST_ORDERS l, Orders o
WHERE l.order_nr=o.order_nr AND EXTRACT (year from order_date)=p_year;
return v_valoare;
END;


function get_total_value_for_id(id_primit number)
RETURN NUMBER IS

valoare number;
BEGIN
SELECT sum(l.price*l.quantity) total_value into valoare
FROM Orders o, list_orders l
WHERE o.order_nr=l.order_nr and o.id_client=id_primit;
return valoare;
END;




END;
/

B)	USAGE OF PACKAGE
DECLARE

totalvaloare number(10);
v_numecerut varchar(30);
v_phonenumber number(10);
valoareAn number(10);
BEGIN
totalvaloare:=pachet_proiect.get_total_value_for_id(201);
dbms_output.put_line('Valoare totala pentru id cerut ' || totalvaloare);
pachet_proiect.get_name_and_phone(201, v_numecerut, v_phonenumber);
valoareAN:=pachet_proiect.get_value_for_year(2018);
dbms_output.put_line('Valoare totala pentru anul cerut ' || totalvaloare);
pachet_proiect.modify_phonenumber_ownedgames(754123875, 1);
END;
/