Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Mladen Gogala on 18 Mar 2010 12:30 On Thu, 18 Mar 2010 13:17:23 +0000, Mladen Gogala wrote: > On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote: > >> create or replace procedure aSimpleSelect (aLikeValue char(4)) as >> myrec aTableName%ROWTYPE; >> begin >> select * >> into myrec >> from aTableName >> where aColumn = aLikeValue >> and rownum < 2; >> end; >> / > > Nope. This is the right answer: > > create or replace function aSimpleSelect (a_like_value char(4)) return > ref cursor as > v_csr ref cursor; > begin > open v_csr for > select * > from aTableName > where aColumn = a_like_value; > return(v_csr); > end; > / This is the right answer that compiles: create or replace package ddf as type refcsr is ref cursor; function ssel(a_empno number) return refcsr; end ddf; / create or replace package body ddf as function ssel(a_empno number) return refcsr is v_csr refcsr; begin open v_csr for select * from emp where empno=a_empno; return(v_csr); end; end ddf; / -- http://mgogala.byethost5.com
From: ddf on 18 Mar 2010 15:01 On Mar 18, 12:30 pm, Mladen Gogala <n...(a)email.here.invalid> wrote: > On Thu, 18 Mar 2010 13:17:23 +0000, Mladen Gogala wrote: > > On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote: > > >> create or replace procedure aSimpleSelect (aLikeValue char(4)) as > >> myrec aTableName%ROWTYPE; > >> begin > >> select * > >> into myrec > >> from aTableName > >> where aColumn = aLikeValue > >> and rownum < 2; > >> end; > >> / > > > Nope. This is the right answer: > > > create or replace function aSimpleSelect (a_like_value char(4)) return > > ref cursor as > > v_csr ref cursor; > > begin > > open v_csr for > > select * > > from aTableName > > where aColumn = a_like_value; > > return(v_csr); > > end; > > / > > This is the right answer that compiles: > > create or replace package ddf > as > type refcsr is ref cursor; > function ssel(a_empno number) return refcsr; > end ddf; > / > create or replace package body ddf > as > function ssel(a_empno number) return refcsr > is > v_csr refcsr; > begin > open v_csr for > select * from emp > where empno=a_empno; > return(v_csr); > end; > end ddf; > / > > --http://mgogala.byethost5.com- Hide quoted text - > > - Show quoted text - You didn't read my entire response? David Fitzjarrell
From: Mladen Gogala on 18 Mar 2010 15:06 On Thu, 18 Mar 2010 12:01:44 -0700, ddf wrote: > You didn't read my entire response? I did. My point is that is a function, not a procedure. -- http://mgogala.byethost5.com
From: ddf on 18 Mar 2010 17:01 On Mar 18, 3:06 pm, Mladen Gogala <n...(a)email.here.invalid> wrote: > On Thu, 18 Mar 2010 12:01:44 -0700, ddf wrote: > > You didn't read my entire response? > > I did. My point is that is a function, not a procedure. > > --http://mgogala.byethost5.com And you read the orignial post where he's asking to create a procedure ... David Fitzjarrell
From: Mladen Gogala on 18 Mar 2010 17:54
On Thu, 18 Mar 2010 14:01:42 -0700, ddf wrote: >> I did. My point is that is a function, not a procedure. >> >> --http://mgogala.byethost5.com > > And you read the orignial post where he's asking to create a procedure > ... He doesn't know what does he want. That's the main problem. -- http://mgogala.freehostia.com |