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

SQL cơ bản - Bài 8: Câu lệnh INSERT

Trong bài viết này, chúng tôi sẽ xem xét một số biến thể của câu lệnh INSERT.

  • Thiết lập môi trường
  • COMMIT và ROLLBACK
  • INSERT cơ bản
  • INSERT vào View
  • INSERT ... SELECT
  • Subqueries

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.sq".
Trong bài viết này, chúng tôi cũng sẽ cần bản sao sau của bảng NHÂN VIÊN. Lưu ý rằng chúng tôi đã bắt buộc cột EMPLOYEE_NAME và JOB. Cột EMPLOYEE_ID hoàn toàn bắt buộc, do đó nó là khóa chính.
-- DROP TABLE employees2 PURGE;

CREATE TABLE employees2 (
  employee_id   NUMBER(4) CONSTRAINT employees2_pk PRIMARY KEY,
  employee_name VARCHAR2(10) NOT NULL,
  job           VARCHAR2(9) NOT NULL,
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2)
);

COMMIT và ROLLBACK

Tất cả các thay đổi của ngôn ngữ dữ liệu (DML - Data Manipulation Language) được thực hiện như một phần của giao dịch. Khi thực hiện câu lệnh COMMIT thì dữ liệu được sẵn sàng để lưu vào datafile. Để lấy lại dữ liệu cũ đã COMMIT thì cần flashback lại.
Các lệnh Ngôn ngữ định nghĩa dữ liệu (DDL) thực hiện một commit ngầm định, cũng xác nhận tất cả các thay đổi DML 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 ROLLBACKNhiều ví dụ trong bài viết này sẽ đưa ra các câu lệnh ROLLBACK sau khi thử nghiệm, để phục hồi dữ liệu về trạng thái ban đầu.
Một số công cụ và ngôn ngữ lập trình mặc định là tự động commit, vì vậy chúng tự động đưa ra câu lệnh COMMIT sau mỗi câu lệnh DML mà chúng xử lý. Đừng để điều này đánh lừa bạn nghĩ rằng đây là hành vi mặc định. Không phải vậy.

INSERT cơ bản

INSERT INTO employees2 VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL>

ROLLBACK;
Chỉ định danh sách cột an toàn hơn nhiều vì câu lệnh sẽ không bị hỏng nếu các cột tùy chọn được thêm vào bảng.
INSERT INTO employees2 (employee_id, employee_name, job, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL>

ROLLBACK;
Danh sách cột rõ ràng cũng cho phép bạn xóa các cột tùy chọn khỏi câu lệnh và hoán đổi vị trí của các cột trong câu lệnh. Trong ví dụ sau, các cột ManageER_ID, COMMISSION và DEPARTMENT_ID đã bị xóa và cột EMPLOYEE_ID đã được chuyển đến cuối danh sách cột và danh sách giá trị.
INSERT INTO employees2 (employee_name, job, hiredate, salary, employee_id)
VALUES ('JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800,8888);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL>

ROLLBACK;
Hãy nhớ rằng, bỏ lỡ các cột bắt buộc là không được phép, trừ khi bạn có các giá trị hoặc kích hoạt mặc định của cột tạo ra chúng. Trong ví dụ sau, cột JOB bị bỏ qua, đây là cột bắt buộc trong bảng EMPLOYEES2.
INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."EMPLOYEES2"."JOB")

SQL>

INSERT into Views

Có thể chèn vào bảng cơ sở được liên kết với chế độ xem. Có một số hạn chế liên quan đến điều này, nhưng chúng hơi ngoài phạm vi cho một bài viết cấp độ mới bắt đầu. Trong ví dụ sau, chúng ta tạo một dạng xem đơn giản trên bảng EMPLOYEES2, sau đó chèn vào đó.
CREATE OR REPLACE VIEW employees2_v AS
SELECT * FROM employees2;

INSERT INTO employees2_v VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL>

ROLLBACK;
Bạn sẽ không thấy nó rất thường xuyên, nhưng bạn cũng có thể chèn vào inline view. Danh sách cột rõ ràng.
INSERT INTO (SELECT employee_id, employee_name, job, hiredate, salary
             FROM   employees2)
VALUES (8888,'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL>

ROLLBACK;

INSERT ... SELECT

Có thể tạo nhiều hàng trong một câu lệnh INSERT bằng cách sử dụng cú pháp INSERT ... SELECT. Ví dụ sau sao chép tất cả dữ liệu từ bảng NHÂN VIÊN vào bảng EMPLOYEES2.
-- Xóa trắng bảng này
TRUNCATE TABLE employees2;

INSERT INTO employees2
SELECT * FROM employees;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300            30
       7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500            30
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400            30
       7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                       30
       7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                       10
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                       10
       7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0            30
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                       30
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20
       7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                       10

14 rows selected.

SQL>
Danh sách cột trong câu lệnh INSERT câu lệnh phải khớp với SELECT trong truy vấn.
TRUNCATE TABLE employees2;

INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
SELECT employee_id, employee_name, job, hiredate, salary
FROM   employees;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK                17-DEC-1980 00:00:00        800
       7499 ALLEN      SALESMAN             20-FEB-1981 00:00:00       1600
       7521 WARD       SALESMAN             22-FEB-1981 00:00:00       1250
       7566 JONES      MANAGER              02-APR-1981 00:00:00       2975
       7654 MARTIN     SALESMAN             28-SEP-1981 00:00:00       1250
       7698 BLAKE      MANAGER              01-MAY-1981 00:00:00       2850
       7782 CLARK      MANAGER              09-JUN-1981 00:00:00       2450
       7788 SCOTT      ANALYST              19-APR-1987 00:00:00       3000
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000
       7844 TURNER     SALESMAN             08-SEP-1981 00:00:00       1500
       7876 ADAMS      CLERK                23-MAY-1987 00:00:00       1100
       7900 JAMES      CLERK                03-DEC-1981 00:00:00        950
       7902 FORD       ANALYST              03-DEC-1981 00:00:00       3000
       7934 MILLER     CLERK                23-JAN-1982 00:00:00       1300

14 rows selected.

SQL>
Các quy tắc thông thường áp dụng cho phần truy vấn của câu lệnh, vì vậy bạn có thể lọc dữ liệu hoặc join với các bảng khác để thu thập dữ liệu.
TRUNCATE TABLE employees2;

INSERT INTO employees2
SELECT *
FROM   employees
WHERE  department_id = 20;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20

5 rows selected.

SQL>

Truy vấn con vô hướng (Scalar Subqueries)

Các truy vấn con vô hướng có thể được sử dụng để tạo giá trị cho câu lệnh INSERT. Trong ví dụ dưới đây, cột SALARY được điền bởi một truy vấn vô hướng vô hướng, trả về SALARY tối đa từ bảng EMPLOYEES  và thêm 1000 vào nó.

TRUNCATE TABLE employees2;

INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
VALUES (8888, 'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),(SELECT MAX(salary)+1000 FROM employees));

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK                17-DEC-1980 00:00:00       6000

1 row selected.

SQL>

@ 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