package com.oracle.tutorial.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class StoredProcedureMySQLSample {
private String dbName;
private Connection con;
private String dbms;
public StoredProcedureMySQLSample(Connection connArg, String dbName,
String dbmsArg) {
super();
this.con = connArg;
this.dbName = dbName;
this.dbms = dbmsArg;
}
public void createProcedureRaisePrice() throws SQLException {//新建存储过程
String createProcedure = null;
String queryDrop = "DROP PROCEDURE IF EXISTS RAISE_PRICE";
createProcedure =
"create procedure RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2)) " +
"begin " +
"main: BEGIN " +
"declare maximumNewPrice numeric(10,2); " +
"declare oldPrice numeric(10,2); " +
"select COFFEES.PRICE into oldPrice " +//读取原价格
"from COFFEES " +
"where COFFEES.COF_NAME = coffeeName; " +
"set maximumNewPrice = oldPrice * (1 + maximumPercentage); " +
"if (newPrice > maximumNewPrice) " +
"then set newPrice = maximumNewPrice; " +
"end if; " +
"if (newPrice <= oldPrice) " +
"then set newPrice = oldPrice;" +
"leave main; " +
"end if; " +
"update COFFEES " +
"set COFFEES.PRICE = newPrice " +//写入新价格
"where COFFEES.COF_NAME = coffeeName; " +
"select newPrice; " +
"END main; " +
"end";
Statement stmt = null;
Statement stmtDrop = null;
try {
System.out.println("Calling DROP PROCEDURE");
stmtDrop = con.createStatement();
stmtDrop.execute(queryDrop);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtDrop != null) { stmtDrop.close(); }
}
try {
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}
public void createProcedureGetSupplierOfCoffee() throws SQLException {
String createProcedure = null;
String queryDrop = "DROP PROCEDURE IF EXISTS GET_SUPPLIER_OF_COFFEE";
createProcedure =
"create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40)) " +
"begin " +
"select SUPPLIERS.SUP_NAME into supplierName " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"and coffeeName = COFFEES.COF_NAME; " +
"select supplierName; " +
"end";
Statement stmt = null;
Statement stmtDrop = null;
try {
System.out.println("Calling DROP PROCEDURE");
stmtDrop = con.createStatement();
stmtDrop.execute(queryDrop);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtDrop != null) { stmtDrop.close(); }
}
try {
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}
public void createProcedureShowSuppliers() throws SQLException {
String createProcedure = null;
String queryDrop = "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";
createProcedure =
"create procedure SHOW_SUPPLIERS() " +
"begin " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"order by SUP_NAME; " +
"end";
Statement stmt = null;
Statement stmtDrop = null;
try {
System.out.println("Calling DROP PROCEDURE");
stmtDrop = con.createStatement();
stmtDrop.execute(queryDrop);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtDrop != null) { stmtDrop.close(); }
}
try {
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}
public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg) throws SQLException {
CallableStatement cs = null;
try {
System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE");
cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");//存储过程语句
cs.setString(1, coffeeNameArg);//in的跟以前一样赋值
cs.registerOutParameter(2, Types.VARCHAR);//执行前先注册out参数
cs.executeQuery();//执行
String supplierName = cs.getString(2);//获取结果,out参数为第二个
if (supplierName != null) {
System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);
} else {
System.out.println("\nUnable to find the coffee " + coffeeNameArg);
}
System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
String supplier = rs.getString("SUP_NAME");
String coffee = rs.getString("COF_NAME");
System.out.println(supplier + ": " + coffee);
}
System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
CoffeesTable.viewTable(this.con);
System.out.println("\nCalling the procedure RAISE_PRICE");
cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
cs.setString(1, coffeeNameArg);
cs.setFloat(2, maximumPercentageArg);
cs.registerOutParameter(3, Types.NUMERIC);//inout类型的也要注册
cs.setFloat(3, newPriceArg);
cs.execute();//如果不确定会返回几个ResultSet就用这个
System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));
System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
CoffeesTable.viewTable(this.con);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (cs != null) { cs.close(); }
}
}
关于RowSet和几个不常见类型(略) (mysql没有,oracle有)
用Swing界面展现结果(略)