[SQL] Stored Routines

Stored Routines (Stored procedures & Stored functions)
自定義一連串的sql語句與function.

Stored procedures

新增

mysql> delimiter $$
mysql> create procedure procedure_table ()
    -> begin
    -> select* from testtable2 where name = 'lewis';
    -> select* from testtable where car >110;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> ;
ERROR:
No query specified

使用

mysql> call procedure_table();
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
+----+-------+------+-------+-----+-------+
2 rows in set (0.01 sec)

刪除

mysql> drop procedure if exists procedure_table;
Query OK, 0 rows affected (0.00 sec)

mysql> call procedure_table();
ERROR 1305 (42000): PROCEDURE testdb.procedure_table does not exist

Stored functions

有參數

mysql> delimiter $$
mysql> create function sumarg(n1 int, n2 int)  
returns int                 
begin return n1 + n2 ; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> select sumarg(1,3);
+-------------+
| sumarg(1,3) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

無參數

mysql> delimiter $$
mysql> create function datetest() returns varchar(24) 
begin                
declare d,t,w varchar(24); 
set d = date_format(curdate(),'%y/%m/%d'); 
set t = time_format(curtime(),'%y/%m/%d'); 
set w = dayname(curdate()); 
return concat(d,' ', t ,' ',w); end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> ;
ERROR:
No query specified

mysql> select datetest();
+--------------------------+
| datetest()               |
+--------------------------+
| 15/02/14 00/00/00 Saturd |
+--------------------------+
1 row in set, 1 warning (0.00 sec)