實驗6PLSQL程序設計

1、實驗6 PL/SQL程序設計1 實驗目的(1) 掌握PL/SQL程序開發(fā)方法。(2) 掌握存儲過程、函數(shù)、觸發(fā)器、包的創(chuàng)建于調用。2 實驗要求(1) 根據(jù)圖書銷售系統(tǒng)業(yè)務要求創(chuàng)建特定的存儲過程、函數(shù)、觸發(fā)器。(2) 根據(jù)圖書銷售系統(tǒng)業(yè)務要求將圖書銷售系統(tǒng)相關的函數(shù)、存儲過程封裝到包里。3 實驗步驟以bs用戶登錄BOOKSALES數(shù)據(jù)庫，利用PL/SQL程序編寫下列功能模塊。(1) 創(chuàng)建一個存儲過程，輸出不同類型圖書的數(shù)量、平均價格。SQL> create or replace procedure proc_category_static 2 as 3 -定義游標，獲取當前有哪些圖書種類

2、4 cursor c_all_category is select distinct category from books; 5 -圖書的平均價格 6 v_avg_cost number; 7 begin 8 -保存圖書種類 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where category=v_each_category.category group by category; 11 dbms_output.put_line('種類為：

3、'|v_each_category.category|',平均價格為：'| v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 /(2) 創(chuàng)建一個存儲過程，以客戶號為參數(shù)，輸出該客戶訂購的所有圖書的名稱與數(shù)量。create or replace procedure proc_get_orderinfo( 2 p_customer_id customers.customer_id%type) 3 as 4 -聲明游標存儲客戶的訂單號 5 cursor c_orderid is select order_id

4、from orders where customer_id=p_customer_id; 6 v_orderid orders.order_id%type; 7 -聲明游標存儲訂單信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; 9 -保存圖書的書名 10 v_title books.title%type; 11 12 begin 13 open c_orderid; 14 LOOP 15 fetch c_or

5、derid into v_orderid; 16 exit when c_orderid%NOTFOUND; 17 for v_orderitem in c_orderitem LOOP 18 select title into v_title from books where ISBN=v_orderitem.ISBN; 19 DBMS_OUTPUT.PUT_LINE(p_customer_id|'訂購'|v_title|'的數(shù)量是'|v_orderitem.totalnum); 20 end LOOP; 21 end LOOP; 22 close c_ord

6、erid; 23 end proc_get_orderinfo; 24 /exec proc_get_orderinfoo(1001);(3) 創(chuàng)建一個存儲過程，以訂單號為參數(shù)，輸出該訂單中所有圖書的名稱、單價、數(shù)量。create or replace procedure proc_get_orderinfoo( p_order_id orderitem.order_id%type)as -聲明游標存儲訂單號的ISBN cursor c_ISBN is select ISBN from orderitem where order_id=p_order_id; v_ISBN orderitem.

7、ISBN%type; -聲明游標存儲訂單信息 cursor c_orderitem is select ISBN,sum(quantity) totalnum from orderitem where ISBN=v_ISBN ; v_title books.title%type; v_retail books.retail%type;begin open c_ISBN; LOOP fetch c_ISBN into v_ISBN; exit when c_ISBN%NOTFOUND; for v_orderitem in c_orderitem LOOP select title,retail

8、 into v_title,v_retail from books where ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_order_id|v_title|v_retail|v_orderitem.totalnum); end LOOP; end LOOP; close c_ISBN;end proc_get_orderinfoo;/(4) 創(chuàng)建一個存儲過程，以出版社名為參數(shù)，輸出該出版社出版的所有圖書的名稱、ISBN、批發(fā)價格、零售價格信息。create or replace procedure proc_get_name( p_title

9、books.title%type)as cursor c_orderid is select order_id from orders where customer_id=p_customer_id; v_orderid orders.order_id%type; cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; v_title books.title%type;begin open c_orderid; LOOP fe

10、tch c_orderid into v_orderid; exit when c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP select title into v_title from books where ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_customer_id|''|v_title|'的數(shù)量是'|v_orderitem.totalnum); end LOOP; end LOOP; close c_orderid;end proc_g

11、et_orderinfo;/set serveroutput ondeclarev_customer number;beginv_customer :=&x;proc_get_orderinfo(v_customer);end;/(5) 創(chuàng)建一個存儲過程，輸出每個客戶訂購的圖書的數(shù)量、價格總額。create or replace procedure proc_category_staticas cursor c_all_category is select distinct category from books; v_sum_cost number;begin for v_each_

12、category in c_all_category LOOP select sum(retail) into v_sum_cost from books where category=v_each_category.category group by category; dbms_output.put_line('種類為：'|v_each_category.category|',總價格為：'| v_sum_cost); END LOOP;end proc_category_static;/set serveroutput onexec proc_categor

13、y_static;/(6) 創(chuàng)建一個存儲過程，輸出銷售數(shù)量前3名的圖書的信息及銷售名次。create or replace procedure proc_category_staticas cursor c_all_category is select distinct category from books; v_sum_retail number;begin for v_each_category in c_all_category LOOP select sum(cost) into v_sum_retail from books where category=v_each_catego

14、ry.category group by category; dbms_output.put_line('種類為：'|v_each_category.category|',數(shù)量為：'| v_sum_retail); END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static; (7) 創(chuàng)建一個存儲過程，輸出訂購圖書數(shù)量最多的客戶的信息及訂購圖書的數(shù)量。(8) 創(chuàng)建一個存儲過程，輸出各類圖書中銷售數(shù)量最多的圖書的信息及銷售的數(shù)量。(9) 創(chuàng)建一個包，實現(xiàn)查詢客戶訂購

15、圖書詳細信息的分頁顯示。create or replace procedure proc_title_staticas cursor c_all_title is select distinct title from books; v_sum_retail number;begin for v_each_title in c_all_title LOOP select sum(cost) into v_sum_retail from books where title=v_each_title.title group by title; dbms_output.put_line('信息

16、為：'|v_each_title.title|',數(shù)量為：'| v_sum_retail); END LOOP;end proc_title_static;/(10) 創(chuàng)建一個包，利用集合實現(xiàn)圖書銷售排行榜的分頁顯示。(11) 創(chuàng)建一個包，包含一個函數(shù)和一個過程。函數(shù)以圖書類型為參數(shù)，返回該類型圖書的平均價格。過程輸出各種類型圖書中價格高于同類型圖書平均價格的圖書信息。create or replace package pkg_bookas function get_book_avgcost(p_book_category BOOKS.category%type) re

17、turn number; procedure pro_showbook(p_book_category BOOKS.category%type);end;/create or replace package body pkg_bookas function get_book_avgcost(p_book_category BOOKS.category%type) return number as v_ISBN BOOKS.ISBN%type; cursor c_books is select retail from BOOKS where ISBN=v_ISBN; v_sumcost numb

18、er(6,2):=0; v_count number(6) :=0; v_avgcost number :=0; v_book_category varchar2(10); begin select ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOP v_count:=v_count+1; v_sumcost:= v_sumcost+v_retail.retail; end LOOP; v_avgcost:=v_sumcost/v_count; DBMS_OUTPUT.

19、PUT_LINE(v_book_category| '-'|v_avgcost); return v_avgcost; end; procedure pro_showbook(p_book_category BOOKS.category%type) as v_book_category varchar2(10); cursor c_books is select * from BOOKS where retail>=get_book_avgcost(v_book_category); begin for v_books in c_books loop dbms_outpu

20、t.put_line(v_books.ISBN|' '|v_books.title|' '|v_books.author|' '|v_books.pubdate|' '|v_books.publisher_id|' '|v_books.retail); end loop; end;end;/set serveroutput ondeclare p_book_category BOOKS.category%type; avgcost number;begin p_book_category:='管理'

21、 avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg__showbook('管理');end;/(12) 創(chuàng)建一個觸發(fā)器，當客戶下完訂單后，自動統(tǒng)計該訂單所有圖書價格總額。create or replace package order_total_costas v_order_id orders.order_id%type;end;/create or replace trigger trg_before_orderbefore insert on ORDERS for each rowbegin o

22、rder_total_cost.v_order_id:=:new.order_id;end;/set serveroutput oncreate or replace trigger trg_orderafter insert on ORDERitem declare cursor c_orderitem is select ISBN, quantity from orderitem where order_id=order_total_cost.v_order_id; v_ISBN orderitem.ISBN%type; v_quantity orderitem.quantity%type

23、; v_cost books.cost%type; v_sumcost number(6,2):=0;begin 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

24、 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*'

25、;|'now v_sumcost is'|v_sumcost); end LOOP;end;/(13) 創(chuàng)建一個觸發(fā)器，禁止客戶在非工作時間（早上8:00之前，晚上17:00之后）下訂單。(14) 創(chuàng)建一個函數(shù)，以客戶號為參數(shù)，返回該客戶訂購圖書的價格總額。 create or replace function get_sumcost( v_customer_id customers.customer_id%type)return numberas cursor c_orderid is select order_id from orders where customer_id

26、=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_orde

27、rid; 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 fr

28、om 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_orde

29、rid; return v_sumcost;end get_sumcost;/set serveroutput ondeclarev_totalMoney BOOKS.cost%type; v_customer number;beginv_customer :=&x;v_totalMoney:=get_sumcost(v_customer);dbms_output.put_line(v_customer|'的購買總額是'|v_totalMoney);end;/(15) 創(chuàng)建一個函數(shù)，以訂單號為參數(shù)，返回該訂單訂購圖書的價格總額。(16) 創(chuàng)建一個函數(shù)，以出版社名為參數(shù)，

30、返回該出版社出版的圖書的平均價格。create or replace function get_pub_avgcost( v_pub_name %type)return numberas v_pub_id publishers.publisher_id%type; cursor c_books is select retail from books where publisher_id=v_pub_id; v_sumcost number(6,2):=0; v_count number(6) :=0;begin select publisher_id into v

31、_pub_id from publishers where name=v_pub_name; for v_retail in c_books LOOP v_count:=v_count+1; v_sumcost:= v_sumcost+v_retail.retail; DBMS_OUTPUT.PUT_LINE(v_count| '-'|v_sumcost); end LOOP; return v_sumcost;end get_pub_avgcost;/set serveroutput ondeclarev_avgMoney BOOKS.cost%type; v_pubname

32、 %type;beginv_pubname :=&x;v_avgMoney:=get_pub_avgcost(v_pubname);dbms_output.put_line(v_pubname|'的出版圖書的平均價格是'|v_avgMoney);end;/(17) 創(chuàng)建一個函數(shù)，以客戶號為參數(shù)，返回該客戶可以獲得的禮品名稱。create or replace function get_gift( v_customer_id customers.customer_id%type)return numberas cursor c_orderid

33、 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_gift number(6,2):=0;begin op

34、en 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.qua

35、ntity<=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_gift:= v_gift+v_orderitem.quantity*v_cost; DBMS_OUTPUT.PUT_LINE('3*'|v_gi

36、ft); end LOOP; end LOOP; close c_orderid; return v_gift;end get_gift;/set serveroutput ondeclarev_totalMoney BOOKS.cost%type; v_customer number;beginv_customer :=&x;v_totalMoney:=get_sumcost(v_customer);dbms_output.put_line(v_customer|'的禮物是'|v_totalMoney);end;/(18) 創(chuàng)建一個函數(shù)，以圖書號為參數(shù)，統(tǒng)計該圖書被訂購的總數(shù)量。create or replace function get_sumnum( v_customer_id customers.customer_id%type)return numberas cursor c_orderid is select order_id from orders where customer_id=v_c