融海咨询informix_4gl教材 第三十三章SCROLL游标和过期数据：一个解决方案

1、.  第三十三章      SCROLL游标和过期数据：一个解决方案本章讨论了一个关于SCROLL游标的问题，讲解了当临时表空间有限时怎样使用SCROLL游标，以及怎样解决过期数据的问题。为了在行集中滚动，SCROLL游标创建了临时表，临时表包含了满足与SCROLL游标相关联的SELECT语句的所有数据行。l l DECLARE语句：DECLARE语句为SELECT语句分配一个名字及所需的最小内存。l l OPENU语句：OPEN语句申请内存缓冲区，以便把数据行从数据库引擎传到应用程序，缓冲区大小与机器有关。l l

2、 FETCH语句：当用SCROLL游标时，FETCH语句把所有满足SELECT的行都检索出来。在上图例子中，即取出Customer表中的所有行，这些数据都存在磁盘的临时表上供FETCH使用。l l CLOSE语句：CLOSE语句释放缓冲区并删除SCROLL游标所用的临时表。SCROLL游标有两个潜在的问题：l l 临是表可能很大l l 数据可能过期满足SECECT的所有行都放在了临时表，这使得临时表会变得很大，在临时表创建时会导致内存问题。例如：customer表每行有134字节，若customer表有20000行，且SELECT语句检索所有行，临时表就要2M以上才能放下检索的行。SCROLL

3、游标还有另一个问题，一但把数据读到临时表，所有数据都将从临时表取，这意味着若有其它用户对Customer表做了修改或删除，都不会反映到临时表，只有重新打开SCROLL游标才起作用。只要明白游标操作，问题解决起来就变得简单了。对于大临时表问题：要减小创建的临时表，可以少检索些行或字段，SCROLL游标一般和CONSTRUCT语句相关联，一但执行了带CONSTRUCT的WHERE子句，你就无法控制检索出来多少行。另外还可以少选些字段。对于过期数据问题：只有一个方法来解决过期数据问题，每次作FETCH语句时，都要从实际表而不要从临时表中取数。解决这个问题要用到两个游标，第一个游标是SCROLL 游标

4、，但只从customer表取出主键字段，而不取表中所有字段。这个游标中的FETCH语句每次执行时只把主键值装到程序变量中，这个值在声明的第二个游标中用到，用于从数据库检索满足第一个游标提供的主键的整行数据。第二个游标用于检索整行数据，针对第一个游标中的每个FETCH语句，打开第二个游标，并执行相应的FETCH语句。对第二个游标语句预处理完，用问号代替实际的客户号。当SELECT语句作了准备，准备语句中用到的问号的值要在OPEN语句时提供。如上图例子中，是在OPEN语句中用USING子句实现的。OPEN/FETCH语句使第二个游标每次从表中检索一行数据。第二个游标取出的是与第一个游标提供的值相应

5、的那行。第一个游标是在主键字段上前后移动的SCROLL 游标。第二个游标不是SCROLL游标。它只是用第一个游标提供的值从磁盘检索信息。这样就解决了数据过期的问题。上图例子中说明了使用两个游标的解决方案，第一个游标即cust_ptr，取出满足用户输入条件的行的主键值。声明的cust_all游标用于从customor表取出满足gr_customer.customer_num值的行的所有字段。gr_customer.customor_num变量的值从cust_ptr游标得到。cust_all游标在它打开之前的任意地方声明即可。两个游标声明之后，打开了cust_ptr游标，并执行FETCH FIRS

6、T，若FETCH FIRST执行失败，也就不必使用cust_all游标了。若FETCH FIRST执行成功，则打开cust_all游标，并执行FETCH。只要执行了OPEN和FETCH语句，cust_all游标就从customer表把这行的其余部分检索出来了。后面数据的读取也要用到两个游标，若FETCH语句执行时没找到行，说明两个游标有出入。cust_ptr游标执行后若SQLCA.SQLCODE=100，意味着到了临时表的顶部或底部，若找到了一行，新值就装入gr_customer.cuotorner_num变量，并提供给cust_all游标。cust_all游标执行后若SQLCA.SQLCOD

7、E=100，意味着cust_ptr游标提供的主键值在查询执行过程中，从customer表中删除了。注意：在上图代码中，OPEN和FETCH语句都是针对cust_all游标的，是从磁盘检索数据。程序实例：  Module 1 cust_globs.4gl  database stores globals  define gr_customer record like customer.* define nr_customer record like customer.* define ur_customer record like cu

8、stomer.* define fnext, fprior smallintend globalsModule 2 cust_main.4gl globals "cust_globs.4gl" main defer interrupt call cust_inp_init() call cust_updel_init() call prog_init () call cust_gry_init() call main_menu()end main function prog_init() open form custform from &quo

9、t;custform" initialize nr_customer.* to null let fnext = 1 let fprior = -1end function function main_menu()menu "MAIN MENU"command "Customer" "Go to the CUSTOMER menu."call cust_menu()next option "Orders"command "Orders" "Add a new ord

10、er."call dummy()command "Exit" "Exit to O.S."exit menuend menuend functionfunction cust_menu() define qry_flag smallint display "" at 1,1 display "" at 2,2 open window w_cust at 3,3 with 15 rows, 65 columns attribute (border) display form custform let gr_

11、customer.* = nr_customer.* menu "CUSTOMERS" before menu hide option "Next","Previous","Delete" command "Query" "Search for a customer." let qry_flag = false let qry_flag = query_cust() if qry_flag then show option "Next","Pre

12、vious","Delete","Update" else hide option "Next","Previous","Delete","Update end if command "Next" "Go to next customer." call fetch_cust(fnext) command "Previous" "Go to previous customer." call fetc

13、h_cust(fprior) command "Add" "Add a new customer." call input_cust() command "Delete" "Delete the current customer." call delete_cust() command "Exit" "Exit to Main Menu." exit menu end menu close window w_cust end function  function d

14、ummy() error "Function not yet implemented." end functionModule 3 cust_inp.4gl globals "cust_globs.4gl" function cust_inp_init() define prepvar char(200) let prepvar = "insert into customer ", "values (?,?,?,?,?,?,?,?,?,?)" prepare ins_cust from prep

15、varend function function input_cust() options message line last let int_flag = false input by name gr_customer.* on key (control-e) clear form let gr_customer.* = nr_customer.* next field fname end input if int_flag then let int_flag = false clear form error "Data entry aborted." retu

16、rn end if let gr_customer.customer_num = 0 whenever error continue execute ins_cust using gr_customer.*全局变量gr_customer.*和PREPARE中的问号个数和顺序都相同。 whenever error stop if sqlca.sqlcode < 0 then error "Error number ", sqlca.sqlcode using "-<<<<"," has occurred."

17、 else let gr_customer.customer_num = sqlca.sqlerrd2 display by name gr_customer.customer_num message "This row has been added." end if end function Module 4 cust_browse.4gl globals "cust_globs.4gl"  这个初始化函数按取主键的SCROLL游标返回的值检索该主键所在行的所有字段。function cust_qry_init() def

18、ine select_stmt2 char(350) let select_stmt2 = "select * from customer where ", "customer_num = ?" prepare ex_stmt2 from select_stmt2 end function function query_cust() define where_clause char(250) define select_stmt char(350) display "Enter search criteria, and pr

19、ess ESC.","" at 1,1 display "" at 2,1 let int_flag = false construct by name where_clause on customer.* if int_flag then clear form error "Query aborted." return false end if let select_stmt = "select customer_num from customer ", "where ", wher

20、e_clause clipped prepare ex_stmt1 from select_stmtcust_ptr是只检索主键字段的SCROLL游标，cust_all游标是在初始化函数中准备的 declare cust_ptr scroll cursor with hold for ex_stmt1 open cust_ptr declare cust_all cursor for ex_stmt2 fetch first cust_ptr into gr_customer.customer_num* if sqlca.sqlcode = 100 then close cust_ptr er

21、ror "No rows found." return false end if open cust_all using gr_customer.customer_num fetch cust_all into gr_customer.* call display_cust() close cust_all return trueend function无论是选择Next还是Previous，都是检索主键值。function fetch_cust(fetch_flag) define fetch_flag smallint  fetch relative fetc

22、h_flag cust_ptr into gr_customer.customer_num* if sqlca.sqlcode = not found then error "No more rows in that direction." else open cust_all using gr_customer.customer_num fetch cust_all into gr_customer.customer_num.* if sqlca.sqlcode = 100 then clear form display by name gr_customer.custo

23、mer_num error "Row has been deleted since query." return else call display_cust() end if  close cust_all end if end function function display_cust() display by name gr_customer.*end function  Module 5 cust_updel.4glglobals "cust_globs.4gl" 初始化DELETE和UPDAT

24、E语句，并进行准备。删除和修改要用FOR UPDATE游标加锁。function updel_init() define prepvar char(250)  let prepvar = "delete from customer " , "where current of lockcust"  prepare del_cust from prepvar  let prepvar = "update customer ", "set (fname, lname, company, address

25、1, address2, ", "city, state, zipcode, phone) = ", "(?,?,?,?,?,?,?,?,?) ", "where current of lockcust"  prepare upd_cust from prepvar   declare lockcust cursor for select * from customer where customer.customer_num = gr_customer.customer_num for upda

26、te end function function delete_cust() define ord_count integer  select count(*) into ord_count from orders where orders.customer_num = gr_customer.customer_num  if ord_count then error "This customer has placed ", ord_count using "<<<<", " order(s

27、) and cannot be deleted." else menu "ARE YOU SURE?" command "No" "Do not delete this customer." error "Delete aborted." exit menu command "Yes" "Delete this customer." begin work open lockcust whenever error continue fetch lockcust int

28、o gr_customer.* whenever error stop if sqlca.sqlcode < 0 then error "Could not lock row for delete." rollback work else whenever error continue execute del_cust whenever error stop if sqlca.sqlcode < 0 then error "Error number ", sqlca.sqlcode using "-<<<<&

29、quot;, " has occurred." rollback work else message "Customer number ", gr_customer.customer_num using "<<<<<", " has been deleted." commit work end if end if exit menu end menuend ifend functionfunction update_cust() display "Press ESC after you