数据库
本章主要展示有关数据库的代码参考，比如从数据库中用SQL读入序表、返回用SQL计算的单值结果、用数据库的存储过程返回（多个）序表、令数据库执行SQL语句以修改数据、用程序代码连接和断开数据库、由程序代码自行管理事务的提交、取得数据库错误信息、使用游标分批读入较多数据、将序表（排列）写入数据库等。
从数据库中用 SQL 读入序表
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=demo.query("select * from EMPLOYEE where EID=?",1) |
|
返回用 SQL 计算的单值结果
|
|
A |
|
|
1 |
=demo.query@1("select count(*) from EMPLOYEE") |
|
|
2 |
=demo.query@1("select count(*) from EMPLOYEE where SALARY>?",10000) |
|
用数据库的存储过程返回（多个）序表
|
|
A |
|
|
1 |
=db.proc("{call proc1(?,?)}",:101:"o":a,:101:"o":b) |
执行存储过程，返回两个序表 |
|
2 |
=A1(1) |
第一个序表 |
|
3 |
=A1(2) |
第二个序表 |
|
4 |
=a |
用变量名访问第一个序表 |
令数据库执行 SQL 语句以修改数据
|
|
A |
|
|
1 |
>demo.execute("update SCORES set SCORE=? where STUDENTID=10",90) |
更新 |
|
2 |
=create(wineID,wineName,wineType,wineProduction,wineStock).record([100,"Amaretto","Liqueur","France",2130,200,"Tia Maria","Liqueur","Jamaica",1570]) |
|
|
3 |
>demo.execute(A2,"update LIQUORS set STOCK=? where NAME=?", wineStock, wineName) |
批量更新 |
|
4 |
>demo.execute([1,3,5],"delete from ADVENTURE where AID=?",~) |
批量删除 |
|
5 |
>demo.execute(A2,"insert into LIQUORS (LID, NAME, TYPE, PRODUCTION, STOCK) values (?,?,?,?,?)",wineID, wineName, wineType, wineProduction, wineStock) |
批量插入
|
用程序代码连接和断开数据库
|
|
A |
|
|
1 |
=connect("demo") |
连接数据库 |
|
2 |
>A1.close() |
断开连接 |
由程序代码自行管理事务的提交
|
|
A |
B |
|
|
1 |
=connect@e("demo") |
|
建立连接 |
|
2 |
=A1.execute@k(.…) |
|
|
|
3 |
=A1.error() |
|
读取上一条数据库执行的错误信息 |
|
4 |
if A3==0 |
>A1.commit() |
无错则提交 |
|
5 |
else |
>A1.rollback() |
有错则回滚 |
|
6 |
>A1.close() |
|
关闭连接 |
取得数据库错误信息
|
|
A |
|
|
1 |
=connect@e("demo") |
|
|
2 |
… |
|
|
3 |
=A1.error() |
错误代码 |
|
4 |
=A1.error@m() |
错误信息 |
使用游标分批读入较多数据
|
|
A |
B |
C |
|
|
1 |
=demo.cursor("select * from STOCKRECORDS") |
|
|
|
|
2 |
for |
|
|
|
|
3 |
|
=A1.fetch(1000) |
|
取1000条返回成序表 |
|
4 |
|
if B3==null |
break |
数据取完中断 |
|
5 |
|
… |
|
|
将序表（排列）写入数据库
|
|
A |
|
|
1 |
=demo.query("select ID, NAME,GENDER,AGE from STUDENTS") |
|
|
2 |
=A1.keys(ID) |
|
|
3 |
=demo.update(A1,STUDENTS1,ID, NAME) |
|
|
4 |
=demo.update@u(A1,STUDENTS1,ID, NAME) |
只生成"update" |
|
5 |
=demo.update@i(A1,STUDENTS1, ID, NAME) |
只生成"insert" |
|
6 |
=demo.update@a(A1,STUDENTS1, ID,NAME) |
先清空目标表再insert |