当X是变量时，DB2创建表X？ [英] DB2 Create Table X when X is a variable?

问题描述

在Oracle中，我们这样做：

In Oracle, we do this:

def TNAME=&1
create table &TNAME (foo varchar(10));

如何做DB2的等效项？限制是：

How to do the equivalent for DB2? The constraints are:

1. The create table statement is generated when TNAME is unknown
2. The create table statement is in a file which cannot be modified
3. Cannot create a temporary file with TNAME substituted by awk
4. Essentially we want to pass in the table name at run time as an argument

可能？

推荐答案

EXECUTE IMMEDIATE 语句可以在任何由BEGIN和END包围的SQL块中使用，例如作为存储过程或匿名复合语句。

The EXECUTE IMMEDIATE statement can be used in just about any block of SQL that is surrounded by a BEGIN and END, such as a stored procedure or anonymous compound statement.

CREATE PROCEDURE create_table_from_file( IN fileDir VARCHAR( 1024 ), 
                                         IN fileName VARCHAR( 128 ),
                                         IN tabSchema VARCHAR( 128 ),
                                         IN tabName VARCHAR( 128 )
                                        )
BEGIN
DECLARE fh UTL_FILE.FILE_TYPE;
DECLARE sqlStmt VARCHAR( 32672 );
DECLARE currentLine VARCHAR( 32672 );
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLSTATE1 CHAR(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR SQLSTATE 'ORANF' SET SQLSTATE1 = SQLSTATE;

SET sqlStmt = 'CREATE TABLE ' 
    || CASE WHEN ( NULLIF( tabSchema, '' ) IS NOT NULL ) 
       THEN RTRIM( tabSchema ) || '.'
       ELSE '' END || tabName;
CALL UTL_DIR.CREATE_OR_REPLACE_DIRECTORY( 'createTablePath', fileDir );
SET fh = UTL_FILE.FOPEN( 'createTablePath', fileName, 'r' );
loop1: LOOP
    CALL UTL_FILE.GET_LINE( fh, currentLine );
    IF SQLSTATE1 = 'ORANF' THEN -- NO DATA FOUND
        LEAVE loop1;
    END IF;
    SET sqlStmt = sqlStmt || ' ' || currentLine ;
END LOOP;
EXECUTE IMMEDIATE sqlStmt;
END
@

这篇关于当X是变量时，DB2创建表X？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！