在JAVA里面如何调用ORCALE数据库里的存储过程(带输入输出参数)的????

[Guizhi]

rt

[Raulgodle]

顶一下

[Raulgodle]

-- create the PL/SQL functions, procedures and packages

CREATE PROCEDURE update_product_price(

p_product_id IN products.id%TYPE,
p_factor IN NUMBER

) AS

product_count INTEGER;

BEGIN

-- count the number of products with the
-- supplied id (should be 1 if the product exists)
SELECT
COUNT(*)
INTO
product_count
FROM
products
WHERE
id = p_product_id;

-- if the product exists (product_count = 1) then
-- update that product's price
IF product_count = 1 THEN
UPDATE
products
SET
price = price * p_factor;
COMMIT;
END IF;

END update_product_price;
/


CREATE FUNCTION update_product_price_func(

p_product_id IN products.id%TYPE,
p_factor IN NUMBER

) RETURN INTEGER AS

product_count INTEGER;

BEGIN

SELECT
COUNT(*)
INTO
product_count
FROM
products
WHERE
id = p_product_id;

-- if the product doesn't exist then return 0,
-- otherwise perform the update and return 1
IF product_count = 0 THEN
RETURN 0;
ELSE
UPDATE
products
SET
price = price * p_factor;
COMMIT;
RETURN 1;
END IF;

END update_product_price_func;
/


-- package ref_cursor_package illustrates the use of the
-- REF CURSOR type
CREATE OR REPLACE PACKAGE ref_cursor_package AS

TYPE t_ref_cursor IS REF CURSOR;
FUNCTION get_products_ref_cursor RETURN t_ref_cursor;

END ref_cursor_package;
/

CREATE PACKAGE BODY ref_cursor_package AS

-- function get_products_ref_cursor() returns a REF CURSOR
FUNCTION get_products_ref_cursor
RETURN t_ref_cursor IS

products_ref_cursor t_ref_cursor;

BEGIN

-- get the REF CURSOR
OPEN products_ref_cursor FOR
SELECT
id, name, price
FROM
products;

-- return the REF CURSOR
RETURN products_ref_cursor;

END get_products_ref_cursor;

END ref_cursor_package;
/

调用：
/*
PLSQLExample1.java shows how to call a PL/SQL procedure
and function
*/

// import the JDBC packages
import java.sql.*;

public class PLSQLExample1 {

public static void main(String args [])
throws SQLException {

// register the Oracle JDBC drivers
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver()
);

// create a Connection object, and connect to the database
// as store_user using the Oracle JDBC Thin driver
Connection myConnection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:OCL",
"store_user",
"store_password"
);

// disable auto-commit mode
myConnection.setAutoCommit(false);

// create a Statement object
Statement myStatement = myConnection.createStatement();

// display product #1's id and price
System.out.println("Id and original price");
displayProduct(myStatement, 1);

// create a CallableStatement object to call the
// PL/SQL procedure update_product_price()
CallableStatement myCallableStatement = myConnection.prepareCall(
"{call update_product_price(?, ?)}"
);

// bind values to the CallableStatement object's parameters
myCallableStatement.setInt(1, 1);
myCallableStatement.setDouble(2, 1.1);

// execute the CallableStatement object - this increases the price
// for product #1 by 10%
myCallableStatement.execute();
System.out.println("Increased price by 10%");
displayProduct(myStatement, 1);

// call the PL/SQL function update_product_price_func()
myCallableStatement = myConnection.prepareCall(
"{? = call update_product_price_func(?, ?)}"
);

// register the output parameter, and bind values to
// the CallableStatement object's parameters
myCallableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
myCallableStatement.setInt(2, 1);
myCallableStatement.setDouble(3, 0.8);

// execute the CallableStatement object - this decreases the new
// price for product #1 by 20%
myCallableStatement.execute();
int result = myCallableStatement.getInt(1);
System.out.println("Result returned from function = " + result);
System.out.println("Decreased new price by 20%");
displayProduct(myStatement, 1);

// reset the price back to the original value
myStatement.execute(
"UPDATE products " +
"SET price = 19.95" +
"WHERE id = 1"
);
myConnection.commit();
System.out.println("Reset price back to 19.95");

// close the JDBC objects
myCallableStatement.close();
myStatement.close();
myConnection.close();

} // end of main()


public static void displayProduct(
Statement myStatement,
int id
) throws SQLException {

// display the id and price columns
ResultSet productResultSet = myStatement.executeQuery(
"SELECT id, price " +
"FROM products " +
"WHERE id = " + id
);
productResultSet.next();
System.out.println("id = " + productResultSet.getInt("id"));
System.out.println("price = " + productResultSet.getDouble("price"));

productResultSet.close();

} // end of displayProduct()

}

[mousefog]

这个我做过，不过我是在一个操作类中调用的，这个操作类负责对数据的
insert,update,select,delete
insert的参数一般和表的字段数相同，如果有序列的话，可以少一个参数
其他的三个一般都按主键来操作
想要代码，留e_mail给我，我发给你

[Koham]

使用CallableStatement这个Interface来做，输入的参数和PreparedStatement相同，输出的需要使用registerOutParameter(int parameterIndex, int sqlType) 注册后取出。

[Guizhi]

能给个例子吗?

[wuyaxlz]

有没有参数传出呀？？？？

[viano]

jsp从入门到精通 要有实例！