第10章作业
1、创建一个不带参数据的存储过程pro1,输出2015年的图书借出情况。
CREATE OR REPLACE PROCEDURE pro1
AS
var_id borrow.BOOK_ID%type;
var_date borrow.BORROW_DATE%type;
BEGIN
for my_emp_rec in (select BOOK_ID,BORROW_DATE from BORROW)
loop
var_id:=my_emp_rec.BOOK_ID;
var_date:=my_emp_rec.BORROW_DATE;
if(to_char(var_date,'YYYY')='2015') then
dbms_output.put_line(var_id ||' '||var_date);
end if;
end loop;
END pro1;
/
2、创建一个不带参数据的存储过程pro2,统计并输出2015年每个月份的图书借出的册数。
CREATE OR REPLACE PROCEDURE pro2
AS
v_status varchar2(5);
var_date borrow.BORROW_DATE%type;
var_one int :=0;
var_two int :=0;
var_three int :=0;
var_four int :=0;
var_five int :=0;
var_six int :=0;
var_seven int :=0;
var_eight int :=0;
var_nine int :=0;
var_ten int :=0;
var_eleven int :=0;
var_twelef int :=0;
BEGIN
for my_emp_rec in (select BORROW_DATE from BORROW)
loop
var_date:=my_emp_rec.BORROW_DATE;
v_status:=to_char(var_date,'MM');
case v_status
when '01' then var_one:=var_one+1;
when '02' then var_two:=var_two+1;
when '03' then var_three:=var_three+1;
when '04' then var_four:=var_four+1;
when '05' then var_five:=var_five+1;
when '06' then var_six:=var_six+1;
when '07' then var_seven:=var_seven+1;
when '08' then var_eight:=var_eight+1;
when '09' then var_nine:=var_nine+1;
when '10' then var_ten:=var_ten+1;
when '11' then var_eleven:=var_eleven+1;
when '12' then var_twelef:=var_twelef+1;
endcase;
end loop;
dbms_output.put_line(var_one||' '||var_two||' '||var_three||' '||var_four||' '||var_five||' '||var_six||''||var_seven||' '||var_eight||' '||var_nine||' '||var_ten||' '||var_eleven||''||var_twelef);
END pro2;
/
3、将上面的存储过程修改为带参的存储过程pro3,根据输入的年份,统计并输出该年份每个月份的图书借出的册数。
CREATE OR REPLACE PROCEDURE pro3(var_yearin varchar2)
AS
v_status varchar2(5);
var_date borrow.BORROW_DATE%type;
var_one int :=0;
var_two int :=0;
var_three int :=0;
var_four int :=0;
var_five int :=0;
var_six int :=0;
var_seven int :=0;
var_eight int :=0;
var_nine int :=0;
var_ten int :=0;
var_eleven int :=0;
var_twelef int :=0;
BEGIN
for my_emp_rec in (select BORROW_DATE from BORROW)
loop
var_date:=my_emp_rec.BORROW_DATE;
v_status:=to_char(var_date,'MM');
if(to_char(var_date,'YYYY')=var_year) then
case v_status
when '01' then var_one:=var_one+1;
when '02' then var_two:=var_two+1;
when '03' then var_three:=var_three+1;
when '04' then var_four:=var_four+1;
when '05' then var_five:=var_five+1;
when '06' then var_six:=var_six+1;
when '07' then var_seven:=var_seven+1;
when '08' then var_eight:=var_eight+1;
when '09' then var_nine:=var_nine+1;
when '10' then var_ten:=var_ten+1;
when '11' then var_eleven:=var_eleven+1;
when '12' then var_twelef:=var_twelef+1;
endcase;
else exit;
end if;
end loop;
dbms_output.put_line(var_one||' '||var_two||' '||var_three||' '||var_four||' '||var_five||' '||var_six||''||var_seven||' '||var_eight||' '||var_nine||' '||var_ten||' '||var_eleven||''||var_twelef);
END pro3;
/
4、创建一个带参的存储过程pro4,根据输入的读者的姓名,输出该读者的借书情况,包括借书的日期、书名、出版社、单价及应归还的日期(假设最长借期为30天)。
CREATE OR REPLACE PROCEDURE pro4(var_namein varchar2)
AS
v_status varchar2(5);
var_date borrow.BORROW_DATE%type;
var_bookname BOOK.BOOK_NAME%type;
var_output BOOK.OUTPUT%type;
var_price BOOK.PRICE%type;
var_readername READER.NAME%type;
var_shdate date;
BEGIN
for my_emp_rec in (select a.BORROW_DATE,b.BOOK_NAME,b.OUTPUT,b.PRICE,c.NAME from BORROW a,BOOK b,READER c where a.BOOK_ID=b.BOOK_ID anda.READER_ID=c.READER_ID)
loop
var_date:=my_emp_rec.BORROW_DATE;
var_bookname:=my_emp_rec.BOOK_NAME;
var_output:=my_emp_rec.OUTPUT;
var_price:=my_emp_rec.PRICE;
var_readername:=my_emp_rec.NAME;
var_shdate:=var_date+30;
if(var_readername=var_name) then
dbms_output.put_line(var_date||' '||var_bookname||' '||var_output||' '||var_price||' '||var_shdate);
end if;
end loop;
END pro4;
/