Can I do something like the following in PLSQL?
if (some_query) then
dbms_output.put_line('Your query returned at least 1 row.');
else
dbms_output.put_line('Your query returned no rows.');
end if;
My use case is I want to check if a value already exists in my database. If the value already exists then I will do something different than if the value doesn't exist at all.
If you a checking for existence of a record, then you can select COUNT(*) from the source table based on the key as it will always return a value you can check:
SQL> set serverout on
SQL> DECLARE
2 v_check NUMBER;
3 BEGIN
4 SELECT COUNT(*)
5 INTO v_check
6 FROM DUAL
7 WHERE DUMMY = 'X'
8 AND ROWNUM = 1;
9
10 if (v_check = 0) then
11 dbms_output.put_line('Your query returned no rows.');
12 else
13 dbms_output.put_line('Your query returned at least 1 row.');
14 end if;
15 END;
16 /
Your query returned at least 1 row.
PL/SQL procedure successfully completed.
SQL>
You'd have to do something like
BEGIN
SELECT 1
INTO l_foo
FROM dual
WHERE EXISTS (<<some query>>);
dbms_output.put_line( 'Your query returned at least 1 row' );
EXCEPTION
WHEN no_data_found
THEN
dbms_output.put_line( 'Your query returned 0 rows' );
END;
If the query isn't too expensive, it will be almost as efficient and probably a bit easier to maintain if you do something simpler
SELECT COUNT(*)
INTO l_foo
FROM (<<some query>>)
WHERE rownum = 1;
IF( l_foo = 1 )
THEN
dbms_output.put_line( 'Your query returned at least row.' );
ELSE
dbms_output.put_line( 'Your query returned 0 rows.' );
END IF;
Exactly the same, no. But there are several ways you can fake it:
If you only need to do one thing do it inside an implicit cursor that confirms whether your value exists.
for i in ( some_query ) loop
do_something;
end loop;
You can also set a value in here and use it in an if
for i in ( some_query ) loop
result := True;
end loop;
if result then
do_something;
else
do_something_else;
end if;
Or you can use an explicit cursor and catch the no_data_found error that'll be raised
declare
cursor c_blah is
select my_value
from my_table
where id = my_id
;
my_value varchar2(4000);
begin
open c_blah(my_id);
fetch c_blah into my_value;
close c_blah;
do_something;
exception when no_data_found then
do_something_else;
end;
Since your use case behaves identically whether the query returns one row or a thousand, use EXISTS:
DECLARE
l_dummy VARCHAR2(1);
BEGIN
SELECT NULL
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM <some_query>);
DBMS_OUTPUT.PUT_LINE('Your query returned at least one row.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Your query returned no rows.');
END;
