DYNAMISCHES PL/SQL

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