在JAVA里面如何调用ORCALE数据库里的存储过程(带输入输出参数)的????
来源:互联网 发布:9.3.5越狱优化 编辑:程序博客网 时间:2024/05/16 12:35
在JAVA里面如何调用ORCALE数据库里的存储过程(带输入输出参数)的????
rt 问题点数:100、回复次数:7Top
1 楼Koham(永恒)回复于 2004-04-02 12:07:08 得分 25
使用CallableStatement这个Interface来做,输入的参数和PreparedStatement相同,输出的需要使用registerOutParameter(int parameterIndex, int sqlType) 注册后取出。Top
2 楼Guizhi(一星一星。。。还是一星。。。从此努力升星)回复于 2004-04-02 12:43:20 得分 0
能给个例子吗?
Top
3 楼Raulgodle(赵蓓)回复于 2004-04-02 17:14:40 得分 1
顶一下Top
4 楼Raulgodle(赵蓓)回复于 2004-04-02 21:21:50 得分 60
-- 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()
}Top
5 楼mousefog(IT老鼠)回复于 2004-04-02 23:20:15 得分 12
这个我做过,不过我是在一个操作类中调用的,这个操作类负责对数据的
insert,update,select,delete
insert的参数一般和表的字段数相同,如果有序列的话,可以少一个参数
其他的三个一般都按主键来操作
想要代码,留e_mail给我,我发给你Top
6 楼wuyaxlz(芒果苯苯)回复于 2004-04-24 14:38:17 得分 1
有没有参数传出呀????Top
7 楼viano(优秀是一种习惯!)回复于 2004-04-24 15:02:57 得分 1
jsp从入门到精通 要有实例!
- 在JAVA里面如何调用ORCALE数据库里的存储过程(带输入输出参数)的????
- hibernate 调用带输入输出参数的存储过程
- C#调用带输入输出参数的存储过程
- Java代码调用数据库带输出参数的存储过程
- 在java的hibernate3框架中调用oracle数据库中带游标参数的存储过程
- 在Java中调用带参数的存储过程
- 在Java中调用带参数的存储过程
- 存储过程如何调用带参数的存储过程
- 使用带输入输出参数的存储过程
- Oracle带输入输出参数的存储过程
- C#里调用带输出参数的存储过程
- Oracle pl/sql编程 21---在java中调用带有输入输出参数的存储过程
- c#带输入输出参数调用存储过程
- shell如何调用带参数的mysql存储过程
- 在mybaits中调用带参数的存储过程
- C#如何在线程里调用带参数的方法
- C#中调用SQL存储过程(带输入输出参数的例子)
- C#中调用SQL存储过程(带输入输出参数的例子)
- Java标准版的EJB Persistence(一上)
- 使用Java扩展XSL
- 不知道多少人存在这个误解:
- Internal .Net Framework Data Provider error 6
- Java标准版的EJB Persistence(一下)
- 在JAVA里面如何调用ORCALE数据库里的存储过程(带输入输出参数)的????
- 中文分词算法
- IWebPartParameters makes Connection between Web Parts easy
- 洋流[Theocean flow]介绍----科普知识
- TranslateMessage
- 计算两个日期之间相差的天数(JS)
- Ajax 顺序图
- “易语言.飞扬”第一个第三方界面库,efgui,作者迷迷66
- 科普知识------地球上的水