存储过程及触发器

一、目的

1. 了解存储过程的概念、优点
2. 熟练掌握创建存储过程的方法
3. 熟练掌握存储过程的调用方法
4. 了解触发器的概念、优点
5. 掌握触发器的方法和步骤
6. 掌握触发器的使用

二、准备

ORACLE,PLSQL

三、步骤、出现的问题及解决方案

1、建立存储过程完成图书管理系统中的借书功能，并调用该存储过程实现借书功能。

功能要求：
借书时要求输入借阅流水号，借书证号，图书编号。（即该存储过程有3个输入参数）
借书时，借书日期为系统时间。
图书的是否借出改为‘是’

CREATE OR REPLACE PROCEDURE LENDBOOK
(V_BO_ID BORROWING.BO_ID%TYPE,
V_R_NUMBER READERS.R_NUMBER%TYPE,
V_B_ID BOOK.B_ID%TYPE)
AS
BEGIN
  INSERT INTO BORROWING VALUES(V_BO_ID,V_R_NUMBER,V_B_ID,SYSDATE, NULL, NULL, NULL);
  UPDATE BOOK SET B_STATE='是'
  WHERE BOOK.B_ID = V_B_ID;
END;

测试：CALL LENDBOOK(7,20051001,3007071);



要求达成！

2、建立存储过程完成图书管理系统中的预约功能。

预约时要求输入预约流水号，借书证号，ISBN。（即该存储过程有3个输入参数）
存储过程先检查输入的ISBN版本的图书是否都已借出，如果是则进行预约，否则提示“该书目有可借图书，请查找”。
预约时间为系统时间。

CREATE OR REPLACE PROCEDURE BOOKINGBOOK
(V_Y_ID YUYUE.Y_ID%TYPE,
V_R_NUMBER READERS.R_NUMBER%TYPE,
V_BI_ISBN BOOK.BI_ISBN%TYPE)
AS V_BORROW NUMBER;
BEGIN
  SELECT COUNT(*) INTO V_BORROW FROM BOOK 
  WHERE BOOK.B_STATE = '否' AND BOOK.BI_ISBN = V_BI_ISBN;
  IF V_BORROW = 0 THEN
    INSERT INTO YUYUE(Y_ID,R_NUMBER,BI_ISBN,Y_TIME)
    VALUES(V_Y_ID,V_R_NUMBER,V_BI_ISBN,SYSDATE);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line('该书目有可借图书，请查找');
  END IF;
END;

测试：CALL BOOKINGBOOK(2,20062001,9787010073750);
CALL BOOKINGBOOK(3,20051001,9787010073750);

3、建立存储过程完成图书管理系统中的还书功能。

还书时要求输入借书证号，图书编号，罚款分类号（即该存储过程有3个输入参数）。
还书日期为系统时间。
图书的是否借出改为‘否’。

CREATE OR REPLACE PROCEDURE SENDBOOK
(V_R_NUMBER READERS.R_NUMBER%TYPE,
V_B_ID BORROWING.B_ID%TYPE,
V_F_ID F.F_ID%TYPE)
AS
BEGIN
  UPDATE BORROWING
  SET RETURNDATE = SYSDATE,F_ID = V_F_ID
  WHERE
    V_R_NUMBER = BORROWING.R_NUMBER AND V_B_ID = BORROWING.B_ID;
  UPDATE BOOK
  SET BOOK.B_STATE = '否'
  WHERE V_B_ID = BOOK.B_ID;
END;

测试：call sendbook(20051001,2001231,null);

4、通过序列和触发器实现借阅表中借阅流水号字段的自动递增。

CREATE SEQUENCE SEQ_ID
MINVALUE 1
MAXVALUE 1.0E28
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE OR REPLACE TRIGGER TIR_LEND
       BEFORE INSERT ON BORROWING
       FOR EACH ROW
BEGIN
  SELECT SEQ_ID.NEXTVAL INTO :NEW.BO_ID
  FROM DUAL;
END;

5、修改借书功能的存储过程。

该存储过程要求：
（1）借书时输入借书证号，图书编号。（即该函数有2个输入参数）
（2）借书时，借书日期为系统时间。
*该存储过程主体部分只有insert into语句。

CREATE OR REPLACE PROCEDURE LEND_BOOK
(
       V_R_NUMBER IN CHAR,
       V_B_ID IN CHAR
)
AS V_BORROW BOOK.B_STATE%TYPE;
BEGIN
  SELECT B_STATE INTO V_BORROW FROM BOOK WHERE BOOK.B_ID = V_B_ID;
  IF V_BORROW = '否' THEN
    INSERT INTO BORROWING(R_NUMBER,B_ID,LENDDATE) 					    VALUES(V_R_NUMBER,V_B_ID,SYSDATE);
    UPDATE BOOK SET B_STATE = '是';
   		COMMIT;
  	ELSE
    		DBMS_OUTPUT.put_line('该书已被借出!');
    END IF;
END;

6、建立与借书存储过程相对应的触发器，当借阅表中加入借阅信息时，该触发器触发，自动修改所借图书的是否借出改为‘是’。

CREATE OR REPLACE TRIGGER TRI_BORROW_INSERT
AFTER INSERT ON BORROWING
FOR EACH ROW
BEGIN
  UPDATE BOOK SET B_STATE = '是'
  WHERE B_ID = :NEW.B_ID;
END;

测试：
INSERT INTO BORROWING VALUES(7,20051001,2001231,sysdate,null,null,null);
commit;

插入数据后：