融海咨询informix_4gl教材 第二十九章4GL中的删除语句

1、. 第二十九章      INFORMIX-4GL中的删除语句本章介绍了删除操作所须的步骤。上图给出了DELETE语句的语法。注意其中WHERE子句是可选项，若DELETE语句不含WHERE子句，则删除表中的所有数据行。这种情况一般是不希望发生的。用户选择Delete选项后，怎么能知道删除哪行数据呢?其实很简单，是删当前行。那怎么知道当前行是哪行呢？为了回答这个问题，你应该查看用户是怎样删除这行的。大多数情况，都是用户先进行查询，查询选项完成以下功能：l l 让用户输入查询条件l l 声明一个SCROLL游标来检索l l 把

2、满足条件的第一行数据取到程序变量中每次用户向上、向下移动光标，都把新行的值送到程序变量中。 程序变量中包含表的主键(上例Customer表的主键是Customer.customer_num)。若用户从屏幕上选中一行后，选择了删除选项，那么只是删除了Customer表中Customer.customer_num=gr_customer.customer_num的行。因为这是主键，只会有一行满足条件，所以只删除一行(或因为此客户早已被删除而一行也没被删除)。用FOR UPDATE游标时，在DELETE语句中用WHERE CURRENT OF游标名是删除当前行的另一种方法。它比用上表的主键

3、值等于程序变量值的WHERE子句更有效。在上图例子中，用WHERE CURRENT OF lock_cust替了WHERE customer_num = gr_customer.customer_num。WHERE CURRENT OF子句指示DELTET语句检索lockcust游标指定的行。因为这行已经放在了内存，所以WHERE CURRENT OF节省了DELETE语句的数据库I/O。 注意WHERE CURRENT OF只能和FOR UPDATE游标一起使用，最好用FOR UPDATE游标来修改或删除行，特别是在OLTP环境下。上图中是删除一个客户的函数例子，其中WHENEVE

4、R语句用于错误检测，并假设lockcust游标已在初始化函数中声明了。从数据库表删任何数据都要好好考虑，看是否会对数据库中其它表有影响。若从Customer表删除一行是否会影响其它表？往orders表插入时，要检测order表的客户(外键)在customer表中是否存在，当从customer表删除客户时，也要检测客户号(主键)在orders表中是否用到。为什么要检测呢？这是因为若删除了有订单的客户，在打印订单信息时就无法找到该客户的信息了。最简单的检测方法是计算将要删除的客户号在orders表中出现次数，若次数大于0，则不允许对该客户记录进行删除。上图中是检测删除行的主键在另一个表中是否以外键

5、存在的例子，注意，一个表的主键可能是多个表的外键，这时，其它表也应进行检测。例子中为什么要用聚合函数呢？用Count(*)聚合函数是为了避免声明游标。聚合函数通常只返回一个信息(一行)，数据库引掣一次处理一行，因此不需要游标。在上图例子中，全局变量gr_customer.customer_num用于同orders.customer_num字段的值进行比较，若找到了行，即count(*)0，则这个customer不能删除。最好给用户一个最后结果。上图例子中，在检测主键后，MENU语句用于确认用户是否确实要删除这一行。若用户选择NO，则DELETE被放弃；只有选择YES才执行DELETE。在YES

6、和NO选项中加上EXIT MENU非常有用。若不加上，MENU将成为死循环。程序实例： Module 1 cust_globs.4gl database stores用了LIKE保留字要有此句。  globalsdefine gr_customer record like customer.*define nr_customer record like customer.*define fnext, fprior smallintend globals Module 2 cust_main.4gl globals "cust

7、_globs.4gl" main defer interrupt此句程序只能用一次。用于初始化 call prog_init()int_flag为FALSE。 call updel_init() call main_menu()end main function prog_init() open form custform from "custform" initialize nr_customer.* to null let fnext = 1 let fprior = -1end function function main_me

8、nu() menu "MAIN MENU" command "Customer" "Go to the CUSTOMER menu." call cust_menu() next option "Orders" command "Orders" "Add a new order." call dummy() command "Exit" "Exit to O.S." exit menu end menuend functionfunct

9、ion 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_customer.* = nr_customer.* menu "CUSTOMERS" before menu let qry_flag = false hide option "

10、;Next","Previous","Delete" command "Query" "Search for a customer." let qry_flag = query_cust() if qry_flag then show option "Next","Previous","Delete" else hide option "Next","Previous","Delete"

11、 end if command "Next" "Go to next customer." call fetch_cust(fnext) command "Previous" "Go to previous customer." call fetch_cust(fprior) command "Add" "Add a new customer." call input_cust() command "Delete" "Delete the cur

12、rent customer." call delete_cust() command "Exit" "Exit to Main Menu." exit menu end menu close window w_cust end function  function dummy() error "Function not yet implemented." end function Module 5 cust_updel.4gl globals "cust_globs.4gl"

13、 function updel_init() declare lockcust cursor for select * from customer where customer.customer_num = gr_customer.customer_num for updateend function function delete_cust() define ord_count integer  select count(*) into ord_count from orders where orders.customer_num = gr_customer.c

14、ustomer_num if ord_count > 0 then error "This customer has placed ",ord_count using "<<<<", " order(s) and cannot be deleted." else menu "ARE YOU SURE?" command "No" "Do not delete this customer." error "Delete aborted.&

15、quot; exit menu command "Yes" "Delete this customer." begin work此句必须在FOR UPDATE游标打开前执行， open lockcust否则会发生运行错误。  whenever error continue  fetch lockcust into gr_customer.*  whenever error stop  if sqlca.sqlcode < 0 then error "Could not lock row."

16、 rollback workelse  whenever error continue  delete from customer where current of lockcust  whenever error stop if sqlca.sqlcode < 0 then error "Error number ",sqlca.sqlcode using "-<<<<"," has occurred." rollback work else message "Cust