MySQL之存储过程

浏览数：26 / 时间：2015年06月12日

引言

　　　　在Mysql中无论是在定义上还是在使用上，自定义函数和存储过程有很多相似的地方，所以我们来简单了解下什么是存储过程以及存储过程的实现方式。以及他们之间的区别。

定义

　　　存储过程的定义是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。它首先是一组能够完成特定功能的SQL集合，经过编译后存储在数据库中，是通过用户指定的存储过程的名字来调用的。

优势

　　存储过程在数据库中有很大的应用范围，可是它有什么优势呢

  • 增强SQL语句的功能和灵活性：当对数据库进行复杂操作，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
  • 实现较快的执行速度：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  • 减少了网络流量
  • 存储过程可以重复使用,可减少数据库开发人员的工作量
  • 安全性高,可设定只有某此用户才具有对指定存储过程的使用权

语法结构及说明

　　如果要定义一个存储过程，它需要满足下面的语法结构

　　

CREATE
[DEFINER={USER|CURRENT_USER}] //不指定则默认为当前用户
PROCEDURE sp_name[pro_parameter[,....]]
[characteristic..]routine_body

proc_parameter:
[IN|OUT|INOUT|] parameter_name type

　　关于存储过程的定义有几点需要说明：

　　1.在定义存储过程的时候我们需要制定定义者，如果不知道用户那么就默认是当前数据库的用户

　　2.定义存储过程的名字和定义自定义函数的名字一样，需要指定属于哪一个数据库的存储过程，默认是当前数据库的

　　3.存储过程的参数可以是零个也是是一个或者多个

　　4.特性（characteristic）和自定义函数基本一致，包括

　　　　

contrains sql：包含 sql 语句，但不包含读或写数据的语句
no sql：不包含 sql 语句
reads sql data：包含读数据的语句
modifies sql data：包含写数据的语句
sql security {default | invoker} 指明谁有权限来执行

　　5.参数的类型

　　　　

IN,表示该参数必须在调用存储过程的时候指定
OUT,表示该参数的值可以被存储过程改变，并且可以返回
INPUT，在调用时候指定能够被存储过程改变然后返回

　　6.存储过程的存储体

　　　　

存储过程的过程体（基本上和函数体的要求相同）：
A、由合法的 sql 语句构成
B、复合结构使用 begin ... end 语句
C、复合结构可以包含声明、循环、控制结构

　　7.调用

　　如果存储过程是有参数的必须使用sp_name(参数)的形式调用，如果没有参数的话可以使用sp_name(）的形式也可以采用sp_namde的形式直接调用。

实现说明

　　1.无参的存储过程实现及调用

　　（获取当前时间）

　　

mysql>  CREATE PROCEDURE getDate SELECT NOW();
ERROR 1046 (3D000): No database selected
mysql>  CREATE PROCEDURE TEST.getDate SELECT NOW();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘SELEC
T NOW()‘ at line 1
mysql>  CREATE PROCEDURE TEST.getDate() SELECT NOW();
Query OK, 0 rows affected (0.05 sec)

mysql> USE  TEST;
Database changed
mysql> CALL getDate;
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-24 21:18:01 |
+---------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> CALL getDate();
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-24 21:18:05 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

　　2.含有IN参数的存储过程的实现

　　（获取某一ID的匹配记录）

mysql> DELIMITER $$
mysql> SHOW  TABLES;
    -> $$
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
| t5             |
| t6             |
| t7             |
+----------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM T1$$
+----+------+----------+---------+-------+-------+
| id | name | password | test001 | test3 | test4 |
+----+------+----------+---------+-------+-------+
|  2 | 1    | 1        | 1       | 1     | 1     |
|  3 | 3    | 4        | 5       | 6     | 7     |
|  4 | 3    | 4        | 5       | 6     | 7     |
|  5 | 3    | 4        | 5       | 6     | 7     |
|  6 | 3    | 4        | 5       | 6     | 7     |
|  7 | 3    | 4        | 5       | 6     | 7     |
|  8 | 3    | 4        | 5       | 6     | 7     |
|  9 | 3    | 4        | 5       | 6     | 7     |
| 10 | 3    | 4        | 5       | 6     | 7     |
+----+------+----------+---------+-------+-------+
9 rows in set (0.00 sec)
mysql> CREATE PROCEDURE fetchById(IN id INT UNSIGNED)
    -> BEGIN
    -> SELECT * FROM T1 WHERE id = id;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL fetchById(5)$$
+----+------+----------+---------+-------+-------+
| id | name | password | test001 | test3 | test4 |
+----+------+----------+---------+-------+-------+
|  2 | 1    | 1        | 1       | 1     | 1     |
|  3 | 3    | 4        | 5       | 6     | 7     |
|  4 | 3    | 4        | 5       | 6     | 7     |
|  5 | 3    | 4        | 5       | 6     | 7     |
|  6 | 3    | 4        | 5       | 6     | 7     |
|  7 | 3    | 4        | 5       | 6     | 7     |
|  8 | 3    | 4        | 5       | 6     | 7     |
|  9 | 3    | 4        | 5       | 6     | 7     |
| 10 | 3    | 4        | 5       | 6     | 7     |
+----+------+----------+---------+-------+-------+
9 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

　　通过上面的例子我们发现我们成功的定义了存储过程fetchById(int)，可是我们却没有取到我们想要的结果我们拿到了所有的数据，那么是什么地方出问题了呢？？我们发现原来是定义的存出结果的参数名和我们数据库的列名相等了，而这会导致的结果就是参数的值将不会是你传入的值，而是变成每条记录的那个字段的值。所以我们需要注意的一定就是在定义存储结构的时候参数名称不能够和数据库列名相同。

　　知道了问题所在，我们只需要修改就行了。和自定义函数一样，存储过程是不能够修改名称和结构体的，因为我们只能删掉后重新构建了，下面的SQL语句就实现了这个功能。

mysql> CREATE PROCEDURE fetchById(IN t_id INT UNSIGNED)
    -> BEGIN
    -> SELECT * FROM T1 WHERE id = t_id;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL fetchById(5);
    -> $$
+----+------+----------+---------+-------+-------+
| id | name | password | test001 | test3 | test4 |
+----+------+----------+---------+-------+-------+
|  5 | 3    | 4        | 5       | 6     | 7     |
+----+------+----------+---------+-------+-------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;
mysql> CALL fetchById(5);
+----+------+----------+---------+-------+-------+
| id | name | password | test001 | test3 | test4 |
+----+------+----------+---------+-------+-------+
|  5 | 3    | 4        | 5       | 6     | 7     |
+----+------+----------+---------+-------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

　　3.创建带有IN和OUT类型参数的存储过程

　　（删除一条记录并且返回删除记录前后记录的总数目）

mysql> CREATE PROCEDURE delBeforeAndAfter(IN t_id INT UNSIGNED,OUT beforeSum INT
 UNSIGNED,OUT afterSum INT UNSIGNED)
    -> BEGIN
    -> SELECT COUNT(1) FROM T1 INTO beforeSum;
    -> DELETE FROM T1 WHERE id=t_id;
    -> SELECT COUNT(1) FROM T1 INTO afterSum;
    -> END
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> SELECT COUNT(1) FROM T1;
+----------+
| COUNT(1) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)



mysql> CALL delBeforeAndAfter(5,@beforeSum,@afterSum);
Query OK, 1 row affected (0.04 sec)




mysql> SELECT @beforeSum,@afterSum;
+------------+-----------+
| @beforeSum | @afterSum |
+------------+-----------+
|          9 |         8 |
+------------+-----------+
1 row in set (0.00 sec)


 




通过传入参数5，我们成功的返回删除前后删除后的记录总数。存储过程得到了实现。


 


与自定义函数的区别


　　1、存储过程实现的功能相对复杂，函数针对性较强：在实际的工作中，我们对表的操作经常是使用存储过程来实现的很少使用函数。


　　2、存储过程可以返回多个值，函数只能有一个返回值


　　3、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现


 这只是一些简单的区别，如果日后了解更多时再更新文章。


 


 


　　　　


 


　　


 


　　


          

            

              
              郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
            

          


          


          

            标签： 
            事务
            
            class
            
            style
            
            使用
            
            log
            
            问题
            
            数据
            
            si
            
            it
            
            事务
            
            class
            
            style
            
            使用
            
            log
            
            问题
            
            数据
            
            si
            
            it
            
            
            
          


          

            

              
相关文章

              
    
                
              

            

            

              
随机文章

              
            

          



          

            

              
您可能还喜欢

              
    
                
              

            


            

              
您可能还喜欢