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

存储过程简介

什么是存储过程：存储过程可以说是一个记录集吧，它是由一些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
)

is

v_sql varchar2(1000) := '';
v_startRecord number(4);
v_endRecord number(4);

begin

v_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();
        }
    }

}

供各位参考指正