實(shí)驗(yàn)6--PLSQL程序設(shè)計(jì)

1、山東建筑大學(xué) 計(jì)算機(jī)學(xué)院 實(shí)驗(yàn)報(bào)告班級(jí)：軟測(cè)143 姓名：劉骎 學(xué)號(hào)：20141113089實(shí)驗(yàn)成績(jī)： 課程：Oracle 同組者： 實(shí)驗(yàn)日期： 實(shí)驗(yàn)6 PL/SQL程序設(shè)計(jì)1 實(shí)驗(yàn)?zāi)康?1) 掌握PL/SQL程序開(kāi)發(fā)方法。(2) 掌握存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器、包的創(chuàng)建于調(diào)用。2 實(shí)驗(yàn)要求(1) 根據(jù)圖書(shū)銷(xiāo)售系統(tǒng)業(yè)務(wù)要求創(chuàng)建特定的存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器。(2) 根據(jù)圖書(shū)銷(xiāo)售系統(tǒng)業(yè)務(wù)要求將圖書(shū)銷(xiāo)售系統(tǒng)相關(guān)的函數(shù)、存儲(chǔ)過(guò)程封裝到包里。3 實(shí)驗(yàn)步驟以bs用戶(hù)登錄BOOKSALES數(shù)據(jù)庫(kù)，利用PL/SQL程序編寫(xiě)下列功能模塊。(1) 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，輸出不同類(lèi)型圖書(shū)的數(shù)量、平均價(jià)格。SQL creat

2、e or replace procedure proc_category_static 2 as 3 -定義游標(biāo)，獲取當(dāng)前有哪些圖書(shū)種類(lèi) 4 cursor c_all_category is select distinct category from books; 5 -圖書(shū)的平均價(jià)格 6 v_avg_cost number; 7 begin 8 -保存圖書(shū)種類(lèi) 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where category=v_each_c

3、ategory.category group by category; 11 dbms_output.put_line(種類(lèi)為：|v_each_category.category|,平均價(jià)格為：| v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 /(2) 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以客戶(hù)號(hào)為參數(shù)，輸出該客戶(hù)訂購(gòu)的所有圖書(shū)的名稱(chēng)與數(shù)量。create or replace procedure proc_get_orderinfo( 2 p_customer_id customers.customer_id%type) 3 as 4 -聲明

4、游標(biāo)存儲(chǔ)客戶(hù)的訂單號(hào) 5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id; 6 v_orderid orders.order_id%type; 7 -聲明游標(biāo)存儲(chǔ)訂單信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; 9 -保存圖書(shū)的書(shū)名 10 v_title books.title%type; 11

5、12 begin 13 open c_orderid; 14 LOOP 15 fetch c_orderid 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|訂購(gòu)|v_title|的數(shù)量是|v_orderitem.totalnum); 20 end LOOP

6、; 21 end LOOP; 22 close c_orderid; 23 end proc_get_orderinfo; 24 /exec proc_get_orderinfoo(1001);(3) 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以訂單號(hào)為參數(shù)，輸出該訂單中所有圖書(shū)的名稱(chēng)、單價(jià)、數(shù)量。create or replace procedure proc_get_orderinfoo( p_order_id orderitem.order_id%type)as -聲明游標(biāo)存儲(chǔ)訂單號(hào)的ISBN cursor c_ISBN is select ISBN from orderitem where order_id=

7、p_order_id; v_ISBN orderitem.ISBN%type; -聲明游標(biāo)存儲(chǔ)訂單信息 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_order

8、item LOOP select title,retail 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)建一個(gè)存儲(chǔ)過(guò)程，以出版社名為參數(shù)，輸出該出版社出版的所有圖書(shū)的名稱(chēng)、ISBN、批發(fā)價(jià)格、零售價(jià)格信息。create or replace proc

9、edure proc_get_name( p_title 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;

10、begin open c_orderid; LOOP fetch 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;en

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

12、ach_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(種類(lèi)為：|v_each_category.category|,總價(jià)格為：| v_sum_cost); END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static;/(6) 創(chuàng)建

13、一個(gè)存儲(chǔ)過(guò)程，輸出銷(xiāo)售數(shù)量前3名的圖書(shū)的信息及銷(xiāo)售名次。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_category.category grou

14、p by category; dbms_output.put_line(種類(lèi)為：|v_each_category.category|,數(shù)量為：| v_sum_retail); END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static; (7) 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，輸出訂購(gòu)圖書(shū)數(shù)量最多的客戶(hù)的信息及訂購(gòu)圖書(shū)的數(shù)量。(8) 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，輸出各類(lèi)圖書(shū)中銷(xiāo)售數(shù)量最多的圖書(shū)的信息及銷(xiāo)售的數(shù)量。(9) 創(chuàng)建一個(gè)包，實(shí)現(xiàn)查詢(xún)客戶(hù)訂購(gòu)圖書(shū)詳細(xì)信息的分頁(yè)顯示。create or replace proced

15、ure 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(信息為：|v_each_title.title|,數(shù)量為：| v_sum_retail

16、); END LOOP;end proc_title_static;/(10) 創(chuàng)建一個(gè)包，利用集合實(shí)現(xiàn)圖書(shū)銷(xiāo)售排行榜的分頁(yè)顯示。(11) 創(chuàng)建一個(gè)包，包含一個(gè)函數(shù)和一個(gè)過(guò)程。函數(shù)以圖書(shū)類(lèi)型為參數(shù)，返回該類(lèi)型圖書(shū)的平均價(jià)格。過(guò)程輸出各種類(lèi)型圖書(shū)中價(jià)格高于同類(lèi)型圖書(shū)平均價(jià)格的圖書(shū)信息。create or replace package pkg_bookas function get_book_avgcost(p_book_category BOOKS.category%type) return number; procedure pro_showbook(p_book_category BOOK

17、S.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 number(6,2):=0; v_count number(6) :=0; v_avgcost number :=0;

18、 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.PUT_LINE(v_book_category| -|v_avgcost); return v_avgcost

19、; 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_output.put_line(v_books.ISBN| |v_books.title| |v_books.author| |v_books.pub

20、date| |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:=管理; avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg__showbook(管理);end;/(12) 創(chuàng)建一個(gè)觸發(fā)器，當(dāng)客戶(hù)下完訂單后，自動(dòng)統(tǒng)計(jì)該訂單所有圖書(shū)價(jià)格總額。create

21、 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 order_total_cost.v_order_id:=:new.order_id;end;/set serveroutput oncreate or replace trigger trg_orderafter insert on ORDERitem declare curs

22、or 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; 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 in

23、to v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(1-|v_cost|:|v_orderitem.ISBN); elsif v_orderitem.quantity10 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.quantity10 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_or