时间戳

首页 > 解决方案 > 从 SQL 服务器到 PostgreSQL 的 T-SQL 存储过程

sql - 从 SQL 服务器到 PostgreSQL 的 T-SQL 存储过程

问题描述

对于一些上下文：我编写了一个使用两个表的存储过程， products（product_id， product_name，description，price，category_id）和supplies（ shipping_id ，供应商_id，product_id（FK到）， supply_dateproducts，数量）。

该过程product_id在给定的时间范围内（从 date1 到 date2）打印并打印有关产品供应的描述和某些信息。

这是 T-SQL 代码：

CREATE OR ALTER PROCEDURE productInfo
    (@product_id INT,
     @date1 DATE,
     @date2 DATE)
AS
BEGIN
    DECLARE @description VARCHAR(100),
            @shipment_id INT, 
            @supply_date DATE, 
            @quantity INT;

    DECLARE product_cursor CURSOR LOCAL FOR
        SELECT description 
        FROM products 
        WHERE product_id = @product_id

    OPEN product_cursor;

    FETCH NEXT FROM product_cursor INTO @description

    CLOSE product_cursor
    
    DECLARE product_supply_cursor CURSOR LOCAL FOR 
        SELECT shipment_id, supply_date, quantity
        FROM supplies
        WHERE product_id = @product_id

    OPEN product_supply_cursor;

    FETCH NEXT FROM product_supply_cursor INTO @shipment_id, @supply_date, @quantity;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @supply_date >= @date1 AND @supply_date <= @date2
        BEGIN
            PRINT 'DESCRIPTION: ' + @description
            PRINT 'SHIPMENT_ID: ' + STR(@shipment_id)
            PRINT 'SUPPLY_DATE: ' + convert(varchar, @supply_date, 23)
            PRINT 'QUANTITY: ' + STR(@quantity)
            PRINT ' '
        END;

        FETCH NEXT FROM product_supply_cursor INTO @shipment_id, @supply_date, @quantity;
    END;

    CLOSE product_supply_cursor;
END;

显然，这个过程不能在 pgSQL 中运行，而且我没有这方面的经验，所以我想要这段代码的翻译，或者（如果我问得太多）关于需要改变什么的提示（语法或逻辑明智）以适应 pgSQL 语言。

标签： sqlpostgresqlstored-proceduresplpgsql

解决方案

SQL Server 代码一开始就非常复杂。游标的使用以及缓慢且低效（嵌套！）的逐行处理可以简化为连接两个表的单个 SELECT 语句。对游标循环内的两个日期进行范围检查非常有效。直接在 SELECT 语句的 WHERE 子句中应用它要好得多，这样您就不必迭代超过 1000 万行，只需显示 5 行。

一个天真的迁移会做这样的事情：

create procedure print_product_info(p_product_id int, p_date1 date, p_date2 date)
as
$$
declare
  l_row record;
begin
  for l_row in SELECT p.description, s.shipment_id, s.supply_date, s.quantity
               FROM supplies s
                 JOIN products p ON p.product_id = s.product_id
               WHERE p.product_id = p_product_id
                 AND s.supply_date >= p_date1
                 AND s.supply_date <= p_date2
  loop
    raise notice 'DESCRIPTION: %', l_row.description;
    raise notice 'SHIPMENT_ID: %', l_row.shipment_id;
    raise notice 'SUPPLY_DATE: %', to_char(l_row.supply_date, 'yyyy-mm-dd');
    raise notice 'QUANTITY: %', l_row.quantity;
  end loop;
end;
$$
language plpgsql;

但是，这不是在 Postgres 中这样做的方式。结果不应该被“打印”，而只是由函数“返回”（过程不打算返回东西）。

在 Postgres 中实现这一点的正确方法是设置返回函数：

create function get_product_info(p_product_id int, p_date1 date, p_date2 date)
  returns table(description text, shipment_id int, supply_date date, quantity int)
as
$$
  SELECT p.description, s.shipment_id, s.supply_date, s.quantity
  FROM supplies s
   JOIN products p ON p.product_id = s.product_id
  WHERE p.product_id = p_product_id
   AND s.supply_date >= p_date1
   AND s.supply_date <= p_date2
$$
language sql;

要显示数据，请使用：

select *
from get_product_info(42, date '2020-01-07', '2020-01-12');

推荐阅读