在Oracle存儲過程中,Rownum可以用來控制返回的記錄行數(shù),對結(jié)果集進行分頁等操作。以下是一些Rownum在Oracle存儲過程中的應(yīng)用示例:
CREATE OR REPLACE PROCEDURE getEmployeesByPage(
pageNum IN NUMBER,
pageSize IN NUMBER
)
IS
BEGIN
SELECT *
FROM employees
WHERE ROWNUM BETWEEN ((pageNum - 1) * pageSize) + 1 AND pageNum * pageSize;
END;
CREATE OR REPLACE PROCEDURE getTopEmployeesBySalary
IS
BEGIN
SELECT *
FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;
END;
CREATE OR REPLACE PROCEDURE getEmployeesByDepartment(
departmentId IN NUMBER
)
IS
BEGIN
SELECT *
FROM employees
WHERE department_id = departmentId
AND ROWNUM <= 10;
END;
這些示例展示了如何在Oracle存儲過程中使用Rownum對結(jié)果集進行控制和篩選,以實現(xiàn)不同的查詢需求。