Please try this piece of code:
DECLARE
newID DD_DONOR.IDDONOR%TYPE;
oldID DD_DONOR.IDDONOR%TYPE;
fname DD_DONOR.FIRSTNAME%TYPE;
lname DD_DONOR.LASTNAME%TYPE;
BEGIN
newID := 305;
fname := 'Thomas';
lname := 'Sheer';
select
iddonor
into
oldId
from
dd_donor
WHERE iddonor = newId;
dbms_output.put_line('This ID is already assigned');
exception
when NO_DATA_FOUND then
UPDATE DD_DONOR
SET IDDONOR = newID
WHERE FIRSTNAME = fname AND LASTNAME = lname;
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID);
END;
/
If you want to stick to your approach with cursor here is the changes to your initial script:
Changes are in :
all_ids DD_DONOR.IDDONOR%TYPE – no need of CLOB as you expect only one record with this ID.
Declaration and Call of cursor – parameter added so you are able to search for a specific id
DECLARE
all_ids DD_DONOR.IDDONOR%TYPE;
newID DD_DONOR.IDDONOR%TYPE;
fname DD_DONOR.FIRSTNAME%TYPE;
lname DD_DONOR.LASTNAME%TYPE;
CURSOR id(newID in DD_DONOR.IDDONOR%TYPE) IS
SELECT IDDONOR FROM DD_DONOR where iddonor = newID;
BEGIN
newID := 305;
fname := 'Thomas';
lname := 'Sheer';
OPEN id(newId);
FETCH id INTO all_ids;
IF id%NOTFOUND THEN
UPDATE DD_DONOR
SET IDDONOR = newID
WHERE FIRSTNAME = fname AND LASTNAME = lname;
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID);
ELSIF id%FOUND THEN
DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
END IF;
CLOSE id;
END;
/
Thanks
CLICK HERE to find out more related problems solutions.