Trong bài viết này, chúng ta hãy xem xét các Câu lệnh DELETE và TRUNCATE TABLE.
Thiết lập môi trường
Các ví dụ trong bài viết này yêu cầu phải có các bảng sau đây.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Các bảng này là một biến thể của các bảng EMP và DEPT từ lược đồ SCOTT. Bạn sẽ thấy rất nhiều ví dụ của Oracle trên internet bằng cách sử dụng các bảng từ lược đồ SCOTT. Bạn có thể tìm thấy các định nghĩa bảng gốc trong tập lệnh "$ORACLE_HOME/rdbms/admin/utlsampl.sql".
COMMIT và ROLLBACK
Khi thay đổi dữ liệu để xác nhận thay đổi đó là đúng người dùng cần gõ lệnh COMMIT, khi commit dữ liệu đó sẽ được lưu vĩnh viễn trong datafile và người dùng khác có thể nhìn thấy dữ liệu thay đổi đó.
Khi chưa gõ commit nhưng có lệnh DDL thì sẽ được commit ngầm định, xác nhận tất cả các thay đổi DML nổi bật trong phiên hiện tại.
Nếu bạn quyết định không muốn giữ một số thay đổi chưa commit, bạn có thể loại bỏ chúng bằng cách sử dụng câu lệnh
ROLLBACK để hoàn nguyên dữ liệu về trạng thái ban đầu.DELETE cơ bản
Câu lệnh
DELETE được sử dụng để xóa các row khỏi bảng. Không có mệnh đề WHERE tất cả các row trong bảng sẽ bị xóa bởi một câu lệnh.
Ví dụ sau đây xóa tất cả các hàng khỏi bảng EMPLOYEES, sau đó
ROLLBACK để hủy xóa.DELETE FROM employees;
14 rows deleted.
SQL>
ROLLBACK;
Mệnh đề
WHERE cho phép bạn giới hạn các row bị xóa.DELETE FROM employees
WHERE employee_id = 7369;
1 row deleted.
SQL>
ROLLBACK;
DELETE qua View
Có thể xóa khỏi bảng cơ sở được liên kết với một view. Trong ví dụ sau, chúng ta tạo một view đơn giản trên bảng EMPLOYEES, sau đó xóa nó.
CREATE OR REPLACE VIEW employees_v AS
SELECT * FROM employees;
DELETE FROM employees_v
WHERE employee_id = 7369;
1 row updated.
SQL>
ROLLBACK;
Bạn sẽ không thấy nó rất thường xuyên, nhưng bạn cũng có thể xóa qua chế độ xem nội tuyến. Điều này có thể được sử dụng để kiểm soát số hàng bị xóa, thay vì sử dụng bộ lọc trong
WHEREmệnh đề của DELETEchính câu lệnh.DELETE FROM (SELECT employee_id, salary
FROM employees
WHERE department_id = 20);
5 rows deleted.
SQL>
ROLLBACK;
0 Rows Deleted
Việc xóa các row bằng 0 là việc xóa hợp lệ và không gây ra lỗi. Điều này có thể gây nhầm lẫn cho người mới bắt đầu.
DELETE FROM employees
WHERE employee_id = 9999;
0 rows deleted.
SQL>
Do đó, bạn không thể kiểm tra việc không xóa các row bằng cách sử dụng ngoại lệ
NO_DATA_FOUND trong PL/SQL:SET SERVEROUTPUT ON
BEGIN
DELETE FROM employees
WHERE employee_id = 9999;
DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Not Raised
PL/SQL procedure successfully completed.
SQL>
Thay vào đó, bạn phải kiểm tra thủ công số lượng row đã xóa bằng cách sử dụng
SQL%ROWCOUNT:SET SERVEROUTPUT ON
BEGIN
DELETE FROM employees
WHERE employee_id = 9999;
IF SQL%ROWCOUNT = 0 THEN
-- Manually raise the NO_DATA_FOUND exception.
RAISE NO_DATA_FOUND;
END IF;
DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised');
END;
/
NO_DATA_FOUND Raised
PL/SQL procedure successfully completed.
SQL>
TRUNCATE TABLE
Nếu bạn muốn xóa tất cả các row khỏi bảng, câu lệnh
TRUNCATE TABLE này hiệu quả hơn nhiều so với câu lệnh DELETE. Câu lệnh TRUNCATE TABLE là một lệnh DDL, vì vậy nó bao gồm một lệnh ngầm COMMIT, vì vậy không có cách nào ROLLBACK lại dữ liệu khi đã gõ TRUNCATE TABLE.
Trong ví dụ sau, chúng ta kiểm tra số row trong bảng, đưa ra câu lệnh
TRUNCATE TABLE, ngay lập tức ROLLBACK và kiểm tra lại số row trong bảng. Bạn sẽ thấy từ đầu ra, ROLLBACK không hủy bỏ được câu lệnh TRUNCATE TABLE nên RẤT CẨN THẬN khi gõ TRUNCATESELECT COUNT(*)
FROM employees;
COUNT(*)
----------
14
1 row selected.
SQL> TRUNCATE TABLE employees;
Table truncated.
SQL> ROLLBACK;
Rollback complete.
SQL>
SELECT COUNT(*)
FROM employees;
COUNT(*)
----------
0
1 row selected.
SQL>
Câu lệnh
TRUNCATE TABLE có thể xóa dung lượng lưu trữ được liên kết với bảng hoặc để nó được sử dụng lại sau này.-- Xóa luôn dung lượng lưu trữ.
TRUNCATE TABLE employees;
TRUNCATE TABLE employees DROP STORAGE;
-- Giữ lại dung lượng lưu trữ.
TRUNCATE TABLE employees REUSE STORAGE;
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam"
#OracleTutorial
#OracleDBA
#OracleDatabaseAdministration
#học oracle database
#oca
#ocp
#oce
#ocm
