Oracle表转Mysql脚本

Oracle转Mysql脚本

由于在网上看到的脚本有些函数Oracle已经不支持，所以做了修改。具体参考文章：https://blog.csdn.net/weixin_45676630/article/details/103187153

create or replace package p_view_param2  is
   function F_LIMS_GET_SQL_FOR_MYSQL(PI_TABLENAME IN VARCHAR2,PI_ISDROP IN INTEGER := 1) return clob;
end p_view_param2;

create or replace package body p_view_param2 is      
	function F_LIMS_GET_SQL_FOR_MYSQL(PI_TABLENAME IN VARCHAR2,PI_ISDROP IN INTEGER := 1) RETURN clob AS
		V_TABLENAME VARCHAR(100);
		V_SQL_HEADER   VARCHAR2(1000);
		V_SQL_BODY  CLOB;
		V_SQL_INDEX  VARCHAR2(4000);
		V_SQL_TABLE_COMMENT VARCHAR(400);
	    V_SQL_PK_COL_LIST VARCHAR(1000);
	BEGIN
	  -- 表名统一为大写
		SELECT UPPER(PI_TABLENAME) INTO V_TABLENAME FROM DUAL;
		SELECT MAX(T.COMMENTS)
		    INTO V_SQL_TABLE_COMMENT
		    FROM USER_TAB_COMMENTS T
		   	WHERE T.TABLE_NAME = V_TABLENAME;
		SELECT MAX(LISTAGG('`' || A.COLUMN_NAME || '` ') WITHIN GROUP (ORDER BY A.COLUMN_NAME))
		    INTO V_SQL_PK_COL_LIST
		    FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
		   	WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
			     AND B.CONSTRAINT_TYPE = 'P'
			     AND A.TABLE_NAME = V_TABLENAME
		   	GROUP BY A.TABLE_NAME;
		V_SQL_HEADER := '-- ' || V_SQL_TABLE_COMMENT || ' 建表语句 ' || CHR(13);
		IF PI_ISDROP = 1 THEN
			V_SQL_HEADER := V_SQL_HEADER || 'DROP TABLE IF EXISTS `' || LOWER(V_TABLENAME) || '`;' || CHR(13);
		END IF;
			V_SQL_HEADER := V_SQL_HEADER || 'CREATE TABLE `' || LOWER(V_TABLENAME) || '`  (';
		
		-- 表列信息
		SELECT LISTAGG(CHR(13) || '`' || LOWER(T.COLUMN_NAME) || '` ' ||
				(CASE
					WHEN T.DATA_TYPE = 'VARCHAR2' OR T.DATA_TYPE = 'NVARCHAR2' THEN 'VARCHAR(' || T.DATA_LENGTH || ')'
					WHEN T.DATA_TYPE = 'CHAR' THEN 'CHAR(' || T.DATA_LENGTH || ')'
					WHEN T.DATA_TYPE = 'DATE' THEN 'DATETIME'
					WHEN T.DATA_TYPE = 'CLOB' THEN 'MEDIUMTEXT'
					WHEN T.DATA_TYPE = 'NUMBER' THEN 'DECIMAL(' || T.DATA_PRECISION || ',' ||T.DATA_SCALE || ')'
					WHEN T.COLUMN_NAME = 'ID' THEN 'VARCHAR(32)'
				ELSE
					T.DATA_TYPE
				END) 
				|| DECODE(T.NULLABLE, 'N', ' NOT NULL ', '') || ' COMMENT ''' || TC.COMMENTS || '''' ||CHR(44))  WITHIN GROUP (ORDER BY T.COLUMN_NAME)
			INTO V_SQL_BODY
			FROM USER_TAB_COLUMNS T
			LEFT JOIN USER_COL_COMMENTS TC
				ON T.TABLE_NAME = TC.TABLE_NAME
				AND T.COLUMN_NAME = TC.COLUMN_NAME
			WHERE T.TABLE_NAME = V_TABLENAME
			ORDER BY T.COLUMN_ID;
	
		IF V_SQL_PK_COL_LIST IS NOT NULL THEN
		V_SQL_BODY := V_SQL_BODY || CHR(13) || ' PRIMARY KEY (' ||
		LOWER(V_SQL_PK_COL_LIST) || ')  ';
		END IF;
		-- 表备注
		V_SQL_BODY := V_SQL_BODY || CHR(13) || ') COMMENT = ''' || V_SQL_TABLE_COMMENT || ''';' || CHR(13);
		-- 唯一索引 
		SELECT (LISTAGG('@ALTER TABLE `' || A.TABLE_NAME ||
			'` ADD UNIQUE INDEX (' ||
			LISTAGG('`' || A.COLUMN_NAME || '`') WITHIN GROUP (ORDER BY A.COLUMN_NAME)  || ');@') WITHIN GROUP (ORDER BY A.TABLE_NAME) )
			INTO V_SQL_INDEX
			FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
			WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
				AND B.CONSTRAINT_TYPE = 'U'
				AND A.TABLE_NAME = V_TABLENAME
			GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME;
		
		IF V_SQL_INDEX IS NOT NULL THEN
			V_SQL_INDEX := REPLACE(REPLACE(V_SQL_INDEX, '@,@', CHR(13)), '@', '');
			V_SQL_INDEX := '-- 创建索引' || CHR(13) || V_SQL_INDEX;
		END IF;

	RETURN  V_SQL_HEADER || V_SQL_BODY || V_SQL_INDEX;
	END;     
END  p_view_param2;
SELECT p_view_param2.F_LIMS_GET_SQL_FOR_MYSQL('TEST_TABLE',0) as temp FROM DUAL;

由于需要，这里使用 LOWER 函数将Oracle的表名和属性统统转为小写了