Re: Help : Convert interbase stored proc

study out the PL/SQL Users' Guide for syntax details

the differences i notice are:

the 'cursor for loop' syntax is difference (requires a record name, parens around the SQL, LOOP .. END LOOP keywords

the suspend keyword looks like the equivalent of EXIT, RETURN or RAISE_APPLICATION_ERROR procedures don't have return values, in PL/SQL only functions have return values

however, functions can only have a single return value (with the keyword RETURN, not RETURNS) -- multiple 'output' values can be passed via output parameters (parameters are input by default, can be declared as IN, OUT or IN OUT) you must explicitly set return values for output parameter values -- there is not implied relationship between parameters and procedure DML

PL/SQL has a declare section in which variables are declared -- declare keyword is used once, variable keyword is not used in PL/SQL

'if' structures are terminated with 'end if'

(i assume your code omitted the logic that sets LastVal)

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048


"William Buchanan" <william.buchanan_at_freenet.co.uk> wrote in message
news:26d39d38.0311030426.768726eb_at_posting.google.com...


> Hi

>

> I have the following stored proc in interbase (which might contain


> errors - i'm doing it off the top of my head), which I would like to

> convert into oracle. Can you help? What I want back is a dataset of

> all the records where Ch_Val is different to the previous value.

>

> Thanks for any help.

>

> create procedure GetChanges(StartDate timestamp, EndDate timestamp)

> returns(SettDate timestamp, Ch_ID integer, Ch_Val integer)

> as

> declare variable lastVal integer;

> begin

>   lastVal = -1;

>   for select * from ChData

>       where SettDate between :StartDate and :EndDate

>       order by SetDate

>   do

>   begin

>     if Ch_Val <> lastVal then

>       suspend;

>   end;

> end