Oracle 触发器查询
让我们考虑一个表 STUD 和一个行级触发器是通过 INSERT 查询实现的。我的场景是这样的，每当插入一行时，就会触发一个触发器，它应该访问放置在硬盘中的一些脚本文件，并且最终应该打印结果。那么，这件事有可能吗？如果是，那么这个东西应该以动态形式存在，即如果我们更改脚本文件的内容，那么预言机也应该反映这些更改。
我曾尝试使用外部过程为 java 执行此操作，但对我想要的结果不太满意。
请给出您对这种场景的看法以及实现方式。
Lets consider a Table STUD and a ROW-Level TRIGGER is implemented over INSERT query.. My scenario goes like this, whenever a row is inserted, a trigger is fired and it should access some script file which is placed in the hard disk, and ultimately should print the result. So, is this thing is possible? and if yes, then this thing should exist in dynamic form, i.e. if we change the content of script file, then the oracle should reflect those changes as well.
I have tried doing this for java using External Procedures, but doesn't feel that much satisfied with the result that i wanted.
Kindly give your point-of-view for this kind of scenario and ways that this can be implemented.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
社区维基百科解释了为什么这是一个坏主意。
使用文件进行动态代码是愚蠢的原因。
插入的每一行都必须打开一个文件，读取整个内容，解析它并对结果“做一些事情”。这会很慢。
根据操作系统环境，您可能无法同时打开文件进行读取和写入。因此，您可能会发现必须关闭数据库才能将代码更改“提升”到文件中。
根据操作系统环境，您甚至可能会发现一次只有一个会话可以读取该文件。
根据操作系统
也许触发器会在“保存”过程中读取文件并执行部分代码。
文件安全性将与数据库安全性完全分离，这会带来维护问题。
简而言之，将动态代码存储在表中而不是文件中将是一个巨大的改进。
无论如何都不应该使用动态代码的原因：
动态代码尚未被解析/编译。因此它可能不起作用，并且在执行之前您不会发现。
由于解析开销减少，静态代码比动态代码执行得更好。
由于
杂项。原因
A community wiki for reasons why this is a bad idea.
Reasons why using a file for dynamic code is dumb.
Each row being inserted has to open a file, read the entire contents, parse it and 'do something' with the result. That is going to be SLOW.
Depending on the OS environment, you may not be able to concurrently open the file for reading and writing. So you may find you have to shutdown the database to 'promote' code changes into the file.
Depending on the OS environment, you may even find that only one session can read from the file at a time.
Maybe the trigger will read the file in the middle of a 'save' and execute partial code.
File security will be totally separate from database security, creating a maintenance headache.
In short, storing the dynamic code in a table, rather than a file, would be a MASSIVE improvement.
Reasons why you shouldn't use dynamic code anyway:
Dynamic code hasn't been parsed/compiled. So it may not work and you don't find out until it gets executed.
Static code performs better than dynamic code because of reduced parsing overhead.
Misc. Reasons
从数据库内部执行操作系统的方法有三种。
外部过程，是用 C 或 Java 编写的操作系统库的 PL/SQL 包装器。自 Oracle 8.0 起可用。
模拟
host命令的Java存储过程。这是 JavaRuntime的手工包装。该命令将以oracle操作系统用户的强大权限执行，因此请勿将此过程公开用于一般用途。相反，让一个安全的用户拥有它，并编写存储过程来公开操作系统功能的特定部分（例如，执行mkdir的过程、执行ls的函数）。从 Oracle 8i 开始就可能了。 了解更多。DBMS_SCHEDULER。除了后台数据库作业外，我们还可以使用计划运行的操作系统作业。从 Oracle 10g 开始可用。 了解更多< /a>
在这三个选项中，DBMS_SCHEDULER 可能是最安全的选项。顾名思义，它旨在定时运行程序（如 unix
cron），因此它不适合按需运行（例如通过触发器）。然而，可能不需要触发器就可以满足您的业务逻辑。外部过程不适用于运行 shell 脚本。外部程序是外部的，需要更多的努力来与数据库组件协调。这适用于发布应用程序的新版本和进行备份时。当（例如）开发环境和实时环境位于不同平台上时，它们也可能会很麻烦。
让数据库执行任意代码是一个非常糟糕的主意。在大多数情况下，所需要的只是一种将参数传递给可执行文件的机制。
There are three ways of executing OS from inside the database.
External procedures, which are PL/SQL wrappers to OS libraries written in C or Java. Available since Oracle 8.0. Find out more.
A Java Stored Procedure emulating a
hostcommand. This is a hand-rolled wrapper of a JavaRuntime. This command will execute with the powerful privileges of theoracleOS user, so do not expose this procedure for general use. Instead have a secure user own it, and write stored procedures to expose specific slivers of OS functionality (e.g. a procedure to executemkdir, a function to executels). Possible since Oracle 8i. Find out more.DBMS_SCHEDULER. As well as background database jobs we can use the scheduled to run OS jobs. Available since Oracle 10g. Find out more
Of these three options, DBMS_SCHEDULER is probably the most secure option. As the name suggests it is intended to run programs on a timed basis (like unix
cron), so it is not suitable for running things on demand (say through a trigger). However, it may be that your business logic can be satisfied without the need for a trigger. External procedures are not intended for running shell scripts.External programs, being external, require more effort to co-ordinate with database components. This is applies when releasing new versions of the application, and doing back-ups. They can also be troublesome when (say) the development environment and the live environments are on different platforms.
In is a very bad idea to wrestle the database into executing arbitrary code. In most scenarios all that is needed is a mechanism for passing parameters to the executable.