-
exists
Hi,
In transact SQL u can use EXIST in this way
IF EXISTS(select * from my_table where my_field='my_value')
begin
...
end
ELSE
begin
...
end
If the select statement returns 1 or more rows the exists returns true otherwise false.
How can I do this same thing in Pl/Sql?
I thought I could do that with the exception but I am not sure.
Could u please tell me how can I do this?
Thank you very much
edli
-
It is annoying that you can't do that in PL/SQL. You could use an exception but I think this looks cleaner:
Code:
DECLARE
CNTR INTEGER;
....
BEGIN
....
SELECT COUNT(*) INTO CNTR from my_table where my_field='my_value' AND ROWNUM = 1;
IF CNTR <> 0 THEN
etc
....
-
This is a little funky, but you might get away with ..
Code:
Begin
....
For X In (
Select
1
From
my_table
Where
my_field='my_value' And
RowNum= 1
)
Loop
...
End Loop;
End;
/
I just throw it out there as a discussion point. Or not. Maybe it's just too funky.
-
Hi slimdave, I think I'd spend half a minute each time I saw that wondering what on earth it does! . . . and how do you deal with the ELSE ?
-
I've posted this before, but here it is again.
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;
Remember, a 3-char tab makes everything line up nicely.
- Chris
-
Originally posted by DaPi
Hi slimdave, I think I'd spend half a minute each time I saw that wondering what on earth it does! . . . and how do you deal with the ELSE ?
LOL, yes, it's true. Its not a very good solution.
-
Originally posted by DaPi
Hi slimdave, I think I'd spend half a minute each time I saw that wondering what on earth it does! . . . and how do you deal with the ELSE ?
Else can be delt...but offcourse its not a good idea...
Code:
TEST:ABHAY> select * from TEST_EXISTS order by 1,2;
ID NAME
-------------------- --------------------
1 Abhay
1 Bhat
1 Raghu
2 Prasad
2 Vimal
TEST:ABHAY> Begin
2 For X in ( Select 1 from TEST_EXISTS where ID=1 ) Loop
3 DBMS_OUTPUT.PUT_LINE('1');
4 End Loop;
5 For X in ( Select 2 from TEST_EXISTS where ID not in (1) ) Loop
6 DBMS_OUTPUT.PUT_LINE('2');
7 End Loop;
8 End;
9 /
1
1
1
2
2
PL/SQL procedure successfully completed.
TEST:ABHAY>
Slimdave code can be formulated something like this
Code:
Begin
....
-- If Success Part of Your logic
For X In (
Select
1
From
my_table
Where
my_field='my_value'
)
Loop
...
End Loop;
-- Else part of your logic.
For X In (
Select
1
From
my_table
Where
my_field NOT IN ('my_value')
)
Loop
...
End Loop;
End;
/
Abhay.
Last edited by abhaysk; 06-28-2003 at 02:45 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by chrisrlong
I've posted this before, but here it is again.
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;
Remember, a 3-char tab makes everything line up nicely.
- Chris
Chris :
i dont understand this part
===========================
RETURN l_Exists_Flg = 1;
===========================
Irespective of l_Exists_Flg's value you r returning 1?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
The function returns a boolean. Therefore, I am simply returning the results of the logical test (l_Exists_Flg = 1). This is simply a shortcut version of
Code:
IF (l_Exists_Flg = 1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
Make sense?
- Chris
-
BTW, my code is pretty much the fastest I have come up with. You can time it against the other proposed solutions here, but I'm fairly confident mine will come out on top. And it's completely generic.
- 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
|