Oracle，从 SQL-Plus 文件 x.sql 中调用 PL/SQL 问题说 my_function“可能不是函数”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/841003/
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
Oracle, calling PL/SQL issues from within SQL-Plus file x.sql says my_function "may not be a function"
提问by yli
so simple, if I create my function as CREATE OR REPLACE FUNCTION MD5_ENCODEit will run smoothly, but if it stays anonymously within the SQL-Plus block as PL/SQL --> "may not be a function" error.
如此简单，如果我创建我的函数，因为CREATE OR REPLACE FUNCTION MD5_ENCODE它会顺利运行，但如果它作为 PL/SQL 匿名保留在 SQL-Plus 块中 -->“可能不是函数”错误。
What is this Oracle "feature" again?
这个Oracle“特性”又是什么？
DECLARE
FUNCTION MD5_ENCODE(CLEARTEXT IN VARCHAR2) RETURN VARCHAR2 IS
CHK VARCHAR2(16);
HEX VARCHAR2(32);
I INTEGER;
C INTEGER;
H INTEGER;
BEGIN
IF CLEARTEXT IS NULL THEN
RETURN '';
ELSE
CHK := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING
=> CLEARTEXT);
FOR I IN 1 .. 16 LOOP
C := ASCII(SUBSTR(CHK, I, 1));
H := TRUNC(C / 16);
IF H >= 10 THEN
HEX := HEX || CHR(H + 55);
ELSE
HEX := HEX || CHR(H + 48);
END IF;
H := MOD(C, 16);
IF H >= 10 THEN
HEX := HEX || CHR(H + 55);
ELSE
HEX := HEX || CHR(H + 48);
END IF;
END LOOP;
RETURN HEX;
END IF;
END;
BEGIN
UPDATE ADDRESSES_T SET STREET = MD5ENCODE(STREET) ;
-- etc...
END
回答by yli
http://forums.oracle.com/forums/thread.jspa?threadID=245112
http://forums.oracle.com/forums/thread.jspa?threadID=245112
There are a number of things it could be.
有很多事情可能是。
My #1 candidate is, we can only use functions in SQL statements that are public i.e. declared in a package spec. This is the case even for SQL statements executed within the same Package Body. The reson is that SQL statements are executed by a different engine which can only see publicly declared functions.
我的#1 候选人是，我们只能在公开的 SQL 语句中使用函数，即在包规范中声明。即使是在同一个包体中执行的 SQL 语句也是如此。原因是 SQL 语句由不同的引擎执行，该引擎只能看到公开声明的函数。
Long story short, function must be defined in a package,like CREATE OR REPLACE FUNCTION MD5ENCODE(IN_TEXT IN VARCHAR2) RETURN VARCHAR2 IS ...
长话短说，函数必须在包中定义，例如 CREATE OR REPLACE FUNCTION MD5ENCODE(IN_TEXT IN VARCHAR2) RETURN VARCHAR2 IS ...
回答by Dave Costa
In order to call a function within a SQL statement, it needs to exist as a function object within the database. The SQL statement is parsed and executed separately from your PL/SQL code, so it doesn't have access to the locally defined function.
为了在 SQL 语句中调用函数，它需要作为函数对象存在于数据库中。SQL 语句与您的 PL/SQL 代码分开解析和执行，因此它无法访问本地定义的函数。
If you really don't want to create the function object, you could use a cursor to loop over the rows in the table, execute the function in a PL/SQL statement, and update each row as you go.
如果您真的不想创建函数对象，您可以使用游标来遍历表中的行，在 PL/SQL 语句中执行该函数，并随时更新每一行。
回答by David
Your first problem is you have a typo.
你的第一个问题是你有一个错字。
FUNCTION MD5_ENCODE(CLEARTEXT IN VARCHAR2) RETURN VARCHAR2 IS
函数 MD5_ENCODE(CLEARTEXT IN VARCHAR2) 返回 VARCHAR2 是
versus
相对
UPDATE ADDRESSES_T SET STREET = MD5ENCODE(STREET) ;
更新 ADDRESSES_T SET STREET = MD5ENCODE(STREET) ;
You are missing the underscore in the function call in the update statement.
您在更新语句的函数调用中缺少下划线。
The next error you will encounter is :
您将遇到的下一个错误是：
PLS-00231: function 'MD5_ENCODE' may not be used in SQL
So you can simply assign the function results to a variable and use it in the Update statement.
因此，您可以简单地将函数结果分配给一个变量并在 Update 语句中使用它。
回答by R?dvan Korkmaz
As it mentioned above comments, this could be useful.
正如上面提到的评论，这可能很有用。
DECLARE
V NUMBER := 0;
FUNCTION GET_SQ(A NUMBER) RETURN NUMBER AS
BEGIN
RETURN A * A;
END;
BEGIN
V := GET_SQ(5);
--DBMS_OUTPUT.PUT_LINE(V);
UPDATE MYTABLE A SET A.XCOL = V;
END;
