JAVA调用oracle存储过程实例

1、创建添加存储过程

CREATEORREPLACEPROCEDURE stu_proc(v_id INNUMBER, v_name INVARCHAR2, v_age INNUMBER) AS
BEGIN
INSERTINTO student(id, sname, age) values (v_id, v_name, v_age);
commit;
END;

     

JAVA调用添加存储过程

复制代码
package com.ljq.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

publicclass ProceTest {

public static void main(String[] args) throws Exception {
Connection conn
=null;
CallableStatement statement
=null;
String sql
="{call stu_proc(?, ?, ?)}";
try {
conn
= ConnUtils.getConnection();
statement
= conn.prepareCall(sql);
statement.setInt(
1, 6);
statement.setString(
2, "laoli");
statement.setInt(
3, 45);
//如果第一个结果是ResultSet对象,则返回true;如果第一个结果是更新、添加、修改或者没有结果,则返回 false
boolean issuccess=statement.execute();
//成功返回true,失败返回false
System.out.println(issuccess);
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
ConnUtils.free(
null, statement, conn);
}
}

}
复制代码

            

创建删除存储过程语句

复制代码
CREATEORREPLACEPROCEDURE stu_proc(v_id INNUMBER, v_msg OUT VARCHAR2) IS
v_flag
NUMBER:=1;
BEGIN
SELECT o.id INTO v_flag FROM student o WHERE o.id=v_id;
DELETEFROM student o WHERE o.id=v_flag;
commit;
v_msg:
='删除成功';
EXCEPTION
WHEN OTHERS THEN v_msg:='删除失败';
END;
复制代码

 

java调用删除存储过程

复制代码
package com.ljq.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

publicclass ProceTest {

public static void main(String[] args) throws Exception {
Connection conn
=null;
CallableStatement statement
=null;
String sql
="{call stu_proc(?, ?)}";
try {
conn
= ConnUtils.getConnection();
statement
= conn.prepareCall(sql);
statement.setInt(
1, 4);
statement.registerOutParameter(
2, Types.VARCHAR);
statement.execute();
String msg
=statement.getString(2);
System.out.println(msg);
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
ConnUtils.free(
null, statement, conn);
}
}

}
复制代码

          

创建修改存储过程

复制代码
CREATEORREPLACEPROCEDURE stu_proc
(
v_id
INNUMBER,
v_name
INVARCHAR2,
v_msg OUT
VARCHAR2
)
AS
v_flag
number;
BEGIN
SELECT o.id INTO v_flag FROM student o WHERE o.id=v_id;
UPDATE student o SET o.sname=v_name WHERE o.id=v_id;
commit;
v_msg:
='修改成功';
EXCEPTION
WHEN OTHERS THEN v_msg:='修改失败';
END;
复制代码

         

java调用修改存储过程

复制代码
package com.ljq.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

publicclass ProceTest {

public static void main(String[] args) throws Exception {
Connection conn
=null;
CallableStatement statement
=null;
String sql
="{call stu_proc(?, ?, ?)}";
try {
conn
= ConnUtils.getConnection();
statement
= conn.prepareCall(sql);
statement.setInt(
1, 3);
statement.setString(
2, "laoli");
statement.registerOutParameter(
3, Types.VARCHAR);
statement.execute();
String msg
=statement.getString(3);
System.out.println(msg);
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
ConnUtils.free(
null, statement, conn);
}
}

}

posted on 2014-05-07 11:41  锟斤拷锟斤拷  阅读(619)  评论(0编辑  收藏  举报

导航