Question: I am not getting the input for my code, the procedure compiled but i am not getting any result what I supposed to get, Please
I am not getting the input for my code, the procedure compiled but i am not getting any result what I supposed to get, Please take a look at my code and fix it Thank you,
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE generatejobalerts
vjobid IN NUMBER,
vtimestamp IN TIMESTAMP
AS
Cursor to find the job post and check if it is active
CURSOR curjobpost IS
SELECT mindegree, minyearsworkexp, jobid
FROM jobpost
WHERE jobid vjobid AND jstatus ;
Cursor to find job seekers who meet the degree and work experience requirements
CURSOR curjobseekers
pdegree NUMBER,
pexperience NUMBER
IS
SELECT jsaccid, atusername
FROM jsaccount js
JOIN accountt at ON jsaccid ataccid
WHERE degree pdegree AND numyrswrkxp pexperience;
Variables to hold job requirements
vmindegree NUMBER;
vminyearsworkexp NUMBER;
vjobpostid NUMBER;
Variables for checking skills
vskillcount NUMBER;
flag INT;
BEGIN
OPEN curjobpost;
FETCH curjobpost INTO vmindegree, vminyearsworkexp, vjobpostid;
IF curjobpostNOTFOUND THEN
DBMSOUTPUT.PUTLINEInvalid job post ID;
CLOSE curjobpost;
RETURN;
END IF;
Loop through all qualified job seekers
FOR rjobseeker IN curjobseekersvmindegree, vminyearsworkexp LOOP
flag :;
Check each required skill and level
FOR rskill IN
SELECT jsskid, jssklvl
FROM jobskills js
WHERE jsjobid vjobpostid
LOOP
SELECT COUNT
INTO vskillcount
FROM appskills aps
WHERE aps.accid rjobseeker.accid AND aps.skid rskill.skid AND aps.sklvl rskill.sklvl;
IF vskillcount THEN
flag :;
EXIT;
END IF;
END LOOP;
IF flag THEN
If all conditions are met, print and insert into message table
DBMSOUTPUT.PUTLINEA job post vjobpostid is available and you are qualified to apply';
INSERT INTO message mid appid, mtime, mbody
VALUES messageseq.nextval, rjobseeker.accid, vtimestamp, 'A job post vjobpostid is available and you are qualified to apply';
END IF;
END LOOP;
CLOSE curjobpost;
END;
BEGIN
generatejobalertsvjobid vtimestamp SYSTIMESTAMP;
END;generatejobalertsvjobid vtimestamp SYSTIMESTAMP;
END;
Script Output
Task completed in seconds
Procedure GENERATEJOBALERTS compiled
Invalid job post ID
PLSQL procedure successfully completed.
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help