Untitled
unknown
sql
2 years ago
6.0 kB
3
Indexable
Never
-- 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 */