KXxwbmfu

1CREATE OR REPLACE PACKAGE triggers_util AS
2    PROCEDURE get_filtered(tbl_name IN VARCHAR2, scheme_name IN VARCHAR2, trg_cursor OUT SYS_REFCURSOR);
3    PROCEDURE print_format(rc IN SYS_REFCURSOR);
4END;
5/
6
7CREATE OR REPLACE PACKAGE BODY triggers_util AS
8    -- получить курсор с именами колонок и назначенных на них триггеров для запрошенного сочетания таблицы и схемы
9    PROCEDURE get_filtered(tbl_name IN VARCHAR2, scheme_name IN VARCHAR2, trg_cursor OUT SYS_REFCURSOR) AS
10    BEGIN
11        OPEN trg_cursor FOR
12            SELECT column_name, trigger_name FROM dba_trigger_cols
13            WHERE table_name LIKE tbl_name AND
14                  trigger_owner LIKE scheme_name AND
15                  table_owner LIKE scheme_name;
16    END;
17
18    -- вывести курсор с колонками и триггерами
19    PROCEDURE print_format(rc IN SYS_REFCURSOR) AS
20        names VARCHAR2(32);
21        triggers VARCHAR2(32);
22        BEGIN
23            DBMS_OUTPUT.PUT_LINE(RPAD('COLUMN NAME', 23) || ' ' || RPAD('TRIGGER NAME', 23));
24            DBMS_OUTPUT.PUT_LINE(RPAD('-', 23, '-') || ' ' || RPAD('-', 23, '-'));
25            FETCH rc INTO names, triggers;
26            IF rc%FOUND THEN
27                LOOP
28                    FETCH rc INTO names, triggers;
29                    EXIT WHEN rc%NOTFOUND;
30                    DBMS_OUTPUT.PUT_LINE(RPAD(names, 23) || ' ' || RPAD(triggers, 23));
31                END LOOP;
32            ELSE DBMS_OUTPUT.PUT_LINE('triggers for requested table and schema combination are did not exists');
33            END IF;
34        END;
35END;
36/
37
38CREATE OR REPLACE FUNCTION is_schema_exist(sch IN VARCHAR2) RETURN BOOLEAN AS
39        accepted_schemas_count NUMBER;
40    BEGIN
41        SELECT COUNT(*) INTO accepted_schemas_count FROM dba_users WHERE username = sch;
42        IF accepted_schemas_count = 0 THEN RETURN FALSE;
43        ELSE RETURN TRUE;
44        END IF;
45    END;
46/
47
48CREATE OR REPLACE FUNCTION is_table_exist(tbl IN VARCHAR2) RETURN BOOLEAN AS
49    accepted_tables_count NUMBER;
50BEGIN
51    SELECT COUNT(*) INTO accepted_tables_count FROM dba_tables WHERE table_name = tbl;
52    IF accepted_tables_count = 0 THEN RETURN FALSE;
53    ELSE RETURN TRUE;
54    END IF;
55END;
56/
57
58
59SET SERVEROUTPUT ON;
60SET FEEDBACK OFF;
61SET VERIFY OFF;
62ACCEPT s CHAR PROMPT 'Type scheme name: '
63ACCEPT t CHAR PROMPT 'Type table name: '
64DECLARE
65    result_cursor SYS_REFCURSOR;
66    scheme_name VARCHAR2(32) := '&s';
67    table_name VARCHAR2(32) := '&t';
68    scheme_input_exception EXCEPTION;
69    table_input_exception EXCEPTION;
70    schema_didnt_exist_exception EXCEPTION;
71    table_didnt_exist_exception EXCEPTION;
72BEGIN
73    IF scheme_name IS NULL THEN RAISE scheme_input_exception; END IF;
74    IF table_name IS NULL THEN RAISE table_input_exception; END IF;
75    IF is_schema_exist(scheme_name) = TRUE THEN
76       IF is_table_exist(table_name) = TRUE THEN
77           triggers_util.get_filtered(table_name, scheme_name, result_cursor);
78           triggers_util.print_format(result_cursor);
79        ELSE RAISE table_didnt_exist_exception;
80        END IF;
81    ELSE RAISE schema_didnt_exist_exception;
82    END IF;
83EXCEPTION
84    WHEN scheme_input_exception THEN DBMS_OUTPUT.PUT_LINE('Typed schema name is empty');
85    WHEN table_input_exception THEN DBMS_OUTPUT.PUT_LINE('Typed table name is empty');
86    WHEN schema_didnt_exist_exception THEN DBMS_OUTPUT.PUT_LINE('Typed schema didnt exist in database');
87    WHEN table_didnt_exist_exception THEN DBMS_OUTPUT.PUT_LINE('Typed table didnt exist in database');
88END;
89/