从PL/SQL函数返回“表"(没有预定义的列名) [英] Returning a 'table' (without pre-defining column names) from a PL/SQL function

问题描述

为您提供信息，我的问题基于此网络教程 http ://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

您可能会看到，该函数返回一个表"，我需要创建一个新的对象类型，其中包含将要返回的字段.我的问题是我不想预定义对象类型，因为我想创建可以生成任何表的通用函数.

有什么主意吗?！

解决方案

我会强烈怀疑您不想这样做.在PL/SQL中动态创建对象类型并建立这种灵活性几乎总是导致失败的秘诀.您最终将在各处(在调用程序和函数中)都编写极其复​​杂的动态SQL，这将使您的代码变得越来越难以阅读和调试.当您的代码变得如此复杂时，最好不要编写会生成代码的框架，但要编写代码来解决问题的代码要比编写只解决问题的代码要困难得多. /p>

也就是说，如果您确实有真正的决心，则可以构建完全通用的流水线表功能.这涉及使用Oracle Data Cartridge框架的一些深入的向导.很酷但是可以实际支持和扩展这种代码的人数很少.

使用较少的向导(但仍然比我对几乎任何组织的建议都多)，您可以构建http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

As you may see, the function returns a 'table' and I need to create a new object type that contains the fields that are going to be returned. My question is that I do not want to pre-define the object type because I wan to create generic function that can generate any table.

Any idea?!!

解决方案

I would strongly suspect that you don't want to do this. Dynamically creating object types and building this sort of flexibility in PL/SQL is almost always a recipe for ruin. You'll end up writing extremely complicated dynamic SQL everywhere (both in the callers and in your functions) which will make your code far, far harder to read and debug. When your code gets that complex, you're better off writing frameworks that generate the code but that leaves you with the much, much harder problem of writing code that generates code that solves your problem rather than just writing code that solves your problem.

That said, if you are truly, truly determined, it is possible to build a completely generic pipelined table function. That involves some deep, deep wizardry using the Oracle Data Cartridge framework. It's quite cool. But the number of people that could realistically support and extend this sort of code is very small.

With slightly less wizardry (but still way, way more than I would suggest for virtually any organization), you can build a flexible pipelined table function that relies on returning types from a pre-defined type hierarchy. That's more likely to be supportable but it assumes that you can build your type hierarchy up front.

这篇关于从PL/SQL函数返回“表"(没有预定义的列名)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！