java中调用ORACLE存储过程
作者:duketian 日期:2008-05-27 16:28:40
一:无返回值的存储过程
存储过程为:
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.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: hyq ";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.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) {
}
}
}
}
当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。
二:有返回值的存储过程(非列表)
存储过程为:
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
在java里调用时就用下面的代码:
package com.hyq.src;
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:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.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) {
}
}
}
}
}
注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在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 ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.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) {
}
}
}
}
存储过程为:
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.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: hyq ";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.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) {
}
}
}
}
当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。
二:有返回值的存储过程(非列表)
存储过程为:
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
在java里调用时就用下面的代码:
package com.hyq.src;
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:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.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) {
}
}
}
}
}
注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在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 ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.testc(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println("
}
}
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) {
}
}
}
}
平均得分
(0 次评分)
评论: 8 | 查看次数: 611
- 共有 8 条评论
- 共有 8 条评论
发表评论
订阅
上一篇
|

文章来自:
标签: 
balance . mp3 mp4 player plays a part in the outcome . mp3 mp4 player of combat, gear is a major differentiator that makes up for shortcomings in other areas. mp3 mp4 player In fact, with . mp3 player kaufen the introduction of Resilience, gear more than mp4 ever plays a more substantial part in PvP. power level In an Arena match, the very first thing we . power level scope out is gear. wow level Through quick tab-selection viewing .wow leveling of character portraits, we generally wow lvl have a good idea of the classes we're up against if they keep their helm graphic on. wow lvl If they are wearing Season 3 shoulders, then we know . wow lvl 60 their relative experience. wow lvl 70 This is why the visual i.wow power leveling mpact of Arena shoulders is so important.wow powerlevel It immediately gives you a general idea of how tough the match will be. wow powerlevel Players in full S3 will likely have over 10k hp and over. wow powerleveling 400 Resilience, depending on the class and spec. A full S3 SL/SL Warlock, for example, will easily have. about 12-13k hp and over 400 Resilience. Identifying weapons is slightly more . difficult but will also give a general idea of an enemy's strength. Season 1 and 2 weapons share the same graphics, so it's harder to identify. Season 3 weapons, on the other hand, are distinctive and share models with Black Temple and Mount Hyjal weapons. which 最新免费网络游戏 have relatively the same power. A review of Brutal Gladiator weapons will come in handy because these will be the most common way to identify opponents of relative skill. With the new mechanics. in place for Arenas, Season 4 will more or less weed out the chaff from the grain.
4GB MP3 PLAYER My wife is a shrewd little fox. Bluetooth Headset She knows just how much I love the fact. Bluetooth Headsets that she plays the game with me so sometimes, when we have our little domestic arguments, she makes sure. cell phone accessories to cancel her WoW account just to drive home a point. des po wow Of course, it doesn't mean much since we're both paid up for the next few months, but the message is clear -- "we make up (or you see things my way). digital camcorder or I'm quitting the game!" Of course, we don't reconcile merely because I'll be losing my . digital camcorders favorite playing partner, but I have to confess that it doesn't make . dvd players me happy one bit. free online games For parents, World of Warcraft can be a . free online war games useful bargaining chip for their kids with the parental . gold wow controls feature. gold wow It's easy enough to control WoW time if kids aren't doing their homework, floundering in school, or simply not. mp3 mp4 player doing their chores. mp3 player Conversely, a friend . mp3 player accessories of mine gave his son a WoW subscription when . mp3 player accessory he did well in school. mp3 player kaufen World of Warcraft can be so. online games much fun and addicting that it's . play war games often used as a social tool, and it's often upsetting when our. po wow friends quit playing the game. portable dvd player How many of us have . wow europe had friends whose significant others have "allowed" them to. wow geld play the game after, say, a wonderful date. wow gold verkaufen I'm not sure if it only applies to me, but . wow level service because I play the game with many of my RL friends . wow leveling service and my family, I use the lure of WoW . to full effect. I once had my brother do a specific task for . the promise of an upgrade to The Burning Crusade. A little before he finished what I asked him to do, I secretly upgraded his account. so he could免费网络游戏 finally make his Blood Elf Priest. Kind of manipulative, I know, but we did end up having . a lot of fun leveling our alts together. How about you. How much a part of your life is WoW and has it .最新网游 ever been used as a bargaining . chip in your social life.
Warhammer Gold
Warhammer Online Gold
Warhammer Accounts
Warhammer Power Leveling
Warhammer Online Key
Warhammer Gold
Warhammer Online Gold
Warhammer Time Card
Warhammer CD Key
WAR gold
warhammer online gold
Buy WAR gold
Buy warhammer gold
Buy warhammer online gold
warhammer gold
WAR Accounts
warhammer Accounts
warhammer online Accounts
Buy WAR Accounts
warhammer Accounts for sale
Warhammer Power Leveling
Warhammer Online Power Leveling
War Power Leveling
Buy Warhammer Power Leveling
Warhammer PowerLeveling
Cheap Warhammer Power Leveling
Cheap Warhammer Online Power Leveling
Buy War Power Leveling
Warhammer EU Power Leveling
Cheap Warhammer Gold
Cheap Warhammer online gold
Buy Cheap Warhammer Gold
Buy WAR Gold
Warhammer EU Gold
Warhammer EU Power Leveling
Warhammer EU CD Key
Warhammer EU Accounts
Warhammer CD Key
Warhammer online CD Key
Warhammer Timecard
Buy Warhammer Time Card
Warhammer 60 days Time Card
Cheap WAR Accounts
Cheap warhammer Accounts
Cheap warhammer online Accounts
Buy Cheap WAR Accounts
buy Warhammer CD Key
buy Warhammer online CD Key
cheap Warhammer CD key
warhammer time card
Warhammer prepaid time card
Lord of the Rings Online Gold
Buy Lotro Gold
Sell LoTRO Gold
LoTRO CD Key
LoTRO Europe Gold
Cheap LoTRO Accounts
Lord of the Rings Online Power Leveling
Lord of the Rings online CD Key
Cheap Lotro Gold
Buy Lotro Gold | Lord Of The Rings Online Gold
Lotro Accounts
| Buy Lotro Accounts
Lord Of The Rings Online Power Leveling | Lord Of The Rings Online PowerLeveling
Lotro Cd Key | Lord Time Card
Lotro Gold | Lotro Gold Instant Delivery
lord of the rings online accounts | lord of the rings online accounts for sale
Lotro Power Leveling | Lotro Powerleveling
Lord Of The Rings Online Cd Key | Lord Of The Rings Online Time Card
oct00nn
brogame
wow gold
wow gold
wow gold
wow power leveling
wow power leveling
wow power leveling
oofay
buy worhammer gold
东方医院
东方医院
阀门
阀门
空调
空调
上海展览公司
上海展览公司
留学新加坡
留学新加坡
英语口语
英语口语
油泵
油泵
真空泵
真空泵
胶体磨
胶体磨
中央空调
中央空调
商用中央空调
商用中央空调
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
wow gold
窃听器
手机监听器
监听器
手机窃听器
窃听器
手机监听器
监听器
手机窃听器
窃听器
手机监听器
监听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
手机窃听器│窃听器
wow gold
wow gold
wow gold
item4sale
item4sale
item4sale
item4sale
item4sale
item4sale
item4sale
age of conan gold
age of conan gold
age of conan gold
age of conan gold
age of conan gold
干洗连锁店
干洗连锁店
水洗设备
水洗设备
干洗设备价格
干洗设备价格
连锁店
连锁店
鞋机
鞋机
连锁洗衣店加盟
连锁洗衣店加盟
擦鞋修鞋
擦鞋修鞋
开干洗店
开干洗店
干洗设备
干洗设备
干洗
干洗
荷兰留学费用
荷兰留学签证
荷兰留学中介
荷兰留学网
荷兰留学论坛
荷兰大学排名
荷兰留学申请
干洗连锁店
干洗
连锁店
擦鞋修鞋
水洗设备
荷兰留学申请
age of conan gold
wow gold
Cheap Warhammer gold
Warhammer Online gold
WAR Gold
Buy Warhammer Gold
Warhammer Online Gold
Warhammer Gold for sale
Warhammer Accounts
Buy Warhammer Accounts
Cheap Warhammer Accounts
Warhammer Power leveling
WAR Power leveling
Warhammer Online Power Leveling
Warhammer CD Key
WAR CD Key
Warhammer Online CD Key
Warhammer Game Time Card