Oracle对数据库字段类型的可重复提交脚本

最新推荐文章于 2024-01-05 10:29:50 发布

howroad

最新推荐文章于 2024-01-05 10:29:50 发布

阅读量361

点赞数

分类专栏： Oracle学习笔记

Oracle学习笔记专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中安全地调整字段类型的方案。通过创建临时字段转移数据的方法，在不丢失现有数据的前提下更改了字段的数据类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DECLARE
	cnt integer; 
	tempCnt integer;
	tempCnt2 integer;
BEGIN
	SELECT COUNT(0)
	INTO cnt
	FROM user_tab_columns a
	WHERE a.Table_name = upper('gdt_apply_intermediary')
		AND a.COLUMN_NAME = upper('INTERMEDIARYCOSTS')
		AND a.DATA_TYPE = upper('number')
		AND a.DATA_SCALE = 2;
		
	SELECT COUNT(0)
	INTO tempCnt
	FROM user_tab_columns a
	WHERE a.Table_name = upper('gdt_apply_intermediary')
		AND a.COLUMN_NAME = upper('INTERMEDIARYCOSTS_1');
		
	IF cnt = 0 THEN 
		IF tempCnt = 1 THEN 
			EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM dual WHERE EXISTS (SELECT NULL FROM gdt_apply_intermediary a WHERE a.INTERMEDIARYCOSTS_1 IS NOT NULL)' INTO tempCnt2;
			IF tempCnt2 = 0 THEN
				EXECUTE IMMEDIATE 'ALTER TABLE gdt_apply_intermediary DROP COLUMN INTERMEDIARYCOSTS_1';
				EXECUTE IMMEDIATE 'ALTER TABLE gdt_apply_intermediary ADD (INTERMEDIARYCOSTS_1 integer)';
			END IF;
		ELSE
			tempCnt2 := 0;
			EXECUTE IMMEDIATE 'ALTER TABLE gdt_apply_intermediary ADD (INTERMEDIARYCOSTS_1 integer)';
		END IF;
		
		IF tempCnt2 = 0 THEN
			EXECUTE IMMEDIATE 'UPDATE gdt_apply_intermediary SET INTERMEDIARYCOSTS_1 = INTERMEDIARYCOSTS';
		END IF;
		
		EXECUTE IMMEDIATE 'update gdt_apply_intermediary set INTERMEDIARYCOSTS = null';
		EXECUTE IMMEDIATE 'ALTER TABLE gdt_apply_intermediary MODIFY (INTERMEDIARYCOSTS number(16,2))';
	END IF;
	SELECT COUNT(0)
	INTO tempCnt
	FROM user_tab_columns a
	WHERE a.Table_name = upper('gdt_apply_intermediary')
		AND a.COLUMN_NAME = upper('INTERMEDIARYCOSTS_1');
	IF tempCnt = 1 THEN
		EXECUTE IMMEDIATE 'UPDATE gdt_apply_intermediary SET INTERMEDIARYCOSTS = INTERMEDIARYCOSTS_1';
		EXECUTE IMMEDIATE 'ALTER TABLE gdt_apply_intermediary DROP COLUMN INTERMEDIARYCOSTS_1';
	END IF;
END;
/