BÀI TẬP ORACLE
Các bạn copy lệnh để để tạo bảng tạo thủ tục trigger
CREATE TABLE BT_HANGHOA(
ma_hang char(5) not null primary key check (REGEXP_LIKE(ma_hang,'^SP\d{3}')),
ten_hh nvarchar2(60)
);
--Insert du lieu
INSERT INTO BT_HANGHOA VALUES('SP001','DAU GOI DAU');
INSERT INTO BT_HANGHOA VALUES('SP002','BOT GIAC ABA HUYEN THOAI');
INSERT INTO BT_HANGHOA VALUES('SP003','DAU XE QUAN AO COMFORT');
INSERT INTO BT_HANGHOA VALUES('SP004','NUOC RUA CHEN SUSU');
INSERT INTO BT_HANGHOA VALUES('SP005','XÀ BÔNG LIFEBUOY DIỆT KHUẨN');
create table BT_DAILY(
ten_dl nvarchar2(80),
diachi_dl nvarchar2(120)
);
---Insert du lieu------
INSERT INTO BT_DAILY VALUES ('Cao Hoàng Kha','xã Vĩnh Trung, huyện Tịnh Biên, AG');
INSERT INTO BT_DAILY VALUES ('Lê Hải Long','xã Châu Lăng, huyện Tri Tôn, AG');
INSERT INTO BT_DAILY VALUES ('NGUYỄN HỒNG HẢI','thi trấn Tri Tôn, huyện Tri Tôn, AG');
INSERT INTO BT_DAILY VALUES ('Nguyễn Thị Thảo Linh','xã Tân Lợi, huyện Tịnh Biên, AG');
INSERT INTO BT_DAILY VALUES ('Phương Văn Thùy','Phường 1, thành phố Cà Mau');
CREATE TABLE BT_MUA(
ma_hang char(5) not null,
ngaymua date default sysdate,
soluong_mua int default 1,
trigia_mua number(2,8),
CONSTRAINT fk_mahh FOREIGN KEY (ma_hang) REFERENCES BT_HANGHOA(ma_hang)
);
ALTER TABLE BT_MUA
MODIFY TRIGIA_MUA FLOAT;
-- inser du lieu--
DELETE BT_MUA WHERE ma_hang='SP001';
SELECT * FROM BT_MUA WHERE trigia_mua >= 350.000000;
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','100','2.300000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP002','80','1.300000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','120','3.500000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','50','350.000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP005','100','2.800000');
CREATE TABLE BT_BAN(
ma_hang char(5) not null,
ngay_ban date default sysdate,
soluong_ban int default 1,
trigia_ban number,
CONSTRAINT fk_mh FOREIGN KEY (ma_hang) REFERENCES BT_HANGHOA(ma_hang)
);
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP001','80','4.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP005','50','2.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP001','50','2.800000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP002','50','1.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP005','80','6.500000');
------------------------
SELECT*FROM BT_HANGHOA
CREATE TABLE BT_HANGHOA(
ma_hang char(5) not null primary key check (REGEXP_LIKE(ma_hang,'^SP\d{3}')),
ten_hh nvarchar2(60)
);
--Insert du lieu
INSERT INTO BT_HANGHOA VALUES('SP001','DAU GOI DAU');
INSERT INTO BT_HANGHOA VALUES('SP002','BOT GIAC ABA HUYEN THOAI');
INSERT INTO BT_HANGHOA VALUES('SP003','DAU XE QUAN AO COMFORT');
INSERT INTO BT_HANGHOA VALUES('SP004','NUOC RUA CHEN SUSU');
INSERT INTO BT_HANGHOA VALUES('SP005','XÀ BÔNG LIFEBUOY DIỆT KHUẨN');
DROP TABLE BT_MUA;
create table BT_DAILY(
ten_dl nvarchar2(80),
diachi_dl nvarchar2(120)
);
---Insert du lieu------
INSERT INTO BT_DAILY VALUES ('Cao Hoàng Kha','xã Vĩnh Trung, huyện Tịnh Biên, AG');
INSERT INTO BT_DAILY VALUES ('Lê Hải Long','xã Châu Lăng, huyện Tri Tôn, AG');
INSERT INTO BT_DAILY VALUES ('NGUYỄN HỒNG HẢI','thi trấn Tri Tôn, huyện Tri Tôn, AG');
INSERT INTO BT_DAILY VALUES ('Nguyễn Thị Thảo Linh','xã Tân Lợi, huyện Tịnh Biên, AG');
INSERT INTO BT_DAILY VALUES ('Phương Văn Thùy','Phường 1, thành phố Cà Mau');
CREATE TABLE BT_MUA(
ma_hang char(5) not null,
ngaymua date default sysdate,
soluong_mua int default 1,
trigia_mua number(2,8),
CONSTRAINT fk_mahh FOREIGN KEY (ma_hang) REFERENCES BT_HANGHOA(ma_hang)
);
ALTER TABLE BT_MUA
MODIFY TRIGIA_MUA FLOAT;
-- inser du lieu--
DELETE BT_MUA WHERE ma_hang='SP001';
SELECT * FROM BT_MUA WHERE trigia_mua >= 350.000000;
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','100','2.300000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP002','80','1.300000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','120','3.500000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP001','50','350.000');
INSERT INTO BT_MUA(MA_HANG, SOLUONG_MUA, TRIGIA_MUA) VALUES('SP005','100','2.800000');
CREATE TABLE BT_BAN(
ma_hang char(5) not null,
ngay_ban date default sysdate,
soluong_ban int default 1,
trigia_ban number,
CONSTRAINT fk_mh FOREIGN KEY (ma_hang) REFERENCES BT_HANGHOA(ma_hang)
);
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP001','80','4.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP005','50','2.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP001','50','2.800000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP002','50','1.500000');
INSERT INTO BT_BAN(MA_HANG, SOLUONG_BAN, TRIGIA_BAN) VALUES ('SP005','80','6.500000');
CREATE TABLE QUAN_LYKHO(
ma_sp char(5) not nulL,
tenhh nvarchar2(120),
soluongkho int,
CONSTRAINT fk_3 FOREIGN KEY (ma_sp) referenceS BT_HANGHOA(MA_HANG)
);
INSERT INTO QUAN_LYKHO VALUES('SP001','DAU GOI DAU',500);
INSERT INTO QUAN_LYKHO VALUES('SP002','BOT GIAC ABA HUYEN THOAI',500);
INSERT INTO QUAN_LYKHO VALUES('SP003','DAU XE QUAN AO COMFORT',450);
INSERT INTO QUAN_LYKHO VALUES('SP004','NUOC RUA CHEN SUSU',400);
INSERT INTO QUAN_LYKHO VALUES('SP005','XÀ BÔNG LIFEBUOY DIỆT KHUẨN',500);
--DECLARE
-- ma_hang bt_mua.ma_hang%TYPE :=ma_hh;
-- BEGIN
-- IF ma_hh = 'SP001'
-- THEN
-- UPDATE BT_MUA SET TRIGIA_MUA=2.500*soluong;
-- ELSE
-- dbms.output.put_line('ma hanng khong ton tai');
-- END IF;
-- END;
--END;
----------------TAO THU TUC (1.1)------------
create or replace PROCEDURE NHAP_SP(ma_hh bt_mua.ma_hang%type, soluong int)
IS
BEGIN
IF ma_hh = 'SP001'
THEN
UPDATE BT_MUA SET TRIGIA_MUA=2.500*soluong WHERE ma_hang = ma_hh;
ELSIF ma_hh = 'SP002'
THEN
UPDATE BT_MUA SET TRIGIA_MUA=3.400*soluong WHERE ma_hang = ma_hh;
ELSIF ma_hh = 'SP003'
THEN
UPDATE BT_MUA SET TRIGIA_MUA=3.900*soluong WHERE ma_hang = ma_hh;
ELSIF ma_hh = 'SP004'
THEN
UPDATE BT_MUA SET TRIGIA_MUA=4.200*soluong WHERE ma_hang = ma_hh;
ELSIF ma_hh = 'SP005'
THEN
UPDATE BT_MUA SET TRIGIA_MUA=1.200*soluong WHERE ma_hang = ma_hh;
ELSE
INSERT INTO BT_MUA(ma_hang, soluong_mua, trigia_mua) values(ma_hh, soluong, soluong*100);
END IF;
END;
--THUC THI THU TUC (1.1)--
EXECUT NHAP_SP('SP001',10);
SET SERVEROUTPUT ON;
DECLARE
nhapma bt_mua.ma_hang%TYPE;
nhapsl bt_mua.soluong_mua%type;
-- ketqua NVARCHAR2(120);
BEGIN
nhapma :=&Nhap_ma_hang;
nhapsl :=&nhap_so_luong_hang;
Nhap_SP(nhapma, nhapsl);
dbms_output.put_line('THEM THANH CONG');
END;
--------------Tao them thu tuc (1.2) them san pham vao bang BT_HANGHOA voi mahang va ten hang hoa la hai tham so dau vao-------
create or replace PROCEDURE THEM_HANGHOA(mahang bt_hanghoa.ma_hang%type, tenhh bt_hanghoa.ten_hh%TYPE)
IS
sohanghoa int;
BEGIN
SELECT COUNT(*)INTO sohanghoa FROM BT_HANGHOA WHERE BT_HANGHOA.TEN_HH=TENHH AND MA_HANG = MAHANG;
if sohanghoa > 0 then
DBMS_OUTPUT.PUT_LINE('SAN PHAM DA TON TAI');
ELSE
INSERT INTO BT_HANGHOA(ma_hang, ten_hh) VALUES(mahang, tenhh);
end if;
END;
----THUC THI THU TUC (1.2)
SELECT*FROM BT_HANGHOA;
SET SERVEROUTPUT ON;
EXECUTE THEM_HANGHOA('SP001','DAU GOI DAU');
SELECT*FROM bt_hanghoa
SELECT*FROM BT_MUA;
--TAO HAM THEM DU LIEU VAO BANG HANG HOA-----
CREATE OR REPLACE PROCEDURE THEM_HANGHOA(mahang bt_hanghoa.ma_hang%type, tenhh bt_hanghoa.ten_hh%TYPE)
IS
BEGIN
INSERT INTO BT_HANGHOA VALUES (mahang, tenhh);
END;
---
SET SERVEROUTPUT ON;
DECLARE
mahang bt_hanghoa.ma_hang%type;
tenhh bt_hanghoa.ten_hh%type;
-- ketqua CHAR(5);
BEGIN
mahang:=&NHAP_MA_HANG_HOA;
tenhh:=&nhap_ten_hang_hoa;
-- them_hanghoa(mahang, tenhh);
-- FOR ma_hang IN SELECT BT_HANGHOA.ma_hang INTO KETQUA from BT_HANGHOA WHERE BT_HANGHOA.ma_hang=mahang
-- LOOP
-- IF (mahang != ketqua) THEN
them_hanghoa(MAhang, tenhh);
dbms_output.put_line('THEM THANH CONG');
-- ELSE
-- dbms_output.put_line('MA HANG DA TON TAI');
-- END IF;
-- END LOOP;
END;
-- TAO HAM LAY RA TONG SO LUONG SAN PHAM VOI DAU VAO LA MA SAN PHAM------
SELECT*FROM BT_MUA;
CREATE OR REPLACE FUNCTION GET_tongtrigia(mahang bt_mua.ma_hang%TYPE)
RETURN NUMBER
IS
tong number;
BEGIN
tong:=0;
FOR BT_MUA IN (SELECT bt_mua.trigia_mua FROM BT_MUA WHERE MA_HANG=MAHANG
AND MA_HANG IS NOT NULL)
LOOP
tong := tong + bt_mua.trigia_mua;
END LOOP;
RETURN TONG;
END;
THỰC THI
----THUC THI---
SELECT GET_TONGTRIGIA('SP001') AS TONG_GIA_TRI FROM DUAL;
---
SET SERVEROUTPUT ON;
DECLARE
nhapmahang bt_mua.ma_hang%type;
result_trigia number;
BEGIN
nhapmahang:=&Nhap_ma_hang;
result_trigia:=GET_TONGTRIGIA(nhapmahang);
dbms_output.put_line('Tong so luong san pham ' || nhapmahang || ' LA: ' || result_trigia );
end;
SELECT*FROM bt_ban;
-------------------------TAO TRIGGER (1.1)----------------------
--TAO BANG--
SELECT*FROM change_daily;
ALTER TABLE CHANGE_DAILY
MODIFY MAHANG CHAR(5) NOT NULL;
ALTER TABLE CHANGE_DAILY
MODIFY NEW_MAHANG CHAR(5) DEFAULT 'SP***'
SELECT*FROM BT_BAN;
--TAO TRIGGER (1.1)
CREATE OR REPLACE TRIGGER CHANGE_INFORDL AFTER UPDATE
ON BT_BAN FOR EACH ROW
BEGIN
INSERT INTO change_daily(USER_NAME, THOIDIEM, MAHANG,NEW_MAHANG, OLD_SOLUONGBAN, NEW_SOLUONGBAN)
VALUES (USER, SYSDATE,:old.MA_HANG,:new.MA_HANG, :old.SOLUONG_BAN, :new.SOLUONG_BAN);
END;
--THUC THI
UPDATE BT_BAN SET soluong_ban=120 WHERE MA_HANG='SP005';
UPDATE BT_BAN SET MA_HANG='SP004' WHERE MA_HANG='SP005';
DROP TRIGGER CHANGE_INFORDL;
------
CREATE OR REPLACE TRIGGER CAPNHAT_DAILY
AFTER INSERT OR UPDATE ON BT_DAILY FOR EACH ROW
begin
INSERT INTO them_daily(new_tenDL, old_tenDL, new_diachi, old_diachi)
VALUES(:new.ten_DL, :old.ten_DL, :new.DIACHI_DL, :old.DIACHI_DL);
end;
------SUA DU LIEU
UPDATE BT_DAILY SET diachi_dl='ấp Tân Thuận, xã Tân Lợi, huyện Tịnh Biên, AG' where ten_dl='Cao Hoàng Kha';
select*from them_daiLy;
----THEM DU LIEU
INSERT INTO BT_DAILY VALUES('Nguyễn Thị Khang','P.Xuân Khánh, Q.Ninh Kiều, TP Cần Thơ');
select*from BT_HANGHOA;
----------Tao trigger (1.2) ngan tran vieC nhap du lieu----
CREATE OR REPLACE TRIGGER DAC0_HANGHOA
BEFORE INSERT ON BT_HANGHOA FOR EACH ROW
DECLARE
soSP int;
mahang char(5);
tenhh nvarchar2(60);
BEGIN
SELECT COUNT(*) INTO soSP from BT_HANGHOA
WHERE ma_hang =:new.ma_hang and ten_hh=:new.ten_hh;
select ma_hang, ten_hh INTO mahang, tenhh from bt_hanghoa where ma_hang =:new.ma_hang and ten_hh=:new.ten_hh;
if sosp > 0 THEN
dbms_output.put_line('SP ' || mahang || ' ten hang hoa ' || tenhh || ' Da Ton Tai Trong Kho, Vui Long Them Ma Hang Moi');
end if;
END;
SET SERVEROUTPUT ON;
INSERT INTO BT_HANGHOA(ma_HANG, ten_hh) VALUES('SP001','DAU GOI DAU');
------------CAP CHO B1805698 QUYEN TRUY CAP DU LIEU, SU DUNG PROCEDURE, FUNTION----------
----------------------------QUYEN TRUY CAP DU LIEU-------
REVOKE ALL ON BT_HANGHOA FROM B1805698;
GRANT SELECT, INSERT, UPDATE ON BT_HANGHOA TO B1805698
GRANT SELECT, INSERT, UPDATE ON BT_MUA TO B1805698
GRANT SELECT, INSERT, UPDATE ON BT_BAN TO B1805698
GRANT SELECT, INSERT, UPDATE ON BT_DAILY TO B1805698
----QUYEN TRUY CAP THU TUC, HAM----
GRANT EXECUTE ON GET_TONGTRIGIA TO B1805698;
GRANT EXECUTE ON THEM_HANGHOA TO B1805698;
GRANT EXECUTE ON NHAP_SP TO B1805698;