存储过程没有通过Dbaver Application在DB2 luw上执行。
我在DB2 luw数据库中的模式“ILQASRC”下创建了一个存储过程。程序代码如下:
CREATE OR REPLACE PROCEDURE ilqasrc.create_n_columns(IN num_of_cols INTEGER,IN schema_name VARCHAR(20),IN typ VARCHAR(20))
BEGIN
DECLARE i INT;
DECLARE create_sql VARCHAR(100);
DECLARE alter_sql VARCHAR(100);
DECLARE table_name VARCHAR(20);
SET table_name=schema_name||'.COLUMNS_'||num_of_cols||'_'||typ;
SET create_sql='CREATE TABLE '||table_name||'(PK INTEGER NOT NULL,PRIMARY KEY(PK))';
EXECUTE IMMEDIATE create_sql;
SET i=0;
WHILE i < num_of_cols-1
DO
IF MOD(i,8)=0 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' INTEGER';
ELSEIF MOD(i,8)=1 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' BIGINT';
ELSEIF MOD(i,8)=2 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' DECIMAL(9,5)';
ELSEIF MOD(i,8)=3 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' CHAR(10)';
ELSEIF MOD(i,8)=4 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' VARCHAR(10)';
ELSEIF MOD(i,8)=5 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' DATE';
ELSEIF MOD(i,8)=6 THEN
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' TIME';
ELSE
SET alter_sql='ALTER TABLE '||table_name||' ADD COLUMN COL'||i||' TIMESTAMP(6)';
END IF;
EXECUTE IMMEDIATE alter_sql;
SET i=i+1;
END WHILE;
END@
为了执行上述过程,我执行了以下语句:
CALL ILQASRC.CREATE_N_COLUMNS(1012, ILQASRC, varchar);
但执行时出现以下错误:
SQL Error [42703]: "ILQASRC" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.31.10
SQL Error [42703]: "ILQASRC" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.31.10
"ILQASRC" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.31.10
"ILQASRC" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.31.10
An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "ILQASRC".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.31.10
An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "ILQASRC".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.31.10
An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "ILQASRC".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.31.10
An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "ILQASRC".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.31.10
1条答案
按热度按时间nkcskrwz1#
问题不在于过程,而在于调用语法,您只需将varchar参数的值放在引号之间,如