一、Oracle 儲存過程分頁
1 create PROCEDURE GetPatientVisitData( 2 p_HospId IN VARCHAR2, -- 院區編碼 3 p_strDate IN VARCHAR2, -- 開始日期 4 p_endDate IN VARCHAR2, -- 結束日期 5 p_page_size IN NUMBER, -- 每頁記錄數 6 p_page_number IN NUMBER, -- 當前頁碼 7 p_result OUT SYS_REFCURSOR, -- 結果集 8 p_total_count OUT NUMBER -- 總記錄數 9 ) AS 10 BEGIN 11 -- 計算總記錄數 12 SELECT COUNT(*) 13 INTO p_total_count 14 FROM ( 15 SELECT * FROM 表名 16 ); 17 -- 分頁查詢資料 18 OPEN p_result FOR 19 -- 你的查詢語句 20 SELECT 欄位1，欄位2，ROW_NUMBER() OVER (ORDER BY 排序欄位) AS rn FROM 表名 AS A 21 --分頁 22 WHERE A.rn BETWEEN (p_page_number - 1) * p_page_size + 1 AND p_page_number * p_page_size ; 23 END GetPatientVisitData; 24 /
二、Sqlsugar 呼叫
1 //設定引數 2 var parameters = new List<SugarParameter> 3 { 4 new SugarParameter("p_HospId", HospId), 5 new SugarParameter("p_strDate", strDate), 6 new SugarParameter("p_endDate", endDate), 7 new SugarParameter("p_page_size", pageSize), 8 new SugarParameter("p_page_number", page), 9 new SugarParameter("p_result", "") 10 { IsRefCursor = true, Direction = System.Data.ParameterDirection.Output }, 11 new SugarParameter("p_total_count", 0) 12 { Direction = System.Data.ParameterDirection.Output } 13 }; 14 //執行儲存過程 15 var resultList = db.Ado.SqlQueryAsync<OutpatientReportStatistics>("BEGIN GetPatientVisitData(:p_HospId, :p_strDate, :p_endDate, :p_page_size, :p_page_number, :p_result, :p_total_count); END;", parameters).Result.ToList(); 16 //總行數 17 var totalItem = Convert.ToInt32(parameters[6].Value);