Use Java to write Oracle stored procedures

Use Java to write Oracle stored procedures

Bromon originality, please respect copyright

We usually use PS/SQL to write stored procedures for Oracle. From Oracle 8, we can have another option: Java. Starting from 8.0, Oracle had a built-in JVM with built-in support for Java. You can do anything that Java can do in Oracle. You can even operate DB2 in it. This gives Oracle great scalability. You can develop a trigger to send an instant message to the Administrator's MSN whenever necessary.

 

The good news is over. Now it is bad news: PS/SQL is a procedural language, and it basically does not have the concept of polymorphism. the Java method used by Oracle must be declared as static, therefore, you cannot use the dynamic features of Java in Oracle, such as interfaces and reflection. However, this does not prevent you from using Java for sex.

 

Let's take a simple hello world as an example. I think any experienced Java programmer can use this example to derive other strange applications. If you have good ideas, remember to share them with me, my MSN is bromon@gmail.com

 

First, create a Java class. You can use JBuilder/eclipse to compile the class, or you can directly create it in the Oracle console. Let's not talk about the former. Let's look at the latter method.

 

Start SQL plus and execute the following command:

 

Create or replace and compile Java source
Named "hello_sp"

/**

* Create a Java class for the Oracle Stored Procedure

* @ Author bromon

**/

Package org. bromon. Oracle;

Public class hello

{

 
Public static String say (string name)

{

Return "hello," + name;

}

}

 

Java to create.

 

Then, import the class into a function in Oracle and execute the following command:

Create or replace function hello_sp (name
Varchar2) return varchar2

As language Java name

'Org. bromon. Oracle. Hello. Say (Java. Lang. String)
Return java. Lang. String ';

 

Function created

 

Now you can call this function and execute:

Select Hello ('bromon') from dual;

 

Returned results: Hello, bromon

 

Note that our Java method has no parameters, for example:

Public static String say ()

{

Return "hello ";

}

 

When creating a function, the function name should not have an extension number: hello_sp

Otherwise, a compilation error is reported for the function.

 

If your Java class is written in IDE, you only need to load the compiled class file in Oracle:

 

Start Enterprise
Manage console, find "Source Type" under "solution", right-click "Java class", select "load Java", and select the corresponding class file.