时间戳

首页 > 解决方案 > Error: DB2 Stored procedure with error A value is not compatible with the data type of its assignment target。目标名称是“C1”

db2 - Error: DB2 Stored procedure with error A value is not compatible with the data type of its assignment target。目标名称是“C1”

问题描述

创建存储过程时出现错误

“一个值与其赋值目标的数据类型不兼容。目标名称是“C1”.. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.19.56”

帮我创建一个带有游标变量的存储过程

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C2 CHAR(1));

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR;

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
BEGIN
    
DECLARE V_ROW DE_ROW_T;
DECLARE C1  C_ID_CURSOR_T;

SET C1 = CURSOR FOR 
        SELECT DISTINCT 
           COL1,COL2,COL3
          FROM    
            MYTABLE
          WHERE COL1=101;

OPEN C1;
FETCH C1 INTO V_ROW;

END;

标签： db2db2-luw

解决方案

您的表 DDL 必须与数据类型和长度中的行类型匹配，否则您将在尝试将错误数据放入您的行类型变量时遇到异常。

以下脚本显示了如何通过将进度打印为调试工具来查看在 Linux 上使用 Db2-LUW v11.5.4.0 运行的存储过程。您还可以使用其他 GUI 工具（包括 IBM Data Studio）调试存储过程。

--#SET TERMINATOR ;

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C3 CHAR(1));

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR;

drop table if exists mytable;

create table mytable(col1 integer, col2 varchar(100), col3 char(1));
insert into mytable(col1,col2,col3) values(101,'abcd','a');


--#SET TERMINATOR @

set serveroutput on@

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
specific curtest
BEGIN
    DECLARE V_ROW DE_ROW_T;
    DECLARE C1  C_ID_CURSOR_T;

    SET C1 = CURSOR FOR SELECT DISTINCT COL1,COL2,COL3 FROM MYTABLE WHERE COL1=101;

    call dbms_output.put_line('opening c1');
    OPEN C1;
    call dbms_output.put_line('opened c1');
    FETCH C1 INTO V_ROW;
    call dbms_output.put_line('fetched c1: '||varchar(v_row.c1)||' , '||trim(v_row.c2)||' , '||v_row.c3);
END
@

call sp_curtest()@

如果您在命令行通过 db2 CLP 运行上述脚本，它将显示以下输出：

    $ db2 -tvf sp_so_8.sql

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C3 CHAR(1))
DB20000I  The SQL command completed successfully.

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR
DB20000I  The SQL command completed successfully.

drop table if exists mytable
DB20000I  The SQL command completed successfully.

create table mytable(col1 integer, col2 varchar(100), col3 char(1))
DB20000I  The SQL command completed successfully.

insert into mytable(col1,col2,col3) values(101,'abcd','a')
DB20000I  The SQL command completed successfully.

set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
specific curtest
BEGIN
    DECLARE V_ROW DE_ROW_T;
    DECLARE C1  C_ID_CURSOR_T;

    SET C1 = CURSOR FOR SELECT DISTINCT COL1,COL2,COL3 FROM MYTABLE WHERE COL1=101;

    call dbms_output.put_line('opening c1');
    OPEN C1;
    call dbms_output.put_line('opened c1');
    FETCH C1 INTO V_ROW;
    call dbms_output.put_line('fetched c1: '||varchar(v_row.c1)||' , '||trim(v_row.c2)||' , '||v_row.c3);
END

DB20000I  The SQL command completed successfully.

call sp_curtest()

  Return Status = 0

opening c1
opened c1
fetched c1: 101 , abcd , a

$

推荐阅读