--dyn aufgabe no1

create or replace function calc_tab(tab in varchar2) return number as

tab_name varchar2(255) := tab;
counter number := 0;

begin

EXECUTE IMMEDIATE 'SELECT count(*) from ' || tab_name into counter;

return counter;

end;

declare
num_bert number := 0;
str_string varchar2(255) := 'customer';
begin 
num_bert := calc_tab(str_string);
dbms_output.put_line(num_bert);
end;

--dyn aufgabe no2

create or replace procedure del_tab(tab in varchar2) as

to_del_tab varchar2(255) := tab;

begin

for i in (select table_name from user_tables where table_name LIKE to_del_tab) loop
    EXECUTE IMMEDIATE 'DROP TABLE ' || i.table_name;
end loop;
end;

declare
tab_nam varchar2(255) := 'OFF%';
begin
del_tab(tab_nam);
end;

-- dyn aufgabe no3

create or replace procedure i_want_a_2(tab_name in varchar2, col_name in varchar2, val_in in number) as

tab varchar2(255) := tab_name;
col varchar2(255) := col_name;
val number := val_in;

num_bernd number := 0;
calc_bernd number := 100;

tab_vorhanden number := 0;

begin

EXECUTE IMMEDIATE 'SELECT ' || col || ' from ' || tab_name into num_bernd;

calc_bernd := calc_bernd + (val);
calc_bernd := calc_bernd / 100;

num_bernd := num_bernd * calc_bernd;

EXECUTE IMMEDIATE 'UPDATE '|| tab ||' set ' || col || ' = ' || num_bernd;

EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('WRONG INPUT, ' || sqlerrm);


end;


declare
val_tab varchar2(255) := 'CALC';
val_col varchar2(255) := 'CALC_VdAL';
val_calc number := 12;
begin
    i_want_a_2(val_tab, val_col, val_calc);
end;