
CREATE OR REPLACE PROCEDURE p_AlterColumn("TABLENAME"    IN VARCHAR(32767),
                                          "COLUMNNAME"   IN VARCHAR(32767),
                                          "COLUMNTYPE"   IN VARCHAR(32767),
                                          "COLUMNISNULL" IN VARCHAR(32767),
                                          "MODIFYFLAG"   IN VARCHAR(32767),
                                          "DEFAULTVALUE" IN VARCHAR(32767) DEFAULT 'NULL')
  AUTHID DEFINER AS
  v_str           varchar(2000);
  v_defaultvalue  varchar(100);
  v_defaultsql    varchar(110);
  v_NewColumnName varchar(30);
  v_ColumnType    varchar(30);
  v_existColumn   number;
  v_DbColIsNull   varchar(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,
    CONSTRAINT_NAME user_constraints.CONSTRAINT_NAME%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,
           con.constraint_name
      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,
              con.constraint_name;

  CURSOR CURSOR2 IS
    select NULLABLE
      from USER_TAB_COLS
     where TABLE_NAME = TableName
       and 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 := 'TIMESTAMP';
  ELSIF ColumnType = 'IMAGE' THEN
    v_ColumnType := 'BLOB';
  ELSIF ColumnType = 'NTEXT' THEN
    v_ColumnType := 'CLOB';
  ELSE
    v_ColumnType := ColumnType;
  END IF;
  v_ColumnType := REPLACE(v_ColumnType, 'VARCHAR', 'VARCHAR2');
  v_ColumnType := REPLACE(v_ColumnType, 'DECIMAL', 'NUMBER');

  TableName := UPPER(TableName);
  ColumnName := UPPER(ColumnName);

	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;
		-- 历史脚本未修改默认会输出 默认值为空（EXEC p_AlterColumn 'T_PRIVACY_DESEN_RULES_L', 'FDESCRIPTION', 'NVARCHAR(1000)', 'NULL', '0100', '';），2023-09之后未修改默认值也会输出默认值。此处增加判断确保历史脚本正确
		IF v_defaultvalue is not null THEN
		    v_defaultsql := ' DEFAULT ' || v_defaultvalue;
		END IF;
		-- 兼容空串<>null
		IF v_defaultvalue = '' THEN
			v_defaultsql := ' ';
		END IF;
	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).constraint_name;
        -- 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 NULLCONSTRAINT = 'N' 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).CONSTRAINT_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 NULLCONSTRAINT = 'N' 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;
		--已经非空，不能modify非空
		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
