1、给普通类型变量赋单个值

CREATE OR REPLACE PROCEDURE T_1208(ID INT)
AS
Z INT;
BEGIN
EXECUTE IMMEDIATE 'SELECT TOP 1  ID FROM  D_I WHERE ID>? ' INTO Z USING ID; 
PRINT Z;
END;

执行结果

[执行语句1]:
CALL T_1208(15);
执行成功, 执行耗时2毫秒. 执行号:671
63

2、给变量赋多个值

2.1、SELECT INTO 中包含多行错误

CREATE OR REPLACE PROCEDURE T_1208(ID INT)
AS
Z INT;
BEGIN
EXECUTE IMMEDIATE 'SELECT  ID FROM  D_I WHERE ID>? ' INTO Z USING ID; 
PRINT Z;
END;

执行结果

说明：由于返回的行数为多行而SELECT … INTO 不支持返回多行。

2.2、使用嵌套表集合类型结合BULK COLLECT参数返回多个值（一）

CREATE OR REPLACE PROCEDURE T_1208(ID INT)
AS

TYPE ZQH IS TABLE OF D_I.ID%TYPE;
Z ZQH;
BEGIN
EXECUTE IMMEDIATE 'SELECT  ID FROM  D_I WHERE ID>? ' BULK COLLECT INTO Z USING ID; 
PRINT Z(1);
END;

执行结果

2.3、使用嵌套表集合类型结合BULK COLLECT参数返回多个值（二）

CREATE OR REPLACE PROCEDURE T_1208(ID INT)
AS

TYPE ZQH IS TABLE OF D_I%ROWTYPE;
Z ZQH;
BEGIN
EXECUTE IMMEDIATE 'SELECT  * FROM  D_I WHERE ID>? ' BULK COLLECT INTO Z USING ID; 
PRINT Z(1).ID;
END;

执行结果

总结：动态SQL中的SELECT …INTO不支持多行数据返回，要使用多行数据返回需要使用SELECT … BULK COLLECT INTO 结合集合类型。

链接: 达梦技术社区https://eco.dameng.com/.