--CHUONG7-PL/SQL--
--1--
DECLARE
SL NUMBER(4);
MDT NVARCHAR2(5):='dt4';
BEGIN
SELECT COUNT(MACB)
INTO SL
FROM CANBO
WHERE MADETAI=MDT;
DBMS_OUTPUT.PUT_LINE('CO '||SL||' CAN BO THAM GIA DE TAI '||MDT);
END;
--2--
DECLARE
SLNN NUMBER(4);
MCB NVARCHAR2(5):='cb1';
BEGIN
SELECT COUNT(TENGOAINGU)
INTO SLNN
FROM NGOAINGU
WHERE MACB=MCB;
DBMS_OUTPUT.put_line('CAN BO '||MCB||' BIET '||SLNN||' NGOAI NGU');
END;
--3--
DECLARE
LUONGMAX NUMBER(10);
LUONGMIN NUMBER(10);
BEGIN
SELECT MAX(LUONG)
INTO LUONGMAX
FROM CANBO;
SELECT MIN(LUONG)
INTO LUONGMIN
FROM CANBO;
DBMS_OUTPUT.PUT_LINE('LUONG CAO NHAT '||LUONGMAX);
DBMS_OUTPUT.PUT_LINE('LUONG THAP NHAT '||LUONGMIN);
END;
--4--
DECLARE
NGAYMAX DATE;
NGAYMIN DATE;
BEGIN
SELECT MAX(NGAYCUOI)
INTO NGAYMAX
FROM DETAI;
SELECT MIN(NGAYCUOI)
INTO NGAYMIN
FROM DETAI;
DBMS_OUTPUT.PUT_LINE('NGAY DE TAI KET THUC MUON NHAT '||NGAYMAX);
DBMS_OUTPUT.PUT_LINE('NGAY DE TAI KET THUC SOM NHAT '||NGAYMIN);
END;
--5--
DECLARE
TIMEMAX NUMBER(10);
TIMEMIN NUMBER(10);
BEGIN
SELECT MAX(TG)
INTO TIMEMAX
FROM (SELECT (NGAYCUOI-NGAYDAU) AS TG FROM DETAI);
SELECT MIN(TG)
INTO TIMEMIN
FROM (SELECT (NGAYCUOI-NGAYDAU) AS TG FROM DETAI);
DBMS_OUTPUT.PUT_LINE('THOI GIAN THUC HIEN DE TAI LAU NHAT '||TIMEMAX);
DBMS_OUTPUT.PUT_LINE('THOI GIAN THUC HINE DE TAI NGAN NHAT '||TIMEMIN);
END;
--6--
DECLARE
SLCB NUMBER(4);
TENNN NVARCHAR2(17):='Anh';
BEGIN
SELECT COUNT(MACB)
INTO SLCB
FROM CANBO
WHERE MACB NOT IN (SELECT CANBO.MACB FROM CANBO JOIN NGOAINGU ON NGOAINGU.MACB = CANBO.MACB WHERE TENGOAINGU = TENNN);
DBMS_OUTPUT.PUT_LINE('CO '||SLCB||' KHONG BIET TIENG '||TENNN);
END;
--7--
DECLARE
SL NUMBER(4);
MDT NVARCHAR2(5):='dt3';
BEGIN
SELECT COUNT(MACB)
INTO SL
FROM CANBO
WHERE MADETAI=MDT;
DBMS_OUTPUT.PUT_LINE('CO '||SL||' CAN BO THAM GIA DE TAI '||MDT);
IF SL>7 THEN DBMS_OUTPUT.PUT_LINE('DE TAI NAY CO QUA NHIEU CAN BO THAM GIA');
END IF;
END;
--8--
ACCEPT SONGUYEN PROMPT 'NHAP 1 SO NGUYEN: '
BEGIN
DBMS_OUTPUT.PUT_LINE('GIA TRI TUYET DOI: '||ABS('&SONGUYEN'));
END;
--9--
DECLARE
SLCB NUMBER(10);
BEGIN
SELECT COUNT(MACB)
INTO SLCB
FROM CANBO
WHERE MADETAI IS NULL;
DBMS_OUTPUT.PUT_LINE('CO '||SLCB||' CAN BO KHONG THAM GIA DE TAI NAO');
IF SLCB>5 THEN DBMS_OUTPUT.PUT_LINE('PHAI XEM XET LAI TINH HINH LAM DE TAI CUA DON VI NAY');
ELSE DBMS_OUTPUT.PUT_LINE('SO LUONG NAY TAM CHAP NHAN DUOC');
END IF;
END;
--10--
DECLARE
LUONGMAX NUMBER(10);
LUONGMIN NUMBER(10);
BEGIN
SELECT MAX(LUONG)
INTO LUONGMAX
FROM CANBO;
SELECT MIN(LUONG)
INTO LUONGMIN
FROM CANBO;
DBMS_OUTPUT.PUT_LINE('LUONG CAO NHAT - LUONG THAP NHAT = '||(LUONGMAX-LUONGMIN));
CASE
WHEN (LUONGMAX-LUONGMIN) > 5000000 THEN DBMS_OUTPUT.PUT_LINE('TANG LUONG 200000');
WHEN (LUONGMAX-LUONGMIN) > 3000000 THEN DBMS_OUTPUT.PUT_LINE('TANG LUONG 100000');
ELSE DBMS_OUTPUT.PUT_LINE('TANG LUONG 50000');
END CASE;
END;
--11--
ACCEPT SOTUOI PROMPT 'NHAP TUOI CUA BAN: '
BEGIN
DBMS_OUTPUT.PUT_LINE('BAN '||'&SOTUOI'||' TUOI');
CASE
WHEN '&SOTUOI'<11 THEN DBMS_OUTPUT.PUT_LINE('TOI TRE QUA');
WHEN '&SOTUOI'<20 THEN DBMS_OUTPUT.PUT_LINE('TOI LA THANH NIEN');
WHEN '&SOTUOI'<35 THEN DBMS_OUTPUT.PUT_LINE('TOI DANG NO LUC LAM VIEC');
ELSE DBMS_OUTPUT.PUT_LINE('TOI DA GIA..');
END CASE;
END;
--12--
--VONG LAP DON GIAN--
DECLARE
X NUMBER :=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('XIN CHAO CAC BAN...');
X:=X+1;
IF X>10 THEN EXIT;
END IF;
END LOOP;
END;
--VONG LAP WHILE--
DECLARE X NUMBER := 1;
BEGIN
WHILE X<=10 LOOP
DBMS_OUTPUT.PUT_LINE('XIN CHAO CAC BAN...');
X:=X+1;
END LOOP;
END;
--VONG LAP FOR--
DECLARE X NUMBER;
BEGIN
FOR X IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('XIN CHAO CAC BAN...');
END LOOP;
END;
--13--
ACCEPT N PROMPT 'NHAP SO NGUYEN N: '
DECLARE
I NUMBER:=0;
TONG NUMBER:=0;
BEGIN
FOR I IN 1..'&N' LOOP
TONG := TONG + I;
END LOOP;
DBMS_OUTPUT.put_line('TONG LA: '||TONG);
END;
--14--
ACCEPT N PROMPT 'NHAP SO NGUYEN N: '
DECLARE
X NUMBER;
TICH NUMBER:=1;
BEGIN
IF '&N' MOD 2 = 0
THEN X:=2;
ELSE X:=1;
END IF;
WHILE X<='&N' LOOP
TICH:=TICH*X;
X:=X+2;
END LOOP;
DBMS_OUTPUT.put_line('TICH LA: '||TICH);
END;
--15--
DECLARE
I NUMBER(5):=0;
TONG NUMBER(5):=0;
BEGIN
WHILE TONG<100 LOOP
I:=I+1;
TONG:=TONG+I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('N LA: '||(I-1));
I:=0;TONG:=0;
WHILE TONG<99 LOOP
I:=I+1;
TONG:=TONG+I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('K LA: '||(I));
END;
--16--
DECLARE I NUMBER(4);
BEGIN
FOR I IN 1..3 LOOP
INSERT INTO DETAI(MADETAI,TENDETAI,NGAYDAU,NGAYCUOI)
VALUES ('dt1'||I,'Quan ly nha dat '||I,to_date('18/3/2014','dd/mm/yyyy'),to_date('20/11/2014','dd/mm/yyyy'));
END LOOP;
END;
--17--
DECLARE
I NUMBER(4);
MDT DETAI.MADETAI%TYPE;
TDT DETAI.TENDETAI%TYPE;
ND DETAI.NGAYDAU%TYPE;
NC DETAI.NGAYCUOI%TYPE;
BEGIN
FOR I IN 1..4 LOOP
SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI
INTO MDT,TDT,ND,NC
FROM DETAI
WHERE MADETAI = 'dt'||I;
DBMS_OUTPUT.PUT_LINE(' MA DE TAI: '||MDT||'| TEN DE TAI: '||TDT||'| NGAY DAU: '||ND||'| NGAY CUOI: '||NC);
END LOOP;
END;
--18--
DECLARE
I NUMBER(4);
MCB CANBO.MACB%TYPE;
TCB CANBO.TENCB%TYPE;
MDT CANBO.MADETAI%TYPE;
QQ CANBO.QUEQUAN%TYPE;
L CANBO.LUONG%TYPE;
BEGIN
FOR I IN 3..10 LOOP
SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG
INTO MCB,TCB,MDT,QQ,L
FROM CANBO
WHERE MACB='cb'||I;
DBMS_OUTPUT.PUT_LINE(MCB||' '||TCB||' '||MDT||' '||QQ||' '||L);
END LOOP;
END;
--19--
DECLARE
I NUMBER(4);
BEGIN
FOR I IN 6..10 LOOP
INSERT INTO NGOAINGU(MACB,TENGOAINGU,TRINHDO)
VALUES ('cb'||I,'Tho Nhi Ky','A');
END LOOP;
END;
--20--
DECLARE
I NUMBER(4);
BEGIN
FOR I IN 6..10 LOOP
UPDATE NGOAINGU
SET TRINHDO = 'B'
WHERE MACB='cb'||I;
END LOOP;
END;
[b]--21--
DECLARE
CURSOR CS IS SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG FROM CANBO WHERE MACB <> 'cb1';
REC CS%ROWTYPE;
X NUMBER:=2;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN X=11;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.MADETAI||', '||REC.QUEQUAN||', '||REC.LUONG);
X:=X+1;
END LOOP;
CLOSE CS;
END;
--22--
CREATE TABLE DETAIPHU
(madetai nvarchar2(5)
CONSTRAINT madtPHU_pk PRIMARY KEY,
tendetai nvarchar2(25),
ngaydau date,
ngaycuoi date) ;
--
DECLARE
CURSOR CS IS SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI FROM DETAI;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
INSERT INTO DETAIPHU(MADETAI,TENDETAI,NGAYDAU,NGAYCUOI)
VALUES(REC.MADETAI,REC.TENDETAI,REC.NGAYDAU,REC.NGAYCUOI);
END LOOP;
CLOSE CS;
END;
--
SELECT * FROM DETAIPHU;
--23--
CREATE TABLE TOP_SAL
(
NAME_PS NVARCHAR2(20),
SAL_PS NUMBER
);
--
ACCEPT N PROMPT 'NHAP SO CAN BO N: '
DECLARE
CURSOR CS IS SELECT TENCB,LUONG FROM CANBO WHERE LUONG IS NOT NULL ORDER BY LUONG DESC;
REC CS%ROWTYPE;
X NUMBER:=0;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN X='&N';
INSERT INTO TOP_SAL(NAME_PS,SAL_PS)
VALUES (REC.TENCB,REC.LUONG);
X:=X+1;
END LOOP;
CLOSE CS;
END;
--
SELECT * FROM TOP_SAL;
--24--
--CANBO--
CREATE OR REPLACE PROCEDURE IN_THONG_TIN_CANBO
IS
CURSOR CS IS SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG FROM CANBO;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.MADETAI||', '||REC.QUEQUAN||', '||REC.LUONG);
END LOOP;
CLOSE CS;
END;
--
EXEC IN_THONG_TIN_CANBO
--DETAI--
CREATE OR REPLACE PROCEDURE IN_THONG_TIN_DETAI
IS
CURSOR CS IS SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI FROM DETAI;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MADETAI||', '||REC.TENDETAI||', '||REC.NGAYDAU||', '||REC.NGAYCUOI);
END LOOP;
CLOSE CS;
END;
--
EXEC IN_THONG_TIN_DETAI
--NGOAINGU--
CREATE OR REPLACE PROCEDURE IN_THONG_TIN_NGOAINGU
IS
CURSOR CS IS SELECT MACB,TENGOAINGU,TRINHDO FROM NGOAINGU;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENGOAINGU||', '||REC.TRINHDO);
END LOOP;
CLOSE CS;
END;
--
EXEC IN_THONG_TIN_NGOAINGU
--25--
CREATE OR REPLACE PROCEDURE IN_TENCANBO_THONGBAO
IS
CURSOR CS IS SELECT TENCB,LUONG FROM CANBO;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TENCB: '||REC.TENCB);
IF REC.LUONG>=7000000 THEN
DBMS_OUTPUT.PUT_LINE('LUONG DU CHI TIEU');
ELSE DBMS_OUTPUT.PUT_LINE('LUONG KHONG DU CHI TIEU');
END IF;
END LOOP;
CLOSE CS;
END;
--
EXEC IN_TENCANBO_THONGBAO
--26--
CREATE OR REPLACE PROCEDURE DS_CANBO_LAMCUNGDETAI(MDT IN CANBO.MADETAI%TYPE)
IS
CURSOR CS IS SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG FROM CANBO WHERE MADETAI=MDT;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.MADETAI||', '||REC.QUEQUAN||', '||REC.LUONG);
END LOOP;
CLOSE CS;
END;
--
EXEC DS_CANBO_LAMCUNGDETAI('dt3')
--27--
CREATE OR REPLACE PROCEDURE DS_CANBO_CUNGQUE(QQ IN CANBO.QUEQUAN%TYPE)
IS
CURSOR CS IS SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG FROM CANBO WHERE QUEQUAN=QQ;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.MADETAI||', '||REC.QUEQUAN||', '||REC.LUONG);
END LOOP;
CLOSE CS;
END;
--
EXEC DS_CANBO_CUNGQUE('Ha Noi')
--28--
CREATE OR REPLACE PROCEDURE DS_CANBO_NGOAINGU(TNN IN NGOAINGU.TENGOAINGU%TYPE)
IS
CURSOR CS IS SELECT CANBO.MACB,CANBO.TENCB,NGOAINGU.TENGOAINGU FROM CANBO JOIN NGOAINGU ON CANBO.MACB=NGOAINGU.MACB WHERE NGOAINGU.TENGOAINGU=TNN;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.TENGOAINGU);
END LOOP;
CLOSE CS;
END;
--
EXEC DS_CANBO_NGOAINGU('Anh')
--29--
CREATE OR REPLACE PROCEDURE DETAI_NOPCUNGNGAY
IS
CURSOR CS IS SELECT MADETAI,TENDETAI,NGAYDAU,NGAYCUOI FROM DETAI ORDER BY NGAYCUOI;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MADETAI||', '||REC.TENDETAI||', '||REC.NGAYDAU||', '||REC.NGAYCUOI);
END LOOP;
CLOSE CS;
END;
--
EXEC DETAI_NOPCUNGNGAY
--30--
CREATE OR REPLACE PROCEDURE DS_CANBO_LUONG(L IN CANBO.LUONG%TYPE)
IS
CURSOR CS IS SELECT MACB,TENCB,MADETAI,QUEQUAN,LUONG FROM CANBO WHERE LUONG>L;
REC CS%ROWTYPE;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO REC;
EXIT WHEN CS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(REC.MACB||', '||REC.TENCB||', '||REC.MADETAI||', '||REC.QUEQUAN||', '||REC.LUONG);
END LOOP;
CLOSE CS;
END;
--
EXEC DS_CANBO_LUONG(5000000)
--31--
--CANBO--
CREATE OR REPLACE PROCEDURE NHAP_DL_CANBO
(
MCB IN CANBO.MACB%TYPE,
TCB IN CANBO.TENCB%TYPE,
MDT IN CANBO.MADETAI%TYPE,
QQ IN CANBO.QUEQUAN%TYPE,
L IN CANBO.LUONG%TYPE
)
IS
BEGIN
INSERT INTO CANBO VALUES(MCB,TCB,MDT,QQ,L);
END;
--
EXECUTE NHAP_DL_CANBO('cb17','Thach Quang Thai','dt4','Ha Noi',6200000)
--DETAI--
CREATE OR REPLACE PROCEDURE NHAP_DL_DETAI
(
MDT IN DETAI.MADETAI%TYPE,
TDT IN DETAI.TENDETAI%TYPE,
ND IN DETAI.NGAYDAU%TYPE,
NC IN DETAI.NGAYCUOI%TYPE
)
IS
BEGIN
INSERT INTO DETAI VALUES(MDT,TDT,ND,NC);
END;
--
EXECUTE NHAP_DL_DETAI('dt14','Quan ly tien gui Agribank',to_date('06/05/2014','dd/mm/yyyy'),to_date('06/09/2014','dd/mm/yyyy'))
--NGOAINGU--
CREATE OR REPLACE PROCEDURE NHAP_DL_NGOAINGU
(
MCB IN NGOAINGU.MACB%TYPE,
TNN IN NGOAINGU.TENGOAINGU%TYPE,
TD IN NGOAINGU.TRINHDO%TYPE
)
IS
BEGIN
INSERT INTO NGOAINGU VALUES(MCB,TNN,TD);
END;
--
EXEC NHAP_DL_NGOAINGU('cb2','Duc','C')
--32--
CREATE OR REPLACE PROCEDURE NHAP_DL_DETAI_NGAYCUOI
(
MDT IN DETAI.MADETAI%TYPE,
NC IN DETAI.NGAYCUOI%TYPE
)
IS
BEGIN
UPDATE DETAI
SET NGAYCUOI=NC
WHERE MADETAI=MDT;
END;
--
EXEC NHAP_DL_DETAI_NGAYCUOI('dt14',to_date('25/10/2014','dd/mm/yyyy'))
--33--
CREATE OR REPLACE PROCEDURE NHAP_DL_CANBO_LUONG
(
MCB IN CANBO.MACB%TYPE,
L IN CANBO.LUONG%TYPE
)
IS
BEGIN
UPDATE CANBO
SET LUONG = L
WHERE MACB=MCB;
END;
--
EXEC NHAP_DL_CANBO_LUONG('cb7',9800000)
--34--
CREATE OR REPLACE PROCEDURE NHAP_DL_NGOAINGU_TRINHDO
(
MCB IN NGOAINGU.MACB%TYPE,
TNN IN NGOAINGU.TENGOAINGU%TYPE,
TD IN NGOAINGU.TRINHDO%TYPE
)
IS
BEGIN
UPDATE NGOAINGU
SET TRINHDO=TD
WHERE MACB=MCB AND TENGOAINGU=TNN;
END;
--
EXEC NHAP_DL_NGOAINGU_TRINHDO('cb2','Duc','A')
--35--
CREATE OR REPLACE PROCEDURE DEL_NGOAINGU
(
MCB IN NGOAINGU.MACB%TYPE
)
IS
BEGIN
DELETE FROM NGOAINGU
WHERE MACB=MCB;
END;
--
EXEC DEL_NGOAINGU('cb8')
--36--
CREATE OR REPLACE PROCEDURE HT_MDT
(
MCB IN CANBO.MACB%TYPE,
MDT OUT CANBO.MADETAI%TYPE
)
IS
BEGIN
SELECT MADETAI
INTO MDT
FROM CANBO
WHERE MACB=MCB;
END;
--
DECLARE MADT CANBO.MADETAI%TYPE;
BEGIN
HT_MDT('cb1',MADT);
DBMS_OUTPUT.PUT_LINE('MA DE TAI LA: '||MADT);
END;
--37--
CREATE OR REPLACE PROCEDURE HT_LUONG
(
MCB IN CANBO.MACB%TYPE,
L OUT CANBO.LUONG%TYPE
)
IS
BEGIN
SELECT LUONG
INTO L
FROM CANBO
WHERE MACB=MCB;
END;
--
DECLARE LUOG CANBO.LUONG%TYPE;
BEGIN
HT_LUONG('cb1',LUOG);
DBMS_OUTPUT.PUT_LINE('MA DE TAI LA: '||LUOG);
END;
--38--
--CB--
CREATE OR REPLACE FUNCTION TONGSO_CB
RETURN NUMBER
AS
TSCB NUMBER;
BEGIN
SELECT COUNT(*)
INTO TSCB
FROM CANBO;
RETURN TSCB;
END;
--
SELECT TONGSO_CB
FROM DUAL;
--DT--
CREATE OR REPLACE FUNCTION TONGSO_DT
RETURN NUMBER
AS
TSDT NUMBER;
BEGIN
SELECT COUNT(*)
INTO TSDT
FROM DETAI;
RETURN TSDT;
END;
--
SELECT TONGSO_DT
FROM DUAL;
--39--
--CB--
CREATE OR REPLACE FUNCTION TONGSO_NN
RETURN NUMBER
AS
TSNN NUMBER;
BEGIN
SELECT COUNT(DISTINCT TENGOAINGU)
INTO TSNN
FROM NGOAINGU;
RETURN TSNN;
END;
--
SELECT TONGSO_NN
FROM DUAL;
--40--
CREATE OR REPLACE FUNCTION LUONGMIN
RETURN NUMBER
AS
LMIN NUMBER;
BEGIN
SELECT MIN(LUONG)
INTO LMIN
FROM CANBO;
RETURN LMIN;
END;
--
SELECT LUONGMIN
FROM DUAL;
--41--
CREATE OR REPLACE FUNCTION LUONGMAX
RETURN NUMBER
AS
LMAX NUMBER;
BEGIN
SELECT MAX(LUONG)
INTO LMAX
FROM CANBO;
RETURN LMAX;
END;
--
SELECT LUONGMAX
FROM DUAL;
--42--
CREATE OR REPLACE FUNCTION LUONGTB
RETURN NUMBER
AS
LTB NUMBER;
BEGIN
SELECT AVG(NVL(LUONG,0))
INTO LTB
FROM CANBO;
RETURN LTB;
END;
--
SELECT LUONGTB
FROM DUAL;
--43--
CREATE OR REPLACE FUNCTION SL_CB_LUONG(L IN CANBO.LUONG%TYPE)
RETURN NUMBER
AS
SL NUMBER;
BEGIN
SELECT COUNT(*)
INTO SL
FROM CANBO
WHERE LUONG>L;
RETURN SL;
END;
--
SELECT SL_CB_LUONG(2000000)
FROM DUAL;
--44--
CREATE OR REPLACE FUNCTION SL_CB_DETAI(MDT IN CANBO.MADETAI%TYPE)
RETURN NUMBER
AS
SL NUMBER;
BEGIN
SELECT COUNT(*)
INTO SL
FROM CANBO
WHERE MADETAI=MDT;
RETURN SL;
END;
--
SELECT SL_CB_DETAI('dt1')
FROM DUAL;
--45--
CREATE OR REPLACE FUNCTION SL_CB_QQ(QQ IN CANBO.QUEQUAN%TYPE)
RETURN NUMBER
AS
SL NUMBER;
BEGIN
SELECT COUNT(*)
INTO SL
FROM CANBO
WHERE QUEQUAN=QQ;
RETURN SL;
END;
--
SELECT SL_CB_QQ('Ha Noi')
FROM DUAL;
--46--
--46--
--Tao TableSpace--
create tablespace myspace
logging
datafile 'userdata02.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--Tao user--
create user MY_USER identified by Password123;
grant CONNECT to MY_USER
GRANT CREATE ANY TABLE TO MY_USER
grant select any dictionary to MY_USER;
--Cap quyen su dung Tablespace cho user--
ALTER USER MY_USER QUOTA 100M ON MYSPACE;
GRANT UNLIMITED TABLESPACE TO MY_USER;
GRANT CREATE TRIGGER TO MY_USER
--Tao bang ngoaingu_history--
create table ngoaingu_history
(old_macb nvarchar2(5),
old_tengoaingu nvarchar2(17),
old_trinhdo nvarchar2(2),
new_macb nvarchar2(5),
new_tengoaingu nvarchar2(17),
new_trinhdo nvarchar2(2));
--Tao Trigger--
CREATE OR REPLACE TRIGGER NN_HIS
BEFORE UPDATE ON NGOAINGU FOR EACH ROW
BEGIN
INSERT INTO NGOAINGU_HISTORY(OLD_MACB,OLD_TENGOAINGU,OLD_TRINHDO,NEW_MACB,NEW_TENGOAINGU,NEW_TRINHDO)
VALUES(:OLD.MACB,:OLD.TENGOAINGU,:OLD.TRINHDO,:NEW.MACB,:NEW.TENGOAINGU,:NEW.TRINHDO);
END;
--Test--
update ngoaingu
SET TRINHDO='A'
WHERE MACB='cb7' AND TENGOAINGU ='Nga';
SELECT * FROM NGOAINGU_HISTORY;
--47--
CREATE OR REPLACE TRIGGER UPDATE_LUONG
BEFORE UPDATE OF LUONG ON CANBO FOR EACH ROW
BEGIN
CASE
WHEN :OLD.LUONG > :NEW.LUONG
THEN RAISE_APPLICATION_ERROR (
num => -20001,
msg => 'LUONG MOI KHONG DUOC THAP HON LUONG CU');
WHEN :OLD.LUONG*1.75 < :NEW.LUONG
THEN RAISE_APPLICATION_ERROR (
num => -20002,
msg => 'LUONG MOI KHONG DUOC CAO HON 1,75 LAN LUONG CU');
ELSE DBMS_OUTPUT.PUT_LINE('UPDATING...SALARY...');
END CASE;
END;
--TEST--
UPDATE CANBO
SET LUONG = LUONG*1.4
WHERE MACB='cb2';
--48--
CREATE OR REPLACE TRIGGER UPDATE_MADETAI
BEFORE UPDATE OF MADETAI ON CANBO
BEGIN
RAISE_APPLICATION_ERROR (
num => -20003,
msg => 'KHONG DUOC THAY DOI MA DE TAI CUA CAN BO');
END;
--TEST--
UPDATE CANBO
SET MADETAI='dt4'
WHERE MACB='cb1';
--49--
create table THEODOI_detai
(madetai nvarchar2(5),
tendetai nvarchar2(25),
ngayNOPDAU date,
NGAYGIAHAN DATE);
CREATE OR REPLACE TRIGGER UPDATE_NGAYCUOI
BEFORE UPDATE OF NGAYCUOI ON DETAI FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MA DT: '||:OLD.MADETAI||', TEN DT: '||:OLD.TENDETAI||', NGAY NOP DAU: '||:OLD.NGAYCUOI||', NGAY GIA HAN: '||:NEW.NGAYCUOI);
INSERT INTO THEODOI_DETAI
VALUES (:OLD.MADETAI,:OLD.TENDETAI,:OLD.NGAYCUOI,:NEW.NGAYCUOI);
END;
--TEST--
UPDATE DETAI
SET NGAYCUOI = NGAYCUOI+10
WHERE MADETAI='dt3';
--
SELECT * FROM THEODOI_DETAI;
--50--
CREATE OR REPLACE TRIGGER UPDATE_QUEQUAN
BEFORE UPDATE OF QUEQUAN ON CANBO
BEGIN
RAISE_APPLICATION_ERROR (
num => -20005,
msg => 'KHONG DUOC THAY DOI QUE QUAN CUA CAN BO');
END;
--TEST--
UPDATE CANBO
SET QUEQUAN='Hai Duong'
WHERE MACB='cb1';