Pasted

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