WITH TA AS (SELECT 4 KEY FROM DUAL UNION ALL SELECT 5 KEY FROM DUAL ), TB AS (SELECT '2378' AS SA FROM DUAL),
TC AS (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(LENGTH(SA)) FROM TB)) SELECT KEY, SA, REPLACE(WM_CONCAT(MOD(SUBSTR(SA, LV, 1) + KEY, 10)), ',', '') RESULT FROM TA, TB, TC WHERE LENGTH(SA) >= LV GROUP BY KEY, SA------ KEY SA RESULT 4 2378 6712 5 2378 7823
SELECT mod(substr('2378',1,1)+4,10) ||
mod(substr('2378',2,1)+4,10) ||
mod(substr('2378',3,1)+4,10) ||
mod(substr('2378',4,1)+4,10)
FROM dual;SELECT mod(substr('2378',1,1)+5,10) ||
mod(substr('2378',2,1)+5,10) ||
mod(substr('2378',3,1)+5,10) ||
mod(substr('2378',4,1)+5,10)
FROM dual;
如果变位数的话,最好自定义个函数
function:create or replace function test_ff(in_num in varchar2,in_str varchar2)
return varchar2 is
v_ret varchar2(100);
begin
if in_num = '1' then
SELECT mod(substr(in_str, 1, 1) + 4, 10) ||
mod(substr(in_str, 2, 1) + 4, 10) ||
mod(substr(in_str, 3, 1) + 4, 10) ||
mod(substr(in_str, 4, 1) + 4, 10) into v_ret
FROM dual;
elsif in_num = '2' then
SELECT mod(substr(in_str, 1, 1) + 5, 10) ||
mod(substr(in_str, 2, 1) + 5, 10) ||
mod(substr(in_str, 3, 1) + 5, 10) ||
mod(substr(in_str, 4, 1) + 5, 10) into v_ret
FROM dual;
else
v_ret :='It is wrong key';
end if;
return v_ret;
exception
when others then
begin
return '程序异常';
end;
end;测试,键盘输入第一个参数:SQL> select test_ff(&a,'2378') from dual;
TEST_FF(1,'2378')
--------------------------------------------------------------------------------
6712
SQL>
WITH TA AS
(SELECT 4 KEY FROM DUAL UNION ALL
SELECT 5 KEY FROM DUAL
),
TB AS
(SELECT '2378' AS SA FROM DUAL),
TC AS
(SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX(LENGTH(SA)) FROM TB))
SELECT KEY, SA, REPLACE(WM_CONCAT(MOD(SUBSTR(SA, LV, 1) + KEY, 10)), ',', '') RESULT
FROM TA, TB, TC
WHERE LENGTH(SA) >= LV
GROUP BY KEY, SA------
KEY SA RESULT
4 2378 6712
5 2378 7823