Bài 1. Giới thiệu chung
Bài này đề cập các nội dung sau
- Viết các câu lệnh SELECT
- Xử lý các trường dữ liệu NULL
- Đặt lại tên cột bằng alias
- Kết hợp các cột
- Thực hiện các công thức số học
Các khái niệm được đề cập
lệnh SELECT, bảng (table), cột (column), bản ghi (record), SQL*Plus, ký tự '*', giá trị NULL, hàm nvl(), heading, alias, khóa từ "as", bảng DUAL
Viết các câu lệnh SELECT
Câu lệnh SELECT (lệnh truy vấn dữ liệu - query statement) là lệnh phổ biến nhất khi làm việc với CSDL. Lệnh SELECT thường được dùng để lấy/truy vấn dữ liệu từ CSDL Oracle, các dữ liệu này được lưu trong các Table (bảng).
Một Table có thể được hiểu tương tự như một bảng tính Excel, bao gồm các cột và các dòng. Các cột (column) có số lượng nhất định, biểu diễn cấu trúc của table. Các dòng hay còn gọi là các bản ghi (record) là các mục dữ liệu mà table chứa đựng.
Để chạy các câu lệnh SELECT trong loạt bài này, ta sẽ dùng công cụ chuẩn của Oracle là SQL*Plus. Sau khi đăng nhập thành công, SQL*Plus đầu tiên hiện thị thông tin về phiên bản của SQL*Plus, ngày tháng hiện hành, phiên bản của CSDL Oracle mà ta đang đăng nhập vào, và các tùy chọn (server option) mà CSDL có cài đặt.
Mã:
SQL*Plus: Release 8.0.6.0.0 - Production on Wed May 18 11:10:44 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select * from students;
NAME DATE_OF_B G STUDENT_ID EXAMS GPA
------------- --------- - ---------- --------- ---------
Smith 04-JUL-65 F 2 2 5.8
Green 04-JUL-65 F 3 2 3.3
Taylor 01-JAN-77 F 4 1 .8
Bane 01-JAN-55 M 5 4 4
Câu lệnh SELECT trên lấy ra tất cả các cột và các bản ghi của bảng STUDENT. Chú ý cách chúng ta sử dụng ký tự '*' để truy vấn tất cả các cột của một bảng.
Thành phần đầu tiên của câu lệnh SELECT là mệnh đề SELECT, mệnh đề chỉ thị cho ORACLE biết là cần thực hiện một lệnh trả về dữ liệu. Thành phần thứ hai là danh sách các cột mà người sử dụng cần quan tâm. Ở ví dụ trên, ký tự '*' (wildcard) được sử dụng để lấy tất cả các cột của bảng. Thành phần cuối cùng là mệnh đề FROM để chỉ ra bảng cần lấy dữ liệu. Tên của của bảng trong nhiều trường hợp cần đi kèm với tên shema hay tên người dùng sở hữu bảng đó.
Xử lý các trường dữ liệu NULL
Nhiều khi cột dữ liệu cần lấy không chứa dữ liệu, và Oracle xử lý bằng cách trả về giá trị NULL. Về mặt ý nghĩa thì giá trị NULL hàm ý là một tập hợp rỗng. Oracle cung cấp hàm nvl() để giúp ta chỉ ra giá trị mặc định cho cột không mang dữ liệu (hay có giá trị là NULL).
Ta xét ví dụ:
Mã:
SQL> select * from enrolments;
SURNAME FNAME AGE G
-------------------- -------------------- --------- -
Morris Frank 18 N
Yale Tony 72
Brown Mary 71 Y
Wirth Peter 17 N
Klawitter Eric 75 Y
Wong Ronnie 72
6 rows selected.
SQL> select surname, fname,age, nvl(graduate,'N') from enrolments;
SURNAME FNAME AGE N
-------------------- -------------------- --------- -
Morris Frank 18 N
Yale Tony 72 N
Brown Mary 71 Y
Wirth Peter 17 N
Klawitter Eric 75 Y
Wong Ronnie 72 N
6 rows selected.
Nếu giá cột được chỉ ra trong hàm nvl() là khác NULL thì giá trị trong cột đó được trả về, nếu giá trị trong cột là NULL thì giá trị thay thế sẽ được trả về. Cú pháp của hàm nvl() như sau:
Mã:
nvl(column_name,value_if_null)
Đặt lại tên cột bằng alias
Ta có thể nhận xét rằng khi Oracle trả lại kết quả truy vấn, Oracle tạo một tên đầu mục (heading) cho mỗi cột. Các đầu mục này chính là tên các cột được chỉ ra trong câu lệnh SELECT. Tuy nhiên có nhiều tình huống ta không muốn các đầu mục có cùng tên với các cột; chẳng hạn như khi tên cột quá dài gây bất tiện cho việc hiển thị, ...; khi đó ta có thể dùng các alias để chỉ định tên cho các đầu mục.
Mã:
select surname, fname firstname, age, nvl(graduate,'N') from enrolments;
SURNAME FIRSTNAME AGE N
-------------------- -------------------- --------- -
Morris Frank 18 N
Yale Tony 72 N
Brown Mary 71 Y
Wirth Peter 17 N
Klawitter Eric 75 Y
Wong Ronnie 72 N
6 rows selected.
Bạn có thể dùng từ khóa as để dùng alias một cách tường minh hơn như sau:
Mã:
select surname, fname as firstname, age, nvl(graduate,'N')
from enrolments;
Kết hợp các cột
Trong kết quả của truy vấn, ta có thể thực hiện các phép toán trên các cột để tạo thêm các đầu mục dữ liệu mới.
Ví dụ:
Mã:
select fname||' '||surname Name,age, nvl(graduate,'N')
from enrolments;
NAME AGE N
----------------------------------------- --------- -
Frank Morris 18 N
Tony Yale 72 N
Mary Brown 71 Y
Peter Wirth 17 N
Eric Klawitter 75 Y
Ronnie Wong 72 N
6 rows selected.
Thực hiện các công thức số học
Bạn cũng có thể thi hành tất cả các loại phép toán số học trên các cột lấy về
(+, -, /, *, ...).
Ví dụ:
Mã:
select balance * 1.5 from loans;
BALANCE*1.5
-----------
30000
33000
10500
259.485
Oracle cũng cho phép bạn thực hiện các phép toán trên các dữ liệu biết trước. Điều này liên quan tới một bảng đặc biệt trong Oracle, bảng DUAL. Bảng DUAL là một bảng rỗng (empty table) được dùng để hợp lý hóa các yêu cầu của câu lệnh SELECT.
Ví dụ: thực hiện phép tính 27 chia cho 9
Mã:
select 27/9 from dual;
27/9
---------
3
Bài 2. Các hàm xử lý số liệu thời gian và sử dụng các hàm trong câu lệnh SELECT
Nội dung của bài viết
- Các hàm xử lý dữ liệu thời gian.
- Xử dụng các hàm kèm bên trong các câu lệnh SELECT.
- Và miêu tả tóm tắt về các "single row function" (hàm đơn hàng)
Các khái niệm được đề cập
hàm xử lý dữ liệu thời gian (date function), hàm last_day(), hàm add_month(), hàm months_between(), hàm đơn hàng (single row function), hàm decode(), hàm xử lý dữ liệu chuỗi (string function), hàm rpad() và lpad(), hàm upper() và lower(), hàm length, hàm substr(), hàm xử lý dữ liệu kiểu số (math function), hàm floor(), hàm ceil(), hàm abs(), hàm round(), hàm mod(), hàm sqrt(), hàm sign(), hàm trunc()
Các hàm xử lý dữ liệu thời gian
Oracle cung cấp khá nhiều hàm giúp xử lý thuận tiện các dữ liệu thời gian. Một hàm mà chúng ta có lẽ thấy khá hữu dụng là hàm last_day(), hàm này trả về ngày cuối cùng của một tháng nào đó.
Ví dụ:
Mã:
SQL> select last_day('01-OCT-99') from dual;
LAST_DAY(
---------
31-OCT-99
Trong ví dụ trên, hàm last_day() đã trả về ngày cuối cùng của tháng 10 năm 1999.
Hai hàm tiếp theo là add_month() và months_between(). Hàm add_month() có 02 tham số, một tham số chỉ ra một ngày làm mốc và một tham số chỉ ra số lượng các tháng cần dịch.
Ví dụ: 11 tháng sau ngày 01/10/1999 là ngày nào?
Mã:
SQL> select add_months('01-OCT-99',11) from dual;
ADD_MONTH
---------
01-SEP-00
Hàm months_between() tính ra khoảng thời gian theo tháng giữa hai ngày nào đó.
Ví dụ:
Mã:
SQL> select months_between('01-NOV-99','01-OCT-99') from dual;
MONTHS_BETWEEN('01-NOV-99','01-OCT-99')
---------------------------------------
1
SQL> select months_between('25-DEC-99','01-OCT-99') from dual;
MONTHS_BETWEEN('25-DEC-99','01-OCT-99')
---------------------------------------
2.7741935
Sử dụng các hàm kèm bên trong các câu lệnh SELECT
Ngoài việc thi hành các phép toán số học trên các cột lấy (đã đề cập ở bài 1), Oracle còn cho phép ta sử dụng các hàm đi kèm trong các câu lệnh SELECT. Các hàm này phải là các hàm đơn hàng nghĩa là giá trị trả về phải là đơn trị, không được là một tập hợp (chẳng hạn như trả về một cursor). Hàm nvl() mà chúng ta đã biết chính là một hàm như vậy. Dưới đây ta sẽ lần lượt làm quen thêm với một số hàm khác khá hữu dụng.
Hàm decode() có cách xử lý khá giống cấu trúc IF-THEN_ELSE. Ta xét ví dụ sau:
Mã:
select surname, age,decode(graduate,'Y',
'Graduate','N',
'Undergraduate','Undergraduate') Graduate
from enrolments;
SURNAME AGE GRADUATE
-------------------- --------- -------------
Morris 18 Undergraduate
Yale 72 Undergraduate
Brown 71 Graduate
Wirth 17 Undergraduate
Klawitter 75 Graduate
Wong 72 Undergraduate
Hàm decode() xử lý tương đương như sau: nếu giá trị của cột GRADUATE bằng 'Y' thì trả về chuỗi 'Graduate', nếu giá trị của cột GRADUATE bằng 'N' thì trả về 'Undergraduate', nếu không (trong trường hợp này tức là khi giá trị của cột GRADUATE là NULL) thì trả về 'Undergraduate'.
Có thể thấy hàm nvl() là một trường hợp con của hàm decode().
Hàm rpad() và lpad() là 2 hàm làm việc với các dữ liệu kiểu chuỗi, dùng để bổ sung bên phải, bên trái một chuỗi một số lượng ký tự nhất định.
Ví dụ về lpad():
Mã:
select surname, age,
lpad(decode(graduate,'Y','Graduate',
'N','Undergraduate','Undergraduate'),13,'-') Graduate
from enrolments;
SURNAME AGE GRADUATE
-------------------- --------- -------------
Morris 18 Undergraduate
Yale 72 Undergraduate
Brown 71 -----Graduate
Wirth 17 Undergraduate
Klawitter 75 -----Graduate
Wong 72 Undergraduate
Ví dụ về rpad:
Mã:
select surname, age,
rpad(decode(graduate,'Y','Graduate',
'N','Undergraduate','Undergraduate'),13,'*') Graduate
from enrolments;
SURNAME AGE GRADUATE
-------------------- --------- -------------
Morris 18 Undergraduate
Yale 72 Undergraduate
Brown 71 Graduate*****
Wirth 17 Undergraduate
Klawitter 75 Graduate*****
Wong 72 Undergraduate
Hai hàm upper() and lower() là 2 hàm làm việc với dữ liệu kiểu chuỗi, chuyển đổi định dạng một chuỗi sang chữ hoa và chữ thường.
Mã:
SQL> select upper(surname), lower(surname), surname from enrolments;
UPPER(SURNAME) LOWER(SURNAME) SURNAME
-------------- ------------- -------------
MORRIS morris Morris
YALE yale Yale
BROWN brown Brown
WIRTH wirth Wirth
KLAWITTER klawitter Klawitter
WONG wong Wong
Hàm length() trả về độ dài của một chuỗi. Nếu chuỗi có kiểu là VARCHAR2 thì hàm length() không tính các ký tự trắng ở đầu và cuối chuỗi, nếu chuỗi có kiểu là CHAR thì hàm length() tính cả các ký tự trắng ở đầu và cuối chuỗi.
Mã:
SQL> select surname, length(surname) from enrolments;
SURNAME LENGTH(SURNAME)
-------------------- ---------------
Morris 6
Yale 4
Brown 5
Wirth 5
Klawitter 9
Wong 4
Hàm substr(), trả về một chuỗi con của một chuỗi cho trước. Chúng ta gọi hàm này như sau substr(field,1,5), khi đó kết quả trả về sẽ là một chuỗi bao gồm 5 ký tự đầu tiên của chuỗi được chỉ định cột field.
Ví dụ:
Mã:
SQL> select surname, substr(surname,1,5) from enrolments;
SURNAME SUBST
-------------------- -----
Morris Morri
Yale Yale
Brown Brown
Wirth Wirth
Klawitter Klawi
Wong Wong
Chúng ta đã xem qua một số các hàm đơn giản mà hữu dụng để xử lý các chuỗi. Bây giờ ta sẽ xem xét đến các hàm toán học (math function).
Và miêu tả tóm tắt về các "single row function" (hàm đơn hàng)
Bài 3. Liên kết các bảng (Joining tables)
Trong bài này chúng ta sẽ để cập đến việc liên kết (join) các bảng.
Inner Joins.
Outer Joins.
Joining a table to itself.
Để công việc của DBA được thuận tiện bạn cần phải nắm tốt bài này.
Inner Joins
Inner joins là câu select trả về dữ liệu khi dữ liệu ở điều kiện tồn tại và thỏa mãn điều kiện. Câu lệnh thường sử dụng '=' để so sánh dữ liệu từ hai trường của hai bảng.
Sau đây là cấu trúc của 2 bản (bảng nhân viên và bảng khách hàng).
Mã:
desc employees;
Name Null? Type
------------------------------- -------- ----
ID VARCHAR2(10)
EMP_NAME VARCHAR2(50)
EMP_PHONE VARCHAR2(10)
Mã:
desc customers;
Name Null? Type
------------------------------- -------- ----
CUST_ID VARCHAR2(10)
CUST_NAME VARCHAR2(50)
EMP_ID VARCHAR2(10)
LAST_SALE DATE
Chúng ta có 1 bảng thông tin về các nhân viên và một bảng thông tin khách hàng. Với câu lệnh sau chúng ta sẽ được thông tin về cust_name và emp_phone (tên khách hàng và điện thoại của nhân viên) của tất cả các nhân viên đồng thời là khách hàng.
Mã:
select cust_name, emp_phone
from employees, customers
where id = emp_id;
Chúng ta sẽ nhận được kết quả như sau:
Mã:
SQL> select cust_name, emp_phone
2 from employees, customers
3 where id = emp_id;
CUST_NAME EMP_PHONE
----------------------------------- ----------
Smith 123456789
Outer Joins
Cũng giống như câu lệnh Inner joins nhưng outer joins linh động hơn. Nó cho phép select dữ liệu khi có sự liên quan.
Sau đây là cấu trúc của 02 bảng (bảng nhân viên và bảng khách hàng).
Mã:
desc employees;
Name Null? Type
------------------------------- -------- ----
ID VARCHAR2(10)
EMP_NAME VARCHAR2(50)
EMP_PHONE VARCHAR2(10)
Mã:
desc customers;
Name Null? Type
------------------------------- -------- ----
CUST_ID VARCHAR2(10)
CUST_NAME VARCHAR2(50)
EMP_ID VARCHAR2(10)
LAST_SALE DATE
Với câu lệnh sau:
Mã:
select cust_name, emp_phone
from employees, customers
where emp_id = id(+);
chúng ta sẽ nhận được tên khách hàng (cust_name) và nếu như họ cũng là nhân viên thì số điện thoại (emp_phone) cũng sẽ được lấy.
Mã:
SQL> select cust_name, emp_phone
2 from employees, customers
3 where emp_id = id(+);
CUST_NAME EMP_PHONE
---------------------------------------- ----------
Smith 123456789
Green
Joining a table to itself
Ở trên ta đã thực hiện việc so sánh và lấy dữ liệu từ nhiều bảng, để làm việc đó trên 1 bảng thì ta phải đặt 2 bí danh cho bảng này và Oracle sẽ coi đó là dữ liệu từ hai bảng.
Ví dụ.
Mã:
select a.employee_name, a.birth_date
from employees a, employees b
where a.birth_date = b.birth_date
and a.employee_name != b.employee_name;
Câu lệnh trên cho ta danh sách của tất cả các nhân viên có cùng ngày sinh.
Bài 4. Các hàm thống kê số liệu (Group functions)
Bài này chúng ta đề cập đến các hàm thống kê dữ liệu:
Danh sách các hàm.
Sử dụng các hàm.
Thêm các mệnh đề với các hàm.
Loại trừ (giới hạn) dữ liệu.
Các hàm thống kê dữ liệu là một phần quan trọng với công việc của DBA.
Danh sách các hàm gồm
Các hàm thống kê dữ liệu có khả năng tác động trên nhiều bản ghi cùng một lúc. Chúng thường được sử dụng để tính các giá trị nhỏ nhất, giá lớn nhất, giá trị trung bình, đếm số lượng, độ lệch chuẩn.
Sau đây là danh sách các hàm thống kê.
min(x)
Tìm giá trị nhỏ nhất trong một giá trị của trường x trả về từ câu lệnh select.
max(x)
Tìm giá trị lớn nhất trong một giá trị của trường x trả về từ câu lệnh select.
avg(x)
Tìm giá trị lớn nhất trong trường x trả về từ câu lệnh select.
count(x)
Đếm số lượng bản ghi trả về từ câu lệnh select.
sum(x)
Tính tổng các giá trị trong trường x trả về từ câu lệnh select.
stddev(x)
The standard deviation for all values in column x in the group returned by the select statement.
variance(x)
The variance for all values in column x in the group.
Sử dụng các hàm thống kê dữ liệu (Using group functions)
Ví dụ với dữ liệu nhân sự của công ty 'Fred & Barney Hosting Co.'. Bao gồm 4 phòng với 8 nhân viên. Sau đây chúng ta sẽ tạo bảng và nhập dữ liệu.
Mã:
create table employees (id varchar2(10),
name varchar2(30),
age varchar2(3),
sex varchar2(1),
comm_date date,
salary number(10,2));
Sử dụng SQL*Plus ta có như sau:
Mã:
SQL> create table employees (id varchar2(10),
2 name varchar2(30),
3 age varchar2(3),
4 sex varchar2(1),
5 comm_date date,
6 salary number(10,2));
Table created.
Kiểm tra lại ta có:
Mã:
SQL>
SQL> desc employees;
Name Null? Type
------------------------------- -------- ----
ID VARCHAR2(10)
NAME VARCHAR2(30)
AGE VARCHAR2(3)
SEX VARCHAR2(1)
COMM_DATE DATE
SALARY NUMBER(10,2)
Nhập dữ liệu:
Mã:
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(1, 'Smith', 45, 'F', to_date('01-FEB-1997','DD-MON-YYYY'),
70000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(2, 'Slate', 52, 'M', to_date('01-FEB-1997','DD-MON-YYYY'),
80000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(3, 'Rubble', 22, 'M', to_date('10-FEB-1998','DD-MON-YYYY'),
22000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(4, 'Flintstone', 18, 'M', to_date('01-FEB-1998','DD-MON-YYYY'),
70000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(5, 'OOI', 21, 'F', to_date('01-FEB-1999','DD-MON-YYYY'),
99000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(6, 'Vajhaal', 21, 'M', to_date('01-MAR-1999','DD-MON-YYYY'),
2000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(7, 'Mostert', 25, 'M', to_date('20-AUG-1998','DD-MON-YYYY'),
50000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(8, 'Jeffcoat', 45, 'M', to_date('01-FEB-1999','DD-MON-YYYY'),
35000);
Như vậy ta đã có đủ dữ liệu cần thiết.
Bây giờ chúng ta sẽ thực hiện sử dụng các hàm
Đầu tiên là hàm count(). Có 2 cách dùng như sau:
Mã:
select count(*), count(1) from employees;
Câu lệnh trên sẽ có kết quả như sau:
Mã:
SQL> select count(*), count(1) from employees;
COUNT(*) COUNT(1)
--------- ---------
8 8
Chúng ta nhận được cùng một kết quả. Có sự khác biệt duy nhất là count(1) thực hiện nhanh hơn.
Để xem ai là người có lương thấp nhất, cao nhất, lương trung bình là bao nhiêu ta dùng lệnh sau:
Mã:
select min(salary), max(salary), avg(salary)
from employees;
Chúng ta sẽ nhận được kết quả như sau:
Mã:
SQL> select min(salary), max(salary), avg(salary)
2 from employees;
MIN(SALARY) MAX(SALARY) AVG(SALARY)
----------- ----------- -----------
2000 99000 53500
Tổng số lương phải trả cho các nhân viên là bao nhiêu? Chúng ta sẽ sử dụng hàm tính tổng (sum) như sau:
select sum(salary) from employees;
Và kết quả thu được sẽ là:
Mã:
SQL> select sum(salary) from employees;
SUM(SALARY)
-----------
428000
Now for the technical side of things, these two functions are rarely used, in fact I have used then less than once in the past year.
Mã:
select variance(salary),
stddev(salary) f
rom employees;
You should see the output.
Mã:
SQL> select variance(salary), stddev(salary) from employees;
VARIANCE(SALARY) STDDEV(SALARY)
---------------- --------------
1.045E+09 32328.669
Sử dụng thêm các mệnh đề
Ngoài việc muốn tính min() and max() chúng ta còn muốn tính theo nhóm đối tượng mà ở đây phân biệt theo giới tính.
select min(salary), max(salary)
from employees
group by sex;
Chúng ta thu được kết quả như sau:
Mã:
SQL> select min(salary), max(salary)
2 from employees
3 group by sex;
MIN(SALARY) MAX(SALARY)
----------- -----------
70000 99000
2000 80000
Tuy nhiên câu lệnh trên mới chỉ tính ra kết quả theo từng loại giới tính nhưng không thể hiện cho chúng ta thấy chính sác số nào thuộc giới nào. Câu lệnh sau cho chúng ta đầy đủ thông tin.
Như vậy câu lệnh của chúng ta sẽ là:
Mã:
select sex, min(salary), max(salary)
from employees
group by sex;
Và kết quả sẽ thu được đầy đủ như sau:
Mã:
SQL> select sex, min(salary), max(salary)
2 from employees
3 group by sex;
S MIN(SALARY) MAX(SALARY)
- ----------- -----------
F 70000 99000
M 2000 80000
Loại trừ dữ liệu (Excluding data)
Chúng ta có thể dùng 02 phương thức để thực hiện loại trừ dữ liệu đó là mệnh đề “WHERE” cách thứ 2 là mệnh đề “HAVING”.
mệnh đề “WHERE” thực hiện điều kiện trên mọi bản ghi, còn mệnh đề 'HAVING' trên nhóm bản ghi định trước.
Tính tổng lương theo giới tính nếu có ít nhất 4 người thuộc cùng giới đó.
Mã:
select sex, sum(salary)
from employees
group by sex
having count(1) > 4;
Chúng ta có kết quả:
Mã:
SQL> select sex, sum(salary)
2 from employees
3 group by sex
4 having count(1) > 4;
S SUM(SALARY)
- -----------
M 259000
Như vậy có nhiều hơn 4 nam và ít hơn 4 nữ, chúng ta sẽ kiểm tra lại.
Mã:
select sex, count(1)
from employees
group by sex;
Kết quả thu được là:
SQL> select sex, count(1)
2 from employees
3 group by sex;
S COUNT(1)
- ---------
F 2
M 6
Bài 11 - Bộ duyệt dữ liệu (Cursors)
Introduction
Trong bài này chúng tôi giới thiệu về:
Phiên làm việc (Transactions)
Con trỏ (Cursors)
Implicit and Explicit Cursors
Khai báo con trỏ (Declaring Cursors)
Khởi tạo con trỏ (Opening Cursors)
Thực thi từ con trỏ (Fetching from Cursors)
Đóng con trỏ (Closing Cursors)
Select để Update sử dụng con trỏ (Select for Update with Cursors)
Con trỏ là một phần quan trọng trọng PL/SQL. Bạn sẽ thường xuyên sử dụng chúng khi viết PL/SQL.
Phiên làm việc (Transactions)
Phiên làm việc bắt buộc phải xác nhận trạng thái kết thúc hoặc là thành công (committed) hoặc là trả lại trạng thái ban đầu (rollback). Phiên làm việc có thể bao gồm nhiều nhiều công việc, chỉ khi các công này cùng hoàn thành thì kết quả mới được chấp nhận còn không nó sẽ yêu cầu trả lại trạng thái ban đầu nếu có bất cứ một công việc nào không hoàn thành. Nó giống như việc chuyển tiền, thông tin tài khoản chuyển và nhận chỉ được cập nhận khi mà cả bên nhận đã nhận tiền và bên chuển đã được trừ tiền. Nó tránh việc bên nhận đã nhận tiền còn bên chuyển không bị trừ, hoặc bên chuyển đã bị trừ tiền còn bên nhận thì không nhận được tiền.
Câu lệnh commit sẽ thiết lập xác nhận vĩnh cửu các thay đổi cho tất cả những gì thực hiện từ lần commit hoặc rollback trước đó. Lệnh commit sẽ giải phóng tất cả những hàng (row) hoặc các bảng mà nó đã khóa (lock) khi thực hiện phiên làm việc của mình. Nó cũng xóa bỏ tất cả các savepoints trước đó.
Lệnh rollback cho phép hoàn trả lại một vài hoặc tất cả những gì đã thực hiện trong phiên giao dịch đối với cơ sở dữ liệu. Ví dụ như lỗi sau:
Mã:
delete customers;
Bạn đã thực hiện câu lệnh trên thay vì cần phải viết:
Mã:
delete customers where cust_id = 1008;
Lệnh rollback sẽ khôi phục lại dữ liệu nếu bạn vẫn còn thuộc phiên giao dịch đó.
Làm thế nào để lấy lại được một vài thứ mà bạn đã thay đổi? Bạn sẽ thực hiện điều đó bằng cách sử dụng lệnh savepoint. Sử dụng savepoint cho phép bạn quay lại bất cứ thời điểm nào mà bạn mong muốn trong phiên giao dịch.
Mã:
Update customers
set disc = 10;
savepoint after_disc;
delete from customers;
Lỡ tay rồi, cần phải rollback lại.
Mã:
rollback after_disc;
commit;
Kết quả là tất các khách hàng sẽ được update giá trị disc = 10. Chúng ta sẽ tạo một savepoint với tên after_disc. A rollback không thể khai báo như một thành phần mà Oracle có thể nhận biết được, bạn có thể kiểm tra với các câu lệnh sau.
Mã:
SQL> savepoint 'Test';
savepoint 'Test'
*
ERROR at line 1:
ORA-03001: unimplemented feature
Mã:
SQL> savepoint table;
savepoint table
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Câu lệnh:
Mã:
SET TRANSACTION USE ROLLBACK SEGMENT RSBIG;
cho phép bạn đặt tên cho rollback segment ở đây là RSBIG. Một phiên làm việc lớn sẽ được cấp một rollback segment lớn.
Cursors
Khi bạn thực hiện câu lệnh SQL trong PL/SQL Oracle tạo vùng làm việc riêng biệt gọi là PGA. Ở đó sẽ lưu trữ kết quả trả về. Tên của con trỏ sẽ được chỉ đến vùng nhớ đó. Chúng ta sẽ tạo con trỏ như sau.
Mã:
CURSOR c_customers is
SELECT * from CUSTOMERS;
Bạn có thể mở một con trỏ như sau.
Mã:
OPEN c_customers;
Bạn có thể select dữ liệu bằng con trỏ như sau.
Mã:
FETCH c_customers into customers_rec;
Chúng ta có thể đóng con trỏ như sau.
Mã:
CLOSE c_customers;
Khi bạn select dữ liệu bằng con trỏ thực tế nó sẽ lấy dữ liệu từ bảng ảo được định nghĩa bởi con trỏ.
Implicit and Explicit Cursors
Tất cả các câu lệnh SQL đều là con trỏ ngầm (implicit cursor).
Tuy nhiên bạn luôn dùng con trỏ tường minh trong PL/SQL.
Declaring Cursors
Trước khi sử dụng một con trỏ một cách tường minh bạn cần phải khai báo nó. Có 3 các như sau.
Mã:
DECLARE c_customers IS
SELECT name, phone
FROM customers;
DECLARE c_customers(i_cust_id In NUMBER) IS
SELECT name, phone
FROM customers
WHERE cust_id = i_cust_id;
DECLARE c_customers RETURN customers%ROWTYPE IS
SELECT name, phone
FROM customers;
Cách thứ 2 cho phép bạn truyền tham số. Cách thứ 3 thì hầu như không dùng.
Using Cursors
Khởi tạo con trỏ.
Khi bạn khởi tạo một con trỏ, PL/SQL sẽ thực thi yêu cầu cho con. Nó thực hiện thông qua câu lệnh fetch. Oracle RDBMS bảo đảm tính đồng nhất cho con trỏ. Tất cả dữ liệu sẽ không updates, inserts or deleted nếu như nó liên quan đến các bảng đang được dùng bởi con trỏ. Một con trỏ không được mở 2 lần. Bạn cần kiểm tra xem con trỏ có mở không bằng cách sử dụng %ISOPEN trước khi dùng.
Mã:
if c_customers%ISOPEN then
close c_customers;
end if;
open c_customers;
Mã:
Fetching from Cursors.
Bạn phải truyền các giá trị từ con trỏ sang biến để sử dụng như câu lệnh sau:
Mã:
FETCH c_customers INTO l_name, l_phone;
Số lượng biến phải đủ với khai báo con trỏ. Hãy nhớ lại bài 4 để rõ hơn.
Để kiểm tra xem chúng ta đã dùng toàn bộ các bản ghi chúng ta sẽ sử dụng thuộc tính %NOTFOUND. Giá trị trả về TRUE khi không còn bản ghi nào.
Closing Cursors.
Khi sử dụng xong bạn cần phải đóng con trỏ lại để giải phóng vùng nhớ của con trỏ đó.
Mã:
close c_customers;
Select for Update with Cursors.
Khi bạn select dữ liệu và muốn khóa dữ liệu trước khi thực hiện thay đổi trong chương PL/SQL. Bạn có thể sử dụng mệnh đề For Update cho câu select.
Mã:
declare c_customers is
select name,phone
from customers
for update;
Bài 12 - Vòng lặp (Looping)
Bài này chúng ta sẽ đề cập đến các loại vòng lặp
The simple loop
Numeric loops
The WHILE loop
Một trong những khác biệt chính giữa PL/SQL và SQL đó là khả năng thực hiện vòng lặp của PL/SQL. Các cấu trúc vòng lặp trong PL/SQL rất đơn giản và dễ hiểu.
Chúng tôi sẽ giới thiệu 3 cấu trúc vòng lặp sau:
The simple loop
Cấu trúc của vòng lặp đơn giản như sau:
Mã:
loop
executable statements
exit when condition is true
end loop;
Hãy thử ví dụ sau. Nhập đoạn code sau vào sql*plus.
Mã:
set serveroutput on
declare i number := 1;
begin
dbms_output.enable(100000);
loop
dbms_output.put_line(i);
i := i + 1;
exit when i > 10;
end loop;
end;
Khi bạn thực thi đoạn mã trên bạn sẽ nhận được kết quả là 1 dãy số từ 1 đến 10 được in ra. Vòng lặp sẽ tiếp tục chạy cho đến khi mệnh đề điều kiện thỏa mãn. dbms_output là lệnh thực hiện lấy kết quả từ gói dữ liệu sẽ để hiển thị. Bạn sẽ sử dụng vòng lặp đơn giản này khi bạn không biết được có tất cả bao nhiêu lần lặp cần thực hiện và bạn muốn vòng lặp được thực hiện ít nhất là một lần.
Numeric loops
Vòng lặp số sẽ thực hiện đầy đủ số lần mà bạn đã đưa ra. Cấu trúc lệnh vòng lặp số như sau:
Mã:
for loop_index in lower_range .. upper_range
loop
executable statements
end loop;
set serveroutput on
Ví dụ:
begin
Mã:
dbms_output.enable(10000);
for i in 1 .. 10
loop
dbms_output.put_line(i);
end loop;
end;
Đoạn mã trên sẽ thực hiện lặp 10 lần. Bạn sẽ nhận được kết quả giống như trên với một cách lặp hoàn toàn khác. Với cách này bạn sẽ đảm bảo được chắc chắn số lần lặp cần thực hiện.
Trên đây là vòng lặp tiến, bạn có thể sử dùng vòng lặp lùi với cú pháp như sau:
Mã:
set serveroutput on
begin
dbms_output.enable(10000);
for i in reverse 1 .. 10
loop
dbms_output.put_line(i);
end loop;
end;
Vòng lặp này sẽ làm giảm giá trị của i từ 10 xuống 1. PL/SQL thực hiện việc tăng hay giảm với cùng một đơn vị là 1.
Trong thực tế chúng ta sẽ sử dụng vòng lặp không chỉ để hiển thị các con số như trên mà xa hơn đó là việc lấy nhiều bản ghi khi ta thực hiện lấy dữ liệu. Với lặp đơn giản ta sẽ làm như sau:
Mã:
begin
declare cursor c_students is
select * from students;
student_rec c_students%rowtype;
begin
open c_students;
loop
fetch c_students into student_rec;
exit when c_students%notfound;
end loop;
close c_students;
end;
end;
Hoặc:
Mã:
declare
cursor c_students is
select * from students;
begin
for c_students_rec in c_students
loop
null;
end loop;
end;
Cách thứ 2 ngắn hơn nhưng tại sao ta vẫn dùng cách thứ nhất? Vòng lặp đơn giản đó là cách tốt nhất khi bạn cần quyết định khi nào dừng lặp dựa trên giá trị được trả về từ yêu cầu lấy dữ liệu.
The WHILE loop
Vòng lặp While sử dụng khi bạn muốn kiểm tra điều kiện trước khi thực hiện lệnh. Hãy nhập đoạn mã sau vào sql*plus.
Mã:
set serveroutput on
declare
i number := 0;
begin
dbms_output.enable(1000);
while i < 10
loop
i := i + 1;
dbms_output.put_line(i);
end loop;
end;
Ta dùng While khi ta không xác định được là sẽ phải lặp đến khi nào và chỉ thực hiện nếu mệnh đề điều kiện còn thỏa mãn.
Bạn đang đọc truyện trên: Truyen2U.Pro