Library Book Management Using PL/SQL Collections, Records, and GOTO Statements
- Introduction
In modern database systems, data management efficiency is crucial. PL/SQL, Oracle’s procedural language extension for SQL, allows developers to use advanced features such as Collections, Records, and GOTO statements for efficient data handling and control flow.
This project demonstrates how to apply these PL/SQL features through a simple Library Book Management System, where books are retrieved, processed, and filtered using programmatic logic.
- Objectives
The main objectives of this project are:
To demonstrate the use of PL/SQL Records to store related data as a single logical unit.
To show how Collections (specifically Nested Tables) can store and manipulate multiple records in memory.
To illustrate the use of GOTO statements to manage control flow (e.g., skipping specific data).
To integrate SQL and PL/SQL programming techniques in a practical database problem.
-
Tools and Environment Used Tool / Software Description Oracle Database 21c Database environment used to create and manage tables and PL/SQL code. SQL*Plus / SQL Developer Interface used to execute PL/SQL programs and view results. Windows 11 Operating system environment. DBMS_OUTPUT Package Used to display results from the PL/SQL program.
-
Problem Definition
Design a Library Book Management System that can:
Store books and their details in a table.
Use PL/SQL Collections and Records to temporarily handle these books in memory.
Use a GOTO statement to skip books that are not available for borrowing.
- System Design 5.1 Database Table Creation
CREATE TABLE library_books ( book_id NUMBER PRIMARY KEY, book_title VARCHAR2(100), author VARCHAR2(50), category VARCHAR2(30), available CHAR(1) -- 'Y' = Available, 'N' = Borrowed );
5.2 Data Insertion INSERT INTO library_books VALUES (1, 'Database Systems', 'Navathe', 'Education', 'Y'); INSERT INTO library_books VALUES (2, 'Networking Basics', 'Tanenbaum', 'Technology', 'Y'); INSERT INTO library_books VALUES (3, 'Harry Potter', 'J.K. Rowling', 'Fiction', 'N'); INSERT INTO library_books VALUES (4, 'AI Revolution', 'Andrew Ng', 'Technology', 'Y'); INSERT INTO library_books VALUES (5, 'Story of Life', 'John Doe', 'Biography', 'N');
COMMIT;
CREATE OR REPLACE PROCEDURE manage_library_system IS -- Record type for book details TYPE book_rec_type IS RECORD ( book_id library_books.book_id%TYPE, book_title library_books.book_title%TYPE, author library_books.author%TYPE, category library_books.category%TYPE, available library_books.available%TYPE );
-- Collection (nested table) of records
TYPE book_table_type IS TABLE OF book_rec_type;
book_table book_table_type;
-- Cursor for fetching all books
CURSOR book_cursor IS
SELECT book_id, book_title, author, category, available FROM library_books;
-- Local variables
v_count NUMBER;
-- New book details to insert (example)
v_new_book_id NUMBER := 6;
v_new_book_title VARCHAR2(100) := 'Data Science Fundamentals';
v_new_book_author VARCHAR2(50) := 'Andrew Ng';
v_new_book_cat VARCHAR2(30) := 'Education';
v_new_book_avail CHAR(1) := 'Y';
BEGIN ---------------------------------------------------------------- -- 1️ INSERT NEW BOOK SECTION ---------------------------------------------------------------- SELECT COUNT(*) INTO v_count FROM library_books WHERE book_id = v_new_book_id;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Book with ID ' || v_new_book_id || ' already exists.');
GOTO skip_insert;
END IF;
INSERT INTO library_books (book_id, book_title, author, category, available)
VALUES (v_new_book_id, v_new_book_title, v_new_book_author, v_new_book_cat, v_new_book_avail);
COMMIT;
DBMS_OUTPUT.PUT_LINE('New Book "' || v_new_book_title || '" inserted successfully.');
<<skip_insert>>
NULL; -- skip duplicate insert if needed
----------------------------------------------------------------
-- 2️ DISPLAY AVAILABLE BOOKS SECTION
----------------------------------------------------------------
OPEN book_cursor;
FETCH book_cursor BULK COLLECT INTO book_table;
CLOSE book_cursor;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('===== AVAILABLE BOOKS =====');
FOR i IN 1 .. book_table.COUNT LOOP
-- Skip unavailable books
IF book_table(i).available = 'N' THEN
GOTO skip_book;
END IF;
-- Display book info
DBMS_OUTPUT.PUT_LINE('Book ID: ' || book_table(i).book_id);
DBMS_OUTPUT.PUT_LINE('Title: ' || book_table(i).book_title);
DBMS_OUTPUT.PUT_LINE('Author: ' || book_table(i).author);
DBMS_OUTPUT.PUT_LINE('Category:' || book_table(i).category);
DBMS_OUTPUT.PUT_LINE('-----------------------------');
<<skip_book>>
NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('===== END OF AVAILABLE BOOKS =====');
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END; /
5.4 Procedure Execution SET SERVEROUTPUT ON; EXEC manage_library;
- Discussion
This project successfully demonstrates:
Records for grouping related fields (Book details).
Collections for managing multiple records in memory.
GOTO statements for skipping unavailable records dynamically.
The use of BULK COLLECT for efficient data fetching.
These features improve program readability and performance when processing multiple database records.
- Conclusion
This project highlights the integration of procedural programming features in Oracle’s PL/SQL environment. By combining Collections, Records, and GOTO statements, we efficiently manage a library database while controlling program flow logically. Such approaches are valuable for building scalable and maintainable database applications in real-world systems.
- References
Oracle® Database PL/SQL Language Reference.
Navathe & Elmasri – Fundamentals of Database Systems.
Oracle Live SQL Documentation.