sql - 从 Oracle 函数返回多个值
问题描述
我想创建一个函数,将多行返回到对象类型的表中。
我已经创建了一个对象和一个嵌套表对象,现在当我运行该函数时出现一个错误,上面写着
PL/SQL: SQL 语句被忽略 PL/SQL: ORA-00947: 没有足够的值
-- Object type creation
create or replace type test_object_sn as object
(
column_1 varchar2(30),
column_2 varchar2(30),
column_3 number
);
-- Table of object
create or replace type test_otable_sn as table of test_object_sn;
-- function (where I get an error)
create or replace function load_test_object_sn
return test_otable_sn
as
details test_otable_sn;
begin
with ad as (select 'a', 'b', 4 from dual
union all
select 'r', '5', 3 from dual
union all
select 'g', 's', 3 from dual)
select * into details from ad;
return details;
end;
我想让 test_otable_sn 表对象加载数据,然后通过我的 load_test_object_sn 函数使用 table() 函数查询它,例如 select * from table(load_test_object_sn);
解决方案
更新:
你知道如何修改这个场景,即我有一个包含在字符串变量中的sql语句来执行吗?
是的,我们可以使用游标引用 (SYS_REFCURSOR) 和 OPEN/FETCH/CLOSE 来代替 CURSOR 和 CURSOR FOR LOOP。
语法是OPEN <cursor-reference> FOR <string-containing-sql-statement>
. 见下文。
CREATE OR REPLACE FUNCTION load_test_object_sn
RETURN test_otable_sn
AS
details test_otable_sn := test_otable_sn();
-- Variable stores SQL statement for cursor
l_sql CLOB :=
q'[with ad as (
select 'a' column_1, 'b' column_2, 4 column_3 from dual union all
select 'r', '5', 3 from dual union all
select 'g', 's', 3 from dual
)
select *
from ad]';
-- Cursor reference allows us to open cursor for SQL statement above
rc SYS_REFCURSOR;
-- Define object instance to store each row fetched from the cursor
l_obj test_object_sn := test_object_sn(NULL, NULL, NULL);
i PLS_INTEGER := 1;
BEGIN
-- Explicitly open, fetch from, and close the cursor
OPEN rc FOR l_sql;
LOOP
FETCH rc INTO l_obj.column_1, l_obj.column_2, l_obj.column_3;
EXIT WHEN rc%NOTFOUND;
details.extend();
details(i) := test_object_sn(l_obj.column_1, l_obj.column_2, l_obj.column_3);
i := i + 1;
END LOOP;
CLOSE rc;
RETURN details;
END;
原答案:
不幸的是,不能以这种方式将 SELECT * INTO 与集合一起使用,因此这是填充表的另一种方法:
create or replace function load_test_object_sn
return test_otable_sn
as
details test_otable_sn := test_otable_sn();
cursor c_ad is
with ad as (select 'a' column_1, 'b' column_2, 4 column_3 from dual
union all
select 'r', '5', 3 from dual
union all
select 'g', 's', 3 from dual)
select * from ad;
i pls_integer := 1;
begin
for ad_rec in c_ad loop
details.extend();
details(i) := test_object_sn(ad_rec.column_1, ad_rec.column_2, ad_rec.column_3);
i := i + 1;
end loop;
return details;
end;
/
输出:
SQL> SELECT * FROM TABLE(load_test_object_sn);
COLUMN_1 COLUMN_2 COLUMN_3
---------- ---------- ----------
a b 4
r 5 3
g s 3
推荐阅读
- haskell - 使用镜头测试地图成员资格
- javascript - 如何在同一行上制作文字和箭头。文本中心和最左侧/右侧的箭头
- java - NoSuchFileException:/usr/share/java/etc/webdefault.xml 在 Intellij 中运行 jetty 9 服务器时
- fluentd - 如何使程序的标准输出流利(没有 docker)
- javascript - 如何在我的 django 主机网页上运行计时器?
- .net - .NET 批量插入 MSSQL:无法在一台服务器上获取列排序规则信息
- vb.net - 如何获取显示模式对话框的监视器的 DPI
- c# - 将非泛型类转换为泛型基类
- c++ - 命名空间内的命名空间别名
- okta - Okta 可以用于将以编程方式访问的 API 吗?