Question
can you please convert this oracle code to t-sql declare -- find students with at least one course marked to be deleted -- but still
can you please convert this oracle code to t-sql
declare
-- find students with at least one course marked to be deleted
-- but still residing in the stu_acadrec table.
cursor curs_students is
select distinct stu_mast_no
from stu_acadrec
where EVALFLG = 'D'
and SOURCE_ID = '001775';
my_stuno stu_master.stuno%TYPE := ' ';
resultCount number;
begin
-- for each student that has a course to be deleted...
for student in curs_students loop
-- selecting the stuno for display purposes only.
-- would be more efficient to do this as part of the
-- cursor definition, but this is good enough.
select stuno into my_stuno
from stu_master where INT_SEQ_NO = student.stu_mast_no;
dbms_output.put_line('Processing stuno = ' || my_stuno || ', int_seq_no = ' || student.stu_mast_no);
-- remove many-to-many association in stu_egrp_courses
-- when the course no longer exists in stu_acadrec
delete from stu_egrp_courses ec
where ec.STU_MAST_NO = my_stuMastNo
and ec.evalgrp in
(select evalgrp
from stu_egrp_courses
where STU_MAST_NO = my_stuMastNo
and SOURCE_ID || source_cd || cdpmask || crs_yt || crs_num || crs_seq not in
(select SOURCE_ID || source_cd || cdpmask || crs_yt || crs_num || crs_seq
from stu_acadrec
where STU_MAST_NO = my_stuMastNo
and evalflg != 'D')
);
resultCount := sql%rowcount;
dbms_output.put_line(' ' || resultCount || ' rows deleted from stu_egrp_courses');
-- remove empty evaluation groups from stu_evalgrp
-- (empty = no courses found in stu_egrp_courses)
delete
from stu_evalgrp e
where STU_MAST_NO = student.stu_mast_no
and e.evalgrp not in
(select distinct ec.EVALGRP from stu_egrp_courses ec
where ec.stu_mast_no = e.STU_MAST_NO
);
resultCount := sql%rowcount;
dbms_output.put_line(' ' || resultCount || ' rows deleted from stu_evalgrp');
-- delete the courses from stu_acadrec that were previously
-- marked for deletion but never removed from the table.
delete from stu_acadrec a
where a. STU_MAST_NO = student.stu_mast_no
and a.EVALFLG = 'D';
resultCount := sql%rowcount;
dbms_output.put_line(' ' || resultCount || ' rows deleted from stu_acadrec');
-- next student
end loop;
end;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started