I have scoured the internet with no luck. I have attached links to the practice set I am working on, but I am creating a stored procedure that needs to update a row after checking if a foreign key from the one table matches with a corresponding primary key in another table. I'll attach my code so far (which isn't much), but I am honestly lost. I know how to create foreign key constraints like:
ALTER TABLE DRIVE
ADD CONSTRAINT TRUCK_NUM_FK FOREIGN KEY (TRUCK_NUM) REFERENCES TRUCK;
But I don't know how to do these things and more from a stored procedure. Thank you!
Practice Description: http://tinypic.com/r/2djxq4w/8 http://tinypic.com/r/sq61i1/8
CREATE OR REPLACE PROCEDURE TRUCK_RETURN (TR_NUM IN NUMBER,TR_MILE IN NUMBER) AS
BEGIN
IF
UPDATE
This may not be exact but hopefully it will get you close:
CREATE OR REPLACE PROCEDURE TRUCK_RETURN (TR_NUM IN NUMBER,TR_MILE IN NUMBER) AS
BEGIN
IF TR_NUM IS NULL THEN
Dbms_Output.PUT_LINE('Error: No truck number supplied.');
ELSE
DECLARE TR NUMBER(10);
SELECT TRUCK_NUM
INTO TR
FROM TRUCK
WHERE TR_NUM = TRUCK_NUM;
IF TR IS NOT NULL AND TR = TR_NUM THEN
SELECT Truck_Mileage
INTO TR
FROM Truck
WHERE Truck_Num = TR_NUM;
IF TR_MILE >= TR THEN
SELECT COUNT(*)
INTO TR
FROM DRIVE
WHERE Truck_Num = TR_NUM
AND Drive_Status = ‘OUT’;
IF TR = 1 THEN
UPDATE Drive
SET Drive_Status = ‘Received’
WHERE Truck_Num = TR_NUM
AND Drive_Status = ‘OUT’;
ELSE
Dbms_Output.PUT_LINE('Error: Truck has too many or not record of being out.’);
END IF;
ELSE
Dbms_Output.PUT_LINE('Error: Truck mileage is less than previously recorded.’);
END IF;
ELSE
Dbms_Output.PUT_LINE('Error: Truck Number is incorrect.’);
END IF;
END IF;
END TRUCK_RETURN;