编写存储过程,使用游标,进行查询出ORACLE 数据中 syscd用户和base用户下所有的表并查处差集和相同数据。
登录plsql ws 用户,最高权限用户,然后在Packages 上面右键新建 输入包体名称创建包体
包头信息 创建存储过程的名称 时间 游标:
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;
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,
TABLE_START_OUT OUT VARCHAR2
);
END WS_CHECKUSER_TABLE;
包体信息:
CREATE OR REPLACE PACKAGE BODY WS_CHECKUSER_TABLE IS
---left 以syscd为主 查找的差集 syscd 有的 base 没有 right 以syscd为主 查找的差集 base 有的 syscd 没有
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,
TABLE_START_OUT 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;
TABLE_START_OUT :='查询出LEFT数据';
OPEN P_CUR_RIGHT FOR
P_CUR_RIGHT_SQL;
TABLE_START_OUT :='查询出LEFT数据,查询出RIGHT数据';
OPEN P_CUR_CENTER FOR
P_CUR_SAME_SQL;
TABLE_START_OUT :='查询出LEFT数据,查询出RIGHT数据,查询出共有数据';
END USER_TABLE_COMPARISONS;
-------------***-------------
---USER_TABLE_COMPARISONS逻辑结束---
END WS_CHECKUSER_TABLE;
运用了游标 查差集minus 和查共有数据intersect 提前拼接语句信息。