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

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 ?

---

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

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.

Quote:

---

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.

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