今天又把mysql存儲過程學習了下,大家先看以下代碼:
對語法不懂的朋友,可以詳細看下語法結構.
代碼如下 復制代碼CREATE PROCEDURE and CREATE FUNCTION Syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
例
代碼如下 復制代碼
DELIMITER $$ /*改變語句的結束符*/
USE `test`$$ /*選擇數據庫*/
DROP PROCEDURE IF EXISTS `outgo`$$ /*存在outgo存儲過程就刪除*/
CREATE DEFINER=`root`@`%` PROCEDURE `outgo`(IN v_table CHAR(10), IN v_id INT(2), OUT v_value VARCHAR(32))
調用存儲過程的語法是call.如下:
mysql> call outgo('user', 2, @a);
+----+--------------+
| id | title |
+----+--------------+
| 2 | 你大爺的 |
+----+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call outgo('user', 1, @a);
+----+--------------------+
| id | title |
+----+--------------------+
| 1 | 我來測試一記 |
+----+--------------------+
1 row in set (0.00 sec)
其中調用中文的時候可能會出現Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (latin1_swedish_ci,NUMERIC), (utf8_general_ci,COERCIBLE) for operation 'concat'這是由於編碼不一致導致的。