什么时候应该在 Oracle 数据库中使用 java 存储过程......有什么缺点？

When should you use java stored procedures with an Oracle database ... what are the drawbacks?

提问by minty

PL/SQL is not my native tongue. Oracle supports writing stored procedures in Java. What are the advantages of doing this over writing the stored procedures in PL/SQL

PL/SQL 不是我的母语。Oracle 支持用 Java 编写存储过程。与在 PL/SQL 中编写存储过程相比，这样做有什么优势？

回答by Rob Paterson

In the Oracle world the general order of development should be:

在 Oracle 世界中，一般的开发顺序应该是：

Wherever possible do it purely with SQL. If you need more than SQL do it with PL/SQL. If you need something that PL/SQL can't do, then use Java. If all else fails use C. If you can't do it with C, back slowly away from the problem....

在可能的情况下，完全使用 SQL。如果您需要的不仅仅是 SQL，请使用 PL/SQL。如果你需要一些 PL/SQL 不能做的事情，那就使用 Java。如果所有其他方法都失败了，请使用 C。如果您不能使用 C，请慢慢远离问题......

PL/SQL stored procedures are an excellent way of moving your business logic to a layer that will be accessible by any integration technology. Business Logic in a Package (don't write stand alone Functions and Procedures - they'll grow over time in an unmanageable way) can be executed by Java, C#, PL/SQL, ODBC and so on.

PL/SQL 存储过程是将业务逻辑移动到任何集成技术均可访问的层的绝佳方式。包中的业务逻辑（不要编写独立的函数和过程——它们会随着时间的推移以无法管理的方式增长）可以由 Java、C#、PL/SQL、ODBC 等执行。

PL/SQL is the fastest way to throw around huge chunks of data outside of pure SQL. The "Bulk Binding" features means it works very well with the SQL engine.

PL/SQL 是在纯 SQL 之外抛出大量数据的最快方法。“批量绑定”功能意味着它可以很好地与 SQL 引擎配合使用。

Java stored procedures are best for creating functionality that interacts with network or operating system. Examples would be, sending emails, FTP'ing data, outputting to text files and zipping it up, executing host command lines in general.

Java 存储过程最适合创建与网络或操作系统交互的功能。例如，发送电子邮件、FTP 数据、输出到文本文件并将其压缩、执行主机命令行。

I've never had to code up any C when working with Oracle, but presumably it could be used for integrating with legacy apps.

在使用 Oracle 时，我从未需要编写任何 C 代码，但据推测它可以用于与遗留应用程序集成。

回答by Matthew Watson

Only when you can't do it in PL/SQL ( or PL/SQL proves to be too slow, which would be pretty rare I believe ).

仅当您无法在 PL/SQL 中执行此操作时（或者 PL/SQL 被证明太慢，我相信这种情况很少见）。

As a case study... We had a single java stored procedure running in production ( Oracle 9i ), it was originally written in java because at the time we thought java was cool, Something I've long since changed my mind about. Anyway. One day, the DB crashes, after it reboots the java SP doesn't work. After much back and forth with oracle support, they don't really know what the problem is and the only suggestions they have involve much downtime. Something which wasn't an option. 30 minutes later I had rewritten the java SP in PL/SQL.

作为一个案例研究......我们有一个在生产中运行的 Java 存储过程（Oracle 9i），它最初是用 Java 编写的，因为当时我们认为 Java 很酷，我早就改变了主意。反正。有一天，数据库崩溃了，在它重新启动后，java SP 不起作用。经过与 oracle 支持的多次反复之后，他们并不真正知道问题是什么，他们提出的唯一建议涉及大量停机时间。不是一个选项的东西。30 分钟后，我用 PL/SQL 重写了 java SP。

It now, runs faster, is oracle "native" , shares the same deployment process as other objects and is easier to debug.

现在，它运行得更快，是 oracle “本机” ，与其他对象共享相同的部署过程，并且更容易调试。

PL/SQL is a very capable language. If you are writing Stored Procedures, please take the time to learn it rather than just doing things in java because thats what you know.

PL/SQL 是一种非常有能力的语言。如果您正在编写存储过程，请花时间学习它，而不是仅仅用 Java 做事，因为这就是您所知道的。

回答by darreljnz

The main advantage is access to the API's and language features not found in PL/SQL. For example, I have used them for regular expression processing, file/directory manipulation and XML parsing.

主要优点是可以访问 PL/SQL 中没有的 API 和语言功能。例如，我将它们用于正则表达式处理、文件/目录操作和 XML 解析。

There are a number of disadvantages:

有许多缺点：

• Poor tool support
• Lack of control over the JVM
• DBA's often aren't trained in Java. In order to support your production code you'll either either need to give your DBA's more training or hire Java-trained support staff

• 工具支持差
• 缺乏对 JVM 的控制
• DBA 通常没有接受过 Java 培训。为了支持您的生产代码，您要么需要为 DBA 提供更多培训，要么聘请经过 Java 培训的支持人员

Moving the Java to an application server is often a better approach as this counteracts the disadvantages. There is excellent tool support, great control over the JVM and there are heaps of people trained up in the popular application servers so finding support staff is easy. There is the opportunity cost of the performance hit moving away from the database but keeping Java close to the database doesn't give you great performance gains anyway.

将 Java 迁移到应用服务器通常是一种更好的方法，因为这可以抵消缺点。有出色的工具支持，对 JVM 的强大控制，并且有很多人在流行的应用程序服务器中接受过培训，因此很容易找到支持人员。远离数据库会带来性能损失的机会成本，但让 Java 靠近数据库并不会给您带来很大的性能提升。

You definitely need a reason to use Java in the database over a) PL/SQL stored procedures or b) Java outside the database.

您肯定需要一个理由在数据库中使用 Java，而不是 a) PL/SQL 存储过程或 b) 在数据库外使用 Java。

回答by dogbane

Java makes it possible to write database-neutral code. It allows you to reuse existing code and dramatically increase productivity.

Java 使编写与数据库无关的代码成为可能。它允许您重用现有代码并显着提高生产力。

One thing I find Java Stored Procedures useful for is File IO. Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on, as compared to Oracle's UTL_FILE package.

我发现 Java 存储过程有用的一件事是文件 IO。与 Oracle 的 UTL_FILE 包相比，Java 具有更丰富的文件 IO 功能集，允许开发人员删除文件、添加目录等。

回答by Telcontar

I have used Oracle emmbedded java for two problems:

我使用 Oracle 嵌入式 java 解决了两个问题：

1) To do a PLSQL procedure which bulks the results of a query in a text file and send it over FTP. This file was very large and i use Java to Zip it.

1) 执行 PLSQL 过程，将查询结果批量存储在文本文件中并通过 FTP 发送。这个文件非常大，我使用 Java 压缩它。

2) In a client-server aplication with direct connection with the DB, to compare the user sent password to the application (not the DB user password) hashed with MD5, so that the password not travel by the net in plain text. I'm not sure if this was the better solution for this problem, i'm going to ask it now. :)

2）在与DB直接连接的客户端-服务器应用程序中，将用户发送的密码与用MD5散列的应用程序（不是DB用户密码）进行比较，使密码不会以明文形式通过网络传播。我不确定这是否是这个问题的更好解决方案，我现在要问它。:)

回答by Steve Bosman

Advantages:

好处：

• Can share identical application logic in client and database
• Access to the Java API. Watch out for which java version each database supports - I believe 10g only supports 1.4 (which means at my work we have to be very careful since our main codebase has recently moved to 1.5).

• 可以在客户端和数据库中共享相同的应用程序逻辑
• 访问 Java API。注意每个数据库支持哪个 java 版本 - 我相信 10g 只支持 1.4（这意味着在我的工作中我们必须非常小心，因为我们的主要代码库最近移到了 1.5）。

Disadvantages:

缺点：

• Java stored procedures doing lots of database access can be quite slow
• Harder to deploy your code

• 执行大量数据库访问的 Java 存储过程可能会很慢
• 更难部署你的代码

回答by Scott A

Use Java when you absolutely cannot do it in PL/SQL, orif Java will allow you greater performance.

当您绝对不能在 PL/SQL 中使用 Java 时，或者如果 Java 允许您获得更高的性能，请使用 Java。

As an example, if you wish to use sockets within a PL/SQL program (logging, external calls, etc), you can:

例如，如果您希望在 PL/SQL 程序（日志记录、外部调用等）中使用套接字，您可以：

1. write a PL/SQL client that uses UTL_TCP. There is no way to do UDP using only native PL/SQL though.
2. write a Java client that uses TCP or UDP sockets.

1. 编写一个使用 UTL_TCP 的 PL/SQL 客户端。但是，仅使用本机 PL/SQL 无法执行 UDP。
2. 编写一个使用 TCP 或 UDP 套接字的 Java 客户端。

In the first case, you have a synchronous socket that can back up your PL/SQL calls if the remote service has issues. Additionally, if you are using dbms_session.reset_package (as in OWA), you will have to reconnect the socket for every request, which is very expensive.

在第一种情况下，您有一个同步套接字，如果远程服务出现问题，它可以备份您的 PL/SQL 调用。此外，如果您使用 dbms_session.reset_package（如在 OWA 中），您将不得不为每个请求重新连接套接字，这非常昂贵。

In the second case, TCP is still synchronous, but if you need asynchronous, non-blocking behavior you can use UDP. Additionally, reset_package does not reset Java TCP or UDP sockets, so you won't need to deal with tear-down/reconnect pain.

在第二种情况下，TCP 仍然是同步的，但是如果您需要异步的、非阻塞的行为，您可以使用 UDP。此外，reset_package 不会重置 Java TCP 或 UDP 套接字，因此您无需处理拆卸/重新连接的痛苦。

回答by Scott A

The answer is NEVER. If you need to write programs to load or process data you need to do it outside of your data tier from another computer on the network.

答案是永远不会。如果您需要编写程序来加载或处理数据，则需要在数据层之外从网络上的另一台计算机执行此操作。

Running external applications directly on your data tier or god forbid in-process with your data tier or the misapplication of external languages when native query languages are a better fit for the job at hand are fine and perfectly acceptable for a small scale custom in-house application. They simply have no place outside of that arena.

直接在您的数据层上运行外部应用程序或禁止在您的数据层进行进程内或当本机查询语言更适合手头的工作时错误应用外部语言是很好的，并且对于小规模的内部定制来说是完全可以接受的应用。他们在那个舞台之外根本没有位置。