学习下oracle的存储过程增删改查操作以及java调用存储过程

学习下oracle的存储过程增删改查操作以及java调用存储过程

好久没发blog了，今天发下这两天学习的成果---存储过程

也可以让学习oracle的同学学习下

首先打开PL/SQL创建一张表emp_copy

drop table emp_copy;

CREATE TABLE emp_copy(empno NUMBER ,deptno NUMBER, ename VARCHAR2(40));

添加一条记录

INSERT INTO emp_copy VALUES (1, 60, 'qm');

现在我们来看下添加的那条数据

SELECT * FROM emp_copy;

现在我们新建一个SQL Window窗口,写一个添加数据的存储过程

create or replace procedure sp_test8(emp_name in varchar2,dept_no in number)asbegin  declare max_empno number;  begin    if(emp_name is null or length(emp_name)=0) then       return;    end if;    if(dept_no!=60) then       return;    end if;    select max(empno) into max_empno from emp_copy;    insert into emp_copy(empno,ename,deptno) values(max_empno+1,emp_name,dept_no);  end;  end sp_test8;

下面的是判断条件，emp_name 如果为空或者长度为0，则不能插入，dept_no值不为60则不能插入，主键=最大的主键+1，也就是所谓的自增，ok，这个存储过程我们写完了。运行下。

接下来我们用java来调用这个存储过程。

package sptest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 添加 * @author quanmin * */public class Sptest1 {public static void main(String[] args) {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: ORCL ";Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, " quanmin ", " quanmin ");CallableStatement proc = null; // 创建执行存储过程的对象proc = conn.prepareCall("{ call sp_test8(?,?) }"); // 设置存储过程 call为关键字.proc.setString(1, "quanmin"); // 设置第一个输入参数proc.setInt(2, 60);// 设置第二个输入参数proc.execute();// 执行} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}}

然后运行这个main方法。

再次在数据库查询下结果

SELECT * FROM emp_copy;

我们会发现，数据插入成功了。

接下来和上面一样，写一个删除的操作。我就不说那么仔细了直接发存储过程

CREATE OR REPLACE PROCEDURE sp_test7(e_name IN VARCHAR2) ASBEGIN  DELETE emp_copy WHERE ename = e_name;END sp_test7;

同样的发java代码调用

package sptest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 删除 * @author quanmin * */public class Sptest2 {public static void main(String[] args) {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: ORCL ";Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, " quanmin ", " quanmin ");CallableStatement proc = null; // 创建执行存储过程的对象proc = conn.prepareCall("{ call sp_test7(?) }"); // 设置存储过程 call为关键字.proc.setString(1, "quanmin"); // 设置第一个输入参数//proc.setInt(2, 60);// 设置第二个输入参数proc.execute();// 执行} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}}

查询下数据库，发现刚刚之前添加的一条数据已经没有了。

接下来发更新的存储过程

CREATE OR REPLACE PROCEDURE sp_test9(emp_no in number,emp_name IN VARCHAR2) ASBEGIN  UPDATE emp_copy SET ename = emp_name where empno=emp_no;END sp_test9;

java调用存储过程

package sptest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 更新 * @author quanmin * */public class Sptest3 {public static void main(String[] args) {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: ORCL ";Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, " quanmin ", " quanmin ");CallableStatement proc = null; // 创建执行存储过程的对象proc = conn.prepareCall("{ call sp_test9(?,?) }"); // 设置存储过程 call为关键字.proc.setInt(1, 1); // 设置第一个输入参数proc.setString(2, "张三");// 设置第二个输入参数proc.execute();// 执行} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}}

查看数据库发现刚刚的qm变成了“张三”

查找所有的存储过程

create or replace procedure sp_test11/**存储过程实例*/iscursor var_c is SELECT * FROM emp_copy;Begin   for temp in var_c loop       dbms_output.put_line('ename = '||temp.ename);   end loop;end;

最后我们最后介绍查找所有返回结果集

1.首先新建一个包 运行

create or replace package package_test3 is        type mycursor is ref cursor;procedure myproc(outcursor in out mycursor);end package_test3;

2.首先新建一个包body 运行

create or replace package body package_test3 isprocedure myproc(outcursor in out mycursor)isbegin  open outcursor for  select * from emp_copy;return;end myproc;end;

3.接下来开始写java代码

package sptest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.OracleTypes;/** * 查询所有返回结果集 * @author quanmin * */public class Sptest6 {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: ORCL ";    Connection conn=null;    CallableStatement cs=null;//PreparedStatement,Statement    ResultSet rs;        public void getConn(){        try {            Class.forName(driver);            conn = DriverManager.getConnection(strUrl, " quanmin ", " quanmin ");        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }            }    public void callProcForResult(){        try {        cs = conn.prepareCall("{call package_test3.myproc(?)}");            cs.registerOutParameter(1, OracleTypes.CURSOR);            cs.execute();            ResultSet rs = (ResultSet)cs.getObject(1);            while(rs!=null&& rs.next()){                System.out.println(new StringBuilder("empno:").append(rs.getInt(1)).append("\t depdno:").append(rs.getInt(2)).append("\t ename:").append(rs.getString(3)));            }         } catch (SQLException e) {            e.printStackTrace();        }    }    public void closeConn(){        try {            if (cs!=null) cs.close();            if(conn!=null) conn.close();        } catch (SQLException e) {            e.printStackTrace();        }    }    public static void main(String[] args) {    Sptest6 dao = new Sptest6();        dao.getConn();//得到连接        dao.callProcForResult();//调用返回单属性的存储过程        dao.closeConn(); //关闭连接    }}

开始运行java代码。java控制台得到查询的数据集合