Pl/SQL Function syntax

Question

I can't wrap my head around functions to do with pl/SQL i have code which is supposed to check room capacity but I think I've declared a variable wrong somewhere. Honestly can't see what the problem is.

CREATE OR REPLACE Function RoomCapacity
( name_in IN varchar2 )
RETURN varchar2
IS
R_value number(6);
RLevel varchar2(20);

cursor c1 is
  SELECT room_capacity
  FROM ROOMS
  WHERE room_id = name_in;

BEGIN

open c1;
fetch c1 into R_value;
close c1;

IF R_value <= 10 THEN
  RLevel := 'Low capacity';

ELSIF R_value > 10 and R_value <= 18 THEN
  RLevel := 'Avg capacity';

ELSIF R_value > 18 and R_value <= 30 THEN
  RLevel := 'Moderate capacity room';

ELSE
  RLevel := 'High capacity room';

END IF;

RETURN RLevel;

END;

ROOM_ID
BUILDING_PREFIX
ROOM_FLOOR
ROOM_NUMBER
ROOM_TYPE
ROOM_CAPACITY
ROOM_EQUIPMENT

The table ROOMS; is listed above.

So.... what error are you getting? Because the code looks reasonable - assuming that the client is passing in a name_in that correctly matches to a room_id. Oh, and if the room has a room_capactiy between 19 and 30 then your text string will blow up your varchar2(20) RLevel parameter ("Moderate capacity room" has 23 characters!). — Michael Broughton
– Michael Broughton, Commented Nov 30, 2015 at 18:50
Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception or error, post the line it occurred on and the details. Please edit these details in or we may not be able to help. — Mr. Llama
– Mr. Llama, Commented Nov 30, 2015 at 19:28

krokodilko · Accepted Answer · 2015-11-30 19:24:50Z

3

I can't see any error.
Moreover, I tried your code and it worked like a charm,
see this fiddle: http://sqlfiddle.com/#!4/0ae48/1

However, you can simplify your function using CASE expressions, just:

   RETURN
   CASE WHEN R_value <= 10 THEN 'Low capacity'
        WHEN R_value > 10 AND R_value <= 18 THEN 'Avg capacity'
        WHEN R_value > 18 AND R_value <= 30 THEN  'Moderate capacity room'
        ELSE 'High capacity room'
   END;

edited Nov 30, 2015 at 19:24

answered Nov 30, 2015 at 19:01

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Broughton Over a year ago

Your test cases didn't include the ones that blow up trying to put 'Moderate capacity room' into RLevel varchar2(20);...... ;-)

krokodilko Over a year ago

@Michael, thank you for pointing that - please post this as an answer, because this is a real reason of the issue ('Moderate capacity room' does not fit in varchar(20)`)

Michael Broughton Over a year ago

Done - glad to have helped :)

Michael Broughton · Accepted Answer · 2015-11-30 20:03:08Z

1

The code looks good with one exception, your variable defintion of

RLevel varchar2(20);

and:

ELSIF R_value > 18 and R_value <= 30 THEN
  RLevel := 'Moderate capacity room';

That string is 23 characters long and will blow up the rLevel variable if you input a room with a capacity in that range.

answered Nov 30, 2015 at 20:03

Michael Broughton

4,05516 silver badges12 bronze badges

Collectives™ on Stack Overflow

Pl/SQL Function syntax

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related