Hey guys , i have a database which consist of 4 different databases.
1) Book ( which contains variables p#).
2) UserChoice ( contains variables p#).
4)Keyword ( contains variables p# and kword)
What i am trying to achieve is to implement a pl/sql procedure (kword) that finds all the products determined by the kword and then store the p# of that product into UserChoice. what the script does is to first find out which p# in the Keyword has that kword which the user inputs and then storing it. The script will then search in the table Book to see if it exist there. if it does , it will then write the p# into the UserChoice table.
Here is my sql script so far.
CREATE OR REPLACE PROCEDURE T1(
key_keyword IN VARCHAR) IS
book_row BOOK%ROWTYPE;
keyword_p# KEYWORD.P#%TYPE;
Fail EXCEPTION;
BEGIN
SELECT P#
INTO keyword_p#
FROM KEYWORD
WHERE KWORD = key_keyword ;
BEGIN
SELECT *
INTO book_row
FROM BOOK
WHERE P# = keyword_p#;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(
'p# does not exist in BOOK Table! Trying VIDEO table !');
END;
INSERT INTO USERCHOICE (P# ) SELECT P# FROM BOOK WHERE BOOK.P# = keyword_p#;
END T1;
/
My codes work well when there is a single unique row for kword all the time. For example.
Keyword Table
p# kword
1 GARDENING
2 ACTION
3 MORTAL
For example, if p# (1) is also inside Book , so when i execute my procedure EXECUTE T1('GARDENING') , the p# will be stored in my UserChoice.
But , when the table has a duplicate row for kword like this....
Keyword Table
p# kword
1 GARDENING
2 ACTION
3 GARDENING
The problem now is when i run my codes , i will get an error as the p# which they send back has dual values as 1 , 3 both fulfill the criteria of my first select statement. How can i modify my codes so it can store both p#(1) and p#(3) into my UserChoice table