sql - oracle中使用存储过程进行分页和排序

Question

我创建了一个用于分页的存储过程。现在我正在寻找列排序。

我的工作分页存储过程:

PROCEDURE paging (PageSize     IN     INT,
                  PageIndex    IN     INT,
                  SortColumn   IN     VARCHAR,
                  PageData        OUT Page) AS
    FirstIndex   INT;
    LastIndex    INT;
    SortCol      VARCHAR;
BEGIN
    LastIndex := PageSize * (PageIndex + 1);
    FirstIndex := LastIndex - PageSize + 1;
    SortCol := SortColumn;

    OPEN PageData FOR
        SELECT *
          FROM (SELECT a.*, ROWNUM AS rnum
                  FROM (  SELECT *
                            FROM table_name
                        ORDER BY SortCol) a
                 WHERE ROWNUM <= LastIndex)
         WHERE rnum >= FirstIndex;
END paging;
/

Answer 1

我建议你使用一个叫做 Dynamic SQL ( Oracle Docs ) 的很棒的功能。

我还修改了您的 SQL 查询并使用了 ROW_NUMBER()而不是 rownum .与后者相比，它是一种对输出行进行排序和编号的更可靠的方法。

我还从您的 PL/SQL 中删除了一些我认为不需要的变量:

PROCEDURE paging (PageSize     IN     INT,
                  PageIndex    IN     INT,
                  SortColumn   IN     VARCHAR2, -- Assuming this always contains
                                                -- the ordering column name
                  PageData        OUT Page) AS
    FirstIndex   INT;
    LastIndex    INT;
    v_sql        VARCHAR2(4000);
BEGIN
    LastIndex := PageSize * (PageIndex + 1);
    FirstIndex := LastIndex - PageSize + 1;

    v_sql :=  'SELECT *'
            ||'  FROM (SELECT a.*,'
            ||'              ROW_NUMBER() '
            ||'                OVER (ORDER BY ' || SortColumn || ') AS rnum'
            ||'          FROM table_name a)'
            ||' WHERE rnum BETWEEN FirstIndex AND LastIndex';

    OPEN PageData FOR v_sql;

END paging;
/