oracle存储过程学习
程序员文章站
2022-03-30 22:37:58
...
oracle处于入门,看了些例子,写了几个简单的存储过程,记着
表结构
java工具类:
------------------------------
1.无返回值的
-------------------------------------
2.有返回值的
--------------------------------------------
3.返回结果集的,先建包
然后存储过程,以游标作为out参数
---------------------------------------------------
4.java类调用
表结构
create table t_test_procedure ( t_id number primary key, t_name varchar2(255) not null, t_remark varchar2(20) );
java工具类:
package com.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DB { private final static String DRIVER = "oracle.jdbc.driver.OracleDriver"; private final static String URL = "jdbc:oracle:thin:@localhost:1521:orcl"; private final static String USERNAME = "XXX"; private final static String PWD = "XXX"; static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConn(){ Connection conn = null; try { conn = DriverManager.getConnection(URL, USERNAME, PWD); System.out.println("successed"); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void closeConn(Connection conn){ if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { getConn(); } }
------------------------------
1.无返回值的
create or replace procedure pro_insert( v_id in number, v_name in varchar2, v_remark in varchar2 )is sys_exception exception; begin insert into t_test_procedure(t_id,t_name,t_remark) values(v_id,v_name,v_remark); exception when others then rollback; return; end pro_insert;
-------------------------------------
2.有返回值的
create or replace procedure pro_my_procedure( v_name in varchar2, v1_name in varchar2, s_save out varchar2, s_update out varchar2 )is sys_exception exception; db_exception exception; begin select count(*) into s_save from t_test_procedure t where t.t_remark = 'save' and t.t_name = v_name; select count(*) into s_update from t_test_procedure t where t.t_remark = 'update' and t.t_name = v1_name; exception when others then rollback; return; end pro_my_procedure;
--------------------------------------------
3.返回结果集的,先建包
create or replace package mypackage as type test_cursor is ref cursor; end mypackage;
然后存储过程,以游标作为out参数
create or replace procedure pro_queryall ( p_cursor out mypackage.test_cursor )is begin open p_cursor for select * from t_test_procedure; end pro_queryall;
---------------------------------------------------
4.java类调用
package com.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.List; import oracle.jdbc.OracleTypes; import com.utils.DB; public class Test { //无返回结果 public static void insert() { Connection conn = DB.getConn(); CallableStatement cs = null; String sql = "{Call pro_insert(?,?,?)}"; try { cs = conn.prepareCall(sql); cs.setInt(1, 5); cs.setString(2, "wang"); cs.setString(3, "save"); cs.execute(); System.out.println("success"); } catch (SQLException e) { System.err.println("sql 异常"); e.printStackTrace(); }finally{ DB.closeConn(conn); } } //有结果集的返回(带输出参数的返回) public static List<String> query() { List<String> list = new ArrayList<String>(); Connection conn = DB.getConn(); CallableStatement cs = null; String sql = "{Call pro_my_procedure(?,?,?,?)}"; try { cs = conn.prepareCall(sql); cs.setString(1, "wang"); cs.setString(2, "lisi"); cs.registerOutParameter(3, Types.VARCHAR); cs.registerOutParameter(4, Types.VARCHAR); cs.execute(); String s1 = cs.getString(3); String s2 = cs.getString(4); list.add(s1); list.add(s2); System.out.println("success"); } catch (SQLException e) { System.err.println("sql 异常"); e.printStackTrace(); }finally{ DB.closeConn(conn); } return list; } //返回结果集 public static List<String> queryAll() { List<String> list = new ArrayList<String>(); Connection conn = DB.getConn(); CallableStatement cs = null; ResultSet rs = null; String sql = "{Call pro_queryall(?)}"; try { cs = conn.prepareCall(sql); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while(rs.next()){ System.out.println("编号: "+rs.getLong(1) + "姓名: " + rs.getString(2)+ " 操作:" + rs.getString(3)); String s0 = String.valueOf(rs.getLong(1)); String s1 = rs.getString(2); String s2 = rs.getString(3); list.add(s0); list.add(s1); list.add(s2); } } catch (SQLException e) { System.err.println("sql 异常"); e.printStackTrace(); }finally{ DB.closeConn(conn); } return list; } public static void main(String[] args) { insert(); List<String> list = query(); for(String s:list) { System.out.println(s+"------------"); } List<String> list_01 = queryAll(); for(String s:list_01) { System.out.println(s+"======="); } } }