JAVA调用增删改的存储过程

本文主要是介绍JAVA调用增删改的存储过程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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) ISv_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:='删除成功';
EXCEPTIONWHEN 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
) ASv_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:='修改成功';
EXCEPTIONWHEN 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);}}}