《数据库系统原理》实验报告 下载本文

① 定义一个自定义函数,更新并返回给定订单的总价

CREATE OR REPLACE FUNCTION FUN_CalTotalPrice4Order(p_okey INTEGER)

RETURN REAL AS

DECLARE

res REAL;

BEGIN UPDATE Orders SET totalprice =

(SELECT SUM(extendedprice * (1-discount)*(1+tax)) FROM Lineitem WHERE Orders.orderkey=Lineitem.orderkey AND

Lineitem.orderkey=p_okey);

SELECT SUM(toyalprice) INTO res FROM Orders; RETURN res; END;

② 执行自定义函数FUN_CalTotalPrice4Order()

CALL Fun_CalTotalPrice4Order(5365);

(3)有局部变量的自定义函数

① 定义一个自定义函数,计算并返回某个顾客的所有订单的总价 CREATE OR REPLACE FUNCTION FUN_CalTotalPrice4Customer(p_custname CHAR(25)) RETURN REAL AS

DECLARE

L_custkey INTEGER; res REAL; BEGIN

SELECT cuskey INTO L_custkey FROM Customer

WHERE name = trim(p_custname);

RAISE NOTICE'cueskey is %',L_custkey; UPDATE Orders SET totalprice =

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

WHERE Orders.orderkey=Lineitem.orderkey AND

Orders.custkey=L_custkey);

SELECT SUM(toyalprice) INTO res FROM Orders;

WHERE custkey=L_custkey; RETURN res; END;

② 执行自定义函数FUN_CalTotalPrice4Customer()

SELECT FUN_CalTotalPrice4Customer('陆皆宇');

(4)有输出参数的自定义函数

① 定义一个自定义函数,计算并返回某个顾客的所有订单的总价 CREATE OR REPLACE FUNCTION FUN_CalTotalPrice4Customer2(p_custname

CHAR(25),OUT p_totalprice REAL) RETURN REAL AS

DECLARE

L_custkey INTEGER; res REAL; BEGIN

SELECT cuskey INTO L_custkey FROM Customer

WHERE name = trim(p_custname);

RAISE NOTICE'cueskey is %',L_custkey; UPDATE Orders SET totalprice =

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

WHERE Orders.orderkey=Lineitem.orderkey AND

Orders.custkey=L_custkey);

SELECT SUM(toyalprice) INTO p_totalprice FROM Orders WHERE custkey=L_custkey; Res := p_totalprice; RETURN res; END;

② 执行自定义函数FUN_CalTotalPrice4Customer2()。

SELECT FUN_CalTotalPrice4Customer2('陆皆宇',null);

(5)修改自定义函数

① 修改自定义函数名FUN_CalTotalPrice4Order 为CalTotalPrice4Order

ALTER FUNCTIONFUN_CalTotalPrice4Order RENAME TO CalTotalPrice4Order ;

② 编译自定义函数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)看出,记录对于游标结果记录的处理很方便,通过记录变量可以直接访问记录的每个属性,而无需为记录的每个属性定义相应的变量。