oracle存储过程学习

oracle处于入门，看了些例子，写了几个简单的存储过程，记着
表结构

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+"=======");
		}
	}
}