oracle 如何使用TOAD ORACLE调用包DEF中定义的sql函数ABC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8108521/
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
How to call sql function ABC defined in package DEF using TOAD ORACLE
提问by iwan
I have Oracle Package named DEF , with 1 function inside named ABC that accept 1 string argument. May I know how to call this function ABC directly in TOAD Editor?
我有一个名为 DEF 的 Oracle 包，其中有一个名为 ABC 的函数，它接受 1 个字符串参数。我可以知道如何在 TOAD 编辑器中直接调用这个函数 ABC 吗？
Thanks in advance.
提前致谢。
CREATE OR REPLACE PACKAGE HPQ_IF.def AS
FUNCTION def(p_sql IN VARCHAR2)
RETURN VARCHAR2;
END def;
/
FUNCTION abc(p_sql IN VARCHAR2)
RETURN VARCHAR2
IS
j NUMBER;
BEGIN
dbms_output.put_line(p_sql);
RETURN 'Done';
END abc;
Last error (using first answer below):
最后一个错误（使用下面的第一个答案）：
[Error] Execution (6: 31): ORA-06550: line 6, column 31:
PLS-00302: component 'abc' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
回答by Scott A
In the TOAD SQL buffer:
在 TOAD SQL 缓冲区中：
DECLARE
v_sql VARCHAR2(255);
v_return VARCHAR2(255);
BEGIN
v_sql := 'the string'; -- or get it from anywhere else
v_return := HPQ_IF.def.abc(v_sql);
dbms_output.put_line(v_return);
-- do anything else you want to do with the return value here...
END;
If you don't want to do anything with the return value other than display the information:
如果除了显示信息之外不想对返回值做任何事情：
SELECT HPQ_IF.def.abc('the string') FROM DUAL;
However, for a PL/SQL function to be called in SQL (SELECT, INSERT, etc), it must be free from certain side effects (ie. it can't modify certain kinds of state within the database). To be called as a column in a SELECT, it must not modify the database (insert or update, for example), perform DDL, or commit a transaction.
但是，对于要在 SQL（SELECT、INSERT 等）中调用的 PL/SQL 函数，它必须没有某些副作用（即它不能修改数据库中的某些类型的状态）。要在 SELECT 中作为列调用，它不得修改数据库（例如插入或更新）、执行 DDL 或提交事务。
You can also use Execute Procedure from within the Schema Browser.
您还可以从模式浏览器中使用执行过程。
回答by Justin Cave
A function returns a value so you have to return the result somewhere.
函数返回一个值，因此您必须在某处返回结果。
You can call the function from a SQL statement
您可以从 SQL 语句调用该函数
SELECT def.abc( 'Some string' )
FROM dual;
Or you can return the result into a local variable in PL/sQL
或者您可以将结果返回到 PL/SQL 中的局部变量中
DECLARE
l_result VARCHAR2(100);
BEGIN
l_result := def.abc( 'Some string' );
END;
