Procedure HQT
unknown
sql
2 years ago
12 kB
3
Indexable
Never
-- 1. Them/Cap nhat khach hang ---- Procedure nay nhan input: Ma KH, Ten KH, SDT KH. Thuc hien tim kiem SDT trong csdl. ---- Neu khong tim thay: Insert moi du lieu vao csdl, DiemTichLuy = 0. ---- Neu tim thay: Thuc hien update Ho ten va SDT. CREATE OR REPLACE PROCEDURE Proc_01_Add_Or_Update_Customer ( p_maSoKH in KHACHHANG.makh%type, p_ten in KHACHHANG.tenkh%type, p_sdt in KHACHHANG.sdtkh%type ) as var_ms_kh KHACHHANG.makh%type; next_ms_kh KHACHHANG.makh%type; var_sdt_kh KHACHHANG.sdtkh%type; rowCount number; CURSOR C1 IS SELECT * FROM KHACHHANG WHERE MAKH = p_maSoKH or TENKH=p_ten OR SDTKH = p_sdt; CURSOR C_COUNT IS SELECT COUNT(*) FROM KHACHHANG; r_C1 C1%ROWTYPE; BEGIN if (length(p_maSoKH) != 4 or p_ten='' or length(p_sdt) !=10) then -- check neu input sai thi khong thuc hien raise_application_error(-20000, 'Du lieu dau vao khong dung dinh dang'); end if; OPEN C_COUNT; FETCH C_COUNT INTO rowCount; CLOSE C_COUNT; OPEN C1; -- fetch c1 INTO var_ms_kh, var_sdt_kh; fetch c1 INTO r_C1; IF c1%notfound then -- Khong tim thay csdl dbms_output.put_line('Chua ton tai khach hang nay trong CSDL.'); -- Lay MSKH ke tiep rowCount:=rowCount+1; next_ms_kh:='KH'||rowCount; -- Them vao csdl INSERT INTO khachhang (MaKH, TenKH, sdtKH, DiemTichLuy) VALUES (next_ms_kh, p_ten, p_sdt, 0); -- dbms_output.put_line('Them MSKH:'||next_ms_kh|| ' co ten '||p_ten||' voi sdt '||p_sdt||', diem mac dinh la 0.'); dbms_output.put_line('Them MSKH:'||next_ms_kh|| ' co ten '||p_ten||' voi sdt '||p_sdt||', diem mac dinh la 0.'); else -- Tim thay, nen update IF (r_C1.MaKH=p_maSoKH and r_C1.sdtKH=p_sdt and r_C1.tenkh=p_ten) THEN -- da ton tai du lieu raise_application_error(-20000, 'Da ton tai khach hang'); ELSIF (r_C1.MaKH=p_maSoKH and r_C1.sdtKH=p_sdt) THEN -- Ton tai MS va SDT thi cap nhat Ten UPDATE KHACHHANG SET TENKH=p_ten WHERE MAKH=p_maSoKH and SDTKH=p_sdt; dbms_output.put_line('Cap nhat ten: "'||p_ten||'" cho Khach hang co ma so: '||p_maSoKH||' va SDT: '||p_sdt); elsif (r_C1.MaKH=p_maSoKH and r_C1.tenkh=p_ten) THEN -- trung MSKH va TenKH thi cap nhat SDT UPDATE KHACHHANG SET SDTKH=p_sdt WHERE MAKH=p_maSoKH and TENKH=p_ten; dbms_output.put_line('Cap nhat sdt: "'||p_sdt||'" cho Khach hang co ma so: '||p_maSoKH||' va ten: '||p_ten); ELSIF (r_C1.MaKH=p_maSoKH) THEN -- Ton tai MSKH thi cap nhat Ten va SDT UPDATE KHACHHANG SET TENKH=p_ten, SDTKH=p_sdt WHERE MAKH=p_maSoKH; dbms_output.put_line('Cap nhat ten: "'||p_ten||'" va SDT: '||p_sdt||' cho khach hang co ma kh '||p_maSoKH); ELSIF (r_C1.sdtKH=p_sdt) THEN -- Ton tai sdt thi cap nhat ten, con MSKH ko dc phep cap nhat UPDATE KHACHHANG SET TENKH=p_ten WHERE SDTKH=p_sdt; dbms_output.put_line('Cap nhat ten: "'||p_ten||'" cho Khach hang co SDT: '||p_sdt); ELSE dbms_output.put_line('Khong lam gi het.'); END IF; end if; CLOSE C1; END; -- thuc thi Procedure 1 declare p_makh KHACHHANG.makh%type; p_ten KHACHHANG.tenkh%type; p_sdt KHACHHANG.sdtkh%type; begin p_makh := &p_makh; p_ten:=&p_ten; p_sdt:=&p_sdt; Proc_Add_Or_Update_Customer(p_makh, p_ten, p_sdt); end; -- 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; next_maNV NHANVIEN.manv%type; 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; next_maNV:='KH'||rowCount; -- Them vao csdl INSERT INTO nhanvien (MaNV, Username, UserPassword, TenNV, LoaiNV, CMND, sdtNV, GioiTinh,DiemChuyenCan, NgVaoLam, TinhTrang) values (next_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; 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; 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; 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; 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; 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; 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 -- thuc thi Procedure 2 declare p_maNV NHANVIEN.manv%type; p_username NHANVIEN.username%type; p_userpassword NHANVIEN.userpassword%type; p_tenNV NHANVIEN.tennv%type; p_loaiNV NHANVIEN.loainv%type; p_cmnd NHANVIEN.cmnd%type; p_sdt NHANVIEN.sdtnv%type; p_gioitinh NHANVIEN.gioitinh%type; p_NgVaoLam NHANVIEN.ngvaolam%type; p_tinhtrang NHANVIEN.tinhtrang%type; begin p_maNV :=&p_maNV; p_username := &p_username; p_userpassword := &p_userpassword; p_tenNV := &p_tenNV; p_loaiNV := &p_loaiNV; p_cmnd := &p_cmnd; p_sdt := &p_sdt; p_gioitinh := &p_gioitinh; p_NgVaoLam := &p_NgVaoLam; p_tinhtrang := &p_tinhtrang; Proc_02_Add_Or_Update_Employee(p_maNV, p_username, p_userpassword, p_tenNV, p_loaiNV, p_cmnd, p_sdt, p_gioitinh, p_NgVaoLam, p_tinhtrang); end; -- 459875632 -- 4. In ra lich su ban hang cua nhan vien -- In: 2 parameters -> string + choice (MaNV, SDT, CMND) -- Out: List(Chi tiet hoa don) CREATE OR REPLACE PROCEDURE Print_Sales_History_of_an_Employee(p_input varchar2, choice varchar2) as CURSOR C_HOADON IS SELECT * FROM HOADON; var_nv nhanvien%rowtype := get_staff_info(Upper(p_input), Lower(choice)); r_hoadon c_hoadon%ROWTYPE; result_string varchar2(10000):= ''; BEGIN IF var_nv.TenNV IS NULL THEN raise_application_error(-20000, 'Khong co Nhan vien nay.'); END IF; result_string := 'Lich Su Ban Hang cua nhan vien co ma so: '||var_nv.MANV||chr(10)||' '; open C_HOADON; LOOP FETCH C_HOADON INTO r_hoadon; EXIT WHEN C_HOADON%NOTFOUND; IF r_hoadon.MANV=var_nv.MaNV THEN result_string := concat(result_string, '- MaHD: '||r_hoadon.MAHD||', ngay ban '||r_hoadon.NgLap||' tong tri gia: '||r_hoadon.TongTien||chr(10)||' '); END IF; END LOOP; dbms_output.put_line(result_string); close C_HOADON; END; -- show errors procedure Print_Sales_History_of_an_Employee; -- sdt 0808878788 declare p_input varchar2(20); choice varchar2(20); begin p_input:=&p_input; choice:=&choice; Print_Sales_History_of_an_Employee(p_input, choice); end;