《数据库系统原理》实验报告(14)

② 编译自定义函数CalTotalPrice4Order

ALTER FUNCTIONFUN_CalTotalPrice4Order（okey INTEGER) COMPILE;

（6）删除自定义函数

删除自定义函数CalTotalPrice4Order。 DROP FUNCTION CalTotalPrice4Order ; 6.3游标实验 （1）普通游标

① 定义一个存储过程，用游标实现计算所有订单的总价。

CREATE OR REPLACE PROCEDURE ProcCursor_CalTotalPrice() AS

L_orderkey INTEGER; L_totalprice REAL;

CURSOR mycursor FOR

SELECT orderkey,totalprice FROM Orders; BEGIN

OPEN mycursor; LOOP

FETCH mycursor INTO L_orderkey,L_totalprice; IF mycursor%NOTFOUND THEN

EXIT; END IF;

SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO

L_totalprice

FROM Lineitem

WHERE orderkey=L_orderkey ;

UPDATE Orders SET totalprice=L_totalprice WHERE orderkey=L_orderkey; END LOOP;

CLOSE mycuesor; END;

② 执行存储过程ProcCursor_CalTotalPrice()

CALL ProcCursor_CalTotalPrice()

（2）REFCURSOR类型游标

① 定义一个存储过程，用游标实验计算所有订单的总价。

CREATE OR REPLACE PROCEDURE ProcRefCursor_CalTotalPrice() AS

DECLARE

L_orderkey INTEGER; L_totalprice REAL; mycuesor REFCURSOR;

BEGIN

OPEN mycursor FOR SELECT orderkey,totalprice FROM Order; LOOP

FETCH mycursor INTO L_orderkey,L_totalprice; IF mycursor%NOTFOUND THEN EXIT; END IF;

SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO L_totalprice FROM Lineitem

WHERE orderkey=L_orderkey ;

UPDATE Orders SET totalprice=L_totalprice WHERE orderkey=L_orderkey; END LOOP;

CLOSE mycursor;

END;

② 执行存储过程ProcRefCursor_CalTotalPrice() CALL ProcRefCursor_CalTotalPrice();

（3）记录变量与游标

① 定义一个存储过程，用游标实现计算所有订单的总价。

CREATE OR REPLACE PROCEDURE ProcRefCursor_CalTotalPrice() AS

DECLARE

L_totalprice REAL; res RECORD;

CURSOR mycuesor FOR

SELECT orderkey,totalprice FROM Orders; BEGIN

OPEN mycursor LOOP

FETCH mycursor INTO res;

IF mycursor%NOTFOUND THEN EXIT; END IF;

SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO

L_totalprice

FROM Lineitem

WHERE orderkey=res.orderkey ;

UPDATE Orders SET totalprice=L_totalprice WHERE orderkey=res.orderkey; END LOOP;

CLOSE mycursor; END;

② 执行存储过程ProcRefCursor_CalTotalPrice() CALL ProcRefCursor_CalTotalPrice()； （4）带参数的游标

① 定义一个存储过程，用游标实现计算指定国家的用户订单的总价。 CREATE OR REPLACE PROCEDURE ProcRefCursor_CalTotalPrice(p_nationname

CHAR(20)) AS

DECLARE

L_totalprice REAL; res RECORD;

CURSOR mycuesor (c_nationname CHAR(20))FOR

SELECT O.orderkey,O.totalprice FROM Orders O,Customer O,Nation N

WHERE O.custkey=C.custkey AND c.nationkey=N.nationkey

AND

TRIM(N.name)=TRIM(c_nationname);

BEGIN

OPEN mycursor (p_nationname); LOOP

FETCH mycursor INTO res; IF mycursor%NOTFOUND THEN EXIT; END IF;

SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO

L_totalprice

FROM Lineitem

WHERE orderkey=res.orderkey ;

UPDATE Orders SET totalprice=L_totalprice WHERE orderkey=res.orderkey; END LOOP;

CLOSE mycursor; END;

② 执行存储过程ProcParaCursor_CalTotalPrice()

CALL ProcParaCursor_CalTotalPrice('中国')；

五、实验总结

1.REFCURSOR类型的游标定义一个游标应用变量，只是再打开该类型游标时才指定具体的SELECT语句以便产生游标的结果集。因此，REFCURSOR实质上是定义了一个动态游标，可以灵活方便地根据程序运行时情况的变动设置游标的SELECT查询结果集。

2.从任务（1）可以看出，游标可以实现对数据库记录逐条处理，而不是整个结果集一起处理，因此，游标是在PL/SQL语言中实现过程化处理的核心功能。 3.从任务（3）看出，记录对于游标结果记录的处理很方便，通过记录变量可以直接访问记录的每个属性，而无需为记录的每个属性定义相应的变量。