MySql存储过程入门
一:什么是存储过程
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
优点:
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程可以回传值,并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
- 存储过程可以用在数据检验,强制实行商业逻辑等。
缺点:
- 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
- 存储过程的性能调校与撰写,受限于各种数据库系统。
二:存储过程的语法
1.创建和删除
#delimiter 声明结束标志
#CREATE PROCEDURE 函数的名字 创建函数
#BEGIN 开始
#END $$; 函数结束
#CALL 函数名(); 调用函数
#DROP PROCEDURE 函数名; 删除函数
delimiter $$
CREATE PROCEDURE hello_procledure()
BEGIN
select 'hello procedure';
END $$
CALL hello_procledure();
DROP PROCEDURE hello_procledure;
2.变量
###########################################################################################
#局部变量 DECLARE 变量名 变量类型 [DEFAULT 默认值]
delimiter $$
CREATE PROCEDURE sp_var01()
BEGIN
#创建变量
DECLARE name VARCHAR(32) default 'sf';
#查询变量的值
SELECT name;
#给变量赋值
set name ='ug';
SELECT `name`;
END $$
CALL sp_var01();
###########################################################################################
#用户变量(生命周期为你什么时候断MySQL连接什么时候没有,不用声明)@变量名
delimiter $$
CREATE PROCEDURE sp_var02()
BEGIN
set @userName ='zjh';
SELECT @userName;
END $$
#删除函数
drop PROCEDURE sp_var02;
CALL sp_var02;
#########################################################################################
#into 查询出来的值赋值给某变量
delimiter $$
create PROCEDURE zj_test_into()
BEGIN
SELECT b.`name` into @Bname from `user` b WHERE b.id =1;
SELECT @Bname;
END;
call zj_test_into;
3.存储过程的参数(入参出参)
########################################################################################################
#in 入参 语法:in 参数名字 参数类型
delimiter $$
CREATE PROCEDURE jh_test_in(in name VARCHAR(32))
BEGIN
set @uName=`name`;
END $$
CALL jh_test_in('你好');
SELECT @uName;
########################################################################################################
#根据id查询name in/out 变量名 变量类型
delimiter $$
create PROCEDURE jh_test_in_out(in id int ,out name VARCHAR(32))
BEGIN
SELECT u.`name` into `name` FROM user u where u.id=id;
END $$
CALL jh_test_in_out(1,@uName);
SELECT @uName;
########################################################################################################
#测试inOut 出参入参
delimiter $$
create PROCEDURE jh_test_inOut(in id int ,INOUT uname VARCHAR(32))
BEGIN
#声明一个变量接受传进来的值
DECLARE z_name VARCHAR(32);
set z_name=uname;
SELECT u.name into uname from user u WHERE u.id=id;
SELECT CONCAT(z_name,'-',uname);
END $$
set @name='zjh';
CALL jh_test_inOut(1,@name);
4.if then
########################################################################################################
#需求: 传入一个id 查询时间 大于今天的是未来,小于今天的过去
# 语法: IF 条件 then
# dosomthing;
# ELSEIF 条件 then
# dosomthing;
# ELSE
# dosomthing;
# END IF;
delimiter $$
CREATE PROCEDURE jh_test_ifthen ( IN id INT, OUT uResult VARCHAR ( 32 ) )
BEGIN
DECLARE number INT;
SELECT TIMESTAMPDIFF( MONTH, u.time, NOW( ) ) INTO number FROM USER u WHERE u.id = id;
IF
number > 0 THEN
SET uResult = '未来';
ELSEIF number < 0 THEN
SET uResult = '过去';
ELSE
SET uResult = '现在';
END IF;
END $$
#DROP PROCEDURE jh_test_ifthen;
CALL jh_test_ifthen ( 4, @result );
SELECT
@result;
5.循环(loop、REPEAT、while)
#########################################################################################
#循环的名字:loop LEAVE相当于Java的break ITERATE 相当于java的conuinte END LOOP 循环的名字
#loop 循环 打印1到10
#语法: 循环的名字:LOOP
# dosomthing
# END LOOP 循环的名字;
delimiter $$
CREATE PROCEDURE jh_test_loop()
BEGIN
DECLARE number_loop int DEFAULT 1 ;
DECLARE result VARCHAR(32);
myLoop:LOOP
select number_loop;
set number_loop=number_loop+1;
if number_loop <10 THEN
ITERATE myLoop;
ELSE
LEAVE myLoop;
end IF;
END LOOP myLoop;
END $$
DROP PROCEDURE jh_test_loop;
call jh_test_loop();
#########################################################################################
#REPEAT 循环 相当于java的do while
delimiter $$
CREATE PROCEDURE jh_test_repeat()
BEGIN
DECLARE number int default 1;
DECLARE result VARCHAR(32) DEFAULT "0";
#循环
myRepeat:REPEAT
set result=CONCAT(result,',',number);
set number=number+1;
UNTIL number >10
END REPEAT myRepeat;
select result;
END $$
call jh_test_repeat();
#########################################################################################
#while 循环 跟java的while类似
delimiter $$
CREATE PROCEDURE jh_test_while()
BEGIN
DECLARE number int DEFAULT 1;
DECLARE result VARCHAR(32) DEFAULT "1";
#循环
WHILE number < 10 do
set result =CONCAT(result,',',number);
set number = number +1;
END WHILE;
select result;
END$$
CALL jh_test_while();
6.游标和句柄
#########################################################################################
#游标语法
#1.声明语法 DECLARE 游标名字 CURSOR for 查询的结果集
#2.打开语法 OPEN 游标名字
#3.取值语法 FETCH 游标名字 INTO 变量名字(这个地方的变量要跟你查询的结果集类型要对应起来) 一次遍历一行
#4.关闭语法 CLOSE 游标名字
#句柄 HANDLER 类似Java的抓取异常 先声明一个标记 句柄的声明要放在游标下面 变量声明-----游标声明-----句柄声明
#声明句柄并赋值 DECLARE CONTINUE/EXIT HANDLER for 异常对应的状态码 set 赋值
delimiter $$
CREATE PROCEDURE jh_test_fetch(in name VARCHAR(32))
BEGIN
DECLARE uid int ;
DECLARE uname VARCHAR(32) ;
DECLARE usex VARCHAR(32) ;
DECLARE hand_flag boolean DEFAULT true;
#1.声明语法
DECLARE myCursor CURSOR for select u.id,u.name,u.sex from user u where u.name = name ;
#句柄
DECLARE CONTINUE HANDLER for 1329 set hand_flag=false;
#2.打开语法
OPEN myCursor;
#循环
myLoop:loop
#3.取值语法
FETCH myCursor INTO uid,uname,usex;
if hand_flag then
select uid,uname,usex;
ELSE
LEAVE myLoop;
end IF;
end LOOP myLoop;
SELECT uid,uname,usex;
#4.关闭语法
CLOSE myCursor;
end $$
DROP PROCEDURE jh_test_fetch;
call jh_test_fetch("c");
7.综合测试
#########################################################################################
#需求 :修改表的数据 查询所有名字为C的 然后吧id<3 的修改为D
delimiter $$
CREATE PROCEDURE jh_test_update(in name VARCHAR(32))
BEGIN
DECLARE uid int ;
DECLARE uname VARCHAR(32) ;
DECLARE usex VARCHAR(32) ;
DECLARE cur_flag boolean DEFAULT true;
DECLARE myCursor CURSOR for select u.id,u.name,u.sex FROM `user` u where u.name = name ;
DECLARE CONTINUE HANDLER for 1329 set cur_flag=false;
open myCursor ;
myLoop:loop
fetch myCursor into uid,uname,usex;
if cur_flag then
if uid < 3 then
UPDATE user u set u.`name`="d" where u.id =uid;
ELSE
ITERATE myLoop;
end if;
ELSE
LEAVE myLoop;
end if;
end loop myLoop;
CLOSE myCursor;
END $$
call jh_test_update("c")