Oracle中使用Vigenere密码加密主键列遇问题求助

从你给出的查询片段来看，你应该是在实现一个类似Vigenère密码的变种解密逻辑——通过分层拆分密文和密钥字符，做ASCII偏移计算后重组明文，但在处理带主键约束的表时遇到了部分数据异常的情况。我先把你的查询片段补全成可参考的完整形式，再分析可能的问题点和解决方案：

你的解密查询片段（补全后） #

select regexp_replace(max(sys_connect_by_path(cy,'-')),'-','') plaintext 
from (
    select row_number() over (order by L) rn, cy 
    from (
        select chr(mod(ascii(substr(p,level,1)) + ascii(substr(k,decode(mod(level,length(k)),0,length(k), mod(level,length(k))),1))-130,26) + 65) cy, level L 
        from ( 
            -- 假设这里是从带主键的表中获取密文和密钥
            select encrypted_column p, key_column k from your_encrypted_table
        )
        connect by level <= length(p)
    )
)
start with rn=1
connect by prior rn = rn-1;

可能导致数据异常的原因 #

• 主键分组缺失，跨行拼接错误：如果你的查询没有按主键对每一行数据单独处理，sys_connect_by_path和max会把所有行的解密片段混在一起拼接，完全打乱了每一行的明文内容。
• 字符边界处理不当：你的逻辑只针对大写字母（ASCII 65-90）设计，如果密文中包含小写字母、数字或符号，mod运算会产生错误的偏移，导致乱码。
• 空值/零长度数据触发异常：如果表中存在空的密文或密钥列，length(p)或length(k)返回0，会导致mod(level,0)抛出除以0的错误，或者生成无效字符。
• CONNECT BY层级逻辑混乱：当处理多行数据时，connect by level <= length(p)没有限制在单行范围内，会导致层级交叉，拼接出错误的字符串。

针对性解决方案 #

1. 按主键分组，逐行解密
   必须确保每一行数据（以主键为唯一标识）单独执行解密逻辑，避免跨行干扰。假设你的表是your_encrypted_table，主键为id，修改后的查询如下：

   SELECT 
       main.id,
       (SELECT regexp_replace(max(sys_connect_by_path(cy,'-')), '-', '')
        FROM (
            SELECT row_number() over (order by L) rn, cy 
            FROM (
                SELECT chr(mod(ascii(substr(t.p, level, 1)) + ascii(substr(t.k, mod(level-1, length(t.k)) + 1, 1)) - 130, 26) + 65) cy, level L 
                FROM (
                    SELECT encrypted_column p, key_column k 
                    FROM your_encrypted_table 
                    WHERE id = main.id -- 关联主键，锁定单行数据
                )
                CONNECT BY level <= length(p)
            )
        )
        START WITH rn=1
        CONNECT BY PRIOR rn = rn-1) AS plaintext
   FROM your_encrypted_table main;
   

   这里我把密钥索引逻辑简化成了mod(level-1, length(t.k)) + 1，和原来的decode逻辑等价，但更简洁易读。

2. 改用LISTAGG替代sys_connect_by_path
   Oracle 11g及以上版本支持LISTAGG函数，比sys_connect_by_path更稳定，适合字符串拼接：

   SELECT 
       main.id,
       (SELECT LISTAGG(cy, '') WITHIN GROUP (ORDER BY L)
        FROM (
            SELECT chr(mod(ascii(substr(t.p, level, 1)) + ascii(substr(t.k, mod(level-1, length(t.k)) + 1, 1)) - 130, 26) + 65) cy, level L 
            FROM (
                SELECT encrypted_column p, key_column k 
                FROM your_encrypted_table 
                WHERE id = main.id
            )
            CONNECT BY level <= length(p)
        )) AS plaintext
   FROM your_encrypted_table main;
   

3. 增加异常数据处理
   对空值、非字母字符做处理，避免错误：

   SELECT 
       chr(
           CASE 
               -- 只处理大写字母，其他字符直接保留
               WHEN ascii(substr(p, level, 1)) BETWEEN 65 AND 90 
               THEN mod(ascii(substr(p, level, 1)) + ascii(substr(k, mod(level-1, length(k)) + 1, 1)) - 130, 26) + 65
               ELSE ascii(substr(p, level, 1))
           END
       ) cy, level L
   FROM ...
   -- 同时在主查询中处理空值
   CASE 
       WHEN p IS NULL OR k IS NULL OR length(p) = 0 OR length(k) = 0 
       THEN NULL -- 或返回自定义提示，比如'无效数据'
       ELSE ... -- 解密逻辑
   END AS plaintext
   

4. 排查特定异常行
   如果只有部分行异常，单独提取这些行的密文和密钥，手动计算每一步的ASCII值，比如检查密钥索引是否正确、mod运算结果是否符合预期，很快就能定位到问题所在。

内容的提问来源于stack exchange，提问作者Ankit Mongia