# СУБД

### Лабораторная работа №1

sqplus>

Ширина вывода
> set linesize 600;

Отключает игнорирование пробелов спереди
> SET SERVEROUTPUT ON FORMAT WRAPPED;

> set

Первая версия запроса (завернуть в функцию)

select COLUMN_ID as "No.", COLUMN_NAME as "Имя столбца", ('Type: ' || DATA_TYPE || CHR(10) || 'COMMEN: ' || CHR(10) || '' || 'Constraint: ' || '') as "Атрибуты" from USER_TAB_COLUMNS;

Вторая версия

select COLUMN_ID as "No.", COLUMN_NAME as "Имя столбца", ('Type: ' || DATA_TYPE || CHR(10) || 'COMMEN: ' || CHR(10) || '' || 'Constraint: ' || '') as "Атрибуты" from ALL_TAB_COLUMNS WHERE TABLE_NAME = 'Н_УЧЕНИКИ';

Вариант с коментариями

select atc.COLUMN_ID as "No.", atc.COLUMN_NAME as "Имя столбца", ('Type: ' || atc.DATA_TYPE || CHR(10) || 'COMMEN: ' || dcc.COMMENTS || CHR(10) || '' || 'Constraint: ' || '') as "Атрибуты"
from ALL_TAB_COLUMNS atc join DBA_COL_COMMENTS dcc on atc.TABLE_NAME = dcc.TABLE_NAME and atc.COLUMN_NAME = dcc.COLUMN_NAME
WHERE atc.TABLE_NAME = 'Н_УЧЕНИКИ';

Процедура

CREATE OR REPLACE PACKAGE s223860.lab1 IS
  TYPE rowRec IS RECORD
  (
    "No." NUMBER,
    "Имя столбца" VARCHAR2(30),
    "Атрибуты" VARCHAR2(134)
  );

  TYPE tableRec IS TABLE OF rowRec;

  FUNCTION getInfo RETURN tableRec PIPELINED ;
  END lab1;

  CREATE OR REPLACE PACKAGE BODY s223860.lab1 AS
    FUNCTION getInfo
      RETURN  tableRec PIPELINED IS
      BEGIN
        for rec IN (
          select COLUMN_ID as "No.", COLUMN_NAME as "Имя столбца", ('Type: ' || DATA_TYPE || CHR(10) || 'COMMEN: ' || CHR(10) || '' || 'Constraint: ' || '') as "Атрибуты" 
          from ALL_TAB_COLUMNS WHERE TABLE_NAME = 'Н_УЧЕНИКИ'

        )
          LOOP
          PIPE ROW (rec);
        END LOOP;
        END getInfo;
      END lab1;

SELECT * FROM TABLE(lab1.getInfo);

Процедура 2 (не дописана)

create or replace procedure table_info(TAB_NAME VARCHAR2) as
    cursor table_inforamtion is
        select
               COLUMN_ID,
               tc.COLUMN_NAME,
               DATA_TYPE,
               NULLABLE,
               DATA_PRECISION,
               CHAR_LENGTH,
               COMMENTS  from USER_TAB_COLUMNS tc
        join USER_COL_COMMENTS cc on tc.TABLE_NAME = cc.TABLE_NAME and tc.COLUMN_NAME = cc.COLUMN_NAME
        where tc.TABLE_NAME = 'CUSTOMERS';

    dataType VARCHAR2(128);
begin
    DBMS_OUTPUT.ENABLE();
    DBMS_OUTPUT.PUT_LINE('Таблица: ' || TAB_NAME);

    dbms_output.put_line('No.      Имя столбца      Атрибуты');
    dbms_output.put_line('_______  ________________ _______________________');


    for row in table_inforamtion loop

        if row.DATA_PRECISION is not null then
            dataType := row.DATA_TYPE || '(' || row.DATA_PRECISION || ')';
        end if;

        dbms_output.PUT_LINE(RPAD(TO_CHAR(row.COLUMN_ID), 8) || ' ' || RPAD(row.COLUMN_NAME, 12) || ' ' || 'Type: ' || dataType);
    end loop;

end table_info;

что то дописал (добавил комент и попровил 'верстку')

CREATE OR REPLACE PROCEDURE table_info(TAB_NAME VARCHAR2) AS
    CURSOR table_inforamtion IS
        SELECT
               COLUMN_ID,
               tc.COLUMN_NAME,
               DATA_TYPE,
               NULLABLE,
               DATA_PRECISION,
               CHAR_LENGTH,
               COMMENTS
        FROM ALL_TAB_COLUMNS tc JOIN ALL_COL_COMMENTS cc ON tc.TABLE_NAME = cc.TABLE_NAME AND tc.COLUMN_NAME = cc.COLUMN_NAME
        WHERE tc.TABLE_NAME = TAB_NAME;

    dataType VARCHAR2(128);
BEGIN
    DBMS_OUTPUT.ENABLE();
    DBMS_OUTPUT.PUT_LINE('Таблица: ' || TAB_NAME);
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('No.      Имя столбца      Атрибуты');
    DBMS_OUTPUT.PUT_LINE('--- ----------------- ------------------------------------------------------');

    FOR row IN table_inforamtion LOOP
        IF row.DATA_PRECISION IS NOT NULL THEN
            dataType := row.DATA_TYPE || '(' || row.DATA_PRECISION || ')';
        ELSE
            IF row.CHAR_LENGTH > 0 THEN
              dataType := row.DATA_TYPE || '(' || row.CHAR_LENGTH || ')';
            ELSE
              dataType := row.DATA_TYPE;
            END IF;
        END IF;

        DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(row.COLUMN_ID), 3) || ' ' || RPAD(row.COLUMN_NAME, 17) || ' ' || 'Type: ' || dataType);

        IF row.COMMENTS IS NOT NULL  THEN
          DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(' '), 3) || ' ' || RPAD(' ', 17) || ' ' || 'Comment: ' || row.COMMENTS);
        END IF ;
    END LOOP;

END table_info;

как я понял осталось добавить линки (constraint)
???возможно это???

а и еще не совсем понятное not null у типа DATE возиожно это поле ALL_TAB_COLUMNS.nullable

v3

create or replace procedure table_info(TAB_NAME VARCHAR2) as
    cursor table_inforamtion is
        select
               COLUMN_ID,
               tab.COLUMN_NAME,
               DATA_TYPE,
               NULLABLE,
               DATA_PRECISION,
               CHAR_LENGTH,
               COMMENTS
        from ALL_TAB_COLUMNS tab
        left join ALL_COL_COMMENTS col on tab.TABLE_NAME = col.TABLE_NAME and tab.COLUMN_NAME = col.COLUMN_NAME and tab.OWNER = col.OWNER
        where tab.TABLE_NAME = UPPER(TAB_NAME)
        order by tab.COLUMN_ID;

    cursor cnstrs is
        select
               c.CONSTRAINT_NAME,
               COLUMN_NAME,
               CONSTRAINT_TYPE
        from ALL_CONS_COLUMNS cons
        join ALL_CONSTRAINTS c on c.TABLE_NAME = cons.TABLE_NAME and c.OWNER = cons.OWNER and cons.CONSTRAINT_NAME = c.CONSTRAINT_NAME
        where cons.TABLE_NAME = UPPER(TAB_NAME);


    dataType VARCHAR2(128);
    colConstr VARCHAR2(128);

begin
    DBMS_OUTPUT.ENABLE();
    DBMS_OUTPUT.PUT_LINE('Таблица: ' || TAB_NAME);
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('No. Имя столбца       Атрибуты');
    DBMS_OUTPUT.PUT_LINE('--- ----------------- ------------------------------------------------------');

    FOR row IN table_inforamtion LOOP
        IF row.DATA_PRECISION IS NOT NULL THEN
            dataType := row.DATA_TYPE || '(' || row.DATA_PRECISION || ')';
        ELSE
            IF row.CHAR_LENGTH > 0 THEN
              dataType := row.DATA_TYPE || '(' || row.CHAR_LENGTH || ')';
            ELSE
              dataType := row.DATA_TYPE;
            END IF;
        END IF;

        DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(row.COLUMN_ID), 3) || ' ' || RPAD(row.COLUMN_NAME, 17) || ' ' || 'Type: ' || dataType);

        IF row.COMMENTS IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(' '), 3) || ' ' || RPAD(' ', 17) || ' ' || 'Comment: ' || row.COMMENTS);
        END IF ;

        colConstr := NULL;
        for cnstr in cnstrs loop
--             P (primary key) - PK = not null + unique
            if cnstr.COLUMN_NAME = row.COLUMN_NAME and cnstr.CONSTRAINT_TYPE = 'P' then
                colConstr := cnstr.CONSTRAINT_NAME ||' Primary key';
            end if;
        end loop;

        IF colConstr is NULL and row.NULLABLE = 'N' THEN
--             Check NULLABLE
            colConstr := 'NOT NULL';
        END IF ;

        IF colConstr is not NULL then
--             if found constraint
            DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(' '), 3) || ' ' || RPAD(' ', 17) || ' ' || 'Constraint: ' || colConstr);
        end if;

    END LOOP;

end table_info;