exists
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: exists

  1. #1
    Join Date
    Jun 2003
    Posts
    47

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
    ....

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 ?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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 03: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"

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width