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