1、 Dr. Philip Cannata,Data Management,1,First of all, always do this:set serveroutput on size 10000call dbms_java.set_output(10000); If you get something like this ERROR at line 1: ORA-29549: class SCOTT.HelloWorld has changed, Java session state cleared ORA-06512: at “SCOTT.HELLO“, line 0 ORA-06512:
2、at line 1 do this againcall dbms_java.set_output(10000);,Java Stored Procedures, Dr. Philip Cannata,Data Management,2,Hello World method create or replace and compile java source named myProcedureMethods as public class myProcedureMethods public static void helloWorld() System.out.println(“Hello wor
3、ld!“); /,Java Methods in Oracle, Dr. Philip Cannata,Data Management,3,hw stored procedure that calls the helloWorld java method. create or replace procedure hw as language java name myProcedureMethods.helloWorld(); /,Stored Procedure to Call Java Method, Dr. Philip Cannata,Data Management,4,set serv
4、eroutput on size 10000 call dbms_java.set_output(10000); exec hw,Execute Stored Procedure, Dr. Philip Cannata,Data Management,5,Simple JDBC method create or replace and compile java source named myProcedureMethods as import java.util.*; import java.sql.*; public class myProcedureMethods public stati
5、c void helloWorld() System.out.println(“Hello world!“); public static void simpleJDBC() throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“create or replace view empV1 as select * from emp“);pstatement.executeQ
6、uery();System.out.println(“Created view empV1.“); /,Java Methods in Oracle, Dr. Philip Cannata,Data Management,6,sjdbc stored procedure that calls the simpleJDBC java method create or replace procedure sjdbc as language java name myProcedureMethods.simpleJDBC(); /,Stored Procedure to Call Java Metho
7、d, Dr. Philip Cannata,Data Management,7,set serveroutput on size 10000 call dbms_java.set_output(10000); exec sjdbc,Execute Stored Procedure, Dr. Philip Cannata,Data Management,8,JDBC method with an argument create or replace and compile java source named myProcedureMethods as import java.util.*; im
8、port java.sql.*; public class myProcedureMethods public static void helloWorld() System.out.println(“Hello world!“); public static void simpleJDBC() throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“create or
9、replace view empV1 as select * from emp“);pstatement.executeQuery();System.out.println(“Created view emptmp.“); public static void argJDBC(int sal) throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“ update emp
10、 set comm = 0 where sal = ? “);pstatement.setInt(1, sal); pstatement.executeQuery();System.out.println(“Updated emp comm.“); /,Java Methods in Oracle, Dr. Philip Cannata,Data Management,9,argjdbc stored procedure that calls the argJDBC java method create or replace procedure argjdbc(sal number) as l
11、anguage java name myProcedureMethods.argJDBC(int); /,Stored Procedure to Call Java Method, Dr. Philip Cannata,Data Management,10,set serveroutput on size 10000 call dbms_java.set_output(10000); exec argjdbc(800),Execute Stored Procedure, Dr. Philip Cannata,Data Management,11,JDBC method with an argu
12、ment and manipulating the results (see next page) create or replace and compile java source named myProcedureMethods as import java.util.*; import java.sql.*; public class myProcedureMethods public static void helloWorld() System.out.println(“Hello world!“); public static void simpleJDBC() throws SQ
13、LException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“create or replace view empV1 as select * from emp“);pstatement.executeQuery();System.out.println(“Created view emptmp.“);,Java Methods in Oracle, Dr. Philip Cannata,Dat
14、a Management,12,public static void argJDBC(int sal) throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“ update emp set comm = 0 where sal = ? “);pstatement.setInt(1, sal); pstatement.executeQuery();System.out.p
15、rintln(“Updated emp comm.“);public static void fullJDBC(int sal) throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);PreparedStatement pstatement = conn.prepareStatement(“ select * from emp where sal ? “);pstatement.setInt(1, sal); ResultSet rs = pstatement.execute
16、Query();while(rs.next() System.out.println(“Employee number “+ rs.getInt(“empno“) + “ name: “ + rs.getString(“ename“); /,Java Methods in Oracle, Dr. Philip Cannata,Data Management,13,fulljdbc stored procedure that calls the fullJDBC java method create or replace procedure fulljdbc(sal number) as lan
17、guage java name myProcedureMethods.fullJDBC(int); /,Stored Procedure to Call Java Method, Dr. Philip Cannata,Data Management,14,set serveroutput on size 10000 call dbms_java.set_output(10000); exec fulljdbc(800),Execute Stored Procedure, Dr. Philip Cannata,Data Management,15,Triggers, Dr. Philip Can
18、nata,Data Management,16,Java methods for SG Order Trigger create or replace and compile java source named sgTriggerMethods as import java.util.*; import java.sql.*;public class sgTriggerMethodspublic static void changeTotalForItemInsert(String ord_id, int price, int quan) throws SQLException Connect
19、ion conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);String query = “select total from s_ord where id = ? “;PreparedStatement pstatement = conn.prepareStatement(query);pstatement.setString(1, ord_id);ResultSet rs = pstatement.executeQuery();rs.next();int total = rs.getInt(“total“);query = “ u
20、pdate s_ord set total = ? where id = ? “;pstatement = conn.prepareStatement(query);pstatement.setInt(1, (total + (price * quan);pstatement.setString(2, ord_id);pstatement.executeQuery(); /,Triggers, Dr. Philip Cannata,Data Management,17,Stored procedure that calls the changeTotalForItemInsert java m
21、ethod create or replace procedure sgChageTotalForItemInsert(ord_id varchar2, price number, quantity number) as language java name sgTriggerMethods. changeTotalForItemInsert(java.lang.String, int, int); /,Stored Procedure to Call Java Method, Dr. Philip Cannata,Data Management,18,Trigger for changing
22、 order total when item is inserted create or replace trigger sgChageTotalForItemInsert after insert on s_item For each rowcall sgChageTotalForItemInsert(:new.ord_id, :new.price, :new.quantity) /,Execute Stored Procedure, Dr. Philip Cannata,Data Management,19,Java Triggers,To try out the trigger do t
23、he following:drop sg tablescreate sg tablescolumn ord_id format a6select * from s_item where ord_id = 100 order by item_idselect sum (price * quantity) from s_item where ord_id = 100select * from s_ord where id = 100select * from s_product where id not in (select product_id from s_item where ord_id
24、= 100)insert into s_item values(100,8,50536,125,1,null)column ord_id format a6select * from s_item where ord_id = 100 order by item_idselect sum (price * quantity) from s_item where ord_id = 100select * from s_ord where id = 100, Dr. Philip Cannata,Data Management,20,Java Triggers,Think about what i
25、t would take to have triggers for update and delete of items., Dr. Philip Cannata,Data Management,21,Java Triggers,Why cant we just do this? create or replace and compile java source named sgTriggerMethods as import java.util.*; import java.sql.*; public class sgTriggerMethodspublic static void chan
26、geTotalForItemInsert(String ord_id, int price, int quan) throws SQLException Connection conn = DriverManager.getConnection(“jdbc:oracle:kprb:“);String query = “ update s_ord set total = (select sum(price * quantity) from s_item where ord_id = ?) where id = ? “;PreparedStatement pstatement = conn.pre
27、pareStatement(query);pstatement.setString(1, ord_id);pstatement.setString(2, ord_id);pstatement.executeQuery(); /, Dr. Philip Cannata,Data Management,22,Java Triggers,To try out the trigger do the following:drop sg tablescreate sg tablesrecreate the triggerinsert into s_item values(100,8,50536,125,1
28、,null), Dr. Philip Cannata,Data Management,23,Java Triggers The Dreaded Mutating Table Problem,insert into s_item values(100,8,50536,125,1,null) * ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-04091: table SCOTT.S_ITEM is muta
29、ting, trigger/function may not see it ORA-04088: error during execution of trigger SCOTT.ITEM_INSERT_CHANGE_ORDER_TOTAL The problem is a trigger cant make reference to the table being changed. String query = “ update s_ord set total = (select sum(price * quantity) from s_item where ord_id = ?) where id = ? “;,Problem!, Dr. Philip Cannata,Data Management,24,Java Stored Procedures,Write some code,
