作者:LAMP小白 点击:1987 发布日期:2012-11-11 12:26:00 返回列表
存储函数(stored function) 返回一个计算结果,该结果可以用在表达式里
存储过程(stored procedure)不直接返回一个结果,但可以同来完成一般的运算或是生成一个结果集并传递给客户
复合语句
mysql> delimiter $
mysql> CREATE PROCEDURE show_times()
-> BEGIN
-> SELECT 'Local time is:', CURRENT_TIMESTAMP;
-> SELECT 'UTC time is:', UTC_TIMESTAMP;
-> END$
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> CALL show_times();
+----------------+---------------------+
| Local time is: | CURRENT_TIMESTAMP |
+----------------+---------------------+
| Local time is: | 2012-11-11 10:07:59 |
+----------------+---------------------+
1 row in set (0.01 sec)
+--------------+---------------------+
| UTC time is: | UTC_TIMESTAMP |
+--------------+---------------------+
| UTC time is: | 2012-11-11 02:07:59 |
+--------------+---------------------+
1 row in set (0.01 sec)
存储函数和存储过程
存储函数使用CREATE FUNCTION来创建
存储过程使用CREATE PROCEDURE来创建
REATE FUNCTION 方法名 (参数1 类型，参数2 类型，...) RETURNS 返回数据类型
[[NOT] DETERMINISTIC]
[CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA]
[SQL SECURITY DEFINER/INVOKER ]
[COMMENT '函数说明']
BEGIN
函数体..;
RETURN 数据;
END
CONTAINS SQL 表示不包含读写数据的语句
NO SQL 表示不包含SQL语句
READS SQL DATA 表示包含读取的语句
MODILES SQL DATA 表示包含读写语句
mysql> CREATE FUNCTION insert_data_to_user_list(p_name CHAR) RETURNS INT READS SQL DATA BEGIN RETURN (SELECT COUNT(*) FROM user_list); END$
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT insert_data_to_user_list('tt');
+--------------------------------+
| insert_data_to_user_list('tt') |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set, 1 warning (0.01 sec)
存储函数和存储过程的权限
如果log_bin_srust_function_creators系统变量没有被激活,那么需要具备SUPER权限才能创建存储函数,而且创建的函数不能修改数据
mysql> SHOW VARIABLES LIKE 'log%';
+---------------------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------+
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /usr/local/mysql/var/localhost.localdomain.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+---------------------------------+------------------------------------------------+
10 rows in set (0.00 sec)
mysql> CREATE FUNCTION haha_a(p_name CHAR) RETURNS INT MODIFIES SQL DATA BEGIN INSERT INTO `user_list` SET `name`=p_name;RETURN 1; END$
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
存储过程中的参数类型
存储过程有3个参数 IN 调用者吧一个值传递给过程 OUT则是输出 INOUT为传递一个值 然后返回一个值
mysql> delimiter $
mysql> CREATE PROCEDURE count_mio_member
-> (OUT p_male INT, OUT p_female INT)
-> BEGIN
-> SELECT COUNT(*) FROM member WHERE sex='M' INTO p_male;
-> SELECT COUNT(*) FROM member WHERE sex='F' INTO p_famale;
-> END$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL count_mio_member(@man, @woman);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT 'man',@man;
+-----+------+
| man | @man |
+-----+------+
| man | 3 |
+-----+------+
1 row in set (0.00 sec)
mysql> SELECT 'woman' @woman
-> ;
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 '@woman' at line 1
mysql> SELECT 'woman',@woman;
+-------+--------+
| woman | @woman |
+-------+--------+
| woman | 2 |
+-------+--------+
1 row in set (0.00 sec)
上一篇:外键和引用完整性 下一篇:快递查询API