Oracle 实验室数据库设计（管道功能？）
主要目标：查询数据库以确定实验室技术人员下一步应该做什么。
我想要完成的任务
我正在设计一个实验室数据库，其中以下每个实体都需要一个父级（左侧）和至少一个子级（右侧）： request (REQ) ->样品（SAM）->测试（TST）->测量（MEA）。
每个实体都有以下内容：
- 模板 - 该实体的（或类型）（测试模板可能包括：pH 测试、滴定等）
- 自定义表格 - 仅适用于该模板的表格字段
- 计算 - 根据该实体的数据得出相关公式的结果（和孩子）
我们的规格通常非常复杂。例如，规范可能会规定“如果前两次测量不符合规范，则可以进行第三次测量”，或者“如果快速测试方法失败，则执行长测试方法”。
我想要一个接受 ENTITY_id 并（基于已输入的数据和规范规则）返回可用子模板列表的函数。
问题
管道函数（如下面的 tuinstoel 所描述）是实现我想要实现的 need_children() 函数的最佳方法吗？
如果没有，你有什么建议？
Main Goal: Query the database to determine what the lab technician should do next.
What I am trying to accomplish
I am designing a laboratory database where each of the following entities requires exactly one parent (to the left) and at least one child (to the right): request (REQ) -> sample (SAM) -> test (TST) -> measurement (MEA).
Each entity has the following:
- Template - (or type) of that entity (test templates might include: pH test, titration, etc)
- Custom Tables - table fields which only apply to that template
- Calculations - results of relevant formulas from data for that entity (and children)
Our specifications are often very complex. For example, a spec might state, "A third measurement may be taken if the first two are out of spec," or "Perform the long test method if the quick test method fails."
I want a function which takes an ENTITY_id and (based on already entered data and specification rules) returns a list of available child templates.
Question
Are Pipelined Functions (as described by tuinstoel below) the best way to implement the needed_children() function that I'm trying to achieve?
If not, what do you suggest?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我不认为你真的需要一个管道函数。流水线函数对于可以永久生成数据的生成器很有用。例如，您可以使用返回斐波那契数的管道函数。从概念上讲，管道函数与 .net 中的迭代器相同。
对于您的担忧，您还可以使用非管道表函数。这是一个将数据收集（生成、计算等）到“记录数组”中的函数，并最终返回该数组。根据您需要执行的操作，这可能比管道函数更容易使用，也可能不是。如果“记录数组”是您处理数据的自然方式，那么它可能是更好的选择。否则，管道函数可能会更好。
I don't think you really need a pipelined function. Pipelined functions are good for generators that could produce data forever. For example, you could use a pipelined function that returns fibonacci numbers. Conceptually, pipelined functions are the same like iterators in .net.
For your concerns, you could also use a non-pipelined table function. This is a function that collects (generates, calculates etc.) the data into an "array of records", and eventually returns that array. Depending on what you have to do, that can be easier to use than pipelined functions or not. If the "array of records" is something that is natural in the way you process data, it might be the better choice. Otherwise, pipelined functions might be better.
我不同意 - 从功能上讲，它与使用临时表相同，但您可以在父函数中使用 INSERT/UPDATE 语句执行相同的操作。
I do not agree - functionally it's the same as using a temp table, but you could do the same stuff using INSERT/UPDATE statements in the parent function.
对于如此复杂的规范，我会使用支持多态性的面向对象语言或像 Drools 这样的规则引擎（http ://www.jboss.org/drools/）。您仍然可以将数据存储在数据库中，并使用查询来尽可能限制内存中的处理量。
使用面向对象的语言（例如Java或者数据库的过程语言），您可以有一个规范基类（或接口），然后在子类中实现特定的行为。在数据库中，您可以配置规范的应用位置。
规则引擎将更加灵活，因为规则只是可以根据需要更改的文本字符串，但如果从未使用过它们，则存在一些学习曲线。
With such complex specifications, I would use either an object-oriented language that supports polymorphism or a rule engine like Drools (http://www.jboss.org/drools/). You can still store the data in the database and use queries to limit the amount of in-memory processing as much as possible.
With an object-oriented language (e.g. Java or maybe the procedural language of your database), you could have a Specification base class (or interface), and then implement specific behavior in subclasses. In the database you would configure where a Specification applies.
The rule engine would be even more flexible because rules are just text strings that can be changed as needed, but if have never used them there is a bit of a learning curve.
您想通过一条 select 语句选择主数据和明细数据吗？尝试选择..cast..multiset。请参阅此处：http://download.oracle.com /docs/cd/B19306_01/server.102/b14200/operators006.htm
EDIT1
OP似乎想要一个管道函数。您可以使用游标作为管道函数的内参数。流水线功能非常灵活。请参阅此处： http://download.oracle .com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345
You want to select master data and detail data with one select statement? Try select ..cast..mulitiset. See here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/operators006.htm
EDIT1
The OP seems to want a pipelined function. You can use a cursor as in-parameter of a pipelined function. Pipelined function are very flexible. See here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345