MYSQL的存储过程、变量、函数及部分简单优化（非常有用，有存储过程+光标实例）

以下文章为参考的，文章出处:http://blog.csdn.net/l1028386804/article/details/46612859

①：存储过程

1、创建语法 copy

1. CREATE PROCEDURE 存储过程名称 ([IN|OUT|INOUT] 参数名称 参数类型) 
   BEGIN  SQL语句   END;   

示例
-- 简单的存储过程（无参数查询全部数据）  copy

1. DROP PROCEDURE IF EXISTS Proc;    
2. DELIMITER //  
3. CREATE PROCEDURE Proc()   
4. BEGIN  
5.   SELECT * FROM orders;  
6. END//  
7. DELIMITER ;  
8. CALL Proc();   

这里的逻辑是

1、先判断是否有Proc() 这个存储过程，有就drop掉

2、创建Proc() 存储过程

3、执行Proc() 存储过程

4、查看Proc()存储过程

5、删除Proc()存储过程

注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，

为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”结束存储过程。

存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符！！！！！！！！！

注意：当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MYSQL的转义字符！！！

--  输出参数的存储过程

[sql] view plain copy

1. DROP PROCEDURE IF EXISTS CountProc;   
2. DELIMITER //  
3. CREATE PROCEDURE CountProc(OUT param1 INT)  
4. BEGIN  
5. SELECT    COUNT(*) INTO  param1 FROM s_visit_log;  
6. END//  
7. DELIMITER ;  
8. CALL CountProc(@aaa);  
9. SELECT @aaa; 
10. SHOWPROCEDURE STATUS  LIKE '%Proc%';
11. DROP PROCEDURE Proc;

2、存储过程操作符：

l 算术运算符

+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3;  3
%     取模 SET var6=10%3 ;     1

l 比较运算符

>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN  不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE         简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL  不为空 0 IS NOT NULL True

l 逻辑运算符

  与(AND)

  或(OR) 

  异或(XOR) 

l 位运算符

|   位或

&   位与
<<  左移位
>>  右移位
~   位非(单目运算，按位取反)

3.存储过程基本函数：（内置函数）

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类

 

l 字符串类 

CHARSET(str) //返回字串字符集
CONCAT (string2  [,... ]) //连接字串
INSTR (string ,substring )//返回substring首次在string中出现的位置,不存在返回0
LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定开始位置
LCASE (string2 ) //转换成小写
UCASE (string2 ) //转换成大写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
TRIM (string2 ) //去除空格
SPACE(count) //生成count个空格 
SUBSTRING (str , position  [,length ]) //从str的position开始,取length个字符

RIGHT(string2,length) //取string2最后length个字符

l 数学类
RAND([seed]) //随机数

CEILING (number2 ) //向上取整

FLOOR (number2 ) //向下取整
ROUND (number  [,decimals ]) //四舍五入,decimals为小数位数]

l 日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CURRENT_DATE (  ) //当前日期(2017-10-26)
CURRENT_TIME (  ) //当前时间(13:52:20)
CURRENT_TIMESTAMP (  ) //当前时间戳(2017-10-26  13:52:20)
DATE (datetime ) //返回datetime的日期部分('2003-12-31 01:02:03')
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间

DATEDIFF (date1 ,date2 ) //两个日期差
DATE_FORMAT (datetime ,格式 ) //使用formatcodes格式显示datetime

%Y(年份|4位)、%y(年份|2位)、%M(月份|January-December)、%m(月份|00-12)、

%W(周|周日-周六)、%w(周|0-6)、%p(上下午|上午AM、下午PM)%D(日期|0th.1st.2nd.)、

%d(日期|00-31)、%H(小时|0-23)、%h(小时|01-12)、%i(分钟|00-59)、%S(秒|00-59);

示例：SELECT DATE_FORMAT('1995-04-24 12：21：00','%H:%i:%S') 查询得到'12:21:00'

SELECT DATE_FORMAT('1995-04-24 12:21:00','%W:%M:%Y') 查询得到'Saturday October 1995'

MONTHNAME (date ) //英文月份名
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
MONTH(datetime) //月

DAY (date ) //返回日期的天

HOUR(datetime) //小时，0-23
MINUTE(datetime) //分，0-59

SECOND(datetime)//秒，0-59
MICROSECOND(datetime) //微秒
NOW (  ) //当前时间
DAYNAME (date ) //英文星期
DAYOFYEAR (date ) //一年中的第几天，1-365
DAYOFMONTH(datetime) //月的第几天，1-31
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
LAST_DAY(date) //date的月的最后日期

QUARTER(date)//返回date对应的季度，1-4

4.存储过程优化：

（一）SQL优化。存储过程封装的SQL自身的优化；SQL优化详情请见本系列前几篇。

（二）操作前检查。在向表插入数据之前，要判断该条数据在表中是否已经存在了，如果存在，则不再插入数据。同理，在从表中查询数据之前，要先判断该条数据在表中是否存在，如果存在，才能从表中查找数据。

（三）结果标识规范化。不管向表插入数据的操作执行成功与否，都应该有一个标识值来表示执行的结果，这样也方便开发人员对程序流程的追踪和调试。比如，在每条leave语句之前，都应该有一个返回值，我们为此定义一个输出参数。

（四）列出具体的字段名。在以后的软件版本中，新增了字段，那么这条insert语句极有可能会报错。所以罗列出所需字段是规范化很重要的一点。

（五）失败返回。在执行insert语句之后，要用MySQL中自带的@error_count参数来判断插入数据是否成功，方便开发人员跟踪执行结果。比如：如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示操作失败。

DEMO:

@error_count如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示操作失败。

drop procedure if exists p_optimization;delimiter $$create procedure p_optimization(    in    testid int,    out   p_result    int   -- 0-succ, other-fail)         p_optimization_label:begin         -- 循环开始       declare  p_usertype   int;       declare  p_datacount  int;       select count(*) into p_datacount from user1 where id=testid;         -- 第一个结果集       if p_datacount> 0 then               --  第一个判断开始，p_datacount大于0就跳出循环       begin           select name into p_usertype from user1 where id=testid;       end;       else       begin           set p_result = 1;                                   leave p_optimization_label;      -- 循环结束       end;       end if;                                      --  第一个判断结束       select count(*) into p_datacount from user1 where id=testid;       if p_datacount = 0 then                  --  第二个判断开始       begin           insert into user1(name) values('辅助');           if @error_count!=0 then          -- 内嵌的判断开始           begin               set p_result= 3;           end;           else           begin               set p_result= 0;           end;           end if;                              -- 内嵌的判断结束       end;       else       begin           set p_result = 2;       end;       end if;                          --  第二个判断结束       leave p_optimization_label;end;$$delimiter ;//调用测试：set @id = 10;call p_optimization(@id,@p_result);select @id,@p_result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

②：变量的使用  

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END程序中

1、定义变量

在存储过程中定义变量 copy

1. DECLARE var_name[,varname]...date_type[DEFAULT VALUE];  

var_name为局部变量的名称。DEFAULT VALUE子句给变量提供一个默认值。值除了可以被声明为一个常数外，还可以被指定为一个表达式。

如果没有DEFAULT子句，初始值为NULL view plain copy

1. DECLARE MYPARAM INT DEFAULT 100;  

2、为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值，MYSQL中使用SET语句为变量赋值

声明3个变量，分别为var1，var2和var3 view plai copy

1. DECLARE var1,var2,var3 INT;  
2. SET var1=10,var2=20;  
3. SET var3=var1+var2;  

 MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值 view plain copy

1. DECLARE NAME CHAR(50);  
2. DECLARE id DECIMAL(8,2);  
3. SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2; 

③：光标

MYSQL里叫光标，SQLSERVER里叫游标，实际上一样的

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1．声明光标

MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下：  copy

1. DECLARE cursor_name CURSOR FOR select_statement ;  

cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集

下面声明一个名为cur_employee的光标。代码如下： copy

1. DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;  

上面的示例中，光标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。

2．打开光标

MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下： copy

1. OPEN  cursor_name ;   

其中，cursor_name参数表示光标的名称。

下面打开一个名为cur_employee的光标，代码如下：  copy

1. OPEN  cur_employee ;   

3．使用光标

MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下： copy

1. FETCH cur_employee INTO var_name[,var_name…] ;  

其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下： view plain copy

1. FETCH  cur_employee INTO emp_name, emp_age ;   

上面的示例中，将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。

4．关闭光标

MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下： view pla copy

1. CLOSE  cursor_name ;   

其中，cursor_name参数表示光标的名称。

【示例14-11】 下面关闭一个名为cur_employee的光标。代码如下： copy

1. CLOSE  cur_employee ;   

上面的示例中，关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。

注意：MYSQL中，光标只能在存储过程和函数中使用！！

存储过程光标DEMO：题目和SQL语句，可直接在MYSQL中运行。

            orders orderId、clientId、salesmanId、amount、datetimes、       omonth（1）订单表：订单id、客户id、      业务员id、    订单金额、订单日期时间、订单月份，                 客户id的外键为客户表的id，按订单月份分区         clients   clientId、cname、cphone、             createdate（2）客户表：客户id、客户姓名、客户联系电话、创建时间，对客户姓名创建索引（3）书写一个存储过程，输入参数为月份，输出参数为一个字符串，统计该月姓王的客户的订单数量和订单总金额，      且订单不包含15号的，将客户姓名、订单数量、订单总额拼接起来，每两个字段直接用“--”（两个英文的中划线）隔开，每两行记录之间换行。/*创建数据库*/drop DATABASE orders_clients;create DATABASE `orders_clients`;/*创建订单表*/use orders_clients;CREATE TABLE orders (orderId int PRIMARY KEY auto_increment,clientId int not null,salesmanId int null,amount DOUBLE null,datetime DATE null,omonth int null);INSERT into orders(clientId,amount,datetime,omonth) VALUES(1,500,'2017-04-22',4);INSERT into orders(clientId,amount,datetime,omonth) VALUES(1,600,'2017-04-15',4);INSERT into orders(clientId,amount,datetime,omonth) VALUES(2,700,'2017-04-14',4);INSERT into orders(clientId,amount,datetime,omonth) VALUES(2,800,'2017-04-14',4);INSERT into orders(clientId,amount,datetime,omonth) VALUES(3,1000,'2017-07-22',7);/*创建客户表*/use orders_clients;CREATE TABLE clients (clientId int PRIMARY KEY auto_increment,cname VARCHAR(10) not null,cphone VARCHAR(11) null,createdate date null);INSERT into clients(cname,createdate) VALUES('张三','1995-04-24');INSERT into clients(cname,createdate) VALUES('张四','1996-04-24');INSERT into clients(cname,createdate) VALUES('李五','1997-04-24');-- 建立两表外键关系，客户表的id为订单表外键alter table ordersadd constraint fk_clientIdforeign key(clientId)references clients(clientId);-- 第一题SELECT orderId,clientId,salesmanId,amount,datetime,omonth FROM orders GROUP BY omonth;-- 第二题 create unique index ucname on `clients`(`cname`);-- 第三题use orders_clients;drop PROCEDURE IF EXISTS top3;CREATE PROCEDURE top3(OUT str VARCHAR(255),months INT)  BEGIN   declare staticVARCHAR(255) DEFAULT "";  declare staticsVARCHAR(255) DEFAULT "";  declare doneint DEFAULT 0;  declare aint DEFAULT 0;-- 建立游标DECLARE f1 CURSOR FORSELECT CONCAT(cname,"--",count(o.orderId),"--",sum(o.amount))FROM clients c,orders o WHERE c.clientId=o.clientId ANDc.cname LIKE '张%' AND DATE_FORMAT(datetime,'%d')<>15 AND o.omonth=months   GROUP BY o.clientId,datetime;-- 定义状态量，为条件处理DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;-- 开启游标  OPEN f1;-- 开始循环REPEAT-- 使用游标FETCH f1 INTO static;IF NOT done THEN SET statics=Concat(statics,static);-- 叠加SET a=a+1;IF a%2=0 THEN SET statics=Concat(statics,'\n');END IF;END IF;UNTIL done END REPEAT;-- 当done=1时结束循环-- 关闭游标CLOSE f1;SELECT statics INTO str;END;-- 测试SET @s1=""; SET @s=4;   call top3(@s1,@s);select @s1;-- 查询得到：张三--1--500张四--2--1500张四--1--1200
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

④：流程控制 view pl cop

用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套

1．IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下： copy

1. IF search_condition THEN statement_list   
2. [ELSEIF search_condition THEN statement_list] ...   
3. [ELSE statement_list]   
4. END IF   

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句

下面是一个IF语句的示例。代码如下： 

view plain copy

1. IF age>20 THEN SET @count1=@count1+1;    
2. ELSEIF age=20 THEN SET @count2=@count2+1;    
3. ELSE SET @count3=@count3+1;    
4. END IF;  

2．CASE语句

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下： copy

1. CASE case_value   
2. WHEN when_value THEN statement_list   
3. [WHEN when_value THEN statement_list] ...   
4. [ELSE statement_list]   
5. END CASE   

其中，case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

CASE语句还有另一种形式。该形式的语法如下： copy

1. CASE   
2. WHEN search_condition THEN statement_list   
3. [WHEN search_condition THEN statement_list] ...   
4. [ELSE statement_list]   
5. END CASE   

其中，search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句。

下面是一个CASE语句的示例。代码如下： copy

1. CASE age   
2. WHEN 20 THEN SET @count1=@count1+1;   
3. ELSE SET @count2=@count2+1;   
4. END CASE ;   

代码也可以是下面的形式： copy

1. CASE   
2. WHEN age=20 THEN SET @count1=@count1+1;   
3. ELSE SET @count2=@count2+1;   
4. END CASE ;   

本示例中，如果age值为20，count1的值加1；否则count2的值加1。CASE语句都要使用END CASE结束。

注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止！！

3．LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：  copy

1. LOOP   
2. statement_list   
3. END LOOP  

statement_list参数表示需要循环执行的语句。

下面是一个LOOP语句的示例。代码如下：

1. add_num: LOOP    
2. SET @count=@count+1;    
3. END LOOP add_num ;   

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

LOOP循环都以END LOOP结束。

4．LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下： view plain copy

1. LEAVE label   

其中，label参数表示循环的标志。

下面是一个LEAVE语句的示例。代码如下：  copy

1. LOOP   
2. SET @count=@count+1;   
3. IF @count=100 THEN   
4. LEAVE add_num ;   
5. END LOOP;  

该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。

5．REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

REPEAT   

1. statement_list   
2. UNTIL search_condition   
3. END REPEAT 

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个ITERATE语句的示例。代码如下： copy

1. REPEAT   
2. SET @count=@count+1;   
3. UNTIL @count=100   
4. END REPEAT;   

该示例循环执行count加1的操作，count值为100时结束循环。

6．WHILE语句

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

WHILE语句的基本语法形式如下：

view plain copy

1. [begin_label:] WHILE search_condition DO   
2. statement_list   
3. END WHILE [end_label]   

其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；

statement_list参数表示循环的执行语句。

下面是一个ITERATE语句的示例。代码如下： view plain copy

1. WHILE @count<100 DO   
2. SET @count=@count+1;   
3. END WHILE ;   

该示例循环执行count加1的操作，count值小于100时执行循环。

⑤：索引（index）

优点：
   大大加快数据的检索速度;
   创建唯一性索引，保证数据库表中每一行数据的唯一性;
   加速表和表之间的连接;
   在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
    索引需要占物理空间
    当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

1.普通（唯一）索引的创建： view plain copy

1. SETCREATE[UNIQUE] INDEX 索引名称 ON 表名（列名） view plai cop

2.查看索引：Show index  keys from 表名；

3.删除索引：drop index 索引名 ON  表名。

⑥：数据库性能优化

1．SQL语句优化

选择合理的表名顺序

避免使用SELECT *

删除重复记录

减少对表的查询

使用表的别名

用EXISTS替代IN

NOT EXISTS替代NOT IN

用表连接替换EXISTS

用EXISTS替换DISTINCT

2．索引优化

当有多个索引列时，且包含非”=”号时，用”=”号索引；

当有多个索引列时，且都为非”=”号时，只用一个索引；

用>=替代>；

用UNION替换OR；

避免在索引列上使用IS NULL和IS NOT NULL；

用UNION-ALL替换UNION；

3．EM工具优化

Oracle 10g的EM中提供了两个新工具SQL优化指导和SQL优化顾问

SQL优化指导

SQL访问指导