内容发布更新时间 : 2025/7/31 3:52:50星期一 下面是文章的全部内容请认真阅读。
① 定义一个自定义函数,更新并返回给定订单的总价
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.