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
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 不支持返回多行。
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;
执行结果
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/.