Oracle 中的 MD5 (DBMS_OBFUSCATION_TOOLKIT.MD5)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5897438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MD5 in Oracle (DBMS_OBFUSCATION_TOOLKIT.MD5)
提问by álvaro González
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:
我正在尝试编写一个函数来从我在这里和那里收集的位中获取 MD5 哈希值。我想获得散列的小写十六进制表示。到目前为止我有这个：
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;
I'm not sure about the return type of the function. DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUMlooks like the appropriate choice and as far as I can tell it works as expected but the package definition for dbms_obfuscation_toolkitas displayed by SQL Developer shows this:
我不确定函数的返回类型。DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM看起来是合适的选择，据我所知，它按预期工作，但dbms_obfuscation_toolkitSQL Developer 显示的包定义显示了这一点：
SUBTYPE varchar2_checksum IS VARCHAR2(16);
The output has 32 characters so I must be doing something wrong. My questions:
输出有 32 个字符，所以我一定是做错了什么。我的问题：
- What's the correct type for the
RETURNstatement? - Am I doing unnecessary conversions to calculate the hash?
RETURN语句的正确类型是什么？- 我是否在进行不必要的转换来计算哈希？
回答by tbone
Here you go:
干得好：
create or replace function getMD5(
in_string in varchar2)
return varchar2
as
cln_md5raw raw(2000);
out_raw raw(16);
begin
cln_md5raw := utl_raw.cast_to_raw(in_string);
dbms_obfuscation_toolkit.md5(input=>cln_md5raw,checksum=>out_raw);
-- return hex version (32 length)
return rawtohex(out_raw);
end;
The 32 length is because it is a hex representation of the raw(16) value. Or, modify above to output the raw version and store the raw in a RAW column (less space used, but you'll be doing future rawtohex and hextoraw conversions, believe me).
32 的长度是因为它是 raw(16) 值的十六进制表示。或者，修改上面的内容以输出原始版本并将原始版本存储在 RAW 列中（使用较少的空间，但您将来会进行 rawtohex 和 hextoraw 转换，相信我）。
Cheers
干杯
回答by APC
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:
不能限制存储过程参数和函数返回类型是 Oracle PL/SQL 的一个特性。也就是说，我们不能有这样一个签名的过程：
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>
Sure we can define the procedure's parameter using a SUBTYPE but Oracle will ignore it. Same goes for function return types...
当然，我们可以使用 SUBTYPE 定义过程的参数，但 Oracle 会忽略它。函数返回类型也是如此......
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>
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).
限制参数和返回类型的唯一方法是在存储过程中使用子类型声明变量。使用包内的变量，并将它们分配给 OUT 参数（或返回函数的变量）。
Which is a long-winded way of saying, you can use DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUMin your code confident that it won't prevent your function returning 32 characters.
这是一种冗长的说法，您可以DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM在代码中使用 确信它不会阻止您的函数返回 32 个字符。
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:
但是，它会使查找 SUBTYPE 声明的开发人员感到困惑。在最坏的情况下，这些人将使用子类型声明他们自己的工作变量，并带来以下悲惨的结果：
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.
因此，最好不要使用不合适的子类型。而是声明你自己的。
