如何在 Oracle 中定义具有动态返回类型的 pl sql 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5966270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to define a pl sql function with dynamic return types in Oracle?
提问by Alex. S.
I have a set of tables with different data type for the columns and I need to consolidate a way for retrieving data. I thought using a function would be a good idea, but I don't know how to define one function having different return types.
我有一组具有不同数据类型的表，我需要整合一种检索数据的方法。我认为使用函数是个好主意，但我不知道如何定义一个具有不同返回类型的函数。
For example, how to define this function to be able to use different definitions for tabletype.
例如，如何定义这个函数才能对tabletype 使用不同的定义。
CREATE OR REPLACE FUNCTION retrieve_info(field_id in integer)
RETURN pintegertypetable -- <-- how to change this to return a more generic record built dynamically in the code below?
AS
r pintegertypetable := pintegertypetable ();
BEGIN
r.extend;
r(i) := pintegertypetable (someinteger);
return r;
END;
Is that possible?. Is there a better way to handle this problem: different columns stored originally in a lot of legacy tables, and given that every column has different data types, in which way we can retrieve the most recent information conserving the original data types without hardcoding views neither storing everything in varchar2 and casting again in client code?
那可能吗？。有没有更好的方法来处理这个问题：不同的列最初存储在很多遗留表中，并且考虑到每一列都有不同的数据类型，这样我们就可以检索最新的信息，保留原始数据类型，而无需对视图进行硬编码将所有内容存储在 varchar2 中并在客户端代码中再次转换？
回答by Datajam
You can implement this by using a weakly-typed Ref Cursor as the return type. This is especially easy to implement from a client interface using JDBC, as the returned cursor type can be stepped through just like any query result and the metadata can be interrogated from ResultSet.getMetaData(). Here's an example:
您可以通过使用弱类型的 Ref Cursor 作为返回类型来实现这一点。这特别容易从使用 JDBC 的客户端接口实现，因为返回的游标类型可以像任何查询结果一样单步执行，并且可以从 ResultSet.getMetaData() 查询元数据。下面是一个例子：
CREATE OR REPLACE PROCEDURE retrieve_info(field_id in integer, p_cursor in out sys_refcursor)
AS
BEGIN
open p_cursor for 'select * from emp';
END;
The query in quotes could be anything returning any type, for any number of columns.
对于任意数量的列，引号中的查询可以是返回任何类型的任何内容。
回答by Alex. S.
Using the answer of Datajam Ltd, this could be useful for someone else:
使用 Datajam Ltd 的回答，这对其他人可能有用：
drop table xxx1;
drop table xxx2;
drop table xxx3;
drop table xxx4;
create table xxx1(val integer);
create table xxx2(val date);
create table xxx3(val number);
create table xxx4(val varchar2(100));
insert into xxx1 (val) select rownum from all_objects where rownum <= 1;
insert into xxx2 (val) select sysdate+rownum from all_objects where rownum <= 2;
insert into xxx3 (val) select 12.345+rownum from all_objects where rownum <= 3;
insert into xxx4 (val) select 'test'||rownum from all_objects where rownum <= 4;
CREATE OR REPLACE PROCEDURE retrieve_info(p_cursor in out sys_refcursor, tabname in varchar2)
AS
BEGIN
open p_cursor for 'select val from ' || tabname;
END;
/
-- CLIENT CODE IN PLSQL:
drop table logtable;
create table logtable(x varchar2(1000));
declare
v_rc sys_refcursor;
b varchar2(1000);
begin
retrieve_info(v_rc, 'xxx2'); -- here you can parameterize your call
loop
fetch v_rc into b;
exit when v_rc%notfound;
dbms_output.put_line(b);
insert into logtable(x) values(b);
end loop;
end;
/
select * from logtable;
回答by APC
If you are interested in casting single columns to strings, the easiest way is to write a package with overloaded functions. Each function has the same name, only the signature of the input parameter(s) varies:
如果您对将单列转换为字符串感兴趣，最简单的方法是编写一个带有重载函数的包。每个函数都有相同的名称，只有输入参数的签名不同：
SQL> create or replace package str_util
2 as
3 function s (p in varchar2) return varchar2;
4 function s (p in number) return varchar2;
5 function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2;
6 end;
7 /
Package created.
SQL>
This is obviously a simplistic implementation. For production you might want some error handling, numeric format masks, more datatypes, etc.
这显然是一个简单的实现。对于生产，您可能需要一些错误处理、数字格式掩码、更多数据类型等。
SQL> create or replace package body str_util
2 as
3 function s (p in varchar2) return varchar2
4 is
5 begin
6 return p;
7 end s;
8 function s (p in number) return varchar2
9 is
10 begin
11 return to_char(p);
12 end s;
13 function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2
14 is
15 begin
16 return to_char(p, fmt);
17 end s;
18 end;
19 /
Package body created.
SQL>
In the foolowing query I show the datatypes of three columns of a table:
在愚蠢的查询中，我显示了一个表的三列的数据类型：
SQL> select dump(id) id_is_number
2 , dump(col2) col2_is_varchar
3 , dump(col3) col3_is_date
4 from big_table
5 where rownum = 1
6 /
ID_IS_NUMBER
------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------
Typ=2 Len=3: 194,5,37
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=12 Len=7: 120,107,10,15,11,10,14
SQL>
In this version of the query I call the packaged function and get three strings back:
在这个版本的查询中，我调用打包函数并返回三个字符串：
SQL> select dump(str_util.s(id)) id_is_number
2 , dump(str_util.s(col2)) col2_is_varchar
3 , dump(str_util.s(col3)) col3_is_date
4 from big_table
5 where rownum = 1
6 /
ID_IS_NUMBER
------------------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------------------
Typ=1 Len=3: 52,51,54
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=1 Len=11: 49,53,45,111,99,116,45,50,48,48,55
SQL>
