PL/SQL Procedure. avoiding SQL duplication

Question

I'm using an Oracle DB and have several large SQL queries (150 lines +). I need to run these multiples time each to perform different tests. In order to minimise code duplication I would normally use a with clause. For example if I wanted to check if the result of two queries are the same and confirm that the count was non-zero I might use

WITH STATEMENT_1 AS
(
  SELECT ...
)
, STATEMENT_2 AS
(
  SELECT ...
)
SELECT COUNT(*) FROM STATEMENT_1
UNION ALL
SELECT COUNT(*) FROM STATEMENT_2
UNION ALL
(
  SELECT COUNT(*) FROM
  (
    SELECT * FROM STATEMENT_1
    MINUS
    SELECT * FROM STATEMENT_2
  )
);

This works. However I'm now attempting to create a PL/SQL package to store and automate the ruining of these queries. I'm looking for a way to avoid SQL code duplication. I've seen answers like this

Reusing large SQL queries in stored procedures

CREATE OR REPLACE FUNCTION my_ugly_query() 
                           RETURN SYS_REFCURSOR
AS
  my_cursor_ref SYS_REFCURSOR;
BEGIN
  OPEN my_cursor_ref FOR
       SELECT -- 150+ lines of query;

  RETURN my_cursor_ref;
END;

However I don't want to use a cursor as the queries are all set based.

In summary: looking for a way to encapsulate large, repeated SQL base queries in a PL/SQL package without using a cursor.

A view is the most traditional way of encapsulating queries. Does this answer your need? — wolfrevokcats
– wolfrevokcats, Commented Mar 10, 2018 at 11:42
Why not just insert results into a table with an Id or date, then read results after function call — Ab Bennett
– Ab Bennett, Commented Mar 10, 2018 at 11:43
What code duplication? I don't get what you are trying to avoid. — Gordon Linoff
– Gordon Linoff, Commented Mar 10, 2018 at 11:43
@Gordon Linoff - For example if I had 150 line SQL query and I needed to find the COUNT but then later needed to use the same query in a minus statement, I don't want to have to repeat the full 150 line query. — Dave Turner
– Dave Turner, Commented Mar 10, 2018 at 12:18

user5683823 · Accepted Answer · 2018-03-12 22:42:41Z

Views is one part of the answer. It still doesn't allow you to "write once, use everywhere".

You want a generic "program" (query, or function, or procedure) to which you can feed two queries (names for stored queries) and which will spit out the information you need.

I show below how you can do this in a procedure. You call the procedure with two view names, and it prints a message showing you the counts. In a serious implementation you wouldn't use DBMS_OUTPUT.PUT_LINE to get your output, I am not advocating that; I am just showing how you can implement your concept with a procedure, you can modify its behavior to write to a file, or you can change it to a function that will produce a table with the counts, etc.

Note that - since the procedure uses dynamic SQL, and table/view names can't be passed in as bind variables, you must consider SQL injection (a bad thing). To combat it, I wrapped the view names within DBMS_ASSERT.SIMPLE_SQL_NAME. So, make sure your views have simple names (in the technical sense).

Generic procedure:

create or replace procedure
        comp_queries ( view_name_1 in varchar2, view_name_2 in varchar2 )
is
  cnt_1    number;
  cnt_2    number;
  cnt_diff number;
begin
  execute immediate 'select count(*) from ' || dbms_assert.simple_sql_name(view_name_1)
    into  cnt_1;
  execute immediate 'select count(*) from ' || dbms_assert.simple_sql_name(view_name_2)
    into cnt_2;
  execute immediate 'select count(*) from (
                        select * from ' || view_name_1 || ' minus 
                        select * from ' || view_name_2 || ')'
    into cnt_diff;
  dbms_output.put_line('Count from ' || upper(view_name_1) || ': ' || cnt_1);
  dbms_output.put_line('Count from ' || upper(view_name_2) || ': ' || cnt_2);
  dbms_output.put_line('Count from ' || upper(view_name_1) || ' MINUS '
                             || upper(view_name_2) || ': ' || cnt_diff);
end;
/

Compile it and confirm it works without problems.

Then test it by creating two views. Here I write two queries that should give the same output:

create view v_deptno_1 as
  select distinct deptno from emp;

create view v_deptno_2 as
  select deptno from emp group by deptno;

So, let's check they do indeed produce the same output. (Note that this is WEAKER THAN a complete formal proof that the queries are equivalent; it's just what you were doing already, checking that they are equivalent on the currently present data in the underlying tables.)

First, since we use DBMS_OUTPUT, we need to turn server output on. Then I call the procedure twice - once with the two view names we just created, and once in a way that simulates a SQL injection attack (always test that too!)

SQL> set serveroutput on

SQL> exec comp_queries('v_deptno_1', 'v_deptno_2')

Count from V_DEPTNO_1: 3
Count from V_DEPTNO_2: 3
Count from V_DEPTNO_1 MINUS V_DEPTNO_2: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

-- SIMULATE SQL INJECTION ATTACK:

SQL> exec comp_queries('v_deptno_1', 'v_deptno_2;delete * from emp')

BEGIN comp_queries('v_deptno_1', 'v_deptno_2;delete * from emp'); END;

*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 206
ORA-06512: at "INTRO.COMP_QUERIES", line 10
ORA-06512: at line 1

Elapsed: 00:00:00.01

Great, thanks for that. In the end the views ran a little slow. So I've chosen to set up some empty table structures. Then when I run mathguy's PLSQL block I call another stored procedure which populates the table, then I can run the checks, truncate the table and move on to the next query. All in all this lets me avoid code duplication and keep the run times reasonably fast. Thanks for the help.

Collectives™ on Stack Overflow

PL/SQL Procedure. avoiding SQL duplication

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related