Oracle实验四

1、实验目的
（1）掌握PL/SQL程序开发方法。
（2）掌握存储过程的创建与调用。
（3）掌握存储过程的创建与调用。
（4）掌握触发器的创建与应用。
2、实验环境

3、实验要求
（1）根据图书销售系统业务要求创建实现特定功能的函数。
（2）根据图书销售系统业务要求创建实现特定功能的存储过程。
（3）根据图书销售系统业务要求创建实现特定功能的触发器。
4、实验内容
以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。
SQL>CONN bs/bs@booksales
（1）创建一个函数，以客户号为参数，返回该客户订购图书的价格总额。
（6）创建一个存储过程，输出不同类型图书的数量、平均价格。
（18）创建一个触发器，禁止客户子在非工作时间（早上8：00之前，晚上17：00之后）下订单。
5、实验过程与结果
(1)INSERT INTO CUSTOMERS VALUES(SEQ_CUSTOMERS.NEXTVAL,‘王牧’,‘83823422’,‘WANGMU@SINA.COM’,‘北京’,‘110010’);

INSERT INTO CUSTOMERS VALUES(SEQ_CUSTOMERS.NEXTVAL,‘李青’,‘83824566’,‘LIQING@SINA.COM’,‘大连’,‘116023’);

INSERT INTO PUBLISHERS VALUES(1,‘电子工业出版社’,‘张芳’,‘56231234’);

INSERT INTO PUBLISHERS VALUES(2,‘机械工业出版社’,‘孙翔’,‘89673456’);

INSERT INTO BOOKS VALUES(‘978-7-121-18619-8’,‘文化基础’,‘王澜’，TO_DATE(‘2010-1-1’,‘YYYY-MM-DD’),2,35,28,‘管理’);

INSERT INTO BOOKS VALUES(‘978-7-122-18619-8’,‘ORACLE’,‘王凤栋’，TO_DATE(‘2011-2-1’,‘YYYY-MM-DD’),1,40,32,‘计算机’);

INSERT INTO ORDERS VALUES(SEQ_ORDERS.NEXTVAL,2,TO_DATE(‘2013-2-1’,‘YYYY-MM-DD’),TO_DATE(‘2013-2-5’,‘YYYY-MM-DD’),‘大连’,‘116023’);

INSERT INTO ORDERS VALUES(SEQ_ORDERS.NEXTVAL,3,TO_DATE(‘2013-3-1’,‘YYYY-MM-DD’),TO_DATE(‘2013-3-10’,‘YYYY-MM-DD’),‘大连’,‘116023’);

INSERT INTO ORDERITEM VALUES(1001,1,‘978-7-121-18619-8’,5);

INSERT INTO ORDERITEM VALUES(1001,2,‘978-7-122-18619-8’,20);

INSERT INTO ORDERITEM VALUES(1002,1,‘978-7-121-18619-8’,15);

INSERT INTO PROMOTION VALUES(1,‘签字笔’,100,150);

INSERT INTO PROMOTION VALUES(2,‘笔记本’,150,300);

INSERT INTO PROMOTION VALUES(3,‘保温杯’,300,500);

create or replace function get_sumcost(
v_customer_id customers.customer_id%type)
return number
as
cursor c_orderid is select order_id from orders where customer_id=v_customer_id;
v_orderid orders.order_id%type;
cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid;
v_ISBN orderitem.ISBN%type;
v_quantity orderitem.quantity%type;
v_cost books.cost%type;
v_sumcost number(6,2):=0;
begin
open c_orderid;
LOOP
fetch c_orderid into v_orderid;
exit when c_orderid%NOTFOUND;
for v_orderitem in c_orderitem LOOP
if v_orderitem.quantity >10 then
select cost into v_cost from books where ISBN = v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE(‘1----’||v_cost||v_orderitem.ISBN);
elsif v_orderitem.quantity<=10 then
select retail into v_cost from books where ISBN = v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE(‘2----’||v_cost||v_orderitem.ISBN);
else
DBMS_OUTPUT.PUT_LINE(‘number of book is error!’);
end if;
v_sumcost:= v_sumcost+v_orderitem.quantity*v_cost;
DBMS_OUTPUT.PUT_LINE(‘3*****’||v_sumcost);
end LOOP;
end LOOP;
close c_orderid;
return v_sumcost;
end get_sumcost;
/

结果：
SQL> conn bs/bs@wangwen
已连接。
SQL> INSERT INTO CUSTOMERS VALUES(SEQ_CUSTOMERS.NEXTVAL,‘王牧’,‘83823422’,‘WANGMU@SINA.COM’,‘北京’,‘110010’);

已创建 1 行。

SQL> INSERT INTO CUSTOMERS VALUES(SEQ_CUSTOMERS.NEXTVAL,‘李青’,‘83824566’,‘LIQING@SINA.COM’,‘大连’,‘116023’);

已创建 1 行。

SQL> INSERT INTO PUBLISHERS VALUES(1,‘电子工业出版社’,‘张芳’,‘56231234’);

已创建 1 行。

SQL>
SQL> INSERT INTO PUBLISHERS VALUES(2,‘机械工业出版社’,‘孙翔’,‘89673456’);

已创建 1 行。

SQL> INSERT INTO BOOKS VALUES(‘978-7-121-18619-8’,‘文化基础’,‘王澜’，TO_DATE(‘2010-1-1’,‘YYYY-MM-DD’),2,35,28,‘管理’);

已创建 1 行。

SQL> INSERT INTO BOOKS VALUES(‘978-7-122-18619-8’,‘ORACLE’,‘王凤栋’，TO_DATE(‘2011-2-1’,‘YYYY-MM-DD’),1,40,32,‘计算机’);

已创建 1 行。

SQL> INSERT INTO ORDERS VALUES(SEQ_ORDERS.NEXTVAL,2,TO_DATE(‘2013-2-1’,‘YYYY-MM-DD’),TO_DATE(‘2013-2-5’,‘YYYY-MM-DD’),‘大连’,‘116023’);

已创建 1 行。

SQL> INSERT INTO ORDERS VALUES(SEQ_ORDERS.NEXTVAL,3,TO_DATE(‘2013-3-1’,‘YYYY-MM-DD’),TO_DATE(‘2013-3-10’,‘YYYY-MM-DD’),‘大连’,‘116023’);

已创建 1 行。

SQL> INSERT INTO ORDERITEM VALUES(1001,1,‘978-7-121-18619-8’,5);

已创建 1 行。

SQL> INSERT INTO ORDERITEM VALUES(1001,2,‘978-7-122-18619-8’,20);

已创建 1 行。

SQL> INSERT INTO ORDERITEM VALUES(1002,1,‘978-7-121-18619-8’,15);

已创建 1 行。

SQL> INSERT INTO PROMOTION VALUES(1,‘签字笔’,100,150);

已创建 1 行。

SQL> INSERT INTO PROMOTION VALUES(2,‘笔记本’,150,300);

已创建 1 行。

SQL> INSERT INTO PROMOTION VALUES(3,‘保温杯’,300,500);

已创建 1 行。

（2）
create or replace procedure get_avgcost2
as
cursor c_all_category is select distinct category,count(*) c, avg(retail) a from books group by category;
begin
for v_each_category in c_all_category LOOP
dbms_output.put_line(‘种类为：’||v_each_category.category||‘数量为:’||v_each_category.c||’,平均价格(批发价)为：’||v_each_category.a);
END LOOP;
end get_avgcost2;

结果：

（3）
create or replace trigger trg_secure_emp
before insert or update or delete on orders
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_secure_emp;
/
结果：