Task – PL/SQL implementation:
Before you begin please refer to the appendix for initial setup of the database.
1. Populate the tables by writing a procedure that inserts a new record into the database. (The records shown in the appendix can be used). The procedure should:
Check for reasonable inputs
Put a new record in the "books" table
Put a corresponding new record in the "book_copies" table
2. Write a procedure that retrieves a book count.
3. Write a procedure getBookDetails which accepts an isbn number and returns the books title, author, date_published, and the number of copies. The main block should call the procedure with a isbn number and output the book’s details
4. Write a PL/SQL block which utilises the getBookdetails procedure and prints the data for each record.
5. Write a procedure that deletes a book and all copies from the database.
6. Write a trigger that that reports how many book copies are present after any insert/update/delete operation.
The SQL to create these tables:
CREATE TABLE books (
isbn VARCHAR2(13) NOT NULL PRIMARY KEY,
title VARCHAR2(200),
summary VARCHAR2(2000),
author VARCHAR2(200),
date_published DATE,
page_count NUMBER
);
CREATE TABLE book_copies(
barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY,
isbn VARCHAR2(13) REFERENCES books (isbn)
);How does CodersArts helps you ?
CodersArts provide :
PL/SQL assignment Help
Help in development Projects
Mentorship from Experts Live 1:1 session
Course and Project completions
CourseWork help