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; 可以查看结果。
drop PROCEDURE if EXISTS sp1;
create PROCEDURE sp1()
BEGIN
declare t1,t2,t3 int DEFAULT 2;
set t1=5,t3=6;
select t2;
select t3;
end;
call sp1();
sp1
注意: declare 不但可以对变量声明,而且还可以声明条件,声明处理(多用于异常控制)。
声明条件:
DECLARE condition_name CONDITION FOR condition_value
声明处理
DECLARE....HANDLER..FOR
handler_action:
CONTINUE
| EXIT
| UNDO condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| 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 := 赋值
drop PROCEDURE if exists sp2;
create procedure sp2()
BEGIN
set @v1='';
select @v1:='';
select @v1;
end;
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
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(ov VARCHAR(25))
BEGIN
select * from emp where emp.emp_name = ov;
end;
call sp3('张三');
OUT:
create PROCEDURE sp3(OUT ov VARCHAR(25))
BEGIN
select emp.emp_type into ov from emp where emp.emp_name = '张三';
end;
call sp3(@V);
select @V;
INOUT :
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(INOUT ov VARCHAR(25))
BEGIN
select emp.emp_type into ov from emp where emp.emp_name = ov;
end;
set @v_name = '张三';
call sp3(@v_name);
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 功能;
drop PROCEDURE if EXISTS sp1;
create PROCEDURE sp1()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE emp_names,temp VARCHAR(150) DEFAULT '';
DECLARE a varchar(15);
declare cursor1 CURSOR for select emp_name from emp;
DECLARE CONTINUE HANDLER FOR SQLSTATE '' SET done = 1; /*这两个是一个意思*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @l_last_sale=1; /*定义循环结束后的状态*/
open cursor1;
REPEAT
FETCH cursor1 into a;
if not done then
set emp_names=CONCAT(a,',',emp_names);
end if;
until done end REPEAT;
close cursor1;
select temp;
select emp_names;
end;
call sp1();
5. 结构控制
1.IF:
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(INOUT ov VARCHAR(25))
BEGIN
IF ov = '张' THEN
set ov='张三';
ELSEIF ov='李' THEN
set ov='李四';
ELSE
set ov='王五';
end if;
select * from emp where emp.emp_name = ov;
end;
set @ov='样';
call sp3(@ov);
2. CASE:
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(INOUT ov VARCHAR(25))
BEGIN
CASE ov
when '张' THEN
set ov='张三';
when '李' THEN
set ov='李四';
ELSE
set ov='王五';
end case;
select * from emp where emp.emp_name = ov;
end;
set @ov='样';
call sp3(@ov);
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(INOUT ov VARCHAR(25))
BEGIN
CASE when ov='张' THEN
set ov='张三';
when ov='李' THEN
set ov='李四';
ELSE
set ov='王五';
end case;
select * from emp where emp.emp_name = ov;
end;
set @ov='样';
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,不同点可以继续任意循环)
ITERATE label
ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为:“再次循环。”
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
BEGIN
set a=0,b=0;
label1:LOOP
set ov= ov + 1;
label2:LOOP
set a=a + 1;
set ov=ov+10;
if ov>30 THEN leave label2;
end IF;
end loop label2;
set b=b + 1;
if ov < 60 THEN
ITERATE label1;
ELSE
leave label1;
end if;
end LOOP label1;
select * from emp;
end;
set @ov=0;
call sp3(@ov, @a, @b);
select @a,@b;
6. REPEAT语句(相当于java中go....while()语句,loop是死循环需要内部打断执行才可以,repeat是只要满足条件就退出
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
BEGIN
select @a := 20;
REPEAT
set @a=@a+1;
UNTIL @a>ov
end REPEAT;
select * from emp;
set a = @a;
end;
set @ov=50;
call sp3(@ov, @a, @b);
select @a,@b;
7. WHILE语句(相当于java中的while语句)
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
drop PROCEDURE if EXISTS sp3;
create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
BEGIN
select @a := 20;
WHILE @a<ov DO
set @a=@a+1;
end WHILE;
select * from emp;
set a = @a;
end;
set @ov=30;
call sp3(@ov, @a, @b);
select @a,@b;
存储过程:预编译的sql,减少网络之间的数据传输,复杂sql语句多sql语句的处理。
二. FUNCTION
function的创建使用和procedure一样,就是将名字换成function即可。
1.不同点:
- function一般作为其它查询语句的一部分而存在。而procedure一般独立执行。
- function只能参数形式仅可为IN,仅通过return返回单个值,procedure有INOUT ,OUT形式。可返回多个值,且不需要声明return。
- function是为了解决针对性的某个问题,而存储过程多为了满足查找某项输出数据样式而设立。
创建和删除function
drop function if exists f1;
create function f1(v1 VARCHAR(20)) returns VARCHAR(50)
return CONCAT(v1,'HelloWord','!');
select f1('My first Function ');
查看创建的function: SHOW CREATE FUNCTION f1;
drop function if exists f2;
create function f2(v1 VARCHAR(20)) returns VARCHAR(50)
BEGIN
if v1='张' THEN
set v1='张三';
ELSEIF v1='李' THEN
set v1='李四';
else
set v1='王五';
end if;
return v1;
END;
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