Untitled

-- 2. Them/ cap nhat nhan vien
---- Nhan dau vao: MaNV, Username, userpwd, tenNV, LoaiNV, CMND, sdt,GioiTinh, NgVaoLam, TinhTrang
---- Procedure tim kiem theo MaNV, CMND
---- Khong tim thay: Insert du lieu moi vao csdl
---- Tim thay: Update cac truong du lieu, ngoai tru MaNV
CREATE OR REPLACE PROCEDURE Proc_02_Add_Or_Update_Employee
(
    p_maNV in NHANVIEN.manv%type,
    p_username in NHANVIEN.username%type,
    p_userpassword in NHANVIEN.userpassword%type,
    p_tenNV in NHANVIEN.tennv%type,
    p_loaiNV in NHANVIEN.loainv%type,
    p_cmnd in NHANVIEN.cmnd%type,
    p_sdt in NHANVIEN.sdtnv%type,
    p_gioitinh in NHANVIEN.gioitinh%type,
    p_NgVaoLam in NHANVIEN.ngvaolam%type,
    p_tinhtrang in NHANVIEN.tinhtrang%type
)
as
--    empCount number := get_number_of_staffs();
    rowCount number;
    qr varchar2(1000) := 'update NHANVIEN set';
    first_update_flag number := 0;
    CURSOR C2 IS
        SELECT * FROM NHANVIEN 
        WHERE MANV = p_maNV OR CMND = p_cmnd;
    CURSOR C_COUNT IS
        SELECT COUNT(*) FROM KHACHHANG;
    r_C2 C2%ROWTYPE;
BEGIN
    OPEN C_COUNT;
        FETCH C_COUNT INTO rowCount;
    CLOSE C_COUNT;
    OPEN C2;
    
    fetch C2 INTO r_C2;
    if C2%notfound then
        -- Khong tim thay csdl
        dbms_output.put_line('Khong tim thay CMND hoac MaNV nay trong CSDL.');     
        -- Lay MSNV ke tiep
        rowCount:= rowCount+1;
        p_maNV:='KH'||rowCount;
        -- Them vao csdl
        INSERT INTO nhanvien (MaNV, Username, UserPassword, TenNV, LoaiNV, CMND, sdtNV, GioiTinh,DiemChuyenCan, NgVaoLam, TinhTrang) values (p_maNV, p_username, p_userpassword, p_tenNV, p_loaiNV, p_cmnd, p_sdt, p_gioitinh, 0, p_NgVaoLam, p_tinhtrang);
        dbms_output.put_line('Them Nhan vien moi vao CSDL: ');
    else
        IF (r_C2.MANV!=p_maNV and r_C2.CMND=p_cmnd) THEN
            -- Ton tai CMND
            raise_application_error(-20000, 'Da ton tai CMND nay trong CSDL. Input sai.');
        ELSE
            IF (r_C2.Username=p_username and p_username is not null) THEN
                -- MANV va CMND ton tai. Nen update.
                -- Chinh sua username
                IF first_update_flag=0 THEN
                    -- Neu flag=0 thi concat khong co dau ,
                    qr := concat(qr, ' USERNAME="'||p_username||'"');
                    first_update_flag:=1;
                ELSE
                    -- flag khac 0 thi concat co dau ,
                    qr := concat(qr, ', USERNAME="'||p_username||'"');
                END IF;                
            END IF;
            IF (r_C2.UserPassword=p_userpassword and p_userpassword is not null) THEN
                -- KIEM TRA UserPassword
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' UserPassword="'||p_userpassword||'"');
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', UserPassword="'||p_userpassword||'"');
                END IF;
            END IF;
            IF (r_C2.TenNV=p_tenNV and p_tenNV is not null) THEN
                -- KIEM TRA TenNV
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' TenNV="'||p_tenNV||'"');
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', TenNV="'||p_tenNV||'"');            
            END IF;
            IF (r_C2.LoaiNV=p_loaiNV and p_loaiNV is not null) THEN
                -- KIEM TRA LoaiNV
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' LoaiNV='||p_loaiNV);
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', LoaiNV='||p_loaiNV);            
            END IF;
            IF (r_C2.sdtNV=p_sdt and p_sdt is not null) THEN
                -- KIEM TRA sdtNV
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' sdtNV="'||p_sdt||'"');
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', sdtNV="'||p_sdt||'"');            
            END IF;
            IF (r_C2.GioiTinh=p_gioitinh and p_gioitinh is not null) THEN
                -- KIEM TRA GioiTinh
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' GioiTinh='||p_gioitinh);
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', GioiTinh='||p_gioitinh);            
            END IF;
            IF (r_C2.NgVaoLam=p_NgVaoLam and p_NgVaoLam is not null) THEN
                -- KIEM TRA NgVaoLam
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' NgVaoLam="'||p_NgVaoLam||'"');
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', NgVaoLam="'||p_NgVaoLam||'"');            
            END IF;
            IF (r_C2.TINHTRANG=p_tinhtrang and p_tinhtrang is not null) THEN
                -- KIEM TRA TINHTRANG
                IF first_update_flag=0 THEN
                    qr := concat(qr, ' TINHTRANG='||p_tinhtrang);
                    first_update_flag:=1;
                ELSE
                    qr := concat(qr, ', TINHTRANG='||p_tinhtrang);            
            END IF;
        end if;
        IF (r_C2.MANV=p_maNV and r_C2.CMND=p_cmnd and length(qr)>length('update NHANVIEN set')) THEN
            qr := concat(qr, 'where MANV="'||p_maNV||'" and CMND="'||p_cmnd||';');
            dbms_output.put_line(qr);
            execute immediate qr;
        END IF;
    END IF;
    
    CLOSE C2;
END;
show errors procedure Proc_02_Add_Or_Update_Employee

/*
Errors for PROCEDURE PROC_02_ADD_OR_UPDATE_EMPLOYEE:
LINE/COLUMN ERROR
----------- --------------------------------------------------------------------
124/4       PLS-00103: Encountered the symbol ";" when expecting one of the
             following:
               if
*/