这个应该蛮简单的啊 加密: declare v1 varchar2(100); v2 varchar2(100); v3 varchar2(100); begin for a in (select * from tb1) loop for b in 1 .. length(a.xm) loop v1 := substr(a.xm, b, 1); --dbms_output.put_line(v1); select decode(b, length(a.xm), tb2.pas, tb2.pas || 'V6') into v2 from tb2 where tb2.key = v1; --dbms_output.put_line(v2); v3:=v3||v2; end loop; update tb1 set tb1.xm='^1V6'||v3 where tb1.id=a.id; --dbms_output.put_line('^1V6'||v3); v3:=''; end loop; commit; end;
解密也简单,用好substr 和instr 即可
with t1 as ( select 1 id,'^1V6FV10V2CV6GV1KVKJV6GV1KV1C' xm from dual union all select 2 id,'^1V6GV1KVK2V6FV26VJKV6GV1KV1C' xm from dual ),t2 as ( select '张' key,'FV10V2C' pas from dual union all select '三' key,'GV1KVKJ' pas from dual union all select '丰' key,'GV1KV1C' pas from dual union all select '上' key,'GV1KVK2' pas from dual union all select '官' key,'FV26VJK' pas from dual ) select id,replace(wm_concat(key),',','') xm from ( select id,key,xm,lv from ( select distinct id,substr(xm,5+(level-1)*9,7) xm,level lv from t1 connect by level <= round(length(xm)/9,0) ) t left join t2 on t2.pas = t.xm order by id,lv ) group by id id xm ------------------------------ 1 1 张三丰 2 2 上官丰
写的有点复杂了 wm_concat合并不好把握 有时候会出现不按顺序合并的情况 所以又套了第三层
解密 declare v1 varchar2(100); v2 varchar2(100); v3 number(5); v4 number(5); begin for a in (select * from t1) loop for b in 1..length(a.tname) loop v1:=substr(a.tname,b,1); v3:=instr(a.tpassword,'v6',1,b); v4:=instr(a.tpassword,'v6',1,b+1); v2:=substr(a.tpassword,v3+2,v4-v3-2); dbms_output.put_line(v1||' '||v2); end loop; end loop; end;
谢谢12楼jascjasc的回复,但还有点问题 怎么没看到tb2
解密: declare v1 varchar2(10); v2 varchar2(10); begin for a in (select t.id, regexp_replace(t.xm, '\^1V6|V6', ',') xm from tb1 t) loop
for b in (select regexp_substr(a.xm, '[^,]+', 1, level) xm from dual connect by level <= regexp_count(a.xm, ',')) loop --dbms_output.put_line(b.xm); select t.key into v1 from tb2 t where t.pas = b.xm; v2 := v2 || v1; --dbms_output.put_line(v2); end loop; update tb1 set tb1.xm = v2 where tb1.id = a.id; v2 := ''; end loop; commit; end;
我用的是oracle 10G,没有regexp_count这个函数
create or replace function NG_ceshi(name in varchar2) return varchar2 is Result varchar2(400); ls_sql varchar2(4000); v_length number:=length(name); v_mm varchar2(4000); v_am varchar2(4000); begin begin for i in 1..v_length loop ls_sql:='select substr('''||name||''','||i||',1) from dual '; execute immediate ls_sql into v_mm; ls_sql:='select a.pas from tb2 a where a.key='''||v_mm||''''; execute immediate ls_sql into v_am; Result:=Result||v_am; end loop; exception when others then Result:=''; end ; return(Result); end ;
加密:
declare
v1 varchar2(100);
v2 varchar2(100);
v3 varchar2(100);
begin
for a in (select * from tb1) loop
for b in 1 .. length(a.xm) loop
v1 := substr(a.xm, b, 1);
--dbms_output.put_line(v1);
select decode(b, length(a.xm), tb2.pas, tb2.pas || 'V6')
into v2
from tb2
where tb2.key = v1;
--dbms_output.put_line(v2);
v3:=v3||v2;
end loop;
update tb1 set tb1.xm='^1V6'||v3 where tb1.id=a.id;
--dbms_output.put_line('^1V6'||v3);
v3:='';
end loop;
commit;
end;
with t1 as
(
select 1 id,'^1V6FV10V2CV6GV1KVKJV6GV1KV1C' xm from dual union all
select 2 id,'^1V6GV1KVK2V6FV26VJKV6GV1KV1C' xm from dual
),t2 as
(
select '张' key,'FV10V2C' pas from dual union all
select '三' key,'GV1KVKJ' pas from dual union all
select '丰' key,'GV1KV1C' pas from dual union all
select '上' key,'GV1KVK2' pas from dual union all
select '官' key,'FV26VJK' pas from dual
)
select id,replace(wm_concat(key),',','') xm
from
(
select id,key,xm,lv
from
(
select distinct id,substr(xm,5+(level-1)*9,7) xm,level lv
from t1
connect by level <= round(length(xm)/9,0)
) t left join t2 on t2.pas = t.xm
order by id,lv
)
group by id
id xm
------------------------------
1 1 张三丰
2 2 上官丰
declare
v1 varchar2(100);
v2 varchar2(100);
v3 number(5);
v4 number(5);
begin
for a in (select * from t1) loop
for b in 1..length(a.tname) loop
v1:=substr(a.tname,b,1);
v3:=instr(a.tpassword,'v6',1,b);
v4:=instr(a.tpassword,'v6',1,b+1);
v2:=substr(a.tpassword,v3+2,v4-v3-2);
dbms_output.put_line(v1||' '||v2);
end loop;
end loop;
end;
怎么没看到tb2
解密:
declare
v1 varchar2(10);
v2 varchar2(10);
begin
for a in (select t.id, regexp_replace(t.xm, '\^1V6|V6', ',') xm from tb1 t) loop
for b in (select regexp_substr(a.xm, '[^,]+', 1, level) xm
from dual
connect by level <= regexp_count(a.xm, ',')) loop
--dbms_output.put_line(b.xm);
select t.key into v1 from tb2 t where t.pas = b.xm;
v2 := v2 || v1;
--dbms_output.put_line(v2);
end loop;
update tb1 set tb1.xm = v2 where tb1.id = a.id;
v2 := '';
end loop;
commit;
end;
Result varchar2(400);
ls_sql varchar2(4000);
v_length number:=length(name);
v_mm varchar2(4000);
v_am varchar2(4000);
begin
begin
for i in 1..v_length loop
ls_sql:='select substr('''||name||''','||i||',1) from dual ';
execute immediate ls_sql into v_mm;
ls_sql:='select a.pas from tb2 a where a.key='''||v_mm||'''';
execute immediate ls_sql into v_am;
Result:=Result||v_am;
end loop;
exception when others then Result:=''; end ; return(Result);
end ;