
CREATE OR REPLACE PROCEDURE p_AlterColumn
(
    TableName    IN VARCHAR2,
    ColumnName   IN VARCHAR2,
    ColumnType   IN VARCHAR2,
    ColumnIsNull IN VARCHAR2,
    ModifyFlag   IN VARCHAR2,
    DefaultValue IN VARCHAR2 := 'NULL'
) AS
  v_str           varchar2(2000);
  v_defaultvalue  varchar2(100);
  v_defaultsql    varchar2(110);
  v_NewColumnName varchar2(30);
  v_ColumnType varchar2(30);
  v_existColumn number;
  v_DbColIsNull		varchar2(10) := 'NULL';
  num   number:=1;
  Type idx_type is RECORD
  (INDEX_NAME User_Indexes.index_name%TYPE,
   TABLE_NAME user_Indexes.table_name%TYPE,
   COLUMN_NAMES VARCHAR2(2000),
   UNIQUENESS User_Indexes.uniqueness%TYPE,
   CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%TYPE
   );
   TYPE idx_tabType is TABLE of idx_type INDEX BY BINARY_INTEGER;

   idx_row idx_type;
   idx_tab idx_tabType;

   NULLCONSTRAINT USER_CONSTRAINTS.SEARCH_CONDITION%TYPE;

   cursor cursor1 is
          select i.INDEX_NAME, t.TABLE_NAME, LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) COLUMN_NAMES, i.UNIQUENESS, con.CONSTRAINT_TYPE
          from user_ind_columns t inner join user_indexes i on t.INDEX_NAME = i.INDEX_NAME and t.TABLE_NAME = i.TABLE_NAME
               left join user_constraints con on con.table_name = t.TABLE_NAME and con.INDEX_NAME = t.INDEX_NAME
          where EXISTS (SELECT 1 FROM USER_IND_COLUMNS C
             WHERE t.INDEX_NAME = C.INDEX_NAME and TABLE_NAME = TableName AND C.COLUMN_NAME = ColumnName)
          group by t.TABLE_NAME, i.INDEX_NAME, i.UNIQUENESS, con.CONSTRAINT_TYPE;

	CURSOR CURSOR2 IS
		SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS INNER JOIN USER_CONS_COLUMNS ON USER_CONSTRAINTS.CONSTRAINT_NAME = USER_CONS_COLUMNS.CONSTRAINT_NAME
		 WHERE USER_CONSTRAINTS.CONSTRAINT_TYPE IN ('C', 'F') AND USER_CONSTRAINTS.TABLE_NAME = TableName AND USER_CONS_COLUMNS.COLUMN_NAME = ColumnName;

  cursor cursor3 is SELECT count(1) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = TableName AND COLUMN_NAME = ColumnName;

BEGIN
	IF ColumnType = 'DATETIME' THEN
		v_ColumnType := 'DATE';
	ELSIF ColumnType = 'INT' THEN
		v_ColumnType := 'NUMBER(10)';
	ELSIF ColumnType = 'SMALLINT' THEN
		v_ColumnType := 'NUMBER(10)';
	ELSIF ColumnType = 'IMAGE' THEN
		v_ColumnType := 'BLOB';
	ELSIF ColumnType = 'NTEXT' THEN
		v_ColumnType := 'NCLOB';
	ELSIF ColumnType = 'BIGINT' THEN
		v_ColumnType := 'NUMBER(19)';
	ELSE
		v_ColumnType := ColumnType;
	END IF;
	v_ColumnType := REPLACE(v_ColumnType, 'VARCHAR', 'VARCHAR2');
	v_ColumnType := REPLACE(v_ColumnType, 'DECIMAL', 'NUMBER');

	IF DefaultValue is not null AND DefaultValue <> 'NULL' THEN
		IF DefaultValue = 'GETDATE()' THEN
			v_defaultvalue := ' SYSDATE ';
		ELSIF DefaultValue = 'NEWID()' THEN
			v_defaultvalue := ' SYS_GUID() ';
		ELSE
			IF INSTR(DefaultValue, '{ts') = 1 THEN
				v_defaultvalue := REPLACE(REPLACE(DefaultValue, '{ts', 'TO_DATE('), '}', ', ''YYYY-MM-DD HH24:MI:SS'')');
			ELSE
			    v_defaultvalue := DefaultValue;
			END IF;
		END IF;
		v_defaultsql := ' DEFAULT ' || v_defaultvalue;
	ELSIF DefaultValue = 'NULL' THEN
	    v_defaultvalue := 'NULL';
		v_defaultsql := ' DEFAULT NULL';
	END IF;
	IF SUBSTR(ModifyFlag,1,1) = '1' THEN
		v_NewColumnName := REPLACE(ColumnName, 'F', 'N');
		if v_NewColumnName = ColumnName then
			if length(ColumnName) = 30 then
				v_NewColumnName := 'F' || substr(ColumnName, 2, 30);
			else
				v_NewColumnName := 'F' || ColumnName;
			end if;
		end if;

		OPEN cursor1;
		LOOP
			fetch cursor1 into idx_row;
			EXIT   WHEN   cursor1%NOTFOUND;
			idx_tab(num) := idx_row;
			num := num + 1;
		END LOOP;
		close cursor1;

		for i in 1..idx_tab.count loop
			IF idx_tab(i).CONSTRAINT_TYPE = 'P' THEN
				v_str := ' ALTER TABLE ' || TableName || ' DROP CONSTRAINT ' || idx_tab(i).INDEX_NAME || ' DROP INDEX ';
			ELSIF idx_tab(i).CONSTRAINT_TYPE = 'U' THEN
				v_str := ' ALTER TABLE ' || TableName || ' DROP CONSTRAINT ' || idx_tab(i).INDEX_NAME || ' DROP INDEX ';
			ELSE
				v_str := ' DROP INDEX ' || idx_tab(i).INDEX_NAME;
			END IF;
		execute immediate v_str;
		end loop;

		IF v_ColumnType <> 'NULL' THEN
		    v_str := ' alter table ' || TableName || ' add ' || v_NewColumnName || ' ' || v_ColumnType || v_defaultsql || ' ';
			execute immediate v_str;
			v_str := ' update ' || TableName || ' set ' || v_NewColumnName || ' = ' || ColumnName || ' ';
			execute immediate v_str;
			v_str := ' alter table ' || TableName || ' drop column ' || ColumnName || ' ';
			execute immediate v_str;
			v_str := ' alter table ' || TableName || ' rename column ' || v_NewColumnName || ' to ' || ColumnName || ' ';
			execute immediate v_str;

			OPEN CURSOR2;
			LOOP
				FETCH CURSOR2 INTO NULLCONSTRAINT;
				EXIT WHEN CURSOR2%NOTFOUND;
				IF UPPER(NULLCONSTRAINT) = '"'|| ColumnName ||'" IS NOT NULL' THEN
					v_DbColIsNull := 'NOT NULL';
				END IF;
			END LOOP;
			close CURSOR2;
			IF ColumnIsNull = 'NOT NULL' AND v_DbColIsNull <> ColumnIsNull THEN
				v_str := ' alter table ' || TableName || ' modify (' || ColumnName || ' ' || ColumnIsNull || ') ';
				execute immediate v_str;
			END IF;

			for i in 1..idx_tab.count loop
				IF idx_tab(i).CONSTRAINT_TYPE = 'P' THEN
					v_str := ' ALTER TABLE ' || TableName || ' ADD CONSTRAINT ' || idx_tab(i).INDEX_NAME || ' PRIMARY KEY (' || idx_tab(i).COLUMN_NAMES || ')' ;
				ELSE
					IF idx_tab(i).UNIQUENESS = 'UNIQUE' THEN
						v_str := ' CREATE UNIQUE INDEX ' || idx_tab(i).INDEX_NAME || ' ON ' || idx_tab(i).TABLE_NAME || ' (' || idx_tab(i).COLUMN_NAMES || ')' ;
					else
						v_str := ' CREATE INDEX ' || idx_tab(i).INDEX_NAME || ' ON ' || idx_tab(i).TABLE_NAME || ' (' || idx_tab(i).COLUMN_NAMES || ') ' ;
					end if;
				END IF;
				execute immediate v_str;
			end loop;
		ELSE
			open cursor3;
			fetch cursor3 into v_existColumn;
			close cursor3;
			IF v_existColumn = 1 THEN
				v_str := ' alter table ' || TableName || ' drop column ' || ColumnName || ' CASCADE CONSTRAINTS ';
				execute immediate v_str;
			END IF;
		END IF;
	ELSE
		OPEN CURSOR2;
		LOOP
			FETCH CURSOR2 INTO NULLCONSTRAINT;
			EXIT WHEN CURSOR2%NOTFOUND;
			IF UPPER(NULLCONSTRAINT) = '"'|| ColumnName ||'" IS NOT NULL' THEN
				v_DbColIsNull := 'NOT NULL';
			END IF;
		END LOOP;
		close CURSOR2;
		IF SUBSTR(ModifyFlag,3,1) = '1' AND SUBSTR(ModifyFlag,2,1) <> '1' THEN
			v_ColumnType := '';
		END IF;
		IF v_DbColIsNull <> ColumnIsNull AND ColumnIsNull = 'NOT NULL' AND SUBSTR(ModifyFlag,3,1) = '1' AND v_defaultvalue <> 'NULL' THEN
			v_str := 'UPDATE ' || TableName || ' SET ' || ColumnName || ' = ' || v_defaultvalue || ' WHERE ' || ColumnName || ' IS NULL ';
            execute immediate v_str;
        END IF;
		IF v_DbColIsNull = ColumnIsNull THEN
			v_DbColIsNull :='';
		ELSE
			v_DbColIsNull :=ColumnIsNull;
		END IF;
		IF TRIM(v_ColumnType || v_defaultsql || v_DbColIsNull) || ' ' <> ' ' THEN
		    v_str := ' alter table ' || TableName || ' modify ' || ColumnName || ' ' || v_ColumnType || v_defaultsql || ' ' || v_DbColIsNull || ' ';
	        execute immediate v_str;
		END IF;
	END IF;
END p_AlterColumn;
GO
