oracle 如何通过shell脚本捕获存储过程的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32055863/
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 capture the result of stored procedure through shell script?
提问by
I'm trying to execute stored procedure through shell script and try to get return from stored procedure but I didn't get any thing from the stored procedure on other hand same thing I do with sqlplus prompt and I'm able to get the result
我正在尝试通过 shell 脚本执行存储过程并尝试从存储过程中获取返回值，但另一方面我没有从存储过程中得到任何东西，我用 sqlplus 提示做同样的事情，我能够得到结果
sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare
DE_REC_COUNT number(10);
begin
DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
EOF
Through sqlplus prompt
通过sqlplus提示
SQL> set serveroutput on
declare
DE_REC_COUNT number;
begin
DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
0
PL/SQL procedure successfully completed.
采纳答案by Alex Poole
The version of the anonymous block in the shell script will not be executed as shown, because you don't have a slash after the block to run it. If you run that you get no output at all. If you change it to have a slash:
shell 脚本中匿名块的版本不会如图所示执行，因为您在块后没有斜杠来运行它。如果你运行它，你根本没有输出。如果你把它改成斜线：
sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare
DE_REC_COUNT number(10);
begin
DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/
EOF
then you'll see:
然后你会看到：
0
PL/SQL procedure successfully completed.
You've shown the interactive version in SQL*Plus without the slash too, but you must have had that to see the output you showed.
您已经在 SQL*Plus 中显示了没有斜线的交互式版本，但是您必须拥有斜线才能看到您显示的输出。
If you want the zero - which seems to be coming from a dbms_outputcall in your procedure, rather than directly from your anonymous block - n a shell variable you can refer to later, you can assign the output of the heredoc to a variable:
如果您想要零 - 这似乎来自dbms_output您的过程中的调用，而不是直接来自您的匿名块 - na shell 变量您可以稍后引用，您可以将heredoc的输出分配给一个变量：
MY_VAR=`sqlplus -silent xxx@xxx <<EOF
set serveroutput on
set feedback off
declare
DE_REC_COUNT number(10);
begin
DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/
EOF`
printf "Got back MY_VAR as %s\n" ${MY_VAR}
Note that I've added set feedback offso you don't see the PL/SQL procedure successfully completedline. Now when you run that you'll see:
请注意，我已添加，set feedback off因此您看不到该PL/SQL procedure successfully completed行。现在，当您运行它时，您将看到：
Got back MY_VAR as 0
and you can do whatever you need to with ${MY_VAR}. It depends what you mean by 'capture' though.
你可以做任何你需要做的事情${MY_VAR}。不过，这取决于您所说的“捕获”是什么意思。
回答by Sentinel
Here's an example of how it can be done by surrounding the code with the evaluation operators (` back quotes):
下面是一个示例，说明如何通过用计算运算符（` 反引号）包围代码来完成它：
#!/bin/sh
results=`sqlplus -s xxx@xxx <<EOF
set serveroutput on feedback off
declare
DE_REC_COUNT number(10);
begin
DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/
EOF`
echo $results
回答by Sandeep
Lets say for instance I have a below procedure which calculates the sum of two numbers and prints the total using dbms_output.put_line()method
比如说我有一个下面的过程，它计算两个数字的总和并使用dbms_output.put_line()方法打印总数
CREATE OR REPLACE
PROCEDURE SUM
(
a IN NUMBER,
b IN NUMBER)
AS
total NUMBER;
BEGIN
total:= a + b;
dbms_output.put_line(total);
END;
Then in order to call this in our shell script we need to call this procedure in an anonymous block and store in a variable, later using echo we can execute the command.
然后为了在我们的 shell 脚本中调用它，我们需要在匿名块中调用这个过程并存储在一个变量中，稍后我们可以使用 echo 执行命令。
plsqlcode=`sqlplus -silent hr/sandeep@orcl <<EOF
set serveroutput on
begin
sum(10,20);
end;
/
EOF`
echo $plsqlcode
Ideally this is not a recommended approach you should keep your shell and pl/sql code in separate files.
理想情况下，这不是推荐的方法，您应该将 shell 和 pl/sql 代码保存在单独的文件中。
