Home Database Mysql Tutorial MySQL存储过程例子(包含事务,输出参数,嵌套调用)_MySQL

MySQL存储过程例子(包含事务,输出参数,嵌套调用)_MySQL

Jun 01, 2016 pm 01:20 PM
information

bitsCN.com drop procedure if exists pro_rep_shadow_rs;
delimiter |
----------------------------------
-- rep_shadow_rs
-- 用来处理信息的增加,更新和删除
-- 每次只更新上次以来没有做过的数据
-- 根据不同的标志位
-- 需要一个输出的参数,
-- 如果返回为0,则调用失败,事务回滚
-- 如果返回为1,调用成功,事务提交
--
-- 测试方法
-- call pro_rep_shadow_rs(@rtn);
-- select @rtn;
----------------------------------
create procedure pro_rep_shadow_rs(out rtn int)
begin
-- 声明变量,所有的声明必须在非声明的语句前面
declare iLast_rep_sync_id int default -1;
declare iMax_rep_sync_id int default -1;

-- 如果出现异常,或自动处理并rollback,但不再通知调用方了
-- 如果希望应用获得异常,需要将下面这一句,以及启动事务和提交事务的语句全部去掉
declare exit handler for sqlexception rollback;

-- 查找上一次的
select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';

-- 如果不存在,则增加一行
if iLast_rep_sync_id=-1 then
insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');
set iLast_rep_sync_id = 0;
end if;

-- 下一个数字
set iLast_rep_sync_id=iLast_rep_sync_id+1;

-- 设置默认的返回值为0:失败
set rtn=0;

-- 启动事务
start transaction;

-- 查找最大编号
select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- 有新数据
if iMax_rep_sync_id>=iLast_rep_sync_id then
-- 调用
call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
-- 更新日志
update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
end if;

-- 运行没有异常,提交事务
commit;
-- 设置返回值为1
set rtn=1;
end;


delimiter ;
drop procedure if exists pro_rep_shadow_rs_do;

delimiter |
---------------------------------
-- 处理指定编号范围内的数据
-- 需要输入2个参数
-- last_rep_sync_id 是编号的最小值
-- max_rep_sync_id 是编号的最大值
-- 无返回值
---------------------------------
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)
begin
declare iRep_operationtype varchar(1);
declare iRep_status varchar(1);
declare iRep_Sync_id int;
declare iId int;

-- 这个用于处理游标到达最后一行的情况
declare stop int default 0;
-- 声明游标
declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
-- 声明游标的异常处理,设置一个终止标记
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

-- 打开游标
open cur;

-- 读取一行数据到变量
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
-- 这个就是判断是否游标已经到达了最后
while stop 1 do
-- 各种判断
if iRep_operationtype='I' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_operationtype='U' then
begin
if iRep_status='A' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_status='B' then
delete from rs0811 where id=iId;
end if;
end;
elseif iRep_operationtype='D' then
delete from rs0811 where id=iId;
end if;

-- 读取下一行的数据
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;

end while; -- 循环结束
close cur; -- 关闭游标
end;bitsCN.com

Statement of this Website
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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to remove author and last modified information in Microsoft Word How to remove author and last modified information in Microsoft Word Apr 15, 2023 am 11:43 AM

Microsoft Word documents contain some metadata when saved. These details are used for identification on the document, such as when it was created, who the author was, date modified, etc. It also has other information such as number of characters, number of words, number of paragraphs, and more. If you might want to remove the author or last modified information or any other information so that other people don't know the values, then there is a way. In this article, let’s see how to remove a document’s author and last modified information. Remove author and last modified information from Microsoft Word document Step 1 – Go to

How to get the GPU in Windows 11 and check the graphics card details How to get the GPU in Windows 11 and check the graphics card details Nov 07, 2023 am 11:21 AM

Using System Information Click Start and enter System Information. Just click on the program as shown in the image below. Here you can find most of the system information, and one thing you can find is graphics card information. In the System Information program, expand Components, and then click Show. Let the program gather all the necessary information and once it's ready, you can find the graphics card-specific name and other information on your system. Even if you have multiple graphics cards, you can find most content related to dedicated and integrated graphics cards connected to your computer from here. Using the Device Manager Windows 11 Just like most other versions of Windows, you can also find the graphics card on your computer from the Device Manager. Click Start and then

How to share contact details with NameDrop: How-to guide for iOS 17 How to share contact details with NameDrop: How-to guide for iOS 17 Sep 16, 2023 pm 06:09 PM

In iOS 17, there's a new AirDrop feature that lets you exchange contact information with someone by touching two iPhones. It's called NameDrop, and here's how it works. Instead of entering a new person's number to call or text them, NameDrop allows you to simply place your iPhone near their iPhone to exchange contact details so they have your number. Putting the two devices together will automatically pop up the contact sharing interface. Clicking on the pop-up will display a person's contact information and their contact poster (you can customize and edit your own photos, also a new feature of iOS17). This screen also includes the option to "Receive Only" or share your own contact information in response.

The single-view NeRF algorithm S^3-NeRF uses multi-illumination information to restore scene geometry and material information. The single-view NeRF algorithm S^3-NeRF uses multi-illumination information to restore scene geometry and material information. Apr 13, 2023 am 10:58 AM

Current image 3D reconstruction work usually uses a multi-view stereo reconstruction method (Multi-view Stereo) that captures the target scene from multiple viewpoints (multi-view) under constant natural lighting conditions. However, these methods usually assume Lambertian surfaces and have difficulty recovering high-frequency details. Another approach to scene reconstruction is to utilize images captured from a fixed viewpoint but with different point lights. Photometric Stereo methods, for example, take this setup and use its shading information to reconstruct the surface details of non-Lambertian objects. However, existing single-view methods usually use normal map or depth map to represent the visible

How NameDrop works on iPhone (and how to disable it) How NameDrop works on iPhone (and how to disable it) Nov 30, 2023 am 11:53 AM

In iOS17, there is a new AirDrop feature that allows you to exchange contact information with someone by touching two iPhones at the same time. It's called NameDrop, and here's how it actually works. NameDrop eliminates the need to enter a new person's number to call or text them so they have your number, you can simply hold your iPhone close to their iPhone to exchange contact information. Putting the two devices together will automatically pop up the contact sharing interface. Clicking on the popup will display a person's contact information and their contact poster (a photo of your own that you can customize and edit, also new to iOS 17). This screen also includes "Receive Only" or share your own contact information in response

What's the reason for the delay in receiving messages on WeChat? What's the reason for the delay in receiving messages on WeChat? Sep 19, 2023 pm 03:02 PM

The reason for the delay in WeChat receiving information may be network problems, server load, version problems, device problems, message sending problems or other factors. Detailed introduction: 1. Network problems. The delay in receiving information on WeChat may be related to the network connection. If the network connection is unstable or the signal is weak, it may cause delays in information transmission. Please ensure that the mobile phone is connected to a stable network and the network signal strength is good. ; 2. Server load. When the WeChat server load is high, it may cause delays in information transmission, especially during busy periods or when a large number of users use WeChat at the same time, etc.

What is the unit for transmitting information in a computer network? What is the unit for transmitting information in a computer network? Dec 07, 2020 pm 05:26 PM

The transmission of information in computer networks is based on "words"; words are the basic unit of data transmission. Computer networks have two main functions: data communication and resource sharing, and the information transmitted in data communication is expressed in the form of binary data. Data communication is a communication method and communication service that uses data transmission technology to transfer data information between two terminals according to a certain communication protocol.

iOS 17 NameDrop: How to Easily Share Your Contact Information on iPhone iOS 17 NameDrop: How to Easily Share Your Contact Information on iPhone Jul 30, 2023 pm 05:18 PM

Apple has introduced a very fast (if not the fastest) way to share your contact information with another iPhone via NameDrop. Here's everything you need to know. What is NameDrop? iOS 17 introduces NameDrop, a revolutionary feature that leverages contact posters. These personalized cards can be created for yourself and other contacts and will appear whenever you make a call. With multiple customizable options such as photos, Memojis, monograms, and more, you can tailor your contact poster to match your personality using your preferred color scheme and fonts. NameDrop automatically shares your poster when your iPhone is in close proximity to other users, allowing both parties to effortlessly

See all articles