ORACLE存儲(chǔ)過(guò)程怎么調(diào)用？

PL/SQL?SQL窗口?輸入存儲(chǔ)過(guò)程名，右鍵測(cè)試（英文版為test）,出現(xiàn)測(cè)試界面，輸入對(duì)應(yīng)參數(shù)后F8，如果沒(méi)有參數(shù)直接F8.

我們提供的服務(wù)有：成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、寶豐ssl等。為上千企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的寶豐網(wǎng)站制作公司

java怎么調(diào)用oracle的過(guò)程

參考下面代碼

java下實(shí)現(xiàn)調(diào)用oracle的存儲(chǔ)過(guò)程和函數(shù)

在oracle下創(chuàng)建一個(gè)test的賬戶，然后按一下步驟執(zhí)行：

1.創(chuàng)建表：STOCK_PRICES

--創(chuàng)建表格

CREATE TABLE STOCK_PRICES(

RIC VARCHAR(6) PRIMARY KEY,

PRICE NUMBER(7,2),

UPDATED DATE );

2.插入測(cè)試數(shù)據(jù)：

--插入數(shù)據(jù)

INSERT INTO stock_prices values('1111',1.0,SYSDATE);

INSERT INTO stock_prices values('1112',2.0,SYSDATE);

INSERT INTO stock_prices values('1113',3.0,SYSDATE);

INSERT INTO stock_prices values('1114',4.0,SYSDATE);

3.建立一個(gè)返回游標(biāo)： PKG_PUB_UTILS

--建立一個(gè)返回游標(biāo)

CREATE OR REPLACE PACKAGE PKG_PUB_UTILS IS

--動(dòng)態(tài)游標(biāo)

TYPE REFCURSOR IS REF CURSOR;

END PKG_PUB_UTILS;

4.創(chuàng)建和存儲(chǔ)過(guò)程：P_GET_PRICE

--創(chuàng)建存儲(chǔ)過(guò)程

CREATE OR REPLACE PROCEDURE P_GET_PRICE

(

AN_O_RET_CODE OUT NUMBER,

AC_O_RET_MSG OUT VARCHAR2,

CUR_RET OUT PKG_PUB_UTILS.REFCURSOR,

AN_I_PRICE IN NUMBER

)

IS

BEGIN

AN_O_RET_CODE := 0;

AC_O_RET_MSG := '操作成功';

OPEN CUR_RET FOR

SELECT * FROM STOCK_PRICES WHERE PRICEAN_I_PRICE;

EXCEPTION

WHEN OTHERS THEN

AN_O_RET_CODE := -1;

AC_O_RET_MSG := '錯(cuò)誤代碼:' || SQLCODE || CHR(13) || '錯(cuò)誤信息:' || SQLERRM;

END P_GET_PRICE;

5.創(chuàng)建函數(shù)：

--創(chuàng)建函數(shù)：F_GET_PRICE

CREATE OR REPLACE FUNCTION F_GET_PRICE(v_price IN NUMBER)

RETURN PKG_PUB_UTILS.REFCURSOR

AS

stock_cursor PKG_PUB_UTILS.REFCURSOR;

BEGIN

OPEN stock_cursor FOR

SELECT * FROM stock_prices WHERE price v_price;

RETURN stock_cursor;

END;

6.JAVA調(diào)用存儲(chǔ)過(guò)程返回結(jié)果集

代碼示例：JDBCoracle10G_INVOKEPROCEDURE.java

import java.sql.*;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

/* 本例是通過(guò)調(diào)用oracle的存儲(chǔ)過(guò)程來(lái)返回結(jié)果集:

* oracle 9i、10G 的jdbc由1個(gè)jar包組成：classes12.zip

*/

public class JDBCoracle10G_INVOKEPROCEDURE {

Connection conn = null;

Statement statement = null;

ResultSet rs = null;

CallableStatement stmt = null;

String driver;

String url;

String user;

String pwd;

String sql;

String in_price;

public JDBCoracle10G_INVOKEPROCEDURE()

{

driver = "oracle.jdbc.driver.OracleDriver";

url = "jdbc:oracle:thin:@localhost:1521:ORCL";

// oracle 用戶

user = "test";

// oracle 密碼

pwd = "test";

init();

// mysid：必須為要連接機(jī)器的sid名稱，否則會(huì)包以下錯(cuò)：

// java.sql.SQLException: Io 異常: Connection

// refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

// 參考連接方式:

// Class.forName( "oracle.jdbc.driver.OracleDriver" );

// cn = DriverManager.getConnection(

// "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );

}

public void init() {

System.out.println("oracle jdbc test");

try {

Class.forName(driver);

System.out.println("driver is ok");

conn = DriverManager.getConnection(url, user, pwd);

System.out.println("conection is ok");

statement = conn.createStatement();

// conn.setAutoCommit(false);

// 輸入?yún)?shù)

in_price = "3.0";

// 調(diào)用函數(shù)

stmt = conn.prepareCall("call P_GET_PRICE(?,?,?,?)");

stmt.registerOutParameter(1, java.sql.Types.FLOAT);

stmt.registerOutParameter(2, java.sql.Types.CHAR);

stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);

stmt.setString(4, in_price);

stmt.executeUpdate();

int retCode = stmt.getInt(1);

String retMsg = stmt.getString(2);

if (retCode == -1) { // 如果出錯(cuò)時(shí)，返回錯(cuò)誤信息

System.out.println("報(bào)錯(cuò)！");

} else {

// 取的結(jié)果集的方式一：

rs = ((OracleCallableStatement) stmt).getCursor(3);

// 取的結(jié)果集的方式二：

// rs = (ResultSet) stmt.getObject(3);

String ric;

String price;

String updated;

// 對(duì)結(jié)果進(jìn)行輸出

while (rs.next()) {

ric = rs.getString(1);

price = rs.getString(2);

updated = rs.getString(3);

System.out.println("ric:" + ric + ";-- price:" + price

+ "; --" + updated + "; ");

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

System.out.println("close ");

}

}

public static void main(String args[])// 自己替換［］

{

new JDBCoracle10G_INVOKEPROCEDURE();

}

}

7.開(kāi)發(fā)JAVA調(diào)用函數(shù)返回結(jié)果集

代碼示例：JDBCoracle10G_INVOKEFUNCTION.java

import java.sql.*;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

/*

/* 本例是通過(guò)調(diào)用oracle的函數(shù)來(lái)返回結(jié)果集:

* oracle 9i、10G 的jdbc由1個(gè)jar包組成：classes12.zip

*/

public class JDBCoracle10G_INVOKEFUNCTION {

Connection conn = null;

Statement statement = null;

ResultSet rs = null;

CallableStatement stmt = null;

String driver;

String url;

String user;

String pwd;

String sql;

String in_price;

public JDBCoracle10G_INVOKEFUNCTION()

{

driver = "oracle.jdbc.driver.OracleDriver";

url = "jdbc:oracle:thin:@localhost:1521:ORCL";

// oracle 用戶

user = "test";

// oracle 密碼

pwd = "test";

init();

// mysid：必須為要連接機(jī)器的sid名稱，否則會(huì)包以下錯(cuò)：

// java.sql.SQLException: Io 異常: Connection

// refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

// 參考連接方式:

// Class.forName( "oracle.jdbc.driver.OracleDriver" );

// cn = DriverManager.getConnection(

// "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );

}

public void init() {

System.out.println("oracle jdbc test");

try {

Class.forName(driver);

System.out.println("driver is ok");

conn = DriverManager.getConnection(url, user, pwd);

System.out.println("conection is ok");

statement = conn.createStatement();

// conn.setAutoCommit(false);

// 輸入?yún)?shù)

in_price = "5.0";

// 調(diào)用函數(shù)

stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");

// stmt.registerOutParameter(1, java.sql.Types.FLOAT);

// stmt.registerOutParameter(2, java.sql.Types.CHAR);

stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

stmt.setString(2, in_price);

stmt.executeUpdate();

// 取的結(jié)果集的方式一：

rs = ((OracleCallableStatement) stmt).getCursor(1);

// 取的結(jié)果集的方式二：

// rs = (ResultSet) stmt.getObject(1);

String ric;

String price;

String updated;

while (rs.next()) {

ric = rs.getString(1);

price = rs.getString(2);

updated = rs.getString(3);

System.out.println("ric:" + ric + ";-- price:" + price + "; --"

+ updated + "; ");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

System.out.println("close ");

}

}

public static void main(String args[])// 自己替換［］

{

new JDBCoracle10G_INVOKEFUNCTION();

}

}

如何調(diào)用ORACLE自定義函數(shù)？

方式一：

CALL PAMF05_FUN(你需要傳入的參數(shù)');

方式二：

begin

PAMF05_FUN(你需要傳入的參數(shù)');

end;