Cool SQL

DECLARE
CURSOR CS_STRUC IS
SELECT COURSE_ID,SECTION,TERM,STD_TYPE,NEW_HDO_CIT FROM TTMP_HDO165 ;

str_course_id TTMP_HDO165.NEW_HDO_CIT%TYPE;
str_section TTMP_HDO165.NEW_HDO_CIT%TYPE;
str_term TTMP_HDO165.NEW_HDO_CIT%TYPE;
str_std_type TTMP_HDO165.NEW_HDO_CIT%TYPE;
str_new_hdo_cit TTMP_HDO165.NEW_HDO_CIT%TYPE;

BEGIN
OPEN CS_STRUC ; LOOP FETCH CS_STRUC INTO str_course_id,str_section,str_term,str_std_type,str_new_hdo_cit;
IF CS_STRUC % FOUND THEN
UPDATE COURSE_OPEN
SET HDO_CIT_ID = str_new_hdo_cit
WHERE
COURSE_ID = str_course_id
AND SECTION = str_section
AND TERM = str_term
AND STD_TYPE = str_std_type ;
ELSE
EXIT ;
END
IF ;
END LOOP ; COMMIT ; CLOSE CS_STRUC ;
END ;/

SQL เพื่อจัดการข้อมูลการลงรายวิชาซ้ำ

CREATE OR REPLACE VIEW TTMP_RESERVE_DUP265
as
SELECT
COUNT(*) AS C,
STUDENT_ID,
STD_TYPE,
TERM,
FUNC_FIND_TIME_T1_VAL(COURSE_ID, TERM, SECTION,STD_TYPE ) as FF
FROM
RESERVE_SUBJECT_FREE
WHERE
STD_TYPE = '1'
AND TERM = '2/65'
AND STATUS_R '3'
AND FUNC_FIND_TIME_T1_VAL(COURSE_ID, TERM, SECTION, STD_TYPE ) IS NOT NULL
AND STUDENT_ID IS NOT NULL
GROUP BY
STUDENT_ID,
SECTION,
STD_TYPE,
FUNC_FIND_TIME_T1_VAL ( COURSE_ID, TERM, SECTION, STD_TYPE ) ,
TERM
HAVING
COUNT( * ) > 1
ORDER BY STUDENT_ID


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE VIEW V_STUDENT_RESERVE_DUP
as
SELECT
AA.STUDENT_ID,
STD.NAME,
STD.PROGRAM_NAME_TH,
STD.LEV_NAME,
AA.COURSE_ID,
AA.SECTION,
AA.TERM,
AA.CREDIT,
AA.NAME_T,
AA.SJGROUP,
AA.VARNAM,
AA.LEC_TIME,
CC.PRENM||CC.PONAME as NAME_MENTER,
CC.FAC_NAME_TH
FROM
V_RESERVE_SUBJECT_GE_FREE AA
INNER JOIN TTMP_RESERVE_DUP265 BB
ON AA.STUDENT_ID = BB.STUDENT_ID AND AA.TIME_T = BB.FF
LEFT JOIN CURR_V_MENTER CC ON CC.STUDENT_GROUP_ID = SUBSTR(AA.STUDENT_ID,1,9)
INNER JOIN V_STUDENT_ALL STD ON STD.STUDENT_ID = AA.STUDENT_ID
WHERE
AA.TERM = '2/65' AND AA.STATUS_R '3'
ORDER BY AA.STUDENT_ID

ตัวอย่างการใช้งานคำสั่ง SQL เพื่อหาข้อมูลซ้ำก่อนที่จะลบ