1.基本用法
drop PROCEDURE if EXISTS sp1; -- 如果存在sp1存储过程则删除掉
create PROCEDURE sp1() SELECT 1; --创建最简单的存储过程，其中存储过程必须加（），调用的时候也一样。
call sp1(); --调用存储过程
show procedure status; --查看所有的存储过程信息
show create procedure sp1; --查看某个存储过程
*注意：存储过程之间可以调用，不可以删除。
简单创建：
drop PROCEDURE if EXISTS sp1;
create PROCEDURE sp1() SELECT * from emp; --相当于一般的查询语句。
alter procedure ： 修改存储过程仅能修改以下的characteristic。（即修改权限可以，存储内容无法修改）
characteristic
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Alter procedure sp3 READS SQL DATA
SQL SECURITY DEFINER;
SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,
SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME='emp';
注意：delimiter 分割符使用，sql命令中因";"为默认的分隔符，也是执行语句，因此无法编写存储过程。这个时候就要重新定义分割符。
mysql> select * from emp;
+--------+----------+----------+---------+
| emp_id | emp_name | emp_type | emp_age |
+--------+----------+----------+---------+
| 1 | 张三 | 临时工 | 20 |
| 2 | 李四 | 正式工 | 40 |
| 3 | 王五 | 合同工 | 25 |
+--------+----------+----------+---------+
3 rows in set
mysql> delimiter //
mysql> select * from emp//
+--------+----------+----------+---------+
| emp_id | emp_name | emp_type | emp_age |
+--------+----------+----------+---------+
| 1 | 张三 | 临时工 | 20 |
| 2 | 李四 | 正式工 | 40 |
| 3 | 王五 | 合同工 | 25 |
+--------+----------+----------+---------+
3 rows in set
mysql>
2.变量
1.局部变量
局部变量被定义在begin和end之间使用，且要放在复杂sql语句的前面，如同一个方法的私有变量一样。
声明：DECLARE p varchar(200); --不带默认值
DECLARE p varchar(200) DEFAULT '王五'; --带默认值
mysql文档声明： DECLARE var_name[,...] type [DEFAULT value]
也就是可以同时对多个变量进行定义。用“，”号隔开
declare t1,t2,t3 int DEFAULT 2;
赋值：set p = '李四';
mysql文档赋值：SET var_name = expr [, var_name = expr] ... 用“，”号隔开
set t1=5,t3=6;
set t1=(select count(*) from emp);
select *** into
select emp.emp_name into v_name from emp LIMIT 0,1; --变量赋值仅可获得一个结果，变量名不可和表中的输出列名一样，不然拿不到sql中的值。
select v_name; --显示结果
使用 select var_name; 可以查看结果。
1 drop PROCEDURE if EXISTS sp1; 2 create PROCEDURE sp1() 3 BEGIN 4 declare t1,t2,t3 int DEFAULT 2; 5 set t1=5,t3=6; 6 select t2; 7 select t3; 8 end; 9 call sp1();
注意： declare 不但可以对变量声明，而且还可以声明条件，声明处理（多用于异常控制）。
声明条件：
DECLARE condition_name CONDITION FOR condition_value
声明处理
DECLARE....HANDLER..FOR
1 handler_action: 2 CONTINUE 3 | EXIT 4 | UNDO 5 6 condition_value: 7 mysql_error_code 8 | SQLSTATE [VALUE] sqlstate_value 9 | condition_name 10 | SQLWARNING 11 | NOT FOUND 12 | SQLEXCEPTION
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;
为1216 error_code 起名字 foreign_key_error, 声明当遇到foreign_key_error 这种状态时，程序继续。
2.用户变量
用户变量是当本次客户请求连接开始到请求结束之间。当我们在程序中调用一个存储过程A，A存储过程又调用了B存储过程，在A中定义的用户变量，在B中可以 使用。如同一个类中的私有变量一样。
声明： @
赋值： set = 赋值，select := 赋值
1 drop PROCEDURE if exists sp2; 2 create procedure sp2() 3 BEGIN 4 set @v1='1234'; 5 select @v1:='5678'; 6 select @v1; 7 end; 8 call sp2();
3. 系统变量
分为：全局变量和 会话变量。
全局变量影响服务器整体操作。要想更改全局变量，必须具有SUPER权限。
全局变量通过set方式设置，会在当前已经启动的系统中生效，但是如果系统重启，动态设置的全局变量会丢失。静态设置就是在my.ini/my.cnf文件中配置,然后重启服务。
会话变量不需要权限可以自己修改。会话变量的生命周期和用户变量一致，都是客户端连接时产生，客户端关闭时关闭。但会话变量属于客户的环境使用状况设置，而用户变 量用于存储过程。
系统变量不可自定义，只能对现有的进行修改。否则报出 unknown system varitables [v_name].
如果设置SET变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。查看时如果不设置，则会SESSION-》GLOBAL。
set session identity = 0; -- 修改会话变量 set @@session.identity = 0; --修改会话变量 set @@identity = 0; --修改会话变量
set identity =0;
show session VARIABLES like 'identity'; --查看会话变量
show session VARIABLES;--查看会话变量
select @@identity;--查看会话变量
--LOCAL 和 SESSION 是同义词，表达同样的意思set local identity = 0;
set @@local.identity = 0;
show local VARIABLES like 'identity';
show local VARIABLES;
select @@identity;
set global autocommit = 'ON'; --修改系统服务变量 set @@global.autocommit = 'ON'; --修改系统服务变量 show global VARIABLES like 'autocommit'; --查看系统服务变量 show global VARIABLES; --查看修改系统服务变量 select @@autocommit; --查看修改系统服务变量
3. 参数
参数默认是IN，也就是如果是IN的参数可以不用指定。指定有三种：IN, OUT, 或INOUT，这只对PROCEDURE是合法的，Function仅接收IN，且语句中要加强制返回 Return关键 字。
IN：
create procedure sp3(IN v_name varchar(25)) 或create procedure sp3(v_name varchar(25)) ---默认为IN
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(ov VARCHAR(25)) 3 BEGIN 4 select * from emp where emp.emp_name = ov; 5 end; 6 call sp3('张三');
OUT：
1 create PROCEDURE sp3(OUT ov VARCHAR(25)) 2 BEGIN 3 select emp.emp_type into ov from emp where emp.emp_name = '张三'; 4 end; 5 call sp3(@V); 6 select @V;
INOUT ：
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(INOUT ov VARCHAR(25)) 3 BEGIN 4 select emp.emp_type into ov from emp where emp.emp_name = ov; 5 end; 6 set @v_name = '张三'; 7 call sp3(@v_name); 8 select @v_name;
---------------------------------------------------------------------------------------------------------------------------------------------------------
4.cursor光标
光标可以对查询进行遍历，判断。
1. 创建光标
declare cur1 cursor for select emp_name from emp;
2. 打开游标
open cur1；
3. 游标查询
fetch cur1 into [v_name];
4. 关闭游标
close cur1；
注意： emp_names 一定要赋予初始值''，要不然为null，null和任意字符concat结果都为null;
以下例子：实现group_concat 功能；
1 drop PROCEDURE if EXISTS sp1; 2 create PROCEDURE sp1() 3 BEGIN 4 DECLARE done INT DEFAULT 0; 5 DECLARE emp_names,temp VARCHAR(150) DEFAULT ''; 6 DECLARE a varchar(15); 7 declare cursor1 CURSOR for select emp_name from emp; 8 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; /*这两个是一个意思*/ 9 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @l_last_sale=1; /*定义循环结束后的状态*/ 10 open cursor1; 11 REPEAT 12 FETCH cursor1 into a; 13 if not done then 14 set emp_names=CONCAT(a,',',emp_names); 15 end if; 16 until done end REPEAT; 17 close cursor1; 18 select temp; 19 select emp_names; 20 end; 21 call sp1();
5. 结构控制
1.IF：
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(INOUT ov VARCHAR(25)) 3 BEGIN 4 IF ov = '张' THEN 5 set ov='张三'; 6 ELSEIF ov='李' THEN 7 set ov='李四'; 8 ELSE 9 set ov='王五'; 10 end if; 11 select * from emp where emp.emp_name = ov; 12 end; 13 set @ov='样'; 14 call sp3(@ov);
2. CASE：
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(INOUT ov VARCHAR(25)) 3 BEGIN 4 CASE ov 5 when '张' THEN 6 set ov='张三'; 7 when '李' THEN 8 set ov='李四'; 9 ELSE 10 set ov='王五'; 11 end case; 12 select * from emp where emp.emp_name = ov; 13 end; 14 set @ov='样'; 15 call sp3(@ov);
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(INOUT ov VARCHAR(25)) 3 BEGIN 4 CASE when ov='张' THEN 5 set ov='张三'; 6 when ov='李' THEN 7 set ov='李四'; 8 ELSE 9 set ov='王五'; 10 end case; 11 select * from emp where emp.emp_name = ov; 12 end; 13 set @ov='样'; 14 call sp3(@ov);
3. Loop
[begin_label:] LOOP
statement_list
END LOOP [end_label]
label1： loop
end loop label1；
LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。
LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。
4. LEAVE语句 （相当于循环中的break,不同点可以跳出任意及循环，而一般java退出为一层一层退出for循环）
LEAVE label
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。
5. ITERATE语句(相当于循环中的continue，不同点可以继续任意循环)
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int) 3 BEGIN 4 set a=0,b=0; 5 label1:LOOP 6 set ov= ov + 1; 7 label2:LOOP 8 set a=a + 1; 9 set ov=ov+10; 10 if ov>30 THEN 11 12 leave label2; 13 end IF; 14 end loop label2; 15 set b=b + 1; 16 if ov < 60 THEN 17 ITERATE label1; 18 ELSE 19 leave label1; 20 end if; 21 end LOOP label1; 22 select * from emp; 23 end; 24 set @ov=0; 25 call sp3(@ov, @a, @b); 26 select @a,@b;
6. REPEAT语句（相当于java中go....while()语句，loop是死循环需要内部打断执行才可以，repeat是只要满足条件就退出
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int) 3 BEGIN 4 select @a := 20; 5 REPEAT 6 set @a=@a+1; 7 UNTIL @a>ov 8 end REPEAT; 9 select * from emp; 10 set a = @a; 11 end; 12 set @ov=50; 13 call sp3(@ov, @a, @b); 14 select @a,@b;
7. WHILE语句（相当于java中的while语句）
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
1 drop PROCEDURE if EXISTS sp3; 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int) 3 BEGIN 4 select @a := 20; 5 WHILE @a<ov DO 6 set @a=@a+1; 7 end WHILE; 8 select * from emp; 9 set a = @a; 10 end; 11 set @ov=30; 12 call sp3(@ov, @a, @b); 13 select @a,@b;
存储过程：预编译的sql，减少网络之间的数据传输，复杂sql语句多sql语句的处理。
二. FUNCTION
function的创建使用和procedure一样，就是将名字换成function即可。
1.不同点：
- function一般作为其它查询语句的一部分而存在。而procedure一般独立执行。
- function只能参数形式仅可为IN，仅通过return返回单个值，procedure有INOUT ，OUT形式。可返回多个值，且不需要声明return。
- function是为了解决针对性的某个问题，而存储过程多为了满足查找某项输出数据样式而设立。
创建和删除function
1 drop function if exists f1; 2 create function f1(v1 VARCHAR(20)) returns VARCHAR(50) 3 return CONCAT(v1,'HelloWord','!'); 4 select f1('My first Function ');
查看创建的function: SHOW CREATE FUNCTION f1;
1 drop function if exists f2; 2 create function f2(v1 VARCHAR(20)) returns VARCHAR(50) 3 BEGIN 4 if v1='张' THEN 5 set v1='张三'; 6 ELSEIF v1='李' THEN 7 set v1='李四'; 8 else 9 set v1='王五'; 10 end if; 11 return v1; 12 END; 13 select f2('My first Function ');
参考文摘：
MySql 存储过程实例(附完整注释) http://www.cnblogs.com/jukan/p/5815470.html
mysql存储过程语法及实例 http://www.cnblogs.com/cxxjohnson/p/5965194.html
MySQL存储过程中的用户变量，系统变量，局部变量 cla http://blog.csdn.net/qq_29027865/article/details/52623205
134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
