| 基本查询
select twhinh215.item, twhinh215.cwar, twhinh215.sqty
from twhinh215
where twhinh215.item = :item
selectdo
| 处理每条记录
endselect
| 带排序
select twhinh215.*
from twhinh215
where twhinh215.item = :item
order by twhinh215._index1
selectdo
| ...
endselect
| 聚合查询
select twhinh215.item, sum(twhinh215.sqty):total.qty, count(*):line.count
from twhinh215
where twhinh215.item = :item
group by twhinh215.item
selectdo
message("Item: %s, Total: %f, Lines: %d", twhinh215.item, total.qty, line.count)
endselect
| 限制结果行数
select twhinh215.*
from twhinh215
where twhinh215.item = :item
as set with 1 rows
selectdo
| 只取第一条
endselect
2.2 INSERT / UPDATE / DELETE
| 插入
insert into twhinh215 (item, cwar, sqty)
values (:item, :cwar, :qty)
| 更新
update twhinh215
set twhinh215.sqty = :qty
where twhinh215.item = :item
and twhinh215.cwar = :cwar
| 删除
delete from twhinh215
where twhinh215.item = :item
and twhinh215.cwar = :cwar
2.3 子查询
| EXISTS 子查询
select twhinh215.*
from twhinh215
where twhinh215.item = :item
and exists(
select twhinh200.cwar
from twhinh200
where twhinh200.item = twhinh215.item )
selectdo
| ...
endselect
| IN 子查询
select twhinh215.*
from twhinh215
where twhinh215.item in (
select tccom001.item from tccom001
where tccom001.kitm = tcyesno.yes )
selectdo
| ...
endselect
三、索引策略
3.1 LN 索引类型
索引类型
说明
用途
主键索引 (_index1)
主键字段组合
精确查找
辅助索引 (_index2 ~ _indexN)
其他字段组合
特定查询场景
唯一索引
确保字段唯一性
数据完整性
3.2 索引使用原则
#
原则
说明
1
WHERE 条件优先使用索引
确保查询字段有对应索引
2
使用 as set with N rows
限制结果集大小
3
避免 SELECT *
只查询需要的字段
4
利用 ORDER BY 索引
避免排序操作
5
使用 hint 指定索引（必要时）
from twhinh215 hint twhinh215._index2
3.3 检查索引使用
| 使用 ORDER BY 与索引一致，避免数据库排序
select twhinh215.*
from twhinh215
where twhinh215.item = :item
order by twhinh215._index1 | 与主键索引一致
selectdo
| ...
endselect
| 读取记录
twhinh215.item = "ITM000123"
twhinh215.cwar = "WH001"
ret = db.row(twhinh215, db.FIND.BY.KEYS)
if ret = 0 then
message("Qty: %f", twhinh215.sqty)
else
message("Not found")
endif
| 逐行遍历
ret = db.first(twhinh215, db.FIND.BY.KEYS)
while ret = 0
| 处理每条记录
ret = db.next(twhinh215, db.FIND.BY.KEYS)
endwhile
| 写入记录
twhinh215.item = "ITM000123"
twhinh215.cwar = "WH001"
twhinh215.sqty = 100.0
ret = db.insert(twhinh215, db.RETURN.ALL)
| 更新记录
twhinh215.sqty = 200.0
ret = db.update(twhinh215, db.RETURN.ALL)
| 删除记录
ret = db.delete(twhinh215, db.RETURN.ALL)
4.2 Table.io vs SQL 选择指南
场景
推荐
原因
批量查询
SQL
数据库一次返回，减少网络往返
逐行复杂逻辑
Table.io
每行可做条件判断和分支
聚合统计
SQL
SUM/COUNT/AVG 在数据库层计算
简单查找
两者皆可
Table.io 代码更简洁
需要加锁
Table.io
db.LOCK 参数
多表关联
SQL
JOIN 更高效
五、db.bind 详解
5.1 db.bind() 用途
db.bind() 将数据库记录绑定到表缓冲区，支持锁定和并发控制。
5.2 基本用法
| 只读绑定（不加锁）
twhinh215.item = "ITM000123"
twhinh215.cwar = "WH001"
ret = db.bind(twhinh215, db.FIND.BY.KEYS)
| ret = 0: 找到记录
| 读写绑定（加锁）
ret = db.bind(twhinh215, db.FIND.BY.KEYS, db.LOCK)
| ret = db.error.DBRECORDLOCKED: 记录被锁定
| 释放绑定
db.release(twhinh215)
5.3 db.bind() 与 DAL 配合
function long update_via_dal()
{
long ret
string dal.name(20)
dal.name = "twhinh215"
| 1. db.bind 加锁
twhinh215.item = "ITM000123"
twhinh215.cwar = "WH001"
ret = db.bind(twhinh215, db.FIND.BY.KEYS, db.LOCK)
if ret <> 0 then
if ret = db.error.DBRECORDLOCKED then
message("Record locked")
endif
return(ret)
endif
| 2. dal.change.object 通知 DAL
ret = dal.change.object(dal.name)
if ret <> 0 then
db.release(twhinh215)
return(ret)
endif
| 3. 修改字段
dal.set.field("whinh215.sqty", 200.0)
| 4. 保存
ret = dal.save.object(dal.name)
| 5. 释放（始终执行）
db.release(twhinh215)
return(ret)
}
5.4 db.bind() 错误码
返回值
常量
含义
0
—
成功
e (非零)
db.error.DBRECORDLOCKED
记录被锁定
e (非零)
db.error.DBNOTFOUND
记录不存在
e (非零)
db.error.DBERROR
数据库错误
六、批量操作技巧
6.1 批量处理 vs 逐行处理
| 不推荐：循环内逐行查询（N+1 问题）
for i = 1 to item.count
select twhinh215.sqty
from twhinh215
where twhinh215.item = :item.array(i)
as set with 1 rows
selectdo
total = total + twhinh215.sqty
endselect
endfor
| 推荐：一次查询 + TABLE 变量缓存
table t_temp
select twhinh215.item, twhinh215.sqty
from twhinh215
where twhinh215.item in (:item.array)
selectdo
t_temp(1, t_temp.MAX + 1).item = twhinh215.item
t_temp(1, t_temp.MAX + 1).sqty = twhinh215.sqty
endselect
| 遍历缓存结果
long i
for i = 1 to t_temp.MAX
total = total + t_temp(i).sqty
endfor
6.2 使用 HAVING 和 WHERE 过滤
| 不推荐：查询所有数据后在 4GL 中过滤
table t_temp
select twhinh215.item, sum(twhinh215.sqty):total
from twhinh215
group by twhinh215.item
selectdo
if total > 100 then
| 处理
endif
endselect
| 推荐：在 SQL 中用 HAVING 过滤
select twhinh215.item, sum(twhinh215.sqty):total
from twhinh215
group by twhinh215.item
having sum(twhinh215.sqty) > 100
selectdo
| 只处理符合条件的
endselect
| 反模式：嵌套循环查询（O(N*M)）
for i = 1 to order.count
for j = 1 to item.count
select twhinh215.sqty
from twhinh215
where twhinh215.item = :item.array(j)
and twhinh215.orno = :order.array(i)
as set with 1 rows
selectdo
| ...
endselect
endfor
endfor
| 优化：JOIN 一次查询（O(N+M)）
select tdsls401.item, tdsls401.amnt(1)
from tdsls401
where tdsls401.orno in (:order.array)
selectdo
| 一次查询完成
endselect
八、事务管理
8.1 事务控制
| 显式事务
commit.transaction() | 提交当前事务
rollback.transaction() | 回滚当前事务
| 嵌套事务
long savepoint
savepoint = db.savepoint("my_savepoint")
| ... 操作 ...
if error then
db.rollback.to(savepoint)
endif