DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How use "exists"?

  1. #1
    Join Date
    Sep 2001
    Posts
    99
    in sql server7, I can use this block statement like follows:
    if not exists (select * from tablename) then
    begin
    print 'no data found'
    end
    but I use this block statement in oracle:
    if exists (select * from tablename) then
    dbms_output.put_line("no data found");
    end if;
    but one error occurs, tell me the "exists" can only use in sql.
    How should I do?
    ocean

  2. #2
    Join Date
    Sep 2001
    Posts
    120

    use found ,notfound

    you can use %FOUND%,%NOTFOUND%

    for systax see the pl/sql documentation

    Saurabh Garg
    OCP 9i

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    For the above problem, oracle PL/SQL has a built-in exception called no_data_found. This is used as follows:


    Declare
    xyz table_name%rowtype
    Begin
    Select * from table_name into xyz;
    Exception
    When NO_DATA_FOUND then dbms_output.put_line("no data found");
    end;

    'EXISTS' can also be used sql statements as follows:

    Select * from table_name where exists (select col from anytable);

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Oct 2001
    Posts
    122

    Talking

    another way to do it is -->

    1)
    ...
    xyz_variable:='No';

    select 'Yes'
    into xyz_variable
    where exists (select pk_column from table where condition);

    Now check what value xyz_variable has.

    2)

    -- in declare section
    cursor c is
    select pk_column
    from table
    where condition;

    pk_column_variable declaration:=null;

    ....
    begin
    .....

    open c;
    fetch c into pk_column_variable;
    check %NOTFOUND or pk_column_variable value(should be null if no records where there)



















  5. #5
    Join Date
    Sep 2001
    Posts
    99

    an error

    prafful, I use your method. I try this block statement as follows:
    but an error occurs. I think the systax is not right.
    Please help.
    thanks
    SQL> declare
    2 isHaveData varchar2(3);
    3 begin
    4 isHaveData := 'no';
    5 select 'yes' into isHaveData where exists (select * from prod_kind where kind_id like '3900
    __');
    6 if isHaveData = 'no' then
    7 dbms_output.put_line('no data found');
    8 end if;
    9 end;
    10 /
    select 'yes' into isHaveData where exists (select * from prod_kind where kind_id like '3900__');
    *
    ERROR λÓÚµÚ 5 ÐÐ:
    ORA-06550: µÚ 5 ÐÐ, µÚ 34 ÁÐ:
    PLS-00103: ³öÏÖ·ûºÅ "WHERE"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
    .(,%from
    ocean

  6. #6
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    You would need to have a "from" in the select statement:

    select 'yes' into isHaveData
    from dual
    where exists (select * from prod_kind where kind_id like '3900__')

    But I don't think the method will work anyway. If the "exists" clause returns no rows, then query as a whole will return no rows. It will then jump to the "when no_data_found" in exactly the same way as if you had just done the "select * from prod_kind" directly.

    The best thing is to use a cursor:

    declare
    cursor cu_data is
    select * from prod_kind where kind_id like '3900__')
    rec_data cu_data%rowtype;
    begin
    open cu_data;
    fetch cu_data into rec_data;
    if cu_data%notfound then
    dbms_output.put_line('no data found');
    end if;
    while cu_data%found loop
    --process data
    fetch cu_data into rec_data;
    end loop;
    close cu_data;
    end;

    Surely this ought to be in the development forum.

  7. #7
    Join Date
    Sep 2001
    Posts
    99

    thank you ,but I want to know details

    I now know many ways can use. But I want a high efficiency way. I think use cursor will slowly.
    I think if execut a statement like "exists (select * from tablename)", dbms will only search in one index. If a index(for example primary key index) have a record data, the result will return "true". if the index is empty, the result will return "false". Dbms needn't read actual data from disk.
    But I think if use cursor, it will execute slowly. For example,
    if I declare a cursor like follows:
    cursor cu_data is
    selece * from tablename.

    I think when I open the cursor, Dbms must read actual data from disk to fill the cursor. Reading actual data from disk is very slowly. I only need whether there is any data in result. It's why I want to use prafful's way.
    Could you tell me the truth?
    Please help
    thanks
    ocean

  8. #8
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    If all you want to do is retrieve the first row to find out if there is any data in the table, a cursor will execute very quickly. It does not have to scan the entire table just to read the first few rows. It will go immediately to the first block in the table and start pulling back rows. It is only if you want to scan the entire table using this method that it will slow.


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