10.2.5　存储过程中使用游标

存储过程可以灵活地处理各种情况下的数据，它不仅可以用普通的方式处理数据（例如示例4），也可以使用游标来辅助处理更复杂的业务逻辑。下面就创建一个稍微复杂的存储过程，该过程处理数据需要使用游标，具体的业务要求见示例5。

【示例5】使用游标处理数据

要求把PRODUCTINFO表中数据根据不同的产品类型分类把数据输出到屏幕。具体脚本如下：

01 CREATE PROCEDURE PRODUCT_CUR_PRC

02 AS

03 cur_ctgy productinfo.category%TYPE;—存放产品类型编码

04 cur_ctgyname categroyinfo.categroyname%TYPE;—存放产品类型名称

05 cur_prtifo productinfo%ROWTYPE;—存放表productinfo的行记录

06

07 CURSOR cur_category

08 IS

09 SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;

10

11 BEGIN

12 OPEN cur_category;

13 LOOP

14 FETCH cur_category INTO cur_ctgy;

15 EXIT WHEN cur_category%NOTFOUND;

16 SELECT CATEGROYINFO.CATEGROYNAME INTO cur_ctgyname

17 FROM CATEGROYINFO

18 WHERE CATEGROYID=cur_ctgy;—根据类型编码得到产品类型名称

19

20 IF SQL%FOUND THEN

21 DBMS_OUTPUT.PUT_LINE('————————————————————-');

22 DBMS_OUTPUT.PUT_LINE(cur_ctgyname||':');

23 END IF;

24

25 FOR my_prdinfo_rec IN

26 (

27 SELECT*FROM PRODUCTINFO WHERE CATEGORY=cur_ctgy

28 )

29 LOOP

30 DBMS_OUTPUT.PUT_LINE(

31 产品名称：'||my_prdinfo_rec.PRODUCTNAME

32 ||'产品价格：'||my_prdinfo_rec.PRODUCTPRICE

33 ||'产品数量：'||my_prdinfo_rec.QUANTITY

34 );

35 END LOOP;

36 END LOOP;

37 CLOSE cur_category;

38 END;

39 /

【代码解析】

❑第1行表示创建存储过程，名称是PRODUCT_CUR_PRC。

❑第3～5行表示变量名称以及变量类型，脚本具体含义可参考游标部分的讲解。其中cur_ctgy存放产品类型编码，cur_ctgyname存放产品类型名称，cur_prtifo存放表productinfo的行记录。

❑第7～9行表示创建一个游标，表示从PRODUCTINFO表中查询已有的产品类型。

❑第12～15行表示打开游标cur_category并进入循环流提取数据，当数据提取完毕后退出。

❑第16～18行表示在CATEGROYINFO表中，根据产品类型编码得到产品类型名称，并把得到的结果赋值给变量cur_ctgyname。

❑第20～23行利用隐式游标%FOUND属性判断第16～18行的查询是否有结果，如果有则把产品类型名称输出到屏幕。

❑第25～35行表示利用隐式游标获取某类型的所有产品，并把产品信息输出到屏幕。

【执行效果】

以上脚本编译成功后利用如下脚本执行：

EXEC PRODUCT_CUR_PRC;

执行效果如图10.4所示。

图　10.4　示例5执行效果

从执行效果可以看出，此存储过程已经满足了示例的要求，把商品信息按商品类型分类输出到屏幕。事实上，在过程当中使用游标的情况很普遍，该示例中有三处用到了游标的知识（两处利用隐式游标），方便地解决了相关问题。读者应当熟练地掌握这种方式。