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) BEGININSERT 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 BEGINDELETE SYS_USER WHERE ID=P_ID;X_OUT_ID:=0;EXCEPTIONWHEN 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) ASBEGIN 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 URLpublic 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) */@Overridepublic 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) */@Overridepublic 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;}@Overridepublic 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();@Testpublic 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);}@Testpublic void testDeleteUsers() {String result= sysusers.deleteUsers(1000000000);System.out.println("删除用户："+result);}@Testpublic void testGetUserAll() {List<SysUsers> list=sysusers.getUserAll();for (SysUsers sysUsers : list) {System.out.println("编号："+sysUsers.getId()+"\t用户名："+sysUsers.getUsername());}}}
页:
[1]