祝愿大家身体健康!

 找回密码
 站点注册
查看: 22|回复: 1

[学习笔记] PowerBuilder 分页查询全解:TOP+NOT IN、键集游标、ROW_NUMBER 与 OFFSET FETCH,以及过滤后行序为何会乱(PB10 基准 · PB12.5 实测)

[复制链接]
发表于 昨天 05:47 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?站点注册

×
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 页,不夹取就会取出一个空页,界面白板。

  1. // 演示:分页四要素的计算(可直接粘进窗口按钮的 Clicked 事件运行)
  2. long ll_total      // 输入 1:总行数(由 select count(*) 得到)
  3. long ll_size       // 输入 2:每页行数
  4. long ll_page       // 输入 3:请求页号(1 起)
  5. long ll_pages, ll_off, ll_fix
  6. string ls_msg
  7. ll_total = 105
  8. ll_size  = 10
  9. ll_page  = 99        // 故意给一个越界页号,看夹取是否生效
  10. ll_pages = ll_total / ll_size
  11. if Mod(ll_total, ll_size) > 0 then ll_pages = ll_pages + 1     // 向上取整
  12. ll_off   = (ll_page - 1) * ll_size                             // 偏移量
  13. ll_fix   = ll_page
  14. if ll_fix < 1 then ll_fix = 1
  15. if ll_fix > ll_pages then ll_fix = ll_pages
  16. ls_msg = "总行数=" + String(ll_total) + "  页大小=" + String(ll_size) + "~r~n"
  17. ls_msg = ls_msg + "总页数=" + String(ll_pages) + "  请求页=" + String(ll_page) + "~r~n"
  18. ls_msg = ls_msg + "夹取后页=" + String(ll_fix) + "  偏移量=" + String(ll_off)
  19. MessageBox("分页四要素", ls_msg)
复制代码

实机读数:总页数 11、夹取后页 11、偏移量 980(请求页 99 被夹回 11)。这三个值在 PB10 与 PB12.5 上完全一致。

这三段逻辑在第十二节被封装成 of_totalPages / of_offset / of_clampPage 三个方法,并且带 16 条断言跑过双版本,本文后面直接调用它们。

三、路一:TOP + NOT IN(兼容性最好,代价随页号线性上涨)

原理:先查出前 skip 个主键,再"排除这些主键"取剩下的前 size 个。
  1. // 演示:生成第 3 页(每页 10 行)的 TOP + NOT IN 语句
  2. string ls_sql
  3. long ll_page, ll_size, ll_skip
  4. ll_page = 3
  5. ll_size = 10
  6. ll_skip = (ll_page - 1) * ll_size      // 要跳过的行数 = 20
  7. ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 " + &
  8.          "where id not in (select top " + String(ll_skip) + " id from dbo.t_p104 order by id ASC) " + &
  9.          "order by id ASC"
  10. 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)——深翻页唯一线性方案

思路换了:不数"第几行",只记"上一页最后一行的键是多少",下一页从那个键往后取。
  1. // 演示:键集游标翻页。ls_lastkey 是上一页末行的 id,空串表示第一页
  2. string ls_sql, ls_lastkey
  3. long ll_size
  4. ll_size   = 10
  5. ls_lastkey = "20"       // 上一页末行 id = 20,说明要取 21 起
  6. if ls_lastkey = "" then
  7.     ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 order by id ASC"
  8. else
  9.     ls_sql = "select top " + String(ll_size) + " id, nm, amt from dbo.t_p104 " + &
  10.              "where id > " + ls_lastkey + " order by id ASC"
  11. end if
  12. 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 之后最常用的服务端分页写法。
  1. // 演示:用 ROW_NUMBER() 取第 5 页(每页 10 行)
  2. string ls_sql, ls_inner
  3. long ll_page, ll_size, ll_lo, ll_hi
  4. ll_page = 5
  5. ll_size = 10
  6. ll_lo = (ll_page - 1) * ll_size        // 40
  7. ll_hi = ll_lo + ll_size                // 50
  8. ls_inner = "select id, nm, amt, row_number() over (order by id ASC) as rn from dbo.t_p104"
  9. ls_sql = "select * from (" + ls_inner + ") t where rn > " + String(ll_lo) + " and rn <= " + String(ll_hi) + " order by rn"
  10. 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 掐掉再套:
  1. // 演示:要把分页结果再包一层时,先掐掉末尾的 order by rn
  2. string ls_sql, ls_wrap
  3. long ll_p
  4. ls_sql = "select * from (select id, row_number() over (order by id ASC) as rn from dbo.t_p104) t " + &
  5.          "where rn > 40 and rn <= 50 order by rn"
  6. ll_p = Pos(Upper(ls_sql), " ORDER BY RN")
  7. ls_wrap = "select id from (" + Left(ls_sql, ll_p - 1) + ") q"
  8. 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 起才有

语法最干净,可惜老库用不了:
  1. // 演示:OFFSET FETCH 写法(需 SQL Server 2012 及以上)
  2. string ls_sql
  3. long ll_page, ll_size, ll_off
  4. ll_page = 5
  5. ll_size = 10
  6. ll_off  = (ll_page - 1) * ll_size
  7. ls_sql  = "select id, nm, amt from dbo.t_p104 order by id ASC " + &
  8.           "offset " + String(ll_off) + " rows fetch next " + String(ll_size) + " rows only"
  9. 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 里切。

适用场景很窄但真实存在:数据量小(几千行以内)、要频繁来回翻页、网络往返比内存更贵。
  1. // 演示:一次性取全量,再按 id 区间切出第 5 页
  2. string ls_sql, ls_syn, ls_err
  3. long ll_rows
  4. datastore lds
  5. // 连接参数按你自己的库填
  6. SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
  7. SQLCA.LogId = "你的账号"
  8. SQLCA.LogPass = "你的口令"
  9. SQLCA.ServerName = "你的服务器,端口"
  10. SQLCA.Database = "你的库名"
  11. SQLCA.AutoCommit = False
  12. SQLCA.DBParm = "Provider='SQLNCLI10',DataSource='你的服务器,端口'"
  13. CONNECT USING SQLCA;
  14. if SQLCA.SQLCode <> 0 then
  15.     MessageBox("连接失败", SQLCA.SQLErrText)
  16.     return
  17. end if
  18. lds = create datastore
  19. ls_sql = "select id, amt from dbo.t_p104 order by id"
  20. ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
  21. lds.Create(ls_syn, ls_err)
  22. lds.SetTransObject(SQLCA)
  23. ll_rows = lds.Retrieve()                        // 全量取回,实测 100 行
  24. lds.SetFilter("id >= 41 and id <= 50")          // 按主键区间切页
  25. ll_rows = lds.Filter()                          // 返回 1 表示成功,不是行数
  26. MessageBox("第 5 页", "行数=" + String(lds.RowCount()) + "  首行id=" + String(lds.GetItemNumber(1, "id")))
  27. destroy lds
  28. DISCONNECT USING SQLCA;
复制代码

实机读数:SetFilter 返回 1、Filter() 返回 1、RowCount() = 10,首行 id = 41。

注意两个返回值的语义:SetFilter() 和 Filter() 返回的都是成败码(1 成功 / -1 失败),不是过滤掉多少行。想知道切出几行,只能读 RowCount()。这一条我在别处见过不少代码写成 if lds.Filter() > 0 then,逻辑上恰好也"对",但含义完全不是作者以为的那样。

内存分页真正的坑在下一节和第十一节——行序。

八、总行数怎么拿:四条路的读数对照

页数靠总行数算,总行数有四条来源,各有各的坑:

来源写法实测读数适用与坑
单独 COUNTselect count(*) into :ll_c from dbo.t_p104;100最稳。缺点是多一次往返;大表上 COUNT 本身可能比取一页还慢
动态 SQL 影响行数EXECUTE IMMEDIATE "update ..." USING SQLCA; 后读 SQLCA.SQLNRows7(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,不抛错)。
  1. // 演示:SetSQLSelect 换分页 SQL 的正确顺序(列集必须与建窗时一致)
  2. string ls_sql, ls_syn, ls_err
  3. long ll_rc, ll_rows
  4. datastore lds
  5. // 连接段同第七节,此处省略 CONNECT,假设 SQLCA 已连上
  6. lds = create datastore
  7. ls_sql = "select id, nm, amt from dbo.t_p104"
  8. ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
  9. lds.Create(ls_syn, ls_err)
  10. lds.SetTransObject(SQLCA)
  11. ll_rows = lds.Retrieve()                       // 100 行
  12. ls_sql = "select id, nm, amt from dbo.t_p104 where id between 11 and 20"
  13. ll_rc = lds.SetSQLSelect(ls_sql)               // 返回 1
  14. ll_rows = lds.RowCount()                       // 仍是 100,没重取
  15. ll_rows = lds.Retrieve()                       // 这一步之后才是 10 行
  16. MessageBox("换页结果", "SetSQLSelect=" + String(ll_rc) + "  重取后行数=" + String(ll_rows))
  17. 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()。
  1. // 演示:链式过滤后行序被打乱,必须补 Sort() 恢复顺序
  2. string ls_sql, ls_syn, ls_err, ls_ids
  3. long ll_i, ll_rc
  4. datastore lds
  5. // 连接段同第七节,假设 SQLCA 已连上
  6. lds = create datastore
  7. ls_sql = "select id, amt from dbo.t_p104 order by id"
  8. ls_syn = SQLCA.SyntaxFromSQL(ls_sql, "Style(Type=Grid)", ls_err)
  9. lds.Create(ls_syn, ls_err)
  10. lds.SetTransObject(SQLCA)
  11. lds.Retrieve()
  12. lds.SetFilter("Mod(GetRow(), 10) = 1")
  13. lds.Filter()
  14. lds.SetFilter("id >= 41 and id <= 50")
  15. lds.Filter()
  16. ls_ids = ""
  17. for ll_i = 1 to lds.RowCount()
  18.     ls_ids = ls_ids + String(lds.GetItemNumber(ll_i, "id")) + ","
  19. next
  20. // 此时实测:41,50,49,48,47,46,45,44,43,42 —— 顺序是乱的
  21. lds.SetSort("id A")
  22. ll_rc = lds.Sort()                       // 返回 1
  23. ls_ids = ls_ids + "~r~n排序后:"
  24. for ll_i = 1 to lds.RowCount()
  25.     ls_ids = ls_ids + String(lds.GetItemNumber(ll_i, "id")) + ","
  26. next
  27. MessageBox("过滤后行序", "Sort()=" + String(ll_rc) + "~r~n过滤后:" + ls_ids)
  28. destroy lds
复制代码

实机读数:Sort() 返回 1,排序后序列恢复为 41,42,43,44,45,46,47,48,49,50。PB10 与 PB12.5 两个版本读数逐字相同。

十二、封装成类:nvo_pager104(完整可运行)

把前面所有计算与 SQL 生成收进一个非可视对象,业务代码就只剩"传参、取 SQL、Retrieve"三行。下面是完整对象源码,可以直接建成 NVO 使用,方法都是纯计算,不碰数据库,方便单测。
  1. // nvo_pager104:分页四要素计算 + 三种服务端分页 SQL 生成
  2. // 用法:lo = create nvo_pager104;ls_sql = lo.of_sqlTopNotIn(...);再 SetSQLSelect + Retrieve
  3. forward
  4. global type nvo_pager104 from nonvisualobject
  5. end type
  6. end forward
  7. global type nvo_pager104 from nonvisualobject
  8. end type
  9. global nvo_pager104 nvo_pager104
  10. type variables
  11. end variables
  12. forward prototypes
  13. public function long of_clampPage (long al_page, long al_total, long al_size)
  14. public function long of_offset (long al_page, long al_size)
  15. public function long of_totalPages (long al_total, long al_size)
  16. public function string of_sqlKeyset (string as_cols, string as_table, string as_key, string as_dir, string as_lastkey, long al_size)
  17. public function string of_sqlRowNumber (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size)
  18. public function string of_sqlTopNotIn (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size)
  19. end prototypes
  20. public function long of_offset (long al_page, long al_size);
  21.     // 偏移量 = (页号 - 1) * 每页行数;页号或页大小非法一律返 0
  22.     if al_page < 1 then return 0
  23.     if al_size < 1 then return 0
  24.     return (al_page - 1) * al_size
  25. end function
  26. public function long of_totalPages (long al_total, long al_size);
  27.     long ll_pages
  28.     // 总页数 = ceil(总行数 / 每页行数);总行数或页大小非法返 0
  29.     if al_total < 1 then return 0
  30.     if al_size < 1 then return 0
  31.     ll_pages = al_total / al_size
  32.     if Mod(al_total, al_size) > 0 then ll_pages = ll_pages + 1
  33.     return ll_pages
  34. end function
  35. public function long of_clampPage (long al_page, long al_total, long al_size);
  36.     long ll_pages
  37.     // 先把请求页夹到 1..总页数;总行数为 0 时返回 1
  38.     ll_pages = of_totalPages(al_total, al_size)
  39.     if ll_pages < 1 then return 1
  40.     if al_page < 1 then return 1
  41.     if al_page > ll_pages then return ll_pages
  42.     return al_page
  43. end function
  44. public function string of_sqlTopNotIn (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size);
  45.     string ls_sql, ls_skip
  46.     long ll_skip
  47.     // TOP + NOT IN:第 1 页只有 TOP,第 2 页起用 NOT IN 子查询跳过前 (页号-1)*页大小 个键
  48.     if al_size < 1 then return ""
  49.     if al_page < 2 then return "select top " + String(al_size) + " " + as_cols + " from " + as_table + " order by " + as_key + " " + as_dir
  50.     ll_skip = (al_page - 1) * al_size
  51.     ls_skip = "select top " + String(ll_skip) + " " + as_key + " from " + as_table + " order by " + as_key + " " + as_dir
  52.     ls_sql = "select top " + String(al_size) + " " + as_cols + " from " + as_table
  53.     ls_sql = ls_sql + " where " + as_key + " not in (" + ls_skip + ")"
  54.     ls_sql = ls_sql + " order by " + as_key + " " + as_dir
  55.     return ls_sql
  56. end function
  57. public function string of_sqlRowNumber (string as_cols, string as_table, string as_key, string as_dir, long al_page, long al_size);
  58.     string ls_sql, ls_inner
  59.     long ll_lo, ll_hi
  60.     // ROW_NUMBER:内层编号,外层按 rn 区间取,可任意跳页
  61.     if al_size < 1 then return ""
  62.     ll_lo = of_offset(al_page, al_size)
  63.     ll_hi = ll_lo + al_size
  64.     ls_inner = "select " + as_cols + ", row_number() over (order by " + as_key + " " + as_dir + ") as rn from " + as_table
  65.     ls_sql = "select * from (" + ls_inner + ") t where rn > " + String(ll_lo) + " and rn <= " + String(ll_hi)
  66.     ls_sql = ls_sql + " order by rn"
  67.     return ls_sql
  68. end function
  69. public function string of_sqlKeyset (string as_cols, string as_table, string as_key, string as_dir, string as_lastkey, long al_size);
  70.     string ls_sql, ls_cmp, ls_order
  71.     // 键集:只按上一页末行的键往下取,不能跳页;as_dir 传 ASC 或 DESC
  72.     if al_size < 1 then return ""
  73.     if as_lastkey = "" then return "select top " + String(al_size) + " " + as_cols + " from " + as_table + " order by " + as_key + " " + as_dir
  74.     if Upper(as_dir) = "DESC" then
  75.         ls_cmp = " < "
  76.         ls_order = "desc"
  77.     else
  78.         ls_cmp = " > "
  79.         ls_order = "asc"
  80.     end if
  81.     ls_sql = "select top " + String(al_size) + " " + as_cols + " from " + as_table
  82.     ls_sql = ls_sql + " where " + as_key + ls_cmp + as_lastkey
  83.     ls_sql = ls_sql + " order by " + as_key + " " + ls_order
  84.     return ls_sql
  85. end function
复制代码

调用示例(窗口按钮里跑,含建表 SQL 与三种分页的取值):
  1. // 演示:建示例表 -> 用 nvo_pager104 生成三种分页 SQL -> 各取一页看 id 序列
  2. // 建表 SQL(PowerBuilder 之外手工执行也可以,这里用动态 SQL 建)
  3. //   create table dbo.t_p104(id int identity(1,1) primary key,
  4. //                           grp char(1) not null, nm varchar(20) not null, amt int not null)
  5. //   然后插入 100 行:grp = 行号除 3 的余数,nm = 'n' + 行号,amt = 行号除 7 的余数
  6. string ls_out, ls_sql
  7. long ll_p, ll_rows
  8. nvo_pager104 lp
  9. // 连接段按自己的库填,此处略(见第七节)
  10. lp = create nvo_pager104
  11. // 1) 第 3 页:TOP + NOT IN
  12. ls_sql = lp.of_sqlTopNotIn("id, amt", "dbo.t_p104", "id", "ASC", 3, 10)
  13. // 实测该语句返回 id 21..30 共 10 行
  14. // 2) 第 5 页:ROW_NUMBER
  15. ls_sql = lp.of_sqlRowNumber("id, amt", "dbo.t_p104", "id", "ASC", 5, 10)
  16. // 实测该语句返回 id 41..50 共 10 行
  17. // 3) 上一页末行 id = 20 时的下一页:键集
  18. ls_sql = lp.of_sqlKeyset("id, amt", "dbo.t_p104", "id", "ASC", "20", 10)
  19. // 实测该语句返回 id 21..30 共 10 行
  20. ls_out = "总页数(105/10)=" + String(lp.of_totalPages(105, 10))
  21. ls_out = ls_out + "  偏移(3/10)=" + String(lp.of_offset(3, 10))
  22. ls_out = ls_out + "  夹取(99)=" + String(lp.of_clampPage(99, 105, 10))
  23. MessageBox("分页器", ls_out)
  24. 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 秒超时、进程挂住),所以断言脚本把输出写成文本文件再比对,业务逻辑与文中代码逐字一致,只是最后那行输出方式不同。读者在窗口里跑原始代码不受影响。
  • 未实测项:第十三节的性能量级(理由见该节)。除此之外的每一条读数都来自实际运行。
共享共进共赢
Sharing And Win-win Results
SYBASEBBS - 免责申明1、欢迎访问“SYBASEBBS.COM”,本文内容及相关资源来源于网络,版权归版权方所有!本站原创内容版权归本站所有,请勿转载!
2、本文内容仅代表作者观点,不代表本站立场,作者自负,本站资源仅供学习研究,请勿非法使用,否则后果自负!请下载后24小时内删除!
3、本文内容,包括但不限于源码、文字、图片等,仅供参考。本站不对其安全性,正确性等作出保证。但本站会尽量审核会员发表的内容。
4、如本帖侵犯到任何版权问题,请立即告知本站 ,本站将及时删除并致以最深的歉意!客服邮箱:admin@sybasebbs.com
 楼主| 发表于 昨天 05:55 | 显示全部楼层
本帖最后由 pbai 于 2026-11-21 05:56 编辑

PB10 兼容版交付包:nvo_pager104(分页计算 + 三种分页 SQL 生成器)

本文正文里用到的分页逻辑已经封装成一个非可视对象,做成 PB10 兼容版 PBL 打包放在本帖附件,另附自验证件,可自行复跑核对读数。

一、包内有什么

  • pager104 相关 PBL(PB10 兼容版编译产物,全量重建 0 错误)
  • src/nvo_pager104.sru —— 对象源码(GBK / 无 BOM),六个方法:of_offset、of_totalPages、of_clampPage、of_sqlTopNotIn、of_sqlRowNumber、of_sqlKeyset
  • tests/t_pager104.pbtest —— 自验证件,16 条断言,跑完生成 pager104_result.txt,正常内容为 FAIL=[](空)
  • readme104.txt —— 使用说明(部署步骤、调用样例、常见坑、期望读数)


art104_pb10.zip (10.65 KB, 下载次数: 2)

二、环境要求

  • PowerBuilder 10 或 12.5(对象只用 PB 原生语法,两版行为一致)
  • Windows 7 及以上,32 位 PB 运行库
  • 不需要任何第三方组件库;对象本身是纯计算,不连库,因此不受数据库接口 DLL 影响


三、部署与使用步骤

  • 解包,把 PBL 放进应用目录,或把 pager104.pbl 加入 Library List
  • 也可以只取 src/nvo_pager104.sru,用 Entry &gt; Import 导入到你自己的 PBL(效果相同)
  • 调用:先 create nvo_pager104,用 of_totalPages / of_clampPage 算好页数与页号,再用 of_sqlRowNumber(或其他两个)取分页 SQL
  • 把 SQL 交给 DataWindow:SetSQLSelect(ls_sql) 之后再 Retrieve(),两步都要有
  • 用完 destroy


四、数据库说明(正文示例用表)
对象本身不依赖数据库,但正文的读数来自下表(SQL Server 2008 R2 实测),要复现请先建表并插 100 行:
  1. create table dbo.t_p104(
  2.     id  int identity(1,1) primary key,
  3.     grp char(1)     not null,
  4.     nm  varchar(20) not null,
  5.     amt int         not null);
  6. -- 插 100 行:grp = 行号除 3 余数,nm = 'n' + 行号,amt = 行号除 7 余数
  7. declare @i int = 1;
  8. while @i &lt;= 100
  9. begin
  10.     insert into dbo.t_p104(grp, nm, amt)
  11.     values(cast(@i % 3 as char(1)), 'n' + cast(@i as varchar(10)), @i % 7);
  12.     set @i = @i + 1;
  13. end
  14. select count(*) from dbo.t_p104;   -- 应为 100
复制代码

五、实机验证情况

  • 编译:PB10 兼容版全量重建 0 错误;同源码在 PB 12.5 下亦 0 错误
  • 运行:包内 tests/t_pager104.pbtest 在 PB10 与 PB 12.5 上均 PASS,16 条断言全中,两版读数逐字一致
  • 双轨独立复验:把附件 ZIP 分别在 PB10、PB 12.5 环境独立解包后各自 rebuild --type full(两版均 0 错误)+ 对交付 PBL 本体跑 pbtest,读数均为 FAIL=[](空)
  • 连库部分:分页 SQL 的真实读数(第 1/3/5 页 id 区间、并发插入后 offset 重叠 5 行 / 键集重叠 0 行)在 PB 12.5 + SQL Server 2008 R2 上跑通,详见正文第九、十二节
  • 未实测项:深翻页的性能数字。PB 的 CPU() 分辨率只有十几毫秒,100 行的表压不出可比差值,正文按逻辑代价讲并已标注


六、两个最容易踩的点(包内 readme 第七节有完整清单)

  • SetSQLSelect 之后不 Retrieve(),界面不会变;改列集的 SQL 会返回 -1 且静默继续用旧 SQL
  • 内存分页(Retrieve 全量 + SetFilter)每次重设 filter 后会从全量重新评估并打乱主缓冲区行序,必须补一次 Sort() 才能保证翻页顺序
共享共进共赢
Sharing And Win-win Results
您需要登录后才可以回帖 登录 | 站点注册

本版积分规则

免责声明:
本站所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。

Mail To:Admin@SybaseBbs.com

客服微信:18669893686
挂谷猜想 · 探索

Archiver|PowerBuilder(PB)BBS社区 ( 鲁ICP备2021027222号-1 )

GMT+8, 2026-11-22 04:43 , Processed in 0.019314 second(s), 9 queries , MemCached On.

Powered by Discuz! X5.0

© 2001-2026 Discuz! Team.

快速回复 返回顶部 返回列表