Use a variable to write a procedure called AddNewAlbum . This procedure takes at
ID: 665967 • Letter: U
Question
Use a variable to write a procedure called AddNewAlbum. This procedure takes at least two parameters:- NameOfArtist and AlbumName. (If YearReleased, Rating, or NumberOfSongs is NOT NULL in your Albums table you may need to include parameters for the NOT NULL columns as well.) The procedure will:
Look up ArtistID from the Artist table, where artist name is NameOfArtist
Insert a new row into Albums, using the ArtistID found in step #1 and the AlbumName parameter
For now, it is safe to assume the artist exists before you run this procedure (meaning if you CALL AddNewAlbum ('Bob Dylan', 'Street Legal');, then "Bob Dylan" already exists in the Artist table.
Explanation / Answer
Procedure in PL/SQL:
//creating a procedure
CREATE PROCEDURE AddNewAlbum (
NameOfArtist varchar(50),
AlbumName varchar(50)
)
//starting a procedure
BEGIN
//declaring a default value for artist_id
DECLARE artist_id INT DEFAULT 0;
// Obtain ArtistID from Artist table
SELECT ArtistID INTO artist_id
FROM Artist
WHERE ArtistName = NameOfArtist;
// Insert a new row into Albums
INSERT INTO Albums (ArtistID, Title)
VALUES (artist_id, AlbumName);
END;
// procedure call to run the function
CALL AddNewAlbum (
"Bob Dylan",
"Street Legal"
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.