oracle sqr之多個procedure過程變數傳遞及同名變數定義

wisdomone1發表於2011-12-20
Oracle變數
昨日測試了根據不同判斷條件寫入多個lis檔案的sqr程式碼
http://space.itpub.net/9240380/viewspace-713575

 今天在此基礎上，測試瞭如下幾個功能點：
 1,過程之間（指：begin-procedure)傳遞引數及變數；
 2,多個過程變數是否可以命名相同的變數
 3,begin-procedure節alter-report指定特有的begin-heading

上述程式碼如下：
#define addtlsetup
#include 'Rptstub.lib'
#include '{libpath}tkit_batchstartup.inc'
#include '{libpath}tkit_rptheader.inc'
#include '{libpath}tkit_queappl.inc'
#include '{libpath}tkit_vars_opts.inc'
#include '{libpath}tkit_Sqrfunc.inc'

begin-setup
 Declare-layout batch-land
   rientation=landscape
   left-margin=0
   top-margin=0
   max-lines=60
   max-columns=180
 end-declare
end-setup

!20111220 newly added 無資料使用報表頭
begin-heading 5 name=nodataheading
 print 'a列' (1,11)
 print 'b列' (1,20)
 print '─' (+1,2,84) fill
end-heading

!主過程呼叫其它子過程
begin-procedure osi-main
 begin-select
count(*) &a_1_rowcount
 from osibank.zxytestsqr z where z.a=1
 end-select
 
 begin-select
count(*) &a_2_rowcount
 from osibank.zxytestsqr z where z.a=2
 end-select
   
 if &a_1_rowcount>0
   do  query_a_1
 else
      !20111220 newly added 無資料啟用指定報表頭,且使用另外的lis檔案
      alter-report  heading=nodataheading
      let $out='無資料表頭'||'.LIS'
      new-report $out
      print '查詢不到資料' (+1,0) center
      !print #b2  (+1,0) center !在主過程呼叫子過程產生的變數，用於測試,如寫為#b2，結果為0.0,試下#_b2如何
     
 end-if
 
 if &a_2_rowcount>0
   do  query_a_2(#b2)
   print #b2 (+1,20) center
 else
      !20111220 newly added  
 end-if
 
end-procedure

!查詢a=1
begin-procedure query_a_1
 let $count='變數1' !　此過程的變數$count
 let $out1='查詢1'||$count||'.LIS'
 new-report $out1
 begin-select
  position (+1)
a &a1
 move &a1 to #a1
 print #a1 (,20,10) edit 9
b &b1
 move &b1 to #b1
 print #b1 (,40,10) edit 9
 from osibank.zxytestsqr z where z.a=1
 end-select
end-procedure


!查詢a=2
begin-procedure query_a_2(:#b2)
 let $count='變數2' !此過程的變數$count
 !#debug show $count
 let $out2='查詢2'||$count||'.LIS'
 new-report $out2
 begin-select
  position (+1)
a &a2
 move &a2 to #a2
 print #a2 (,60,10) edit 9
b &b2
 move &b2 to #b2
 print #b2 (,80,10) edit 9
 from osibank.zxytestsqr z where z.a=2
 end-select
end-procedure

begin-procedure OSI-Startup   
End-procedure

上述示例程式碼產生lis檔案如下：

查詢2變數2.lis

        a列      b列
 ────────────────────────────────────────────────────────────────────────────────────
                                                           2                   2
                                                                                       2.00


無資料表頭.lis
         a列      b列
 ────────────────────────────────────────────────────────────────────────────────────
                                                                                   查詢不到資料





繼續測試，sqr呼叫oracle儲存過程2分支正常錯誤分別處理及錯誤報表lis之sqr程式碼
#define addtlsetup
#include 'Rptstub.lib'
#include '{libpath}tkit_batchstartup.inc'
#include '{libpath}tkit_rptheader.inc'
#include '{libpath}tkit_queappl.inc'
#include '{libpath}tkit_vars_opts.inc'
#include '{libpath}tkit_Sqrfunc.inc'

begin-setup
 Declare-layout batch-land
   rientation=landscape
   left-margin=0
   top-margin=0
   max-lines=60
   max-columns=180
 end-declare
end-setup

!20111220 newly added 無資料使用報表頭
begin-heading 5 name=nodataheading
 print 'a列' (1,11)
 print 'b列' (1,20)
 print '─' (+1,2,84) fill
end-heading

!儲存過程執行出錯報表頭
begin-heading 5 name=procerrheading
 print '儲存過程錯誤頭' (1,11)
 print '─' (+1,2,84) fill
end-heading

begin-procedure osi-main
 begin-sql
   begin
     osibank.proc_zxytestsqr(#errnbr);;
   end;;
 end-sql
 
 if #errnbr=0
   !儲存過程執行出錯，不再繼續執行下述子過程，產生錯誤檔案
   alter-report heading=procerrheading
   let $out='儲存過程執行出錯'||'.LIS'
   new-report $out
   print '錯誤沒產生資料' (+1,0) center
 else
      !儲存過程執行正常，執行下述子過程
      do submain
 end-if
end-procedure

!主過程呼叫其它子過程
begin-procedure submain
 begin-select
count(*) &a_1_rowcount
 from osibank.zxytestsqr z where z.a=1
 end-select
 
 begin-select
count(*) &a_2_rowcount
 from osibank.zxytestsqr z where z.a=2
 end-select
   
 if &a_1_rowcount>0
   do  query_a_1
 else
      !20111220 newly added 無資料啟用指定報表頭,且使用另外的lis檔案
      alter-report  heading=nodataheading
      let $out='無資料表頭'||'.LIS'
      new-report $out
      print '查詢不到資料' (+1,0) center
      !print #b2  (+1,0) center !在主過程呼叫子過程產生的變數，用於測試,如寫為#b2，結果為0.0,試下#_b2如何
 end-if
 
 if &a_2_rowcount>0
   do  query_a_2(#b2)
   print #b2 (+1,20) center
 else
      !20111220 newly added  
 end-if
end-procedure

!查詢a=1
begin-procedure query_a_1
 let $count='變數1'
 let $out1='查詢1'||$count||'.LIS'
 new-report $out1
 begin-select
  position (+1)
a &a1
 move &a1 to #a1
 print #a1 (,20,10) edit 9
b &b1
 move &b1 to #b1
 print #b1 (,40,10) edit 9
 from osibank.zxytestsqr z where z.a=1
 end-select
end-procedure


!查詢a=2
begin-procedure query_a_2(:#b2)
 let $count='變數2'
 !#debug show $count
 let $out2='查詢2'||$count||'.LIS'
 new-report $out2
 begin-select
  position (+1)
a &a2
 move &a2 to #a2
 print #a2 (,60,10) edit 9
b &b2
 move &b2 to #b2
 print #b2 (,80,10) edit 9
 from osibank.zxytestsqr z where z.a=2
 end-select
end-procedure

begin-procedure OSI-Startup   
End-procedure



上述sqr呼叫儲存過程內容如下：
 create or replace procedure proc_zxytestsqr(errnbr out number)
 as
 lvncount integer;
 begin
 select count(a) into lvncount from osibank.zxytestsqr;
 if lvncount=0 then
  errnbr:=-1;
 else
  errnbr:=0;
 end if;
 end;


繼續改革上述程式碼，新增begin-footing，輸出報表尾及begin-setup的引數rows,
程式碼如下：
#define addtlsetup
#include 'Rptstub.lib'
#include '{libpath}tkit_batchstartup.inc'
#include '{libpath}tkit_rptheader.inc'
#include '{libpath}tkit_queappl.inc'
#include '{libpath}tkit_vars_opts.inc'
#include '{libpath}tkit_Sqrfunc.inc'

begin-setup
 Declare-layout batch-land
   rientation=landscape
   left-margin=0
   top-margin=0
   max-lines=60
   max-columns=180
 end-declare
end-setup

!20111220 newly added 無資料使用報表頭
begin-heading 5 name=nodataheading
 print 'a列' (1,11)
 print 'b列' (1,20)
 print '─' (+1,2,84) fill
end-heading

!儲存過程執行出錯報表頭
begin-heading 5 name=procerrheading
 print '儲存過程錯誤頭' (1,11)
 print '─' (+1,2,84) fill
end-heading

begin-procedure osi-main
 begin-sql
   begin
     osibank.proc_zxytestsqr(#errnbr);;
   end;;
 end-sql
 
 if #errnbr!=0
   !儲存過程執行出錯，不再繼續執行下述子過程，產生錯誤檔案
   alter-report heading=procerrheading
   let $out='儲存過程執行出錯'||'.LIS'
   new-report $out
   print '錯誤沒產生資料' (+1,0) center
 else
      !儲存過程執行正常，執行下述子過程
      do submain
 end-if
end-procedure

!主過程呼叫其它子過程
begin-procedure submain
 begin-select
count(*) &a_1_rowcount
 from osibank.zxytestsqr z where z.a=1
 end-select
 
 begin-select
count(*) &a_2_rowcount
 from osibank.zxytestsqr z where z.a=2
 end-select
   
 if &a_1_rowcount>0
   do  query_a_1
 else
      !20111220 newly added 無資料啟用指定報表頭,且使用另外的lis檔案
      alter-report  heading=nodataheading
      let $out='無資料表頭'||'.LIS'
      new-report $out
      print '查詢不到資料' (+1,0) center
      !print #b2  (+1,0) center !在主過程呼叫子過程產生的變數，用於測試,如寫為#b2，結果為0.0,試下#_b2如何
 end-if
 
 if &a_2_rowcount>0
   do  query_a_2(#b2)
   print #b2 (+1,20) center
 else
      !20111220 newly added  
 end-if
end-procedure

!查詢a=1
begin-procedure query_a_1
 let $count='變數1'
 let $out1='查詢1'||$count||'.LIS'
 new-report $out1
 begin-select
  position (+1)
a &a1
 move &a1 to #a1
 print #a1 (,20,10) edit 9
b &b1
 move &b1 to #b1
 print #b1 (,40,10) edit 9
 from osibank.zxytestsqr z where z.a=1
 end-select
end-procedure


!查詢a=2
begin-procedure query_a_2(:#b2)
 let $count='變數2'
 !#debug show $count
 let $out2='查詢2'||$count||'.LIS'
 new-report $out2
 begin-select
  position (+1)
a &a2
 move &a2 to #a2
 print #a2 (,60,10) edit 9
b &b2
 move &b2 to #b2
 print #b2 (,80,10) edit 99
 
 !測試新增總記錄數及累計a列值
 let #count=#count+1 !輸出每頁記錄總數
 
 let #sum_a=#sum_a+&a2 !輸出每頁a列累計數
 from osibank.zxytestsqr z where z.a=2
 end-select
 
 move #count to $count 999,999,999
 let $co='總記錄數：'||$count
 print $co (+1,2)
 
 move #sum_a to $sum_a 999,999,999
 let $out_a='a列累計值為：'||$sum_a
 print $out_a (,100)
 
 
end-procedure

begin-procedure OSI-Startup   
End-procedure


begin-footing 5 !name=endfooting
 
  !報表尾列印位置要注意，不然可能會出現當前頁沒列印輸出完，就會列印第二頁報錯，調整position即可
  page-number (+1,20)  '當前頁為第' '頁，'
  last-page () '共' '頁。'
end-footing

上述輸出．LIS報表檔案如下：
變數2查詢2.lis

         a列      b列
 ────────────────────────────────────────────────────────────────────────────────────
                                                           2                   2
                                                           2                   10
                                                           2                   2
 總記錄數：          3                                                                             a列累計值為：          6
                                                                                       2.00
                   當前頁為第1頁，共1頁




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-713603/，如需轉載，請註明出處，否則將追究法律責任。

相關文章