如何从 pl sql 过程运行 sql 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4772500/
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 run sql scripts from a pl sql procedure
提问by Aniket
I have a procedure like :
我有一个程序，如：
CREATE OR REPLACE PROCEDURE test is
BEGIN
DBMS_OUTPUT.PUT_LINE('This is a Test');
END;
I want to run some sql scripts stored in the current directory. I could run them from sqlplus with '@scriptname.sql' but how can i do it from inside the procedure ? For ex:
我想运行一些存储在当前目录中的 sql 脚本。我可以使用“@scriptname.sql”从 sqlplus 运行它们，但我如何从程序内部运行它们？例如：
CREATE OR REPLACE PROCEDURE test is
BEGIN
DBMS_OUTPUT.PUT_LINE('This is a Test');
@scriptname.sql
END;
This doesn't seem to work ! Is there a specific to run sql scripts from pl/sql procedures ?
这似乎不起作用！是否有特定的从 pl/sql 程序运行 sql 脚本？
回答by DCookie
You can't, in general, because the pl/sql is run in the database, on the server, and sqlplus is a client process. The server can't rely on even being on the same system as the client and its files, much less knowing anything about how to find the file the client is referring to. Even if the syntax were supported (and it isn't), your sql script would have to be on the server, in a location the server knew about and had access to.
一般来说，你不能，因为pl/sql是在数据库中，在服务器上运行的，而sqlplus是一个客户端进程。服务器甚至不能依赖于与客户端及其文件在同一系统上，更不用说如何找到客户端所指的文件了。即使支持语法（并且不支持），您的 sql 脚本也必须在服务器上，在服务器知道并可以访问的位置。
回答by Jeffrey Kemp
Actually, you cando this in SQL*Plus - you just need to ensure the @ is the first character on the line, e.g.:
实际上，您可以在 SQL*Plus 中执行此操作 - 您只需要确保 @ 是该行的第一个字符，例如：
CREATE OR REPLACE PROCEDURE test is
BEGIN
DBMS_OUTPUT.PUT_LINE('This is a Test');
@scriptname.sql
END;
SQL*Plus will read the entire contents of the script and insert it at that point in the procedure, then create the procedure as it is given. That means you can't have SQL*Plus commands in scriptname.sql. Also, there won't be any reference to @scriptname.sql in the actual procedure created on the database.
SQL*Plus 将读取脚本的全部内容并将其插入到过程中的那个点，然后按照给定的方式创建过程。这意味着您不能在scriptname.sql. 此外，在数据库上创建的实际过程中不会有任何对 @scriptname.sql 的引用。
回答by Karl
You could execute an OS command to start SQLPlus and have that execute the scripts. You can pass a filename into SQLplus at start up and it will execute it.
您可以执行操作系统命令来启动 SQLPlus 并让其执行脚本。您可以在启动时将文件名传递给 SQLplus，它会执行它。
Google External Procedures and extproc or this article. Or something like call OS command with Java
谷歌外部程序和 extproc 或这篇文章。或者类似用 Java 调用 OS 命令
回答by Wolfram
Even if there should be a solution, I would not recommend to to this. A PL/SQL procedure basically is a SQL script. Either 1. run your SQL scripts from outside the database, e.g. via shell script or 2. move the SQL code inside your procedure.
即使应该有解决方案，我也不建议这样做。PL/SQL 过程基本上是一个 SQL 脚本。1. 从数据库外部运行您的 SQL 脚本，例如通过 shell 脚本或 2. 将 SQL 代码移动到您的过程中。
回答by Bryan
You could write a Java Stored Procedureto open the file and return its contents as a String and then call Execute Immediateon the String.
您可以编写一个Java 存储过程来打开文件并将其内容作为字符串返回，然后对该字符串调用立即执行。
Be VERY CAREFUL doing this though as any malicious sql in those files can do pretty much whatever it wants.
这样做时要非常小心，因为这些文件中的任何恶意 sql 几乎可以为所欲为。
