组合2个if块并创建单个查询 [英] Combining 2 if block and creating single query
问题描述
是否有可能合并if块并从下面创建单个块.两者都是逻辑连接的.
Is there any possibility to merge both the if block and create single block from below. Both are logically connected.
如果p_update_mode为FUll，那么我必须添加为P_entity_type传递的表的所有数据.如果它是增量的，那么我将加入条件添加选择的添加.同样，如果p_entity_type为null，那么我们必须为item和org表添加数据.
If it is p_update_mode is FUll then I have to add all the data of table passed for P_entity_type . if it is incremental then I have put join condition to add selected add. also if p_entity_type is null then we have to add data for both item and org tables.
CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
p_update_mode VARCHAR2) IS
BEGIN
IF lower(p_update_mode) = 'incremental'
THEN
INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
( -- ITEM table
SELECT 'ITEM' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORG' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
-- NEXT entity table
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE
WHEN t.creation_date > b.max_last_update_date THEN
'update'
WHEN t.creation_date < b.max_last_update_date THEN
'add'
END
FROM data_view t
JOIN batch_run_details b
ON b.entity_type = t.entity_type
WHERE upper(p_entity_type) = t.entity_type
OR p_entity_type IS NULL;
END IF;
IF UPPER(UPDATE_MODE)='FULL' then
INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
(
SELECT 'ITEM' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
'add' action
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORG' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
'add' action
FROM orgde
)
SELECT upper(entity_type), data_id, data_name, data_desc,action
FROM data_view
WHERE upper(p_entity_type) = entity_type
OR p_entity_type IS NULL;
END IF;
END update_dynamic_entity;
推荐答案
(注意:此代码未经测试，我可能在这里逗号或括号内...)
(NOTE: this code is untested and I may be off a comma here or a parenthesis there...)
这两个块似乎仅在操作列和连接上有所不同，因此您可以消除2条IF腿，然后将p_update_mode的检查移到CASE语句中，如下所示:
Those two blocks seem to only differ in the action column and a join, so you could eliminate the 2 IF legs and move the checking of p_update_mode into a CASE statement like this:
CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
p_update_mode VARCHAR2) IS
BEGIN
INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
( -- ITEM table
SELECT 'ITEM' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORG' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
-- NEXT entity table
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE lower(p_update_mode)
WHEN 'incremental' THEN
CASE
WHEN t.creation_date > b.max_last_update_date THEN
'update'
WHEN t.creation_date < b.max_last_update_date THEN
'add'
END
WHEN 'full' THEN
'add'
END action
FROM data_view t
LEFT JOIN batch_run_details b
ON b.entity_type = t.entity_type
AND lower(p_update_mode )='incremental'
WHERE (upper(p_entity_type) = t.entity_type OR p_entity_type IS NULL)
AND (lower(p_update_mode) = 'full'
OR (lower(p_update_mode) = 'incremental' AND b.entity_type IS NOT NULL)
);
END update_dynamic_entity;
在您的FULL块中的查询说，我们不应该在这种模式下加入B.因此，LEFT JOIN子句仅在处于INCREMENTAL模式时才返回行，而对于FULL模式则不产生任何行.
The query in your FULL block says we shouldn't join to B in this mode. So the LEFT JOIN clause only brings back rows when in INCREMENTAL mode, but should produce no rows for FULL mode.
这必须是LEFT联接，否则我们可能无法从data_view中获得任何与B处于FULL模式的实体不对应的行.换句话说，如果这仍然是常规的JOIN，则由于联接中的AND子句，在FULL模式下您的整体查询将获得零行.
This needs to be a LEFT join or we might not get any rows from your data_view that don't correspond to entities in B for FULL mode. In other words, if this remained a regular JOIN, your overall query would get zero rows in FULL mode because of the AND clause in the join.
最后，由于存在LEFT JOIN，因此底部WHERE子句中的AND过滤器变得必要.否则，在INCREMENTAL模式下运行时，您将在data_view中获得每行，无论B中是否存在对应的实体行.即使您在entity_id上进行联接，即使在B中没有匹配的行的情况下，左联接也将为T中的每一行带回一行，因为这是LEFT JOIN设计的目的.
Lastly, the AND filter in the WHERE clause at the bottom becomes necessary now that there is a LEFT JOIN. Without this, when running in INCREMENTAL mode, you would get EVERY row in your data_view REGARDLESS of there being a corresponding entity row in B or not. Even though you are joining on entity_id, the left join is going to bring a row back for each row in T even without a matching row in B, because that is what LEFT JOINs were designed to do.
所有这些，您将不得不决定是否值得将这两个模块融合在一起.仅仅因为您可以，并不意味着您应该.按照您的方式进行操作可能会更好，请运行一些测试.只有您知道数据量和处理频率.您还需要考虑对代码的维护，因为下一个家伙/加仑将不得不弄清楚这里发生了什么.
All that said, you will have to decide if it is worth blending these two blocks. Just because you CAN, doesn't mean you SHOULD. Your performance may be better leaving it the way you have it--run some tests. Only you know the volume of data and the frequency of processing. You also need to consider the maintenance of your code, as the next guy/gal will have to figure out what is going on here.
这篇关于组合2个if块并创建单个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
