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