带参数据的存储过程

第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;

/