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

内容发布更新时间 : 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.

>>展开全文<<
12@gma联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4 ceshi