Technical Blog Post
Abstract
嵌入式SQL在RPG中的使用范例
Body
嵌入式SQL在RPG中的使用范例
众所周知,嵌入式SQL在RPG程序中的使用越来越普遍,嵌入式SQL包括静态SQL和动态SQL,而RPG程序有自由格式和非自由格式两种格式。本文主要介绍嵌入式SQL在RPG中不同格式的使用范例,供大家编写时参考。
嵌入SQL的RPG程序类型定义为SQLRPGLE,创建程序命令为CRTSQLRPGI, 例如:CRTSQLRPGI OBJ(libname/pramname) SRCFILE(libname/srcmember),或者直接使用编译选项14 进行编译。
1. 静态SQL嵌入到自由格式的RPG程序中
本文例子中创建了一个简单的员工表,包括部门编号Dept,员工姓名Emplname,工资Sal三列,所执行的操作是将某部门编号为DeptID的员工工资涨幅比例为RaisePct,该操作逻辑简单,所以使用静态SQL进行update来实现即可。
自由格式编写较简便,在每个SQL语句前添加exec sql 即可,变量赋值时使用exec sql set,变量前加“:” ,或者也可以直接在RPG里赋值DeptID = 1。在update语句中使用主变量可以提供更好的灵活性,依据主变量RaisePct,按不同部门各自的比例更新表中的所有行,完整的SQL语句:
exec sql update EmplTable
set Sal = Sal + (Sal * :RaisePct)
where Dept = :DeptID;
程序结束时要使用*InLr = *On和return一起作为程序结束的标志,*InLr = *On表示强制将内存中的数据写到磁盘,400缺省的是BLOCK输出,即数据记录满一个BLOCK 块时才会将这一组记录写到磁盘上。那么如果这时BLOCK 没满,数据信息不会立刻写到磁盘上。之后有其它作业用到该文件,读取的数据就不完整。如果不写return,程序执行完最后一句后,将会再从第一条开始执行,造成死循环。
************** Beginning of data *************************************
d*
d RaisePct S 5P 2
d DeptID S 10U 0
d c1 S 10U 0
d c2 S 10
d c3 S 4F
**
**
/FREE
exec sql set :RaisePct = 0.20;
DeptID = 1;
exec sql drop table EmplTable;
exec sql create table EmplTable
(Dept int, Emplname char(20), Sal float);
exec sql insert into EmplTable values(1,'Jim',1000),(2,'Bob',1000);
exec sql select * into :c1, :c2, :c3 from EmplTable;
exec sql update EmplTable
set Sal = Sal + (Sal * :RaisePct)
where Dept = :DeptID;
exec sql select * into :c1, :c2, :c3 from EmplTable;
*InLr = *On ;
return;
/END-FREE
******************End of data ***************************************
2. 静态SQL嵌入到非自由格式的RPG程序中
该例子程序与例子1实现操作相同,只是使用的是非自由格式的RPG编程,每一句SQL都以C/EXEC SQL 开始,以C/END-EXEC结束,中间的SQL以C+写入SQL语句。程序结尾处要SETON LR和RETURN,与自由格式中的*InLr = *On和return相对应。
*************** Beginning of data *************************************
*
d*
d RaisePct S 5P 2 INZ(0.2)
d DeptID S 10U 0 INZ(1)
d c1 S 10U 0
d c2 S 10
d c3 S 4F
**
**
C/EXEC SQL drop table EmplTable
C/END-EXEC
C/EXEC SQL create table EmplTable
C+ (Dept int, Emplname char(20), Sal float)
C/END-EXEC
C/EXEC SQL insert into EmplTable
C+ values(1,'Jim',1000),(2,'Bob',1000)
C/END-EXEC
C/EXEC SQL select * into :c1, :c2, :c3 from EmplTable
C/END-EXEC
C/EXEC SQL update EmplTable
C+ set Sal = Sal + (Sal * :RaisePct)
C+ where Dept = :DeptID
C/END-EXEC
C/EXEC SQL select * into :c1, :c2, :c3 from EmplTable
C/END-EXEC
C SETON LR
C RETURN
******************* End of data **************************************
3. 动态SQL嵌入到自由格式的RPG程序中
上面例子中的条件都很简单,是对某个部门员工工资统一调薪。假如条件是可变的,可以按照部门调整UpdByDept,也可以按照工作类型调整UpdByJob,或者按照入职时间来调整工资UpdByDate,这个时候就需要使用动态嵌入式SQL。
将所需的SQL语句放入字符型变量,通过一次prepare, 然后可以多次通过execute来执行。该示例中使用字符’?’ 来替代主变量,每次execute, 可以通过Using子句来指定不同的值。有两个参数标记’?’,分别在SET子句和WHERE子句,所以在Using子句中要按照顺序列出主变量。
动态SQL中使用select … into,必须使用cursor,如示例中declare cursor, open cursor, close cursor。
*************** Beginning of data *************************************
d*
d RaisePct S 5P 2
d DeptID S 10A
d c1 S 10U 0
d c2 S 10
d c3 S 4F
D stmt S 200A
D varnum S 5A
D CompareValue S 10A
D Pos S 10A
D StartDate S 20A
D WhereClause S 100A
C*
/FREE
exec sql DECLARE cursor1 CURSOR FOR s1;
RaisePct = 0.20;
exec sql set :DeptID = 1;
exec sql drop table EmplTable;
exec sql create table EmplTable
(Dept int, Emplname char(20), Sal float);
exec sql insert into EmplTable values(1,'Jim',1000),(2,'Bob',1000);
exec sql select * into :c1, :c2, :c3 from EmplTable;
Select;
When varnum = 'UpdByDept';
WhereClause = 'Dept = ?';
CompareValue = DeptID;
When varnum = 'UpdByJob';
WhereClause = 'Job = ? ' ;
CompareValue = Pos;
when varnum = 'UpdByDate';
WhereClause = 'HireDate < ?';
CompareValue = StartDate;
EndSl;
Stmt = 'UPDATE EmplTable SET Sal = Sal + (Sal * ?) WHERE '
+ WhereClause;
Exec SQL PREPARE s1 from :Stmt;
Exec SQL EXECUTE s1 Using :RaisePct, :DeptID;
exec sql select * into :c1, :c2, :c3 from EmplTable;
exec sql open cursor1;
exec sql close cursor1;
*InLr = *On ;
return;
/END-FREE
****************** End of data ***************************************
4. 动态SQL嵌入到非自由格式的RPG程序中
本例子程序是例子3的非自由格式书写。Select When是RPG语句,所以里面的赋值使用EVAL即可。也是将所需的SQL语句放入字符型变量,通过一次prepare, 然后通过execute来执行,每次execute可以通过Using子句来指定不同的值。
*************** Beginning of data ************************************
D*
D RaisePct S 5P 2
D DeptID S 10A
D c1 S 10U 0
D c2 S 10
D c3 S 4F
D stmt S 200A
D varnum S 5A
D CompareValue S 10A
D Pos S 10A
D StartDate S 20A
D WhereClause S 100A
C*
C/EXEC SQL DECLARE cursor1 CURSOR FOR s1
C/END-EXEC
C/EXEC SQL set :RaisePct = 0.20
C/END-EXEC
C/EXEC SQL set :DeptID = 1
C/END-EXEC
C/EXEC SQL drop table EmplTable
C/END-EXEC
C/EXEC SQL create table EmplTable
C+ (Dept int, Emplname char(20), Sal float)
C/END-EXEC
C/EXEC SQL insert into EmplTable values(1,'Jim',1000),(2,'Bob',1000)
C/END-EXEC
C/EXEC SQL select * into :c1, :c2, :c3 from EmplTable
C/END-EXEC
C Select
C When varnum = 'UpdByDept'
C EVAL WhereClause = 'Dept = ?'
C EVAL CompareValue = DeptID
C When varnum = 'UpdByJob'
C EVAL WhereClause = 'Job = ? '
C EVAL CompareValue = Pos
C When varnum = 'UpdByDate'
C EVAL WhereClause = 'HireDate < ?'
C EVAL CompareValue = StartDate
C ENDSL
C Eval stmt = 'UPDATE EmplTable SET Sal = '
C +'Sal + (Sal * ?) WHERE '
C + WhereClause
C/EXEC SQL PREPARE s1 from :Stmt
C/END-EXEC
C/EXEC SQL EXECUTE s1 Using :RaisePct, :DeptID
C/END-EXEC
C/EXEC SQL select * into :c1, :c2, :c3 from EmplTable
C/END-EXEC
C/EXEC SQL OPEN cursor1
C/END-EXEC
C/EXEC SQL CLOSE cursor1
C/END-EXEC
C SETON LR
C RETURN
****************** End of data ***************************************
动态SQL可以使RPG程序的逻辑更加灵活简单,所以它的编码也要复杂些,而静态SQL可以提供更好的性能。
作者:Li Jing
UID
ibm11145854