使用 SQL 模板

将 SQL 看作一种特定的脚本语言，提供了执行手工编写的 SQL 语句的能力，适合复杂查询场景。

• 支持命名参数绑定，通过 :paramName 的形式在 SQL 中指定占位符
• 参数通过 globalThis 对象传递，键名与占位符名对应
• 对于 SELECT 查询：
  • 如果有 LIMIT 1 则返回单个对象
  • 否则返回数组
• 对于 INSERT/UPDATE/DELETE 操作返回受影响的行数
• 支持与前置、后置 JavaScript/Python 脚本混合使用

注意: 不支持 Jinja 模板引擎语法

绑定查询参数

在 SQL 模板中，可以绑定命名参数，例如:

• API 请求参数: :globalThis.input
• 服务端保存的会话信息: :globalThis.session
• 环境变量 :globalThis.env

使用 . 点号访问对象字段，例如 API 请求的 json 格式是

{
  "params": {
    "user": {
      "name": "luwak"
    }
  }
}

使用 :globalThis.input.user.name 绑定的值是 luwak。

SQL 模板最终将转换成包含占位符的预处理语句。

示例

场景描述

查询金卡客户的总销售额

请求

{
  "jsonrpc": "2.0",
  "method": "smartPanda.oms.order.totalGoldMemberSales",
  "params": {
    "startDate": "2025-01-01",
    "endDate": "2025-03-31",
    "status": "CONFIRMED",
    "tierId": "GOLD"
  },
  "metas": {
    "token": "your_token",
    "signature": "your_signature",
    "appKey": "your_app_key",
    "timestamp": 1744342566709
  },
  "id": "b0c6a28c-3f0c-4cf2-98bc-676ba83ecdf3"
}

SQL 模板

SELECT
  COALESCE(SUM(o.amount_paid), 0) AS total_sales
FROM
  `order` o
WHERE
  o.ordered_at BETWEEN :globalThis.input.startDate
  AND :globalThis.input.endDate
  AND o.status = :globalThis.input.status
  AND o.ordered_by IN (
    SELECT
      id
    FROM
      customer
    WHERE
      tier_id = :globalThis.input.tierId
  )
LIMIT 1

实际执行的语句

Prepare   SELECT COALESCE(SUM(o.amount_paid), 0) AS total_sales FROM `order` o WHERE o.ordered_at BETWEEN ? AND ? AND o.status = ? AND o.ordered_by IN ( SELECT id FROM customer WHERE tier_id = ? ) LIMIT 1
Execute   SELECT COALESCE(SUM(o.amount_paid), 0) AS total_sales FROM `order` o WHERE o.ordered_at BETWEEN '2025-01-01' AND '2025-03-31' AND o.status = 'CONFIRMED' AND o.ordered_by IN ( SELECT id FROM customer WHERE tier_id = 'GOLD' ) LIMIT 1

响应结果

{
  "jsonrpc": "2.0",
  "result": {
    "totalSales": "0.00"
  },
  "id": "c1f15ca5-0d0e-445b-942c-b0cb03d2ef35"
}

示例2

场景描述2

获取指定品牌id的一组数据

请求2

{
  "jsonrpc": "2.0",
  "method": "smartPanda.mdh.brand.sqlGetMany",
  "params": [
    1,
    2,
    3
  ],
  "metas": {
    "token": "44ea16b7-3aa1-41a5-81da-8ee9d2b840c2",
    "signature": "c8f1500af5e6366369f74d7ee989750d",
    "appKey": "NSEA4ETIpBlyB2l9",
    "timestamp": 1744618785228
  },
  "id": "176d5611-53fa-4d38-89ed-e05a636c5c28"
}

SQL 模板2

SELECT * FROM brand WHERE id IN ( :globalThis.input )

实际执行的语句2

Prepare   SELECT * FROM brand WHERE id IN ( ?, ?, ? )
Execute   SELECT * FROM brand WHERE id IN ( 1, 2, 3 )

响应结果2

{
  "jsonrpc": "2.0",
  "result": [
    {
      "id": 1,
      "name": "Brand Name"
    },
    {
      "id": 2,
      "name": "Louis Vuitton"
    },
    {
      "id": 3,
      "name": "Channel"
    }
  ],
  "id": "176d5611-53fa-4d38-89ed-e05a636c5c28"
}