Avatar of PingoLU

PingoLU's solution

to Roman Numerals in the PL/SQL Track

Published at Nov 22 2018 · 0 comments
Instructions
Test suite
Solution

Write a function to convert from normal numbers to Roman Numerals.

The Romans were a clever bunch. They conquered most of Europe and ruled it for hundreds of years. They invented concrete and straight roads and even bikinis. One thing they never discovered though was the number zero. This made writing and dating extensive histories of their exploits slightly more challenging, but the system of numbers they came up with is still in use today. For example the BBC uses Roman numerals to date their programmes.

The Romans wrote numbers using letters - I, V, X, L, C, D, M. (notice these letters have lots of straight lines and are hence easy to hack into stone tablets).

 1  => I
10  => X
 7  => VII

There is no need to be able to convert numbers larger than about 3000. (The Romans themselves didn't tend to go any higher)

Wikipedia says: Modern Roman numerals ... are written by expressing each digit separately starting with the left most digit and skipping any digit with a value of zero.

To see this in practice, consider the example of 1990.

In Roman numerals 1990 is MCMXC:

1000=M 900=CM 90=XC

2008 is written as MMVIII:

2000=MM 8=VIII

See also: http://www.novaroma.org/via_romana/numbers.html

Setup

Go through the setup instructions for PL/SQL to get ready to code:

http://exercism.io/languages/plsql

Running the Tests

Execute the tests by calling the run method in the respective ut_<exercise># package. The necessary code should be contained at the end of the test package. As an example, the test for the hamming exercise would be run using

begin
  ut_hamming#.run;
end;
/

Source

The Roman Numeral Kata http://codingdojo.org/cgi-bin/index.pl?KataRomanNumerals

Submitting Incomplete Solutions

It's possible to submit an incomplete solution so you can see how others have completed the exercise.

ut_numeral#.plsql

create or replace package ut_numeral#
is
  procedure run;
end ut_numeral#;
/
 
create or replace package body ut_numeral#
is
  procedure test (
    i_descn                                       varchar2
   ,i_exp                                         varchar2
   ,i_act                                         varchar2
  )
  is
  begin
    if i_exp = i_act then
      dbms_output.put_line('SUCCESS: ' || i_descn);
    else
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl('' || i_exp, 'null') || ', but received ' || nvl('' || i_act, 'null'));
    end if;
  end test;
 
  procedure run
  is
  begin
    test(i_descn => 'test_1',    i_exp => 'I',      i_act => numeral#.to_roman(1   ));
    test(i_descn => 'test_2',    i_exp => 'II',     i_act => numeral#.to_roman(2   ));
    test(i_descn => 'test_3',    i_exp => 'III',    i_act => numeral#.to_roman(3   ));
    test(i_descn => 'test_4',    i_exp => 'IV',     i_act => numeral#.to_roman(4   ));
    test(i_descn => 'test_5',    i_exp => 'V',      i_act => numeral#.to_roman(5   ));
    test(i_descn => 'test_6',    i_exp => 'VI',     i_act => numeral#.to_roman(6   ));
    test(i_descn => 'test_9',    i_exp => 'IX',     i_act => numeral#.to_roman(9   ));
    test(i_descn => 'test_27',   i_exp => 'XXVII',  i_act => numeral#.to_roman(27  ));
    test(i_descn => 'test_48',   i_exp => 'XLVIII', i_act => numeral#.to_roman(48  ));
    test(i_descn => 'test_59',   i_exp => 'LIX',    i_act => numeral#.to_roman(59  ));
    test(i_descn => 'test_93',   i_exp => 'XCIII',  i_act => numeral#.to_roman(93  ));
    test(i_descn => 'test_141',  i_exp => 'CXLI',   i_act => numeral#.to_roman(141 ));
    test(i_descn => 'test_163',  i_exp => 'CLXIII', i_act => numeral#.to_roman(163 ));
    test(i_descn => 'test_402',  i_exp => 'CDII',   i_act => numeral#.to_roman(402 ));
    test(i_descn => 'test_575',  i_exp => 'DLXXV',  i_act => numeral#.to_roman(575 ));
    test(i_descn => 'test_911',  i_exp => 'CMXI',   i_act => numeral#.to_roman(911 ));
    test(i_descn => 'test_1024', i_exp => 'MXXIV',  i_act => numeral#.to_roman(1024));
    test(i_descn => 'test_3000', i_exp => 'MMM',    i_act => numeral#.to_roman(3000));
  end run;
end ut_numeral#;
/
 
begin
  ut_numeral#.run;
end;
/
CREATE OR REPLACE PACKAGE numeral# AS
  ----------------------------------------
  -- Declaration:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- TO_ROMAN:
  --   Converts a decimal number into
  --   a roman numeral.
  --
  --   p_number: the number to convert
  ----------------------------------------
  FUNCTION to_roman (
    p_number IN NUMBER)
  RETURN VARCHAR2;

  ----------------------------------------
  -- TO_DECIMAL:
  --   Converts a roman numeral into
  --   a decimal number.
  --
  --   p_numeral: the numeral to convert
  ----------------------------------------
  FUNCTION to_decimal (
    p_numeral IN VARCHAR2)
  RETURN NUMBER;
END numeral#;
/

CREATE OR REPLACE PACKAGE BODY numeral# AS
  ----------------------------------------
  -- Implementation:
  --   Public Functions.
  ----------------------------------------
  ----------------------------------------
  -- TO_ROMAN:
  --   Converts a decimal number into
  --   a roman numeral.
  --
  --   p_number: the number to convert
  ----------------------------------------
  FUNCTION to_roman (
    p_number IN NUMBER)
  RETURN VARCHAR2 IS
    v_number VARCHAR2(4 CHAR);
    v_result VARCHAR2(9 CHAR);
  BEGIN
    IF (p_number IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20001, 'Input Parameter is NULL.');
    ELSIF (p_number <= 0) THEN
      RAISE_APPLICATION_ERROR (-20003, 'Input Parameter is too small.');
    ELSIF (p_number > 4000) THEN
      RAISE_APPLICATION_ERROR (-20004, 'Input Parameter is too huge.');
    END IF;

    v_number := CAST(p_number AS VARCHAR2);

    EXECUTE IMMEDIATE (
      'WITH DecimalToRoman (GivenNumber) AS
         (SELECT :s AS GivenNumber FROM dual)
         , RomanNumerals (GivenNumber
                          , RomanNumeral) AS
           ((SELECT ''3000'',  ''MMM'' FROM dual)
            UNION ALL
            (SELECT ''2000'',   ''MM'' FROM dual)
            UNION ALL
            (SELECT ''1000'',    ''M'' FROM dual)
            UNION ALL
            (SELECT  ''900'',   ''CM'' FROM dual)
            UNION ALL
            (SELECT  ''800'', ''DCCC'' FROM dual)
            UNION ALL
            (SELECT  ''700'',  ''DCC'' FROM dual)
            UNION ALL
            (SELECT  ''600'',   ''DC'' FROM dual)
            UNION ALL
            (SELECT  ''500'',    ''D'' FROM dual)
            UNION ALL
            (SELECT  ''400'',   ''CD'' FROM dual)
            UNION ALL
            (SELECT  ''300'',  ''CCC'' FROM dual)
            UNION ALL
            (SELECT  ''200'',   ''CC'' FROM dual)
            UNION ALL
            (SELECT  ''100'',    ''C'' FROM dual)
            UNION ALL
            (SELECT   ''90'',   ''XC'' FROM dual)
            UNION ALL
            (SELECT   ''80'', ''LXXX'' FROM dual)
            UNION ALL
            (SELECT   ''70'',  ''LXX'' FROM dual)
            UNION ALL
            (SELECT   ''60'',   ''LX'' FROM dual)
            UNION ALL
            (SELECT   ''50'',    ''L'' FROM dual)
            UNION ALL
            (SELECT   ''40'',   ''XL'' FROM dual)
            UNION ALL
            (SELECT   ''30'',  ''XXX'' FROM dual)
            UNION ALL
            (SELECT   ''20'',   ''XX'' FROM dual)
            UNION ALL
            (SELECT   ''10'',    ''X'' FROM dual)
            UNION ALL
            (SELECT    ''9'',   ''IX'' FROM dual)
            UNION ALL
            (SELECT    ''8'', ''VIII'' FROM dual)
            UNION ALL
            (SELECT    ''7'',  ''VII'' FROM dual)
            UNION ALL
            (SELECT    ''6'',   ''VI'' FROM dual)
            UNION ALL
            (SELECT    ''5'',    ''V'' FROM dual)
            UNION ALL
            (SELECT    ''4'',   ''IV'' FROM dual)
            UNION ALL
            (SELECT    ''3'',  ''III'' FROM dual)
            UNION ALL
            (SELECT    ''2'',   ''II'' FROM dual)
            UNION ALL
            (SELECT    ''1'',    ''I'' FROM dual))
          , ConvertDecimalToRoman (DecimalValue
                                   , RemainingDecimal
                                   , CurrentRomanNumeral
                                   , TotalRomanNumeral) AS
            (-- Anchor member.
             (SELECT ''-''                          AS DecimalValue
                     , DecimalToRoman.GivenNumber AS RemainingDecimal
                     , ''''                         AS CurrentRomanNumeral
                     , ''''                         AS TotalRomanNumeral
               FROM DecimalToRoman)
              UNION ALL
             (-- Recursive member.
              SELECT rpad(substr(ConvertDecimalToRoman.RemainingDecimal
                                 , 1
                                 , 1)
                          , LENGTH(ConvertDecimalToRoman.RemainingDecimal)
                          , ''0'')                                                      AS DecimalValue
                     , substr(ConvertDecimalToRoman.RemainingDecimal
                              , 2
                              , LENGTH(ConvertDecimalToRoman.RemainingDecimal) - 1)   AS RemainingDecimal
                     , RomanNumerals.RomanNumeral                                     AS CurrentRomanNumeral
                     , ConvertDecimalToRoman.TotalRomanNumeral || CurrentRomanNumeral AS TotalRomanNumeral
              FROM ConvertDecimalToRoman
                LEFT OUTER JOIN RomanNumerals
                  ON (rpad(substr(ConvertDecimalToRoman.RemainingDecimal
                                  , 1
                                  , 1)
                           , LENGTH(ConvertDecimalToRoman.RemainingDecimal)
                           , ''0'') = RomanNumerals.GivenNumber)))
       -- Replicate NOCYCLE clause.
       CYCLE RemainingDecimal SET cycle TO 1 DEFAULT 0
       -- Get the result
       SELECT TotalRomanNumeral
       FROM ConvertDecimalToRoman
       WHERE (DecimalValue IS NULL)')
    INTO v_result
    USING v_number;
    
    RETURN (v_result);
  END to_roman;

  ----------------------------------------
  -- TO_DECIMAL:
  --   Converts a roman numeral into
  --   a decimal number.
  --
  --   p_numeral: the numeral to convert
  ----------------------------------------
  FUNCTION to_decimal (
    p_numeral IN VARCHAR2)
  RETURN NUMBER IS
    v_numeral VARCHAR2(9 CHAR);
    
    v_result  NUMBER(11, 0);
  BEGIN
    v_numeral := UPPER(TRIM(p_numeral));
    
    IF (p_numeral IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20001, 'Input Parameter is NULL.');
    ELSIF (v_numeral IS NULL) THEN
      RAISE_APPLICATION_ERROR (-20002, 'Input Parameter is empty.');
    END IF;

    IF (NOT (REGEXP_LIKE(v_numeral, '^M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})$'))) THEN
      RAISE_APPLICATION_ERROR (-20002, 'Input Parameter is not a valid Roman Numeral.');
    END IF;

    ----------------------------------------------------------------------------
    -- The algorithm to convert roman numerals to decimal numbers
    -- is very straightforward:
    --   For each character, starting from the lowest roman numeral
    --   single value: 
    --     Convert this numeral value into the representing decimal value
    --     If this decimal value is greater than or equal to the previous one
    --     then
    --       add this decimal value to the total
    --     else
    --       subtract this decimal value from the total
    ----------------------------------------------------------------------------
    EXECUTE IMMEDIATE (
      'WITH RomanToDecimal (GivenNumeral) AS
         (SELECT :s AS GivenNumeral FROM dual)
         , DecimalValues (GivenNumeral
                          , DecimalValue) AS
           ((SELECT ''M'', 1000 FROM dual)
            UNION ALL
            (SELECT ''D'', 500 FROM dual)
            UNION ALL
            (SELECT ''C'', 100 FROM dual)
            UNION ALL
            (SELECT ''L'', 50 FROM dual)
            UNION ALL
            (SELECT ''X'', 10 FROM dual)
            UNION ALL
            (SELECT ''V'', 5 FROM dual)
            UNION ALL
            (SELECT ''I'', 1 FROM dual))
          , ConvertRomanToDecimal (Letter
                                   , RemainingNumeral
                                   , CurrentDecimalValue
                                   , LastDecimalValue
                                   , TotalDecimalValue) AS
            (-- Anchor member
             (SELECT ''-''                      AS Letter
                     , RomanToDecimal.Numeral AS RemainingNumeral
                     , 0                      AS CurrentDecimalValue
                     , 0                      AS LastDecimalValue
                     , 0                      AS TotalDecimalValue
               FROM RomanToDecimal)
              UNION ALL
             (-- Recursive member
              SELECT -- Get the current roman numeral, (one character)
                     substr(ConvertRomanToDecimal.RemainingNumeral
                            , LENGTH(ConvertRomanToDecimal.RemainingNumeral)
                            , 1)                                                    AS Letter
                     -- Get the remaining roman numeral
                     , substr(ConvertRomanToDecimal.RemainingNumeral
                              , 1
                              , LENGTH(ConvertRomanToDecimal.RemainingNumeral) - 1) AS RemainingNumeral
                     -- Get the current decimal value
                     , DecimalValues.DecimalValue                                   AS CurrentDecimalValue
                     -- Get the previous decimal value
                     , LAST_VALUE(CurrentDecimalValue)
                         OVER (ORDER BY RemainingNumeral DESC
                               RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   AS LastDecimalValue
                     -- If thecurrent decimal value is greater than or equal
                     -- to the previous one then
                     --   add the current decimal value to the total
                     -- else
                     --   subtract the current decimal value from the total
                     , (CASE 
                          WHEN ConvertRomanToDecimal.CurrentDecimalValue >= ConvertRomanToDecimal.LastDecimalValue
                            THEN ConvertRomanToDecimal.TotalDecimalValue + CurrentDecimalValue
                          ELSE ConvertRomanToDecimal.TotalDecimalValue - CurrentDecimalValue
                        END)                                                        AS TotalDecimalValue
              FROM ConvertRomanToDecimal
                LEFT OUTER JOIN DecimalValues
                  ON (substr(ConvertRomanToDecimal.RemainingNumeral
                             , LENGTH(ConvertRomanToDecimal.RemainingNumeral)
                             , 1) = DecimalValues.Numeral)))
       -- Replicate NOCYCLE clause
       CYCLE RemainingNumeral SET cycle TO 1 DEFAULT 0
       -- Get the result
       SELECT TotalDecimalValue
       FROM ConvertRomanToDecimal
       WHERE (Letter IS NULL)')
    INTO v_result
    USING v_numeral;

    RETURN (v_result);
  END to_decimal;
END numeral#;
/

CREATE OR REPLACE PACKAGE ut_numeral# AS
  PROCEDURE RUN;
END ut_numeral#;
/
 
CREATE OR REPLACE PACKAGE BODY ut_numeral# AS
  PROCEDURE TEST (
    i_descn VARCHAR2
    , i_exp VARCHAR2
    , i_act VARCHAR2) AS
  BEGIN
    IF i_exp = i_act THEN
      dbms_output.put_line('SUCCESS: ' || i_descn);
    ELSE
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl(i_exp, 'null') || ', but received ' || nvl(i_act, 'null'));
    END IF;
  END TEST;
 
  PROCEDURE RUN AS
  BEGIN
    TEST(i_descn => 'test_1',    i_exp => 'I',      i_act => numeral#.to_roman(   1));
    TEST(i_descn => 'test_2',    i_exp => 'II',     i_act => numeral#.to_roman(   2));
    TEST(i_descn => 'test_3',    i_exp => 'III',    i_act => numeral#.to_roman(   3));
    TEST(i_descn => 'test_4',    i_exp => 'IV',     i_act => numeral#.to_roman(   4));
    TEST(i_descn => 'test_5',    i_exp => 'V',      i_act => numeral#.to_roman(   5));
    TEST(i_descn => 'test_6',    i_exp => 'VI',     i_act => numeral#.to_roman(   6));
    TEST(i_descn => 'test_9',    i_exp => 'IX',     i_act => numeral#.to_roman(   9));
    TEST(i_descn => 'test_27',   i_exp => 'XXVII',  i_act => numeral#.to_roman(  27));
    TEST(i_descn => 'test_48',   i_exp => 'XLVIII', i_act => numeral#.to_roman(  48));
    TEST(i_descn => 'test_59',   i_exp => 'LIX',    i_act => numeral#.to_roman(  59));
    TEST(i_descn => 'test_93',   i_exp => 'XCIII',  i_act => numeral#.to_roman(  93));
    TEST(i_descn => 'test_141',  i_exp => 'CXLI',   i_act => numeral#.to_roman( 141));
    TEST(i_descn => 'test_163',  i_exp => 'CLXIII', i_act => numeral#.to_roman( 163));
    TEST(i_descn => 'test_402',  i_exp => 'CDII',   i_act => numeral#.to_roman( 402));
    TEST(i_descn => 'test_575',  i_exp => 'DLXXV',  i_act => numeral#.to_roman( 575));
    TEST(i_descn => 'test_911',  i_exp => 'CMXI',   i_act => numeral#.to_roman( 911));
    TEST(i_descn => 'test_1024', i_exp => 'MXXIV',  i_act => numeral#.to_roman(1024));
    TEST(i_descn => 'test_3000', i_exp => 'MMM',    i_act => numeral#.to_roman(3000));
  END run;
END ut_numeral#;
/

Community comments

Find this solution interesting? Ask the author a question to learn more.

What can you learn from this solution?

A huge amount can be learned from reading other people’s code. This is why we wanted to give exercism users the option of making their solutions public.

Here are some questions to help you reflect on this solution and learn the most from it.

  • What compromises have been made?
  • Are there new concepts here that you could read more about to improve your understanding?