CODEWITHSUNDEEP
oracle-databaseif-statementprocedurenot-exists
Brandon
Brandon

Reputation: 11

Oracle Stored Procedure If Not Exists Return

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;

Upvotes: 0

Views: 8562

Answers (4)

MT0
MT0

Reputation: 168081

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;
/

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 1

Rusty
Rusty

Reputation: 2138

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;

Upvotes: 1

Marcin Wesel
Marcin Wesel

Reputation: 104

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

Upvotes: 1

Related Questions