Home » SQL & PL/SQL » SQL & PL/SQL » How to implement this logic in a stored procedure (Oracle 9.2.0.5)
How to implement this logic in a stored procedure [message #440161] Thu, 21 January 2010 07:33 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
create or replace procedure t109(
...
in_param1 in varchar2 DEFAULT 'day', --can be hour
in_param3_val number,
...)




default is for the parameter in_param1 is 'day'
if its 'day' then it should be 1,2,3,4..7 not more than 7 .If its more than 7 then it should be 1
if its 'hour' then it should be 1,2,3,4,4,5,6 not more than 6 .If its more than 6 then it should be 1
if its not 'HOUR' OR 'DAY' then default it to DAY
How I would implement this?
Re: How to implement this logic in a stored procedure [message #440162 is a reply to message #440161] Thu, 21 January 2010 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Its_me_ved wrote on Thu, 21 January 2010 14:33
if its 'day' then it should be 1,2,3,4..7 not more than 7 .If its more than 7 then it should be 1

What if it is less than 1?


IF could do the job, I guess.
Re: How to implement this logic in a stored procedure [message #440163 is a reply to message #440161] Thu, 21 January 2010 07:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
With IF ... THEN ... ELSE statements possibly.
Re: How to implement this logic in a stored procedure [message #440165 is a reply to message #440161] Thu, 21 January 2010 07:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Just put all of your logic in your procedure before you do anything, after the BEGIN of course.

As Littlefoot says, just a bunsh of simple IF statements would suffice.

Do you plan on returning error messages if the parameter value do not conform?
Re: How to implement this logic in a stored procedure [message #440166 is a reply to message #440165] Thu, 21 January 2010 07:45 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thank you! But I can not assign value to a in parameter directly. For that I need to use another variable (?) .Is there a way?
I used a variable like this
 v_param1:=in_param1;
 if v_param1 <>'DAY' or v_param1<>'HOUR'
  then  v_param1:= 'DAY';
 end if;

Is there any other approach?

[Updated on: Thu, 21 January 2010 07:58]

Report message to a moderator

Re: How to implement this logic in a stored procedure [message #440232 is a reply to message #440166] Thu, 21 January 2010 13:11 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I understood it:
declare
  l_par_1 varchar2(10) := in_param_1;
  l_par_3 number       := in_param_3;
begin
  if in_param_1 = 'day' then
     if in_param_3 not in (1, 2, 3, 4, 5, 6, 7) then
        <raise a "day" error>
     elsif in_param_3 > 7 then
        l_par_1 := 1;
     end if;
  elsif in_param_1 = 'hour' then
     if in_param_3 not in (1, 2, 3, 4, 5, 6) then
        <raise an "hour" error>
     elsif in_param_3 > 6 then
        l_par_3 := 1;
     end if;
  end if;

  <now use l_par_1 and l_par_3>
Previous Topic: problem with my insert query
Next Topic: Sorting strings in Order
Goto Forum:
  


Current Time: Sun Dec 04 12:38:47 CST 2016

Total time taken to generate the page: 0.11959 seconds