SQL 将 varchar2 参数从 Select 语句传递给 plsql 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15633246/
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
Passing varchar2 parameter to plsql procedure from the Select statement
提问by Peter17
Executing the following statement in Oracle Toad
在 Oracle Toad 中执行以下语句
exec plsql_procedure(select 'somestring' from dual);
trhows the following exception:
抛出以下异常：
ORA-06550: line 1, column 33: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
ORA-06550：第 1 行，第 33 列：PLS-00103：在预期以下情况之一时遇到符号“SELECT”：
( ) - + case mod new not null
( ) - + case mod new 不为空
Procedure takes one VARCHAR2 parameter. How it can be fixed?
过程采用一个 VARCHAR2 参数。如何修复？
采纳答案by David Aldridge
Unless your use of the select from dual is just an example of something else, leave out the selection.
除非您使用 select from dual 只是其他示例，否则请忽略选择。
exec plsql_procedure('somestring');
Otherwise, select into a variable and pass that to the procedure.
否则，选择一个变量并将其传递给过程。
declare
my_var table_name.column_name%Type;
begin
select column_name
into my_var
from table_name
where ...;
plsql_procedure(parameter_name => my_var);
end;
/
回答by Rachcha
I have tried a few approaches. Those include:
我尝试了几种方法。其中包括：
exec plsql_procedure((select 'somestring' from dual));
EXECUTE IMMEDIATE ('testProc('''||(SELECT 'Testing' FROM dual)||''')');
EXECUTE IMMEDIATE ('EXEC testProc('''||(SELECT 'Testing' FROM dual)||''')');
None of the above worked. Looks like Oracle doesn't allow SELECTstatements in the arguments list under any circumstances.
以上都没有奏效。看起来 OracleSELECT在任何情况下都不允许参数列表中的语句。
One thing I did was store the SELECTquery result into a variable and use it later like follows:
我做的一件事是将SELECT查询结果存储到一个变量中，然后像下面这样使用它：
CREATE OR REPLACE PROCEDURE testProc (
testVar IN VARCHAR2 -- Proc accepts VARCHAR2
) IS
BEGIN
dbms_output.put_line(testVar);
END;
/
SET SERVEROUTPUT ON
DECLARE
testVarIn VARCHAR(2000); -- Declare the variable
BEGIN
SELECT 'Testing' INTO testVarIn FROM dual;
-- Accept its value using SELECT ... INTO
testProc(testVarIn); -- Use the variable value to pass to the proc
END;
/
Output:
输出：
PROCEDURE testProc compiled
anonymous block completed
Testing
When things don't happen, we have to live with the alternatives. Our end goal is just to get the job done. It doesn't matter what we do for getting it done.
当事情没有发生时，我们必须接受替代方案。我们的最终目标只是完成工作。我们为完成它做了什么并不重要。
回答by Klas Lindb?ck
It is possible for functions, but not for procedures. Note that the selectneeds to be enclosed in parenthesis, hence the double parenthesis in the select below.
函数可以，但过程不行。请注意，select需要用括号括起来，因此下面的 select 中有双括号。
create or replace function foo (x varchar2) return integer is
begin
--Do various stuff;
return 1;
end;
/
select foo((select 'somestring' from dual)) from dual;
回答by Bob Jarvis - Reinstate Monica
One thing to keep in mind is that a relational query does not return a VALUE - it returns a TABLE. Even though we might know due to the context (such as selecting a literal value from DUAL) that a particular SELECT will only return a single row, the database does not know that and will assume that the SELECT will return a table with multiple rows. Even SELECT 1 FROM DUAL returns a table - this table has a single column and a single row, but it's still a table. :-)
要记住的一件事是关系查询不返回 VALUE - 它返回一个 TABLE。尽管我们可能根据上下文（例如从 DUAL 中选择文字值）知道特定的 SELECT 只会返回单行，但数据库不知道这一点，并且会假设 SELECT 将返回一个包含多行的表。即使 SELECT 1 FROM DUAL 返回一个表 - 该表只有一列和一行，但它仍然是一个表。:-)
Share and enjoy.
分享和享受。
