CREATE OR REPLACE PACKAGE WS_CHECKUSER_TABLE IS
-- Author : ADMINISTRATOR
-- Created : 2018/11/7 09:22:42
--视图1 视图2 结果状态 用户名称1 用户名称2
--创建存储过程 查询用户的所有表的差集 目前定位 syscd 和base
--USER_CHECK_LEFT VARCHAR2, USER_CHECK_RIGHT VARCHAR2 暂时不需要
TYPE PUBLICK_USER_TABLES_LEFT IS REF CURSOR;
TYPE PUBLICK_USER_TABLES_RIGHT IS REF CURSOR;
TYPE PUBLICK_USER_TABLES_SAME IS REF CURSOR;
TYPE PUBLICK_ATTRIBUTE_TABLES IS REF CURSOR;
---查询单个表的差异
---根据输入的表,进行表的对比对比出来表的差异
---返回2个查询的结果集
---表的名称,表的库前缀和表查询的类别。
PROCEDURE TWOCOMPARISONS(P_CUR OUT BASE_CONSTANT.T_CUR,
P_CUR2 OUT PUBLICK_ATTRIBUTE_TABLES,
RESULTMESSAGE OUT VARCHAR2,
TABLENAMECHECK_LEFT VARCHAR2,
TABLENAMECHECK_RIGHT VARCHAR2,
TABLENAMECHECK_LEFT_P VARCHAR2,
TABLENAMECHECK_RIGHT_P VARCHAR2,
P_QUERY_TYPE NUMBER DEFAULT 0);
---查询表的名称
---left 查询 syscd 下有base 下没有的表名称
---right查询 base 下有syscd下没有的表名称
---CENTER syscd 下和base 下都有的表名称
---不传参
PROCEDURE USER_TABLE_COMPARISONS(P_CUR_LEFT OUT PUBLICK_USER_TABLES_LEFT,
P_CUR_RIGHT OUT PUBLICK_USER_TABLES_RIGHT,
P_CUR_CENTER OUT PUBLICK_USER_TABLES_SAME,
RESULTMESSAGE OUT VARCHAR2);
---查询准备进阶版本 半完成状态
PROCEDURE USER_TABLE_COLUMN_COMPARISONS(P_CUR_LEFT OUT PUBLICK_USER_TABLES_LEFT,
P_CUR_RIGHT OUT PUBLICK_USER_TABLES_RIGHT);
--查询syscd下和base下所有公共表的数据的差异sql语句组合表集
--传入参数为GENERATION_ID 和CD_SET 必须
--返回结果集为表TABLEID(序号),TABLENAME(表名称),TABLESQL(对应的sql语句,2个表没有相同列名称 则为空),
---TABLESTART(语句状态 true:有共同列,有语句,false ;没有公共列,没有语句)
---准备进阶的目标,查询出来差异结果是否有数量,然后进行判断是否需要进行显示出来
PROCEDURE USER_TABLE_COLUMN__SQL(
GENERATION_ID IN NUMBER ,
CD_SET IN NUMBER ,
P_CUR_DB OUT PUBLICK_USER_TABLES_RIGHT);
END WS_CHECKUSER_TABLE;
CREATE OR REPLACE PACKAGE BODY WS_CHECKUSER_TABLE IS
-------------存储过程查询单个表的
PROCEDURE TWOCOMPARISONS(P_CUR OUT BASE_CONSTANT.T_CUR,
P_CUR2 OUT PUBLICK_ATTRIBUTE_TABLES,
RESULTMESSAGE OUT VARCHAR2,
TABLENAMECHECK_LEFT VARCHAR2,
TABLENAMECHECK_RIGHT VARCHAR2,
TABLENAMECHECK_LEFT_P VARCHAR2,
TABLENAMECHECK_RIGHT_P VARCHAR2,
P_QUERY_TYPE NUMBER DEFAULT 0
) AS
TWO_COUNT NUMBER;
ONE_COLUMN VARCHAR2(3999);
SELECT_TYPE_ZERO VARCHAR2(3999);
SELECT_TYPE_ONE VARCHAR2(3999);
SELECT_TYPE_TWO VARCHAR2(3999);
BEGIN
--******************************--
--******************************--
---查询两表的公共列
SELECT COUNT(*) INTO TWO_COUNT FROM(
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = TABLENAMECHECK_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = TABLENAMECHECK_RIGHT) tb2 );
---查询两表的公共列
IF TWO_COUNT <= 0 THEN
--标识没有相同的列直接不查询 返回空的结果集
P_CUR:= NULL;
ELSE
--标识有相同的直 分条件判断
--查询公共的列部分
SELECT WMSYS.WM_CONCAT(COLUMN_NAME) INTO ONE_COLUMN FROM (
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = TABLENAMECHECK_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = TABLENAMECHECK_RIGHT) tb2
) ;
--查询公共的列部分
SELECT_TYPE_ONE := 'SELECT '|| ONE_COLUMN ||' FROM '||TABLENAMECHECK_LEFT_P||'.'||TABLENAMECHECK_LEFT||' MINUS SELECT '|| ONE_COLUMN ||' FROM '||TABLENAMECHECK_RIGHT_P||'.'||TABLENAMECHECK_RIGHT;
SELECT_TYPE_TWO := 'SELECT '|| ONE_COLUMN ||' FROM '||TABLENAMECHECK_RIGHT_P||'.'||TABLENAMECHECK_RIGHT||' MINUS SELECT '|| ONE_COLUMN ||' FROM '||TABLENAMECHECK_LEFT_P||'.'||TABLENAMECHECK_LEFT;
IF P_QUERY_TYPE = 0 THEN
OPEN P_CUR FOR
SELECT * FROM USER_TABLES;
ELSIF P_QUERY_TYPE = 1 THEN
-----1-----------
OPEN P_CUR FOR
SELECT_TYPE_ONE ;
RESULTMESSAGE:= '查询成功';
-----1-----------
ELSIF P_QUERY_TYPE = 2 THEN
OPEN P_CUR FOR
SELECT_TYPE_TWO ;
RESULTMESSAGE:= '查询成功';
ELSE
P_CUR :=NULL;
RESULTMESSAGE:= '查询失败';
END IF;
--标识有相同的直 分条件判断
END IF;
--******************************--
END TWOCOMPARISONS;
-------------存储过程查询单个表的
---left 以syscd为主 查找的差集 syscd 有的 base 没有 right 以syscd为主 查找的差集 base 有的 syscd 没有 。center 为2个用户 共有的表
PROCEDURE USER_TABLE_COMPARISONS(P_CUR_LEFT OUT PUBLICK_USER_TABLES_LEFT,
P_CUR_RIGHT OUT PUBLICK_USER_TABLES_RIGHT,
P_CUR_CENTER OUT PUBLICK_USER_TABLES_SAME,
RESULTMESSAGE OUT VARCHAR2) AS
---USER_TABLE_COMPARISONS逻辑开始---
P_CUR_LEFT_SQL VARCHAR2(3999);
P_CUR_RIGHT_SQL VARCHAR2(3999);
P_CUR_SAME_SQL VARCHAR2(3999);
BEGIN
-------------***-------------
P_CUR_LEFT_SQL := 'SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_SYSCD'' AND t.TABLE_NAME LIKE ''S%'' ORDER BY t.TABLE_NAME ) SYSCD_TABLE
MINUS
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_BASE'' AND t.TABLE_NAME LIKE ''B%'' ORDER BY t.TABLE_NAME)BASE_TABLE';
P_CUR_RIGHT_SQL := 'SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_BASE'' AND t.TABLE_NAME LIKE ''B%'' ORDER BY t.TABLE_NAME)BASE_TABLE
MINUS
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_SYSCD'' AND t.TABLE_NAME LIKE ''S%'' ORDER BY t.TABLE_NAME ) SYSCD_TABLE';
P_CUR_SAME_SQL := 'SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_SYSCD'' AND t.TABLE_NAME LIKE ''S%'' ORDER BY t.TABLE_NAME ) SYSCD_TABLE
INTERSECT
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) FROM all_tables t WHERE owner=''EMASS_BASE'' AND t.TABLE_NAME LIKE ''B%'' ORDER BY t.TABLE_NAME)BASE_TABLE';
OPEN P_CUR_LEFT FOR P_CUR_LEFT_SQL;
RESULTMESSAGE := '查询出LEFT数据';
DBMS_OUTPUT.PUT_LINE( '查询出LEFT数据');
OPEN P_CUR_RIGHT FOR P_CUR_RIGHT_SQL;
RESULTMESSAGE := '查询出LEFT数据,查询出RIGHT数据';
DBMS_OUTPUT.PUT_LINE( '查询出LEFT数据,查询出RIGHT数据');
OPEN P_CUR_CENTER FOR P_CUR_SAME_SQL;
RESULTMESSAGE := '查询出LEFT数据,查询出RIGHT数据,查询出共有数据';
DBMS_OUTPUT.PUT_LINE( '查询出LEFT数据,查询出RIGHT数据,查询出共有数据');
END USER_TABLE_COMPARISONS;
-------------***-------------
---USER_TABLE_COMPARISONS逻辑结束---
---USER_TABLE_COLUMN_COMPARISONS---
PROCEDURE USER_TABLE_COLUMN_COMPARISONS(
P_CUR_LEFT OUT PUBLICK_USER_TABLES_LEFT,
P_CUR_RIGHT OUT PUBLICK_USER_TABLES_RIGHT
) AS
INDEXS NUMBER(38) DEFAULT 0 ;
USER_TABLE_NAME_LEFT VARCHAR2(100);
USER_TABLE_NAME_RIGHT VARCHAR2(100);
USER_TABLE_NAME_Q_LEFT VARCHAR2(100);
USER_TABLE_NAME_Q_RIGHT VARCHAR2(100);
TWINS_COUNT NUMBER;
TWINS_TABLEORCOLUMN_COUNT NUMBER;
TWINS_START VARCHAR2(100);
TWINS_COLUMN VARCHAR2(3999);
TWINS_SELECT_WHERE VARCHAR2(3999);
P_CUR_LEFT_SQL_COUNT NUMBER;
P_CUR_RIGHT_SQL_COUNT NUMBER;
P_CUR_LEFT_SQL VARCHAR2(3999);
P_CUR_RIGHT_SQL VARCHAR2(3999);
---声明查询sy下和base下的具有相同的表数据 查询 all_tables表在最高权限用户下
CURSOR C_EMP IS
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) TABLENAME FROM all_tables t WHERE owner='EMASS_SYSCD' AND t.TABLE_NAME LIKE 'S%' ORDER BY t.TABLE_NAME ) SYSCD_TABLE
INTERSECT
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) TABLENAME FROM all_tables t WHERE owner='EMASS_BASE' AND t.TABLE_NAME LIKE 'B%' ORDER BY t.TABLE_NAME)BASE_TABLE;
C_ROW C_EMP%ROWTYPE;
---声明查询sy下和base下的具有相同的表数据 查询 all_tables表在最高权限用户下
BEGIN
---循环相同的表开始
FOR C_ROW IN C_EMP LOOP
INDEXS :=INDEXS+1;
--DBMS_OUTPUT.PUT_LINE(INDEXS || C_ROW.TABLENAME || '--' );
--DBMS_OUTPUT.PUT_LINE(INDEXS);
---循环体内的逻辑处理
USER_TABLE_NAME_LEFT := 'S_'||C_ROW.TABLENAME;--组合标准查询表名
USER_TABLE_NAME_RIGHT := 'B_'||C_ROW.TABLENAME;--组合标准查询表名
USER_TABLE_NAME_Q_LEFT := 'EMASS_SYSCD.S_'||C_ROW.TABLENAME;--组合标准查询表名
USER_TABLE_NAME_Q_RIGHT := 'EMASS_BASE.B_'||C_ROW.TABLENAME;--组合标准查询表名
---0.先根据表名查询出来公共的列个数
SELECT COUNT(*) INTO TWINS_COUNT FROM(
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
);
IF TWINS_COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('索引'|| INDEXS||'表名'||C_ROW.TABLENAME ||'没有公共列'); TWINS_START :='FALSE';
ELSE TWINS_START :='TRUE';
END IF;
IF TWINS_START = 'TRUE' THEN
---1.查询出来公共的表的列
SELECT WMSYS.WM_CONCAT(COLUMN_NAME) INTO TWINS_COLUMN FROM (
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
) ;
---1.2 查询出来公共的表的列的查询条件
/* SELECT WMSYS.WM_CONCAT('''||COLUMN_NAME||''') INTO TWINS_SELECT_WHERE FROM (
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
) ;*/
--2.根据公共的列进行判断 判断列名和类型是否一致
/*SELECT COUNT(*) INTO TWINS_TABLEORCOLUMN_COUNT FROM(
SELECT * FROM( SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
MINUS
SELECT * FROM( SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
) ;*/
---这里如果直接大于0 的话,就跳出
/*SELECT COUNT(*) FROM(
SELECT * FROM( SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLS WHERE TABLE_NAME = 'S_APPORTIONMENT_CONDITION'
AND COLUMN_NAME IN('CONDITION_INDEX','DESTINATION_PARTICIPANT_ID','PRODUCT_TYPE','SOURCE_PARTICIPANT_ID','UD_SUBTYPE','UD_TYPE')
) tb1
MINUS
SELECT * FROM( SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLS WHERE TABLE_NAME = 'B_APPORTIONMENT_CONDITION'
AND COLUMN_NAME IN('CONDITION_INDEX','DESTINATION_PARTICIPANT_ID','PRODUCT_TYPE','SOURCE_PARTICIPANT_ID','UD_SUBTYPE','UD_TYPE')
) tb2
) ;*/
---
P_CUR_LEFT_SQL :=
'
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_SYSCD.'||USER_TABLE_NAME_LEFT||' t WHERE t.GENERATION_ID = 21 AND t.CD_SET = 25
ORDER BY '||TWINS_COLUMN||'
) t
MINUS
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_BASE.'||USER_TABLE_NAME_RIGHT||' t
ORDER BY '||TWINS_COLUMN||') t;';
P_CUR_RIGHT_SQL :=
'
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_BASE.'||USER_TABLE_NAME_RIGHT||' t
ORDER BY '||TWINS_COLUMN||') t
MINUS
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_SYSCD.'||USER_TABLE_NAME_LEFT||' t WHERE t.GENERATION_ID = 21 AND t.CD_SET = 25
ORDER BY '||TWINS_COLUMN||'
) t; ';
DBMS_OUTPUT.PUT_LINE('这个是第'||INDEXS);
IF INDEXS = 1 THEN
DBMS_OUTPUT.PUT_LINE('这个是第'||P_CUR_LEFT_SQL);
DBMS_OUTPUT.PUT_LINE('这个是第'||P_CUR_RIGHT_SQL);
END IF ;
-- DBMS_OUTPUT.PUT_LINE(P_CUR_LEFT_SQL);
-- DBMS_OUTPUT.PUT_LINE(P_CUR_RIGHT_SQL);
---这里如果直接大于0 的话,就跳出
/* IF TWINS_TABLEORCOLUMN_COUNT != TWINS_COUNT THEN
DBMS_OUTPUT.PUT_LINE('索引'|| INDEXS||'表名'||C_ROW.TABLENAME ||'列名的数据类型不同');
ELSIF TWINS_TABLEORCOLUMN_COUNT <=0 THEN
DBMS_OUTPUT.PUT_LINE('索引'|| INDEXS||'表名'||C_ROW.TABLENAME ||'数据类型不一致');
ELSE
--3.在这里是列名和列名数据类型都一样。在这里进行查询差集,返出差集的结果
DBMS_OUTPUT.PUT_LINE('--');
\* OPEN P_CUR_LEFT FOR P_CUR_LEFT_SQL;
OPEN P_CUR_RIGHT FOR P_CUR_RIGHT_SQL;*\
-- SELECT COUNT(*) INTO P_CUR_LEFT_SQL_COUNT FROM P_CUR_LEFT;
--3.
END IF; */
---2.进行查询相关的差集
END IF;
---循环体内的逻辑处理
END LOOP;
---循环相同的表结束
DBMS_OUTPUT.PUT_LINE(INDEXS);
END USER_TABLE_COLUMN_COMPARISONS;
---USER_TABLE_COLUMN_COMPARISONS---
---USER_TABLE_COLUMN_COMPARISONS---
PROCEDURE USER_TABLE_COLUMN__SQL(
GENERATION_ID IN NUMBER ,
CD_SET IN NUMBER ,
P_CUR_DB OUT PUBLICK_USER_TABLES_RIGHT
) AS
INDEXS NUMBER(38) DEFAULT 0 ;
USER_TABLE_NAME_LEFT VARCHAR2(100);
USER_TABLE_NAME_RIGHT VARCHAR2(100);
TWINS_LEFT_COUNT NUMBER;
TWINS_RIGHT_COUNT NUMBER;
TWINS_COUNT NUMBER;
TWINS_START VARCHAR2(100);
TWINS_COLUMN VARCHAR2(3999);
P_CUR_LEFT_SQL VARCHAR2(3999);
P_CUR_RIGHT_SQL VARCHAR2(3999);
---临时表的数据
OUT_TABLE_DB_SQL VARCHAR2(3999);
OUT_INDERT_SQL VARCHAR2(3999);
OUT_TABLE_DB_START VARCHAR2(100);
---声明查询sy下和base下的具有相同的表数据 查询 all_tables表在最高权限用户下
CURSOR C_EMP IS
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) TABLENAME FROM all_tables t WHERE owner='EMASS_SYSCD' AND t.TABLE_NAME LIKE 'S%' ORDER BY t.TABLE_NAME ) SYSCD_TABLE
INTERSECT
SELECT * FROM (SELECT SUBSTR(t.TABLE_NAME,3) TABLENAME FROM all_tables t WHERE owner='EMASS_BASE' AND t.TABLE_NAME LIKE 'B%' ORDER BY t.TABLE_NAME)BASE_TABLE;
C_ROW C_EMP%ROWTYPE;
---声明查询sy下和base下的具有相同的表数据 查询 all_tables表在最高权限用户下
BEGIN
---创建存储表 逻辑部分
SELECT COUNT(*) INTO OUT_TABLE_DB_START FROM user_tables WHERE table_name = 'OUT_TABLE_DB';
IF OUT_TABLE_DB_START <= 0 THEN
/*只存在第一次创建的时候用*/
OUT_TABLE_DB_SQL :='CREATE GLOBAL TEMPORARY TABLE OUT_TABLE_DB (
TABLEID NUMBER,
TABLENAME VARCHAR2(500),
TABLESQL VARCHAR2(3999),
TABLESTART VARCHAR2(100)
) ON COMMIT PRESERVE ROWS ';
EXECUTE IMMEDIATE OUT_TABLE_DB_SQL;
/*ELSE
OUT_TABLE_DB_SQL := 'DELETE FROM OUT_TABLE_DB';
EXECUTE IMMEDIATE OUT_TABLE_DB_SQL; */
END IF;
---创建存储表 逻辑部分
---循环相同的表开始
FOR C_ROW IN C_EMP LOOP
INDEXS :=INDEXS+1;
--DBMS_OUTPUT.PUT_LINE(INDEXS || C_ROW.TABLENAME || '--' );
--DBMS_OUTPUT.PUT_LINE(INDEXS);
---循环体内的逻辑处理
USER_TABLE_NAME_LEFT := 'S_'||C_ROW.TABLENAME;--组合标准查询表名
USER_TABLE_NAME_RIGHT := 'B_'||C_ROW.TABLENAME;--组合标准查询表名
---0.先根据表名查询出来公共的列个数
SELECT COUNT(*) INTO TWINS_COUNT FROM(
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
);
IF TWINS_COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('索引'|| INDEXS||'表名'||C_ROW.TABLENAME ||'没有公共列');
OUT_INDERT_SQL :='INSERT INTO OUT_TABLE_DB(TABLEID,TABLENAME,TABLESQL,TABLESTART) VALUES('||INDEXS||','''||USER_TABLE_NAME_LEFT||''','''',''FALSE'')';
EXECUTE IMMEDIATE OUT_INDERT_SQL;
OUT_INDERT_SQL :='INSERT INTO OUT_TABLE_DB(TABLEID,TABLENAME,TABLESQL,TABLESTART) VALUES('||INDEXS||','''||USER_TABLE_NAME_RIGHT||''','''',''FALSE'')';
EXECUTE IMMEDIATE OUT_INDERT_SQL;
TWINS_START :='FALSE';
ELSE TWINS_START :='TRUE';
END IF;
IF TWINS_START = 'TRUE' THEN
---1.查询出来公共的表的列 例如: t.GENERATION_ID = 21 AND t.CD_SET = 25
SELECT WMSYS.WM_CONCAT(COLUMN_NAME) INTO TWINS_COLUMN FROM (
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_LEFT) tb1
INTERSECT
SELECT * FROM( SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = USER_TABLE_NAME_RIGHT) tb2
) ;
P_CUR_LEFT_SQL := 'SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_SYSCD.'||USER_TABLE_NAME_LEFT||' t WHERE t.GENERATION_ID = '||GENERATION_ID||' AND t.CD_SET = '||CD_SET||'
ORDER BY '||TWINS_COLUMN||'
) t
MINUS
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_BASE.'||USER_TABLE_NAME_RIGHT||' t
ORDER BY '||TWINS_COLUMN||') t;';
P_CUR_RIGHT_SQL := 'SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_BASE.'||USER_TABLE_NAME_RIGHT||' t
ORDER BY '||TWINS_COLUMN||') t
MINUS
SELECT '||TWINS_COLUMN||' FROM (
SELECT '||TWINS_COLUMN||'
FROM EMASS_SYSCD.'||USER_TABLE_NAME_LEFT||' t WHERE t.GENERATION_ID = '||GENERATION_ID||' AND t.CD_SET = '||CD_SET||'
ORDER BY '||TWINS_COLUMN||'
) t; ';
OUT_INDERT_SQL :='INSERT INTO OUT_TABLE_DB(TABLEID,TABLENAME,TABLESQL,TABLESTART) VALUES('||INDEXS||','''||USER_TABLE_NAME_LEFT||''','''||P_CUR_LEFT_SQL||''',''TRUE'')';
EXECUTE IMMEDIATE OUT_INDERT_SQL;
OUT_INDERT_SQL :='INSERT INTO OUT_TABLE_DB(TABLEID,TABLENAME,TABLESQL,TABLESTART) VALUES('||INDEXS||','''||USER_TABLE_NAME_RIGHT||''','''||P_CUR_RIGHT_SQL||''',''TRUE'')';
EXECUTE IMMEDIATE OUT_INDERT_SQL;
--- 这个是
DBMS_OUTPUT.PUT_LINE('这个是第'||INDEXS);
END IF;
---循环体内的逻辑处理
END LOOP;
---循环相同的表结束
---进行查处数据放到游标中
OUT_INDERT_SQL :='SELECT * FROM OUT_TABLE_DB';
OPEN P_CUR_DB FOR
OUT_INDERT_SQL;
---进行查处数据放到游标中
DBMS_OUTPUT.PUT_LINE(INDEXS);
END USER_TABLE_COLUMN__SQL;
---USER_TABLE_COLUMN_COMPARISONS---
END WS_CHECKUSER_TABLE;