融海咨詢informix_4gl教材 第二十九章4GL中的刪除語句

1、. 第二十九章      INFORMIX-4GL中的刪除語句本章介紹了刪除操作所須的步驟。上圖給出了DELETE語句的語法。注意其中WHERE子句是可選項，若DELETE語句不含WHERE子句，則刪除表中的所有數(shù)據(jù)行。這種情況一般是不希望發(fā)生的。用戶選擇Delete選項后，怎么能知道刪除哪行數(shù)據(jù)呢?其實很簡單，是刪當前行。那怎么知道當前行是哪行呢？為了回答這個問題，你應(yīng)該查看用戶是怎樣刪除這行的。大多數(shù)情況，都是用戶先進行查詢，查詢選項完成以下功能：l l 讓用戶輸入查詢條件l l 聲明一個SCROLL游標來檢索l l 把

2、滿足條件的第一行數(shù)據(jù)取到程序變量中每次用戶向上、向下移動光標，都把新行的值送到程序變量中。 程序變量中包含表的主鍵(上例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游標指定的行。因為這行已經(jīng)放在了內(nèi)存，所以WHERE CURRENT OF節(jié)省了DELETE語句的數(shù)據(jù)庫I/O。 注意WHERE CURRENT OF只能和FOR UPDATE游標一起使用，最好用FOR UPDATE游標來修改或刪除行，特別是在OLTP環(huán)境下。上圖中是刪除一個客戶的函數(shù)例子，其中WHENEVE

4、R語句用于錯誤檢測，并假設(shè)lockcust游標已在初始化函數(shù)中聲明了。從數(shù)據(jù)庫表刪任何數(shù)據(jù)都要好好考慮，看是否會對數(shù)據(jù)庫中其它表有影響。若從Customer表刪除一行是否會影響其它表？往orders表插入時，要檢測order表的客戶(外鍵)在customer表中是否存在，當從customer表刪除客戶時，也要檢測客戶號(主鍵)在orders表中是否用到。為什么要檢測呢？這是因為若刪除了有訂單的客戶，在打印訂單信息時就無法找到該客戶的信息了。最簡單的檢測方法是計算將要刪除的客戶號在orders表中出現(xiàn)次數(shù)，若次數(shù)大于0，則不允許對該客戶記錄進行刪除。上圖中是檢測刪除行的主鍵在另一個表中是否以外鍵

5、存在的例子，注意，一個表的主鍵可能是多個表的外鍵，這時，其它表也應(yīng)進行檢測。例子中為什么要用聚合函數(shù)呢？用Count(*)聚合函數(shù)是為了避免聲明游標。聚合函數(shù)通常只返回一個信息(一行)，數(shù)據(jù)庫引掣一次處理一行，因此不需要游標。在上圖例子中，全局變量gr_customer.customer_num用于同orders.customer_num字段的值進行比較，若找到了行，即count(*)0，則這個customer不能刪除。最好給用戶一個最后結(jié)果。上圖例子中，在檢測主鍵后，MENU語句用于確認用戶是否確實要刪除這一行。若用戶選擇NO，則DELETE被放棄；只有選擇YES才執(zhí)行DELETE。在YES

6、和NO選項中加上EXIT MENU非常有用。若不加上，MENU將成為死循環(huán)。程序?qū)嵗?#160;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游標打開前執(zhí)行， open lockcust否則會發(fā)生運行錯誤。  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