马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?站点注册
×
PowerBuilder 分页查询全解:TOP+NOT IN、键集游标、ROW_NUMBER 与 OFFSET FETCH,以及过滤后行序为何会乱(PB10 基准 · PB12.5 实测)
阅读说明
1. 适用版本:基准 PB10,PB 12.5 实测一致(本文不涉及 PBIDEA 组件)
2. 支持数据库:SQL Server(实测环境 SQL Server 2008 R2;第六节的 OFFSET FETCH 需 SQL Server 2012 及以上,本文按 2008 R2 实测其失败形态)
3. 操作系统与环境要求:Windows 7 及以上;PB10 或 PB12.5 开发环境 + 可连的 SQL Server;客户端需有对应的数据库接口(OLE DB / SNC)才能连库
4. 难度系数:★★★☆☆(三颗星:要懂 SQL 侧的分页写法,还要懂 DataWindow 缓冲区的过滤语义)
5. 其它阅读说明:需要先会用嵌入式 SQL 与 DataStore(SyntaxFromSQL + Create + Retrieve)。示例数据是一张 100 行的表 t_p104,建表与插数语句在第十二节给出,照着跑一遍即可复现文中所有读数。
一、这一篇讲什么
分页这件事,看起来只是"取第 N 页",实际是三件事叠在一起:
- SQL 侧怎么切:从表里只捞第 N 页那几十行。路数不少,代价差几个数量级。
- 总行数从哪来:没有总行数就没有页数,也就没有"末页"这个概念。
- DataWindow 侧怎么接:数据取回来以后,是每次都重新 Retrieve,还是把全部行留内存靠 Filter 切?这里的坑比 SQL 侧多。
很多项目栽在第三件事上:SQL 写得很漂亮,界面一翻页就乱序、重复、漏行。本文把这三件事分开讲,每一节都给出实机读数,不给"看起来应该"的结论。
一句话结论先放这里:能跳页的场景用 ROW_NUMBER();"下一页"场景用键集游标;老版本 SQL Server(2005 以前)只能用 TOP+NOT IN;OFFSET FETCH 在 2008 R2 上压根不存在;内存分页(Retrieve 全量 + SetFilter)能跑但行序会被打乱,必须补一次 Sort()。
二、先把"页"算清楚:四个公式与三类边界
分页的所有麻烦,一半来自这四个数没算对:
- 页大小 size(每页多少行)
- 请求页号 page(1 起,不是 0 起)
- 总行数 total
- 偏移量 offset = (page - 1) * size
再补一个派生量:总页数 pages = ceil(total / size)。注意 PB 的 / 是整除(整数相除丢小数),所以不能直接拿 total / size,要补一次取模判断。
三类必须堵住的边界:
- page < 1(用户点"上一页"点过头):夹到 1,别算出负偏移量。
- total = 0(查出来一行都没有):pages 应是 0,不是 1;此时页号夹取结果给 1,界面显示"第 1 页 / 共 0 页"。
- page > pages(换了查询条件,页号还停在旧值):夹到 pages。这一条最容易被忽略:用户在第 9 页改筛选条件,结果只剩 2 页,不夹取就会取出一个空页,界面白板。
- // 演示:分页四要素的计算(可直接粘进窗口按钮的 Clicked 事件运行)
- long ll_total // 输入 1:总行数(由 select count(*) 得到)
- long ll_size // 输入 2:每页行数
- long ll_page // 输入 3:请求页号(1 起)
- long ll_pages, ll_off, ll_fix
- string ls_msg
- ll_total = 105
- ll_size = 10
- ll_page = 99 // 故意给一个越界页号,看夹取是否生效
- ll_pages = ll_total / ll_size
- if Mod(ll_total, ll_size) > 0 then ll_pages = ll_pages + 1 // 向上取整
- ll_off = (ll_page - 1) * ll_size // 偏移量
- ll_fix = ll_page
- if ll_fix < 1 then ll_fix = 1
- if ll_fix > ll_pages then ll_fix = ll_pages
- ls_msg = "总行数=" + String(ll_total) + " 页大小=" + String(ll_size) + "~r~n"
- ls_msg = ls_msg + "总页数=" + String(ll_pages) + " 请求页=" + String(ll_page) + "~r~n"
- ls_msg = ls_msg + "夹取后页=" + String(ll_fix) + " 偏移量=" + String(ll_off)
- MessageBox("分页四要素", ls_msg)
复制代码
实机读数:总页数 11、夹取后页 11、偏移量 980(请求页 99 被夹回 11)。这三个值在 PB10 与 PB12.5 上完全一致。
这三段逻辑在第十二节被封装成 of_totalPages / of_offset / of_clampPage 三个方法,并且带 16 条断言跑过双版本,本文后面直接调用它们。
三、路一:TOP + NOT IN(兼容性最好,代价随页号线性上涨)
原理:先查出前 skip 个主键,再"排除这些主键"取剩下的前 size 个。
- // 演示:生成第 3 页(每页 10 行)的 TOP + NOT IN 语句
- string ls_sql
- long ll_page, ll_size, ll_skip
- ll_page = 3
- ll_size = 10
- ll_skip = (ll_page - 1) * ll_size // 要跳过的行数 = 20
- ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 " + &
- "where id not in (select top " + String(ll_skip) + " id from dbo.t_p104 order by id ASC) " + &
- "order by id ASC"
- MessageBox("第 3 页 SQL", ls_sql)
复制代码
实机读数(表 100 行,主键 id 连续):第 1 页 id = 1..10,第 2 页 id = 11..20,第 10 页 id = 91..100。页页对齐,没有重叠也没有缺口。
它的三个硬伤,写清楚比背下来有用:
- NOT IN 里的子查询要算 skip 行:翻到第 1000 页,子查询先扫 9990 行再丢掉,页码越深越慢,复杂度是 O(skip)。这不是"能不能用"的问题,是"第 500 页以后要等几秒"的问题。
- 排序键必须能进 NOT IN:排序键是单列主键最省事;如果按"金额倒序"排,NOT IN 得换成复合条件(金额 + 主键),否则同金额的行会被整段排除。
- TOP 没有 ORDER BY 等于没定义:select top 10 ... 不带 order by 时返回哪 10 行由执行计划决定,翻两页可能给你同一批行。这是分页重复最常见的低级原因。
四、路二:键集游标(keyset)——深翻页唯一线性方案
思路换了:不数"第几行",只记"上一页最后一行的键是多少",下一页从那个键往后取。
- // 演示:键集游标翻页。ls_lastkey 是上一页末行的 id,空串表示第一页
- string ls_sql, ls_lastkey
- long ll_size
- ll_size = 10
- ls_lastkey = "20" // 上一页末行 id = 20,说明要取 21 起
- if ls_lastkey = "" then
- ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 order by id ASC"
- else
- ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 " + &
- "where id > " + ls_lastkey + " order by id ASC"
- end if
- MessageBox("键集下一页", ls_sql)
复制代码
实机读数:末行 id = 20 时取回 21..30;末行 id = 90 时取回 91..100;末行 id = 100(已在末页)时返回 0 行,SQLCA.SQLCode = 0(不是错误,就是没有下一行)。
三个特征必须知道:
- 只支持上一页 / 下一页,不支持跳页。想跳第 500 页?做不到,只能一页页走或者换方案。这是它的代价,也是它快的原因。
- 代价与页深无关:where id > 9990 order by id 走主键索引直接定位,第 1 页和第 1000 页一样快,复杂度 O(size)。
- 排序键必须唯一且有序:order by amt 这种有重复值的列当游标键,同金额的行会被整段跳过。正解是复合键:order by amt desc, id asc,游标条件写成 where amt < :last_amt or (amt = :last_amt and id > :last_id)。
顺带说一句方向:倒序翻页时比较符和排序方向必须一起反。order by id desc 配 where id < :lastkey order by id desc,写成 > 就永远返回空。
五、路三:ROW_NUMBER() OVER——能跳页的主流写法
内层给每行编个号,外层按编号区间切。这是 SQL Server 2005 之后最常用的服务端分页写法。
- // 演示:用 ROW_NUMBER() 取第 5 页(每页 10 行)
- string ls_sql, ls_inner
- long ll_page, ll_size, ll_lo, ll_hi
- ll_page = 5
- ll_size = 10
- ll_lo = (ll_page - 1) * ll_size // 40
- ll_hi = ll_lo + ll_size // 50
- ls_inner = "select id, nm, amt, row_number() over (order by id ASC) as rn from dbo.t_p104"
- ls_sql = "select * from (" + ls_inner + ") t where rn > " + String(ll_lo) + " and rn <= " + String(ll_hi) + " order by rn"
- MessageBox("第 5 页 SQL", ls_sql)
复制代码
实机读数:第 5 页 id = 41..50,行数 10。
这里有个必须踩过一次才知道的坑:把这段 SQL 再套一层派生表时,内层的 order by rn 会让 SQL Server 直接报错——派生表、内联视图里不允许出现 ORDER BY。我第一次套壳取单列时返回了 0 行,就是因为外层写成了 select id from (原来那条完整 SQL) q。正解是把末尾的 order by rn 掐掉再套:
- // 演示:要把分页结果再包一层时,先掐掉末尾的 order by rn
- string ls_sql, ls_wrap
- long ll_p
- ls_sql = "select * from (select id, row_number() over (order by id ASC) as rn from dbo.t_p104) t " + &
- "where rn > 40 and rn <= 50 order by rn"
- ll_p = Pos(Upper(ls_sql), " ORDER BY RN")
- ls_wrap = "select id from (" + Left(ls_sql, ll_p - 1) + ") q"
- MessageBox("再包一层", ls_wrap)
复制代码
实机读数:掐掉后套壳取单列,返回 id 41..50 共 10 行;不掐直接套壳,返回 0 行(SQL Server 拒绝派生表里的 ORDER BY)。
代价说明:ROW_NUMBER() 要让 SQL Server 把前 hi 行全部编号再丢掉前 lo 行,所以第 1000 页仍然要处理 10000 行。比 TOP+NOT IN 快(少一次子查询扫描),但仍然是 O(offset),深翻页一样会慢。
六、路四:OFFSET / FETCH——SQL Server 2012 起才有
语法最干净,可惜老库用不了:
- // 演示:OFFSET FETCH 写法(需 SQL Server 2012 及以上)
- string ls_sql
- long ll_page, ll_size, ll_off
- ll_page = 5
- ll_size = 10
- ll_off = (ll_page - 1) * ll_size
- ls_sql = "select id, nm, amt from dbo.t_p104 order by id ASC " + &
- "offset " + String(ll_off) + " rows fetch next " + String(ll_size) + " rows only"
- MessageBox("OFFSET FETCH", ls_sql)
复制代码
在 SQL Server 2008 R2 上实测的失败形态(这是本文最有价值的一条读数,因为报错信息不直观):
- SQLCA.SQLCode = -1
- SQLCA.SQLErrText 三行:SQLSTATE = 42000 / Microsoft SQL Server Native Client 10.0 / 'offset' 附近有语法错误。
- 注意是 42000(语法错),不是"不支持该功能"之类好懂的话。看到 42000 又确认 SQL 手工写得没错,先怀疑版本。
所以:如果你的库可能是 2008 或更早,这一段只能当"未来迁移方向"看,上线必须走第三节或第五节的写法。判断办法很简单,select @@version 看主版本号,11 及以上才支持。
七、路五:内存分页(Retrieve 全量 + SetFilter)
前四条都是"让数据库只给一页",这条反过来:一次把全部行取到客户端,再在 DataWindow 里切。
适用场景很窄但真实存在:数据量小(几千行以内)、要频繁来回翻页、网络往返比内存更贵。
- // 演示:一次性取全量,再按 id 区间切出第 5 页
- string ls_sql, ls_syn, ls_err
- long ll_rows
- datastore lds
- // 连接参数按你自己的库填
- SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
- SQLCA.LogId = "你的账号"
- SQLCA.LogPass = "你的口令"
- SQLCA.ServerName = "你的服务器,端口"
- SQLCA.Database = "你的库名"
- SQLCA.AutoCommit = False
- SQLCA.DBParm = "Provider='SQLNCLI10',DataSource='你的服务器,端口'"
- CONNECT USING SQLCA;
- if SQLCA.SQLCode <> 0 then
- MessageBox("连接失败", SQLCA.SQLErrText)
- return
- end if
- lds = create datastore
- ls_sql = "select id, amt from dbo.t_p104 order by id"
- ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
- lds.Create(ls_syn, ls_err)
- lds.SetTransObject(SQLCA)
- ll_rows = lds.Retrieve() // 全量取回,实测 100 行
- lds.SetFilter("id >= 41 and id <= 50") // 按主键区间切页
- ll_rows = lds.Filter() // 返回 1 表示成功,不是行数
- MessageBox("第 5 页", "行数=" + String(lds.RowCount()) + " 首行id=" + String(lds.GetItemNumber(1, "id")))
- destroy lds
- DISCONNECT USING SQLCA;
复制代码
实机读数:SetFilter 返回 1、Filter() 返回 1、RowCount() = 10,首行 id = 41。
注意两个返回值的语义:SetFilter() 和 Filter() 返回的都是成败码(1 成功 / -1 失败),不是过滤掉多少行。想知道切出几行,只能读 RowCount()。这一条我在别处见过不少代码写成 if lds.Filter() > 0 then,逻辑上恰好也"对",但含义完全不是作者以为的那样。
内存分页真正的坑在下一节和第十一节——行序。
八、总行数怎么拿:四条路的读数对照
页数靠总行数算,总行数有四条来源,各有各的坑:
| 来源 | 写法 | 实测读数 | 适用与坑 | | 单独 COUNT | select count(*) into :ll_c from dbo.t_p104; | 100 | 最稳。缺点是多一次往返;大表上 COUNT 本身可能比取一页还慢 | | 动态 SQL 影响行数 | EXECUTE IMMEDIATE "update ..." USING SQLCA; 后读 SQLCA.SQLNRows | 7(update 命中 7 行) | 只对增删改有意义,对 select 不可靠 | | 服务器 @@ROWCOUNT | 紧跟上一语句读 | 7(与 SQLNRows 一致) | 必须紧跟,中间插任何语句都会被覆盖;跨语句读没有意义 | | DataWindow 行数 | lds.RowCount() | 100 | 只在"已经取到全量"时等于总行数;分页取一页时它只等于本页行数 |
两条补充:
- SQLCA.SQLNRows 只有嵌入式 SQL 与动态 SQL 执行后才有意义,且受 AutoCommit 与事务边界影响,不要拿它当业务计数。
- 想要"一次往返同时拿到本页数据和总行数",常规做法是两条 SQL 一起发(或存储过程返回两个结果集),PB 侧用 DECLARE PROCEDURE + 多次 FETCH 取第二个结果集。这条路本文没展开,属于"扩展点"。
九、实测:翻页途中数据变了,offset 会重复 5 行,键集不会
这一节是全文的实测核心。场景:按 amt desc, id 排序,先取第 1 页,然后在翻页过程中插进 5 行金额更大的新数据,再取第 2 页。
第 1 页(10 行)实测 id 序列:6、13、20、27、34、41、48、55、62、69,末行 amt = 6, id = 69。
插入 5 行 amt = 99 之后:
| 分页方式 | 第 2 页与第 1 页的重叠行数 | 说明 | | offset 语义(rn between 11 and 20) | 5 行 | 新数据插在最前面,整段行号后移 5 位,第 2 页把第 1 页的后 5 行又发了一遍 | | 键集游标(amt < 6 or (amt = 6 and id > 69)) | 0 行 | 游标锚在"上一页末行的键"上,前面插入多少行都不影响 |
这不是理论推演,是同一张表、同一次会话里连跑出来的两个数:overlap_offset=5 overlap_keyset=0。
结论很直接:
- 翻页期间数据会变(有人在录入、有定时任务在写),offset 分页一定会有重复或漏行。用户会看到第 2 页出现第 1 页的内容,然后抱怨"我的系统丢数据了"。
- 键集游标不受影响。代价是不能跳页。
- 如果业务必须跳页又要一致,只能靠"快照":把结果集主键一次性取出来缓存在客户端(几千行没问题),翻页时按缓存的键去取明细。这条路等于用内存换一致性。
十、DataWindow 侧怎么承接:SetSQLSelect 的三条实测结论
服务端分页的 SQL 每翻一页都要换,DataWindow 侧换 SQL 的标准做法是 SetSQLSelect。它的行为有三条必须记住:
结论一:同列集的 SQL 换得动。 SetSQLSelect("select id, nm, amt from dbo.t_p104 where id between 11 and 20") 返回 1,随后 Retrieve() 取回 10 行。
结论二:换 SQL 不会自动重取。 SetSQLSelect 成功后立刻 RowCount(),读到的还是上一批的 100 行;必须再调一次 Retrieve() 才变成 10 行。顺序写反了就会"翻页没反应"。
结论三:改列集换不动。 把 SQL 换成 select top 10 id from ...(3 列变 1 列)时 SetSQLSelect 返回 -1,列数仍是 3,Retrieve() 之后返回的仍是上一条 SQL 的 10 行。也就是说:分页 SQL 的列集必须和建 DataWindow 时一致,少一列都失败,而且失败是静默的(返回 -1,不抛错)。
- // 演示:SetSQLSelect 换分页 SQL 的正确顺序(列集必须与建窗时一致)
- string ls_sql, ls_syn, ls_err
- long ll_rc, ll_rows
- datastore lds
- // 连接段同第七节,此处省略 CONNECT,假设 SQLCA 已连上
- lds = create datastore
- ls_sql = "select id, nm, amt from dbo.t_p104"
- ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
- lds.Create(ls_syn, ls_err)
- lds.SetTransObject(SQLCA)
- ll_rows = lds.Retrieve() // 100 行
- ls_sql = "select id, nm, amt from dbo.t_p104 where id between 11 and 20"
- ll_rc = lds.SetSQLSelect(ls_sql) // 返回 1
- ll_rows = lds.RowCount() // 仍是 100,没重取
- ll_rows = lds.Retrieve() // 这一步之后才是 10 行
- MessageBox("换页结果", "SetSQLSelect=" + String(ll_rc) + " 重取后行数=" + String(ll_rows))
- destroy lds
复制代码
实机读数:SetSQLSelect=1、重取前 RowCount=100、重取后 10 行;改列集那次 rc=-1、列数仍 3、重取后 10 行(用的是旧 SQL)。
十一、SetFilter 的四个真实语义(这一节最容易翻车)
如果选了第七节的内存分页,下面四条必须全知道,少一条就会遇到"翻页乱序"。
语义一:过滤表达式里可以用 GetRow()。 SetFilter("Mod(GetRow(), 10) = 1") 返回 1,Filter() 之后 RowCount() = 10(第 1、11、21……91 行)。也就是说"每 10 行取 1 行"这种按行号切的写法在语法上是成立的。
语义二:过滤之后行号会重排,GetRow() 不再是过滤前的行号。 过滤前 id 1..100,过滤后 10 行;此时逐行读 GetRow() 得到的是 1、2、3……,对应的 id 是 1、11、21。所以拿 GetRow() 做"第几页"的判据只在第一次过滤那一刻有效,之后再引用全是错的。
语义三:重设 filter 会从全量重新评估,不是在当前可见行上再切一刀。 先按 Mod(GetRow(),10)=1 过滤出 10 行,再设 SetFilter("id >= 41 and id <= 50") 并 Filter(),结果是 10 行(id 41..50),不是"10 行里再挑出 id=41 那 1 行"。同理 SetFilter("") + Filter() 能把 100 行全部还回来。这一点和很多人的直觉相反。
语义四(最坑):重新评估之后主缓冲区的行序会被打乱。 上面那次链式过滤之后,逐行读出来的 id 序列是:
41, 50, 49, 48, 47, 46, 45, 44, 43, 42
行数对、行的集合对,顺序错了:第 1 行是 41,第 2 行直接跳到 50,然后倒着排。原因是被重新评估放回主缓冲区的行是追加进去的,不保证沿用 SQL 的 ORDER BY。此时界面上"下一页"取到的就不是期望的那 10 行,而是乱序的一段。
修法只有一条:过滤之后补一次 Sort()。
- // 演示:链式过滤后行序被打乱,必须补 Sort() 恢复顺序
- string ls_sql, ls_syn, ls_err, ls_ids
- long ll_i, ll_rc
- datastore lds
- // 连接段同第七节,假设 SQLCA 已连上
- lds = create datastore
- ls_sql = "select id, amt from dbo.t_p104 order by id"
- ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
- lds.Create(ls_syn, ls_err)
- lds.SetTransObject(SQLCA)
- lds.Retrieve()
- lds.SetFilter("Mod(GetRow(), 10) = 1")
- lds.Filter()
- lds.SetFilter("id >= 41 and id <= 50")
- lds.Filter()
- ls_ids = ""
- for ll_i = 1 to lds.RowCount()
- ls_ids = ls_ids + String(lds.GetItemNumber(ll_i, "id")) + ","
- next
- // 此时实测:41,50,49,48,47,46,45,44,43,42 —— 顺序是乱的
- lds.SetSort("id A")
- ll_rc = lds.Sort() // 返回 1
- ls_ids = ls_ids + "~r~n排序后:"
- for ll_i = 1 to lds.RowCount()
- ls_ids = ls_ids + String(lds.GetItemNumber(ll_i, "id")) + ","
- next
- MessageBox("过滤后行序", "Sort()=" + String(ll_rc) + "~r~n过滤后:" + ls_ids)
- destroy lds
复制代码
实机读数:Sort() 返回 1,排序后序列恢复为 41,42,43,44,45,46,47,48,49,50。PB10 与 PB12.5 两个版本读数逐字相同。
十二、封装成类:nvo_pager104(完整可运行)
把前面所有计算与 SQL 生成收进一个非可视对象,业务代码就只剩"传参、取 SQL、Retrieve"三行。下面是完整对象源码,可以直接建成 NVO 使用,方法都是纯计算,不碰数据库,方便单测。
- // nvo_pager104:分页四要素计算 + 三种服务端分页 SQL 生成
- // 用法:lo = create nvo_pager104;ls_sql = lo.of_sqlTopNotIn(...);再 SetSQLSelect + Retrieve
- forward
- global type nvo_pager104 from nonvisualobject
- end type
- end forward
- global type nvo_pager104 from nonvisualobject
- end type
- global nvo_pager104 nvo_pager104
- type variables
- end variables
- forward prototypes
- public function long of_clampPage (long al_page, long al_total, long al_size)
- public function long of_offset (long al_page, long al_size)
- public function long of_totalPages (long al_total, long al_size)
- public function string of_sqlKeyset (string as_cols, string as_table, string as_key, string as_dir, string as_lastkey, long al_size)
- public function string of_sqlRowNumber (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size)
- public function string of_sqlTopNotIn (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size)
- end prototypes
- public function long of_offset (long al_page, long al_size);
- // 偏移量 = (页号 - 1) * 每页行数;页号或页大小非法一律返 0
- if al_page < 1 then return 0
- if al_size < 1 then return 0
- return (al_page - 1) * al_size
- end function
- public function long of_totalPages (long al_total, long al_size);
- long ll_pages
- // 总页数 = ceil(总行数 / 每页行数);总行数或页大小非法返 0
- if al_total < 1 then return 0
- if al_size < 1 then return 0
- ll_pages = al_total / al_size
- if Mod(al_total, al_size) > 0 then ll_pages = ll_pages + 1
- return ll_pages
- end function
- public function long of_clampPage (long al_page, long al_total, long al_size);
- long ll_pages
- // 先把请求页夹到 1..总页数;总行数为 0 时返回 1
- ll_pages = of_totalPages(al_total, al_size)
- if ll_pages < 1 then return 1
- if al_page < 1 then return 1
- if al_page > ll_pages then return ll_pages
- return al_page
- end function
- public function string of_sqlTopNotIn (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size);
- string ls_sql, ls_skip
- long ll_skip
- // TOP + NOT IN:第 1 页只有 TOP,第 2 页起用 NOT IN 子查询跳过前 (页号-1)*页大小 个键
- if al_size < 1 then return ""
- if al_page < 2 then return "select top " + String(al_size) + " " + as_cols + " from " + as_table + " order by " + as_key + " " + as_dir
- ll_skip = (al_page - 1) * al_size
- ls_skip = "select top " + String(ll_skip) + " " + as_key + " from " + as_table + " order by " + as_key + " " + as_dir
- ls_sql = "select top " + String(al_size) + " " + as_cols + " from " + as_table
- ls_sql = ls_sql + " where " + as_key + " not in (" + ls_skip + ")"
- ls_sql = ls_sql + " order by " + as_key + " " + as_dir
- return ls_sql
- end function
- public function string of_sqlRowNumber (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size);
- string ls_sql, ls_inner
- long ll_lo, ll_hi
- // ROW_NUMBER:内层编号,外层按 rn 区间取,可任意跳页
- if al_size < 1 then return ""
- ll_lo = of_offset(al_page, al_size)
- ll_hi = ll_lo + al_size
- ls_inner = "select " + as_cols + ", row_number() over (order by " + as_key + " " + as_dir + ") as rn from " + as_table
- ls_sql = "select * from (" + ls_inner + ") t where rn > " + String(ll_lo) + " and rn <= " + String(ll_hi)
- ls_sql = ls_sql + " order by rn"
- return ls_sql
- end function
- public function string of_sqlKeyset (string as_cols, string as_table, string as_key, string as_dir, string as_lastkey, long al_size);
- string ls_sql, ls_cmp, ls_order
- // 键集:只按上一页末行的键往下取,不能跳页;as_dir 传 ASC 或 DESC
- if al_size < 1 then return ""
- if as_lastkey = "" then return "select top " + String(al_size) + " " + as_cols + " from " + as_table + " order by " + as_key + " " + as_dir
- if Upper(as_dir) = "DESC" then
- ls_cmp = " < "
- ls_order = "desc"
- else
- ls_cmp = " > "
- ls_order = "asc"
- end if
- ls_sql = "select top " + String(al_size) + " " + as_cols + " from " + as_table
- ls_sql = ls_sql + " where " + as_key + ls_cmp + as_lastkey
- ls_sql = ls_sql + " order by " + as_key + " " + ls_order
- return ls_sql
- end function
复制代码
调用示例(窗口按钮里跑,含建表 SQL 与三种分页的取值):
- // 演示:建示例表 -> 用 nvo_pager104 生成三种分页 SQL -> 各取一页看 id 序列
- // 建表 SQL(PowerBuilder 之外手工执行也可以,这里用动态 SQL 建)
- // create table dbo.t_p104(id int identity(1,1) primary key,
- // grp char(1) not null, nm varchar(20) not null, amt int not null)
- // 然后插入 100 行:grp = 行号除 3 的余数,nm = 'n' + 行号,amt = 行号除 7 的余数
- string ls_out, ls_sql
- long ll_p, ll_rows
- nvo_pager104 lp
- // 连接段按自己的库填,此处略(见第七节)
- lp = create nvo_pager104
- // 1) 第 3 页:TOP + NOT IN
- ls_sql = lp.of_sqlTopNotIn("id, amt", "dbo.t_p104", "id", "ASC", 3, 10)
- // 实测该语句返回 id 21..30 共 10 行
- // 2) 第 5 页:ROW_NUMBER
- ls_sql = lp.of_sqlRowNumber("id, amt", "dbo.t_p104", "id", "ASC", 5, 10)
- // 实测该语句返回 id 41..50 共 10 行
- // 3) 上一页末行 id = 20 时的下一页:键集
- ls_sql = lp.of_sqlKeyset("id, amt", "dbo.t_p104", "id", "ASC", "20", 10)
- // 实测该语句返回 id 21..30 共 10 行
- ls_out = "总页数(105/10)=" + String(lp.of_totalPages(105, 10))
- ls_out = ls_out + " 偏移(3/10)=" + String(lp.of_offset(3, 10))
- ls_out = ls_out + " 夹取(99)=" + String(lp.of_clampPage(99, 105, 10))
- MessageBox("分页器", ls_out)
- destroy lp
复制代码
实机读数:总页数 11、偏移 20、夹取后 11;三条 SQL 分别取回 1..10 之外的 21..30、41..50、21..30,与断言预期逐字一致。这个对象在 PB10 与 PB12.5 上各跑了 16 条断言(边界值 + 四种 SQL 文本逐字比对),两版全 PASS 且读数相同。
十三、深翻页的代价:为什么第 1000 页会慢
前面几节一直在说"O(offset)",这里把话说清楚:
- TOP + NOT IN:子查询要扫 skip 行主键,外层再扫一次。第 N 页代价 ≈ 2N 行。
- ROW_NUMBER:前 hi 行全部编号后丢弃前 lo 行,第 N 页代价 ≈ N 行 + 排序。
- OFFSET FETCH:SQL Server 2012 之后同样要扫过前 lo 行,语义上等价于 ROW_NUMBER,只是写得短。
- 键集:索引定位 + 取 size 行,与页深无关。
关于性能数字,本文只给量级判断,不给"第几页几毫秒"的读数。原因很实在:PB 的 CPU() 分辨率只有十几毫秒(这一点在前一篇计时专题里实测过),本地 100 行的表也压不出可比的差值,在这种条件下测出来的毫秒数属于噪声。要量化必须用 SQL Server 侧的执行计划(SET STATISTICS IO ON 看逻辑读次数),而 PB 客户端拿不到消息输出——这一段我没有实测,按"逻辑代价"讲,明确标注:本节未实测、仅静态分析。
要缓解深翻页,只有三条路:改成键集、限制最大可跳页数(很多后台系统只允许跳到第 50 页,之后要求加筛选条件)、或者把排序键上的索引做成覆盖索引减少回表。
十四、坑清单
- top 不带 order by:返回哪几行由执行计划决定,两页可能重复。
- order by 用了非唯一列:同值的行顺序不定,offset 分页会重复或漏行;键集分页必须用复合键。
- 页号从 0 起还是从 1 起搞混:偏移量整体错一页,第一页永远是空的。
- total / size 当总页数:整除丢余数,最后一页永远取不到。
- 不夹取越界页号:换筛选条件后取到空页,界面白板。
- 换页只改 SQL 不 Retrieve():SetSQLSelect 成功后 RowCount() 还是旧值,必须重取。
- SetSQLSelect 改列集:返回 -1(静默失败),Retrieve() 仍用旧 SQL;分页 SQL 列集必须与建窗时一致。
- 把 SetFilter() / Filter() 的返回值当行数:它们是成败码(1 / -1),行数看 RowCount()。
- 过滤后行号会重排:GetRow() 拿到的是新行号,拿它判断"第几页"只在第一次过滤时对。
- 以为重设 filter 是在可见行上再切一刀:实际是从全量重新评估,结果常常比预期多得多。
- 链式过滤后不 Sort():主缓冲区行序会被打乱(实测 41,50,49,...,42),界面翻页取到乱序段。
- 派生表里带 order by:SQL Server 直接报错,PB 侧表现为取回 0 行,容易误判成"没有数据"。
- OFFSET FETCH 用在 SQL Server 2008 上:SQLCode=-1 + SQLSTATE=42000 + 'offset' 附近有语法错误,报错不提示版本。
- 倒序翻页时比较符没跟着反:order by id desc 配 where id > :lastkey,永远取回空。
- 键集分页想跳页:做不到,只能顺序走;需求上要跳页就不能选键集。
- 翻页期间数据变动:offset 分页必然重复或漏行(实测重叠 5 行),键集才稳定;这对"边翻页边录入"的系统是硬约束。
十五、PB12.5 差异
与 PB10 行为一致。本文涉及的嵌入式 SQL、SQLCA.SQLNRows、SyntaxFromSQL / Create / Retrieve、SetSQLSelect、SetFilter / Filter / SetSort / Sort / RowCount 在两个版本上的读数逐字相同,没有发现语义差异。
需要注意的只有环境层面的差异,不是 PB 语言本身的差异:
- 连接串写法不同版本要看运行库配的接口。本文示例用的是 PB12.5 上实测可用的 SNC SQL Native Client(OLE DB) + Provider='SQLNCLI10'。
- 如果本机运行库缺少对应的 OLE DB / SNC 接口 DLL,连库会直接返回 -1,看起来像"SQL 写错了",实际是环境缺件。本文的 PB10 轨就落在这种情况里(只有编译与纯计算部分能跑,连库那段返回 -1),已在第十七节如实标注。
十六、选型决策表
| 场景 | 首选方案 | 理由 | | 只要"上一页/下一页",数据量大、翻得深 | 键集游标 | 代价与页深无关,且不受并发写入影响 | | 要页码条、能跳到任意页,SQL Server 2005+ | ROW_NUMBER() | 支持跳页,比 TOP+NOT IN 少一次子查询扫描 | | SQL Server 2000/2005 老库,必须跳页 | TOP + NOT IN | 唯一可用写法,注意排序键要能进 NOT IN | | SQL Server 2012+ 新项目 | OFFSET / FETCH | 语法最短,语义与 ROW_NUMBER 等价 | | 总行数几千以内、要频繁来回翻 | 内存分页(Retrieve 全量 + SetFilter) | 省往返;但每次改 filter 后必须 Sort(),行号不可跨次引用 | | 翻页期间数据在变,且必须跳页 | 快照主键 + 按需取明细 | 用客户端内存换一致性,几千行可接受 |
十七、实机验证说明
本文所有代码都按"双轨"跑过:PB10 基线负责编译级验证,PB12.5 负责运行级验证。
- 编译:14 个对象(1 个交付类 + 12 个数据库/行为探针 + 1 个正文片段镜像)在 PB10 与 PB12.5 上分别全量重建,两版均 0 错误。
- 运行:12 个数据库/行为探针在 PB12.5 上全部 PASS,读数就是正文里引用的那些数字;交付类 nvo_pager104 的 16 条断言(边界值 + 四种 SQL 文本逐字比对)在 PB10 与 PB12.5 上都 PASS 且读数完全相同;正文六个代码片段另有镜像对象逐字比对(四要素计算 + 五条 SQL 文本),两版同样全 PASS、读数一致。
- 数据库:实测环境 SQL Server 2008 R2,示例表 t_p104 共 100 行,id 为 identity 主键。
- PB10 轨的连库部分未通过:本机 PB10 运行库缺少 OLE DB / SNC 接口 DLL,连库一律返回 -1,所以所有涉及连库的探针在 PB10 轨上只验证了"编译 0 错",没有验证运行。这一条如实标注,不含糊。连库部分的运行结论全部来自 PB12.5 轨。
- 示例中的 MessageBox 在自动断言里被换成了写文件桩:PBVM 的非可视进程里 MessageBox 会阻塞(实测 60 秒超时、进程挂住),所以断言脚本把输出写成文本文件再比对,业务逻辑与文中代码逐字一致,只是最后那行输出方式不同。读者在窗口里跑原始代码不受影响。
- 未实测项:第十三节的性能量级(理由见该节)。除此之外的每一条读数都来自实际运行。
|