_surname);
-> END IF;
->
-> RETURN(l_name_string);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> select myFunction3('Mrs','M','First','M','Last');
-> select myFunction3('Mrs','M','First','M','Last')$$
+-------------------------------------------+
| myFunction3('Mrs','M','First','M','Last') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set (0.01 sec)
+-------------------------------------------+
| myFunction3('Mrs','M','First','M','Last') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set (0.03 sec)
mysql> delimiter ;
mysql> select myFunction3(NULL,'M','First','M','Last');
+------------------------------------------+
| myFunction3(NULL,'M','First','M','Last') |
+------------------------------------------+
| Mr First M Last |
+------------------------------------------+
1 row in set (0.00 sec)
红色部分，因为没有delimiter ;，从而以分号结尾的调用没有成功，使用$$就可以.
创建完函数或过程，一定delimiter ;
当然可以指定别的分隔符:
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction4(rush_ship INT(10)) RETURNS DECIMAL(10,2)
-> BEGIN
-> DECLARE rush_shipping_cost DECIMAL(10,2);
-> CASE rush_ship
-> WHEN 1 THEN
-> SET rush_shipping_cost = 20.00;
-> WHEN 2 THEN
-> SET rush_shipping_cost = 15.00;
-> WHEN 3 THEN
-> SET rush_shipping_cost = 10.00;
-> ELSE
-> SET rush_shipping_cost = 0.00;
-> END CASE;
-> RETURN rush_shipping_cost;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select myFunction4(2);
+----------------+
| myFunction4(2) |
+----------------+
| 15.00 |
+----------------+
1 row in set (0.00 sec)
一个简单的sp:
mysql> DELIMITER //
mysql> CREATE PROCEDURE tom2.myProc (IN in_count INT)
-> BEGIN
-> DECLARE count INT default 0;
-> increment: LOOP
-> SET count = count + 1;
-> IF count < 20 THEN ITERATE increment;
-> END IF;
-> IF count > in_count THEN LEAVE increment;
-> END IF;
-> END LOOP increment;
-> SELECT count;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> call tom2.myProc(5);
-> call tom2.myProc(5)//
+-------+
| count |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)
查看sp(stored procedure)状态:
mysql> show procedure status like 'myProc' \G;
*************************** 1. row ***************************
Db: tom2
Name: myProc
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-10-12 23:18:28
Created: 2014-10-12 23:18:28
Security_type: DEFINER
Comment:
1 row in set (0.00 sec)
为sp添加characteristics特征值:
1.LANGUAGE SQL:说明该sp由sql编写;
2.CONTAINS SQL(包含sql)|NO SQL|READS SQL DATA|MODIFIES SQL DATA;
3.SQL SECURITY{DEFINER|INVOKER},指定sp的调用者权限;
4.COMMENT:sp的说明
实例:
mysql> ALTER PROCEDURE tom2.myProc SQL SECURITY INVOKER
-> COMMENT "Tom's procedure";
Query OK, 0 rows affected (0.00 sec)
mysql> show procedure status like 'myProc' \G;
*************************** 1. row ***************************
Db: tom2
Name: myProc
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-10-12 23:27:54
Created: 2014-10-12 23:18:28
Security_type: INVOKER
Comment: Tom's procedure
1 row in set (0.02 sec)
一个完整的demo：
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, sa
lary, City, Description)
-> values (1,'Jason', 'M