seminar sgbd 9

create or replace package  grupa_1044 is
cota_tva constant number := 19;
function get_val_cu_tva(p_val number) return number;
end;
/

set serveroutput on;

create or replace package body grupa_1044 is

function get_val_cu_tva(p_val number) return number
is
begin return p_val *( 1+ cota_tva/100);
end get_val_cu_tva;
 function get_valoare_comanda  ( p_id_client number,p_an number default 1998)
return number is
v_val number;
begin
select sum(pret*cantitate) into v_val  from rand_comenzi r join comenzi co on r.nr_comanda=co.nr_comanda    and
co.id_client = p_id_client and extract (year from data )= p_an;
return v_val;
end ;
function get_val_cu_tva2(p_val number , p_cota number ) return number
is
begin
return p_val *(1+p_cota/100*get_valoare_comanda(103));
end get_val_cu_tva2;

end grupa_1044;
/

begin
DBMS_OUTPUT.PUT_LINE ('cota standard tva : ' || grupa_1044.cota_tva);
dbms_output.put_line ( 'val cu tva standard : ' || grupa_1044.get_val_cu_tva(200));
dbms_output.put_line ( 'val cu tva 5% : ' || grupa_1044.get_val_cu_tva2(200 ,5));

end;
/
create or replace package body grupa_1044 is

function get_val_cu_tva(p_val number  ) return number


is
begin return p_val *( 1+ cota_tva/100);
end get_val_cu_tva;
 function get_valoare_comanda  ( p_id_client number,p_an number default 1998)
return number is
v_val number;
begin
select sum(pret*cantitate) into v_val  from rand_comenzi r join comenzi co on r.nr_comanda=co.nr_comanda    and
co.id_client = p_id_client and extract (year from data )= p_an;
return v_val;
end ;
end grupa_1044;


/
-- sa se construiasca un pachet ce contine o functie si o procedura si sa se apeleze functia si procedura
create or replace package  grupa_1000 is

function suma(p_val number,p_val2 number) return number;
procedure construim_tabela(f_nume_tabela varchar2);
end;
/
create or replace package body grupa_1000 is
function suma (p_val number, p_val2 number) return number
is
begin return (p_val+p_val2);
end suma;
 procedure construim_tabela( f_nume_tabela varchar2) is n number;
begin
select count(*) into n from user_tables where table_name = f_nume_tabela;
if n=0 then
execute immediate' create table' || f_nume_tabela || '(n number)';
else
execute immediate 'drop table' || f_nume_tabela;
end if;
end;

end;
/
begin
dbms_output.put_line( ' suma ' || grupa_1000.suma(10,5));
construim_tabela('abcde');
end;

/

create or replace package diaconita is
function suma( n1 number, n2 number) return number;
procedure Paste_Fericit( p_nume varchar2, p_urare OUT varchar2);
end;
/


create or replace package body diaconita is
function suma( n1 number, n2 number) return number
is begin return n1+n2;
end suma;
procedure Paste_Fericit( p_nume varchar2, p_urare OUT varchar2)
is
begin
p_urare:= 'Paste fericit' || p_nume;
end Paste_Fericit;
end;
/


declare
m varchar2(50);
begin

dbms_output.put_line( diaconita.suma(10,5));
diaconita.Paste_Fercit('Vlad',m);
dbms_output.put_line(m);
end;
/