如何在 Teradata 中执行动态 SQL [英] How to execute dynamic SQL in Teradata

问题描述

有没有办法将动态生成的 SQL 提交给 Teradata?我已经编写了一个查询，该查询将创建用于对表进行非规范化的代码.现在，我正在将代码拉到我的客户端 (SAS) 并在第二步中重新提交.我不熟悉 Teradata 宏或程序；这样的事情会起作用吗?

Is there any way to submit dynamically generated SQL to Teradata? I've written a query that will create the code to denormalize a table. Right now, I am pulling the code down to my client (SAS) and resubmitting it in a second step. I am not familiar with either Teradata macros or procedures; would something like that work?

为了说明，我有一个这样定义的表:

To illustrate, I have a table defined like this:

create multiset table MYTABLE
    (  RECID  integer generated always as identity
              ( start with 1
               increment by 1
               minvalue -2147483647
               maxvalue 2147483647
               no cycle )
     , SNAP_DATE  date format 'YYYY/MM/DD'
     , EMAIL_DATE date format 'YYYY/MM/DD'
     , FREQ integer
    )
unique primary index ( RECID  )

该表每天都会填充(SNAP_DATE)，用于监控另一个表中 email_date 的更改.以下查询返回我可以运行以创建非规范化视图的代码:

The table is populated every day (SNAP_DATE) and is used to monitor changes to an email_date in another table. The following query returns the code that I can run to create my denormalized view:

select RUN_THIS
from (
    select RUN_THIS, RN
    from (
        select 'select EMAIL_DATE ' (varchar(100)) as RUN_THIS
              , 0 (int) as RN
          ) x

    union all
    select ', sum( case when SNAP_DATE = date '''
             || (SNAP_DATE (format 'yyyy-mm-dd') (char(10)) )
             || ''' then FREQ else 0 end ) as D'
             || (SNAP_DATE (format 'yyyymmdd') (char(8)) ) as RUN_THIS
          , row_number() over ( partition by 1 order by SNAP_DATE ) as RN
    from ( select distinct SNAP_DATE 
           from MYTABLE 
           where SNAP_DATE > current_date - 30) t1

    union all
    select RUN_THIS, RN
    from (
        select 'from MYTABLE group by 1 order by 1;' as RUN_THIS
                , 10000 as RN
          ) y
    ) t
order by RN

我将上述查询的结果导出到我客户端上的一个文件中，然后转身将该文件提交回 Teradata.我希望有某种方法可以将这个完整的定义存储在某个 Teradata 对象中，以便可以直接执行.

I export the result of the above query to a file on my client, then turn around and submit that file back to Teradata. I'm hoping there is some way to store this complete definition in some Teradata object so it can be executed directly.

推荐答案

您可能会发现使用 DBC.SysExecSQL 命令将其成功放入存储过程中.

You may find success putting this in a stored procedure using the DBC.SysExecSQL command.

以下是 Teradata 中存储过程的过度简化示例.显然，在生产中需要定义一个错误处理程序来解决诸如无效数据库对象之类的问题.此外，您可以将 SQLSTATE 作为参数返回，以测试存储过程是否成功完成.

Here is an overly simplified example of a stored procedure in Teradata. Obviously in production would want an error handler defined to address things like invalid database objects. Furthermore, you could return the SQLSTATE back as a parameter to test for whether the stored procedure completed successfully or not.

CREATE PROCEDURE SYSDBA.CommentDatabase
(
  IN P_Database VARCHAR(30),
  IN P_Comment VARCHAR(255), 
  OUT MSG
)
MAIN:  --Label
BEGIN

  DECLARE  P_SQL_TEXT     VARCHAR(4000);

  SET P_SQL_TEXT='COMMENT ON DATABASE '||P_DATABASE||' AS '''||P_COMMENT||'''';
  CALL dbc.SysExecSQL (:P_SQL_TEXT);

  SET MSG = 'Database '||P_DBNAME||' commented successfully!';
END;

这篇关于如何在 Teradata 中执行动态 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！