Procedure-Function mysql

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

上一篇:linux的~和/的区别


下一篇:Combination Sum Two