从动态PL/SQL返回匹配基本SELECT from语句的值

Question

这无疑是一个非常基本的动态PL/SQL问题，但是我被卡住了。

我希望编写一个动态PL/SQL，其功能与下面的代码相同，从表名的所有行返回field1, field2, field3的值:

SELECT field1, field2, field3 FROM databasename.tablename;

如果我写下面的动态SQL，我可以让它成功执行。然而，我不能让它返回任何东西:

declare
Query  VARCHAR2(200) := 'SELECT field1, field2, field3 FROM databasename.tablename';
begin
EXECUTE IMMEDIATE Query;
end;

如何返回动态SQL的结果来匹配上面的一行SELECT语句，请问?

Answer 1

• 你的第一个选择语句是sql查询，它可以返回一个表格格式的结果。

• 但是你的第二个代码是anonymous block，如果没有INTO子句，你就不能在匿名块中执行任何select查询。

如果你知道你的select查询将给你单个记录，那么使用INTO子句如下:

EXECUTE IMMEDIATE Query into var1, var2, var3;

注意:用正确的数据类型声明var1, var2 and var3变量

Answer 2

从查询字符串返回数据集的直接方法是在返回类型为SYS_REFCURSOR的存储函数或过程中使用游标，例如

CREATE OR REPLACE FUNCTION Get_Result_Of_TheTable RETURN SYS_REFCURSOR IS
Query       VARCHAR2(200) := 'SELECT field1, field2, field3 FROM tablename';
v_recordset SYS_REFCURSOR;
BEGIN
OPEN v_recordset FOR Query;
RETURN v_recordset;
END;
/

，然后从SQL Developer的控制台中调用

DECLARE
result SYS_REFCURSOR;
BEGIN
:result := Get_Result_Of_TheTable;
END;
/
PRINT result;

Answer 3

有几种可能性。如果您知道返回哪些字段，那么您可以这样解决:

DECLARE
TYPE cursor_type IS REF CURSOR;
l_cursor cursor_type;
l_record tablename%ROWTYPE;
l_query  VARCHAR2(32767) := 'SELECT field1, field2, field3 FROM tablename';
BEGIN
OPEN l_cursor FOR l_query;
LOOP
FETCH l_cursor INTO l_record;
EXIT WHEN l_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (l_record.field1 ||' '|| l_record.field2 ||' '|| l_record.field3);
END LOOP;

CLOSE l_cursor;
END;
/

或者你可以写一个小的流水线函数:

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE test_type IS TABLE
OF tablename%ROWTYPE;

FUNCTION get_fields
RETURN test_type PIPELINED;
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
AS    
FUNCTION get_fields
RETURN test_type PIPELINED
AS
TYPE cursor_type IS REF CURSOR;
l_cursor cursor_type;
l_record tablename%ROWTYPE;
l_query  VARCHAR2(32767) := 'SELECT field1, field2, field3 FROM tablename';
BEGIN
OPEN l_cursor FOR l_query;
LOOP
FETCH l_cursor INTO l_record;
EXIT WHEN l_cursor%NOTFOUND;

PIPE ROW (l_record);
END LOOP;
CLOSE l_cursor;

END;
END pkg_test;
/
SELECT * FROM TABLE(pkg_test.get_fields);

这是我的测试:

CREATE TABLE tablename
( field1 VARCHAR2(10),
field2 VARCHAR2(10),
field3 VARCHAR2(10)
);
INSERT INTO tablename VALUES ('A1', 'A2', 'A3');
INSERT INTO tablename VALUES ('B1', 'B2', 'B3');
INSERT INTO tablename VALUES ('C1', 'C2', 'C3');
COMMIT;

Answer 4

您可以使用bulk collect into子句，就像PL/SQL块中的静态SQL一样。

下面是一个简单的例子:

drop table users_tb;
create table users_tb (
id           integer      generated always as identity,
--
username     varchar2(30) not null,
is_active    char(1)      default 1 not null,
created_date date         default sysdate not null ,
edited_date  date,
--
constraint user_id_pk        primary key (id),
constraint user_is_active_ch check (is_active in (1,0))
);
insert into users_tb (username) values ('john.wick');
insert into users_tb (username) values ('constantine');
insert into users_tb (username) values ('neo');
commit;
declare
-- types
type tr_list is record (
username  varchar2(30),
is_active integer
);
type tt_usernamelist is table of tr_list
index by pls_integer;

-- variables
lt_users tt_usernamelist;
l_stmt   varchar2(2000);
begin
-- select statement
l_stmt := 'select username, is_active from users_tb';

-- execution of dynamic code
execute immediate l_stmt 
bulk collect into lt_users;

-- loop over retrived data
for i in 1..lt_users.count loop
dbms_output.put_line('User: '||lt_users(i).username||' - Is active: '||lt_users(i).is_active);
end loop;
end;

重要的部分是我使用bulk collect into的execution of dynamic code。这里有一个DBFiddle的链接，可以试用一下。