如何使用 plsql 返回多个列

Question

我有一个受SAR保护的oracle数据库，我需要从中向PowerBI公开一个表。

我不熟悉 PLSQL。

我已经设法向 PowerBI 公开了表的一列。

需要在两个方面提供帮助

1(我需要你们的帮助才能从表中返回选择性列

2(我需要你们的帮助才能从表中返回所有列

DROP TYPE testarr;
CREATE OR REPLACE TYPE testarr IS TABLE OF VARCHAR2(70);
/
GRANT EXECUTE ON testarr TO public;
DROP FUNCTION TestPowerBI
CREATE OR REPLACE FUNCTION TestPowerBI
RETURN testarr AUTHID CURRENT_USER AS
o_recorset SYS_REFCURSOR;
arr testarr := testarr();
pragma autonomous_transaction;
BEGIN
sar.pk_sar_enable_roles.............
commit;
OPEN o_recordset FOR
SELECT NAME FROM vw_people;
FETCH o_recordset BULK COLLECT INTO arr;
CLOSE o_recordset;
RETURN arr;
END TestPowerBI
Grant execute on TestPowerBi to public;

Answer 1

您可以创建一个PIPELINED TABLE函数。

假设这是您的桌子。

create table vw_people ( ID INTEGER, NAME VARCHAR2(10));
INSERT INTO vw_people(id,name) VALUES ( 1,'Knayak');
commit;

创建对象和对象类型的集合

CREATE OR REPLACE TYPE vw_people_typ AS OBJECT( ID INTEGER,NAME VARCHAR2(10)); 
CREATE OR REPLACE TYPE vw_people_tab AS TABLE OF vw_people_typ; 

这是你的功能

CREATE OR REPLACE FUNCTION testpowerbi RETURN vw_people_tab
PIPELINED
AUTHID current_user
AS
vwt   vw_people_tab;
PRAGMA autonomous_transaction;
BEGIN
sar.pk_sar_enable_roles;
commit;
SELECT
vw_people_typ(id,name)
BULK COLLECT
INTO vwt
FROM
vw_people;
FOR i IN 1..vwt.count LOOP
PIPE ROW ( vw_people_typ(vwt(i).id,vwt(i).name) );
END LOOP;
END testpowerbi;
/

将函数的输出作为TABLE查询

select * from TABLE(TestPowerBI);
ID NAME      
---------- ----------
1 Knayak