PL SQL t9 oracle-CodePudding

i want to recognise 1 thing. I want to solve task about T9 encription. I have table T9_encryiption with columns codes which are numbers from 1 to 9. and in second column letters. There is needed to enter a variable2:='YAGUBZADA' and the output should be 924829232 using T9_encryiption table records in for loop. I have example code but in this code the table and for loop are not used .

CodePudding user response：

You can join to the T9_ENCRYPTION table in your function, then just output the result of that function.

DECLARE
    FUNCTION t9_encrypt (p_text VARCHAR2)
        RETURN VARCHAR2
    IS
        l_encrypted_text   VARCHAR2 (4000);
    BEGIN
        SELECT LISTAGG (t.codes) WITHIN GROUP (ORDER BY w.lvl)     AS encrypted_text
          INTO l_encrypted_text
          FROM (    SELECT SUBSTR (p_text, LEVEL, 1) AS single_char, LEVEL AS lvl
                      FROM dual
                CONNECT BY LEVEL <= LENGTH (p_text)) w
                JOIN t9_encryption t ON (INSTR (t.names, w.single_char) > 0);

        RETURN l_encrypted_text;
    END;
BEGIN
    dbms_output.put_line(t9_encrypt('YAGUBZADA'));
END;

CodePudding user response：

If you're stuck with that data model and the assignment is to use a PL/SQL loop over the table, you could convert each names value to a regex pattern - for example changing 'A,B,C' to '(A|B|C)' - and use that to replace those matching characters:

declare
  variable2 varchar2(100) := 'YAGUBZADA';
begin
  for r in (select codes, names from t9_encryiption) loop
    variable2 := regexp_replace(variable2, '(' || replace(r.names, ',', '|') || ')', r.codes);
  end loop;
  dbms_output.put_line(variable2);
end;
/

924829232

That assume the names value for 1 is a space, as in your string version; if it's null you could coalesce or nvl it, or exclude that row.

Or you could use a second loop to split each comma-separated list into individual characters and replace those individually:

declare
  variable2 varchar2(100) := 'YAGUBZADA';
begin
  for r1 in (select codes, names from t9_encryiption) loop
    for r2 in (
      select regexp_substr(r1.names, '[^,] ', 1, level) as name
      from dual
      connect by regexp_substr(r1.names, '[^,] ', 1, level) is not null
    )
    loop
      variable2 := replace(variable2, r2.name, r1.codes);
    end loop;
  end loop;
  dbms_output.put_line(variable2);
end;
/

924829232

which would work with a space or null for 1.

db<>fiddle

You can do it without PL/SQL too, e.g. by generating the translate arguments from the table:

select translate('YAGUBZADA',
  listagg(replace(names, ',')) within group (order by codes),
  listagg(rpad(codes, length(replace(names, ',')), codes)) within group (order by codes))
from t9_encryiption

You don't have to remove the commas but it seems neater to do so.

db<>fiddle