In fact, this short article, I should have written a long time ago. Because, java stored procedures in the future in the major database vendors are becoming more and more popular and powerful. Here is an example of the specific usage of java stored procedures with Oracle. iihero on csdn)I. How to create java stored procedures? There are usually three ways to create java stored procedures.
1. Use oracle sql statement to create:
e.g. Use create or replace and compile java source named "" as followed by java source program. It is required that the methods of the class must be public static to be used in stored procedures.
SQL> create or replace and compile java source named "javademo1" 2 as 3 import java.sql.*; 4 public class JavaDemo1 5 { 6 public static void main (String[] argv) 7 { 8 System.out.println("hello, java demo1"); 9 } 10 } 11 /Java has been created. SQL> show errors java source "javademo1" has no errors. SQL> create or replace procedure javademo1 replace procedure javademo1 2 as 3 language java name 'JavaDemo1.main(java.lang.String[])'; 4 /Procedure created. SQL> set serveroutput onSQL> call SQL> call javademo1(); call done. sql> call dbms_java.set_output(5000); call done. sql> call javademo1(); hello, java demo1 call done. sql> call javademo1(); call done. sql> call javademo1(). SQL> call javademo1(); hello, java demo1 call complete.
2. Using an external class file to load the creation e.g. Since an external file is used here, it is inevitable that the class file should be placed in a directory of oracle Server.
public class OracleJavaProc{ public static void main(String[] argv) { System.out.println("It's a Java Oracle procedure."); }}SQL> grant create any directory to scott; authorization succeeded. sql> conn scott/tiger@iihero.oracledb已连接. sql> create or replace directory test_dir as 'd:/oracle'; directory has been SQL> create or replace java class using bfile(test_dir, 'OracleJavaProc.CLASS') 2 /Java has been created. SQL> create or replace procedure testjavaproc as language java name 'OracleJavaProc.main(java.lang.String[])'; 2 /Procedure has been created. sql> call testjavaproc();call completed. sql> execute testjavaproc;pl/ SQL procedure has completed successfully.SQL> set serveroutput on size 5000SQL> call dbms_java.set_output(5000); call completed.SQL> execute testjavaproc;It's a Java Oracle procedure.
3. One of the methods I recommend is to load and create it remotely using the loadjava command. First create a class, e.g.
import java.sql.*;import oracle.jdbc.*;public class OracleJavaProc ... { //Add a salgrade to the database. public static void addSalGrade(int grade, int losal, int hisal) ... { System.out.println("Creating new salgrade for EMPLOYEE...") ; try ... { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "INSERT INTO salgrade " + "(GRADE,LOSAL,HISAL) " + " VALUES(? ,? ,?)" ; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,grade); pstmt.setInt(2,losal); pstmt.setInt(3,hisal); pstmt. executeUpdate(); pstmt.close(); } catch(SQLException e) ... { System.err.println("ERROR! Adding Salgrade: "+ e.getMessage()); } }}
Use the loadjava command to load it server-side and compile it:
D:eclipse3.1workspacedbtest& gt;loadjava -u scott/tiger@iihero.oracledb -v -resolve OracleJavaProc.javaarguments: '-u' 'scott/tiger@iihero.oracledb '-v' '-resolve' ' OracleJavaProc.java'creating : source OracleJavaProcloading : source OracleJavaProcresolving: source OracleJavaProc
Check the status:
Connecting Go to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - Production ProductionSQL> SELECT object_name, object_type, status FROM user_objects WHERE object_type LIKE 'JAVA%';OBJECT_NAME---------------------- ----------------------------------------------------------OBJECT_TYPE STATUS------------------------------------ -------------- OracleJavaProcJAVA CLASS VALIDOracleJavaProcJAVA SOURCE VALID
Test the procedure:
SQL> create or replace procedure add_salgrade(id number, losal number, hisal number) as language java name 'OracleJavaProc.addSalGrade(int, int, int)'; 2 / Procedure created. SQL> set serveroutput on size 2000SQL> call dbms_java.set_output(2000);Call completed. sql> execute add_salgrade(6, 10000, 15000);Creating new salgrade for EMPLOYEE.... . the PL/SQL procedure has completed successfully. sql> select * from salgrade where grade=6; GRADE LOSAL HISAL---------- ---------- ---------- 6 10000 15000
Two, how do you update a java stored procedure that you have written? java stored procedure?
If you want to add a stored procedure method to class OracleJavaProc, how to develop it? The correct steps should be the first dropjava, change the program, and then loadjava. e.g. Modify the OracleJavaProc class content as follows:
import java.sql.*;import oracle.jdbc.*;public class OracleJavaProc ... { // Add a salgrade to the database. public static void addSalGrade(int grade, int losal, int hisal) ... { System.out.println("Creating new salgrade for EMPLOYEE...") ; try ... { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "INSERT INTO salgrade " + "(GRADE,LOSAL,HISAL) " + " VALUES(? ,? ,?)" ; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,grade); pstmt.setInt(2,losal); pstmt.setInt(3,hisal); pstmt. executeUpdate(); pstmt.close(); } catch(SQLException e) ... { System.err.println("ERROR! Adding Salgrade: " + e.getMessage()); } }public static int getHiSal(int grade) ... { try ... { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "SELECT hisal FROM salgrade WHERE grade = ?" ; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, grade); ResultSet rset = pstmt.executeQuery(); int res = 0; if (rset.next( )) ... { res = rset.getInt(1); } rset.close(); return res;
} catch (SQLException e)... { System.err.println("ERROR! Querying Salgrade: " + e.getMessage()); return -1; }} }
How do I update it?
D:eclipse3.1workspacedbtest>dropjava -u scott -v OracleJavaProcD:/tiger@iihero.oracledbeclipse3.1workspacedbtest>loadjava -u scott -v -resolve OracleJavaProc/tiger@iihero.oracledb.javaarguments: '-u' 'scott/tiger@iihero.oracledb' '-v' '-resolve' 'OracleJavaProc .java'creating : source OracleJavaProcloading : source OracleJavaProcresolving: source OracleJavaProc
Example of the latter application:
SQL> create or replace function query_hisal(grade number) return number as language java name 'OracleJavaProc.getHiSal(int) return int'; 2 / function created. sql> set serveroutput on size 2000 SQL> call dbms_java.set_output(2000); call complete. SQL> select query_hisal(5) from dual;QUERY_HISAL(5)-------------- 9999
Full text finished! Usage personal opinion: do not manually drop java source, do not manually drop procedure.