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

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

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 ;