JAVA调用增删改的存储过程

1、创建添加存储过程

CREATE OR REPLACE PROCEDURE stu_proc(v_id IN NUMBER, v_name IN VARCHAR2, v_age IN NUMBER) AS
BEGIN
INSERT INTO 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;

public class 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);
}
}

}

            

创建删除存储过程语句

CREATE OR REPLACE PROCEDURE stu_proc(v_id IN NUMBER, v_msg OUT VARCHAR2) IS
v_flag
NUMBER:=1;
BEGIN
SELECT o.id INTO v_flag FROM student o WHERE o.id=v_id;
DELETE FROM 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;

public class 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);
}
}

}

          

创建修改存储过程

CREATE OR REPLACE PROCEDURE stu_proc
(
v_id
IN NUMBER,
v_name
IN VARCHAR2,
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;

public class 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 2011-04-17 20:06  Ruthless  阅读(3542)  评论(0编辑  收藏  举报