mysql存储过程实例for_mysql存储过程实例

最新推荐文章于 2023-08-23 15:01:44 发布
最新推荐文章于 2023-08-23 15:01:44 发布
阅读量671 收藏
点赞数
文章标签： mysql存储过程实例for
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39942400/article/details/113590024
版权

先放入mysql存储过程，便于大家了解一下存储过程的整体代码结构：

CREATE PROCEDURE `get_branch_path`(IN company CHAR(32))

BEGIN

DECLARE _branchid CHAR(32) ;

DECLARE _branchpath LONGTEXT;

DECLARE _currentid CHAR(32);

DECLARE fetchSeqOK boolean;

DECLARE cur1 CURSOR FOR SELECT branchid FROM t_branch WHERE companyid=company;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOK=TRUE;

SET fetchSeqOK=FALSE;

DROP TEMPORARY TABLE IF EXISTS temp_table;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (

branchid CHAR(32),

branchpath LONGTEXT

);

OPEN cur1;

FETCH cur1 INTO _currentid;

fetchSeqLoop:LOOP

IF fetchSeqOK THEN

LEAVE fetchSeqLoop;

ELSE

SET _branchid = _currentid;

SET _branchpath = NULL;

CALL generate_branch_path(_currentid, _branchpath);

INSERT INTO temp_table VALUES(_branchid, _branchpath);

FETCH cur1 INTO _currentid;

END IF;

END LOOP;

CLOSE cur1;

SELECT branchid,branchpath FROM temp_table;

DROP TABLE temp_table;

END

CREATE PROCEDURE `generate_branch_path`(INOUT `_subid` varchar(255),INOUT `_subpath` longtext)

BEGIN

DECLARE _path LONGTEXT;

DECLARE _parentid CHAR(32);

DECLARE fetchSeqOK boolean;

DECLARE cur2 CURSOR FOR SELECT `name`,parentid FROM t_branch WHERE branchid=_subid;

SET @@max_sp_recursion_depth = 20;

OPEN cur2;

FETCH cur2 INTO _path,_parentid;

IF( LEFT(_parentid,1) <> 0 ) THEN

SET _subid = _parentid;

IF (_subpath IS NULL) THEN

SET _subpath = _path;

ELSE

SET _subpath = CONCAT(_path,'/',_subpath);

END IF;

CALL generate_branch_path(_subid,_subpath);

ELSE

IF (_subpath IS NULL) THEN

SET _subpath = _path;

ELSE

SET _subpath = CONCAT(_path,'/',_subpath);

END IF;

END IF;

END

上述两个存储过程所需要的数据表及测试数据：

CREATE TABLE `t_branch` (

`branchid` char(32) NOT NULL,

`companyid` char(32) NOT NULL,

`name` varchar(50) NOT NULL,

`parentid` char(32) NOT NULL,

`sortid` int(11) NOT NULL,

`remark` varchar(200) DEFAULT NULL,

`isshare` int(11) DEFAULT NULL,

`lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`createtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`branchid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_branch` VALUES ('402880ff2d6f871a012d6f871cf30000','402880ff2d5f53cd012d5f53d43d0000','销售部门','0','2','就是湖北销售部门！','0','2011-01-11 18:13:35','2011-01-10 18:46:32'),

('402880ff2d6f871a012d6f8788210001','402880ff2d5f53cd012d5f53d43d0000','hbdev','402880ff2d744a06012d74531f8e0002','1','就是湖北研发','1','2011-01-11 17:16:42','2011-01-10 18:47:00'),

('402880ff2d6f871a012d6f8816f30002','402880ff2d5f53cd012d5f53d43d0000','abc team','402880ff2d6f871a012d6f8788210001','2','就是 abc team！','1','2011-01-11 18:21:34','2011-01-10 18:47:36'),

('402880ff2d744a06012d745231660000','402880ff2d5f53cd012d5f53d43d0000','人力资源部','0','3','就是湖北人力资源部！','0','2011-01-11 18:18:11','2011-01-11 17:06:47'),

('402880ff2d744a06012d7452930e0001','402880ff2d5f53cd012d5f53d43d0000','市场部','0','4','就是湖北市场部！','0','2011-01-11 18:18:11','2011-01-11 17:07:12'),

('402880ff2d744a06012d74531f8e0002','402880ff2d5f53cd012d5f53d43d0000','PTC','0','1','就是EFG！','0','2011-01-11 17:17:23','2011-01-11 17:07:48'),

('402880ff2d749155012d7492e8e70000','402880ff2d5f53cd012d5f53d43d0000','HDK team','402880ff2d6f871a012d6f8788210001','1','就是HDK team！','0','2011-01-11 18:17:28','2011-01-11 18:17:28'),

('ff8080812e3792bb012e50492fad0018','402880ff2d5f53cd012d5f53d43d0000','新部门','402880ff2d6f871a012d6f871cf30000','1','','0','2011-02-23 10:11:37','2011-02-23 10:11:37');

1.1存储过程代码结构：

CREATE PROCEDURE 存储过程名(参数列表，有三种类型：IN,OUT,INOUT)

BEGIN

1.变量和条件声明

2. Cursor声明

3. Handler声明

4. 程序代码

END

注意：

由括号包围的参数列必须总数存在的。如果没有参数，也要使用一个空参数列()。每个参数默认都是一个IN参数，可在参数名之间加入OUT,INOUT等关键字指定为其他类型。

变量和条件声明不需放在Cursor声明等代码块之前，否则该存储过程编译不能通过。

1.2变量声明

DECLARE _branchid CHAR(32) ;

DECLARE 关键字

_branchid 变量名

CHAR(32) 变量类型

1.3指针和Handler声明

定义游标

cur1 CURSOR FOR SELECT branchid FROM t_branch WHERE companyid=company;

使用游标

open cur1；

fetch数据

FETCH cur1 INTO _currentid;

关闭游标

close cur1;

用到游标cur1，都会离不开循环语句。

一般使用Loop和while来进行循环语句的编写。

这里使用Loop为例

fetchSeqLoop:LOOP

fetch cur1 into _currentid;

end Loop;

现在是死循环，还没有退出的条件，那么在这里和oracle有区别，OraclePL/SQL的指针有个隐性变量%notfound，

Mysql是通过一个Error handler的声明来进行判断的，

DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOK=TRUE;

在Mysql里当游标遍历溢出时，会出现一个预定义的NOT FOUND的Error，我们处理这个Error并定义一个continue的handler就可以了，

关于Mysql Error handler可以查询Mysql手册。定义一个flag，在NOT FOUND，标示Flag,在Loop里以这个flag为结束循环的判断就可以了。

具体代码可以参考上面的存储过程：get_branch_path();

注意：在循环语句中，记得使用FETCH cur1 into _currentid来进行指针的数据移动，否则指针会一直指向你初次赋值的地方，而不指向下一个值。

1.4程序代码

根据上述定义的变量，临时表，指针等运用循环语句，IF/ELSE语句，函数等内容完成一定的业务逻辑。

IF语句的结构：

IF 逻辑表达式 THEN

程序语句;

ELSE

程序语句;

END IF;

WHILE语句的结构：

WHILE 逻辑表达式 DO

程序语句；

END WHILE;

LOOP语句的结构：

LOOP

程序语句；

END LOOP;

可以给代码块加lebel，这样END匹配比较直观，还可以用LEAVE语句来终结代码块:

fetchSeqLoop:LOOP

IF fetchSeqOK THEN

LEAVE fetchSeqLoop;

ELSE

程序语句；

END IF;

END LOOP;

2.1存储过程调用

CALL generate_branch_path(_currentid, _branchpath);

存储过程中的参数有三种IN,OUT,INOUT.具体用法可以看看下面的这篇文章，就能够明白：

http://www.blogjava.net/nonels/archive/2009/04/22/233324.html

2.2存储过程的结果集返回

上述存储过程中，创建了一个临时表temp_table，用于存储调用方(持久层中间件：hibenate,ibatis,JDBC)所需要的结果集。如果不考虑临时表的回收问题，在调用方可以在调用存储过程之后，再直接用sql语句查询该临时表获取结果集。但考虑到临时表的回收问题，需要在存储过程结束时，删除该临时表，并返回该结果集。使用如下代码即可达到目的。

SELECT branchid,branchpath FROM temp_table;

DROP TABLE temp_table;

在有了上面的SELECT语句之后，在DAO层调用存储过程之后，通过代码就可以获取结果集了。

使用JDBC等接口代码完成结果集的返回。

String url = "jdbc:mysql://127.0.0.1:3306/test";

try {

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Connection conn = DriverManager.getConnection(url, "root","");

CallableStatement stmt = conn.prepareCall("{call xx()}");

stmt.execute();

ResultSet rs = (ResultSet)stmt.getResultSet();

while(rs.next()){

System.out.print (rs.getString(1));

System.out.print (" , ");

System.out.println (rs.getString(2));

}

} catch (SQLException e) {

e.printStackTrace();

}

行了，关于mysql总结的经验主要就是这些了，上述代码很多借鉴了其他的一些文章，有雷同之处还望大家见谅。关于上述贴出的存储过程，数据表等是可以编译通过，并运行的。大家想学习的话，可以拷贝下来测试一下。

分享到：

18e900b8666ce6f233d25ec02f95ee59.png

72dd548719f0ace4d5f9bca64e1d7715.png

2011-02-25 19:15

浏览 945

分类:数据库

评论

weixin_39942400
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql存储过程实例
09-10
主要介绍了mysql存储过程实例,需要的朋友可以参考下
MYSQL存储过程进行写读操作（for循环）
大王叫我来巡山的博客
11-10 5168
MYSQL存储过程进行写读操作（for循环）
mysql数据库for循环 存储过程
https://gitee.com/hello-piper/PiperChat
07-19 3905
mysql存储过程 for循环生成数据
MySQL进阶篇之存储过程（procedure）
kuaixiao0217的博客
02-08 6204
主要介绍了存储过程的相关知识，包括基本语法、常量的使用、if判断、参数的使用、case、循环、游标、条件处理程序，且同时介绍了存储函数的学习知识。
mysql对结果集进行遍历(mysql双重for循环如何写)
最新发布
城南蝈蝈
08-23 784
更新的原则是type为c的currentValue的值= (type为b的currentValue) / ((type为b的currentValue) + (type为a的currentValue)) *100。上面这个需求有很多种实现方法，看到这个需求的时候，我想到的双重for循环：先查询第一个结果集，第一个结果集合里面包含oid字段。当定义完了以后，执行sql。FETCH [游标名] INTO [变量名 1 ],[变量名 2 ];FETCH [游标名] INTO [变量名 1 ],[变量名 2 ];
mysql存储过程（通俗易懂）
m0_52261367的博客
07-19 3353
declare变量名int[default1];default代表给变量赋初值用户定义的,使用时不用提前声明,用的时候直接用"@变量名"即可,并且其作用域为当前会话,可以通过=和=两种方式赋值--设置变量valueset@名称=value;set@名称=value;--设置变量valueselect@名称=value;select字段into@名称from表名;set@a=1;set@b=2;...
mysql数据存储过程参数实例详解
12-15
MySQL 存储过程参数有三种类型：in、out、inout。它们各有什么作用和特点呢？ 一、MySQL 存储过程参数（in） MySQL 存储过程 “in” 参数：跟 C 语言的函数参数的值传递类似， MySQL 存储过程内部可能会修改此参数...
php调用mysql存储过程实例分析
10-24
主要介绍了php调用mysql存储过程,综合各种常见实例分析了php调用mysql存储过程的各种常见操作与使用技巧,具有一定的参考借鉴价值,需要的朋友可以参考下
MySQL存储过程使用实例详解
01-19
例1、一个简单存储过程游标实例 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$CREATE PROCEDURE getUserInfo(in date_day datetime)— — 实例— 存储过程名为：getUserInfo— 参数为：date_day...
MySQL中的存储过程（详细篇）
m0_67391121的博客
07-29 2116
由MySQL5.0版本开始支持存储过程。如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。就是数据库SQL语言层面的代码封装与重用。...
mysql存储过程循环表
12-15
一个简单易理解的mysql存储过程 循环表操作 使用游标
MySQL存储过程
热门推荐
ReincarnationArm的博客
05-04 2万+
目录 一、存储过程 1.1什么是存储过程 1.2存储过程的优缺点 二、创建存储过程 2.1语法与特性： 2.2声明语句分隔符 2.3过程体的标识 2.4存储过程参数设置 三、存储过程中的变量 四、MySQL变量 五、设置用户执行存储过程的权限 六、查看存储过程的状态 七、查看存储过程的创建代码 八、修改存储过程 九、删除存储过程 十、存储过程的控制语句 十一、IF-ELSEIF-ELSE条件语句 十二、CASE条件语句 十三、WHILE循环语句 十四、LOOP循环语句
存储过程中遍历游标的三种方式(For,Fetch,While)
雨夜星辰03--的博客
12-24 1657
create or replace procedure myprocedure is CURSOR CUR_TEST IS --声明显式游标 SELECT ECODE,ENAME FROM EMP; CUR CUR_TEST%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录 ...
mysql for循环存储过程
王洪洪的博客
08-22 1303
DROP PROCEDURE IF EXISTS test_insert; DELIMITER ;; CREATE PROCEDURE test_insert () BEGIN DECLARE i int; set i=14; WHILE i<30 DO insert into student(name,age,nianji,phone...
在MySQL中使用游标和for循环
寒冰屋的专栏
03-30 1万+
​​​​​​​在本文中，我们将学习使用MySQL游标和for循环，它们的功能类似于一般编程中的迭代循环。我们还将介绍变量、游标和处理程序声明。
使用存储过程和for循环往mysql批量插入数据
不因俊俏难为友，正为风流始读书
04-12 1369
使用存储过程和for循环往mysql批量插入数据
mysql 游标用法
wrh_csdn的博客
04-28 293
12345678910111213141516171819202122232425262728293031delimiter $create PROCEDURE phoneDeal()BEGIN    DECLARE  id varchar(64);   -- id    DECLARE  phone1  varchar(16); -- phone    DECLARE  password1  v...
Mysql存储过程游标加循环
瑛民星的专栏
02-18 5498
Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。 一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。 首先先介绍循环的分类： (1)WHILE ... END WHILE   (2)LOOP ... END LOOP   (3)REPEAT ... EN
MySQL存储过程－循环遍历查询到的结果集
IT之旅 的博客
05-07 1万+
MySQL存储过程－循环遍历查询到的结果集 1、创建存储过程 根据MySQL的语法创建存储过程，要注意的是如果循环遍历查询到的结果集，取出结果集中的数据做操作。 CREATE DEFINER=`root`@`%` PROCEDURE `alte...
mysql存储过程实例详解
05-17
MySQL 存储过程是一种封装了 SQL 语句的代码块，可以在 MySQL 数据库中创建、存储和调用。存储过程可以接受输入参数、输出参数和返回值，可以使用分支、循环、异常处理等复杂的编程语言结构，适用于处理复杂的数据操作和业务逻辑。 下面是一个简单的 MySQL 存储过程实例： ```mysql DELIMITER // CREATE PROCEDURE sp_get_customer(IN customer_id INT, OUT customer_name VARCHAR(50)) BEGIN SELECT customer_name INTO customer_name FROM customers WHERE customer_id = customer_id; END // DELIMITER ; ``` 这个存储过程名为 `sp_get_customer`，有一个输入参数 `customer_id` 和一个输出参数 `customer_name`。存储过程的作用是根据传入的 `customer_id` 查询 `customers` 表中对应的客户名，并将其赋值给输出参数 `customer_name`。 使用以下语句调用这个存储过程： ```mysql CALL sp_get_customer(1, @customer_name); SELECT @customer_name; ``` 这个语句会查询 `customers` 表中 `customer_id` 为 1 的客户名，并将其赋值给变量 `@customer_name`，然后输出变量的值。 存储过程可以简化复杂的数据操作和业务逻辑，提高数据库的性能和安全性。但是，存储过程也有一些限制和注意事项： 1. 存储过程的语法比较复杂，需要熟悉 MySQL 的编程语言和语法规则。 2. 存储过程可能会影响数据库的性能和可维护性，应该谨慎使用和设计。 3. 存储过程可能会影响数据库的安全性，应该限制存储过程的访问权限和操作权限。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值