插入表 Teradata 动态存储过程 SQL [英] Insert Into table Teradata dynamic stored procedure SQL
问题描述
我正在尝试在 Teradata 中创建一个可以接受各种参数的存储过程.我的查询有 4 次 SQL,它创建了 3 个易失性表.在 Select 语句中是我需要动态的 SQL,这就是我遇到问题的地方.
I am trying to create a Stored Procedure in Teradata that will accept various arguments. My query has 4 passes of SQL where it creates 3 lots of volatile tables. Within the Select statements is the SQL that I need to be dynamic and this is where I run into problems.
这是我的 SQL:
CREATE PROCEDURE "mydb"."test_sp20" (DepID integer) --DepID is my parameter
DYNAMIC RESULT SETS 1 SQL SECURITY OWNER
BEGIN
DECLARE q1 VARCHAR(10000);
DECLARE cur1 CURSOR WITH RETURN ONLY TO client FOR s1;
CREATE VOLATILE TABLE mydb.tbl_1 , no fallback, no log(
Consumer_Unit_Id INTEGER,
Price_Promotion_Id INTEGER,
Promotion_Id INTEGER)
primary index (Consumer_Unit_Id, Price_Promotion_Id, Promotion_Id) on commit preserve rows ;
INSERT INTO mydb.tbl_1
SELECT * FROM mydb.tbl_1
SET q1 = 'Select * from mydb.tbl_1'
PREPARE s1 FROM q1;
OPEN cur1;
END;
这作为带有静态 SQL 的 SP 工作正常,但我需要 Select 语句是动态的,因为在其中我有我希望能够传递的 Department 和 Sections 参数.例如
This works fine as a SP with static SQL but I need the Select statements to be dynamic in that within them I have Department and Sections parameters that I want to be able to pass accross. e.g.
INSERT INTO mydb.tbl_1
SQL = 'Select * from mydb.tbl_1 where Department_ID = ' || DepID ||'
我也试过:
SQL = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ' || DepID ||'
在这两种情况下,我都会收到以下错误:
In both cases I get the following error:
7683: TEST_SP20:Invalid statement specified inside a dynamic declare cursor/SQL statement
我似乎可以有一个动态 SQL 字符串,但我不能有一个 INSERT INTO SELECT 字符串??
I seem to be able to have a dynamic SQL string but I can't have a INSERT INTO SELECT string??
@dnoeth 帮助我开始了这项工作,因此非常感谢您提供更多帮助.
@dnoeth helped me get this started so any more help is much appreciated.
推荐答案
你没有告诉你要INSERT/SELECT
使用动态 SQL,只有当你想返回时才需要一个游标来自您的 SP 的结果集.
You didn't tell that you want to INSERT/SELECT
using Dynamic SQL, a cursor is only needed when you want to return a result set from your SP.
DECLARE q1 VARCHAR(10000);
SET q1 = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ' || TRIM(DepID) ||';';
EXECUTE IMMEDIATE q1;
但如果只有传递给 WHERE 条件的值应该是动态的,您最好应用一个准备好的语句(可以多次使用):
But if only the values passed to the WHERE-condition are supposed to be dynamic you might better apply a prepared statement (which could be used multiple times):
SET q1 = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ?;';
PREPARE stmt FROM q1;
EXECUTE stmt USING DepID;
这篇关于插入表 Teradata 动态存储过程 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!