I am not sure how to resolve the above error I am getting. How should I declare it? I created the procedure below to allow a passenger to buy a metro card and add it to an existing account. Input includes account id, initial balance
SET SERVEROUTPUT ON
-- procedure to buy new metro, output error if account already exists.
CREATE OR REPLACE PROCEDURE NewCard (
input_Account_ID IN varchar,
input_Balance IN varchar,
input_age IN varchar
)
IS
acct varchar(255);
-- compare the input account id to see if it exists in the table
CURSOR S1 is SELECT Account_id from Rider_Account where ACCOUNT_ID = input_Account_ID;
BEGIN
--THIS MEANS THE RECORD EXISTS
open S1;
LOOP
FETCH S1 into acct;
EXIT WHEN S1%notfound;
END LOOP;
-- go through the records
IF (acct = input_Account_ID) THEN
Dbms_ouput.Put_line('Account exists');
ELSE
BEGIN
INSERT into Metro_Card(Card_ID, Account_ID, Balance) VALUES(Card_Sequence.NEXTVAL,input_Account_ID, input_Balance);
INSERT into rider_account(Age) VALUES (input_age);
END;
END IF;
IF (input_age <= 12) THEN
UPDATE Metro_Card
SET Discount_type = 2
Where Metro_Card.Account_ID = input_Account_ID;
ELSIF (input_age >= 65) THEN
UPDATE Metro_Card
SET Discount_type = 3
Where Metro_Card.Account_ID = input_Account_ID;
ELSE
UPDATE Metro_Card
SET Discount_type = 1
Where Metro_Card.Account_ID = input_Account_ID;
END IF;
END;
