mysql 存储过程如何给输入的参数添加默认值 mysql存储过程写法和调用

转载

风之谷启航 2024-09-01 22:32:25

文章标签 mysql复杂存储过程写法简书存储过程 mysql ide 文章分类 MySQL 数据库

一、存储过程概念

什么是存储过程

一组为了完成特定功能的SQL 语句集。

更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查)，然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

优点

存储过程是一个预编译的代码块，执行效率比较高

一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率

可以一定程度上确保数据安全(可设定只有某些用户才具有对指定存储过程的使用权)

缺点

如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

可移植性差

很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。

代码可读性差，相当难维护。

要不要用存储过程

个人觉得各组件应该各司其职。MySQL就是存储数据的仓库，逻辑实现还是应该放在业务层。

建议看看别人说的。为什么要用存储过程

二、存储过程使用

存储过程的创建语法：

DELIMITER //
CREATE PROCEDURE 储存过程名([in|out|inout] 参数 datatype)
BEGIN
SQL语句代码快
END
//
DELIMITER ;

几点说明：

存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”

begin end 可以在只有一条sql语句的时候省略。

每条语句的末尾，都要加上分号 “;”

不能在存储过程中使用 “return” 关键字。

DELIMITER 的意思是，告诉mysql，下面的语句中，语句定界符不再是分号(;)，而是双斜线(//)。这里的双斜线可以任意指定，比如指定为|或者///，都是可以的(但不要指定为sql语句中经常出现的逗号或等号等，不然就结束了)，如：

mysql> delimiter //
mysql> create procedure simpleproc (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> //
Query OK, 0 rows affected
mysql> delimiter |
mysql> create procedure simpleproc1 (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> |
Query OK, 0 rows affected
mysql> delimiter ///
mysql> create procedure simpleproc2 (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> ///
Query OK, 0 rows affected

还要注意，定义完存储过程之后，要重新将分号(;)指定为语义分隔符。即调用DELIMITER ;

关于参数

in表示向存储过程传递参数，out表示从存储过程返回参数，而inout表示传递参数和返回参数；如果不显式指定in、out、inout，则默认为in。习惯上，对于是in的参数，我们都不会显式指定；

参数只能指定参数类型，不能指定长度；

参数不能指定默认值。

关于注释

这是

多行

注释

declare a int; -- 这是单行 MySQL 注释 (注意 -- 后至少要有一个空格)

if a is null then set a = 0; # 这也是个单行 MySQL 注释

查看已经创建的存储过程：

show procedure status where Db='数据库名';或者：先use 数据库名，再show procedure status
mysql> show procedure status where Db='cpgl';
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| cpgl | hi | PROCEDURE | root@localhost | 2017-05-30 11:39:44 | 2017-05-30 11:39:44 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| cpgl | pr_add | PROCEDURE | root@localhost | 2017-05-30 11:41:42 | 2017-05-30 11:41:42 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set

删除存储过程

drop procedure '存储过程名字';

mysql> drop procedure hi;
Query OK, 0 rows affected

实例

从网上找了几个例子，真正用到的时候，可以参考：包含了事务，参数，嵌套调用，游标，循环等使用

例子1

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 ;

例子2

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;
|
delimiter ;

游标循环次数不对的问题

在使用游标的时候，最容易出现的问题就是循环次数与实际记录数不一：第一种就是循环次数比实际记录数多一次；第二种情况就是循环次数远小于记录数。

第一种情况的发生，是由于对while do结构(或者有时候使用repeat)以及cursor的性质认识不够导致的，cursor会在找不到记录时(一般是循环完了最后一行，也有可能是循环时的select into 赋值语句结果为空导致)发生NOT FOUND，可以根据这一点来判断循环结束。而while do 结构类似于java中的do while 结构，无论怎样都会先do再判断。上面例子2中的写法可以很好的解决这个问题：在每次循环结尾，执行赋值操作，相当于让cursor的判断提前一步。

第二种情况的发生，一般是select into 赋值语句结果为空(有待研究)。

存储过程如何调试

我使用的有两种：

第一种是在call调用存储过程之前，定义全局变量，如SET @test=0；在存储过程的函数体中的某个地方，为该变量赋值，如SET @test=1；在调用完存储过程之后，使用select @test的方式查看之前定义的@test的值。如果存储过程写的没问题，执行到了你写SET @test=1；的地方，则值为1，否则还是初始值0。

第二种则是，在函数体中，使用select var1,var2...这种方式：var1,var2...是你在其中定义的变量，你想在某处查看它们的值是否与你期望的一样，就在该处写上select var1,var2...语句。这样，在控制台调用存储过程时，就会打印出这样变量的值。

下面的存储过程可供参考：

CREATE PROCEDURE copy_nfi_item(nfi_id_from varchar(100), nfi_id_to varchar(100))

COMMENT '复制nfi考核，与业务无关；如果两个部门的nfi考核项相同，可以通过该存储过程快捷复制。'

BEGIN
DECLARE v_item_id varchar(50);
DECLARE v_percent float(11,0);
DECLARE v_first_level_duty varchar(255);
DECLARE v_first_level_duty_weight float(11,0);
DECLARE v_title varchar(255);
DECLARE v_task text;
DECLARE v_task_weight float(11,0);
DECLARE v_plan_begin_date date;
DECLARE v_plan_end_date date;
DECLARE v_complete_date date;
DECLARE v_results text;
DECLARE v_scoring_standard text;
DECLARE v_results_desc text;
DECLARE v_provide_dept varchar(255);
DECLARE v_check_dept varchar(255);
DECLARE v_self_marks float(11,0);
DECLARE v_self_score float(11,2);
DECLARE v_dept_marks float(11,0);
DECLARE v_dept_score float(11,2);
DECLARE v_nonfinancial_score float(11,2);
DECLARE v_president_score float(11,2);
DECLARE v_review_marks float(11,2);
DECLARE v_review_score float(11,2);
DECLARE v_advice text;
DECLARE v_nfi_id varchar(50);
DECLARE v_executor_id varchar(50);
DECLARE v_status int(11);
DECLARE v_step int(11);
DECLARE v_dr bit(1);
DECLARE v_ts datetime;
DECLARE v_improvements text;
DECLARE v_update_ts varchar(50);
DECLARE temp_dept_id_from VARCHAR(200);
DECLARE temp_dept_id_to VARCHAR(200);
DECLARE v_count int(10) DEFAULT 0;

-- 这个用于处理游标到达最后一行的情况

DECLARE stopFlag int default 0;

-- 声明游标：指向数据源

DECLARE cur CURSOR FOR SELECT item.item_id,item.percent,item.first_level_duty,item.first_level_duty_weight,item.title,item.task,item.task_weight,item.plan_begin_date,item.plan_end_date,
item.complete_date,item.results,item.scoring_standard,item.results_desc,item.provide_dept,item.check_dept,item.self_marks,item.self_score,item.dept_marks,item.dept_score,item.review_marks,item.review_score,
item.advice,item.nfi_id,item.status,item.step,item.dr,item.ts,item.executor_id,item.nonfinancial_score,item.president_score,item.improvements FROM nfi_item item WHERE item.nfi_id = nfi_id_from;

-- 声明游标的异常处理，设置一个终止标记

-- 另一种写法：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;
-- 打开游标
OPEN cur;
/**
使用游标的一个常见问题就是循环次数不对。这样可以正确。
mysql的while与java里的类似，会先进入其中，do之后，再判断。
在循环体的最后使用fetch，是为了让游标提前更进一步，使得stopFlag=1。
*/
-- 读取一行数据到变量
FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;
-- 判断是否游标已经到达了最后
WHILE stopFlag <> 1 DO
SET v_count = v_count + 1;
-- 测试用，这样的语法会在控制台直接输出变量的值
SELECT v_item_id,v_percent, temp_dept_id_from, temp_dept_id_to, v_count;
-- 几个特殊的变量重新赋值:使用SELECT 表中字段名 INTO 变量名的方式
SELECT n.dept_id INTO temp_dept_id_from FROM nfi n WHERE n.nfi_id = nfi_id_from;
SELECT n.dept_id INTO temp_dept_id_to FROM nfi n WHERE n.nfi_id = nfi_id_to;
IF temp_dept_id_from = v_provide_dept THEN SET v_provide_dept = temp_dept_id_to;
END IF;
SET v_item_id = CONCAT(SUBSTR(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),3),SUBSTR(UUID(),1,8));
SET v_nfi_id = nfi_id_to;
SELECT n.psn_code INTO v_executor_id FROM nfi n WHERE n.nfi_id = nfi_id_to;
-- 执行插入
INSERT INTO nfi_item
(item_id,percent,first_level_duty,first_level_duty_weight,title,task,task_weight,plan_begin_date,plan_end_date,complete_date,
results,scoring_standard,results_desc,provide_dept,check_dept,self_marks,self_score,dept_marks,dept_score,review_marks,review_score,
advice,nfi_id,status,step,dr,ts,executor_id,nonfinancial_score,president_score,improvements)
VALUES(
v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements);
-- 读取一行数据到变量
FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;
END WHILE; -- 循环结束
CLOSE cur; -- 关闭游标
END

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。