I. KIẾN THỨC CHUNG

1. Các từ khóa

1. Toàn bộ quyền: ALL PRIVELEGES – (grant)
2. Không giới hạn: UNLIMITED – (quota)
3. Xóa hết dữ liệu các bản: DROP ANY TABLE
4. Tất cả người dùng: PUBLIC
5. Gán bất cứ quyền gì cho người dùng khác: GRANT ANY ROLE
6. Quyền import/export dữ liệu: imp_full_database / exp_full_database 
7. Quyền xóa, Thực thi, Lấy dữ liệu: DELETE_CATALOG_ROLE / EXCUTE … / SELECT…

2. Các tùy chọn

- WITH GRANT OPTION: cho phép tiếp tục cấp quyền, ko dùng cho Role – (grant)
- WITH ADMIN OPTION: người dùng có toàn quyền (create + grant role)
- WITH CHECK OPTION: các dòng ko di cư ra khỏi VIEW
- RESTRICT: (mặc định), ngưng thực thi nếu có phụ thuộc – (revoke + drop)
- CASCADE: xóa hết – (revoke + drop + with … check option)
- CASCADE CONSTRAINTS: xóa toàn bộ ràng buộc, tham chiếu (drop table)
- EXTERNALLY: xác nhận user bởi hệ điều hành - (identified… + role)

3. Một số cú pháp

- Tăng hạn mức cho người dùng Kita thành 2M:

ALTER USER Kita QUOTA 2M on users

- Xem thông tin hạn mức người dùng Kita:

SELECT tablespace_name, blocks, bytes
FROM dba_ts_quotas
WHERE username=’Kita’

- Lấy thông tin tài khoản của người dùng

SELECT username, account_status, temporary_tablespace
FROM dba_users

- Liệt kê các “quyền đối tượng” được gán cho Kita:

SELECT * FROM dba_tab_privs
WHERE GRANTEE = ‘Kita’

- Liệt kê các “quyền hệ thống” được gán cho user và role:

·         Select * From dba_sys_privs

- Xem thông tin các quyền cấp phát cho người dùng:

·         Select role, password_required From dba_roles

- Muốn biết user hiện hành có quyền gì: Select * From session_privs
- Thu hồi role từ tất cả người dùng: revoke tên_Role from PUBLIC
- Gán quyền hệ thống cho tất cả người dùng: grant select, update to PUBLIC
- Tạo bảng có cấu trúc giống bảng nhân viên: create table nv_backup AS select * from nhanvien
- Tạo ràng buộc khóa ngoại:

·         alter table nhavien ADD CONSTRAINT nv_fk foreign key (mach) referencescuahang (mach)

- Xóa ràng buộc khóa ngoại: alter table nhanvien drop constraint nv_fk

4. Cách Import/Export

- Các tùy chọn: Constrains: các ràng buộc, File : tên tập tin DUMP, Log : Tập tin nhật kí,Full : Toàn bộ CSDL, Row : Exp dữ liệu ra file DUMP, Grants : các quyền, Indexes : chỉ mục, Owner : tên user cần thực hiện Exp, Table : các bảng, Fromuser…Touser: Imp

- Sử dụng Y hoặc N để thêm điều kiện:

Ví dụ chỉ Exp các bảng: tables = y
- Cấu trúc Epx:

·         userid=…/…  file= tên.dmp  log=tên.log  owner=kita (toàn bộ schema của Kita)tables=nhanvien (chỉ exp bảng nhân viên)

- Cấu trúc Imp:

·         imp userid=…/…  file=patch.dmp fromuser=…. touser=….

5. Biểu thức chính quy

- regexp_LIKE : so khớp, i ko phân biệt, c phân biệt hoa thường
- regexp_REPLACE : thay thế, nguồn-mẫu-thay bằng
- regexp_INSTR : trả về vị trí, trả về 0 nếu ko có, nguồn-mẫu-vị trí-từ thứ
- regexp_SUBSTR : cắt chuỗi, nguồn-mẫu-vị trí-từ thứ, ko có trả NULL

·         LIKE: sử dụng trong WHERE và các ràng buộc toàn vẹn.

·         Còn lại dùng cho SELECT

·         Sử dụng From DUAL

- Vd: Định nghĩa số điện thoại di động: 

o    alter table nhanvien add constraint ck_sdtdd check (REGEXP_LIKE(Sdt, ’0\d{9,10}’))

6. Các hàm xử lý chuỗi

- Độ dài: LENGTH (a)
- Vị trí xuất hiện: INSTR (a,b,pos, i)
- Lấy kí tự: SUBSTR (a, pos, x)
- Ghép chuỗi: CONCAT(a,b)
- Dạng chữ: LOWER,UPPER (a)
- Cắt khỏi chuỗi (cả 2 bên, ở giữa ko cắt): TRIM(a,b), bên trái LTRIM(a,b), bên phảiRTRIM(a,b)

7. Các hàm xử lý ngày tháng

- Trả về ngày, tháng, năm của dữ liệu: EXTRACT(year|month|day FROM <ngày>)

o    select EXTRACT (month from ’1992-09-04′) —> 09

- Cộng tháng: ADD_MONTHS (ngày, tháng)

o    select add_months (sysdate,4)

- Số tháng giữa 2 ngày: MONTHS_BETWEEN (ngày 1, ngày 2)

- Ngày hiện tại: SYSDATE

8. Các hàm chuyển kiểu

- Số về kiểu: TO_CHAR(số)
- Ngày về chuỗi: TO_CHAR(ngày, định_dạng)

o    select TO_CHAR (sysdate, ‘dd-mm-yy hh24:mi:ss’) —> 09/04/1992 09:40:55

- Chuỗi thành ngày: TO_DATE(chuỗi_ngày, định_dạng)

9. Lập trình PL/SQL

- Thuộc tính:

·         Lấy kiểu của bảng: biến_bảng%ROWTYPE

·         Lấy kiểu của biến/trường: biến cột|biến%TYPE

- Định nghĩa kiểu dữ liệu: SUBTYPE tên IS ràng_buộc

subtype birthday is date not null;
myBir birthday := ‘09-Apr-1992‘;

- Kiểu TABLE: giống mảng:

·         TYPE tên_kiểu_bảng IS TABLE OF kiểu INDEX BY BYNARY_INTEGER tên_biến tên_kiểu_bảng

10. Con trỏ

- Nếu dùng FOR i in (Select…) thì khi dùng phải có i.___. Không cần khai báo biến, sử dụng luôn biến sẵn có.

for i in (select * from gd_0506 where magv = ma)
      loop
          if i.siso <= 80 then tong := tong + i.sotiet * 1;
          . . . .
          else tong := tong + i.sotiet * 1.5;
          end if;
      end loop;

Con trỏ sử dụng tham số truyền vào, ví dụ:

declare
    cursor ctro(ma emp.empno%type) is select * from emp where empno = ma;
begin
    for i in ctro(‘&Nhap_ma’)
    loop
        ………….
    end loop;

II. BUỔI 1 – LÀM QUEN VỚI ORACLE

1. Các bước thực hiện

- Copy tên máy vào Net manager (2 chỗ)

- Start các Services

- Mở Oracle à Unlock người dùng Scott

                        alter user scott identified by scott accout unlock;

- Kiểm tra (Net manager / Test)

2. Tạo người dùng mới

create user test1 identified by test1 default tablespace users
(temporary tablespace temp) quota 5M on users;

3. Cấp quyền: kết nối + Import + tạo bảng

grant create session, create table, imp_full_database to Kita;

4. Import dữ liệu

Imp userid=kita/kita file=ENROLLMENT.dmp fromuser=enrollment touser=kita

5. Thao tác với bảng

- Tạo bảng:

create table hocsinh
(
        mssv char(7) primary key,
        hoten varchar2(40),
        namsinh date default sysdate,
        malop varchar2(8) references Lop(MaLop)
)

III. BUỔI 2 – QUẢN LÝ BẢNG DỮ LIỆU

1. Giá trị mặc định

QGia varchar(20) default ‘VietNam’

2. Sửa cấu trúc bảng từ 8 số —-> (xxx) xxx-xxxx

alter table donvi modify tel char(14)
update donvi set tel= regexp_replace(trim(‘  ‘ from tel), ’(\d{1})(\d{3})(\d{4})’, ‘(00\1) \2-\3′);

3. Thêm ràng buộc, cập nhật dữ liệu bảng

alter table DONVI  add constraint ck_dvi_tel check(regexp_like(tel, ‘\(\d{3}\)\d{3}-\d{4}’))

- Tất cả khách hàng đã từng mua thẻ bảo hiểm y tế của Sở Điện Lực muốn mua tiếp 6 tháng bảo hiểm y tế này từ ngày hôm nay. hãy viết lệnh insert để thêm những bảo hiểm này một cách tự động

insert into thebh (maloai, makh, ngaybd, thoihan, ngaykt, conhl)
select distinct a.maloai, a.makh, sysdate, 6, add_months(sysdate,6), 1
from thebh a, kh b, donvi c, loaibh d
where a.makh = b.makh 
and b.madv=c.madv 
and d.maloai=a.maloai 
and tendv=’SO DIEN LUC’ 
and tenloai=’BH Y TE’;

IV. THỰC HÀNH BUỔI 3

1. Dòng lệnh

- Hiện kết quả: set serveroutput on;
- Nhận dữ liệu do người dùng nhập: ten := &Nhap_ten_vao
- In dữ liệu ra: dbms_output.put_line(‘….’ || biến)
- Dòng mới: dbms_output.new_line

- Vòng lặp chạy ngược: FOR i IN REVERSE 1 . . 5

- Tính số năm/tuổi: EXTRACT (year from sysdate) - EXTRACT (year from birth)

- Giá trị trống: IS NULL

-

2. Thuộc tính

- Tạo biến có kiểu dữ liệu tương tự: vSalary employees.Salary%type

3. Truy vấn dữ liệu

Select last_name, salary Into vName, vSalary
From emloyees
Where employees_id = 120;

4. Các cấu trúc lệnh điều khiển

* IF

IF …. THEN
lệnh_1
ELSIF …. THEN
lệnh_2
ELSE
lệnh_3
END IF;

* CASE

CASE biến
WHEN ..1… THEN lệnh_1;
WHEN …2.. THEN lệnh_2;
ELSE lệnh_3
END CASE;

* Lặp LOOP

LOOP
Lệnh;
EXIT WHEN điều-kiện
END LOOP;

* WHILE

WHILE điều kiện
LOOP
Lệnh;
END LOOP;

* FOR

FOR chạy IN [REVERSE] nhỏ . . lớn
LOOP
lệnh;
END LOOP;

* GOTO

<<nhãn>>
…
lệnh;
GOTO nhãn;

5. Con trỏ

- Dùng trực tiếp, ko cần khai báo, ko dùng FETCH: FOR emp_rec IN Retire

- Chuyển những nhân viên qua bảng khác, và xóa khỏi bảng hiện tại:  DELETE FROMEmp

V. THỰC HÀNH BUỔI 4

1. Thủ tục

- Trả về giá trị nào đó… thì trong phần khai báo phải có tên giá trị đó, chú ý sử dụng thêm OUT

- Sử dụng OUT nếu trả về nội dung có sẵn trong bảng. P.128

- Trả về lương thấp nhất và cao nhất ứng với mã công việc này

Create or replace procedure MucLuong (p_jobid jobs.job_id%TYPE, 

        min_lg OUT jobs.min_salary%type, max_lg OUTjobs.max_salary%type)

- Viết một thủ tục để thực hiện việc tăng lương cho các nhân viên với các tham số đầu vào là mã phòng ban và phần trăm lương tăng thêm.

CREATE OR REPLACE PROCEDURE raise_salary(dno NUMBER, percent NUMBER DEFAULT 0.5) 
IS 
BEGIN 
    UPDATE scott.emp SET sal = sal * ((100 + percent)/100) 
    WHERE deptno = dno; 
    COMMIT; 
END;

·         Sử dụng EXECUTE để thực thi

           set serveroutput on;

           EXECUTE raise_salary(20,10);

·         Gọi thủ tục có trả về:

set serveroutput on;
declare
begin
    GoiTen(‘CT00′);
end;

2. Hàm

- Nếu hàm không có tham số đầu vào thì không để ( . . . ), chỉ có tên hàm thôi!

create or replace function TAMUNGCN return nvarchar2

- Gọi hàm :   select TongLuong(30) as Total from DUAL;

- Gọi hàm và truyền vào biến:

     DECLARE 
            kq  NUMBER;  //result  NUMBER := get_dept_salary(30);
      BEGIN    
            kq  := TongLuong(30); 
            dbms_output.put_line(kq); 
      END;

- Viết một hàm để lấy tổng tiền lương của một phòng ban nào đó

create or replace function tongluong(ma number) return number
is
    sumsal number :=0 ;
begin
   select  sum(sal) into sumsal from emp 
   where deptno = ma;
  return sumsal;
end;

3. Ngoại lệ

exception 
    when no_data_found then 
       dbms_output.put_line (‘Ma cong viec ‘ || p_jobid ||  ‘ khong tim thay’); 
    when others then 
       dbms_output.put_line (‘Khong biet loi gi’);

VI. THỰC HÀNH BUỔI 5

- Before : chạy trigger trước, kiểm tra dữ liệu thêm vào có họp lệ hay không rồi mới thêm

- After: chạy trigger sau, thường để lưu trữ thông tin khi xóa, sửa

- Cấu trúc 1:

CREATE OR REPLACE TRIGGER Cau1
AFTER INSERT OR UPDATE OF SAL, DEPTNO ON EMP
DECLARE
–Khai báo
BEGIN
…………
END;

- Cấu trúc 2:

CREATE OR REPLACE TRIGGER Cau2
AFTER UPDATE OF SAL on EMP
FOR EACH ROW 
BEGIN
//(:new.Empno, :old.sal, :new.sal);
END;

Chú ý:Chỉ thuộc tính có trong bảng thay đổi mới có thể dùng :new. hoặc :old., còn những thuộc tính khác phải khai báo biến và truy xuất từ các bảng khác dựa trên các :new . (Như Câu 3 đề thi)