CODEWITHSUNDEEP
sqloracle-database
user3444106
user3444106

Reputation: 29

Exception in CURSOR or other solution

I have a DB where selling tickets. I have such procedure, where I count all sold money from some race:

CREATE OR REPLACE PROCEDURE Total_money(depart IN RACE.DEPART_PLACE%TYPE, 
  dest IN RACE.DESTINATION_PLACE%TYPE, total OUT TICKET.PRICE%TYPE)
  IS
  CURSOR tickets
    IS SELECT t.CLIENT_ID, t.PRICE FROM TICKET t JOIN VAGON v ON t.VAGON_ID = v.VAGON_ID
        JOIN RACE r ON v.RACE_ID = r.RACE_ID
        WHERE r.DEPART_PLACE = depart AND r.DESTINATION_PLACE = dest;
  BEGIN 
  FOR t IN tickets
    LOOP
      IF t.CLIENT_ID IS NOT NULL THEN 
        total := total + t.PRICE;
      END IF;
    END LOOP;
  END;

First question: Can I place an exception into CURSOR declaration? Or what can I do, when I pass wrong depart name or destination name of the train? Or these names don't exist in DB. Then it will create an empty cursor. And return 0 money. How to control this?

Second question: After procedure declaration, I run these commands: 

 DECLARE t TICKET.PRICE%TYPE;
      t:=0;
    execute total_money('Kyiv', 'Warsaw', t)

But there is an error(PLS-00103 Encountered the symbol...) First question: How to fix it?

Upvotes: 0

Views: 51

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

A simple check is just to test that total is non-zero after the loop:

...
  END LOOP;
  IF total <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Toal zero, invalid arguments?');
  END IF;
END;

If the total could legitimately be zero (which seems unlikely here, apart from the client ID check) you could have a counter of a flag and check that:

CREATE ... IS
  found BOOLEAN := false;
  CURSOR ...
BEGIN
  total := 0;
  FOR t IN tickets
    LOOP
      found := true;
      IF t.CLIENT_ID IS NOT NULL THEN 
        total := total + t.PRICE;
      END IF;
  END LOOP;
  IF NOT found THEN
    RAISE_APPLICATION_ERROR(-20001, 'No records, invalid arguments?');
  END IF;
END;

execute is an SQL*Plus command, so I'm not sure which way you want this to work. You can use an anonymous block like this:

DECLARE
  t TICKET.PRICE%TYPE;
BEGIN
  total_money('Kyiv', 'Warsaw', t);
  -- do something with t
END;
/

Or using an SQL*Plus (or SQL Developer) variable you can do:

variable t number;
execute total_money('Kyiv', 'Warsaw', :t);
print t

I'd change it from a procedure to a function though; declare a total within it, initialise it to zero, and return that instead of having an out parameter. Then you can call it from PL/SQL or from SQL, within a simple select.

And as ElectricLlama points out, you don't need a cursor; and don't need to do this in PL/SQL at all - just use an aggregate sum(). I assume this is an exercise to learn about cursors though?

Upvotes: 1

Related Questions