Listing 2：   

  SQL> @firstclass.java.sql 

                  Java created.

                  Function created.

So now we have our class loaded in the OracleJVM, and we also have a function that maps to our class's static method. So let's call that function and see what happens:

listing 3：

SQL> select firstclass_greeting('Bruce') from dual; FIRSTCLASS_GREETING('BRUCE') -------------------------------------------------------------------------------- Hello Bruce!

Creating the Book Reorder Trigger

      

drop table publisher_supply_orders
/

create table books(
        book_id                 number  primary key,
        publisher_id    number,
        page_count              number,
        author_name             varchar2(50),
        book_title              varchar2(50),
        description             varchar2(500),
        status                  varchar2(10),
        inventory_qty   number
)
/

insert into books values(100, 200, 234, 'Bruce Hopkins', 'Bluetooth for Java', 'great book', 'IN STOCK', 10);
insert into books values(101, 200, 401, 'Sam Jones', 'Living on the East Coast', 'worth every penny', 'IN STOCK', 50);
insert into books values(102, 250, 278, 'Max Jason', 'The South of France', 'a best-seller', 'IN STOCK', 20);

create table publisher_supply_orders(
        book_id                 number,
        publisher_id    number,
        order_quantity  number
)
/

   So, as you can imagine, the business logic for our internal trigger is very 
simple. Whenever the books table is updated, check to see if the 
books.inventory_qty field is less than a particular threshold. If 
the books.inventory_qty is less than the threshold (which I set to 
be 5 books), then reorder the book by inserting a new row in the 
publisher_supply_orders table. At a later time, a batch process can 
read all the entries in the publisher_supply_orders and place the 
actual orders with the publishers at the one time. This way, you can aggregate 
the orders with the individual publishers, which is a lot more efficient 
compared to submitting the orders directly to the publishers for each title one 
at a time. The code located in Listing 5, located below creates the Java class, 
the PL/SQL wrapper, as well as the trigger that the database will use to call 
your Java class's static method.

脚本ReorderTrigger.java.sql:

        create or replace java source named "ReorderTrigger" as

        import java.sql.*;
        import oracle.jdbc.driver.*;
 
        public class ReorderTrigger {

                public static int REORDER_THRESHOLD = 5;
                public static int REORDER_QTY = 25;
 
                public static void reorderBooks(Integer bookID, Integer publisherID, Integer inventoryQty) {

                        if(inventoryQty.intValue() < REORDER_THRESHOLD){
                        
                                try {
                                Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
                                PreparedStatement prep = conn.prepareStatement("insert into publisher_supply_orders values(?,?,?)");
 
                                prep.setInt(1, bookID);
                                prep.setInt(2, publisherID);
                                prep.setInt(3, REORDER_QTY);
 
                                prep.executeUpdate();
                                prep.close();
                                conn.close();

                                } catch (Exception e){

                                }
                        }

                }
        }
/

create or replace procedure procedure_reorderbooks(bookID number, publisherID number, inventoryQty number) as

        language java name 'ReorderTrigger.reorderBooks(java.lang.Integer, java.lang.Integer)';

/

create or replace trigger trigger_reorderbooks
before update on books
for each row
begin
    procedure_reorderbooks(:new.book_id, :new.publisher_id, :new.inventory_qty);
end;
/

    As you can see in Listing 5, when your Java code executes from within the 
OracleJVM you still use all the JDBC classes and paradigms that you would use as 
if your code executed outside of the database. You should notice that the 
connection String that allows you to utilize the internal JDBC driver in the 
OracleJVM is "jdbc:default:connection:". Additionally, you should 
notice that we're creating a procedure as our PL/SQL wrapper for our Java method 
since we're not returning a result from the method call. After you execute the 
ReorderTrigger.java.sql file, you should see the results shown in 
Listing 6, below.

       SQL> @ReorderTrigger.java.sql

          Java created.

          Procedure created.

          Trigger created.

  So now that we have all the components in place for the trigger to execute, 
let's update the books table so that the inventory_qty is less than 
the threshold of 5 books. Figure 4 shows one of the rows of sample data in the 
books table to be modified under the threshold, and Figure 5 shows the new row 
that was inserted in the publisher_supply_orders table 
automatically by our trigger.
Conclusion
Whew! We've covered a lot of material today. The Oracle database is a 
powerful development platform. that not only allows developers to store and query 
relational data, but it also includes a JVM, which enables developers to create 
powerful server-side applications. Trust me, we've only scratched the surface here... Stay tuned for more.