Procedure HQT
unknown
sql
3 years ago
12 kB
4
Indexable
-- 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;