exists

[edli]

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

[DaPi]

It is annoying that you can't do that in PL/SQL. You could use an exception but I think this looks cleaner:

DECLARE
  CNTR INTEGER;
....
BEGIN
....
  SELECT COUNT(*) INTO CNTR from my_table where my_field='my_value' AND ROWNUM = 1;
  IF CNTR <> 0 THEN
etc
....

[slimdave]

This is a little funky, but you might get away with ..

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.

[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 ?

[chrisrlong]

I've posted this before, but here it is again.

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

[slimdave]

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.

[abhaysk]

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...

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

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.

[abhaysk]

Originally posted by chrisrlong
I've posted this before, but here it is again.

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.

[chrisrlong]

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

IF (l_Exists_Flg = 1) THEN
   RETURN TRUE;
ELSE
   RETURN FALSE;
END IF;

Make sense?

- Chris

[chrisrlong]

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