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 1 Chương 3: Cơ bản về Oracle NỘI DUNG MÔN HỌC 2 1 2 3 4 Chương 1. Tổng quan CSDL phân tán (4) Chương 2. Tổng quan CSDL hướng đối tượng Chương 3. Cơ bản về Oracle (4) Chương 4: Lập trình PL/SQL (4) 5 6 Chương 5: Procedure, Function (4) 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 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 3 Chương 3: Cơ bản về Oracle 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 4 Chương 3: Cơ bản về Oracle 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. 5 Chương 3: Cơ bản về Oracle 6 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 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 7 Chương 3: Cơ bản về Oracle 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 8 Chương 3: Cơ bản về Oracle 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; 9 Chương 3: Cơ bản về Oracle 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; 10 Chương 3: Cơ bản về Oracle 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 11 Chương 3: Cơ bản về Oracle 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 12 Chương 3: Cơ bản về Oracle 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 13 Chương 3: Cơ bản về Oracle 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 14 Chương 3: Cơ bản về Oracle 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; 15 Chương 3: Cơ bản về Oracle 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 [,]] ) 16 Chương 3: Cơ bản về Oracle Tạo bảng (tt) Trong đó: – Global Temporary: nếu có từ khóa này bảng được tạo sẽ là bảng tạm – Table_name: tên bảng do người dùng đặt – Column_name: tên cột trong bảng – Datatype: kiểu dữ liệu của cột – Constraint: từ khóa cho phép tạo ràng buộc trên cột – 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 CREATE [GLOBAL TEMPORARY] TABLE ( column_name datatype [CONSTRAINT constraint_def DEFAULT default_exp] [, column_name type [CONSTRAINT constraint_def DEFAULT default_exp]...] [,table_constraint [,]] ) 17 Chương 3: Cơ bản về Oracle 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 ) 18 Chương 3: Cơ bản về Oracle 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) ) 19 Chương 3: Cơ bản về Oracle 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 (Column_name1) Ràng buộc khóa ngoại cho cột sẽ tham chiếu tới Column_name đã tồn tại trong bảng Table_name. Mô tả: CONSTRAINT [] 20 Chương 3: Cơ bản về Oracle 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 21 Chương 3: Cơ bản về Oracle 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) ) 22 Chương 3: Cơ bản về Oracle Sửa bảng (tt)  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  Thêm thuộc tính bảng: ALTER TABLE HANGHOA ADD DONGIA FLOAT 23 Chương 3: Cơ bản về Oracle 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 24 Chương 3: Cơ bản về Oracle 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 25 Chương 3: Cơ bản về Oracle 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: Chương 3: Cơ bản về Oracle 26 DONVI MADV TENDV DV0001 Phong giam doc DV0002 Phong to chuc Bài tập (tt) Chương 3: Cơ bản về Oracle 27 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 Bài tập (tt) 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 GIUCHUCVU MACB MACV NGAYBD NGAYKT CB0001 CV0001 01-JAN-15 CB0003 CV0002 01-JAN-15 01-DEC-15 CB0004 CV0002 01-JAN-16 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 29 Chương 3: Cơ bản về Oracle