Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE myproc(table_name_in IN VARCHAR2 , per_in IN v ) IS
- var1 VARCHAR2 (500 CHAR) ;
- var2 VARCHAR2 (500 CHAR) ;
- var3 VARCHAR2 (500 CHAR) ;
- var4 NUMBER (4);
- var5 VARCHAR2 (500 CHAR) := '' ;
- var6 VARCHAR2 (400 CHAR) := '' ; --для формирования динамического insert
- var7 VARCHAR2 (500 CHAR) := ''; --туда пихаю data_scale (для NUMBER)
- var8 NUMBER (10);
- var9 VARCHAR (500 CHAR) := ''; --туда пихаю data_precision (ДЛя NUMBER)
- var10 NUMBER (10);
- var11 NUMBER (10);
- var12 VARCHAR2 (500);
- var13 VARCHAR2 (500) := '';
- var14 VARCHAR2 (500);
- var15 VARCHAR2 (500);
- var16 VARCHAR2 (500) := '';
- var17 VARCHAR2 (500) := '';
- var18 VARCHAR2 (500) := '';
- CURSOR cur1 IS SELECT (ROWNUM) "No." , (all_col_comments.COLUMN_NAME ) "Имя столбца", ALL_TAB_COLUMNS.DATA_TYPE , ALL_TAB_COLUMNS.NULLABLE, all_col_comments.CoMMENTS , ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.CHAR_LENGTH, ALL_TAB_COLUMNS.COLUMN_NAME
- FROM all_col_comments
- LEFT JOIN ALL_TAB_COLUMNS ON all_col_comments.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND all_col_comments.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME
- WHERE all_col_comments.TABLE_NAME LIKE UPPER(table_name_in) AND ALL_TAB_COLUMNS.TABLE_NAME LIKE UPPER(table_name_in)
- ;
- ERR_NUMBER EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_NUMBER, -6502); --PRAGMA EXCEPTION_INIT для связи ошибки и объявяемого исключения
- ERR_DATE EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_DATE, -01830);
- ERR_DATE_FORMAT EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_DATE_FORMAT, -01840);
- ERR_DATE_FORMAT1 EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_DATE_FORMAT1, -01847);
- ERR_DATE_FORMAT2 EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_DATE_FORMAT2, -01858);
- ERR_DATE_FORMAT3 EXCEPTION; -- not a valid month
- PRAGMA EXCEPTION_INIT(ERR_DATE_FORMAT3, -01843);
- ERR_CHECK EXCEPTION; -- check constraint violated
- PRAGMA EXCEPTION_INIT(ERR_CHECK, -02290);
- ERR_UNIQUE EXCEPTION;
- PRAGMA EXCEPTION_INIT(ERR_UNIQUE, -00001);
- PER_MENISHE EXCEPTION; -- когда пеерменных меньше чем стоблцов таблицы
- i NUMBER(4) := 0 ;
- m NUMBER(4) := 0 ;
- s BOOLEAN := TRUE; --для решения вставка или нет массива в таблицу
- BEGIN
- --проверка количевства соответствия столбцов и элементов массива
- SELECT COUNT(COLUMN_NAME) INTO var4 FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE UPPER(table_name_in ); --заносим в var4 количество столбцов таблицы
- IF ( per_in.COUNT > var4 ) THEN
- m := per_in.COUNT - var4;
- dbms_output.put_line('Значений переменных в массиве больше, чем столбцов таблицы на '||m);
- ELSE
- IF ( per_in.COUNT < var4 ) THEN
- m := var4 - per_in.COUNT;
- dbms_output.put_line('===================================================================================================');
- dbms_output.put_line('Значений переменных в массиве меньше, чем столбцов таблицы на '||m||' поэтому резульат вывода некорректен');
- dbms_output.put_line('===================================================================================================');
- RAISE PER_MENISHE; -- переход на этот эксепшн
- ELSE
- dbms_output.put_line (' ');
- END IF;
- END IF;
- DBMS_OUTPUT.ENABLE;
- dbms_output.put_line('===================================================================================================');
- dbms_output.put_line(' No Column name Attributes ');
- dbms_output.put_line(' ------ --------------- ----------------------------------------------------------------------');
- FOR index_cicla1 IN cur1 LOOP
- -- проверка условия на Number (NUMBER[(precision,scale)])
- IF (index_cicla1.DATA_TYPE = 'NUMBER') THEN
- BEGIN
- var1 :='';
- var1 := RPAD(index_cicla1."No.", 15, ' ');
- var1 := var1||RPAD(index_cicla1."Имя столбца", 25, ' ');
- IF (index_cicla1.DATA_SCALE = '0') THEN -- проверка на значения после запятой (SCALE)
- var1 := var1||'Type: '|| index_cicla1.DATA_TYPE || '(' || index_cicla1.DATA_PRECISION||')';
- ELSE
- var1 := var1||'Type: '|| index_cicla1.DATA_TYPE || '(' || index_cicla1.DATA_PRECISION||','||index_cicla1.DATA_SCALE||')';
- END IF;
- IF (index_cicla1.NULLABLE = 'N') THEN --if на NOT_NULL
- var1 :=var1|| ' NOT NULL';
- ELSE
- var1 :=var1;
- END IF;
- IF (index_cicla1.COMMENTS IS NOT NULL) THEN -- проверка условия на наличие comments
- var1 := var1||'
- '||RPAD(' ', 36, ' ') ||'Comment: '||'"'||index_cicla1.COMMENTS||'"';
- ELSE
- var1 := var1;
- END IF;
- Dbms_output.put_line(TO_CHAR(var1)); --вывод строк Number
- END;
- ELSIF (index_cicla1.data_type IN ('VARCHAR2','CHAR','VARCHAR')) THEN -- проверка условия на VARCHAR2 и CHAR
- BEGIN
- var2 :='';
- var2 := var2|| RPAD(index_cicla1."No.", 15, ' ');
- var2 := var2|| RPAD(index_cicla1."Имя столбца", 25, ' ');
- IF (index_cicla1.NULLABLE = 'N') THEN --if на NOT_NULL
- var2 :=var2 ||'Type: '|| index_cicla1.DATA_TYPE||'('|| index_cicla1.CHAR_LENGTH||') NOT NULL';
- ELSE
- var2 :=var2 ||'Type: '|| index_cicla1.DATA_TYPE||'('|| index_cicla1.CHAR_LENGTH||')';
- END IF;
- IF (index_cicla1.COMMENTS IS NOT NULL) THEN -- проверка условия на наличие comments
- var2 := var2||'
- '||RPAD(' ', 34, ' ') ||' Comment: '||'"'||index_cicla1.COMMENTS||'"';
- ELSE
- var2 := var2;
- END IF;
- Dbms_output.put_line(TO_CHAR(var2)); --вывод значений varchar2
- END;
- ELSIF (index_cicla1.data_type = 'DATE') THEN --проверка условия на DATE
- BEGIN
- var3:='';
- var3 := var3|| RPAD(index_cicla1."No.", 15, ' ');
- var3 := var3|| RPAD(index_cicla1."Имя столбца", 25, ' ');
- IF (index_cicla1.NULLABLE = 'N') THEN --if на NOT_NULL
- var3 :=var3 ||'Type: '|| index_cicla1.DATA_TYPE||' NOT NULL';
- ELSE
- var3 :=var3 ||'Type: '|| index_cicla1.DATA_TYPE;
- END IF;
- IF (index_cicla1.COMMENTS IS NOT NULL) THEN -- проверка условия на наличие comments
- var3 := var3||'
- '||RPAD(' ', 35, ' ') ||'Comment: '||'"'||index_cicla1.COMMENTS||'"';
- ELSE
- var3 := var3;
- END IF;
- Dbms_output.put_line(TO_CHAR(var3)); --вывод значений DATE
- END;
- END IF;
- -- контрэинт типа check!
- FOR constrcursor IN ( SELECT ALL_CONS_COLUMNS.CONSTRAINT_NAME, ALL_CONSTRAINTS.SEARCH_CONDITION, ALL_CONS_COLUMNS.COLUMN_NAME
- FROM ALL_CONSTRAINTS
- JOIN ALL_CONS_COLUMNS ON
- ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME AND index_cicla1.COLUMN_NAME = ALL_CONS_COLUMNS.COLUMN_NAME
- WHERE ALL_CONS_COLUMNS.TABLE_NAME LIKE UPPER(table_name_in) AND ALL_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'C' )
- LOOP
- IF ( TO_CHAR(constrcursor.SEARCH_CONDITION) = TO_CHAR('"'||index_cicla1.COLUMN_NAME||'" IS NOT NULL' ) ) THEN
- var3 := var3;
- ELSE
- var14 := '';
- dbms_output.put_line(RPAD(' ', 40) || 'Constr : "'|| constrcursor.constraint_name|| '" CHECK ('||constrcursor.SEARCH_CONDITION||')' );-- чтобы не выводило лишний check (is not null)
- var14 := constrcursor.COLUMN_NAME;
- IF (var13 IS NULL) THEN
- var13 := var14; --чтобы лишнних запятых не вставлял
- ELSE
- var13 := var13||','||var14;
- END IF;
- IF ( var13 = var14||','||var14) THEN -- проверка чтобы если 2 констреинта типа check в одном поле, чтобы выводил не 2 раза подряд
- var13 := var14;
- ELSE
- var13 := var13;
- END IF;
- -- в var13 будут через запятую писать констреинты типа check (но не те которые not null)
- END IF;
- END LOOP;
- -- контрэинт типа UNIQUE
- FOR uniquecursor IN (SELECT ALL_CONS_COLUMNS.CONSTRAINT_NAME, ALL_CONSTRAINTS.CONSTRAINT_TYPE,ALL_CONS_COLUMNS.COLUMN_NAME
- FROM ALL_CONSTRAINTS
- JOIN ALL_CONS_COLUMNS ON
- ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME AND index_cicla1.COLUMN_NAME = ALL_CONS_COLUMNS.COLUMN_NAME AND ALL_CONS_COLUMNS.TABLE_NAME LIKE UPPER(table_name_in) AND ALL_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'U')
- LOOP
- dbms_output.put_line(RPAD(' ', 40) || 'UNIQUE ');
- var16 := uniquecursor.COLUMN_NAME;
- IF (var15 IS NULL) THEN
- var15 := var16; --чтобы лишнних запятых не вставлял
- ELSE
- var15 := var15||','||var16;
- END IF;
- END LOOP;
- -- контрэинт типа PRIMARY KEY - первичный ключ - уникален(ИД и т.п.)
- FOR uniquecursor IN (SELECT ALL_CONS_COLUMNS.CONSTRAINT_NAME, ALL_CONSTRAINTS.CONSTRAINT_TYPE,ALL_CONS_COLUMNS.COLUMN_NAME
- FROM ALL_CONSTRAINTS
- JOIN ALL_CONS_COLUMNS ON
- ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME AND index_cicla1.COLUMN_NAME = ALL_CONS_COLUMNS.COLUMN_NAME AND ALL_CONS_COLUMNS.TABLE_NAME LIKE UPPER(table_name_in) AND ALL_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'P')
- LOOP
- dbms_output.put_line(RPAD(' ', 40) || 'PRIMARY KEY');
- var17 := uniquecursor.COLUMN_NAME;
- IF (var17 IS NULL) THEN
- var18 := var17; --чтобы лишнних запятых не вставлял
- ELSE
- var18 := var18||','||var17;
- END IF;
- END LOOP;
- /* ЗАЩИТА:
- CREATE OR REPLACE TYPE V IS VARRAY(4000) OF VARCHAR2(4000); -- тип VARRAY с именем 'V'
- правильные значения у массива:
- exec myproc('Н_ЛЮДИ',v('100500','ivanov','ivan','nikolaivich','pin1','inn','20.07.2003','м','olololo9','olo','SPb','20.07.2003','PIN','20.07.2003',''));
- */
- BEGIN
- i := i + 1;
- IF( per_in(i) IS NULL AND index_cicla1.NULLABLE = 'N') THEN --проверка поля на NULL
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ Т.К. ЗНАчение этого поля не может быть NULL'); -- т.к. ЕСТЬ ограничения NOT NULL
- s := FALSE ;
- ELSE
- IF (index_cicla1.DATA_TYPE = 'NUMBER') THEN
- var7 := LTRIM ( per_in(i) , '1||2||3||4||5||6||7||9||8||0'); -- удаляю слева все сиволы (остается что-то вроде ',11034')
- var7 := LTRIM ( var7 , ','); --удаляю слева направо зяптую (остается '11034')
- var8 := LENGTH(var7); --получаю длину символов для SCALE
- IF ( var8 IS NULL) THEN
- var11 := LENGTH (per_in(i)); --длина строки, которая без запятых
- ELSE
- var8:= var8;
- END IF;
- IF ( var11 <= index_cicla1.Data_Precision ) THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 40)|| ''''|| TO_NUMBER(per_in(i))||''''|| ' ПОДХОДИТ'); --под Number
- ELSE
- var9 := RTRIM ( per_in(i) , '1||2||3||4||5||6||7||9||8||0');
- var9 := RTRIM ( var9 , ',');
- var10 := LENGTH(var9); --получаю длину символов для Data_Precision
- IF (index_cicla1.Data_Precision >= var8 AND index_cicla1.DATA_SCALE >= var10 ) THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||''''|| TO_NUMBER(per_in(i))||''''|| ' ПОДХОДИТ'); --под Number
- ELSE
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ - в этом поле ограничение в '||index_cicla1.Data_Precision||' знаков или масштаб (scale) указан неправильно');
- s := FALSE ;
- END IF;
- END IF;
- ELSE
- IF ( index_cicla1.DATA_TYPE = 'DATE') THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||''''|| TO_DATE(per_in(i), 'DD-MM-YYYY')||'''' || ' ПОДХОДИТ' ); --под DATE
- ELSE
- -- обработка index_cicla1.CHAR_LENGTH
- var12 := LENGTH(per_in(i));
- IF (index_cicla1.CHAR_LENGTH >= var12 ) THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||''''||(per_in(i))||''''||' ПОДХОДИТ');-- под CHAR
- ELSE
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||(per_in(i))||' НЕ ПОДХОДИТ, длина строки больше на '|| (var12 -index_cicla1.CHAR_LENGTH) );
- s := FALSE ;
- END IF;
- END IF;
- END IF;
- END IF;
- EXCEPTION
- --INVALID_NUMBER:
- WHEN ERR_NUMBER THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- --INVALID_DATE:
- WHEN ERR_DATE THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- WHEN ERR_DATE_FORMAT THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- WHEN ERR_DATE_FORMAT1 THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- WHEN ERR_DATE_FORMAT2 THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- WHEN ERR_DATE_FORMAT3 THEN
- s := FALSE ;
- DBMS_OUTPUT.put_line(RPAD(' ', 40)||per_in(i)||' НЕ ПОДХОДИТ');
- -- WHEN OTHERS THEN
- -- RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');
- END;
- EXIT WHEN cur1%NOTFOUND;
- END LOOP;
- dbms_output.put_line('===================================================================================================');
- IF (s = TRUE ) THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 27)||'TRUE - все значения верны по типу и точности') ;
- ELSE
- DBMS_OUTPUT.put_line(RPAD(' ', 27)||'FALSE - есть ошибки в некоторых значениях') ;
- END IF;
- dbms_output.put_line('===================================================================================================');
- -- нужно прописывать SET SCAN OFF чтобы экранирвание прошло успешно, а потом SET SCAN ON
- var5 := ''''||per_in(1)||'''';
- FOR i IN 2..per_in.COUNT - m LOOP
- var5 := var5||','||''''||per_in(i)||''''; --экранирование одной кавчки ''
- END LOOP;
- --dbms_output.put_line(var5); -- вывод элементов массива 'a','s','gg' и и т.п.
- -- правильно: INSERT INTO Н_ЛЮДИ values ('100500','ivanov','ivan','nikolaivich','pin1','inn','20.07.2003','Ж','olololo9','olo','SPb','20.07.2003','PIN','20.07.2003','')
- -- если уже есть: delete from Н_ЛЮДИ where ИД = 100500;
- -- вставка элементов массива VARRAY в таблицу
- IF (s = TRUE) THEN
- var6 := 'INSERT INTO '||UPPER(table_name_in)||' VALUES '||'('||TO_CHAR(var5)||')';
- -- dbms_output.put_line(var6);
- EXECUTE IMMEDIATE var6; -- добавляю в таблицу данные
- dbms_output.put_line(RPAD(' ', 20)||'Строка добавлена в таблицу '||UPPER(table_name_in));
- ELSE
- dbms_output.put_line(' Строка не добавлена в таблицу '||UPPER(table_name_in)||', смотрите правильность типа данных и точность');
- END IF;
- dbms_output.put_line('===================================================================================================');
- EXCEPTION --общий блок исключений
- WHEN ERR_UNIQUE THEN
- DBMS_OUTPUT.put_line(RPAD(' ', 5)||' Строка с такими уникальными элементами (это поля '||var15||' и '||var18||') уже присутствует в таблице '||UPPER(table_name_in));
- dbms_output.put_line('===================================================================================================');
- WHEN ERR_CHECK THEN
- DBMS_OUTPUT.put_line('Строка не добавлена в таблицу '||UPPER(table_name_in)||', обратите внимание на Column name: '||var13||'; там есть const CHECK');
- dbms_output.put_line('===================================================================================================');
- WHEN PER_MENISHE THEN
- RAISE_APPLICATION_ERROR(-20012,'Число переменных массива < чем число столбцов таблицы '||UPPER(table_name_in));
- END myproc;
- /
Add Comment
Please, Sign In to add comment