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" <mc******@xenquery.com> wrote in message
news:v5********************@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" <wi**************@freenet.co.uk> wrote in message
news:26**************************@posting.google.c om... 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
