内容发布更新时间 : 2024/5/20 19:47:03星期一 下面是文章的全部内容请认真阅读。
① 定义一个自定义函数，更新并返回给定订单的总价
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）看出，记录对于游标结果记录的处理很方便，通过记录变量可以直接访问记录的每个属性，而无需为记录的每个属性定义相应的变量。