MySQL 实践之存储过程（上）

mysql

如果你创建了一个有数据库的程序，为了方便代码管理，你最好将SQL代码放到数据库中，即存储过程或函数中。

存储过程，就是在数据库中保存代码块的对象。然后在程序中，通过调用这些过程来获得或存入数据。

创建

在 MySQL 中创建存储过程：

1
2
3
4
5
6
7


DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
  SELECT * FROM clients;
  -- 函数体
END $$
DELIMITER ;

在 SQL 代码中调用存储过程：

1

CALL get_clients()

获取发票应收款：

1
2
3
4
5
6
7
8
9


-- 返回应收款>0的发票数据
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
  SELECT *
  FROM invoices
  WHERE invoice - payment > 0;
END$$
DELIMITER ;

在存储过程中，每个语句都需要用分号结尾，我们修改默认的分隔符（delimiter）之后，就可以在过程中用分号结尾。

对于修改默认的分隔符，每个团队有不同的习惯，有的喜欢DELIMETER $$，有的喜欢DELIMETER //，以公司规范为准。

删除存储过程：

1

DROP PROCEDURE IF EXISTS get_clients

传参

在存储过程中添加参数：

1
2
3
4
5
6
7
8


-- 通过city获取用户
DELIMITER $$
CREATE PROCEDURE get_clients_by_city(country CHAR(2), city CHAR(2))
BEGIN
  SELECT * FROM clients c WHERE c.city = city and c.country = country;
  -- 给表名一个别名，区分表格中与传入的
END$$
DELIMITER ;

1

CALL PROCEDURE get_clients_by_city('US', 'LA')

char 字符串；varchar 可变长度的字符串，如姓名，电话等。

练习：通过客户 id 获取发票

1
2
3
4
5
6
7
8


CREATE PROCEDURE get_invoices_by_client
(
  client_id INT
)
BEGIN
  SELECT * FROM invoices i
  WHERE i.client_id = client_id;
END

默认值

1
2
3
4
5
6
7
8
9


CREATE PROCEDURE get_clients_by_city(country CHAR(2))
BEGIN
  IF country IS NULL THEN
    SET country = 'CH';
  END IF;

  SELECT * FROM clients c 
  WHERE c.country = country;
END

调用：

1
2


CALL PROCEDURE get_clients_by_city(NULL)
-- 如果括号里为空，会报错

如果不想传入指定值，而是进行其他操作。

1
2
3
4
5
6
7
8
9


CREATE PROCEDURE get_clients_by_city(country CHAR(2))
BEGIN
  IF country IS NULL THEN
    SELECT * FROM clients;
  ELSE
    SELECT * FROM clients c 
    WHERE c.country = country;
  END IF; 
END

练习：Write a procedure called get_payments with two parameters.

如果只传入0个参数，返回全部消费记录；如果传入第一个参数，返回指定客户的消费记录；传入两个参数，返回指定客户在指定平台的消费记录。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE PROCEDURE get_payments
(
  client_id INT,
  payment_method_id TINYINT
  -- 0-255
)
BEGIN
  SELECT * FROM payments p 
    WHERE p.client_id = IFNULL(client_id, p.client_id) AND
    p.payment_method = IFNULL(payment_method, p.payment_method);
END

1
2
3
4


CALL PROCEDURE get_payments(NULL, NULL)
-- CALL PROCEDURE get_payments(5, NULL)
-- CALL PROCEDURE get_payments(NULL, 2)
-- CALL PROCEDURE get_payments(5, 2)