Wasze wskazówki średnio pomogły.
CREATE OR REPLACE PROCEDURE show_reservations IS
CURSOR c_reservation IS SELECT r.reservation_id,
r.reader_id,
r.document_id r_document,
r.date_reservation,
r.date_expiration,
r.active,
r.quantity_documents,
ad.document_id ad_document,
ad.author_id ad_author,
d.document_id,
d.title,
d.isbn,
a.firstname a_firstname,
a.surname a_surname,
a.author_id,
re.firstname,
re.surname ,
re.reader_id re_reader
FROM
documents d,
authors a,
reservations r,
readers re,
author_document ad
WHERE
a.author_id = ad_author AND
r_document = d.document_id AND
r_document = ad_document AND
ad_document = d.document_id AND
r.reader_id = re_reader;
r_reservation c_reservation%rowtype;
BEGIN
htp.htmlOpen;
htp.headOpen;
htp.title('SYSTEM BIBLIOTECZNY - rezerwacje');
htp.headClose;
htp.bodyOpen;
htp.p('LISTA REZERWACJI');
htp.p('<TABLE BORDER=2>');
htp.p('<TR><TH>ID rezerwacji</TH><TH>ID czytelnika</TH><TH>Czytelnik</TH><TH>ID dokumentu</TH><TH>Autor</TH><TH>Tytuł</TH>
<TH>ISBN</TH><TH>Data rezerwacji</TH><TH>Termin ważności</TH> <TH>Aktywność</TH><TH>Ilość sztuk</TH><TH>Szczegóły</TH></TR>');
FOR r_reservation IN c_reservation LOOP
htp.p('<TD>'||r_reservation.r.reservation_id ||'</TD>');
htp.p('<TD>'||r_reservation.re_reader ||'</TD>');
htp.p('<TD>'||r_reservation.re.firstname || ' ' || r_reservation.re.surname ||'</TD>');
htp.p('<TD>'||r_reservation.d.document_id ||'</TD>');
htp.p('<TD>'||r_reservation.a_firstname || ' ' ||r_reservation.a_surname||'</TD>');
htp.p('<TD>'||r_reservation.d.title ||'</TD>');
htp.p('<TD>'||r_reservation.d.isbn||'</TD>');
htp.p('<TD>'||r_reservation.r.date_reservation||'</TD>');
htp.p('<TD>'||r_reservation.r.date_expiration||'</TD>');
htp.p('<TD>'||r_reservation.r.active||'</TD>');
htp.p('<TD>'||r_reservation.r.quantity_documents||'</TD>');
htp.p('<TD> <a href="show_document?id='||r_reservation.d.document_id||'">Szczegóły</a> </TD>');
END LOOP;
htp.p('</TABLE>');
htp.bodyClose;
htp.htmlClose;
END show_reservations;
/
CREATE TABLE readers
(
reader_id number CONSTRAINT reader_pk PRIMARY KEY CONSTRAINT c_reader NOT NULL,
password varchar2(30),
firstname varchar2(50),
surname varchar2(50),
email varchar2(50),
date_create date,
ID_number varchar2(11)
);
CREATE TABLE authors
(
author_id number(10) CONSTRAINT author_pk PRIMARY KEY CONSTRAINT c_author NOT NULL,
country_id number(10) CONSTRAINT a_country_fk REFERENCES countries(country_id),
firstname varchar2(50),
surname varchar2(50)
);
CREATE TABLE documents
(
document_id NUMBER(10) CONSTRAINT document_pk PRIMARY KEY CONSTRAINT c_document NOT NULL,
category_id NUMBER(10) CONSTRAINT d_category_fk REFERENCES categories(category_id),
type_document_id NUMBER(2) CONSTRAINT d_type_document_fk REFERENCES type_documents(type_document_id),
publisher_id NUMBER(10) CONSTRAINT d_publisher_fk REFERENCES publishers(publisher_id),
title VARCHAR2(200),
isbn VARCHAR2(13),
pages VARCHAR2(10),
release_date DATE,
description VARCHAR2(4000),
quantity NUMBER(5),
date_create DATE,
date_edit DATE,
state_of_book CHAR(1)
);
CREATE TABLE reservations
(
reservation_id NUMBER(10) CONSTRAINT reservation_pk PRIMARY KEY CONSTRAINT c_reservation NOT NULL,
reader_id NUMBER(10) CONSTRAINT re_reader_fk REFERENCES readers(reader_id),
document_id NUMBER(10) CONSTRAINT re_document_fk REFERENCES documents(document_id),
date_reservation DATE,
date_expiration DATE,
active CHAR(1),
quantity_documents NUMBER(20)
);
CREATE TABLE author_document
(
author_document_id NUMBER(2) CONSTRAINT author_document_pk PRIMARY KEY CONSTRAINT c_author_document NOT NULL,
author_id NUMBER(10) CONSTRAINT ad_author_fk REFERENCES authors(author_id),
document_id NUMBER(10) CONSTRAINT ad_document_fk REFERENCES documents(document_id)
);
LINE/COL ERROR
2/8 PLS-00341: declaration of cursor 'C_RESERVATION' is incomplete or
malformed
2/25 PL/SQL: SQL Statement ignored
31/15 PL/SQL: ORA-00904: "RE_READER": invalid identifier
32/15 PL/SQL: Item ignored
44/1 PL/SQL: Statement ignored
44/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
45/1 PL/SQL: Statement ignored
45/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
46/13 PL/SQL: Statement ignored
LINE/COL ERROR
46/27 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
47/1 PL/SQL: Statement ignored
47/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
48/1 PL/SQL: Statement ignored
48/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
49/1 PL/SQL: Statement ignored
49/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
50/1 PL/SQL: Statement ignored
50/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid
51/1 PL/SQL: Statement ignored
51/15 PLS-00364: loop index variable 'R_RESERVATION' use is invalid>