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

}