Oracle 儲存過程分頁 + Sqlsugar呼叫

奋斗小土豪發表於2024-11-08
Oracle儲存過程SqlSugar

一、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);

相關文章