Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Procedure- if x exists in subset

    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
    CUR$CA.ERROR_CODE :=3;
    END IF;

    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.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    I understand the concept, just dont know how to put it inside the proc.
    For example, there are a few similar items where they had done a
    "Select count(*) into v$temp...."

    Can I bypass the v$temp somehow?
    I'm thinking of a "If select count(*)...=0 then"
    but it might not make sense.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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).

    Alan

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps IF EXISTS (SELECT ...) from your initial post might be rewritten this way:
    Code:
    SELECT DECODE(store_id, NULL, unknown_value, 3)
    INTO cur$ca.error_code
    FROM gv$store
    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.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You can also add a WHERE ROWNUM = 1 condition to limit the amount of work you are asking it to do, if you only care whether one or more rows exist rather than the actual count.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •