Oracle 合并号段，整理缺失号段存储过程及SQL

最新推荐文章于 2022-02-25 11:28:55 发布

trw777

最新推荐文章于 2022-02-25 11:28:55 发布

阅读量340

点赞数

分类专栏： ORACLE

本文链接：https://blog.csdn.net/trw777/article/details/106411647

版权

ORACLE 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

更新号段表。

1、获取全量号段信息，使用提取工具，提取号段,保存到包表HD中（省份：SF，区号：AREACODE，最大7位号段:HD）

2、利用存储过程P_HBHD_AREACODE合并新获取的全量号段表 HD 的连续号段，到新的表中USER_TABLE_NEW。如果有相互包含，可利用存储过程P_QCHD_AREACODE合并相互包含号段

BEGIN
  P_HBHD_AREACODE(7,HD,USER_TABLE_NEW);
END;

3、利用存储过程P_HBHD_AREACODE合并老号段表 USER_TABLE 的连续号段，保存到表USER_TABLE_OLD中。如果有相互包含，可利用存储过程P_QCHD_AREACODE合并相互包含号段

BEGIN
  P_HBHD_AREACODE(7,USER_TABLE,USER_TABLE_OLD);
END;

4、利用查漏补缺 SQL 查询缺少的号段

合并连续号段存储过程：P_HBHD_AREACODE:

CREATE OR REPLACE PROCEDURE P_HBHD_AREACODE (P NUMBER DEFAULT 7,v_table_src VARCHAR2 DEFAULT 'HD',v_table_dst VARCHAR2 DEFAULT 'HD_HB')
AUTHID CURRENT_USER  ----添加后存储过程拥有创建表的权限
AS
--变量
i NUMBER;
N NUMBER;
v_table_tmp1		VARCHAR2(100);
v_table_tmp2		VARCHAR2(100);
v_sql_create  	VARCHAR2 (400);
v_sql_insert 		VARCHAR2 (400);
v_sql_count 		VARCHAR2 (500);
BEGIN
--执行
i:=0;
N:=0;
v_table_tmp1:='HD_HB1';
v_table_tmp2:='HD_HB2';

----初始化，删除重名表，创建循环表 v_table_tmp1 ，v_table_tmp2
--删除重名表
v_sql_count:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '''||v_table_dst||'''';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0) > 0 THEN
    EXECUTE IMMEDIATE ' DROP TABLE '||v_table_dst||' PURGE';
END IF;
v_sql_count:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '''||v_table_tmp1||'''';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0) > 0 THEN
    EXECUTE IMMEDIATE ' DROP TABLE '||v_table_tmp1||' PURGE';
END IF;
v_sql_count:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '''||v_table_tmp2||'''';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0) > 0 THEN
    EXECUTE IMMEDIATE ' DROP TABLE '||v_table_tmp2||' PURGE';
END IF;
--创建循环表 v_table_tmp1 ，v_table_tmp2
v_sql_create:='CREATE TABLE '||v_table_tmp1||' (AREACODE VARCHAR2(16),BEGINNUMBER NUMBER, ENDNUMBER NUMBER)';
EXECUTE IMMEDIATE v_sql_create;
v_sql_create:='CREATE TABLE '||v_table_tmp2||' (AREACODE VARCHAR2(16),BEGINNUMBER NUMBER, ENDNUMBER NUMBER)';
EXECUTE IMMEDIATE v_sql_create;
IF P=11 THEN
v_sql_insert:='INSERT INTO  '||v_table_tmp2||' SELECT AREACODE, BEGINNUMBER ,ENDNUMBER FROM '||v_table_src||' ORDER BY 2';
ELSE
v_sql_insert:='INSERT INTO  '||v_table_tmp2||' SELECT AREACODE, RPAD(HD , 11, 0) ,RPAD(HD , 11, 9) FROM '||v_table_src||' ORDER BY 2';
END IF;
EXECUTE IMMEDIATE v_sql_insert;

dbms_output.put_line('开始合并连续号段');
----循环体
loop
----预备 v_table_tmp1 表
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_table_tmp1||' DROP STORAGE';
COMMIT;
--判断是否有连续号段
v_sql_count:='SELECT COUNT(*) FROM '||v_table_tmp2||' A , '||v_table_tmp2||' B WHERE A.AREACODE=B.AREACODE AND B.BEGINNUMBER = A.ENDNUMBER + 1';
EXECUTE IMMEDIATE v_sql_count INTO N;
dbms_output.put_line('含有连续号段:'||N);
if N=0 then
--如果没有连续号段，输出结果到 HD_JG 表
v_sql_create:='CREATE TABLE '||v_table_dst||' AS SELECT AREACODE,MIN(BEGINNUMBER) BEGINNUMBER,ENDNUMBER FROM '||v_table_tmp2||' GROUP BY AREACODE,ENDNUMBER ORDER BY 2';
EXECUTE IMMEDIATE v_sql_create;
exit;
end if;
--合拼连续号段到 v_table_tmp1 表
i:=i+1;
dbms_output.put_line('合并连续号段第- '||i||' -遍。');
v_sql_insert:='INSERT INTO '||v_table_tmp1||' SELECT A.AREACODE,A.BEGINNUMBER,CASE WHEN B.ENDNUMBER IS NOT NULL THEN B.ENDNUMBER ELSE A.ENDNUMBER END FROM '||v_table_tmp2||' A LEFT JOIN '||v_table_tmp2||' B ON A.AREACODE=B.AREACODE AND A.ROWID != B.ROWID AND B.BEGINNUMBER = A.ENDNUMBER + 1';
EXECUTE IMMEDIATE v_sql_insert;
COMMIT;

----预备 v_table_tmp2 表
EXECUTE IMMEDIATE 'TRUNCATE TABLE  '||v_table_tmp2||' DROP STORAGE';
--判断是否有连续号段
v_sql_count:='SELECT COUNT(*) FROM '||v_table_tmp1||' A , '||v_table_tmp1||' B WHERE A.AREACODE=B.AREACODE AND B.BEGINNUMBER = A.ENDNUMBER + 1';
EXECUTE IMMEDIATE v_sql_count INTO N;
dbms_output.put_line('含有连续号段:'||N);
if N=0 then
--如果没有连续号段，输出结果到 HD_JG 表
v_sql_create:='CREATE TABLE '||v_table_dst||' AS SELECT AREACODE,MIN(BEGINNUMBER) BEGINNUMBER,ENDNUMBER FROM '||v_table_tmp1||' GROUP BY AREACODE,ENDNUMBER ORDER BY 2';
EXECUTE IMMEDIATE v_sql_create;
exit;
end if;
--合拼连续号段到 v_table_tmp2 表
i:=i+1;
dbms_output.put_line('合并连续号段第- '||i||' -遍。');
v_sql_insert:='INSERT INTO '||v_table_tmp2||' SELECT A.AREACODE,A.BEGINNUMBER ,CASE WHEN B.ENDNUMBER IS NOT NULL THEN B.ENDNUMBER ELSE A.ENDNUMBER END FROM '||v_table_tmp1||' A LEFT JOIN '||v_table_tmp1||' B ON A.AREACODE=B.AREACODE AND A.ROWID != B.ROWID  AND B.BEGINNUMBER = A.ENDNUMBER + 1';
EXECUTE IMMEDIATE v_sql_insert;
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_table_tmp1||' DROP STORAGE';
COMMIT;
end loop;
dbms_output.put_line('最后合并连续结果在'||v_table_dst||'表');
dbms_output.put_line(chr(10)||'合并连续号段--完成'||chr(10));
EXECUTE IMMEDIATE 'DROP TABLE '||v_table_tmp1||' PURGE';
EXECUTE IMMEDIATE 'DROP TABLE '||v_table_tmp2||' PURGE';
dbms_output.put_line('检测相互包含');
v_sql_count:='SELECT COUNT(distinct A.BEGINNUMBER) FROM '||v_table_dst||' A , '||v_table_dst||' B WHERE  A.AREACODE=B.AREACODE AND (A.BEGINNUMBER < B.BEGINNUMBER AND B.BEGINNUMBER < A.ENDNUMBER) OR (A.BEGINNUMBER > B.BEGINNUMBER AND A.ENDNUMBER < B.ENDNUMBER )';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0) > 0 THEN
    dbms_output.put_line('含有相互包含号段:'||N||'条，需要执行包含去重');
	P_QCHD_AREACODE();
ELSE
	dbms_output.put_line('没有相互包含记录');
END IF;
END;

合并相互包含号段存储过程：P_QCHD_AREACODE:

CREATE OR REPLACE PROCEDURE P_QCHD_AREACODE (v_table_src VARCHAR2 DEFAULT 'HD_HB',v_table_dst VARCHAR2 DEFAULT 'HD_QC')
AUTHID CURRENT_USER  ----添加后存储过程拥有创建表的权限
AS
i								NUMBER;
N								NUMBER;
v_table_tmp			VARCHAR2(100);
v_sql_count			VARCHAR2(400);
v_sql_create		VARCHAR2(400);
v_sql_insert		VARCHAR2(400);
BEGIN			--开始执行
i:=0;
N:=0;
v_table_tmp:='HD_TMP';

--判断是否含有相互包含号段
v_sql_count:='SELECT COUNT(DISTINCT A.BEGINNUMBER) FROM '||v_table_src||' A , '||v_table_src||' B WHERE A.AREACODE=B.AREACODE AND (A.BEGINNUMBER < B.BEGINNUMBER AND B.BEGINNUMBER < A.ENDNUMBER) OR (A.BEGINNUMBER > B.BEGINNUMBER AND A.ENDNUMBER < B.ENDNUMBER )';
EXECUTE IMMEDIATE v_sql_count INTO N;
--EXECUTE IMMEDIATE v_sql_count;
dbms_output.put_line('含有相互包含号段:'||N);
IF NVL(N, 0)=0 THEN
--如果没有相互包含号段，删除表HD_QC1
dbms_output.put_line('没有相互包含号段，无需合并！');
ELSE

----初始化，删除重名表，创建表
--删除重名表
v_sql_count:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '''||v_table_dst||'''';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0) > 0 THEN
EXECUTE IMMEDIATE ' DROP TABLE '||v_table_dst||' PURGE';
END IF;
v_sql_count:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '''||v_table_tmp||'''';
EXECUTE IMMEDIATE v_sql_count INTO N;
IF NVL(N, 0)  > 0 THEN
	EXECUTE IMMEDIATE 'DROP TABLE '||v_table_tmp||' PURGE';
END IF;

--创建表 HD_QC1
v_sql_create:='CREATE TABLE '||v_table_dst||' (AREACODE VARCHAR2(16),BEGINNUMBER NUMBER, ENDNUMBER NUMBER)';
EXECUTE IMMEDIATE v_sql_create;
v_sql_create:='CREATE TABLE '||v_table_tmp||' (AREACODE VARCHAR2(16),BEGINNUMBER NUMBER, ENDNUMBER NUMBER)';
EXECUTE IMMEDIATE v_sql_create;
v_sql_insert:='INSERT INTO '||v_table_dst||' SELECT * FROM '||v_table_src||' ORDER BY 2';
EXECUTE IMMEDIATE v_sql_insert;
COMMIT;
	----循环体
dbms_output.put_line('开始合并相互包含号段');
LOOP
----预备 HD_QC1 表
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_table_tmp||' DROP STORAGE';
--合拼相互包含号段到 HD_QC1 表
i:=i+1;
dbms_output.put_line('合并相互包含号段第- '||i||' -遍。');
v_sql_insert:='INSERT INTO '||v_table_tmp||' SELECT DISTINCT A.AREACODE,A.BEGINNUMBER, CASE WHEN A.ENDNUMBER < B.ENDNUMBER THEN B.ENDNUMBER ELSE A.ENDNUMBER END FROM '||v_table_dst||' A LEFT JOIN '||v_table_dst||' B ON A.AREACODE=B.AREACODE AND (A.BEGINNUMBER < B.BEGINNUMBER AND A.ENDNUMBER > B.BEGINNUMBER) OR (A.BEGINNUMBER > B.BEGINNUMBER AND A.ENDNUMBER < B.ENDNUMBER)';
EXECUTE IMMEDIATE v_sql_insert;
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_table_dst||' DROP STORAGE';
v_sql_insert:='INSERT INTO '||v_table_dst||' WITH A AS (SELECT AREACODE,BEGINNUMBER,MAX(ENDNUMBER) ENDNUMBER FROM '||v_table_tmp||' GROUP BY AREACODE,BEGINNUMBER)  SELECT AREACODE,MIN(BEGINNUMBER),ENDNUMBER FROM A GROUP BY AREACODE,ENDNUMBER ORDER BY 2';
EXECUTE IMMEDIATE v_sql_insert;
COMMIT;
--判断是否含有相互包含号段
v_sql_count:='SELECT COUNT(DISTINCT A.BEGINNUMBER) FROM '||v_table_dst||' A , '||v_table_dst||' B WHERE A.AREACODE=B.AREACODE AND (A.BEGINNUMBER < B.BEGINNUMBER AND B.BEGINNUMBER < A.ENDNUMBER) OR (A.BEGINNUMBER > B.BEGINNUMBER AND A.ENDNUMBER < B.ENDNUMBER )';
EXECUTE IMMEDIATE v_sql_count INTO N;
--EXECUTE IMMEDIATE v_sql_count;
dbms_output.put_line('含有相互包含号段:'||N);
IF NVL(N, 0)=0 THEN
--如果没有相互包含号段，删除表HD_QC1
dbms_output.put_line('最后合并相互包含号段结果在'||v_table_dst||'表');
EXECUTE IMMEDIATE 'DROP TABLE '||v_table_tmp||' PURGE';
dbms_output.put_line(chr(10)||'合并相互包含号段--完成'||chr(10));
EXIT;
END IF;
END LOOP;
END IF;

END;				--结束

查漏补缺 SQL方法一:


/*-----------------------------------处理老表缺少号段记录方法1-----------------------------------
1、使用存储过程P_HBHD_AREACODE先合并老号段表连续号段到表HD_HB(AREACODE区号，BEGINNUMBER开始号码，ENDNUMBER结束号码)	 
2、使用存储过程P_HBHD_AREACODE,统计新号段到表USER_TABLE(AREACODE区号，BEGINNUMBER开始号码，ENDNUMBER结束号码)
3、统计新老表子集、交集、补集处理
*/
---------------------------  1、全量交集子集  ---------------------------
WITH T AS (
  SELECT DISTINCT
    COUNT(A.BEGINNUMBER) OVER ( PARTITION BY A.BEGINNUMBER ) JS,  --新表单条记录包含老表交集，子集个数
    dense_rank ( ) OVER ( ORDER BY A.BEGINNUMBER ) FL,  --新表记录原号码序号
    RANK ( ) OVER ( partition BY A.BEGINNUMBER ORDER BY B.BEGINNUMBER ) PX,  --新表单条记录对应老表交集，子集序号
    A.AREACODE A_A,  --区号
    TO_NUMBER( A.BEGINNUMBER ) A_S,  --新表开始号码
    TO_NUMBER( A.ENDNUMBER ) A_E,  --新表结束号码
    TO_NUMBER( B.BEGINNUMBER ) B_S,  --老表开始号码
    TO_NUMBER( B.ENDNUMBER ) B_E,  --老表结束号码
    CASE
      WHEN A.BEGINNUMBER < B.BEGINNUMBER AND A.ENDNUMBER > B.ENDNUMBER THEN 1 
      ELSE 0 
    END ZJ 
  FROM USER_TABLE_NEW A,USER_TABLE_OLD B 
  WHERE	A.AREACODE = B.AREACODE 
    AND (
      ( A.BEGINNUMBER < B.BEGINNUMBER AND A.ENDNUMBER > B.BEGINNUMBER ) 
      OR ( A.BEGINNUMBER < B.ENDNUMBER AND A.ENDNUMBER > B.ENDNUMBER ) 
    ) 
  ORDER BY 1 DESC,2
)
---------------------------  2、含有一次交集，子集  ---------------------------
SELECT T.A_A 区号,
  CASE WHEN A_S >= B_S THEN B_E+1 ELSE A_S END 开始号段,
  CASE WHEN A_E <= B_E OR ZJ = 1 THEN B_S-1 ELSE A_E END 结束号段 
FROM T 
WHERE JS = 1 
UNION ALL
SELECT T.A_A,B_E+1,A_E FROM T WHERE JS = 1 AND ZJ = 1
---------------------------  3、含有一次以上子集  ---------------------------
UNION ALL 
SELECT T.A_A,
  CASE WHEN PX=1 THEN A_S ELSE B_E+1 END J_S ,
  CASE WHEN PX=1 THEN B_S-1 ELSE A_E END J_E
FROM T 
WHERE JS>1 
  AND ZJ=1 
  AND (PX=1 OR PX = JS) 
---------------------------  4、含有一次以上交集与子集  ---------------------------
UNION ALL
SELECT M.A_A,M.B_E+1,N.B_S-1 
FROM T M 
  LEFT JOIN T N ON M.FL = N.FL AND M.PX+1 = N.PX 
WHERE M.JS > 1 AND M.PX !=M.JS
---------------------------  5、缺少号段  ---------------------------
UNION ALL
SELECT A.AREACODE,TO_NUMBER(A.BEGINNUMBER),TO_NUMBER(A.ENDNUMBER)
FROM USER_TABLE_NEW A 
  LEFT JOIN USER_TABLE_OLD B ON A.AREACODE=B.AREACODE 
    AND (
      (A.BEGINNUMBER >= B.BEGINNUMBER AND A.ENDNUMBER <= B.ENDNUMBER) 
      OR ( A.BEGINNUMBER < B.ENDNUMBER AND A.ENDNUMBER > B.ENDNUMBER )
    ) 
WHERE B.BEGINNUMBER IS NULL 
ORDER BY 3
---------------------------  END  ---------------------------

查漏补缺 SQL方法二:未完成，

/*-----------------------------------处理缺少号段记录方法2-----------------------------------
	1、先更新1、2交集，在处理3子集，比第一种方法较复杂，但思路较清晰，待完成。
	2、1、2、3记录处理完，加上4补集，就为老表缺少号段记录，5-A为B的子集记录无需处理
*/
---------------------------1、下交集---------------------------
SELECT 'XJJ' GX,A.DELINEATION,A.BEGINNUMBER,A.ENDNUMBER,B.BEGINNUMBER,B.ENDNUMBER 
FROM USER_TABLE_NEW A,USER_TABLE_OLD B 
WHERE A.AREACODE=B.AREACODE 
		AND A.BEGINNUMBER<B.BEGINNUMBER 
		AND A.ENDNUMBER>B.BEGINNUMBER 
		AND A.ENDNUMBER<=B.ENDNUMBER
---------------------------2、上交集---------------------------
UNION ALL
SELECT 'SJJ' GX,A.DELINEATION,A.BEGINNUMBER,A.ENDNUMBER,B.BEGINNUMBER,B.ENDNUMBER 
FROM USER_TABLE_NEW A,USER_TABLE_OLD B 
WHERE A.AREACODE=B.AREACODE 
		AND A.BEGINNUMBER<B.ENDNUMBER 
		AND A.ENDNUMBER>B.ENDNUMBER 
		AND A.BEGINNUMBER>=B.BEGINNUMBER
ORDER BY 2
---------------------------3、子集---------------------------
UNION ALL
SELECT 'ZJ' GX,A.DELINEATION,A.BEGINNUMBER,A.ENDNUMBER A_E,B.BEGINNUMBER,B.ENDNUMBER
FROM USER_TABLE_NEW A,USER_TABLE_OLD B 
WHERE A.AREACODE=B.AREACODE AND A.BEGINNUMBER < B.BEGINNUMBER  AND A.ENDNUMBER > B.ENDNUMBER
---------------------------4、补集---------------------------
SELECT A.DELINEATION,A.BEGINNUMBER,A.ENDNUMBER 
FROM USER_TABLE_NEW A 
	LEFT JOIN USER_TABLE_OLD B ON A.AREACODE=B.AREACODE 
		AND (
			(A.BEGINNUMBER >= B.BEGINNUMBER AND A.ENDNUMBER <= B.ENDNUMBER)    --5、A为B的子集，不处理部分
			OR (A.BEGINNUMBER < B.BEGINNUMBER AND A.ENDNUMBER > B.BEGINNUMBER )	--全量1交集子集
			OR (A.BEGINNUMBER < B.ENDNUMBER AND A.ENDNUMBER > B.ENDNUMBER )		--全量2交集子集
		) 
WHERE B.BEGINNUMBER IS NULL ORDER BY 2
---------------------------5、A为B的子集---------------------------
SELECT DISTINCT A.DELINEATION,A.BEGINNUMBER,A.ENDNUMBER,B.BEGINNUMBER,B.ENDNUMBER 
FROM USER_TABLE_NEW A,USER_TABLE_OLD B 
WHERE A.AREACODE=B.AREACODE 
		AND A.BEGINNUMBER >= B.BEGINNUMBER 
		AND A.ENDNUMBER <= B.ENDNUMBER 
ORDER BY 2
---------------------------		END		---------------------------

trw777

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 合并号段，整理缺失号段存储过程及SQL

/*-----------------------------------处理老表缺少号段记录方法1-----------------------------------1、使用存储过程P_HBHD_AREACODE先合并老号段表连续号段到表HD_HB(AREACODE区号，BEGINNUMBER开始号码，ENDNUMBER结束号码) 2、使用存储过程P_HBHD_AREACODE,统计新号段到表USER_TABLE(AREACODE区号，BEGINNUMBER开始号码，ENDNUMBER结束号码..
复制链接

扫一扫