Oracle 與 Sybase 分頁查詢語法對比：ROWNUM vs SET ROWCOUNT

Oracle 與 Sybase 分頁查詢語法對比：ROWNUM vs SET ROWCOUNT

分批處理大量資料是常見需求。Oracle 使用 ROWNUM 偽欄位，Sybase (ASE/SQL Anywhere) 使用 SET ROWCOUNT 會話層級設定。本文以相同測試表 TB_EMPLOYEEINFO 對比兩者語法、行為差異與注意事項。

測試表與資料

-- Oracle
CREATE TABLE TB_EMPLOYEEINFO (
  EMPLOYENO VARCHAR2(20) NOT NULL,
  EMPLOYENAME VARCHAR2(20) NOT NULL,
  EMPLOYEEAGE INT NOT NULL
);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1000','ZhangSan',20);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1001','LiSi',21);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1002','WangWu',21);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1003','ZhouLiu',22);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1004','SunQi',22);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1005','LiuBa',23);
INSERT INTO TB_EMPLOYEEINFO VALUES ('A1006','ChenShi',25);

-- Sybase
CREATE TABLE TB_EMPLOYEEINFO (
  EMPLOYENO VARCHAR(20) NOT NULL,
  EMPLOYENAME VARCHAR(20) NOT NULL,
  EMPLOYEEAGE INT NOT NULL
);
-- 相同 INSERT 語句

Oracle：ROWNUM 限制回傳筆數

SET ROWCOUNT 5;
SELECT EMPLOYENO, EMPLOYENAME, EMPLOYEEAGE FROM TB_EMPLOYEEINFO;
SET ROWCOUNT 0;  -- 必須歸零，否則後續所有查詢受限

• SET ROWCOUNT N 影響當前會話所有後續 SELECT/INSERT/UPDATE/DELETE
• 必須配對 SET ROWCOUNT 0 恢復預設，否則後續查詢永久限制 5 筆
• 不支援 OFFSET/FETCH（視版本而定）