00VZnN6D

1create or replace procedure lab_1(
2    in_table_name IN VARCHAR
3)
4    as
5        table_is_found NUMBER;
6        columns_amount NUMBER;
7        constr_name VARCHAR(256);
8        constr_table_name VARCHAR(256);
9        constr_column_name VARCHAR(256);
10    begin
11--      check input table name
12        if in_table_name is null or in_table_name = '' then
13            DBMS_OUTPUT.PUT_LINE('Not empty table name is needed');
14            return;
15        end if;
16
17--      check if table exists
18        table_is_found := 0;
19        select count(*) into table_is_found from ALL_TABLES tabs where tabs.TABLE_NAME = in_table_name;
20        if table_is_found = 0 then
21            DBMS_OUTPUT.PUT_LINE('Table with given name was not found');
22            return;
23        end if;
24
25--      get the amount of columns of given table
26        select count(*) into columns_amount from ALL_TAB_COLUMNS where TABLE_NAME = in_table_name;
27        if columns_amount = 0 then
28            DBMS_OUTPUT.PUT_LINE('Table "' || in_table_name || '" has no columns');
29            return;
30        end if;
31
32--      print table name and table headings
33        DBMS_OUTPUT.PUT_LINE('Таблица: ' || in_table_name);
34        DBMS_OUTPUT.NEW_LINE();
35        DBMS_OUTPUT.PUT_LINE('No. ' || RPAD('Имя столбца', 32, ' ') || 'Атрибуты');
36        DBMS_OUTPUT.PUT_LINE('---' || RPAD(' ', 32, '-') || RPAD(' ', 64, '-') );
37
38        FOR row IN (select * from ALL_TAB_COLS where TABLE_NAME = in_table_name)
39        LOOP
40            DBMS_OUTPUT.PUT_LINE(RPAD(row.COLUMN_ID, 4, ' ') || RPAD(row.COLUMN_NAME, 33, ' ') || RPAD('Type', 7, ' ') || ': ' || row.DATA_TYPE);
41
42            begin
43                select
44                    curr_table_a.CONSTRAINT_NAME,
45                    curr_table_b.TABLE_NAME,
46                    curr_table_b_columns.COLUMN_NAME
47                into
48                    constr_name,
49                    constr_table_name,
50                    constr_column_name
51                from
52                     ALL_CONSTRAINTS curr_table_a
53                join ALL_CONSTRAINTS curr_table_b on curr_table_a.R_CONSTRAINT_NAME = curr_table_b.CONSTRAINT_NAME
54                join ALL_CONS_COLUMNS curr_table_a_columns on curr_table_a_columns.CONSTRAINT_NAME = curr_table_a.CONSTRAINT_NAME
55                join ALL_CONS_COLUMNS curr_table_b_columns on curr_table_b_columns.CONSTRAINT_NAME = curr_table_b.CONSTRAINT_NAME
56                where
57                    curr_table_a.TABLE_NAME = in_table_name
58                    and curr_table_a.CONSTRAINT_TYPE = 'R'
59                    and curr_table_a_columns.COLUMN_NAME = row.COLUMN_NAME;
60            exception
61                when no_data_found then
62                    continue;
63            end;
64
65            DBMS_OUTPUT.PUT_LINE(RPAD(' ', 37) || 'Constr : ' || '"' || constr_name || '" References ' || constr_table_name || '(' || constr_column_name || ')');
66
67        END LOOP;
68    end;