Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Databases questions

Question

design a simple disciplinary and grievance procedure.

Answered: 1 week ago