Oracle中的MD5(DBMS_OBFUSCATION_TOOLKIT.MD5) [英] MD5 in Oracle (DBMS_OBFUSCATION_TOOLKIT.MD5)

问题描述

我正在尝试编写一个函数，以从我在这里和那里收集的位中获取MD5哈希值.我想获取哈希的小写十六进制表示形式.到目前为止，我已经知道了:

I'm trying to compose a function to obtain MD5 hashes from bits I've gathered here and there. I want to obtain the lower-case hexadecimal representation of the hash. I have this so far:

CREATE OR REPLACE FUNCTION MD5 (
    CADENA IN VARCHAR2
) RETURN DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM
AS
BEGIN
    RETURN LOWER(
        RAWTOHEX(
            UTL_RAW.CAST_TO_RAW(
                DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => CADENA)
            )
        )
    );
END;

我不确定函数的返回类型. DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM看起来是合适的选择，据我所知它可以按预期工作，但是SQL Developer显示的dbms_obfuscation_toolkit程序包定义显示了这一点:

I'm not sure about the return type of the function. DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM looks like the appropriate choice and as far as I can tell it works as expected but the package definition for dbms_obfuscation_toolkit as displayed by SQL Developer shows this:

SUBTYPE varchar2_checksum IS VARCHAR2(16);

输出有32个字符，所以我一定做错了.我的问题:

The output has 32 characters so I must be doing something wrong. My questions:

• RETURN语句的正确类型是什么?
• 我是否在进行不必要的转换以计算哈希值?

• What's the correct type for the RETURN statement?
• Am I doing unnecessary conversions to calculate the hash?

推荐答案

Oracle PL/SQL的独特之处在于，不能限制存储过程参数和函数返回类型.也就是说，我们不能有一个带有如下签名的过程:

It's a peculiarity of Oracle PL/SQL that stored procedure parameters and function return types cannot be limited. That is, we cannot have a procedure with a signature like this:

SQL> create or replace procedure my_proc (p1 in varchar2(30))
  2  is
  3  begin
  4      null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/34     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

SQL> create or replace procedure my_proc (p1 in varchar2)
  2  is
  3  begin
  4      null;
  5  end;
  6  /

Procedure created.

SQL>

当然，我们可以使用SUBTYPE定义过程的参数，但是Oracle会忽略它.函数返回类型也是如此...

Sure we can define the procedure's parameter using a SUBTYPE but Oracle will ignore it. Same goes for function return types...

SQL> create or replace package my_subtypes as
  2      subtype ltd_string is varchar2(30);
  3  end;
  4  /

Package created.

SQL> create or replace function my_func return my_subtypes.ltd_string
  2  is
  3  begin
  4      return lpad('a', 4000, 'a');
  5  end;
  6  /

Function created.

SQL> select length(my_func) from dual
  2  /

LENGTH(MY_FUNC)
---------------
           4000

SQL>

限制参数和返回类型的唯一方法是在存储过程中使用子类型声明变量.使用包中的变量，然后将其分配给OUT参数(或为函数返回变量).

The only way of limiting parameters and return types is to declare variables using subtypes within the stored procedure. Use the variables within the package, and assign them to the OUT paramters (or RETURN the variable for functions).

这是长篇大论的说法，您可以在代码中使用DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM，因为它确信不会阻止您的函数返回32个字符.

Which is a long-winded way of saying, you can use DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM in your code confident that it won't prevent your function returning 32 characters.

但是，这会使使用SUBTYPE声明的开发人员感到困惑.在最坏的情况下，这些人将使用该子类型来声明自己的工作变量，并产生以下悲剧性的结果:

However, it will confuse developers who will lookup the SUBTYPE declaration. In the worst case these people will use the subtype to declare their own working variables with the following tragic result:

SQL> declare
  2      v my_subtypes.ltd_string;
  3  begin
  4      v := my_func;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


SQL>

因此，最好不要使用不合适的子类型.而是声明自己的.

So, it is better not to use an inappropriate subtype. Instead declare your own.

这篇关于Oracle中的MD5(DBMS_OBFUSCATION_TOOLKIT.MD5)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！