gpt4 book ai didi

mysql - 将 PostgreSQL 存储过程迁移到 MYSQL

转载 作者:行者123 更新时间:2023-11-29 13:10:12 24 4
gpt4 key购买 nike

我有以下 PostgreSQL,我想重写它以使其与 MySQL 存储函数兼容。

CREATE OR REPLACE FUNCTION test.yearly_demand_function(IN paramstartdate date, 
OUT network character varying,
OUT season_number integer,
OUT week_trans numeric,
OUT month_trans numeric,
OUT year_trans numeric )
RETURNS SETOF record AS
$BODY$

BEGIN
RETURN QUERY
(select qq.network::varchar,
qq.season_number,
qq.week_trans::numeric,
qq.month_trans::numeric,
qq.year_trans::numeric
from
(
SELECT coalesce(nullif(mpf.studio,''),fi.name) AS network,
coalesce(mpf.season_number, mpf.reported_season_number) AS season_number ,
sum(case when activity_date >= date_trunc('week', paramStartDate::timestamp) - interval '7 day' and activity_date <= paramStartDate then mpf.units_sold else 0 END) as week_trans,
sum(case when activity_date >= date_trunc('month', paramStartDate::timestamp) and activity_date <= paramStartDate then mpf.units_sold else 0 END) as month_trans,
sum(case when activity_date >= date_trunc('year', paramStartDate::timestamp) and activity_date <= paramStartDate then mpf.units_sold else 0 END) as year_trans
FROM customer.dim_product_view mpf
left join customer.feed_indicator fi on mpf.series_name = fi.indicator_value
and mpf.series_name = fi.indicator_value
left join
(
select p.series_name,p.season_number,count(*) as episode_count
from product p
where source in ('Amway','FifthThird')
and p.episode_number is not null
group by 1,2) as pec on mpf.series_name = pec.series_name
and mpf.season_number = pec.season_number
WHERE mpf.activity_date BETWEEN date_trunc('year', paramStartDate::timestamp) AND paramStartDate
AND 1=1
AND ( mpf.demographic is null or '' not in ( '' ) or ('' in ( '' ) and mpf.demographic = 'Persons') )
AND mpf.customer_product_id not ilike '%unallocated%'
GROUP BY 1,2,3,7,34,35,36
)qq
);

end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION yearly_demand_function(date);

现在我不知道如何从上面的 PostgreSQL(RETURN QUERY 部分)为 MYSQL 编写 RETURNS SETOF 记录 AS

虽然我已经开始为 MY SQL 编写存储过程,如下

-- DROP FUNCTION IF EXISTS looptest;
DELIMITER $$

CREATE FUNCTION test.yearly_demand_function( IN paramstartdate date,
OUT network varchar(256),
OUT season_number integer,
OUT week_value numeric,
OUT month_value numeric,
OUT year_value numeric
) RETURNS <What to Write >
LANGUAGE SQL
BEGIN

RETURN QUERY
(select qq.network::varchar,
qq.season_number,
qq.week_value::numeric,
qq.month_value::numeric,
qq.year_value::numeric
from
(
SELECT coalesce(nullif(mm.studio,''),fi.name) AS network,
coalesce(mm.season_number, mm.reported_season_number) AS season_number ,
sum(case when final_date >= date_trunc('week', paramStartDate::timestamp) - interval '7 day' and final_date <= paramStartDate then mm.units_sold else 0 END) as week_value,
sum(case when final_date >= date_trunc('month', paramStartDate::timestamp) and final_date <= paramStartDate then mm.units_sold else 0 END) as month_value,
sum(case when final_date >= date_trunc('year', paramStartDate::timestamp) and final_date <= paramStartDate then mm.units_sold else 0 END) as year_value
FROM customer.product_view mm
left join customer.f_indicator fi on mm.series_name = fi.indicator_value
and mm.series_name = fi.indicator_value
left join
(
select p.name,p.s_number,count(*) as episode_count
from product p
where source in ('Amway','FifthThird')
and p.e_number is not null
group by 1,2) as tt on mm.series_name = tt.series_name
and mm.season_number = tt.season_number
WHERE mm.final_date BETWEEN date_trunc('year', paramStartDate::timestamp) AND paramStartDate
AND 1=1
AND ( mm.demographic is null or '' not in ( '' ) or ('' in ( '' ) and mm.demographic = 'Persons') )
AND mm.customer_product_id not ilike '%unallocated%'
GROUP BY 1,2,3,7,34,35,36
)qq
);


END;
$$
DELIMITER

如何从上面的 PostgreSQL(RETURN QUERY 部分)为 MYSQL 编写 RETURNS SETOF 记录 AS

最佳答案

使用CREATE PROCEDURE语法代替CREATE FUNCTION。然后,您可以在该 block 内编写普通的 SELECT 语句。要执行您创建的存储过程,请使用 CALL 语法(即 CALL test.yearly_demand_function('2013-01-01'))。您也不需要指定 5 个OUT 参数。 5 个 OUT 参数的值将仅对应于您将在 SELECT 语句中指定的 5 列。

关于mysql - 将 PostgreSQL 存储过程迁移到 MYSQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22197770/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com