如何在Oracle中使用动态返回类型定义pl sql函数? [英] How to define a pl sql function with dynamic return types in Oracle?

问题描述

我有一组表，这些表的列的数据类型不同，我需要合并一种检索数据的方法.我以为使用函数是一个好主意，但我不知道如何定义一个具有不同返回类型的函数.

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.

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;

有可能吗?有没有更好的方法来解决此问题:不同的列最初存储在许多旧表中，并且鉴于每一列都有不同的数据类型，因此我们可以检索保留原始数据类型的最新信息，而无需对视图进行硬编码将所有内容都存储在varchar2中，然后再次投射到客户端代码中?

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?

推荐答案

您可以通过使用弱类型的Ref Cursor作为返回类型来实现此目的.通过使用JDBC的客户端接口，这特别容易实现，因为返回的游标类型可以像任何查询结果一样逐步执行，并且可以从ResultSet.getMetaData()询问元数据.这是一个示例:

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:

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.

这篇关于如何在Oracle中使用动态返回类型定义pl sql函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！