MySQL vs PostgreSQL 存储过程：输入 + 输出参数 + 返回值（实战 AU9999 黄金金价分析）

IT咸鱼

发布于 2025-06-25 10:36:00

27600

代码可运行

运行总次数：0

代码可运行

一、业务背景与需求

多数金融系统需在周末自动回顾上周五黄金金价，输出是否符合预设阈值（如高于/低于某值），并返回分析结果。

我们要实现的存储过程功能：

输入：日期（例如 '2025-06-20'）和判断阈值（如 775）。
查询当天 AU9999 金价。
判断金价是否高于阈值。
输出分析信息（文字）并返回一个状态码（1：高于，0：不高于）。

下面用 MySQL 和 PostgreSQL 实现，双修模式上线。

二、MySQL 实现（含输入/输出参数与返回值）

1. 表结构准备

CREATE DATABASE IF NOT EXISTS test_sp;
USE test_sp;

CREATE TABLE IF NOT EXISTS gold_price (
  price_date DATE PRIMARY KEY,
  au9999_price DECIMAL(,)
);

-- 插入真实数据（以 6 月 20 日为例）
INSERT INTO gold_price (price_date, au9999_price) 
VALUES ('2025-06-20', 780.00)
ON DUPLICATE KEY UPDATE au9999_price = VALUES(au9999_price);

2. 创建存储过程

DELIMITER $$

CREATE PROCEDURE analyze_gold_price(
  IN in_date DATE,
  IN threshold DECIMAL(,),
  OUT out_message VARCHAR(),
  OUT out_status INT
)
BEGIN
  DECLARE v_price DECIMAL(,);

  -- 查询指定日期金价
  SELECT au9999_price INTO v_price
    FROM gold_price
    WHERE price_date = in_date;

  -- 判断并设置输出
  IF v_price IS NULL THEN
    SET out_message = CONCAT('无此日期（', in_date, '）金价数据');
    SET out_status = -1;
  ELSEIF v_price > threshold THEN
    SET out_message = CONCAT('日期', in_date, '金价', v_price, '元/克，高于阈值', threshold);
    SET out_status = ;
  ELSE
    SET out_message = CONCAT('日期', in_date, '金价', v_price, '元/克，不高于阈值', threshold);
    SET out_status = ;
  END IF;
END $$

DELIMITER ;

3. 测试调用（复制即可执行）

-- 定义变量接收输出
CALL analyze_gold_price('2025-06-20', , @msg, @status);

-- 查看结果
SELECT @msg AS message, @status AS status;

预期输出：

message                                    | status
-------------------------------------------|-------
日期2025-06-20金价780.00元/克，高于阈值775 | 1

三、PostgreSQL 实现

1. 表结构准备

CREATE TABLE IF NOT EXISTS gold_price (
  price_date DATE PRIMARY KEY,
  au9999_price NUMERIC(,)
);

INSERT INTO gold_price (price_date, au9999_price)
VALUES ('2025-06-20', 780.00)
ON CONFLICT (price_date) DO UPDATE SET au9999_price = EXCLUDED.au9999_price;

2. 创建过程

CREATE OR REPLACE FUNCTION analyze_gold_price_fn(
  in_date DATE,
  threshold NUMERIC
)
RETURNS TABLE(out_message TEXT, out_status INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    CASE
      WHEN p.au9999_price IS NULL THEN
'无此日期（' || in_date || '）金价数据'
WHEN p.au9999_price > threshold THEN
'日期' || in_date || '金价' || p.au9999_price || '元/克，高于阈值' || threshold
ELSE
'日期' || in_date || '金价' || p.au9999_price || '元/克，不高于阈值' || threshold
END,
CASE
      WHEN p.au9999_price IS NULL THEN -1
WHEN p.au9999_price > threshold THEN 1
ELSE 0
END
  FROM gold_price p
WHERE p.price_date = in_date;
END;
$$;

3. 调用示例

select * from  analyze_gold_price('2025-06-20', );

在 psql或 PgAdmin 中，会直接显示 out_message和 out_status，与 MySQL 输出一致。

五、工作融合示例

实际工作场景，可每逢周末自动运行该过程分析上周五（金价可能为空）。可以用调度工具（如 cron 或 aizuan）调用，结果写入日志或业务表。

例如，加入记录分析结果表（MySQL）：

CREATE TABLE IF NOT EXISTS gold_analysis (
  analysis_date DATETIME PRIMARY KEY,
  price_date DATE,
  status INT,
  message VARCHAR()
);

-- 调用后插入日志
CALL analyze_gold_price('2025-06-20', , @msg, @status);
INSERT INTO gold_analysis VALUES (NOW(), '2025-06-20', @status, @msg);

同样逻辑也可迁移到 PostgreSQL，插入表 gold_analysis_pg即可。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-06-24，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql