Pl/sql

[nik_flash]

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.

[chrisrlong]

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

[spbraja]

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

[chrisrlong]

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

[spbraja]

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

[jmodic]

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)".

[DaPi]

(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.

[jmodic]

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...

[nik_flash]

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

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