Jslfl【软件开发技术笔记】

转 mysql存储过程说明

2014 年 12 月 16 日 jslfl mysql

原文:http://levi.cg.am/archives/3809转载

存储过程 Stored Procedure

存储过程就是保存一系列SQL命令的集合，将这些sql命令有组织的形成一个小程序，这样会实现很复杂的处理。SQL基本是一个命令一个命令执行，虽然可以通过连接、子查询等实现些高级的处理，但局限性是显而易见的

存储过程的优势

提高执行性能(存储过程事先完成了解析、编译的处理，执行时能减轻数据库负担)
可减轻网络负担(比起多次传递SQL命令本身，这大大减轻了网络负担)
可防止对表的直接访问(可只赋予用户对相关存储过程的访问权限)
存储过程会保存在数据库中，应用程序只需要知道调用哪个存储过程就可以完成相应处理
使用存储过程

参数种类分为：

IN：输入型
OUT：输出型
INOUT：输入输出型

SELECT column1.. INTO 变量1... FROM table1 WHERE xxx; //这个变量1对应OUT，INOUT
格式：
create procedure 存储过程名(
    参数种类1 参数1 参数类型1
    参数种类2 参数2 参数类型2...）
begin
    处理内容
end

示例：

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //
CREATE PROCEDURE search_customer(
    IN p_nam VARCHAR(20))
BEGIN
    IF p_nam IS NULL OR p_nam = '' THEN
        SELECT * FROM customer;
    ELSE
        SELECT * FROM customer WHERE nam LIKE p_nam;
    END IF;
END
//
DELIMITER;

注意事项

DELIMITER命令改变分隔符
默认分隔符是“;”存储过程中肯定会有“;”，所以使用其将分隔符改为“//”，创建好后，在将分隔符改回“;”
可使用的控制语句
简单的实例

创建存储函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> DELIMITER //                      
mysql> CREATE PROCEDURE search_nam(  
    ->       IN p_nam VARCHAR(20))
    -> BEGIN
    ->       IF p_nam IS NULL OR p_nam='' THEN
    ->            SELECT * FROM USER3;
    ->       ELSE
    ->            SELECT * FROM USER3 WHERE name LIKE p_nam;    
    ->       END IF;
    -> END
    -> //
Query OK, 0 rows affected (0.05 sec)
 
mysql> DELIMITER;

查看创建的存储函数语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SHOW CREATE PROCEDURE search_nam \G;
*************************** 1. row ***************************
       Procedure: search_nam
        sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `search_nam`(
      IN p_nam VARCHAR(20))
BEGIN
      IF p_nam IS NULL OR p_nam='' THEN
           SELECT * FROM USER3;
      ELSE
           SELECT * FROM USER3 WHERE name LIKE p_nam;
      END IF;
END
1 row in set (0.00 sec)

查看创建的存储函数状态

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW PROCEDURE STATUS \G;
*************************** 1. row ***************************
           Db: test
         Name: search_nam
         Type: PROCEDURE
      Definer: root@localhost
     Modified: 2011-08-13 05:40:12
      Created: 2011-08-13 05:40:12
Security_type: DEFINER
      Comment:
1 row in set (0.00 sec)

调用存储过程，成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> CALL search_nam('aa%');
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL search_nam('');  
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
+------+------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

演示OUT类型参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> DELIMITER //
mysql> CREATE PROCEDURE search_nam2(
    ->      IN p_nam VARCHAR(20),
    ->      OUT p_num INT)
    -> BEGIN
    ->      IF p_nam IS NULL OR p_nam='' THEN
    ->           SELECT * FROM user3;
    ->      ELSE      
    ->           SELECT * FROM USER3 WHERE name LIKE p_nam;
    ->      END IF;
    ->      SELECT FOUND_ROWS() INTO p_num;
    -> END
    -> //
mysql> DELIMITER ;
mysql> SHOW PROCEDURE STATUS ;
+------+-------------+-----------+----------------+---------------------+---------------------+
| Db   | Name        | Type      | Definer        | Modified            | Created             |
+------+-------------+-----------+----------------+---------------------+---------------------+
| test | search_nam  | PROCEDURE | root@localhost | 2011-08-13 05:40:12 | 2011-08-13 05:40:12 |
| test | search_nam2 | PROCEDURE | root@localhost | 2011-08-13 05:56:37 | 2011-08-13 05:56:37 |
+------+-------------+-----------+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

调用成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> CALL search_nam3('bb%',@num);
+------+------+
| id   | name |
+------+------+
|    2 | bbb  |
+------+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @num;
+------+
| @num |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

删除存储过程

1
DROP PROCEDURE search_nam;

存储过程中的条件判断

变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。

1
2
3
4
5
6
7
8
9
10
11
12
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
     -> begin
     ->     declare x1 varchar(5) default 'outer';  
     -> begin
     ->     declare x1 varchar(5) default 'inner';  
     ->     select x1;  
     -> end;  
     ->     select x1;  
     -> end;  
     -> //  
mysql > DELIMITER;

IF语句

格式：

IF situation=1  THEN
        command1;
ELSEIF  situation=2  THEN
        command2;
ELSE
        command3;
END IF ;

IF多分枝演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> DELIMITER //                
mysql> CREATE PROCEDURE depart(    
    ->      IN de_nam VARCHAR(10))
    -> BEGIN
    ->      IF de_nam=1 THEN
    ->              SELECT * FROM USER3 WHERE depart='IT';  
    ->      ELSEIF de_nam=2 THEN
    ->              SELECT * FROM USER3 WHERE depart='HR';
    ->      ELSE
    ->              SELECT * FROM USER3 WHERE depart='BOSS';
    ->      END IF;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

演示成功

1
2
3
4
5
6
7
mysql> CALL depart(2);
+------+------+--------+
| id   | name | depart |
+------+------+--------+
|    2 | bbb  | HR     |
+------+------+--------+
1 row in set (0.00 sec)

CASE语句
格式:

CASE  situation
        WHEN 1 THEN  command1;
        WHEN 2 THEN  command2;
        WHEN 3 THEN  command3;
        ELSE         command4;
END CASE;

CASE演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> DELIMITER //
mysql> CREATE PROCEDURE depart2(
    ->       IN de_num INT)
    -> BEGIN
    ->       CASE de_num
    ->       WHEN 1 THEN
    ->              SELECT * FROM USER3 WHERE depart='IT';
    ->       WHEN 2 THEN
    ->              SELECT * FROM USER3 WHERE depart='HR';    
    ->       ELSE
    ->              SELECT * FROM USER3 WHERE depart='BOSS';  
    ->       END CASE;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL depart2(1);
+------+------+--------+
| id   | name | depart |
+------+------+--------+
|    1 | aaa  | IT     |
+------+------+--------+
1 row in set (0.00 sec)

WHILE (前置判断)
根据条件，循环有可能一次不执行
格式：

WHILE  situation >1 DO
        command1;
END WHILE;

演示while

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_sum(
    ->       IN p_num INT,
    ->       OUT res INT)
    -> BEGIN
    ->       SET res=1;
    ->       WHILE p_num > 1 DO
    ->           SET res=res * p_num;
    ->           SET p_num=p_num - 1;
    ->       END WHILE;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;  
mysql> CALL sp_sum(5,@res);
Query OK, 0 rows affected (0.00 sec)

演示成功

1
2
3
4
5
6
7
mysql> SELECT @res;
+------+
| @res |
+------+
| 120  |
+------+
1 row in set (0.00 sec)

REPEAT (后置判断)
不论条件如何，循环至少会执行一次 command1
格式：

REPEAT 
        command1;
UNTIL situation<=1  END REPEAT;

演示repeat

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_sum2(
    ->         IN p_num INT,
    ->         OUT res INT)
    -> BEGIN
    ->         SET res = 1;
    ->         REPEAT
    ->              SET res=res * p_num;
    ->              SET p_num=p_sum - 1;
    ->         UNTIL p_num < 2 END REPEAT;
    -> END
    -> //
mysql> DELIMITER ;
 
mysql> CALL sp_sum2(5,@res);
调用成功
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @res;
+------+
| @res |
+------+
| 120  |
+------+
1 row in set (0.00 sec)

loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin
     ->     declare v int;  
     ->     set v=0;  
     ->     LOOP_LABLE:loop  
     ->         insert into t values(v);  
     ->         set v=v+1;  
     ->         if v >=5 then
     ->             leave LOOP_LABLE;  
     ->         end if;  
     ->     end loop;  
     -> end;  
     -> //  
mysql > DELIMITER;

MySQL存储过程的基本函数

字符串类
CHARSET(str )                                      // 返回字串字符集 
CONCAT (string2 [,... ] )                          // 连接字串 
INSTR (string ,substring )                         // 返回substring首次在string中出现的位置,不存在返回0 
LCASE (string2 )                                   // 转换成小写 
LEFT (string2 ,length )                            // 从string2中的左边起取length个字符 
LENGTH (string )                                   // string长度 
LOAD_FILE (file_name )                             // 从文件读取内容 
LOCATE (substring , string [,start_position ] )    // 同INSTR,但可指定开始位置 
LPAD (string2 ,length ,pad )                       // 重复用pad加在string开头,直到字串长度为length 
LTRIM (string2 )                                   // 去除前端空格 
REPEAT (string2 ,count )                           // 重复count次 
REPLACE (str ,search_str ,replace_str )            // 在str中用replace_str替换search_str 
RPAD (string2 ,length ,pad)                        // 在str后用pad补充,直到长度为length 
RTRIM (string2 )                                   // 去除后端空格 
STRCMP (string1 ,string2 )                         // 逐字符比较两字串大小, 
SUBSTRING (str , position [,length ])              // 从str的position开始,取length个字符[1]
注[1]：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

数学类
ABS (number2 )                     // 绝对值 
BIN (decimal_number )              // 十进制转二进制 
CEILING (number2 )                 // 向上取整 
CONV(number2,from_base,to_base)    // 进制转换 
FLOOR (number2 )                   // 向下取整 
FORMAT (number,decimal_places )    // 保留小数位数 
HEX (DecimalNumber )               // 转十六进制[2] 
LEAST (number , number2 [,..])     // 求最小值 
MOD (numerator ,denominator )      // 求余 
POWER (number ,power )             // 求指数 
RAND([seed])                       // 随机数 
ROUND (number [,decimals ])        // 四舍五入,decimals为小数位数[3]
注[2]：HEX()中可传入字符串，则返回其ASC-11码，如HEX(‘DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
注[3]：返回类型并非均为整数

日期时间类
ADDTIME (date2 ,time_interval )                // 将time_interval加到date2 
CONVERT_TZ (datetime2 ,fromTZ ,toTZ )          // 转换时区 
CURRENT_DATE ( )                               // 当前日期 
CURRENT_TIME ( )                               // 当前时间 
CURRENT_TIMESTAMP ( )                          // 当前时间戳 
DATE (datetime )                               // 返回datetime的日期部分 
DATE_ADD (date2 , INTERVAL d_value d_type )    // 在date2中加上日期或时间 
DATE_FORMAT (datetime ,FormatCodes )           // 使用formatcodes格式显示datetime 
DATE_SUB (date2 , INTERVAL d_value d_type )    // 在date2上减去一个时间 
DATEDIFF (date1 ,date2 )                       // 两个日期差 
DAY (date )                                    // 返回日期的天 
DAYNAME (date )                                // 英文星期 
DAYOFWEEK (date )                              // 星期(1-7) ,1为星期天 
DAYOFYEAR (date )                              // 一年中的第几天 
EXTRACT (interval_name FROM date )             // 从date中提取日期的指定部分 
MAKEDATE (year ,day )                          // 给出年及年中的第几天,生成日期串 
MAKETIME (hour ,minute ,second )               // 生成时间串 
MONTHNAME (date )                              // 英文月份名 
NOW ( )                                        // 当前时间 
SEC_TO_TIME (seconds )                         // 秒数转成时间 
STR_TO_DATE (string ,format )                  // 字串转成时间,以format格式显示 
TIMEDIFF (datetime1 ,datetime2 )               // 两个时间差 
TIME_TO_SEC (time )                            // 时间转秒数 
WEEK (date_time [,start_of_week ])             // 第几周 
YEAR (datetime )                               // 年份 
DAYOFMONTH(datetime)                           // 月的第几天 
HOUR(datetime)                                 // 小时 
LAST_DAY(date)                                 // date的月的最后日期 
MICROSECOND(datetime)                          // 微秒 
MONTH(datetime)                                // 月 
MINUTE(datetime)                               // 分返回符号,正负或0 
SQRT(number2)                                  // 开平方

Comments are currently closed.

近期文章

近期评论

    文章归档

    分类目录

    标签

    ajax apache baiduMap centos css date eclipse el findbugs fun git hibernate html iframe java java web jdk jquery jre js json jsp jstl linux map maven mssql mvc mysql nginx oracle Quartz replace rpc spring sql ssh Struts Struts2 svn tomcat uur 教育 正则 网络

    Powered by WordPress Designed by - Bazahoteli.pl - 蜀ICP备2021011521号-1