8.4　有关游标的案例

本节将完成一个有关游标的案例，加强读者的动手能力，以达到更加熟练地运用游标解决问题的目的。

本案例涉及两张表，这两张表在第4章已经设计完成，分别是PRODUCTINFO（产品信息表）和CATEGROYINFO（产品类型信息表）。下面列出这两张表的数据记录供读者参考。其中，图8.1为PRODUCTINFO表的数据列表，图8.2为表CATEGROYINFO的数据列表。

图　8.1　PRODUCTINFO表数据列表

图　8.2　CATEGROYINFO表数据列表

【示例15】利用游标转换这两张表的数据，要求把商品价格高于1000的产地标为“中国”和“杭州”的家电和电子产品放入到表PRODUCTINFO_TMP（该表字段同PRODUCTINFO一致，字段类型可根据实际需要自行定义）中，并且要求商品类型编号换成商品类型，商品价格高于2000的下调5%。

【案例分析】

❑要建表PRODUCTINFO_TMP。

❑利用SQL语句把符合要求的数据查询出来。

❑把符合要求的数据放进新表。

❑把新表的数据商品类型编号换成商品类型。

❑商品价格下调。

【案例脚本】

按照分析步骤写成脚本如下。

（1）建表PRODUCTINFO_TMP

首先创建表PRODUCTINFO_TMP。由于要求该表和PRODUCTINFO表字段一致，而且经过观察CATEGORY字段的长度可以暂时容纳表CATEGROYINFO中CATEGROYNAME字段内容，这里不对新表CATEGORY字段长度做修改。利用如下语句完成创建：

01 CREATE TABLE productinfo_tmp AS SELECT*FROM productinfo

02 WHERE 1=0

【代码解析】

❑该脚本表示创建结构和表productinfo一样的新表productinfo_tmp。

❑第2行表示复制表的时候不包括数据。如果WHERE后的条件为TRUE，则复制表的时候会把原表的数据一同复制。

（2）创建游标完成案例要求

以下是按照案例要求完成的脚本，脚本比较多，读者可以仔细阅读代码解析部分。

01 DECLARE

02

03 cur_categoryid categroyinfo.categroyid%TYPE;

04 cur_categoryname categroyinfo.categroyname%TYPE;

05 cur_prodrcd productinfo%ROWTYPE;

06 tmpnum number(8,0);

07

08 CURSOR cur_prdt_catg IS

09 SELECT*FROM productinfo WHERE productprice＞1000 AND origin in('中国','杭州')

10 AND category IN

11 (SELECT categroyid

12 FROM categroyinfo

13 WHERE categroyname

14 IN('路由器','电视','洗衣机','MP3')

15 );

16

17 CURSOR cur_catg IS

18 SELECT CATEGROYID,CATEGROYNAME FROM categroyinfo

19 WHERE categroyname

20 IN('路由器','电视','洗衣机','MP3');

21

22 BEGIN

23

24 ——把符合要求数据放进表productinfo_tmp

25 OPEN cur_prdt_catg;

26 LOOP

27 FETCH cur_prdt_catg INTO cur_prodrcd;

28 IF cur_prdt_catg%FOUND THEN

29 INSERT INTO productinfo_tmp

30 (productid,productname,productprice,

31 quantity,category,desperation,origin)

32 VALUES

33 (cur_prodrcd.productid,cur_prodrcd.PRODUCTNAME,

34 cur_prodrcd.PRODUCTPRICE,cur_prodrcd.QUANTITY,

35 cur_prodrcd.CATEGORY,cur_prodrcd.DESPERATION,

36 cur_prodrcd.ORIGIN);

37

38 ELSE

39 DBMS_OUTPUT.PUT_LINE('已取出所有数据！共'||cur_prdt_catg%ROWCOUNT||'条记录');

40 EXIT;

41 END IF;

42 END LOOP;

43 COMMIT;

44

45 ——转换产品类型

46 OPEN cur_catg;

47 tmpnum:=0;

48 LOOP

49 FETCH cur_catg INTO cur_categoryid,cur_categoryname;

50 IF cur_catg%FOUND THEN

51 UPDATE productinfo_tmp SET productinfo_tmp.category=cur_categoryname

52 WHERE category=cur_categoryid;

53 IF SQL%FOUND THEN

54 tmpnum:=tmpnum+SQL%ROWCOUNT;

55 END IF;

56 ELSE

57 DBMS_OUTPUT.PUT_LINE('产品类型转换完毕！共转换'||tmpnum||'条记录');

58 EXIT;

59 END IF;

60 END LOOP;

61

62 ——产品价格下调

63 UPDATE productinfo_tmp

64 SET productinfo_tmp.productprice=productinfo_tmp.productprice*0.95

65 WHERE productinfo_tmp.productprice＞2000;

66 DBMS_OUTPUT.PUT_LINE('价格下调完毕！共下调'||SQL%ROWCOUNT||'条商品');

67 COMMIT;

68 END;

【代码解析】

❑第3～4行表示声明变量，类型同表字段类型一致。

❑第5行声明一个行对象类型的变量。

❑第6行声明一个number类型的变量。

❑第8～15行表示创建游标得到商品符合价格高于1000的产地标为“中国”和“杭州”的家电和电子产品的数据。

❑第11～15行表示查询出categroyname字段符合IN括号里面条件的数据，此查询的结果将作为第9～10行的条件。

❑第17～20行表示创建游标得到表CATEGROYINFO中家电和电子产品的产品编码和产品类型。

❑第25～27行表示打开游标cur_prdt_catg并进入循环流提取行数据。

❑第28行表示如果%FOUND属性为TRUE，则进入存放数据语句。

❑第29～36行表示把符合要求的数据插入新表PRODUCTINFO_TMP中。

❑第38～39行表示当没有符合要求的数据时会进入该流程，它利用游标的%ROWCOUNT属性进行统计共输入新表记录数。EXIT表示退出该游标。

❑第43行表示提交事务。

❑第46～60行表示打开游标cur_catg，完成把PRODUCTINFO_TMP表中的产品类型编码改成产品类型名称功能。

❑第47行为变量tmpnum赋初始值0。

❑第48～50行表示进入循环流取数据，并利用%FOUND属性判断数据是否提取完毕。

❑第51～52行对表PRODUCTINFO_TMP中的数据进行修改，把产品类型编码修改为产品类型名称。

❑第53～55行是比较有趣的地方。这里利用了隐式游标的%FOUND属性判断修改了多少条数据，并利用tmpnum变量和隐式游标的%ROWCOUNT属性最终得到转换数据的数量。

❑第57行表示输出最终转换的数据数目。

❑第63～65行完成产品价格下调功能。

❑第66行表示利用隐式游标的%ROWCOUNT属性得到价格下调成功产品的数量。

❑第67行表示事务提交。

【执行效果】

在PL/SQL Developer的SQL窗口中执行以上代码。执行效果见SQL窗口中的Output标签页面，打印输出结果如下：

已取出所有数据！共4条记录

产品类型转换完毕！共转换4条记录

价格下调完毕！共下调3条商品

最后表PRODUCTINFO_TMP中的内容如图8.3所示，读者可以自行同PRODUCTINFO表做对比。

图　8.3　PRODUCTINFO_TMP表数据列表