I have a very lengthy procedure that I inherited. I am used to VB, Access, Fox for the last 20 years and am quickly picking up PL/SQL
The procedure goes through a number of transaction records.
The first thing it does is test various aspects of the transaction and if they do not qualify it gives it an error code.
Then it totals all the non-error transactions into various tables with specific calculations.
I would like to add something whereby it checks the current record's STORE_ID against a table ( EXCLUDE_ID) 's STORE_ID
So to keep it fast, I figured I would load a cursor GV$STORE from the table and look for the id there. I didn't find any quick function to do a lookup, so
I tried to use the EXIST keyword:
IF EXISTS(SELECT STORE_ID FROM GV$STORE WHERE CUR$CA.STORE_ID=GV$STORE.STORE_ID) THEN
needless to say, it doesn't want me using the exists keyword there.
So, I have two questions -
What is the proper way to handle this and...
is there a better, quicker way?
Without a complete re-write, I do have to handel each record as it comes through.
You can do it by just doing the select and catching the NO_DATA_FOUND exception. Alternatively do a select count(*)... and if it returns 0 then you havent found any matches.
However if you can avoid cursors completely and use straight sql you will find tha performance is usually an order of magnitude better. Remember you can do a hell of a lot using sql (especially with case and decode) and using set operations instead of cursors and loops.
If you want to do it using loops you have to do select into... and then test the variable.
But like I said before its is much much faster cutting out loops and using straight sql. If it is very complicated you could write to temp table. For example if you write your result set to a temp table. Then to elimanate any record just delete from temptable where id in (select store_id from gv$store).
Perhaps IF EXISTS (SELECT ...) from your initial post might be rewritten this way:
SELECT DECODE(store_id, NULL, unknown_value, 3)
WHERE cur$ca.store_id = gv$store.store_id;
"unknown_value" should be something you want to enter into "cur$ca.error_code" if "store_id" is null. It can be NULL as well - doesn't have to have a known value.
However, such a query might suffer from a disease called TOO-MANY-ROWS which will be raised if WHERE clause isn't restrictive enough to return only one value. SELECT DISTINCT might help, but not always. In that case, you'll need an EXCEPTION handler which should solve such a problem.