Re: Can't PL/SQL do the simple stuff that Transact-SQL can?

From: Patrick Flahan <flahan_at_earthlink.net>
Date: Wed, 17 Feb 1999 21:25:41 -0500
Message-ID: <7aftqa$m6v$1@holly.prod.itd.earthlink.net>

For the exists test you can do something similar. It requires additional code, but does what you want. As to not being able to return result sets, you can use pl/sql tables as parameters in and out of procedures and functions. For complex sets you can base a pl/sql table on a record.

Hope this helps

Patrick Flahan
flahan_at_earthlink.net

Try this:

CREATE FUNCTION NameInTest(pcName IN VARCHAR2)   RETURN BOOLEAN IS    bFound BOOLEAN := FALSE; --initialize to false    cTemp VARCHAR2(1);

   CURSOR curTest(pcName VARCHAR2)

     IS
      SELECT 'x'
           FROM test
         WHERE name = pcName;

  BEGIN
    OPEN curTest(pcName);
    FETCH curTest INTO cTemp;

    --Check to see if at least one row was returned     IF curTest%FOUND
    THEN

       --a row was returned so set return variable to true
       bFound := TRUE

    END IF;
    CLOSE curTest;

    RETURN bFound;
  END NameInTest;

And in your code:

IF NameInTest('Bob')
THEN
  .... --Whatever code goes here
END IF; Jonathan Tew <jtew_at_bpsinfo.com> wrote in message news:36CB6E6F.3B21AFB2_at_bpsinfo.com...
>I'm having a difficult time seeing how Oracle can be so great when we
>can't even find a way to express simple Microsoft SQL Server
>Transact-SQL statements in equivalent PL/SQL. For instance here is
>something I'm not sure PL/SQL can do. It is the if exists() begin ...
>end clause:
>
>if exists(select * from Test where name = 'Bob') begin
>end
>
>How is that accomplished in PL/SQL? I've searched the documentation and
>the only thing they talk about is using exist in the where clause and
>for collections.
>
>Another quite annoying thing is not being able to return a result set
>from a PL/SQL. I've seen nasty kludges where they return a cursor and
>then use the cursor to move through the result set.
>
>Granted I find PL/SQL has a large number of cool functions, but the
>basics have to come first. Hopefully I'm just overlooking something in
>my manuals and books.
>
>Thanks,
>Jonathan Tew
Received on Wed Feb 17 1999 - 20:25:41 CST