利用存储过程进行表数据分离的案例分享-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

利用存储过程进行表数据分离的案例分享

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:13 PM

shareseparationusestorageclientdataCaseprocessconduct

某客户有个需求，有2张业务表，数据量比较大，有2000W行，现在的需求是把这2张表中的一部分记录，根据一定的where条件分离出去，创建到另外的归档表中，即做表记录的迁移操作。最后得到的结果是：未满足筛选条件的记录留在原表中，满足筛选条件的表要插入到归

某客户有个需求，有2张业务表，数据量比较大，有2000W行，现在的需求是把这2张表中的一部分记录，根据一定的where条件分离出去，创建到另外的归档表中，即做表记录的迁移操作。最后得到的结果是：未满足筛选条件的记录留在原表中，满足筛选条件的表要插入到归档表中，并且要在原表中删除这些插入到归档表中的全部记录，最后满足：新表记录+归档记录=原表记录数
下面我来模拟一下这个过程：由于没有拿到具体的建表语句，这里把表的内容最简化，只留2个列，作为最基本的演示
--连接到测试用户，创建测试表 SQL> conn zlm/zlm Connected. SQL> select * from cat;
no rows selected SQL> create table tabhdr(tabhdrid number(10),status number(10));
Table created.
SQL> create table tabdet(tabhdrid number(10));
Table created.
SQL> create table arch_tabdet as select * from tabdet;
Table created.
SQL> create table arch_tabhdr as select * from tabhdr;
Table created.
--创建操作日志表 SQL> create table arch_log( 2 archbegintime char(19), 3 archmiddletime char(19), 4 archendtime char(19), 5 archinscount1 number, 6 archdelcount1 number, 7 archinscount2 number, 8 archdelcount2 number, 9 archstatus varchar2(20), 10 archerrorcode varchar2(20), 11 archerrormsg varchar2(1000));
Table created.
操作日志表字段说明： archbegintime->archimiddletime //第一次迁移操作（insert+delete）的时间 archimiddletime->archendtime //第二次迁移操作（insert+delete）的时间 archcount1 //第一次迁移操作（insert+delete）的数据量 archcount2 //第二次迁移操作（insert+delete）的数据量 archstatus //操作状态（success/failure） archerrorcode //报错代码 archerrormsg //报错信息
--插入测试数据（每个表插入10W条记录，仅测试功能没必要用很大的数据） SQL> begin 2 for i in 1..100000 3 loop 4 insert into tabhdr values(i,9); 5 insert into tabdet values(i); 6 end loop; 7 commit; 8 end; 9 /
PL/SQL procedure successfully completed.
--创建存储过程detach_pro SQL> create or replace procedure detach_pro 2 is 3 maxrows number default 10000; 4 rowid_table dbms_sql.Urowid_Table; 5 i number; 6 cursor cur_1 is SELECT a.rowid FROM tabdet a WHERE tabhdrid IN(SELECT tabhdrid FROM tabhdr WHERE STATUS=9) order by a.rowid; 7 cursor cur_2 is SELECT a.rowid FROM tabhdr a WHERE status=9 order by a.rowid; 8 9 v_begintime char(19):=to_char(sysdate,'yyyy-mm-dd hh:mi:ss'); 10 v_middletime char(19); 11 v_inscount1 number:=0; 12 v_delcount1 number:=0; 13 v_inscount2 number:=0; 14 v_delcount2 number:=0; 15 v_errcode varchar2(100); 16 v_errerrm varchar2(1000); 17 18 begin 19 open cur_1; 20 loop 21 exit when cur_1%notfound; 22 fetch cur_1 bulk collect into rowid_table limit maxrows; 23 24 forall i in 1 .. rowid_table.count 25 insert into arch_tabdet select * from tabdet where rowid = rowid_table(i); 26 commit; 27 v_inscount1:=v_inscount1+rowid_table.count; 28 forall i in 1 .. rowid_table.count 29 delete from tabdet where rowid = rowid_table(i); 30 commit; 31 v_delcount1:= v_delcount1+rowid_table.count; 32 end loop; 33 close cur_1; 34 v_middletime:=to_char(sysdate,'yyyy-mm-dd hh:mi:ss'); 35 open cur_2; 36 loop 37 exit when cur_2%notfound; 38 fetch cur_2 bulk collect into rowid_table limit maxrows; 39 40 forall i in 1 .. rowid_table.count 41 insert into arch_tabhdr select * from tabhdr where rowid = rowid_table(i); 42 commit; 43 v_inscount2:=v_inscount2+rowid_table.count; 44 forall i in 1 .. rowid_table.count 45 delete from tabhdr where rowid = rowid_table(i); 46 commit; 47 v_delcount2:= v_delcount2+rowid_table.count; 48 end loop; 49 close cur_2; 50 insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), v_inscount1,v_delcount1,v_inscount2,v_delcount2,'success',null,null); 51 commit; 52 exception 53 when others then 54 v_errcode :=sqlcode; 55 v_errerrm :=sqlerrm; insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), v_inscount1,v_delcount1,v_inscount2,v_delcount2,'failure',v_errcode,v_errerrm); 57 commit; 58 end; 59 /
Procedure created.
--开始第1次测试 SQL> select count(*) from tabdet;
COUNT(*) ---------- 100000
SQL> select count(*) from tabhdr;
COUNT(*) ---------- 100000

SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 0
SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 0
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count(*) from tabdet;
COUNT(*) ---------- 0
SQL> select count(*) from tabhdr;
COUNT(*) ---------- 0
SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 100000
SQL> select count(*) from arch_tabhdr;
COUNT(*) ---------- 100000
SQL> select * from arch_log;
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ---------------------------------------------------------------------------------------------------------------------------------- 2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02 100000 100000 100000 100000 success
可以看到，执行了存储过程detach_pro以后，原来的两张表中都没有数据了，全部分离到归档表arch_tabdet和arch_tabhdr中去了，当然这是一种极端情况，之前插入的数据都是符合插入到归档表的筛选条件的，即字段"status=9"。操作日志表中记录了各表的插入和删除操作，以及执行的时间。
如果有新的记录插入原表，但是并不符合插入归档表中的筛选条件，比如status=8，来看一下测试结果：
--第2次测试（插入100【本文来自鸿网互联 (http://www.68idc.cn)】00条status=8的记录） SQL> begin
2 for i in 1..10000 3 loop 4 insert into tabhdr values(i,8); 5 insert into tabdet values(i); 6 end loop; 7 commit; 8 end; 9 /
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count(*) from tabdet;
COUNT(*) ---------- 10000
SQL> select count(*) from tabhdr;
COUNT(*) ---------- 10000
SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 100000
SQL> select count(*) from arch_tabhdr;
COUNT(*) ---------- 100000
SQL> select * from arch_log;
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ---------------------------------------------------------------------------------------------------------------------------------- 2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02 100000 100000 100000 100000 success

2014-12-30 10:43:38 2014-12-30 10:43:38 2014-12-30 10:43:38 0 0 0 0 success

ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:44:28 2014-12-30 10:44:28 2014-12-30 10:44:28 0 0 0 0 success

SQL>

发现执行detach_pro的速度很快，而且原表和归档表的记录都没有发生变化，因为status=8不符合筛选条件，执行存储过程并不会进行迁移操作，即使没有操作成功。由于刚才执行了2次存储过程，就会在记录表中生成2行操作结果的记录
--第3次测试（再次插入1000条符合筛选条件的记录，status=9) SQL> begin
2 for i in 1..1000 3 loop 4 insert into tabhdr values(i,9); 5 insert into tabdet values(i); 6 end loop; 7 commit; 8 end; 9 /
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count(*) from tabdet;

COUNT(*) ---------- 9000
SQL> select count(*) from tabhdr;
COUNT(*) ---------- 10000
SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 102000
SQL> select count(*) from arch_tabhdr;
COUNT(*) ---------- 101000
SQL> select * from arch_log;
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ---------------------------------------------------------------------------------------------------------------------------------- 2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02 100000 100000 100000 100000 success

2014-12-30 10:43:38 2014-12-30 10:43:38 2014-12-30 10:43:38 0 0 0 0 success

ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:44:28 2014-12-30 10:44:28 2014-12-30 10:44:28 0 0 0 0 success

2014-12-30 10:53:15 2014-12-30 10:53:15 2014-12-30 10:53:15 2000 2000 1000 1000 success
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ----------------------------------------------------------------------------------------------------------------------------------
第一次迁移操作： tabdet表中的2000行记录被插入到arch_tabdet表中，然后删除tabdet表中的2000行相应记录；因此结果是tabdet表剩下9000条记录，arch_tabdet表增加到12000条记录。
第二次迁移操作：
tabhdr表中的1000行记录被插入到arch_tabhdr表中，然后删除tabhdr表中的1000行相应记录；因此结果是tabhdr表剩下10000条记录，arch_tabhdr表增加到11000条记录。
在实际生产中，具体是哪些符合迁移条件的表是根据存储过程中具体的where条件来定的，这里的测试并不一定很准确。
最后，可以通过创建job来定期自动运行存储过程，如： declare
v_jobnum number; begin
dbms_job.submit(v_jobnum,'detach_pro',sysdate,'sysdate+1/24');
end; commit; 或：
declare v_jobnum number;

begin

dbms_job.submit

( job => v_jobnum

,what => 'detach_pro'

,next_date => sysdate

,interval => 'SYSDATE+1/24'

,no_parse => TRUE

);

end;

commit;

--把存储过程防止到job中，每小时自动运行

SQL> declare v_jobnum number;

2 begin

3 dbms_job.submit

4 ( job => v_jobnum

5 ,what => 'detach_pro'

6 ,next_date => sysdate

7 ,interval => 'SYSDATE+1/24'

8 ,no_parse => TRUE

9 );

10 end;

11 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> desc user_jobs;

Name Null? Type

----------------------------------------------------------------------- -------- ------------------------------------------------

JOB NOT NULL NUMBER

LOG_USER NOT NULL VARCHAR2(30)

PRIV_USER NOT NULL VARCHAR2(30)

SCHEMA_USER NOT NULL VARCHAR2(30)

LAST_DATE DATE

LAST_SEC VARCHAR2(8)

THIS_DATE DATE

THIS_SEC VARCHAR2(8)

NEXT_DATE NOT NULL DATE

NEXT_SEC VARCHAR2(8)

TOTAL_TIME NUMBER

BROKEN VARCHAR2(1)

INTERVAL NOT NULL VARCHAR2(200)

FAILURES NUMBER

WHAT VARCHAR2(4000)

NLS_ENV VARCHAR2(4000)

MISC_ENV RAW(32)

INSTANCE NUMBER

SQL> col interval for a15

SQL> col what for a15

SQL> select job,next_date,interval,what from user_jobs;

JOB NEXT_DATE INTERVAL WHAT

---------- --------- --------------- ---------------

5 30-DEC-14 SYSDATE+1/24 detach_pro

4 01-JAN-00 SYSDATE+1/24/60 xxx_pro;

SQL>

--删除其他无关的job SQL> exec dbms_job.remove(4);
PL/SQL procedure successfully completed.
SQL> select job,next_date,interval,what from user_jobs;
JOB NEXT_DATE INTERVAL WHAT ---------- --------- --------------- --------------- 5 30-DEC-14 SYSDATE+1/24 detach_pro

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Vue3+TS+Vite开发技巧：如何进行数据加密和存储Sep 10, 2023 pm 04:51 PM

Vue3+TS+Vite开发技巧：如何进行数据加密和存储随着互联网技术的快速发展，数据的安全性和隐私保护变得越来越重要。在Vue3+TS+Vite开发环境下，如何进行数据加密和存储，是每个开发人员都需要面对的问题。本文将介绍一些常用的数据加密和存储的技巧，帮助开发人员提升应用的安全性和用户体验。一、数据加密前端数据加密前端加密是保护数据安全性的重要一环。常用

如何在 Windows 11 上清理缓存：详细的带图片教程Apr 24, 2023 pm 09:37 PM

什么是缓存？缓存（发音为ka·shay）是一种专门的高速硬件或软件组件，用于存储经常请求的数据和指令，这些数据和指令又可用于更快地加载网站、应用程序、服务和系统的其他部分。缓存使最常访问的数据随时可用。缓存文件与缓存内存不同。缓存文件是指经常需要的文件，如PNG、图标、徽标、着色器等，多个程序可能需要这些文件。这些文件存储在您的物理驱动器空间中，通常是隐藏的。另一方面，高速缓存内存是一种比主内存和/或RAM更快的内存类型。它极大地减少了数据访问时间，因为与RAM相比，它更靠近CPU并且速度

如何利用GitLab进行项目文档管理Oct 20, 2023 am 10:40 AM

如何利用GitLab进行项目文档管理一、背景介绍在软件开发过程中，项目文档是非常重要的资料，不仅能够帮助开发团队了解项目的需求和设计，还能提供给测试团队和客户参考。为了方便项目文档的版本控制和团队协作，我们可以利用GitLab来进行项目文档管理。GitLab是一个基于Git的版本控制系统，除了支持代码管理，还可以管理项目文档。二、GitLab环境搭建首先，我

抖音作为一个全球知名的短视频社交平台，靠着其独特的个性化推荐算法赢得了广大用户的青睐。本文将深入研究抖音视频推荐的价值和原理，帮助读者更好地了解和充分利用这一功能。一、什么是抖音推荐视频抖音推荐视频是根据用户的兴趣和行为习惯，利用智能推荐算法为用户筛选和推送个性化视频内容。抖音平台通过分析用户的观看历史、点赞和评论行为、分享记录等数据，从庞大的视频库中精选出最符合用户口味的视频进行推荐。这种个性化推荐系统不仅提高了用户体验，也帮助用户发现更多符合其喜好的视频内容，从而增强用户黏性和留存率。在这个

PHP和swoole如何实现高效的数据缓存和存储？Jul 23, 2023 pm 04:03 PM

PHP和swoole如何实现高效的数据缓存和存储？概述：在Web应用开发中，数据的缓存和存储是非常重要的一部分。而PHP和swoole提供了一种高效的方法来实现数据的缓存与存储。本文将介绍如何使用PHP和swoole来实现高效的数据缓存和存储，并给出相应的代码示例。一、swoole简介：swoole是一个针对PHP语言开发的，高性能的异步网络通信引擎，它可以

一文读懂人工智能表：从MindsDB说起Apr 12, 2023 pm 12:04 PM

本文转载自微信公众号「活在信息时代」，作者活在信息时代。转载本文请联系活在信息时代公众号。对于熟悉数据库操作的同学来说，编写优美的SQL语句，从数据库中想方设法找出自己需要的数据，是常规操作了。而对于熟悉机器学习的同学来说，获取数据，对数据进行预处理，建立模型，确定训练集和测试集，用训练好的模型对未来进行一系列的预测，也是一种常规操作了。那么，我们能否将两种技术结合起来呢？我们看到数据库里存储了数据，而进行预测需要基于以往的数据。如果我们通过数据库里现有的数据，对于未来的数据进行查询的话，那么是

使用PHP数组实现数据缓存和存储的方法和技巧Jul 16, 2023 pm 02:33 PM

使用PHP数组实现数据缓存和存储的方法和技巧随着互联网的发展和数据量的急剧增长，数据缓存和存储成为了我们在开发过程中必须要考虑的问题之一。PHP作为一门广泛应用的编程语言，也提供了丰富的方法和技巧来实现数据缓存和存储。其中，使用PHP数组进行数据缓存和存储是一种简单而高效的方法。一、数据缓存数据缓存的目的是为了减少对数据库或其他外部数据源的访问次数，从而提高

威刚工控新一代高性能存储赋能AI时代Sep 22, 2023 pm 10:29 PM

9月19日，中国国际工业博览会的第二十三届正式开幕。本届工博会的主题是“碳循新工业、数聚新经济”，规模庞大，能级高，展品丰富，共有2800多家国内外展商参展。其中，威刚工控作为工业嵌入式存储领导品牌，围绕人工智能应用需求，重点展示了高性能存储解决方案，并展示了网络安全人工智能边缘计算设备、人工智能视觉控制器以及人工智能大数据分析设备的客户样机成功案例。通过展示有效解决人工智能场景中的难题和痛点的解决方案，为人工智能时代提供了新一代高性能存储技术的支持威刚展台的C位区域全面展示了各种主流规格的存储

See all articles