Oracle 페이징 저장 프로시저 - 페이징 검색을 더욱 효율적으로 만듭니다.

인터넷의 발달과 함께 데이터베이스의 활용이 점점 더 보편화되었고, 많은 양의 데이터를 쿼리하고 처리해야 합니다. 이 과정에서 페이징 검색은 가장 일반적인 요구 사항 중 하나입니다. 오라클 데이터베이스는 페이징 쿼리를 구현하기 위한 수단으로 ROWNUM을 제공하지만, 실제 애플리케이션 프로세스에서는 많은 한계와 부족함에 직면하게 됩니다. 따라서 Oracle 페이징 저장 프로시저를 개발하는 것이 더욱 효율적인 솔루션이 되었습니다.

이 기사에서는 실제 사례를 결합하여 Oracle 데이터베이스를 기반으로 효율적인 페이징 저장 프로시저를 설계하는 방법을 소개합니다.

1. 전체 디자인

Oracle 페이징 저장 프로시저의 디자인 아키텍처는 매개변수 전송, 데이터 쿼리, 데이터 페이징 계산 및 결과 반환의 네 가지 핵심 부분으로 나눌 수 있습니다.

1. 매개변수 전달: 쿼리문, 현재 페이지 번호, 페이지당 레코드 수 등을 입력 매개변수로 저장 프로시저에 전달합니다.
2. 데이터 쿼리: 들어오는 쿼리문을 기반으로 쿼리하여 조건에 맞는 데이터를 얻습니다.
3. 데이터 페이징 계산: 쿼리 결과에 대해 페이징 처리를 수행하고 지정된 페이지 번호의 데이터를 계산합니다.
4. 결과 반환: 지정된 페이지 번호와 총 레코드 수의 데이터를 반환합니다.

2. 특정 구현

Oracle 페이징 저장 프로시저를 구현하는 과정에서 일부 Oracle 관련 구문과 함수를 사용해야 합니다. 따라서 먼저 이해해야 할 몇 가지 기본 사항이 있습니다.

1. ROW_NUMBER 함수

ROW_NUMBER 함수는 결과 집합의 행 수를 계산하는 데 사용되는 Oracle의 키워드입니다. 반환 결과는 오름차순 행 순서로 정렬된 정수 값입니다. 예는 다음과 같습니다.

SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
FROM emp;

위 예의 ROW_NUMBER 함수는 직원 번호별로 정렬하고 증가하는 일련 번호를 할당합니다. 이는 페이지를 매긴 쿼리에 매우 중요합니다.

2. 상위 N개 레코드 쿼리

상위 N개 레코드를 쿼리하는 방법은 무엇입니까? Oracle은 ROWNUM 및 ROW_NUMBER() 함수라는 두 가지 방법을 제공합니다. 아래와 같이

SELECT *
FROM (
    SELECT emp.*, ROWNUM rn
    FROM emp
) t
WHERE t.rn <= N;

또는

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno <= N;

N을 쿼리해야 하는 레코드 수로 바꾸세요.

3. 페이지 쿼리

페이지 쿼리는 일반적인 시나리오이며 일반적으로 표시할 페이지 번호와 페이지당 레코드 수를 지정해야 합니다. 그 중 OFFSET은 각 페이지에 표시되는 데이터의 시작 인덱스를 지정하고, LIMIT는 각 페이지에 표시되는 최대 레코드 수를 지정합니다. 아래와 같이:

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno > OFFSET AND t.rowno <= OFFSET+LIMIT;

그 중에서 OFFSET 및 LIMIT는 유연한 페이징 쿼리를 달성하기 위해 특정 요구에 따라 조정될 수 있습니다.

3. 코드 구현

다음은 전체 Oracle 페이징 저장 프로시저의 예입니다.

CREATE OR REPLACE PROCEDURE paginating_demo (
    p_sql       IN    VARCHAR2,    --带有占位符(:P1,:P2...)的查询语句
    p_curPage   IN    NUMBER,      --当前页码
    p_pageSize  IN    NUMBER,      --每页的记录数量
    p_recordset OUT   SYS_REFCURSOR,--查询结果集
    p_total     OUT   NUMBER       --记录的总数
)
AS
  v_sql VARCHAR2(4000); 
  v_fromIndex NUMBER; 
  v_toIndex NUMBER;

BEGIN
  SELECT COUNT(*) INTO p_total FROM ( p_sql );

  IF (p_total > 0) THEN
    -- 计算 limit 和offset 边界值
    v_fromIndex := ((p_curPage - 1) * p_pageSize);
    v_toIndex   := (p_curPage * p_pageSize);

    v_sql := 'SELECT * FROM ( SELECT t.*, ROWNUM RN FROM ( ' || p_sql || ' ) t WHERE ROWNUM <= &#39; || v_toIndex || &#39; ) WHERE RN > ' || v_fromIndex;

    OPEN p_recordset FOR v_sql;
  END IF;

END paginating_demo;

이 코드는 쿼리 문, 현재 페이지 번호, 페이지당 레코드 수, 쿼리 결과 집합 및 총 레코드 수를 다음과 같이 사용합니다. 입력 및 출력 매개변수. 그 중 쿼리 결과 세트와 총 레코드 개수가 출력 매개변수로 반환됩니다.

4. 요약

실제 애플리케이션에서 Oracle 페이징 저장 프로시저는 페이징 쿼리의 효율성과 안정성을 크게 향상시킵니다. 위의 지식과 기술을 습득함으로써 실제로 Oracle 페이징 저장 프로시저를 유연하게 사용하여 시스템 성능과 사용자 경험을 크게 향상시킬 수 있습니다.

위 내용은 Oracle 페이징 저장 프로시저 - 페이징 검색을 더욱 효율적으로 만듭니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!