K14 - HTTTA Học Viện Ngân Hàng
Bạn có muốn phản ứng với tin nhắn này? Vui lòng đăng ký diễn đàn trong một vài cú nhấp chuột hoặc đăng nhập để tiếp tục.
K14 - HTTTA Học Viện Ngân Hàng

Lớp Hệ Thống Thông Tin Quản Lý A - K14 - Học Viện Ngân Hàng
 
Trang ChínhTrang Chính  GalleryGallery  Latest imagesLatest images  Tìm kiếmTìm kiếm  Đăng kýĐăng ký  Đăng NhậpĐăng Nhập  
----- Bài Viết Gần Đây -----
Bài gửiNgười gửiThời gian
[22h 4/10] Soi kèo Arsenal vs Manchester United Bài tập chương 7 (PL/SQL) Icon_arrowSun Oct 04, 2015 11:14 pm
[22h 4/10] Soi kèo Arsenal vs Manchester United Bài tập chương 7 (PL/SQL) Icon_arrowSun Oct 04, 2015 11:14 pm
[19h30 4/10] Soi kèo Everton vs Liverpool Bài tập chương 7 (PL/SQL) Icon_arrowSun Oct 04, 2015 11:11 pm
[22h30 4/10] Soi kèo Bayern Munich vs Borussia Dortmund Bài tập chương 7 (PL/SQL) Icon_arrowSun Oct 04, 2015 7:32 pm
HTTT-K14 UTD VS KTG-TTD-K14! CHỜ TIN VUI TỪ Viettel-stadium! Bài tập chương 7 (PL/SQL) Icon_arrowWed Jun 17, 2015 9:54 pm
Bài tập chương 4 & 5 Bài tập chương 7 (PL/SQL) Icon_arrowSun Oct 26, 2014 6:18 pm
Bài tập chương 7 (PL/SQL) Bài tập chương 7 (PL/SQL) Icon_arrowMon Sep 22, 2014 10:07 pm
Bài tập chương 2 Bài tập chương 7 (PL/SQL) Icon_arrowTue Aug 19, 2014 5:14 pm
justin bieber Bài tập chương 7 (PL/SQL) Icon_arrowThu Aug 09, 2012 11:48 pm
Ôn thi CƠ SỞ LẬP TRÌNH Bài tập chương 7 (PL/SQL) Icon_arrowSat May 26, 2012 11:21 am
Bài Tập Chương 3 (còn update) Bài tập chương 7 (PL/SQL) Icon_arrowMon Feb 27, 2012 12:54 pm
TOURMALET CUỐI TUẦN,THỬ THÁCH THẬT SỰ CHO IS-K14 UTD Bài tập chương 7 (PL/SQL) Icon_arrowTue Feb 21, 2012 12:11 pm

 

 Bài tập chương 7 (PL/SQL)

Go down 
Tác giảThông điệp
Admin
Admin
Admin


Tổng số bài gửi : 9
Points : 4466
Join date : 02/02/2012
Age : 30

Bài tập chương 7 (PL/SQL) Empty
Bài gửiTiêu đề: Bài tập chương 7 (PL/SQL)   Bài tập chương 7 (PL/SQL) EmptyMon Sep 22, 2014 10:07 pm

--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';
Về Đầu Trang Go down
https://k14httta.forumvi.com
 
Bài tập chương 7 (PL/SQL)
Về Đầu Trang 
Trang 1 trong tổng số 1 trang
 Similar topics
-
» Bài Tập Chương 2 (từ 6 - 11)
» Bài tập chương 2
» Bài tập chương 4 & 5
» Bài Tập Chương 3 (còn update)

Permissions in this forum:Bạn không có quyền trả lời bài viết
K14 - HTTTA Học Viện Ngân Hàng :: HỌC TẬP :: Hệ quản trị cơ sở dữ liệu (Oracle)-
Chuyển đến 
Bài tập chương 7 (PL/SQL) Footer11
Create a forum on Forumotion | ©phpBB | Free forum support | Báo cáo lạm dụng | Thảo luận mới nhất
a