3706 teradata 语句报错_sql - 尝试在Teradata中创建存储过程，但出现以下错误。 如何解决呢？ - 堆栈内存溢出...

您应该一一解决错误...

CREATE PROCEDURE MyNewProcedure()

BEGIN

DECLARE tablename CHAR(100);

DECLARE databasename CHAR(100);

DECLARE source_table_cursor CURSOR FOR

SELECT DatabaseName, TableName

FROM DBC.TablesV WHERE DatabaseName = 'Employees';-- missing semicolon

OPEN source_table_cursor;

looplabel:

LOOP

-- missing FROM

FETCH NEXT FROM source_table_cursor INTO databasename, tablename;

IF (SqlState = '02000') THEN

LEAVE looplabel;

END IF;

INSERT INTO table_row_count

SELECT tablename, Count(*)

FROM databasename.tablename;-- missing semicolon

END LOOP;

CLOSE source_table_cursor;

END;

现在SP可以编译了，但是正如Fred所说，CALL失败了，您需要Dyamic SQL。

这是一个示例SP，它通过游标和动态结果集使用Dynamic SQL返回数据库中所有表的行数：

/*

Return a row count for all tables in a database

*/

REPLACE PROCEDURE CountProc

(

IN db_name VARCHAR(128) CHARACTER SET Unicode

)

DYNAMIC RESULT SETS 1

BEGIN

DECLARE tbl_name VARCHAR(128) CHARACTER SET Unicode;

DECLARE sql_stmt VARCHAR(500) CHARACTER SET Unicode;

DECLARE count_cursor CURSOR FOR

SELECT TABLENAME

FROM dbc.TablesV

WHERE databasename = :db_name

AND tablekind = 'T'

;

DECLARE rslt CURSOR WITH RETURN ONLY FOR s;

CREATE VOLATILE TABLE VT_table_counts(

TABLENAME VARCHAR(128) CHARACTER SET Unicode,

Cnt BIGINT

) ON COMMIT PRESERVE ROWS;

OPEN count_cursor;

FETCH NEXT FROM count_cursor INTO tbl_name;

WHILE SqlCode=0

DO SET sql_stmt = 'INSERT INTO VT_table_counts SELECT '''|| tbl_name ||''', COUNT(*) FROM "' || db_name || '"."' || tbl_name || '";';

EXECUTE IMMEDIATE sql_stmt;

FETCH NEXT FROM count_cursor INTO tbl_name;

END WHILE;

CLOSE count_cursor;

SET sql_stmt = 'SELECT * FROM VT_table_counts;';

PREPARE s FROM sql_stmt;

OPEN rslt;

DROP TABLE VT_table_counts;

END;