Oracle-我应该使用临时表还是引用游标 [英] Oracle-Should I use temp table or ref cursor

问题描述

我有以下场景，我将不得不写一个存储过程：



包含invoice_ID和invoice_line_ID的标题表

地址行表包含与标题表中每个invoice_line_ID对应的invoice_line_id和Ship_From和Ship_To。

3.地址标题表包含与标题表中每个invoice_id对应的invoice_ID和'Ship_From'和'Ship_To'。

案例并非总是所有'Ship_From'并且Ship_To信息将出现在地址行表中。在这种情况下，需要从Address Header表中选择信息。



所以我将编写一个案例结构和两个连接：1。那将加入Header表和地址行表2.那将加入Header表和Address Header表。如果条件表中没有特定invoice_line_id的完整信息，则执行第二次连接的条件。



我的问题是我应该在哪里存储信息？我将使用游标来执行上述案例结构。但是在这种情况下我应该使用引用游标还是临时表？



请注意我的客户不喜欢数据库中额外数据库对象的想法所以我我完成显示后可能要删除临时表。我需要帮助以及是否有临时表的替代方法或者引用游标是否占用数据库上的额外空间。

I have the following scenario for which i will have to write a stored procedure:

Header table containing invoice_ID and invoice_line_ID
Address Line table containing invoice_line_id and 'Ship_From' and 'Ship_To' corresponding to each invoice_line_ID in header table.
3.Address header table containing invoice_ID and 'Ship_From' and 'Ship_To' corresponding to each invoice_id in header table.
The cases are such that not always all 'Ship_From' and 'Ship_To' information will be present in the Address Line table. In that case the information needs to be selected from Address Header table.

So i will write a case structure and two joins : 1. That will join Header table and Address Line table 2. That will join Header table and Address Header table. with the condition to do the second join in case entire information for a particular invoice_line_id is not available in line table.

My question here is where should i store the information? I will use a cursor to perform the above case structure. But should i use a ref cursor or a temp table in this case?

Please note that my customer is not liking the idea of extra database objects in the database so i might have to delete the temp table after i am done displaying. I need help on that as well as to is there any alternative to temp table or whether ref cursor take up extra space on the database or not.

推荐答案

没有简单的答案，因为它取决于很多因素。

请参阅： Oracle：复杂记录集 [ ^ ]和在Oracle中创建和使用临时表 [ ^ ]



在你的情况下，我建议使用简单 SELECT 声明以及合适的 JOINs [ ^ ]和 COALESCE [ ^ ]功能。 COALESCE 函数用于将空值替换为默认值。

最后，您的查询可能如下所示：

There is no simple answer, because it depends on many factors.
Please, see: Oracle: Complex recordsets[^] and Creating and Using Temporary Tables in Oracle[^]

In your case i'd suggest to use simple SELECT statement together with suitable JOINs[^] AND COALESCE[^] function. COALESCE function is used to replace nulls with default values.
Finally, your query might look like:

SELECT HT.<Field_List>, COALESCE(AL.ShipFrom, AH.ShipFrom) AS ShipFrom, COALESCE(AL.ShipTo, AH.ShipTo) AS ShipTo
FROM HeaderTable AS HT LEFT JOIN AddressLineTable AS AL ON HT.invoice_line_id = AL.invoice_line_id
    LEFT JOIN AdressHeaderTable AS AH ON HT.invoice_line_id = HT.invoice_line_id

如您所见，如果 AL.ShipFrom 将 NULL ，则应将其替换为 AH.ShipFrom 。



注意：我不知道您的数据结构，因此查询必须根据您的需要进行更改。



关于JOIN的更多有用信息，你可以在这里找到： SQL连接的可视化表示 [ ^ ]

As you can see, in case when AL.ShipFrom will be NULL, it should be replaced with AH.ShipFrom.

Note: i have no idea about your data structure, so the query have to be changed to your needs.

More useful information about JOINs, you'll find here: Visual Representation of SQL Joins[^]

这篇关于Oracle-我应该使用临时表还是引用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！