人大金仓转mysql

1）获取导入的数据库表名称,插入到TABLE_NAME数据表中

CREATE TABLE "PUBLIC"."TABLE_NAME" (
	"C1" VARCHAR NULL
)
WITH (
	OIDS=FALSE
);
CREATE TABLE "PUBLIC"."TABLE_SQL" (
	"C1" TEXT NULL,
	"C2" BPCHAR(10 char) NULL
)
WITH (
	OIDS=FALSE
);

CREATE OR REPLACE  PROCEDURE PUBLIC.pCreate_TableSql(pre CHARACTER VARYING DEFAULT 50 ) 
 LANGUAGE PLSQL
AS  v_relname varchar(300) ;
CURSOR CS1 IS SELECT relname FROM sys_class c   WHERE   c.relname LIKE  CONCAT(pre,'%')  AND relkind='r' AND relnamespace=2200;
BEGIN
DELETE TABLE_NAME;
DELETE from TABLE_SQL;
OPEN cs1;
LOOP
	FETCH cs1 INTO v_relname;
	EXIT WHEN cs1%NOTFOUND;	
	 INSERT INTO "TABLE_NAME" ("C1"  )VALUES( v_relname); 
	 EXECUTE P_TB1(v_relname);
	
END LOOP;

CLOSE cs1;
END;

2）传入表名，生成建表语句，插入到TABLE_SQL表中

CREATE OR REPLACE  PROCEDURE PUBLIC.P_TB1(tablename CHARACTER VARYING DEFAULT 300) 
 LANGUAGE PLSQL
AS 
tablnameSQL text;
droptable VARCHAR2(200);
v_field VARCHAR2(200);
v_COMMENT VARCHAR2(200);
v_type VARCHAR2(50);
v_lengthvar VARCHAR2(50); 
CURSOR CS2 IS   
SELECT a.attname AS field,
       b.description AS COMMENT,
       t.typname AS type,
       CASE WHEN a.atttypmod<=0 THEN 100 ELSE (a.atttypmod-4) END AS lengthvar,
       a.attnotnull AS notnull
  FROM sys_class c 
     INNER JOIN sys_namespace n on c.relnamespace = n.oid,
     sys_attribute a LEFT JOIN sys_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
     sys_type t    
 WHERE a.attnum > 0
       and a.attrelid = c.oid
       and a.atttypid = t.oid
       AND nspname = 'PUBLIC'     -- schema
       AND c.relname = tablename -- table_name
 ORDER BY a.attnum ;
 
 
BEGIN
tablnameSQL := 'CREATE TABLE '|| '`'||tablename ||'`(' ;  
droptable := ' DROP TABLE IF EXISTS `'||tablename||'`;';
INSERT INTO "TABLE_SQL" ("C1","C2" )VALUES( droptable,'3');
 OPEN CS2;
LOOP
	FETCH CS2 INTO v_field,v_COMMENT,v_type,v_lengthvar;
	EXIT WHEN CS2%NOTFOUND;
	IF 'TIMESTAMP' = v_type OR 'TIMESTAMPTZ' =v_type OR 'TIME' = v_type   THEN 
	tablnameSQL :=tablnameSQL||'`'||v_field||'` '||'datetime'||'  DEFAULT NULL COMMENT '||'"'||v_COMMENT||'",'; 
	ELSIF 'INT4' = v_type OR  'INT8' = v_type OR 'INT' = v_type  THEN 
	tablnameSQL :=tablnameSQL||'`'||v_field||'` '||'INT'||'  DEFAULT NULL COMMENT '||'"'||v_COMMENT||'",';
	ELSIF 'DATE' = v_type   THEN 
	tablnameSQL :=tablnameSQL||'`'||v_field||'` '||'DATE'||'  DEFAULT NULL COMMENT '||'"'||v_COMMENT||'",';
	ELSE 
	tablnameSQL :=tablnameSQL||'`'||v_field||'` '||TRIM(v_type)||'('||v_lengthvar||')'||' CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '||'"'||v_COMMENT||'",';
	END IF ; 
	
END LOOP;

CLOSE CS2;
SELECT SUBSTRING(tablnameSQL,0,length(tablnameSQL)-1) INTO tablnameSQL;
tablnameSQL := tablnameSQL||');';
INSERT INTO "TABLE_SQL" ("C1","C2" )VALUES( tablnameSQL,'2');
END;

3）执行 步骤
call PROCEDURE_NAME(‘RS_’);
SELECT * FROM table_sql;

4. 数据导出

sys_dump -h localhost -U SYSTEM -W SYSTEM -t 'PUBLIC.RS_*'    RSDAS > D:/backup/tables_RS.sql
sys_dump -h localhost -p 54322 -U SYSTEM -W SYSTEM -t 'PUBLIC.SYS_*'  -T public.sys_log  RSDAS > D:/backup/tables_SYS.sql