Oracle存储过程的编写以及程序的实现

-- 创建表
CREATE TABLE sys_user(
  ID NUMBER PRIMARY KEY,
  USERNAME VARCHAR2(20) NOT NULL,
  PASSWORD VARCHAR2(20) NOT NULL,
  SEX VARCHAR2(20) NOT NULL,
  STATUS VARCHAR2(20) NOT NULL
);
-- 创建存储过程（新增一条用户信息）
CREATE OR REPLACE PROCEDURE P_USER_ADD(
P_ID IN NUMBER,
P_USERNAME IN VARCHAR2,
P_PASSWORD IN VARCHAR2,
P_SEX IN VARCHAR2,
P_STATUS IN VARCHAR2
) 
BEGIN
INSERT INTO SYS_USER VALUES(P_ID,P_USERNAME,P_PASSWORD,P_SEX,P_STATUS);
END;
-- 创建存储过程（删除一条用户信息）
CREATE OR REPLACE PROCEDURE P_USER_DEL 
(
  P_ID IN NUMBER  
, X_OUT_ID OUT NUMBER  
) AS 
BEGIN
  DELETE SYS_USER WHERE ID=P_ID;
  X_OUT_ID:=0;
  EXCEPTION
  WHEN OTHERS THEN 
  X_OUT_ID:=-1;
END;
-- 创建存储过程（查询全部用户信息）
CREATE OR REPLACE PROCEDURE P_USER_ALL(
X_OUT_RECORD OUT NUMBER,
X_OUT_CURSOR OUT SYS_REFCURSOR
) AS  
BEGIN  
    OPEN x_out_cursor FOR
        SELECT * FROM SYS_USER;   
    x_out_record := 0;   
    EXCEPTION   
    WHEN OTHERS THEN
        x_out_record := -1;   
END; 

package com.ac.procedure.core.dm;
/**
 * 用户实体
 * @author Yan
 * @date 2012-6-13 上午11:26:31
 */
public class SysUsers {
	// 编号
	public int id;
	// 用户名
	public String username;
	// 密码
	public String password;
	// 性别
	public String sex;
	// 状态
	public String struts;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getStruts() {
		return struts;
	}

	public void setStruts(String struts) {
		this.struts = struts;
	}

}

package com.ac.procedure.core.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * 连接DB工具类
 * @author Yan
 * @date 2012-6-13 上午11:37:20
 */
public class BaseDao {
	// 连接DB 驱动
	public static String DRIVER="oracle.jdbc.driver.OracleDriver";
	// 连接DB URL
	public static String URL="jdbc:oracle:thin:@localhost:1521:orcl";
	// 用户名
	public static String USERNAME="scott";
	// 密码
	public static String PASSWORD="tiger";

	/**
	 * 加载信息,连接DB
	 * @return Connection
	 */
	public Connection getConnection() {
		Connection conn=null;
		try {
			Class.forName(DRIVER);
			conn=DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {			
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭DB连接
	 * @param conn DB连接对象
	 * @param call 调用储存过程对象 
	 * @param rs 结果集对象
	 */
	public void closeAll(Connection conn,CallableStatement call,ResultSet rs){
		try {
			if (rs != null) {   
	            rs.close();   
	        }   
	        if (call != null) {   
	        	call.close();   
	        }   
	        if (conn != null) {   
	        	conn.close();   
	        }   
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.ac.procedure.core.dao;

import java.util.List;

import com.ac.procedure.core.dm.SysUsers;
/**
 * 用户接口
 * @author Yan
 * @date 2012-6-13 上午11:29:24
 */
public interface SysUsersDao {
	/**
	 * 新增一条用户信息
	 * @param users 用户对象
	 * @return success or failure
	 */
	public String saveUsers(SysUsers users);
	/**
	 * 删除一条用户信息
	 * @param id 编号
	 * @return success or failure
	 */
	public String deleteUsers(int id);
	/**
	 * 查询全部用户信息
	 * @return List
	 */
	public List<SysUsers> getUserAll();
}

package com.ac.procedure.core.dao.impl;

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.driver.OracleTypes;

import com.ac.procedure.core.dao.SysUsersDao;
import com.ac.procedure.core.dm.SysUsers;
import com.ac.procedure.core.util.BaseDao;
/**
 * 用户接口实现（调用存储过程）
 * @author Yan
 * @date 2012-6-13 上午11:52:58
 */
public class SysUsersDaoImpl extends BaseDao implements SysUsersDao {

	// 连接DB 对象
	private Connection conn;
	// 存储过程 对象
	private CallableStatement call;
	// 结果集 对象
	private ResultSet rs;

	/*
	 * 新增一条用户信息(non-Javadoc)
	 * @see com.ac.procedure.core.dao.SysUsersDao#saveUsers(com.ac.procedure.core.dm.SysUsers)
	 */
	@Override
	public String saveUsers(SysUsers users) {
		String flag="success";
		try {
			String sql="{CALL P_USER_ADD(?,?,?,?,?)}";		
			conn=this.getConnection();
			call=conn.prepareCall(sql);
			call.setInt(1, users.getId());
			call.setString(2, users.getUsername());
			call.setString(3, users.getPassword());
			call.setString(4, users.getSex());
			call.setString(5,users.getStruts());
			call.execute(); 
		} catch (SQLException e) {			
			e.printStackTrace();
			flag="failure";
		} finally{
			this.closeAll(conn, call, null);
		}
		return flag;
	}

	/*
	 * 删除一条用户信息(non-Javadoc)
	 * @see com.ac.procedure.core.dao.SysUsersDao#deleteUsers(int)
	 */
	@Override
	public String deleteUsers(int id) {
		String flag="success";
		try {
			String sql="{CALL P_USER_DEL(?,?)}";		
			conn=this.getConnection();
			call=conn.prepareCall(sql);
			call.setInt(1, id);
			call.registerOutParameter(2, Types.INTEGER);
			call.execute();
		} catch (Exception e) {
			e.printStackTrace();
			flag="failure";
		} finally{
			this.closeAll(conn, call, null);
		}
		return flag;
	}

	@Override
	public List<SysUsers> getUserAll() {
		List<SysUsers> list=new ArrayList<SysUsers>();
		SysUsers users=null;
		try {
			String sql="{CALL P_USER_ALL(?,?)}";		
			conn=this.getConnection();
			call=conn.prepareCall(sql);
			call.registerOutParameter(1, Types.INTEGER);   
		      call.registerOutParameter(2, OracleTypes.CURSOR);   
		      call.execute();		         
		      rs=(ResultSet) call.getObject(2);   
		      while(rs.next()){   
		    	  users=new SysUsers();
		    	  users.setId(rs.getInt(1));
		    	  users.setUsername(rs.getString(2));
		    	  users.setPassword(rs.getString(3));
		    	  users.setSex(rs.getString(4));
		    	  users.setStruts(rs.getString(5));
		    	  list.add(users);
		      }
		} catch (Exception e) {
			e.printStackTrace();
			list=null;
		} finally{
			this.closeAll(conn, call, rs);
		}
		return list;
	}

}

package com.ac.procedure.core.dao.impl;

import java.util.List;

import org.junit.Test;

import com.ac.procedure.core.dm.SysUsers;

public class SysUsersDaoImplTest {

	SysUsersDaoImpl sysusers=new SysUsersDaoImpl();

	@Test
	public void testSaveUsers() {
		SysUsers users=new SysUsers();
		users.setId(1000000000);
		users.setUsername("闫小甲");
		users.setPassword("123456");
		users.setSex("男");
		users.setStruts("启用");
		String result = sysusers.saveUsers(users);
		System.out.println("保存用户："+result);
	}

	@Test
	public void testDeleteUsers() {
		String result= sysusers.deleteUsers(1000000000);
		System.out.println("删除用户："+result);
	}

	@Test
	public void testGetUserAll() {
		List<SysUsers> list=sysusers.getUserAll();
		for (SysUsers sysUsers : list) {
			System.out.println("编号："+sysUsers.getId()
					+"\t用户名："+sysUsers.getUsername());
		}
	}

}