Re: "Sub Routine" Functionality Within Oracle

pmmgpgp_at_gmail.com wrote:
> Mark D Powell wrote:
> > On Nov 2, 9:57 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > > pmmg..._at_gmail.com wrote:
> > > > I have a basic understanding of Functions and Procedures.
> > >
> > > > I need to use one or the other as a sub routine ( I need to pass it no
> > > > value and need nothing returned ). I can do with either Function or a
> > > > Procedure but Oracle gives me "Hints" such as the following; that I
> > > > would like either not to happen or need to know how to resolve (have a
> > > > clean compile). I can use the errors to get to the areas Oracle
> > > > recognizes as problems but can't determine what I need to do to get
> > > > Oracle to think I've "used" the variable.
> > >
> > > > Thanks
> > > > Dave
> > >
> > > > Error: Hint: Value assigned to 'ret' never used in 'cr_data_retention'
> > > > Line: 842
> > > > Text: ret := step07_Apply_min_term('nada');
> > >
> > > > Error: Hint: Variable 'Trunk_Them' is declared but never used in
> > > > 'step00_trunc'
> > > > Line: 128
> > > > Text: Trunk_Them varchar2(1) :=
> > > > pkg_cr_Cust_Retention_data_BSC.Truncate_test_Tables('Y');You haven't used them, so why should Oracle think you have? And why
> > > are you trying to use a function to process data without returning some
> > > value? A procedure is what you would need to take no input, provide no
> > > output yet process data. And I don't understand why you declare
> > > variables you know you won't use.
> > >
> > > Of course all of this means nothing without seeing the actual code
> > > you've written; why have you not provided this?
> > >
> > > David Fitzjarrell- Hide quoted text -- Show quoted text -
> >
> > I agree with David.
> >
> > You need to either post at leat part of the code. If you are only
> > trying to ask general questions of the nature, "I can do A or B, when
> > should I choose A over B?" then you need to explain your questions in
> > more detail.
> >
> > HTH -- Mark D Powell --
>
> Thanks guys, I may have been too general.
> The only way I know to declare a procedure requires the inclusion
> of a parameter or signature such as the "param" in
> "PROCEDURE my_proc(parar in varchar2) as ...." .
> This is how/why it is "declared". Is there a way not to include a
> parameter or signature ? If it is essential what makes Oracle recognize
> that it has been used ? I've tried things like some useless "if"
> statement
> ( if param = param then
> param = null;
> end if;) to no avail.
>
> One thought is that I use PL/SQL developer and this my relate to the
> tool and not PL/SQL at all . Any thoughts on rhis ?
>
> Thanks dave

This works just fine:

create or replace procedure proctest
as

	cursor get_emp_data is
	select empno, ename, sal, comm
	from emp;

begin
	for e in get_emp_data loop

		if e.comm is not null and e.comm < e.sal then
			update emp
			set sal = e.sal * (1 + (e.comm/e.sal))
			where empno   = e.empno;
		elsif e.comm is not null and e.sal < e.comm then
			update emp
			set sal = e.sal * (1 + (e.sal/e.comm))
			where empno   = e.empno;
		end if;

	end loop;

	commit;

end;
/

Yes, it's embarassingly simple and the end result can be accomplished with plain old SQL, but it does illustrate how to write a procedure which does not require parameters.

David Fitzjarrell Received on Thu Nov 02 2006 - 13:30:15 CST