如何从 pl sql 过程运行 sql 脚本
我有一个类似的过程：
CREATE OR REPLACE PROCEDURE test is
BEGIN
DBMS_OUTPUT.PUT_LINE('This is a Test');
END;
我想运行一些存储在当前目录中的sql脚本。 我可以使用“@scriptname.sql”从 sqlplus 运行它们，但是我如何从程序内部执行此操作？例如：
CREATE OR REPLACE PROCEDURE test is
BEGIN
DBMS_OUTPUT.PUT_LINE('This is a Test');
@scriptname.sql
END;
这似乎不起作用！是否有特定的从 pl/sql 程序运行 sql 脚本的方法？
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:
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 ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
一般来说，你不能，因为 pl/sql 是在服务器上的数据库中运行的，而 sqlplus 是一个客户端进程。服务器甚至不能依赖于与客户端及其文件位于同一系统上，更不用说知道如何查找客户端所引用的文件了。即使语法受支持（也不是），您的 sql 脚本也必须位于服务器上、服务器知道并有权访问的位置。
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.
实际上，您可以在 SQL*Plus 中执行此操作 - 您只需确保 @ 是该行的第一个字符，例如：
SQL*Plus 将读取脚本的全部内容并将其插入到过程中的这一点，然后按给定的方式创建过程。这意味着您不能在
scriptname.sql中使用 SQL*Plus 命令。此外，在数据库上创建的实际过程中不会有任何对 @scriptname.sql 的引用。Actually, you can do this in SQL*Plus - you just need to ensure the @ is the first character on the line, e.g.:
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.您可以执行操作系统命令来启动 SQLPlus 并让它执行脚本。您可以在启动时将文件名传递给 SQLplus，它将执行它。
Google 外部过程和 extproc 或本文。或者类似使用Java调用操作系统命令
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.
Google External Procedures and extproc or this article. Or something like call OS command with Java
您可以编写 Java 存储过程 来打开文件并返回将其内容作为字符串，然后调用 在字符串上立即执行。
不过，执行此操作时要非常小心，因为这些文件中的任何恶意 sql 都可以做任何它想做的事情。
You could write a Java Stored Procedure to open the file and return its contents as a String and then call Execute Immediate on the String.
Be VERY CAREFUL doing this though as any malicious sql in those files can do pretty much whatever it wants.
即使应该有解决方案，我也不会推荐这样做。 PL/SQL 过程基本上是一个 SQL 脚本。任何一个
1. 从数据库外部运行 SQL 脚本，例如通过 shell 脚本或
2. 将 SQL 代码移至过程中。
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.