我有两张桌子
书:
CREATE TABLE Book
(
book_id INTEGER NOT NULL ,
shelf_letter CHAR (1) NOT NULL ,
call_number INTEGER ,
no_of_copies INTEGER NOT NULL ,
CONSTRAINT isbn_unique UNIQUE (isbn),
) ;
份数:
CREATE TABLE Copies
(
copy_id INTEGER NOT NULL ,
book_id INTEGER NOT NULL ,
copy_number INTEGER NOT NULL,
constraint copy_number_unique unique(book_id,copy_number)
) ;
因此,一本书的所有副本的副本表条目(Book.no_of_copies为5,那么副本表中有5副本(行))
如何编写可以将输入参数作为book_id并首先查询Book表来查找no_of_copies的过程。如果no_of_copies为正,则查询“份数”表并显示每个结果的copy_number,shelf_letter和call_number。
CREATE PROCEDURE (P_BOOK_ID INTEGER)
CURSOR C1(L_BOOK_ID INTEGER) IS
SELECT * FROM COPIES WHERE BOOK_ID = L_BOOK_ID;
L_NUM_COPIES NUMBER;
BEGIN
SELECT NO_OF_COPIES INTO L_NUM_COPIES FROM BOOK WHERE BOOK_ID = P_BOOK_ID;
IF L_NUM_COPIES>0
THEN
FOR CUR IN C1(P_BOOK_ID)
LOOP
DBMS_OUTPUT.PUT_LINE(CUR.COPY_NUMBER);
END LOOP;
END;
或者
CREATE PROCEDURE (P_BOOK_ID INTEGER)
CURSOR C1(L_BOOK_ID INTEGER) IS
SELECT B.book_id,
B.shelf_letter,
B.call_number,
B.no_of_copies,
C.copy_id,
C.copy_number
FROM COPIES C,
BOOK B
WHERE C.BOOK_ID = L_BOOK_ID
AND C.BOOK_ID=B.BOOK_ID;
L_NUM_COPIES NUMBER;
BEGIN
FOR CUR IN C1(P_BOOK_ID)
LOOP
DBMS_OUTPUT.PUT_LINE(CUR.book_id);
DBMS_OUTPUT.PUT_LINE(CUR.shelf_letter);
DBMS_OUTPUT.PUT_LINE(CUR.call_number);
DBMS_OUTPUT.PUT_LINE(CUR.no_of_copies);
DBMS_OUTPUT.PUT_LINE(CUR.copy_id);
DBMS_OUTPUT.PUT_LINE(CUR.copy_number);
END LOOP;
END;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句