Skip to content

存储 Procedures

编写 reusable 服务器-边 logic 使用存储 procedures and 函数.

你将学到

  • 什么存储 procedures 是 and 当到 use 它们
  • 创建 procedures 使用参数 and control flow
  • 存储函数对比存储 procedures
  • Benefits: 性能, security, and 封装
  • Drawbacks: portability and testability concerns

概念讲解

什么是存储过程?

A stored procedure is a named block of SQL (and sometimes procedural language extensions like PL/pgSQL, T-SQL, or PL/SQL) stored in the database. You call it by name, optionally passing parameters. The procedure runs on the database server, can contain loops, conditionals, and error handling, and can perform multiple operations in one call.

Creating a Procedure

Procedures are defined with CREATE PROCEDURE (or CREATE FUNCTION for a function that returns a value). They accept input parameters, can return result sets or output parameters, and use procedural constructs like IF, WHILE, LOOP, and EXCEPTION blocks. The exact syntax varies by database — PostgreSQL uses PL/pgSQL, MySQL uses its own procedural SQL, SQL Server uses T-SQL.

Functions vs Procedures

A stored function returns a single value and can be used in a SELECT statement (SELECT calculate_tax(price) FROM products). A stored procedure does not return a value inline — it can return result sets via OUT parameters or a SELECT inside the procedure, and is called with CALL. Functions are more restrictive (no transaction control); procedures are more powerful.

Benefits

  • Performance — the procedure is parsed and planned once, then reused. It avoids sending multiple round-trips from the application.
  • Security — you can grant execute access to a procedure without granting access to the underlying tables. The procedure acts as a controlled API.
  • Encapsulation — complex business logic lives in one place, callable from any application.

Drawbacks

  • Portability — procedural SQL is database-specific. Moving from PostgreSQL to MySQL means rewriting procedures.
  • Testability — procedures are harder to unit test than application code. Debugging requires database-specific tools.
  • Scalability — heavy logic in the database can become a bottleneck. Modern practice often prefers keeping logic in the application layer.

示例代码

              -- PostgreSQL: a stored function to calculate discounted price
CREATE OR REPLACE FUNCTION discounted_price(
  original_price DECIMAL(10, 2),
  discount_percent DECIMAL(5, 2)
) RETURNS DECIMAL(10, 2) AS $$
BEGIN
  RETURN original_price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- Use the function in a query
SELECT name, price, discounted_price(price, 10) AS price_with_10pct_off
FROM products WHERE in_stock = TRUE;

-- PostgreSQL: a stored procedure to place an order
CREATE OR REPLACE PROCEDURE place_order(
  p_user_id  INTEGER,
  p_product_id INTEGER,
  p_quantity INTEGER
) AS $$
DECLARE
  v_price DECIMAL(10, 2);
  v_total DECIMAL(10, 2);
BEGIN
  SELECT price INTO v_price FROM products WHERE id = p_product_id;
  v_total := v_price * p_quantity;

  INSERT INTO orders (user_id, total) VALUES (p_user_id, v_total);
  INSERT INTO order_items (order_id, product_id, quantity)
    VALUES (currval('orders_id_seq'), p_product_id, p_quantity);
END;
$$ LANGUAGE plpgsql;

-- Call the procedure
CALL place_order(1, 42, 3);
            

函数那个 computes discounted price (usable in SELECT), and procedure 那个位置顺序通过 inserting 到 two 表 atomically.

动手试一试

  • SQL Formatter

    格式存储 procedure and 函数定义.

  • Code Diff

    Compare procedure versions 当更新 logic.

常见错误

错误写法

Putting 所有 business logic in 存储 procedures

正确做法

Procedures 是困难到 测试, version, and port. Keep 它们 for 数据-intensive 操作那个 benefit 从运行关闭到 数据. Put 大多数 logic in application layer.

错误写法

不 handling 错误内部 procedures

正确做法

Use EXCEPTION 块 (PL/pgSQL) or DECLARE HANDLER (MySQL) 到 catch and handle 错误. unhandled 异常 aborts procedure and propagates 到 caller.

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误