Java调用Oracle存储过程一:
先创建一张表,操作如下,我是从plsql里面创建的:
这上面实际就是建立两个字段就可以了,我是从plsql里面拷贝出来的语句:
下面建立存储过程:
下面是Java调用代码:
第二种:Java调用存储过程:
首先建立表:
表里面需要,为I_ID插入100,I_NAME随便,我这里插入的是100kb,因为下面的java代码要根据I_ID=100来查找I_Name,最终在控制台输出的是:100kb,就说明你成功啦。
建立触发器:
Java代码:
第三种Java调用存储过程:
先建立包,我使用plsql来建立的包
下面建立触发器:
Java代用代码:
先创建一张表,操作如下,我是从plsql里面创建的:
-- Create table
create table B_ID
(
I_ID VARCHAR2(200),
I_NAME VARCHAR2(200)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
这上面实际就是建立两个字段就可以了,我是从plsql里面拷贝出来的语句:
下面建立存储过程:
create or replace procedure TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) is
begin
INSERT INTO zhang.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
end TESTA;
下面是Java调用代码:
package com.hyq.src;
import java.sql.*;
import java.sql.ResultSet;
public class TestProcedureOne {
public TestProcedureOne() {
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "zhang", "zhang");
CallableStatement proc = null;
proc = conn.prepareCall("{ call zhang.TESTA(?,?) }");
proc.setString(1, "100");
proc.setString(2, "TestOne");
proc.execute();
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException ex1) {
}
}
}
}
第二种:Java调用存储过程:
首先建立表:
-- Create table
create table TESTTB
(
I_ID VARCHAR2(300),
I_NAME VARCHAR2(300)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
表里面需要,为I_ID插入100,I_NAME随便,我这里插入的是100kb,因为下面的java代码要根据I_ID=100来查找I_Name,最终在控制台输出的是:100kb,就说明你成功啦。
建立触发器:
create or replace procedure TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) is
begin
SELECT I_NAME INTO PARA2 FROM zhang.TESTTB WHERE I_ID= PARA1;
end TESTB;
Java代码:
package com.hyq.src;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class TestProcedureTWO {
public TestProcedureTWO() {
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "zhang", "zhang");
CallableStatement proc = null;
proc = conn.prepareCall("{ call zhang.TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println("=testPrint=is=" + testPrint);
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException ex1) {
}
}
}
}
第三种Java调用存储过程:
先建立包,我使用plsql来建立的包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
下面建立触发器:
create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) is
begin
OPEN p_CURSOR FOR SELECT * FROM ZHANG.TESTTB;
end TESTC;
Java代用代码:
package com.hyq.src;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args) throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "zhang", "zhang");
CallableStatement proc = null;
proc = conn.prepareCall("{ call zhang.testc(?) }");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(1);
while (rs.next()) {
System.out.println("" + rs.getString(1) + "" + rs.getString(2)
+ "");
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
}
}