杨艳华oracle实验6,Oracle实验六 PL/SQL程序设计

Oracle PL/SQL程序设计 实验六

实验内容

以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。

SQL>CONN bs/bs@booksales

(1)创建一个函数，以客户号为参数，返回该客户订购图书的价格总额。

create or replace function sumprice(

id customers.customer_id%type)

return books.cost%type

as

sumprice books.cost%type;

begin

select sum(quantity*cost) into sumprice from customers,books,orders,orderitem

where customers.customer_id = orders.customer_id and orders.order_id = orderitem.order_id and orderitem.isbn = books.isbn and customers.customer_id = id;

return sumprice;

exception

when no_data_found then

dbms_output.put_line('the id is invaild!');

end sumprice;

/

(6)创建一个存储过程，输出不同类型图书的数量、平均价格。

create or replace procedure books_msg

as

books_count orderitem.quantity%type;

begin

for emp in (

select title,ISBN,cost from books

)

loop

select sum(quantity) into books_count from orderitem where ISBN=emp.ISBN;

dbms_output.put_line(emp.title||' 单价：'||emp.cost||' 数量：'||books_count);

end loop;

end books_msg;

/

(18)创建一个触发器，禁止客户在非工作时间(早上8：00之前，晚上17：00之后)下订单。

CREATE OR REPLACE TRIGGER trg_orderitem

BEFORE INSERT OR UPDATE OR DELETE ON orderitem

BEGIN

IF TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '17:00'

OR TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN')

THEN

RAISE_APPLICATION_ERROR(-20005,'只能在正常的时间内进行改变。');

END IF;

END trg_orderitem;

/

检查

调用函数看结果；

declare

sprice books.cost%type;

begin

sprice:=sumprice(1);

dbms_output.put_line('价格'||sprice);

exception

when no_data_found then

dbms_output.put_line('not found');

end;

/

执行存储过程看结果；

触发触发器看结果

insert into orderitem values('1000','1','978-7-121-18619-8','5');

标签：customers,orderitem,books,sumprice,SQL,Oracle,type,id,PL

来源： https://blog.csdn.net/weixin_43721535/article/details/105941400