在系统开发中，SQL存储过程通常用于封装复杂的数据库操作逻辑，而外部程序往往需要通过标准化的接口调用这些存储过程，API集成就是实现这种通信的主流方案。这种方式既可以避免外部程序直接连接数据库带来的安全风险，也能让不同技术栈的系统之间实现顺畅交互。
API集成调用存储过程的核心流程
整体流程可以分为四个步骤，首先是外部程序发起API请求，携带调用存储过程所需的参数；然后API服务端接收请求，解析参数后连接数据库执行对应的存储过程；接着数据库返回存储过程的执行结果与输出参数；最后API服务端将结果封装成标准响应返回给外部程序。
存储过程准备示例
先创建一个简单的SQL存储过程，用于根据用户ID查询用户信息和订单数量，以MySQL为例：
-- 创建存储过程，输入用户ID，输出用户名称和订单总数
CREATE PROCEDURE get_user_order_info(
IN user_id INT,
OUT user_name VARCHAR(50),
OUT order_count INT
)
BEGIN
-- 查询用户名称
SELECT name INTO user_name FROM user WHERE id = user_id;
-- 统计用户订单数量
SELECT COUNT(*) INTO order_count FROM order_info WHERE user_id = user_id;
END
不同技术栈的API实现示例
Java Spring Boot实现
使用Spring Boot的JdbcTemplate调用存储过程，代码如下：
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.sql.CallableStatement;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
@RestController
public class UserOrderController {
@Autowired
private JdbcTemplate jdbcTemplate;
@GetMapping("/api/user/order")
public Map<String, Object> getUserOrder(@RequestParam Integer userId) {
return jdbcTemplate.execute(
con -> {
// 准备调用存储过程的语句
CallableStatement cs = con.prepareCall("{call get_user_order_info(?, ?, ?)}");
// 设置输入参数
cs.setInt(1, userId);
// 注册输出参数类型
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.INTEGER);
return cs;
},
(CallableStatementCallback<Map<String, Object>>) cs -> {
cs.execute();
Map<String, Object> result = new HashMap<>();
result.put("userName", cs.getString(2));
result.put("orderCount", cs.getInt(3));
return result;
}
);
}
}
Python Flask实现
使用Flask和pymysql库实现API调用存储过程，代码如下：
from flask import Flask, request, jsonify
import pymysql
app = Flask(__name__)
# 数据库连接配置
db_config = {
"host": "127.0.0.1",
"user": "root",
"password": "123456",
"database": "test_db",
"charset": "utf8mb4"
}
@app.route("/api/user/order", methods=["GET"])
def get_user_order():
user_id = request.args.get("userId", type=int)
if not user_id:
return jsonify({"error": "缺少用户ID参数"}), 400
# 连接数据库
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
try:
# 调用存储过程
cursor.callproc("get_user_order_info", (user_id, None, None))
# 获取输出参数，存储过程的输出参数在结果集中
cursor.execute("SELECT @_get_user_order_info_1, @_get_user_order_info_2")
result = cursor.fetchone()
return jsonify({
"userName": result[0],
"orderCount": result[1]
})
except Exception as e:
return jsonify({"error": str(e)}), 500
finally:
cursor.close()
conn.close()
if __name__ == "__main__":
app.run(port=5000)
注意事项
- 参数校验：API层需要对外部传入的参数做合法性校验，避免非法参数传入存储过程导致数据库异常。
- 异常处理：需要捕获存储过程执行过程中的数据库异常，返回友好的错误信息，不要暴露数据库内部细节。
- 权限控制：API服务端连接数据库的账号只需要有对应存储过程的执行权限，不需要多余的数据库操作权限。
- 超时设置：设置合理的API请求超时时间和数据库连接超时时间，避免存储过程执行时间过长导致资源占用过高。
结果返回处理
如果存储过程返回的是结果集，比如查询多个用户的信息，API需要将结果集转换成JSON数组返回。以Java为例，修改存储过程返回结果集：
-- 修改存储过程，返回用户列表结果集
CREATE PROCEDURE get_user_list()
BEGIN
SELECT id, name, age FROM user WHERE status = 1;
END
对应的Java调用代码：
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
public class UserController {
@Autowired
private JdbcTemplate jdbcTemplate;
@GetMapping("/api/user/list")
public List<Map<String, Object>> getUserList() {
// 调用返回结果集的存储过程
return jdbcTemplate.execute(
con -> con.prepareCall("{call get_user_list()}"),
(CallableStatementCallback<List<Map<String, Object>>>) cs -> {
cs.execute();
// 获取第一个结果集
return jdbcTemplate.queryForList(cs.getResultSet());
}
);
}
}