How use "exists"?

[oceanju]

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?

[saurabhg29]

you can use %FOUND%,%NOTFOUND%

for systax see the pl/sql documentation

[Raminder]

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);

[prafful]

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)

[oceanju]

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

[nealh]

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.

[oceanju]

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

[nealh]

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.