Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3665
  • 博文数量: 1
  • 博客积分: 69
  • 博客等级: 民兵
  • 技术积分: 15
  • 用 户 组: 普通用户
  • 注册时间: 2010-03-09 18:30
文章分类
文章存档

2012年(1)

最近访客

分类:

2012-11-30 13:29:22

动态sql分non-select和select两种。non-select包括insert、update和delete。

那如何在sqlrpgle中执行动态sql那。动态sql最重要的是动态sql的合成。合成动态sql有两种方法。
第一种是直接用字符串合成,例子如下:
sqlstm = 'UPDATE PTRLIB/CUSTOM ' +
         'SET NAME = '''+%trim(namec)+''''+ 
         ' WHERE CUST = '''+%trim(custc)+''''
这里最重要的是红色部分。一定要注意单引号的数量。

第二种是在sql中预留要填充的位置,例子如下:
sqlstm = 'update ptrlib/custom ' + 
         'SET NAME = ? WHERE ' +   
         'CUST = ?'                
这里与上面不同的是具体的值是用?来代替的。

下面是两种方法的具体实现:
第一种
d sqlstm          s           1024a   inz(*blanks) VARYING                      
d custc           s              5a   inz('A0001')                              
d namec           s             20a   inz('Lucy and Lily')                      
c                   eval      sqlstm = 'UPDATE PTRLIB/CUSTOM ' +                
c                                      'SET NAME = '''+%trim(namec)+''''+       
c                                      ' WHERE CUST = '''+%trim(custc)+''''     
c****************************************************                           
c* run this sql once,you can skip prepare           *                           
c*/exec sql                                         *                           
c*+ PREPARE S1 FROM :sqlstm                         *                           
c*/end-exec                                         *                           
c****************************************************                           
c/exec sql                                                                      
c+ EXECUTE IMMEDIATE :sqlstm                                                    
c/end-exec                                                                      
c*                                                                              
c                   seton                                        lr             
c                   return                                                      

第二种
d sqlstm          s           1024a   inz(*blanks) VARYING          
d custc           s              5a   inz('A0001')                  
d namec           s             20a   inz(*blanks)                  
c                   eval      sqlstm = 'update ptrlib/custom ' +    
c                                      'SET NAME = ? WHERE ' +      
c                                      'CUST = ?'                   
c/exec sql                                                          
c+ PREPARE s1 FROM :sqlstm                                          
c/end-exec                                                          
c*                                                                  
c                   eval      namec = 'lily'                        
c*                                                                  
c/exec sql                                                          
c+ EXECUTE s1 USING :namec,:custc                                   
c/end-exec                                                          
c*                                                                  
c                   seton                                        lr 
c                   return                                          

要是执行select的话需要使用cursor。例子如下
     D*
     D sqlstm          S           1024A   VARYING
     D file            S             21A   inz('ptrlib/custom')
     D count           S             10p 0 inz(*zero)
     C*
     C                   eval      sqlstm = 'select count(*) from ' +
     C                             %trim(file)
     C*
     C/EXEC SQL
     C+  prepare s1
     C+  from :sqlstm
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  declare c1
     C+  cursor for s1
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  open c1
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  FETCH c1 INTO :count
     C/END-EXEC
     c                   if          SQLCOD = *ZERO
     c     count         dsply
     c                   endif
     C/EXEC SQL
     C+  CLOSE c1
     C/END-EXEC
     C*
     C                   SETON                                        lr
     C                   RETURN

阅读(759) | 评论(0) | 转发(0) |
0

上一篇:没有了

下一篇:没有了

给主人留下些什么吧!~~