Re: Help : Convert interbase stored proc

From: mcstock <mcstockx_at_xenquery.com>
Date: Wed, 5 Nov 2003 07:51:39 -0500
Message-ID: <D5OdnVS4q5gXbzWi4p2dnA_at_comcast.com>

i don't know if this would simplify the function for you, but you could try a structure like this:

create or replace function get_emp(

   ip_empno in emp.empno%type
   )
return emp%rowtype
is

   returnRow emp%rowtype;
begin

   for r1 in (

      select *
      from   emp
      where  empno = ip_empno
      )
   loop
     if 1 < 2 -- some condition that makes theloop necessary
     then
        returnRow := r1;
        exit;
     end if;

   end loop;
   return returnRow;
end;
/

if you don't want to return the entire row, then you do need to create a type as you indicated, or put the code in a package and declare a cursor in the package to use with '%ROWTYPE':

create or replace package emp_stuff
is

    cursor crsr_empNameSal(cp_empno in emp.empno%type)     is

        select ename, sal
        from emp
        where empno = cp_empno
        order by ename;

    function get_emp (
        ip_empno in emp.empno%type
        )
        return crsr_empNameSal%rowtype;

end emp_stuff;
/
create or replace package body emp_stuff is

    function get_emp (

        ip_empno in emp.empno%type
        )
        return crsr_empNameSal%rowtype
        is
            returnRow crsr_empNameSal%rowtype;
        begin
            for r1 in crsr_empNameSal(ip_empno)
            loop
                if 1 < 2 -- some condition that makes this loop necessary
                then
                    returnRow := r1;
                    exit;
                end if;
            end loop;
            return returnRow;
        end get_emp;

end emp_stuff;
/

also, did you see if you can avoid the loop altogether by using a SQL statement with subqueries? (may or may not be worth the effort, but always a good idea to try to avoid explicit looping)

• mcs

"mcstock" <mcstockx_at_xenquery.com> wrote in message news:v5WdnYDGaq3xmzWiRVn-gw_at_comcast.com...
> 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
>
>
Received on Wed Nov 05 2003 - 13:51:39 CET