oracle 从 PL/SQL 块运行 SCRIPT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8830427/
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
Run SCRIPT from PL/SQL Block
提问by G.S
How to use "START SCRIPT" in pl/sql block ?
如何在 pl/sql 块中使用“START SCRIPT”？
I want to use something like this
我想用这样的东西
declare
begin
proc(para1,para2);
execute immediate 'start prompt1' ;
end;
/
Also I want to know , can i get a value from prompt1 into my PL/SQL block where am calling the script ? Because I need to use the value to perform some operations in the PL/SQL block.
另外我想知道，我可以从 prompt1 获取一个值到我调用脚本的 PL/SQL 块中吗？因为我需要使用该值在 PL/SQL 块中执行一些操作。
回答by APC
It is 20122017. Scripts are a clunky and brittle hangover from the last millennium. Oracle has a fantastic range of functionality we can execute in PL/SQL, plus there's Java Stored Procedures, and there's scheduling for starting jobs. Other than running DDL to create or amend schemas there is hardly any need for scripts in an Oracle database environment; even DDL scripts should be triggered from an external client, probably a build tool such as TeamCity.
现在是2012 年和2017年。脚本是上一千年的笨拙而脆弱的宿醉。Oracle 具有我们可以在 PL/SQL 中执行的一系列出色的功能，此外还有 Java 存储过程和用于启动作业的调度。除了运行 DDL 来创建或修改模式之外，在 Oracle 数据库环境中几乎不需要脚本；甚至 DDL 脚本也应该从外部客户端触发，可能是诸如 TeamCity 之类的构建工具。
In particular I would regard attempting to run a SQL script from a PL/SQL program as an architectural failure. What are you doing with the script which you cannot do with a stored procedure?
特别是，我认为尝试从 PL/SQL 程序运行 SQL 脚本是架构失败。你用存储过程不能做的脚本做什么？
As for passing input to a stored procedure, that's what parameters are for. PL/SQL isn't interactive, we need a client to enter the values. Depending on the scenario this can be done asynchronously (values in a file or a table) or synchronously (calling the stored procedure from SQL*Plus, SQL Developer or a bespoke front end).
至于将输入传递给存储过程，这就是参数的用途。PL/SQL 不是交互式的，我们需要一个客户端来输入值。根据场景，这可以异步（文件或表中的值）或同步（从 SQL*Plus、SQL Developer 或定制前端调用存储过程）完成。
Having said all that, in the real world we work with messy architectures with inter-dependencies between the database and the external OS. So what can we do?
话虽如此，在现实世界中，我们使用杂乱的架构，在数据库和外部操作系统之间存在相互依赖关系。所以，我们能做些什么？
- We can write a Java Stored Procedure to execute shell commands. This is the venerable solution, having been around since Oracle 8i. Find out more.
- In 10g Oracle replace DBMS_JOB with DBMS_SCHEDULER. Once of the enhancements of this tool is its ability to run external jobs i.e. shell scripts. Find out more.
- Since Oracle 11g R1 external tables support pre-processor scripts, which run shell commands before querying the table. Find out more.
- 我们可以编写一个 Java 存储过程来执行 shell 命令。这是一个古老的解决方案，自 Oracle 8i 以来一直存在。 了解更多。
- 在 10g Oracle 中，将 DBMS_JOB 替换为 DBMS_SCHEDULER。该工具的一个增强功能是它能够运行外部作业，即 shell 脚本。了解更多。
- 由于 Oracle 11g R1 外部表支持预处理器脚本，它在查询表之前运行 shell 命令。了解更多。
Note that all these options demand elevated access (grants on DIRECTORY objects, security credentials, etc). These can only be granted by privileged users (i.e. DBAs). Unless our database has an astonishingly lax security configuration there is no way for us to run an arbitrary shell script from PL/SQL.
请注意，所有这些选项都需要提升访问权限（授予 DIRECTORY 对象、安全凭证等）。这些只能由特权用户（即 DBA）授予。除非我们的数据库有一个惊人的宽松的安全配置，否则我们无法从 PL/SQL 运行任意的 shell 脚本。
Finally, it is not clear what benefit you expect from running a SQL script in PL/SQL. Remember that PL/SQL runs on the database server, so it can't see scripts on the client machine. This seems relevant in the light of the requirement to accept user input.
最后，不清楚在 PL/SQL 中运行 SQL 脚本有什么好处。请记住，PL/SQL 在数据库服务器上运行，因此它看不到客户端计算机上的脚本。鉴于接受用户输入的要求，这似乎是相关的。
Perhaps the simplest solution is reconfiguration of the original script. Split out the necessary PL/SQL call into a block and then just call the named script:
也许最简单的解决方案是重新配置原始脚本。将必要的 PL/SQL 调用拆分成一个块，然后只调用命名脚本：
begin
proc(para1,para2);
end;
/
@prompt1.sql
回答by J Merritt
You can write a pl/sql block in SqlPlus to check for a parameter from a table then execute a script. In the script to be executed (MyScript.sql below), the statement terminators must be ";" instead of "/"
您可以在 SqlPlus 中编写一个 pl/sql 块来检查表中的参数，然后执行脚本。在要执行的脚本（下面的MyScript.sql）中，语句终止符必须是“;” 代替 ”/”
declare
vMyParameter number := 0;
begin
select count(*) into vMyParameter
from MyTable
where MyCheckValue = 'Y';
if vMyParameter = 1 then
@MyFolder/MyScript.sql;
end if;
end;
/
回答by A.B.Cade
If you're using sql*plus (or a tool that is using it) then you can do something like this:
如果您正在使用 sql*plus （或正在使用它的工具），那么您可以执行以下操作：
set serveroutput on
variable a number;
begin
:a := &promt;
dbms_output.put_line(:a);
end;
/
If it runs in batch then you can do:
如果它批量运行，那么您可以执行以下操作：
variable a number;
begin
:a := &1;
dbms_output.put_line(:a);
end;
and get the value for :a as a parameter-
并获取 :a 的值作为参数-
sqlplus sdad/fdsfd@fdggd @<your_script.sql> <val_for_a>
回答by victor
Another practice is to execute on one *.batwith parameters, like:
另一种做法是*.bat使用参数执行，例如：
Example c:/oracle/bin/sqlplus.exe -w @c:/name
sql %1 %2 @c:/output.sql
示例 c:/oracle/bin/sqlplus.exe -w @c:/name
sql %1 %2 @c:/output.sql
回答by Sathyajith Bhat
execute immediate 'start prompt1' ;
立即执行 'start prompt1' ;
Execute immediate is to execute SQL statements , not arbitrary commands.
立即执行是执行 SQL 语句，而不是任意命令。
can i get a value from prompt1 into my PL/SQL block where am calling the script
我可以从 prompt1 获取一个值到我调用脚本的 PL/SQL 块中吗
You can run a run script - but I doubt you can capture input from an SQL script, esp within a PL/SQL block
您可以运行运行脚本 - 但我怀疑您是否可以从 SQL 脚本中捕获输入，尤其是在 PL/SQL 块中
