Bài giảng Cơ sở dữ liệu nâng cao - Chương 3: Cơ bản về Oracle - Nguyễn Thị Mỹ Dung

 Bài giảng 
 CƠ SỞ DỮ LIỆU 
 NÂNG CAO 
 Số tc: 2; LT: 20; Btập: 10 
 GV: Nguyễn Thị Mỹ Dung 
 Khối lớp: Đại học L2 
Chương 3: Cơ bản về Oracle 1 
 NỘI DUNG MÔN HỌC 
 1 Chương 1. Tổng quan CSDL phân tán (4) 
 2 Chương 2. Tổng quan CSDL hướng đối tượng 
 3 Chương 3. Cơ bản về Oracle (4) 
4 Chương 4: Lập trình PL/SQL (4) 
 5 Chương 5: Procedure, Function (4) 
 6 Chương 6: Thiết kế đối tượng (6) 
 7 Chương 7: Truy vấn trong CSDL HĐT (4) 
Chương 3: Cơ bản về Oracle 2 
 Chương 3: Cơ bản về ORACLE 
I. Các kiểu dữ liệu trong Oracle 
II. Các hàm thông dụng 
III. Thiết kế, xoá, sửa bảng 
IV. Bài tập thực hành 
 Chương 3: Cơ bản về Oracle 3 
 I. Các kiểu dữ liệu 
 Kiểu Diễn giải 
VARCHAR2 Dùng để khai báo chuỗi ký tự với 
 chiều dài thay đổi (2000 byte) 
VARCHAR Tương tự VARCHAR2 (Oracle khuyên 
 nên dùng Varchar2) 
NUMBER Dùng để chứa các mục tin dạng số 
 dương, số âm, số với dấu chấm động. 
 Number(p, s); Number(p), Number 
INTEGER Dùng để khai báo kiểu chuỗi ký tự với 
 độ dài biến đổi, (2Gb) 
DATE Dùng để chứa dữ liệu ngày, thời gian 
 Chương 3: Cơ bản về Oracle 4 
 Các kiểu dữ liệu (tt) 
 Kiểu Diễn giải 
CHAR Dùng để khai báo chuỗi ký tự với 
 chiều dài cố định (255 byte) 
FLOAT Dùng để khai báo kiểu dấu chấm động 
LONG Dùng để khai báo kiểu chuỗi ký tự với 
 độ dài biến đổi (2GB). Thường để 
 chứa văn bản. 
 Chương 3: Cơ bản về Oracle 5 
 II. Các hàm thông dụng 
 1. LENGTH (): Trả về chiều dài chuỗi 
 Select LENGTH ('lop DHCNTT') LEN 
 From Dual -- len = 10 
 2. INSTR(, , 
 ,) 
 Tìm vị trí chuỗi b trong chuỗi a bắt đầu từ vị trí n, 
 lần xuất hiện thứ m. 
 n<0: tìm từ phải sang trái 
 Select INSTR ('CORORATE FLOOR','OR',3,2) 
 From Dual -- vị trí 13 
Chương 3: Cơ bản về Oracle 6 
 Các hàm xử lý dữ liệu (tt) 
 3. SUBSTR (, , 
 ) 
 Cắt lấy chuỗi con của chuỗi a, lấy từ vị trí n về 
 phải m ký tự, nếu không chỉ m thì lấy cho đến 
 cuối chuỗi . 
 n<0: vị trí cắt đếm từ phải sang trái 
 Select SUBSTR('ORACLE',1,3) Substring 
 From Dual --ORA 
 4. CONCAT (,): Nối 2 chuỗi a, b 
 Select CONCAT ('Lop DHCNTT',' Khoa SP Toan-
Tin') Substring 
 From Dual -- Lop DHCNTT Khoa SP Toan-Tin 
 Chương 3: Cơ bản về Oracle 7 
 Các hàm xử lý dữ liệu (tt) 
 5. SUBOWER/UPPER/INITCAPSTR () 
  Chuyển chuỗi thành chuỗi viết thường /hoa /ký 
 tự đầu các từ viết hoa. 
 6. LTRIM/RTRIM(, ) 
  Loại bỏ chuỗi b ở bên trái/ phải chuỗi a 
 Select LTRIM(‘khoa su pham toan - tin’, ’khoa’) 
 From Dual; -- su pham toan - tin 
 Select RTRIM(‘LAST WORDx’, ’xy’) 
 FROM Dual; -- LAST WORD 
Chương 3: Cơ bản về Oracle 8 
 Các hàm xử lý dữ liệu (tt) 
 7. REPLACE (chuỗi a, chuỗi b [,chuỗi c]) 
  Thay tất cả các chuỗi b có trong chuỗi a bằng 
 chuỗi c 
 SELECT JOB, REPLACE (JOB, ‘SALESMAN’, 
 ‘SALESPERSON’) 
 FROM EMP 
 WHERE DEPTNO =30 OR DEPTNO =20; 
Chương 3: Cơ bản về Oracle 9 
 Các hàm xử lý dữ liệu (tt) 
 8. SYSDATE(chuỗi a, chuỗi b [,chuỗi c]) 
  Hàm lấy ngày tháng hiện hành của hệ thống 
 SELECT SYSDATE FROM DUAL 
 9. EXTRACT (YEAR|MONTH|DAY FROM <chuỗi 
ngày>) Tách lấy năm|tháng|ngày của 
 Select EXTRACT(Month FROM DATE 
 ‘2001-05-07’) 
 FROM Dual; -- 5 
 Select EXTRACT(DAY FROM Sysdate) 
 FROM Dual; 
 Chương 3: Cơ bản về Oracle 10 
 Các hàm xử lý dữ liệu (tt) 
 10. MONTHS_BETWEEN(, ) 
  Cho biết số tháng giữa và 
 Select MONTHS_BETWEEN 
 (DATE ‘1981-01-02’, DATE ‘1980-01-02’) 
 FROM Dual --12 
 11. ADD_MONTHS (, n) Thêm n tháng 
 vào 
 Select ADD_MONTHS (DATE ’1981-01-02’, 2) 
 FROM Dual; --3 
Chương 3: Cơ bản về Oracle 11 
 Các hàm xử lý dữ liệu (tt) 
 12. NEXT_DAY(, ) 
  Cho biết sau là ngày nào 
 Select NEXT_DAY(DATE ‘2015-04-10’, 'friday') 
 FROM Dual; --17-04-2015 
 13. LAST_DAY () Cho biết ngày cuối 
 cùng trong tháng chỉ bởi 
 Select LAST_DAY (DATE ‘2015-04-10’) 
 FROM Dual; --30-04-2015 
Chương 3: Cơ bản về Oracle 12 
 Các hàm xử lý dữ liệu (tt) 
 14. TO_CHAR(): Chuyển số sang chuỗi 
 TO_CHAR(, ):Chuyển 
ngày sang chuỗi theo định dạng 
 Select TO_CHAR(DATE'2011-09-20', 
 'DD-MM-YYYY') 
 FROM Dual; -- ‘20-09-2011’ 
 16. TO_NUMBER(): Chuyển chuỗi 
ký số sang dạng số 
 Select TO_NUMBER('123')+TO_NUMBER('34') 
 FROM Dual; => 157 
 Chương 3: Cơ bản về Oracle 13 
 Các hàm xử lý dữ liệu (tt) 
 17. TO_DATE(, <chuỗi 
định dạng>) 
 Chuyển chuỗi ngày sang dạng ngày theo định 
dạng 
 Select TO_DATE(’10-04-2015’,’DD-MM-YYYY’) 
 FROM Dual; -- 10-04-2015 
 Chương 3: Cơ bản về Oracle 14 
 Các hàm xử lý dữ liệu (tt) 
 18. Lựa chọn Case 
 CASE 
 WHEN THEN 
 [WHEN THEN 
 [ELSE ] 
 END 
 Đánh giá danh sách các điều kiện và trả về giá trị phù 
hợp 
 Select CASE 
 WHEN extract(day from sysdate)>15 
 THEN ‘Cuoi thang’ 
 ELSE ‘Dau thang’ END 
 From Dual; 
Chương 3: Cơ bản về Oracle 15 
 III. Thiết kế, xoá, sửa bảng 
 1. Tạo bảng 
 CREATE [GLOBAL TEMPORARY] TABLE 
 Table_name 
 ( Column_name Datatype [CONSTRAINT constraint_def 
 DEFAULT default_exp] 
 [, column_name type [CONSTRAINT constraint_def 
 DEFAULT default_exp]...] 
 [,table_constraint [,]] 
 ) 
Chương 3: Cơ bản về Oracle 16 
 Tạo bảng (tt) 
CREATE Trong đó: 
[GLOBAL TEMPORARY] – Global Temporary: nếu có từ khóa này 
 TABLE bảng được tạo sẽ là bảng tạm 
( column_name datatype 
[CONSTRAINT – Table_name: tên bảng do người dùng 
constraint_def đặt 
DEFAULT default_exp] – Column_name: tên cột trong bảng 
[, column_name type 
[CONSTRAINT – Datatype: kiểu dữ liệu của cột 
constraint_def – Constraint: từ khóa cho phép tạo ràng 
DEFAULT default_exp]...] buộc trên cột 
[,table_constraint [,]] 
) – Contraint_def: ràng buộc của cột 
 – Default: từ khóa cho phép xác định giá 
 trị mặc định cho cột 
 – Default_exp: dữ liệu của cột nhận giá trị 
 default_exp nếu người dùng không 
 nhập dữ liệu vào cột 
 – Table_constraint: ràng buộc của toàn 
 bảng dữ liệu 
 Chương 3: Cơ bản về Oracle 17 
 Tạo bảng (tt) 
 CREATE TABLE SINHVIEN ( 
 MASV CHAR(10) PRIMARY KEY, 
 HOTENSV VARCHAR2(40), 
 NAMSINH INT, 
 QUEQUAN VARCHAR2(40), 
 HOCLUC FLOAT 
 ) 
 CREATE TABLE DETAI( 
 MADT CHAR(10) PRIMARY KEY, 
 TENDT VARCHAR2(40), 
 CHUNHIEM VARCHAR2(40), 
 KINHPHI NUMBER 
 ) 
Chương 3: Cơ bản về Oracle 18 
 Tạo bảng (tt) 
 CREATE TABLE SVDT ( 
 MASV CHAR(10) NOT NULL, 
 MADT CHAR(10) NOT NULL, 
 NOIA_D CHAR(40), 
 KETQUA FLOAT, 
 PRIMARY KEY (MASV, MADT), 
 CONSTRAINT SVDT_SV FOREIGN KEY 
 (MASV) REFERENCES SINHVIEN (MASV), 
 CONSTRAINT SVDT_DT FOREIGN KEY 
 (MADT) REFERENCES DETAI(MADT) 
 ) 
Chương 3: Cơ bản về Oracle 19 
 Thiết kế, xoá, sửa bảng (tt) 
 Kiểu ràng buộc Diễn giải 
NULL/NOT NULL Cho phép/không cho phép cột chứa giá 
 trị rỗng 
CHECK (Criteria) Giá trị nhập vào cột phải thỏa Criteria 
UNIQUE Giá trị nhập vào cột phải là duy nhất 
PRIMARY KEY Ràng buộc khóa chính cho cột 
REFERENCES Ràng buộc khóa ngoại cho cột sẽ tham 
 chiếu tới Column_name đã tồn tại trong 
(Column_name1) bảng Table_name. 
Mô tả: 
CONSTRAINT [] 
Chương 3: Cơ bản về Oracle 20 
 Thao tác CSDL (tt) 
 2. Sửa bảng 
 Thêm cột: ALTER TABLE ADD 
 [] 
 Xóa cột: ALTER TABLE 
 DROP COLUMN 
 Thay đổi kiểu dữ liệu cột: 
 ALTER TABLE MODIFY 
Chương 3: Cơ bản về Oracle 21 
 Sửa bảng (tt) 
 Thêm RBTV: ALTER TABLE ADD 
 CONSTRAINT , 
 CONSTRAINT , 
 Xóa RBTV: 
 ALTER TABLE DROP 
 Ví dụ: 
 CREATE TABLE HANGHOA( 
 MAHG CHAR(10), 
 TENHANG CHAR(50), 
 DVT CHAR(5) 
 ) 
Chương 3: Cơ bản về Oracle 22 
 Sửa bảng (tt) 
  Thêm thuộc tính bảng: 
 ALTER TABLE HANGHOA ADD 
 DONGIA FLOAT 
  Mở rộng kiểu dữ liệu cho thuộc tính bảng: 
 ALTER TABLE HANGHOA 
 MODIFY DONGIA LONG 
  Xóa thuộc tính bảng: 
 ALTER TABLE HANGHOA 
 DROP COLUMN DVT 
Chương 3: Cơ bản về Oracle 23 
 Sửa bảng (tt) 
  Thay đổi RBTV: 
 ALTER TABLE SINHVIEN ADD 
 CONSTRAINT fk_sv FOREIGN KEY (MAKHOA) 
 REFERENCES KHOA(MAKHOA) 
 ALTER TABLE HANGHOA ADD CONSTRAINT fk_HH 
 PRIMIRY KEY (MAHG) 
  Xóa ràng buộc toàn vẹn trong bảng 
 ALTER TABLE SINHVIEN DROP CONSTRAINT fk_sv 
Chương 3: Cơ bản về Oracle 24 
 Thiết kế, xoá, sửa bảng (tt) 
 3. Xoá bảng 
 DROP TABLE 
 [CASCADE CONSTRAINT] 
 CASCADE CONSTRAINT sẽ xóa luôn tất cả các 
ràng buộc toàn vẹn liên quan đến bảng cần xóa. 
 DROP TABLE HANGHOA 
 CASCADE CONSTRAINT 
 Chương 3: Cơ bản về Oracle 25 
 IV. Bài tập 
 Cho CSDL như sau: 
 DONVI (MADV, TENDV) 
 CANBO (MACB, HOTENCB, MADV) 
 CHUCVU (MACV, TENCV, HESO) 
 GIUCHUCVU (MACB, MACV, NGAYBD, 
 NGAYKT) 
 Yêu cầu: 
 1. Tạo bảng và tạo RBTV cho các bảng. 
 2. Nhập dữ liệu cho bảng như sau: 
 DONVI MADV TENDV 
 DV0001 Phong giam doc 
 Chương 3: Cơ bản về Oracle DV0002 Phong to chuc 26 
 Bài tập (tt) 
CHUCVU MACV TENCV HESO 
 CV0001 Giam doc 3.5 
 CV0002 Truong phong 3.0 
 CV0003 Pho phong 2.7 
 CV0004 Ke toan truong 2.7 
CANBO MACB HOTENCB MADV 
 CB0001 Nguyen Van A DV0001 
 CB0002 Le Thi B DV0001 
 CB0003 Tran Van C DV0001 
 CB0004 Bach Tuan Kiet DV0002 
 CB0005 Thai Binh An DV0001 
 CB0006 Dang Van Khang DV0002 
Chương 3: Cơ bản về Oracle 27 
 Bài tập (tt) 
GIUCHUCVU MACB MACV NGAYBD NGAYKT 
 CB0001 CV0001 01-JAN-15 
 CB0003 CV0002 01-JAN-15 01-DEC-15 
 CB0004 CV0002 01-JAN-16 
 3. Tìm thông tin CB giữ chức vụ từ năm 2015 
 4. Tìm thông tin CB giữ chức vụ dưới 1 năm 
 5. Tìm thông tin CB vẫn còn giữ chức vụ. 
 Chương 3: Cơ bản về Oracle 28 
 Tổng kết chương 
 - Giới thiệu Oracle 
 - Các kiểu dữ liệu trong Oracle 
 - Cách thiết kế bảng, sửa, xoá bảng 
 - Nhập và xem dữ liệu bảng 
Chương 3: Cơ bản về Oracle 29