Zr0pGyyt

1create or replace PACKAGE PACKAGE1 
2AUTHID CURRENT_USER
3AS 
4    PROCEDURE make(table_name VARCHAR2, columns_ VARCHAR2);
5    PROCEDURE add_row(table_name VARCHAR2, values_ VARCHAR2, cols VARCHAR2);
6    PROCEDURE upd_row(table_name VARCHAR2, value_ VARCHAR2, cond VARCHAR2 := null);
7    PROCEDURE delete_row(table_name VARCHAR2, cond VARCHAR2 := null);
8    FUNCTION remove_(table_name VARCHAR2) RETURN NUMBER;
9END PACKAGE1;
10/
11
12CREATE OR REPLACE PACKAGE BODY PACKAGE1 AS 
13
14    PROCEDURE make(table_name VARCHAR2, columns_ VARCHAR2)
15    IS
16    BEGIN
17        EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name || ' (' || columns_ || ' )';
18    EXCEPTION
19    WHEN OTHERS
20    THEN 
21        IF SQLCODE = -00955
22        THEN 
23        DBMS_OUTPUT.PUT_LINE('ERROR! Table ' || table_name || ' already exists!!!');
24        END IF;
25    END make;
26    
27    
28    
29    PROCEDURE add_row(table_name VARCHAR2, values_ VARCHAR2, cols VARCHAR2) IS
30    BEGIN
31        EXECUTE IMMEDIATE 'INSERT INTO ' || table_name || ' ( ' || cols || ' ) VALUES ( ' || values_ || ' ) ';
32    EXCEPTION
33    WHEN OTHERS
34    THEN 
35        IF SQLCODE = -00942
36        THEN 
37        DBMS_OUTPUT.PUT_LINE('ERROR! Table ' || table_name || ' does not exist!!!');
38        END IF;   
39    END add_row;
40    
41    
42    
43    PROCEDURE upd_row(table_name VARCHAR2, value_ VARCHAR2, cond VARCHAR2 := null) IS
44    BEGIN
45        IF cond IS null
46        THEN
47            EXECUTE IMMEDIATE 'UPDATE ' || table_name ||  ' SET ' || value_;
48        ELSE
49            EXECUTE IMMEDIATE 'UPDATE ' || table_name ||  ' SET ' || value_ || ' WHERE ' || cond;
50        END IF;
51    EXCEPTION
52    WHEN OTHERS
53    THEN 
54        IF SQLCODE = -00942
55        THEN 
56        DBMS_OUTPUT.PUT_LINE('ERROR! Table ' || table_name || ' does not exist!!!');
57        ELSIF SQLCODE = -00904
58        THEN
59        DBMS_OUTPUT.PUT_LINE('ERROR! Wrong column name!!!');
60        END IF;
61    END upd_row;
62    
63    
64    
65    
66    PROCEDURE delete_row(table_name VARCHAR2, cond VARCHAR2 := null) IS
67    BEGIN
68        IF cond IS null
69        THEN
70            EXECUTE IMMEDIATE 'DELETE FROM ' || table_name;
71        ELSE
72            EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE ' || cond;
73        END IF;
74    EXCEPTION
75    WHEN OTHERS
76    THEN 
77        IF SQLCODE = -00942
78        THEN 
79        DBMS_OUTPUT.PUT_LINE('ERROR! Table ' || table_name || ' does not exist!!!');
80        END IF;
81    END delete_row;
82
83
84
85    FUNCTION remove_(table_name VARCHAR2) RETURN NUMBER IS
86    deleted_rows NUMBER;
87    BEGIN
88        EXECUTE IMMEDIATE 'DELETE FROM ' || table_name;
89        deleted_rows := SQL%ROWCOUNT;
90        EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
91    RETURN deleted_rows;
92    
93    EXCEPTION
94    WHEN OTHERS
95    THEN 
96        IF SQLCODE = -00942
97        THEN 
98        DBMS_OUTPUT.PUT_LINE('ERROR! Table ' || table_name || ' does not exist!!!');
99        RETURN -1;
100        END IF;
101    END remove_;
102END;
103/