Pl/sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Pl/sql

  1. #1
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51

    Pl/sql

    Hi All
    I have a PL/SQL Procedure in which following statement is there
    Select sum(amount) from deal where acntid=9834;
    I want to do some processing if no data is found for the query.The mount field can be null also.How this can be done.i.e how can I check whether NO_DATA_FOUND is being returned from the query i.e not a single row exists for the condition in PL/SQL.
    regards
    nik

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Code:
    DECLARE
       L_AMOUNT   NUMBER;
       L_COUNT   NUMBER;
    BEGIN
       SELECT
          SUM(ORG_PK),
          COUNT(*)
       INTO
          L_AMOUNT,
          L_COUNT
       FROM
          ORG
       WHERE
          ORG_PK   =   0;
       IF L_COUNT = 0 THEN
          DBMS_OUTPUT.PUT_LINE('NO RECORDS');
       ELSE
          DBMS_OUTPUT.PUT_LINE('SOME RECORDS');
       END IF;
    END;
    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Oct 2002
    Posts
    12
    Chris!!

    Why going for pl/sql?

    Hope the following would work well.

    Try it.

    -----------------------------------------------------------
    Select decode(count(acntid),0,'No Record','Some Records'),
    sum(amount) from deal where acntid=9834;
    -----------------------------------------------------------

    Raja.M

    spbraja@rediffmail.com
    Last edited by spbraja; 05-03-2003 at 08:45 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I'm using PL/SQL because he said he is in PL/SQL. And since he is already in PL/SQL and wants to know whether a given statement actually hit any records, it is much easier for him to test a numeric variable with 0 or !0 in it than to test a string with 'No Record' or 'Some Records' in it.

    Make sense?

    However, if the desired outcome was to simply display the values 'No Record' or 'Some Records', then doing it in straight SQL, as you have, would obviously have been the preferred method.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Oct 2002
    Posts
    12
    Chris,

    I agree with ur justification.

    Anyway , replacing 'No record' by number 0 and 'Some record' by some number, say 1,in my query and process accordingly wouldnot affect the logic behind it.

    Also, the count(acntid) in my query would be a bit faster than ur count(*).

    ------------------------
    Raja.M
    spbraja@rediffmail.com

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by spbraja
    Also, the count(acntid) in my query would be a bit faster than ur count(*).
    No it wouldn't!

    This topic has been discussed and coverred zillion of times here and elsewhere, and the obvious conclusion is: "performance wise COUNT(*) = COUNT(NULL) = COUNT(any_constant) = COUNT(primary_key) = COUNT(rowid) = COUNT(anything_you_like)".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (Deep breath - not sure I'm up to crossing swords with Jurij . . . )

    Hi Jurij,

    The Urban Legend needs to be squashed however . . . .

    I don't think you want COUNT(NULL) - it ought to be fastest 'coz the result is always zero. If you really want to slow things down, COUNT(DISTINCT anycolumn_evenPK) will do so - so it's not quite anything_you_like.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by DaPi
    I don't think you want COUNT(NULL) - it ought to be fastest 'coz the result is always zero.
    Doh. Of course, that realy was silly of me. But, BTW, it wouldn't be any faster (nor slower) than other options, because the explain plan will still be the same, the number of I/O operations will still be the same, the CPU load will still be the same - ONLY THE RESULT WILL BE WRONG! So technicaly speaking, my answer still stands: "performance wise COUNT(*) = COUNT(NULL) = .....".

    If you really want to slow things down, COUNT(DISTINCT anycolumn_evenPK) will do so - so it's not quite anything_you_like.
    Well, yes, of course, if you substitute "anything_you_like" with something stupid like some heawy power PL/SQL function that can bring your server to the knees. In this context you can treat DISTINCT in this case as such a function (BTW, DISTINCT is a funny beast, as it can be used both as a function and as an operator). What I ment with "anything_you_like" was any_constant, any_pseudocolumn, any not_null_column, any_you_know_what...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51
    Thanx friends...
    I have another question.
    I have to read a file (coming in ISOLATIN format - .txt form) which contains czech characters & some of its content I have to upload it to our system's Database (8i)
    Tables containing VARCHAR attribute. It shows me the correct characters when I ftp it from Windows to Solaris then from there I run the scripts to Upload that file to the system , but I find it is not showing me the correct Czech characters in the database,it shows some junk values instead. What can be the problem? Same System we use for uploading files in English version. So do I need to change NLS_LANG for ISOLATIN while uploading this file or any other parameter in the database?. We r getting UTF8 format files for other version i.e English. Any idea friends?????......

    Originally posted by chrisrlong
    Code:
    DECLARE
       L_AMOUNT   NUMBER;
       L_COUNT   NUMBER;
    BEGIN
       SELECT
          SUM(ORG_PK),
          COUNT(*)
       INTO
          L_AMOUNT,
          L_COUNT
       FROM
          ORG
       WHERE
          ORG_PK   =   0;
       IF L_COUNT = 0 THEN
          DBMS_OUTPUT.PUT_LINE('NO RECORDS');
       ELSE
          DBMS_OUTPUT.PUT_LINE('SOME RECORDS');
       END IF;
    END;
    - Chris
    regards
    nik

Posting Permissions

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


Click Here to Expand Forum to Full Width