Fellow PL/SQL experts, this is perhaps an unanswerable question, but just maybe someone has a brilliant solution.
Today I found myself copy-pasting a very lengthy SQL statement so that I can join in another table conditionally in one circumstance, and not do so in another. Obviously this irks the programmer in me, who now has two copies of the SQL to maintain:
IF <condition>
THEN
FOR rec_data IN (SELECT <complex SQL, pages long>)
LOOP
PIPE ROW(....);
END LOOP;
ELSE
FOR rec_data IN (SELECT *
FROM (SELECT <same complex SQL, pages long> ) x,
<another table> y)
LOOP
PIPE ROW(....);
END LOOP;
END IF;
How can I avoid having two copies of the same SQL?
Options:
Use dynamic SQL and conditionally wrap the main SQL with another query block that does the additional join. CONS: dynamic SQL is harder to read and work with because of all the escaping single quotes, maintaining a list of bind variables, etc. And, it's string manipulation and that just feels like poor coding. And, I would have to define a record type with all the columns to fetch from it. More work, more redundant coding (I need to do this for dozens of functions, not just one, so it matters).
Create another pipelined function that runs the core SQL and returns rows, then in my top function query that in the two different ways. CONS: my code is now split out in an entirely different object, and now I have to not just create types for the rows but they have to be SQL types. A lot of definition just for one function's local use.
Create a global temporary table and load it with the core SQL. Select straight from it or select and join conditionally. CONS: now I have a table defined with hard-coded column definitions outside my code object, just for use by that code object. Remember, I have dozens of these functions to write, and I don't want object spaghetti.
Create a view for the core SQL. Query conditionally. CONS: separate object to maintain, plus inability to drive variables deep into the view.
Use only the longer version (the one with the conditional join) unconditionally, but use fancy CASE/DECODE within it to effectively disable the join (e.g.
DECODE(<condition>,x.join_key,NULL) = y.join_key). CONS: this is rather hacky, and may not be so easy to get the performance benefit if the additional "table" you are conditionally joining in is an expensive PL/SQL pipeline function. I'm trying to avoid having to call that function if it's output isn't needed.
The desideratum is to avoid having to copy-paste, avoid string manipulation, and avoid having to define column definitions just to fetch something needed only locally. It's like we're needing to do something like this (have SQL read from my PL/SQL cursor var... this is pseudo-code, I know you can't do this as written!)
DECLARE
CURSOR cur_data IS
SELECT <complex SQL>;
BEGIN
IF <condition>
THEN
FOR rec_data IN (SELECT *
FROM cur_data)
LOOP
PIPE ROW(....);
END LOOP;
ELSE
FOR rec_data IN (SELECT *
FROM cur_data,
other_table)
LOOP
PIPE ROW(....);
END LOOP;
END IF;
Any crazy-awesome ideas?
CodePudding user response：
The solution that has the least # of drawbacks seems to use normal SQL with a join that is conditionally turned off. In my case, it's an expensive pipelined function whose output I don't always need. So:
SELECT *
FROM (<very long main query>)
LEFT OUTER JOIN (SELECT * FROM expensivefunction(in_param => 12345)) s ON 'N' = var_bypass_function
Set var_bypass_function to Y or N in PLSQL, then execute the cursor. I've verified with dbms_output tracing that when the join condition is always false (Y=N, 1=2, etc.) it prunes the whole block and bypasses executing the function altogether. So there is no need to have my SQL in two different places.
CodePudding user response：
As there are quite a few conditions and changes of SQL depending on those conditions maybe you could create a small package to get it done. There you could define your long query (just once) and combine it with some embeded variables to handle the changing parts. Here is the basic structure that you should adjust to your environment and needs.
Here is the package:
CREATE OR REPLACE
PACKAGE REF_CURSORS AS
--
Procedure Init;
--
Function Get_Cursor(p_add_select IN VARCHAR2 := 'Select base.* ', p_add_from IN VARCHAR2 := '', p_add_where IN VARCHAR2 := '') RETURN SYS_REFCURSOR;
--
Procedure Do_It;
--
END REF_CURSORS;
... and package body:
CREATE OR REPLACE
PACKAGE BODY REF_CURSORS AS
-- variables to construct different cursors
m_sql VarChar2(4000) := '';
m_select VarChar2(2000) := '';
m_from VarChar2(255) := '';
m_where VarChar2(255) := '';
--
-- here you can declare all the variables that you need - just once - use them later to fetch into
c_ID Number(6) := 0;
c_NAME VarChar2(32) := '';
c_BORN DATE;
--
-- ---------------------------------------------------------------------------------------------------
PROCEDURE Init AS
BEGIN
-- here you can define the part that doesn't change of your pages long SQL and embed some variables for parts that does change
m_sql := m_select || ' FROM (SELECT 1 "ID", ''John'' "NAME", To_Date(''1987-NOV-27'', ''yyyy-MON-dd'') "BORN" From dual Union All
SELECT 2 "ID", ''Mary'' "NAME", To_Date(''1989-MAY-29'', ''yyyy-MON-dd'') "BORN" From dual Union All
SELECT 3 "ID", ''Mike'' "NAME", To_Date(''1991-JAN-20'', ''yyyy-MON-dd'') "BORN" From dual
) base' || ' ' || m_from || ' ' || m_where;
--
END Init;
-- -----------------------------------------------------------------------------------------------------------
FUNCTION Get_Cursor (p_add_select IN VARCHAR2 := 'Select base.* ', p_add_from IN VARCHAR2 := '', p_add_where IN VARCHAR2 := '') RETURN SYS_REFCURSOR AS
BEGIN
Declare
m_cursor SYS_REFCURSOR;
Begin
-- building cursors
m_select := p_add_select;
m_from := p_add_from;
m_where := p_add_where;
Init;
-- return built cursor
OPEN m_cursor FOR m_sql;
RETURN m_cursor;
End;
END Get_Cursor;
-- ------------------------------------------------------------------------------------------------------------
PROCEDURE Do_It AS
BEGIN
Declare
l_cursor SYS_REFCURSOR;
Begin
For i In 0..3 Loop
If i = 0 Then
l_cursor := Get_Cursor();
Else
l_cursor := Get_Cursor(p_add_where => ' WHERE ID = ' || i);
End If;
DBMS_OUTPUT.PUT_LINE(m_sql || Chr(10)); -- test print out
LOOP
FETCH l_cursor Into c_ID, c_NAME, c_BORN;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(c_ID || ' | ' || c_NAME || ' | ' || c_BORN); -- test print out
END LOOP;
CLOSE l_cursor;
End Loop;
--
l_cursor := Get_Cursor( p_add_select => 'Select added.* ',
p_add_from => 'Left Join (Select 4 "ID", ''Bob'' "NAME", To_Date(''06.07.2000'', ''dd.mm.yyyy'') "BORN" From Dual) added ON(added.ID > base.ID)',
p_add_where => ' WHERE added.ID = 4');
FETCH l_cursor Into c_ID, c_NAME, c_BORN;
DBMS_OUTPUT.PUT_LINE('-- ************************************ --'); -- test print out
DBMS_OUTPUT.PUT_LINE(m_sql); -- test print out
DBMS_OUTPUT.PUT_LINE(c_ID || ' | ' || c_NAME || ' | ' || c_BORN); -- test print out
End;
END Do_It;
END REF_CURSORS;
The functionality for testing is coded in Do_It Procedure where some loops and conditions are put together and at the end there are some additional data embeded and completely new select statement is set.
The call and resulting printouts:
SET SERVEROUTPUT ON
BEGIN
REF_CURSORS.Do_It;
END;
/
anonymous block completed
Select base.* FROM (SELECT 1 "ID", 'John' "NAME", To_Date('1987-NOV-27', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 2 "ID", 'Mary' "NAME", To_Date('1989-MAY-29', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 3 "ID", 'Mike' "NAME", To_Date('1991-JAN-20', 'yyyy-MON-dd') "BORN" From dual
) base
1 | John | 27-NOV-87
2 | Mary | 29-MAY-89
3 | Mike | 20-JAN-91
Select base.* FROM (SELECT 1 "ID", 'John' "NAME", To_Date('1987-NOV-27', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 2 "ID", 'Mary' "NAME", To_Date('1989-MAY-29', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 3 "ID", 'Mike' "NAME", To_Date('1991-JAN-20', 'yyyy-MON-dd') "BORN" From dual
) base WHERE ID = 1
1 | John | 27-NOV-87
Select base.* FROM (SELECT 1 "ID", 'John' "NAME", To_Date('1987-NOV-27', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 2 "ID", 'Mary' "NAME", To_Date('1989-MAY-29', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 3 "ID", 'Mike' "NAME", To_Date('1991-JAN-20', 'yyyy-MON-dd') "BORN" From dual
) base WHERE ID = 2
2 | Mary | 29-MAY-89
Select base.* FROM (SELECT 1 "ID", 'John' "NAME", To_Date('1987-NOV-27', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 2 "ID", 'Mary' "NAME", To_Date('1989-MAY-29', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 3 "ID", 'Mike' "NAME", To_Date('1991-JAN-20', 'yyyy-MON-dd') "BORN" From dual
) base WHERE ID = 3
3 | Mike | 20-JAN-91
-- ************************************ --
Select added.* FROM (SELECT 1 "ID", 'John' "NAME", To_Date('1987-NOV-27', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 2 "ID", 'Mary' "NAME", To_Date('1989-MAY-29', 'yyyy-MON-dd') "BORN" From dual Union All
SELECT 3 "ID", 'Mike' "NAME", To_Date('1991-JAN-20', 'yyyy-MON-dd') "BORN" From dual
) base Left Join (Select 4 "ID", 'Bob' "NAME", To_Date('06.07.2000', 'dd.mm.yyyy') "BORN" From Dual) added ON(added.ID > base.ID) WHERE added.ID = 4
4 | Bob | 06-JUL-00