3
\$\begingroup\$

I have a PL/SQL stored procedure which searches for a row in 3 different tables (not all columns are the same).

The logic goes as follows:

Search for the item in the first table
  If item was found
      select the details of it from the first table
  If item was not found
      search for the item in the second table
          If Item was found
              Select the details of it from the second table
          If item was not found
              Select the item from the third table (returning blank if it's not found here neither)

Here's the actual code (actual symbol names modified for security):

PROCEDURE GETREQUEST(REQUESTID IN NUMBER, request OUT pkg_request.refcur)
 IS
requestFound NUMBER := 0;
BEGIN

            -- Search for the request ID in the first table
            SELECT COUNT(REQ_ID) INTO requestFound FROM FIRSTTABLE
            WHERE FIRSTTABLE.REQ_ID = REQUESTID
            AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A');

            IF(REQUESTFOUND > 0) THEN
                    -- Select the request details
                    OPEN REQUEST FOR
                    SELECT REQ_ID, REQ_TYPE_STATUS FROM FIRSTTABLE
                    WHERE FIRSTTABLE.REQ_ID = REQUESTID
                    AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A');
            ELSE
                    -- Search for the request from the second table
                    SELECT COUNT(REQ_ID) INTO REQUESTFOUND FROM SECONDTABLE
                    WHERE SECONDTABLE.REQ_ID = REQUESTID;

                    IF(REQUESTFOUND > 0) THEN
                              -- Select the request details from second table
                              OPEN REQUEST FOR
                              SELECT REQ_TYPE, '-' AS REQ_TYPE_STATUS FROM SECONDTABLE
                              WHERE SECONDTABLE.REQ_ID = REQUESTID;
                      ELSE
                              -- Get the request from third table (will return as blank if nothing found)
                              OPEN REQUEST FOR
                              SELECT REQ_TYPE, '-' AS REQ_TYPE_STATUS  FROM THIRDTABLE
                              WHERE THIRDTABLE.REQ_ID = REQUESTID;
                      END IF;
            END IF;
END GET_REQUEST

My issues with this are as follows:

  • It's not very concise, with a lot of nesting.
  • It's not very efficient, since it needs to perform a count, then if it exists it selects the details. That's two operations. How can I modify it to have a single operation for each search?

Also, the REQ_TYPE_STATUS column doesn't exist in the second and third table, so as you can see I have just assigned the value '-' to the returned result, indicating a blank value. I'm not really sure if there's a better or more standard way of doing this.

\$\endgroup\$
4
\$\begingroup\$

I'm afraid you current solution is a prime example of inappropriate use of PL/SQL when plain SQL provides much more elegant solution:

create table t1 (
  id number,
  status varchar2(10)
);

create table t2 (
  id number
);

create table t3 (
  id number
);

insert into t1 values(1,'A');
insert into t2 values(2);
insert into t3 values(3);
insert into t1 values(4,'A');
insert into t2 values(4);
insert into t3 values(4);

select source, priority, id, status from (
  select 't1' as source, 1 as priority, id, status from t1 where status in ('A', 'D')
  union
  select 't2' as source, 2, id, null from t2
  union
  select 't3' as source, 3, id, null from t3
)
where id = 1
order by priority
;

The source is just for demonstration and priority is to guarantee the correct ordering of the tables. The priority is not needed if you have other rules ensuring the id is found only from one table. If the id can be found from different tables just add a wrapper rownum = 1-query (the details left for exercise) to pick the first one only. Now just wrap that inside your PL/SQL as you see appropriate.

When some information is not available in all tables it's up to you what is the value indicating missing value. It can be e.g. null or - or -1 depending on the context. However I'd recommend null unless there is other factors that rule that out.

Hope this helps !

\$\endgroup\$
1
  • \$\begingroup\$ Brilliant work, this is a much shorter and simpler solution! \$\endgroup\$ – Ciaran Gallagher Mar 24 '14 at 11:31
4
\$\begingroup\$

I am not sure there is a way to reduce the number of if-statements and nesting. What is possible though is to improve the performance of the conditions you have that check those conditions.

The count(*) is inefficient. Consider adding a 'ROWNUM = 1' condition to your where clause in a way that makes the following conditions possible:

        SELECT COUNT(REQ_ID) INTO requestFound
        FROM FIRSTTABLE
        WHERE FIRSTTABLE.REQ_ID = REQUESTID
          AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A')
          AND ROWNUM = 1;

This will be optimized by Oracle to exit the condition on the first matching result, ending up with a requestFound value that will be either 0, or 1. A value of 1 means there is some data, and does not indicate how much data.

Reading up on ROWNUM is useful. Ask Tom!

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.