英文:
How to create a stored procedure in snowflake using insert into statement?
问题
我有一个类似于以下的插入语句：
这个查询将数据插入到表DB.PUBLIC.SK_HISTORY中，数据来自于从其他两个表创建的CTE。然后，在将其插入DB.PUBLIC.SK_HISTORY之前，对CTE进行了数据透视。
如何从这个查询创建一个存储过程？
英文:
I have an insert into statement that look like this:
This query inserts into the table DB.PUBLIC.SK_HISTORY from a CTE created from 2 other tables. Then the CTE is pivoted before inserting it into DB.PUBLIC.SK_HISTORY.
INSERT INTO DB.PUBLIC.SK_HISTORY
WITH
OPEN_PO AS
(
SELECT A.COL1, COL2, COL3, COL4, COL5, COL6, COL7, B.TIMING,
SUM(COL8) AS OPEN_DOLLARS,
COUNT(COL9) AS OPEN_LINES
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.COL1= B.COL1
GROUP BY 1,2,3,4,5,6,7,8
)
SELECT *
FROM
(
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, OPEN_LINES
FROM OPEN_PO
) T
PIVOT
(
SUM(OPEN_DOLLARS)
FOR TIMING IN ('T1', 'T2', 'T3', 'T4')
) P
How do I create a stored procedure from this query?
答案1
得分: 0
我找到了解决此问题的答案。
请注意，调用该存储过程时需要使用 EXCUTE AS CALLER，以防止权限问题。
CREATE OR REPLACE PROCEDURE DB_NAME.SCHEMA.PROCEDURE_NAME()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE message VARCHAR;
BEGIN
INSERT INTO DB.PUBLIC.SK_HISTORY
WITH
OPEN_PO AS
(
SELECT A.COL1, COL2, COL3, COL4, COL5, COL6, COL7, B.TIMING,
SUM(COL8) AS OPEN_DOLLARS,
COUNT(COL9) AS OPEN_LINES
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.COL1= B.COL1
GROUP BY 1,2,3,4,5,6,7,8
)
SELECT *
FROM
(
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, OPEN_LINES
FROM OPEN_PO
) T
PIVOT
(
SUM(OPEN_DOLLARS)
FOR TIMING IN ('T1', 'T2', 'T3', 'T4')
) P
;
message := '表格更新成功';
RETURN message;
END;
$$
英文:
I found the answer to this issue.
It's important to note that EXCUTE AS CALLER is required to prevent permission issues when calling the procedure.
CREATE OR REPLACE PROCEDURE DB_NAME.SCHEMA.PROCEDURE_NAME()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE message VARCHAR;
BEGIN
INSERT INTO DB.PUBLIC.SK_HISTORY
WITH
OPEN_PO AS
(
SELECT A.COL1, COL2, COL3, COL4, COL5, COL6, COL7, B.TIMING,
SUM(COL8) AS OPEN_DOLLARS,
COUNT(COL9) AS OPEN_LINES
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.COL1= B.COL1
GROUP BY 1,2,3,4,5,6,7,8
)
SELECT *
FROM
(
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, OPEN_LINES
FROM OPEN_PO
) T
PIVOT
(
SUM(OPEN_DOLLARS)
FOR TIMING IN ('T1', 'T2', 'T3', 'T4')
) P
;
message := 'table updated successfully';
RETURN message;
END;
$$
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论