Procedure HQT

mail@pastecode.io avatar
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;