Thứ Năm, 6 tháng 8, 2020

SQL cơ bản - Bài 10: DELETE và TRUNCATE

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
  • COMMIT và ROLLBACK
  • DELETE cơ bản
  • DELETE qua View
  • 0 Rows Deleted
  • TRUNCATE TABLE

Thiết lập môi trường

Bạn có thể thực hiện tất cả các truy vấn trực tuyến miễn phí bằng SQL Fiddle .
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 DELETECâ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õ TRUNCATE
SELECT 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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master