存储过程（一）
2018-12-06 本文已影响154人
三斤耳朵
在如下数据库中做操作
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;
