Oracle Stored Procedure If Not Exists Return

Question

I've been a strictly MS SQL consumer, but have been tasked with writing an Oracle stored procedure that compares 2 tables for missing data. I need to write something that will return a 1 if an order is missing. So far I have the following, but it's not even close to working. I've scoured the web with little results. Any help would be greatly appreciated.

CREATE OR REPLACE PROCEDURE SP_PO_CHECK


AS

BEGIN

IF NOT EXISTS ( SELECT PO FROM ORDERS, PO_LIST, WHERE Order_PO = PO; ) THEN RETURN 0; ELSE RETURN 1

END;

What is the exact test you want to perform? To return 1, must there exist at least one record in ORDERS for which there is no corresponding PO value in the PO_LIST table, us that it? — David Aldridge
– David Aldridge, Commented Apr 10, 2015 at 14:39
I'm trying to determine if there are POs in the PO_LIST table that aren't in the ORDERS table. If PO_LIST.PO doesn't exist in ORDERS, return 0, else return 1. — Brandon
– Brandon, Commented Apr 10, 2015 at 14:45

David Aldridge · Accepted Answer · 2015-04-10 14:52:40Z

1

The correct query to identify this situation is:

select count(*)
from   po_list p
where  not exists (
         select 1
         from   orders o
         where  o.order_po = p.po)
  and  rownum = 1;

This will stop the query as soon as a problem po is found, and return wither 0 or 1.

So select that into a variable and return 1 - its value:

create or replace function missing_order
as
  missing_po_found integer;
begin
  select count(*)
  into   missing_po_found
  from   po_list p
  where  not exists (
           select 1
           from   orders o
           where  o.order_po = p.po)
  and    round = 1;

  return 1 - missing_o_found;
end;

Not run, so not 100% sure there's not a typo.

answered Apr 10, 2015 at 14:52

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcin Wesel · Accepted Answer · 2015-04-10 14:22:28Z

1

Try something like this:

RETURN CASE WHEN (SELECT COUNT(*) FROM ORDERS, PO_LIST, WHERE Order_PO = PO; ) <> (SELECT COUNT(*) FROM ORDERS) THEN 0 ELSE 1 END

edited Apr 10, 2015 at 14:22

answered Apr 10, 2015 at 14:14

Marcin Wesel

1045 bronze badges

Comments

Rusty · Accepted Answer · 2015-04-10 14:36:34Z

First of all it need to be function not procedure.

Then you need to define how you going to check existence in two tables. You can use master table left join to detail table and check that it returns NOT NULL for some mandatory column (I use rowid because it is always not null). Or you can use NOT EXISTS clause.

Then you need to return result into PL_SQL variable using INTO clause And finally return variable as result of function.

CREATE OR REPLACE FUNCTION SP_PO_CHECK
RETURN NUMBER
AS
 l_total_cnt number; 
 l_match_cnt number;
BEGIN
  select count(*), -- total count of record only in po_list. I assume that this is your drive table. If not you can switch tables or use full outer join
         count(orders.rowid) -- count of records exist in both tables
    into l_total_cnt, l_match_cnt
    from po_list
    left join orders
      on Order_PO = PO;

  if l_total_cnt =  l_match_cnt then 
    return 1;
  else 
    return 0; 
  end if;

END SP_PO_CHECK;

MT0 · Accepted Answer · 2015-04-10 21:15:56Z

1

You could use MINUS:

CREATE PROCEDURE SP_PO_CHECK (
  hasSamePOs  OUT NUMBER
)
AS
  p_rowcount
BEGIN
  SELECT COUNT(1)
  INTO   p_rowcount
  FROM   (
           SELECT PO FROM PO_LIST
           MINUS
           SELECT PO FROM ORDERS
         );
  hasSamePOs := CASE WHEN p_rowcount > 0 THEN 0 ELSE 1 END;
END;
/

Or

CREATE PROCEDURE SP_PO_CHECK (
  hasSamePOs   OUT NUMBER
)
AS
BEGIN
  SELECT CASE WHEN EXISTS (
                SELECT PO FROM PO_LIST
                MINUS
                SELECT PO FROM ORDERS
              )
              THEN 0
              ELSE 1
              END
  INTO   hasSamePOs
  FROM   DUAL;
END;
/

edited Apr 10, 2015 at 21:15

answered Apr 10, 2015 at 14:56

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle Stored Procedure If Not Exists Return

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related