存储过程(一)

在如下数据库中做操作

mysql> SHOW CREATE TABLE student;
+---------+----------------------------------------------------------------------+
| Table   | Create Table                                                         |
+---------+----------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `sid` char(11) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `cid` char(4) NOT NULL,
  `gender` enum('男','女') DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `cs1` (`cid`),
  CONSTRAINT `cs1` FOREIGN KEY (`cid`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT * FROM student;
+-----+-------+-----+--------+------+
| sid | sname | cid | gender | age  |
+-----+-------+-----+--------+------+
| 102 | 元元  | 171 | 女     |   18 |
| 109 | 辰辰  | 171 | 女     |   73 |
| 117 | 贱贱  | 171 | 男     |   17 |
| 128 | 萍萍  | 171 | 女     |   28 |
| 134 | 淘淘  | 171 | 男     |   34 |
| 201 | 达达  | 172 | 男     |    1 |
| 202 | 其其  | 172 | 男     |    2 |
| 217 | 权权  | 172 | 男     |   17 |
+-----+-------+-----+--------+------+
8 rows in set (0.04 sec)
mysql> show create table class;
+-------+--------------------------------------------------+
| Table | Create Table                                     |
+-------+--------------------------------------------------+
| class | CREATE TABLE `class` (
  `cid` char(4) NOT NULL,
  `cname` varchar(20) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------+
1 row in set (0.04 sec)
mysql> select * from class;
+-----+---------+
| cid | cname   |
+-----+---------+
| 171 | 软件171 |
| 172 | 软件172 |
| 181 | 软件181 |
| 182 | 软件182 |
+-----+---------+
4 rows in set (0.04 sec)

存储过程的创建

创建最基础的存储过程

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc ()
    -> BEGIN
    -> SELECT * FROM student;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL proc();

在此基础上,使用聚合函数

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc ()
    -> BEGIN
    -> SELECT COUNT(*) FROM student;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL proc();

在聚合函数的基础上,使用参数

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc (IN inparam CHAR(4) ,OUT outparam INT)
    -> BEGIN
    -> SELECT COUNT(*) FROM student WHERE cid = inparam INTO outparam;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL proc('171',@a);
mysql> SELECT @a;
+------+
| @a   |
+------+
|    5 |
+------+
1 row in set (0.04 sec)

在参数的基础上,在存储过程内部,使用变量

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc (IN inparam1_cid CHAR(4) ,IN inparam2_cid CHAR(4),OUT outparam_num INT)
    -> BEGIN
    -> DECLARE num1 , num2 INT;
    -> SELECT COUNT(*) FROM student WHERE cid = inparam1_cid INTO num1;
    -> SELECT COUNT(*) FROM student WHERE cid = inparam2_cid INTO num2;
    -> SET outparam_num = num1 - num2;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL proc('171','172',@a);
mysql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.04 sec)

在存储过程中,可以修改一定的字符集编码类型
已知能够修改的字符集编码类型为character_set_results

mysql>  show variables like "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)

mysql>  DELIMITER //
mysql>  CREATE PROCEDURE proc ()
    ->  BEGIN
    ->  set character_set_results="gbk";
    ->  END //
Query OK, 0 rows affected (0.04 sec)

mysql>  DELIMITER ;
mysql> CALL proc ();
Query OK, 0 rows affected (0.04 sec)

mysql>  show variables like "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.05 sec)

存储过程的使用

我们已经在前面的案例中使用过若干次CALL proc;

存储过程的查看

1、

SHOW PROCEDURE STATUS LIKE 'proc%';

这个语句是一个MySQL的扩展。它返回子程序的特征,如数据库、名字、类型、创建者及创建、修改日期。如果没有指定样式,根据使用的语句,所有存储程序或存储函数的信息都被列出。
2、

SHOW CREATE PROCEDURE cxc.proc;

这个语句也是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。
3、

SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME = 'proc' AND ROUTINE_TYPE= 'PROCEDURE';

在information_schema数据库下的Routines表中,存储所有存储过程的定义。使用SELECT语句查询Routine表中的存储过程的定义时,一定要使用ROUTINE_NAME字段指定存储过程的名称,否则将查询出所有存储过程的定义。如国有存储过程和函数名称形同,则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储过程。

存储过程的修改

在实际开发中,业务需求更改的情况时有发生,这样就不可避免地需要修改存储过程的特性,在MySQL中可以使用ALTER语句修改存储过程的特性,其基本语句格式如下:

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic...]

上述语法格式中,sp_name表示存储过程或函数的名称;characteristic表示要修改存储过程的哪个部分,characteristic的取值具体如下。

(1)CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;
(2)NO SQL表示子程序中不包含SQL语句;
(3)READS SQL DATA表示子程序中包含读数据的语句;
(4)MODIFIES SQL DATA表示子程序中包含写数据的语句;
(5)SQL SECURITY{DEFINER | INVOKER}指明谁有权限来执行;
(6)DEFINER表示只有定义者自己才能够执行;
(7)INVOKER表示调用者可以执行;
(8)COMMENT’string’表示注释信息。

例:修改存储过程proc的定义
将读写权限修改为MODIFIES SQL DATA,并指名调用者可以执行,代码如下:

ALTER PROCEDURE proc
MODIFIES SQL DATA
SQL SECURITY INVOKER;

存储过程的删除

当数据库中存在废弃的数据存储过程,需要删除。MySQL中可以使用DROP语句删除存储过程,其基本语法格式如下:

DROP {PROCEDURE | FUNCTION } [IF EXISTS] sp_name

上述语法格式中,sp_name为要移除的存储过程的名称,IF EXISTS表示如果程序不存在,它可以避免发生错误,产生一个警告。该警告可以使用SHOW WARNING进行查询。

drop procedure proc;

推荐阅读更多精彩内容