Is there a algorithm to resolve credit rating?

I need to implement a logic in either Java OR SQL for the following.

There are credit ratings from numbers 1 to 11. And corresponding to each rating say x, I have 3 grades, effectively :x+, x and x-. Difference between each grade is termed as a step. For example, 2 and 2- is single(1) step,2+ and 2- is double(2) step, and 2- and 3+ is again a single(1) step.

My objective is to programmatically find out the number of steps between two credit rating values, until the difference in number of steps is 3.

I tried to solve this problem using conditional (if-else/case) statements, but a deeper analysis led to the conclusion that this would require about 50 odd such statements, which is not a very efficient thing to do, at least in terms of code layout.

Is there any other way to get the number of steps? Perhaps using a temporary data structure, and then using that for lookup?

Note: I can have the two credit rating values pulled into Java Code from DB and then compute number of steps. Alternatively, I need to create a database object(like procedure, function) and then have the number of steps computed that way, such that my Java code can directly consume it.

Answer

I would just assign a numeric value to each rating and have a function that subtracts one from the other.

create or replace package rating
as
    function diff(r1 varchar2, r2 varchar2)
        return integer
        deterministic
        parallel_enable;
end rating;
/

create or replace package body rating
as
    type rating_tab is table of simple_integer index by varchar2(3);
    ratings rating_tab;

    function diff(r1 varchar2, r2 varchar2)
        return integer
        deterministic
        parallel_enable
    is
        pragma udf;
    begin
        return ratings(r2) - ratings(r1);
    end diff;

begin
    ratings('1+')  := 1;
    ratings('1+')  := 2;
    ratings('1')   := 3;
    ratings('1-')  := 4;
    ratings('2+')  := 5;
    ratings('2')   := 6;
    ratings('2-')  := 7;
    ratings('3+')  := 8;
    ratings('3')   := 9;
    ratings('3-')  := 10;
    ratings('4+')  := 11;
    ratings('4')   := 12;
    ratings('4-')  := 14;
    ratings('5+')  := 15;
    ratings('5')   := 16;
    ratings('5-')  := 17;
    ratings('6+')  := 18;
    ratings('6')   := 19;
    ratings('6-')  := 20;
    ratings('7+')  := 21;
    ratings('7')   := 22;
    ratings('7-')  := 23;
    ratings('8+')  := 24;
    ratings('8')   := 25;
    ratings('8-')  := 26;
    ratings('9+')  := 27;
    ratings('9')   := 28;
    ratings('9-')  := 29;
    ratings('10+') := 30;
    ratings('10')  := 31;
    ratings('10-') := 32;
    ratings('11+') := 33;
    ratings('11')  := 34;
    ratings('11-') := 35;
end rating;
/

select rating.diff('1-', '3+') as steps from dual;

    STEPS
---------
        4

I am not sure whether a PL/SQL function is acceptable though, since you started by saying you wanted to use either Java or SQL.