PLSQL 검증 | Notion

<aside> 💡

관리자

</aside>

관리자 로그인 검증

CREATE OR REPLACE PROCEDURE admin_login(
    p_id IN tblAdmin.id%TYPE,
    p_password IN tblAdmin.password%TYPE
) IS
    v_admin_seq tblAdmin.admin_seq%TYPE;
    v_admin_name tblAdmin.admin_name%TYPE;
    v_phone_number tblAdmin.phone_number%TYPE;
    v_email tblAdmin.email%TYPE;
    v_stored_password tblAdmin.password%TYPE;
BEGIN
    -- ID로 관리자 정보 조회
    SELECT admin_seq, admin_name, phone_number, email, password
    INTO v_admin_seq, v_admin_name, v_phone_number, v_email, v_stored_password
    FROM tblAdmin
    WHERE id = p_id;

    -- 비밀번호 검증
    IF v_stored_password = p_password THEN
        DBMS_OUTPUT.PUT_LINE('로그인 성공!');
        DBMS_OUTPUT.PUT_LINE('관리자 번호: ' || v_admin_seq);
        DBMS_OUTPUT.PUT_LINE('관리자 이름: ' || v_admin_name);
        DBMS_OUTPUT.PUT_LINE('연락처: ' || v_phone_number);
        DBMS_OUTPUT.PUT_LINE('이메일: ' || v_email);
    ELSE
        DBMS_OUTPUT.PUT_LINE('로그인 실패: 비밀번호가 일치하지 않습니다.');
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('로그인 실패: 존재하지 않는 ID입니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('로그인 중 오류 발생: ' || SQLERRM);
END admin_login;
/

-- 검증 테스트
BEGIN
    admin_login('admin123', 'password123'); -- 실제 존재하는 ID와 비밀번호를 입력해야 합니다.
END;
/

BEGIN
    admin_login('admin5', 'password123'); -- 실제 존재하는 ID와 비밀번호를 입력해야 합니다.
END;
/

기초 정보 관리

등록

CREATE OR REPLACE PROCEDURE basic_insert_p (
    p_seq IN tblBasic.basic_seq%TYPE,
    p_book_seq IN tblBasic.book_seq%TYPE,
    p_subject_seq IN tblBasic.subject_seq%TYPE,
    p_room_seq IN tblBasic.room_seq%TYPE,
    p_course_seq IN tblBasic.course_seq%TYPE
)
    IS
    v_book_name tblBook.book_name%TYPE;
    v_subject_name tblSubject.subject_name%TYPE;
    v_room_number tblRoom.room_number%TYPE;
    v_course_name tblCourse.course_name%TYPE;
BEGIN
    -- 이름 조회
    SELECT book_name INTO v_book_name FROM tblBook WHERE book_seq = p_book_seq;
    SELECT subject_name INTO v_subject_name FROM tblSubject WHERE subject_seq = p_subject_seq;
    SELECT room_number INTO v_room_number FROM tblRoom WHERE room_seq = p_room_seq;
    SELECT course_name INTO v_course_name FROM tblCourse WHERE course_seq = p_course_seq;

    -- 데이터 삽입
    INSERT INTO tblBasic (basic_seq, book_seq, subject_seq, room_seq, course_seq)
    VALUES (p_seq, p_book_seq, p_subject_seq, p_room_seq, p_course_seq);

    COMMIT;

    -- 출력
    DBMS_OUTPUT.PUT_LINE('기초정보 등록 성공');
    DBMS_OUTPUT.PUT_LINE('기초정보 번호: ' || p_seq);
    DBMS_OUTPUT.PUT_LINE('📚 교재: ' || v_book_name || ' (' || p_book_seq || ')');
    DBMS_OUTPUT.PUT_LINE('📖 과목: ' || v_subject_name || ' (' || p_subject_seq || ')');
    DBMS_OUTPUT.PUT_LINE('🏫 강의실: ' || v_room_number || ' (' || p_room_seq || ')');
    DBMS_OUTPUT.PUT_LINE('📅 과정: ' || v_course_name || ' (' || p_course_seq || ')');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('❌ 입력된 ID 중 하나가 존재하지 않습니다.');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('❌ 등록 실패: ' || SQLERRM);
        ROLLBACK;
END basic_insert_p;
/

수정

CREATE OR REPLACE PROCEDURE basic_update_p (
    p_seq IN tblBasic.basic_seq%TYPE,
    p_book_seq IN tblBasic.book_seq%TYPE,
    p_subject_seq IN tblBasic.subject_seq%TYPE,
    p_room_seq IN tblBasic.room_seq%TYPE,
    p_course_seq IN tblBasic.course_seq%TYPE
)
IS
    v_book_name tblBook.book_name%TYPE;
    v_subject_name tblSubject.subject_name%TYPE;
    v_room_number tblRoom.room_number%TYPE;
    v_course_name tblCourse.course_name%TYPE;
BEGIN
    -- 이름 조회
    SELECT book_name INTO v_book_name FROM tblBook WHERE book_seq = p_book_seq;
    SELECT subject_name INTO v_subject_name FROM tblSubject WHERE subject_seq = p_subject_seq;
    SELECT room_number INTO v_room_number FROM tblRoom WHERE room_seq = p_room_seq;
    SELECT course_name INTO v_course_name FROM tblCourse WHERE course_seq = p_course_seq;

    -- 데이터 수정
    UPDATE tblBasic
    SET book_seq = p_book_seq,
        subject_seq = p_subject_seq,
        room_seq = p_room_seq,
        course_seq = p_course_seq
    WHERE basic_seq = p_seq;

    COMMIT;
    
    -- 출력
    DBMS_OUTPUT.PUT_LINE('기초정보 수정 성공');
    DBMS_OUTPUT.PUT_LINE('기초정보 번호: ' || p_seq);
    DBMS_OUTPUT.PUT_LINE('📚 교재: ' || v_book_name || ' (' || p_book_seq || ')');
    DBMS_OUTPUT.PUT_LINE('📖 과목: ' || v_subject_name || ' (' || p_subject_seq || ')');
    DBMS_OUTPUT.PUT_LINE('🏫 강의실: ' || v_room_number || ' (' || p_room_seq || ')');
    DBMS_OUTPUT.PUT_LINE('📅 과정: ' || v_course_name || ' (' || p_course_seq || ')');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('❌ 입력된 ID 중 하나가 존재하지 않습니다.');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('❌ 수정 실패: ' || SQLERRM);
        ROLLBACK;
END basic_update_p;
/

-- 수정
BEGIN
    basic_update_p(148, 17, 10, 3, 2);
END;
/