使用存储过程在Oracle中来封装分页

来源：互联网发布：淘宝退款凭证怎么做编辑：程序博客网时间：2024/05/21 22:51

存储过程简介

什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。

3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

以上是参考的相关的概念，下面就来写个例子，分页怎么用存储过程进行封装，pl/sql代码如下：

**create or replace package pkg_query as     type cur_query is ref cursor;end pkg_query;**/create or replace procedure pre_query(p_tableName in varchar2,p_strWhere in varchar2,p_orderColumn in varchar2,p_orderStyle in varchar2,p_curPage in out number,p_pageSize in out number,p_totalRecords out number,p_totalPages out number,v_cur out pkg_query.cur_query)isv_sql varchar2(1000) := '';v_startRecord number(4);v_endRecord number(4);beginv_sql := ' select to_number(count(*)) from ' || p_tableName || ' where 1=1 ';if p_strWhere is not null or p_strWhere <> '' then     v_sql := v_sql + p_strWhere;end if;execute immediate v_sql into p_totalRecords;if mod(p_totalRecords,p_pageSize) = 0 then    p_totalPages := p_totalRecords / p_pageSize;else     p_totalPages := p_totalRecords / p_pageSize + 1;end if;if p_curPage < 1 then    p_curPage := 1;end if; if p_curPage > p_totalPages then    p_curPage := p_totalPages;end if;v_startRecord := (p_curPage - 1) * p_pageSize + 1;v_endRecord := p_curPage * p_pageSize;v_sql :=' select * from (select A.* , rownum r from ' ||    ' (select * from ' || p_tableName;if p_strWhere is not null or p_strWhere <> '' then    v_sql := v_sql || ' where 1=1 ' || p_strWhere;end if;if p_orderColumn is not null or p_orderColumn <> '' then    v_sql := v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle;end if;v_sql := v_sql || ' ) A where rownum <= '|| v_endRecord || ' ) B where r >= '        || v_startRecord;dbms_output.put_line(v_sql);open v_cur for v_sql;end pre_query;

以下是在Java中的调用测试：

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;public class TestProcedure {    public static void main(String[] args) {        Connection con = null;        // PreparedStatement pre = null;        ResultSet result = null;        String sql = "{call pre_query(?,?,?,?,?,?,?,?,?)}";        CallableStatement cstmt = null;        try {            Class.forName("oracle.jdbc.driver.OracleDriver");            System.out.println("tyr to connect oracle database!");            String url = "jdbc:oracle:thin:@127.0.0.1:1521:testhy";            String user = "scott";            String password = "orcl";            try {                con = DriverManager.getConnection(url, user, password);                System.out.println("connect database success!");                cstmt = con.prepareCall(sql);                // 1 p_tableName in varchar2,                // 2 p_strWhere in varchar2,                // 3 p_orderColumn in varchar2,                // 4 p_orderStyle in varchar2,                // 5 p_curPage in out number,                // 6 p_pageSize in out number,                // 7 p_totalRecords out number,                // 8 p_totalPages out number,                // 9 v_cur out pkg_query.cur_query                cstmt.setString(1, "user_tbl");                cstmt.setString(2, "");                cstmt.setString(3, "id");                cstmt.setString(4, "asc");                cstmt.setInt(5, 2);                cstmt.setInt(6, 3);                cstmt.registerOutParameter(7, oracle.jdbc.OracleTypes.NUMBER);                cstmt.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);                cstmt.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);                cstmt.execute();                int pageCount = cstmt.getInt(7);                int totalPages = cstmt.getInt(8);                System.out.println("pageCount= " + pageCount + " totalPages="                        + totalPages);                result = (ResultSet) cstmt.getObject(9);                while (result.next()) {                    System.out.print(result.getInt(1) + "  ");                    System.out.print(result.getString(2) + "  ");                    System.out.print(result.getString(3) + "  ");                    System.out.println();                    Person person = new Person();                    person.setId(result.getInt(1));                    person.setUsername(result.getString(2));                    person.setPassword(result.getString(3));                    System.out.println(person.toString());                }            } catch (SQLException e) {                e.printStackTrace();            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        }    }}

供各位参考指正

0 0