Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1083139
  • 博文数量: 106
  • 博客积分: 9093
  • 博客等级: 中将
  • 技术积分: 2770
  • 用 户 组: 普通用户
  • 注册时间: 2006-06-01 17:22
文章分类

全部博文(106)

文章存档

2014年(1)

2012年(29)

2011年(32)

2010年(20)

2008年(24)

分类: 系统运维

2011-04-06 17:23:13

动态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

阅读(3232) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~