Printable View
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 ..
I just throw it out there as a discussion point. Or not. Maybe it's just too funky.Code:Begin
....
For X In (
Select
1
From
my_table
Where
my_field='my_value' And
RowNum= 1
)
Loop
...
End Loop;
End;
/
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.
Remember, a 3-char tab makes everything line up nicely.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;
- Chris
LOL, yes, it's true. Its not a very good solution.Quote:
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...Quote:
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 ?
Slimdave code can be formulated something like thisCode: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>
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.
Chris :Quote:
Originally posted by chrisrlong
I've posted this before, but here it is again.
Remember, a 3-char tab makes everything line up nicely.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;
- Chris
i dont understand this part
===========================
RETURN l_Exists_Flg = 1;
===========================
Irespective of l_Exists_Flg's value you r returning 1?
Abhay.
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
Make sense?Code:IF (l_Exists_Flg = 1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
- 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
If i was going to be checking for existence with a particular SQL very frequently, I would go with a more specific version than the generic function, complete with bind variables -- all those hard parses would be undesirable.