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); } } }
1591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
