Java存储过程和触发器(24)+.ppt

Dr PhilipCannata DataManagement 1 Firstofall alwaysdothis setserveroutputonsize10000calldbms java set output 10000 IfyougetsomethinglikethisERRORatline1 ORA 29549 classSCOTT HelloWorldhaschanged JavasessionstateclearedORA 06512 at SCOTT HELLO line0ORA 06512 atline1dothisagaincalldbms java set output 10000 JavaStoredProcedures Dr PhilipCannata DataManagement 2 HelloWorldmethodcreateorreplaceandcompilejavasourcenamedmyProcedureMethodsaspublicclassmyProcedureMethods publicstaticvoidhelloWorld System out println Helloworld JavaMethodsinOracle Dr PhilipCannata DataManagement 3 hwstoredprocedurethatcallsthehelloWorldjavamethod createorreplaceprocedurehwaslanguagejavaname myProcedureMethods helloWorld StoredProceduretoCallJavaMethod Dr PhilipCannata DataManagement 4 setserveroutputonsize10000calldbms java set output 10000 exechw ExecuteStoredProcedure Dr PhilipCannata DataManagement 5 SimpleJDBCmethodcreateorreplaceandcompilejavasourcenamedmyProcedureMethodsasimportjava util importjava sql publicclassmyProcedureMethods publicstaticvoidhelloWorld System out println Helloworld publicstaticvoidsimpleJDBC throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement createorreplaceviewempV1asselect fromemp pstatement executeQuery System out println CreatedviewempV1 JavaMethodsinOracle Dr PhilipCannata DataManagement 6 sjdbcstoredprocedurethatcallsthesimpleJDBCjavamethodcreateorreplaceproceduresjdbcaslanguagejavaname myProcedureMethods simpleJDBC StoredProceduretoCallJavaMethod Dr PhilipCannata DataManagement 7 setserveroutputonsize10000calldbms java set output 10000 execsjdbc ExecuteStoredProcedure Dr PhilipCannata DataManagement 8 JDBCmethodwithanargumentcreateorreplaceandcompilejavasourcenamedmyProcedureMethodsasimportjava util importjava sql publicclassmyProcedureMethods publicstaticvoidhelloWorld System out println Helloworld publicstaticvoidsimpleJDBC throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement createorreplaceviewempV1asselect fromemp pstatement executeQuery System out println Createdviewemptmp publicstaticvoidargJDBC intsal throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement updateempsetcomm 0wheresal pstatement setInt 1 sal pstatement executeQuery System out println Updatedempcomm JavaMethodsinOracle Dr PhilipCannata DataManagement 9 argjdbcstoredprocedurethatcallstheargJDBCjavamethodcreateorreplaceprocedureargjdbc salnumber aslanguagejavaname myProcedureMethods argJDBC int StoredProceduretoCallJavaMethod Dr PhilipCannata DataManagement 10 setserveroutputonsize10000calldbms java set output 10000 execargjdbc 800 ExecuteStoredProcedure Dr PhilipCannata DataManagement 11 JDBCmethodwithanargumentandmanipulatingtheresults seenextpage createorreplaceandcompilejavasourcenamedmyProcedureMethodsasimportjava util importjava sql publicclassmyProcedureMethods publicstaticvoidhelloWorld System out println Helloworld publicstaticvoidsimpleJDBC throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement createorreplaceviewempV1asselect fromemp pstatement executeQuery System out println Createdviewemptmp JavaMethodsinOracle Dr PhilipCannata DataManagement 12 publicstaticvoidargJDBC intsal throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement updateempsetcomm 0wheresal pstatement setInt 1 sal pstatement executeQuery System out println Updatedempcomm publicstaticvoidfullJDBC intsal throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb PreparedStatementpstatement conn prepareStatement select fromempwheresal pstatement setInt 1 sal ResultSetrs pstatement executeQuery while rs next System out println Employeenumber rs getInt empno name rs getString ename JavaMethodsinOracle Dr PhilipCannata DataManagement 13 fulljdbcstoredprocedurethatcallsthefullJDBCjavamethodcreateorreplaceprocedurefulljdbc salnumber aslanguagejavaname myProcedureMethods fullJDBC int StoredProceduretoCallJavaMethod Dr PhilipCannata DataManagement 14 setserveroutputonsize10000calldbms java set output 10000 execfulljdbc 800 ExecuteStoredProcedure Dr PhilipCannata DataManagement 15 Triggers Dr PhilipCannata DataManagement 16 JavamethodsforSGOrderTriggercreateorreplaceandcompilejavasourcenamedsgTriggerMethodsasimportjava util importjava sql publicclasssgTriggerMethods publicstaticvoidchangeTotalForItemInsert Stringord id intprice intquan throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb Stringquery selecttotalfroms ordwhereid PreparedStatementpstatement conn prepareStatement query pstatement setString 1 ord id ResultSetrs pstatement executeQuery rs next inttotal rs getInt total query updates ordsettotal whereid pstatement conn prepareStatement query pstatement setInt 1 total price quan pstatement setString 2 ord id pstatement executeQuery Triggers Dr PhilipCannata DataManagement 17 StoredprocedurethatcallsthechangeTotalForItemInsertjavamethodcreateorreplaceproceduresgChageTotalForItemInsert ord idvarchar2 pricenumber quantitynumber aslanguagejavaname sgTriggerMethods changeTotalForItemInsert java lang String int int StoredProceduretoCallJavaMethod Dr PhilipCannata DataManagement 18 TriggerforchangingordertotalwhenitemisinsertedcreateorreplacetriggersgChageTotalForItemInsertafterinsertons itemForeachrowcallsgChageTotalForItemInsert new ord id new price new quantity ExecuteStoredProcedure Dr PhilipCannata DataManagement 19 JavaTriggers Totryoutthetriggerdothefollowing dropsgtablescreatesgtablescolumnord idformata6select froms itemwhereord id 100orderbyitem idselectsum price quantity froms itemwhereord id 100select froms ordwhereid 100select froms productwhereidnotin selectproduct idfroms itemwhereord id 100 insertintos itemvalues 100 8 50536 125 1 null columnord idformata6select froms itemwhereord id 100orderbyitem idselectsum price quantity froms itemwhereord id 100select froms ordwhereid 100 Dr PhilipCannata DataManagement 20 JavaTriggers Thinkaboutwhatitwouldtaketohavetriggersforupdateanddeleteofitems Dr PhilipCannata DataManagement 21 JavaTriggers Whycan twejustdothis createorreplaceandcompilejavasourcenamedsgTriggerMethodsasimportjava util importjava sql publicclasssgTriggerMethods publicstaticvoidchangeTotalForItemInsert Stringord id intprice intquan throwsSQLException Connectionconn DriverManager getConnection jdbc oracle kprb Stringquery updates ordsettotal selectsum price quantity froms itemwhereord id whereid PreparedStatementpstatement conn prepareStatement query pstatement setString 1 ord id pstatement setString 2 ord id pstatement executeQuery Dr Ph