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
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 employees . 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
ROLLBACK. Nhiề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>
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
SQL cơ bản - Bài 8: Câu lệnh INSERT, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
