-
proper method for testing for a value in a column on a table
Hello, I've been a programmer of many years but this is my first time with PL/SQL so please excuse the naivety of my question.
In an "INSTEAD OF" trigger on a view I'm trying to test a column in a different table for the existence of a value and then based on that result I'll insert the data in one table or another. This seems to me to be a trivial task but the only way I've found to do this is to "select into" on the table and then catch the resulting exception. This works but it's a kludge, and it really falls apart with compound decisions - especially if NO_DATA_FOUND is the true condition.
So my question is; what is the correct way to do this? I know I'm missing something fundamental here, but to me it seems it should be a simple as "if A exists in B.C then ..."
Thanks for any help you can offer,
Matt
-
DECLARE
v_count number;
BEGIN
select count(whatever) into v_count
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
-- you can add this if all you want to do is check for existence
-- if having just one is good enough, use this
-- or you can compare v_count directly to any number
and rownum =1;
IF v_count > 0 THEN
-- meaning the conditions you are looking for exist
do_the_voodoo_you_do_as_a_programmer;
update table_name set ...
insert into table_name ...
etc;
ELSE
do_whatever;
END IF;
END;
-
to check for existence, an alternative is ..
select 1 from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);
I think there was something on AskTom about it recently
-
Tom as in Tom Kyte? What the heck does he know anyway?
-
Thanks Steve,
Using the count make sense to me. I had thought that methods like "count" and "exists" could only operate on collections - not tables. I guess I didn't understand the doc's correctly, or had the syntax wrong. Anyway this is MUCH better than my approch, trying to use exceptions for logic was making me quezy
Matt
-
Originally posted by stecal
Tom as in Tom Kyte? What the heck does he know anyway?
Good point. forget i mentioned it.
-
Here it is.
Actually he suggests ...
select count(*) from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);
...since it always returns one row.
-
That works as far as output is concerned, but for PL/SQL usage, you still need a collector or variable to store the result so dependent processing can occur (the IF-THEN-ELSE based on the result stored in the variable). The output from your suggestion would have to be "selected into" for it to have any meaning in PL/SQL.
Last edited by stecal; 04-07-2003 at 04:37 PM.
-
of course, in which case ...
declare
my_number number;
begin
select count(*) nito my_number from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);
if my_number = 1
then
null;
else
blah blah blah ...
-
Here's a handy function for ya:
Code:
CREATE OR REPLACE FUNCTION Exists_sf
/*****************************************************************************
** FUNCTION: Exists_sf
**
** DESC: This function allows one to do an IF Exists_sf(),
** much like one can do in SQLServer. Since the statement is a string,
** single quotes must be 'doubled' and binds are not presently handled.
** Still a fairly useful piece of functionality.
**
** USAGE:
DECLARE
l_TABLE_NAME VARCHAR2(30);
BEGIN
l_TABLE_NAME := 'XAPLCN_DEBUG';
IF ( Exists_sf('
SELECT
1
FROM
USER_TABLES
WHERE
TABLE_NAME = '''||
l_TABLE_NAME||''''
)
)
THEN
DBMS_OUTPUT.PUT_LINE ('Does exist');
ELSE
DBMS_OUTPUT.PUT_LINE ('Does not exist');
END IF;
END;
**
** HISTORY: NAME DATE COMMENT
** -------------------- -------- ------------------------------------
** Christopher R. Long 04/2001 Initial Code
**
*****************************************************************************/
(
i_SQL IN VARCHAR2
)
RETURN
BOOLEAN
IS
-- ---------------
-- Local Variables
-- ---------------
l_SQL VARCHAR2(32767);
l_Exists_Flg CHAR(1) ;
BEGIN
l_SQL := '
SELECT
-- Exists_sf
COUNT(*)
FROM
DUAL
WHERE
EXISTS
(
'||i_SQL||'
) ';
EXECUTE IMMEDIATE
l_SQL
INTO
l_Exists_Flg ;
RETURN l_Exists_Flg = 1;
END Exists_sf;
And, as always, a 3-character tab makes it all line up nicely
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|