쿼리들

 

 

ㅁ *-mapper.xml에 쿼리 쓸 때 끝에 ; 없어야 한다.

 

 

 

ㅁ 2_1. 의료진 계정 검색 요청 (이름)

 

(1) 넘기는 값

- 의사명

 

(2) 가져와야 하는 값

- DOCTOR: 사번, 이름,  면허번호, 진료과, 입사일

- MEMBER : 주민등록번호,  전화번호, 주소

 

(3) 쿼리

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DOCTOR_NAME=?
;

 

(4) 쿼리 예시

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DOCTOR_NAME='박시우'
;

 

 

ㅁ 2_2. 의료진 계정 검색 요청 (사번)

 

(1) 넘기는 값

- 사번

 

(2) 가져와야 하는 값

- DOCTOR: 사번, 이름,  면허번호, 진료과, 입사일

- MEMBER : 주민등록번호,  전화번호, 주소

 

(3) 쿼리

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DOCTOR_NO=?
;

 

(4) 쿼리 예시

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DOCTOR_NO='vp1'
;

 

 

 

ㅁ 2_3. 의료진 계정 검색 요청 (진료과)

 

(1) 넘기는 값

- 진료과

 

(2) 가져와야 하는 값

- DOCTOR: 사번, 이름,  면허번호, 진료과, 입사일

- MEMBER : 주민등록번호,  전화번호, 주소

 

(3) 쿼리

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DEPT_NAME=?
;

 

(4) 쿼리 예시

 

SELECT DOCTOR_NO, DOCTOR_NAME, LICENCE_NO, DEPT_NAME, HIRE_DATE, USER_SSN, PHONE, ADDRESS
FROM DOCTOR
JOIN MEMBER USING (USER_NO)
WHERE DEPT_NAME='내과'
;

 

 

 

ㅁ 2_4. 의료진 계정 추가 요청

 

(1) 넘기는 값

- MEMBER : 이름, 주민등록번호, 아이디, 비밀번호, 전화번호, 주소, 이메일

- DOCTOR : 이름, 면허번호, 진료과

- SCHEDULE 

 

 

(2) 쿼리

 

INSERT INTO MEMBER (USER_NO, USER_ID, USER_PWD, USER_NAME, PHONE, EMAIL, ADDRESS, 

                                          ENROLL_DATE, USER_TYPE, USER_SSN, CALLBACK_YN, RESIGN_YN, USER_DATE,                                                USER_GENDER, REPRESENTATIVE)
VALUES (TO_CHAR(SYSDATE, 'YYMMDD') || LPAD(SEQ_UNO.NEXTVAL, 3, '0'),
               ?, ?, ?, ?, ?, ?, SYSDATE, 'D', ?, 'N', 'N', ?, ?, NULL
               )
;

 

INSERT INTO DOCTOR (DOCTOR_NO, USER_NO, HIRE_DATE, DOCTOR_NAME, LICENCE_NO, DEPT_NAME)
VALUES ('vp' || SEQ_DON.NEXTVAL, 
            (SELECT USER_NO
             FROM MEMBER
             WHERE USER_SSN=?,
        SYSDATE, ?, ?, ?)
;

 

 

- member 테이블에 먼저 insert하고, 회원번호를 받아서 doctor 테이블에 insert하고, 사번을 받아서 schedule 테이블에 insert 한다.

- 생년월일과 성별은 substr로 주민등록번호에서 추출한다.

 

 

 

(3) 쿼리 예시

 

INSERT INTO MEMBER (USER_NO, USER_ID, USER_PWD, USER_NAME, PHONE, EMAIL, ADDRESS, 

                                          ENROLL_DATE, USER_TYPE, USER_SSN, CALLBACK_YN, RESIGN_YN, USER_DATE,                                                USER_GENDER, REPRESENTATIVE)
VALUES (TO_CHAR(SYSDATE, 'YYMMDD') || LPAD(SEQ_UNO.NEXTVAL, 3, '0'),
               'test99', 'test99', '히딩크', '010-2958-2851', 'asdf@gmail.com', '노숙자', 
               SYSDATE, 'D', '123456-1234777', 'N', 'N', '123456', 'M', NULL
               )
;

INSERT INTO DOCTOR (DOCTOR_NO, USER_NO, HIRE_DATE, DOCTOR_NAME, LICENCE_NO, DEPT_NAME)
VALUES ('vp' || SEQ_DON.NEXTVAL, 
            (SELECT USER_NO
             FROM MEMBER
             WHERE USER_SSN='123456-1234777'),
        SYSDATE, '히딩크', 23999, '내과')
;

 

 

 

 

ㅁ 2_5. 의료진 계정 수정 요청

 

 

(1) 넘기는 값

- MEMBER : 이름, 주민등록번호, 아이디, 비밀번호, 전화번호, 주소, 이메일

- DOCTOR : 사번, 면허번호, 진료과

 

(2) 가져와야하는 값

- MEMBER : 이름, 주민등록번호, 아이디, 비밀번호, 전화번호, 주소, 이메일

- DOCTOR : 사번, 면허번호, 진료과

 

 

(3) 쿼리

 

UPDATE MEMBER
SET USER_NAME=?
, USER_SSN=?
, USER_DATE=?
, USER_ID=?
, USER_PWD=?
, PHONE=?
, ADDRESS=?
, EMAIL=?
WHERE USER_ID=?
;


UPDATE DOCTOR
SET DOCTOR_NO=?
, LICENCE_NO=?
, DEPT_NAME=?
WHERE LICENCE_NO=?
;

 

 

 

(4) 쿼리 예시

 

 

UPDATE MEMBER
SET USER_NAME='김은비'
, USER_SSN='122136-4987654'
, USER_DATE='122136'
, USER_ID='AWB32'
, USER_PWD='AWB32'
, PHONE='010-4655-9752'
, ADDRESS='강남구 동학로 32길'
, EMAIL='ASD@naver.com'
WHERE USER_ID='test99'
;


UPDATE DOCTOR
SET DOCTOR_NO='vp10'
, LICENCE_NO=25111
, DEPT_NAME='치과'
WHERE LICENCE_NO=23999
;

 

- UPDATE문의 조건으로건 USER_ID와 LICENCE_NO는 UNIQUE 제약이 걸려있어서 식별자로서의 역할을 할 수 있다.

 

 

 

 

 

ㅁ 2_6. 의료진 계정 삭제 요청

 

 

 

(1) 넘기는 값

- 사번, 아이디

 

 

(2) 쿼리 

 

DELETE FROM DOCTOR
WHERE DOCTOR_NO=?
;


DELETE FROM MEMBER
WHERE USER_ID=?
;

 

 

(3) 쿼리 예시

 

DELETE FROM DOCTOR
WHERE DOCTOR_NO='vp5'
;


DELETE FROM MEMBER
WHERE USER_ID='test99'
;

 

 

 

 

 

ㅁ 3-1. 회원 계정 검색 요청(이름)

 

(1) 넘기는 값

- 이름 

 

 

(2) 가져와야 하는 값

- MEMBER : 아이디, 이름, 주민등록번호, 주소, 이메일, 수신여부, 핸드폰 번호, 회원가입일

 

(3) 쿼리

 

SELECT USER_ID, USER_NAME, USER_SSN, ADDRESS, EMAIL, CALLBACK_YN, PHONE, ENROLL_DATE
FROM MEMBER
WHERE USER_NAME = ?
;

 

(4) 쿼리 예시

 

SELECT USER_ID, USER_NAME, USER_SSN, ADDRESS, EMAIL, CALLBACK_YN, PHONE, ENROLL_DATE
FROM MEMBER
WHERE USER_NAME = '손흥민'
;

 

 

 

 

ㅁ 3-2. 회원 계정 검색 요청(아이디)

 

 

(1) 넘기는 값

- 아이디

 

(2) 가져와야 하는 값

- MEMBER : 아이디, 이름, 주민등록번호, 주소, 이메일, 수신여부, 핸드폰 번호, 회원가입일

 

(3) 쿼리

 

SELECT USER_ID, USER_NAME, USER_SSN, ADDRESS, EMAIL, CALLBACK_YN, PHONE, ENROLL_DATE
FROM MEMBER
WHERE USER_ID = ?
;

 

 

(4) 쿼리 예시

 

 

SELECT USER_ID, USER_NAME, USER_SSN, ADDRESS, EMAIL, CALLBACK_YN, PHONE, ENROLL_DATE
FROM MEMBER
WHERE USER_ID = 'test25'
;

 

 

 

 

ㅁ 4_1. 진료 예약 검색 요청

 

(1) 넘기는 값

- 진료과, 의료진명, 날짜

 

(2) 가져와야 하는 값

- CARE_APP : 예약번호, 예약일, 예약시간

- DOCTOR : 진료과, 의료진명

- MEMBER : 회원번호, 이름, 핸드폰번호

- CHECK_LIST : 복용중인 약, 최근 수술 여부, 수술명, 비행예정

 

(3) 쿼리

 

SELECT APPOINTMENT_NO, APPOINTMENT_DATE, APPOINTMENT_TIME, DEPT_NAME, DOCTOR_NAME,                          MEMBER.USER_NO, USER_NAME, PHONE, MEDI_LIST, SURGERY_YN, SURGERY_NAME, FLY_YN

FROM CARE_APP
JOIN DOCTOR ON CARE_APP.DOCTOR_NO = DOCTOR.DOCTOR_NO
JOIN MEMBER ON CARE_APP.USER_NO = MEMBER.USER_NO
JOIN CHECK_LIST ON CARE_NO = APPOINTMENT_NO
;

 

- 다중 조인의 경우 JOIN USING을 쓰면 컬럼명 겹친다고 떠서 그냥 JOIN ON을 사용함. 

 

 

 

 

ㅁ 4_2. 진료 예약 추가 요청

 

(1) 넘기는 값

- CARE_APP : 예약일, 예약시간, 

(의료진번호 = 진료과, 의료진명)

(회원번호 = 회원번호, 이름, 핸드폰번호)

- CHECK_LIST : 복용중인 약, 최근 수술 여부, 수술명, 비행예정

 

 

(2) 쿼리

 

INSERT INTO CARE_APP(APPOINTMENT_NO, USER_NO, DOCTOR_NO, APPOINTMENT_DATE, APPOINTMENT_TIME, CARE_STATUS, CARECHECK_YN)
VALUES ('cu'||SEQ_CAN.NEXTVAL, ?, ?, ?, ?, 'N', 'Y');

 

INSERT INTO CHECK_LIST(USER_NO, MEDI_LIST, SURGERY_NAME, SURGERY_YN, FLY_YN, CHECKUP_NO, CARE_NO)
VALUES (?, ?, ?, ?, ?, NULL, 
        (SELECT APPOINTMENT_NO
         FROM (   SELECT APPOINTMENT_NO
                  FROM CARE_APP
                  WHERE USER_NO=200603012
                  AND DOCTOR_NO='vp2'
                  AND APPOINTMENT_DATE=TO_DATE('2024-09-20', 'YYYY-MM-DD')
                  AND APPOINTMENT_TIME='오전'
                  AND CARE_STATUS='N'
                  ORDER BY APPOINTMENT_NO DESC
              )
         WHERE ROWNUM=1)     
        );
        

 

 

 

(3) 쿼리 예시 

 

INSERT INTO CARE_APP(APPOINTMENT_NO, USER_NO, DOCTOR_NO, APPOINTMENT_DATE, APPOINTMENT_TIME, CARE_STATUS, CARECHECK_YN)
VALUES ('cu'||SEQ_CAN.NEXTVAL, 200603012, 'vp2', TO_DATE('2024-09-20', 'YYYY-MM-DD'), '오전', 'N', 'Y');


INSERT INTO CHECK_LIST(USER_NO, MEDI_LIST, SURGERY_NAME, SURGERY_YN, FLY_YN, CHECKUP_NO, CARE_NO)
VALUES (200603012, '없음', '없음', 'N', 'N', NULL, 
        (SELECT APPOINTMENT_NO
         FROM (   SELECT APPOINTMENT_NO
                  FROM CARE_APP
                  WHERE USER_NO=200603012
                  AND DOCTOR_NO='vp2'
                  AND APPOINTMENT_DATE=TO_DATE('2024-09-20', 'YYYY-MM-DD')
                  AND APPOINTMENT_TIME='오전'
                  AND CARE_STATUS='N'
                  ORDER BY APPOINTMENT_NO DESC
              )
         WHERE ROWNUM=1)     
        );
        

- 진료 예약 테이블에 INSERT 후 CHECK_LIST에도 INSERT한다.

이때 예약번호가 필요한데 진료예약 테이블에 예약번호를 시퀀스로 넣어서 체크리스트 테이블에 넣을 예약번호는 서브쿼리로 가져와야 한다.

- 서브쿼리로 가져올 때 한 행만이 조회되게 ROWNUM을 주었다.

- DATE 타입의 컬럼에 문자열 값을 넣으므로 TO_DATE를 사용한다.

- 6번째로 들어가는 값은 건강검진 예약번호이다.

CHECK_LIST 테이블에 진료예약번호와 건강검진예약번호 컬럼이 NULL 허용으로 존재한다.

진료예약 추가요청이므로 건강검진 예약번호는 NULL을 준다.

 

 

 

ㅁ 4_3. 진료 예약 삭제 요청

 

(1) 넘기는 값

- 예약번호

 

 

(2) 쿼리

 

DELETE FROM CHECK_LIST
WHERE CARE_NO=?;
        
DELETE FROM CARE_APP
WHERE APPOINTMENT_NO=?; 

 

 

 

(3) 쿼리 예시

 

DELETE FROM CHECK_LIST
WHERE CARE_NO='cu13';
        
DELETE FROM CARE_APP
WHERE APPOINTMENT_NO='cu13';