Sử dụng dữ liệu thao tác
Oracle cung cấp các lệnh Ngôn ngữ thao tác dữ liệu để thực hiện các thao tác dữ liệu trong cơ sở dữ liệu. Các thao tác dữ liệu có thể được đưa vào các bảng cơ sở dữ liệu với ứng dụng hoặc dữ liệu nghiệp vụ, sửa đổi dữ liệu và xóa dữ liệu khỏi cơ sở dữ liệu, bất cứ khi nào cần thiết. Bên cạnh các hoạt động dữ liệu, có một tập hợp các lệnh được sử dụng để điều khiển các hoạt động này. Các lệnh này được nhóm thành Ngôn ngữ điều khiển giao dịch.
Có ba loại câu lệnh DML liên quan đến một giao dịch SQL logic là Chèn, Cập nhật, Xóa và Hợp nhất. Một giao dịch là tập hợp hợp lý các hành động DML trong một phiên cơ sở dữ liệu.
Câu lệnh INSERT
Lệnh INSERT được sử dụng để lưu trữ dữ liệu trong bảng. Lệnh INSERT thường được sử dụng trong các ngôn ngữ lập trình cấp cao hơn như Visual Basic.NET hoặc C ++ dưới dạng lệnh SQL nhúng; tuy nhiên, lệnh này cũng có thể được thực thi tại dấu nhắc SQL * PLUS trong chế độ lệnh. Có hai dạng lệnh INSERT khác nhau. Biểu mẫu đầu tiên được sử dụng nếu một hàng mới sẽ có một giá trị được chèn vào mỗi cột của hàng. Dạng thứ hai của lệnh INSERT được sử dụng để chèn các hàng trong đó một số dữ liệu cột không xác định hoặc được mặc định từ một logic nghiệp vụ khác. Dạng lệnh INSERT này yêu cầu bạn chỉ định tên cột mà dữ liệu đang được lưu trữ.
Cú pháp:
Cú pháp dưới đây có thể được tuân theo nếu giá trị của tất cả các cột trong bảng là xác định và được biết.
INSERT INTO table
VALUES (column1 value, column2 value,
...);Cú pháp dưới đây có thể được sử dụng nếu chỉ có một số cột từ bảng phải được điền một giá trị. Phần còn lại của các cột có thể suy ra giá trị của chúng dưới dạng NULL hoặc từ một logic nghiệp vụ khác.
INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);Câu lệnh INSERT bên dưới tạo một bản ghi nhân viên mới trong bảng NHÂN VIÊN. Lưu ý rằng nó sẽ chèn các giá trị cho các cột chính EMPLOYEE_ID, FIRST_NAME, SALARY và DEPARTMENT_ID.
INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);Nếu không, dữ liệu nhân viên đầy đủ có thể được chèn vào bảng NHÂN VIÊN mà không cần chỉ định danh sách cột bằng cách sử dụng câu lệnh INSERT bên dưới - miễn là các giá trị đã được biết trước và phải tuân thủ kiểu dữ liệu và vị trí của các cột trong bảng.
INSERT INTO employees
VALUES (130, 'KEMP','GARNER', '[email protected]', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);Giá trị được chèn phải tương thích với kiểu dữ liệu của cột. Chữ số, giá trị cố định và giá trị đặc biệt như hàm, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL) hoặc USER có thể được sử dụng làm giá trị cột. Các giá trị được chỉ định phải tuân theo các quy tắc chung. Chuỗi ký tự và giá trị ngày tháng phải được đặt trong dấu ngoặc kép. Giá trị ngày tháng có thể được cung cấp ở định dạng DD-MON-RR hoặc D-MON-YYYY, nhưng YYYY được ưu tiên hơn vì nó chỉ định rõ ràng thế kỷ và không phụ thuộc vào logic tính toán thế kỷ RR bên trong.
Câu lệnh INSERT-AS-SELECT (IAS)
Dữ liệu có thể được điền vào bảng đích từ bảng nguồn bằng thao tác INSERT..AS..SELECT (IAS). Đây là một hoạt động đọc đường dẫn trực tiếp Là một cách đơn giản để tạo bản sao dữ liệu từ bảng này sang bảng khác hoặc tạo bản sao dự phòng của bảng mà các hoạt động của bảng nguồn đang trực tuyến.
Ví dụ: dữ liệu có thể được sao chép từ bảng EMPLOYEES sang bảng EMP_HISTORY.
INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;CẬP NHẬT tuyên bố
Lệnh UPDATE sửa đổi dữ liệu được lưu trữ trong một cột, nó có thể cập nhật một hoặc nhiều hàng cùng một lúc tùy thuộc vào tập kết quả được lọc theo các điều kiện được chỉ định trong mệnh đề WHERE. Lưu ý rằng Cập nhật cột khác với thay đổi cột. Trước đó trong chương này, bạn đã nghiên cứu về lệnh ALTER, lệnh ALTER thay đổi cấu trúc bảng nhưng không ảnh hưởng đến dữ liệu bảng. Lệnh UPDATE thay đổi dữ liệu trong bảng chứ không phải cấu trúc bảng.
Cú pháp:
UPDATE table
SET column = value [, column = value ...]
[WHERE condition]Từ cú pháp,
Cột SET = biểu thức có thể là bất kỳ sự kết hợp nào của các ký tự, công thức hoặc hàm sẽ cập nhật dữ liệu trong tên cột được chỉ định. Mệnh đề WHERE là tùy chọn, nhưng nếu nó được bao gồm, nó chỉ định các hàng sẽ được cập nhật. Chỉ một bảng có thể được cập nhật tại một thời điểm bằng lệnh UPDATE.
Tuyên bố UPDATE bên dưới cập nhật mức lương của nhân viên JOHN lên 5000.
UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';Mặc dù các vị từ WHERE là tùy chọn, nhưng phải được nối một cách hợp lý để chỉ sửa đổi hàng bắt buộc trong bảng. Câu lệnh UPDATE bên dưới cập nhật mức lương của tất cả nhân viên trong bảng.
UPDATE employees
SET salary = 5000;Nhiều cột cũng có thể được cập nhật bằng cách chỉ định nhiều cột trong mệnh đề SET được phân tách bằng dấu phẩy. Ví dụ: nếu cả tiền lương và vai trò công việc phải được thay đổi thành 5000 và SALES tương ứng cho JOHN, thì câu lệnh UPDATE trông giống như sau:
UPDATE employees
SET SALARY = 5000,
JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';
1 row updated.Một cách khác để cập nhật nhiều cột của cùng một hàng cho thấy việc sử dụng truy vấn con.
UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'DELETE tuyên bố
Lệnh DELETE là một trong những câu lệnh SQL đơn giản nhất. Nó loại bỏ một hoặc nhiều hàng khỏi bảng. Không cho phép nhiều thao tác xóa bảng trong SQL. Cú pháp của lệnh DELETE như bên dưới.
DELETE FROM table_name
[WHERE condition];Lệnh DELETE xóa tất cả các hàng trong bảng thỏa mãn điều kiện trong mệnh đề WHERE tùy chọn. Vì mệnh đề WHERE là tùy chọn, nên người ta có thể dễ dàng xóa tất cả các hàng khỏi bảng bằng cách bỏ qua mệnh đề WHERE vì mệnh đề WHERE giới hạn phạm vi hoạt động DELETE.
Câu lệnh DELETE dưới đây sẽ xóa các chi tiết của EDWIN khỏi bảng EMP.
DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'
1 row deleted.Lưu ý: XÓA [TÊN BẢNG] và XÓA khỏi [TÊN BẢNG] có cùng ý nghĩa.
Điều kiện WHERE trong câu lệnh xóa có điều kiện có thể sử dụng truy vấn con như hình dưới đây.
DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM LOCATIONS
WHERE LOCATION_CODE = 'SFO')TRUNCATE
Truncate là một lệnh DDL được sử dụng để loại bỏ tất cả các bản ghi khỏi một bảng nhưng vẫn giữ lại cấu trúc bảng. Nó không hỗ trợ điều kiện WHERE để loại bỏ các bản ghi đã chọn.
Cú pháp:
TRUNCATE [table name]Nó là Auto Cam kết tức là nó cam kết giao dịch đang hoạt động hiện tại trong phiên. Việc cắt bớt bảng không làm giảm các chỉ mục, trình kích hoạt hoặc ràng buộc bảng phụ thuộc. Nếu bảng A là cha của một ràng buộc tham chiếu của bảng B trong cơ sở dữ liệu, thì bảng A không thể bị cắt bớt.
Giao dịch
Giao dịch là một đơn vị logic của công việc được thực hiện trong cơ sở dữ liệu. Nó có thể chứa -
Nhiều lệnh DML kết thúc bằng lệnh TCL tức là COMMIT hoặc ROLLBACK
Một lệnh DDL
Một lệnh DCL
Bắt đầu một giao dịch được đánh dấu bằng lệnh DML đầu tiên. Nó kết thúc bằng lệnh TCL, DDL hoặc DCL. Lệnh TCL tức là COMMIT hoặc ROLLBACK là các vấn đề rõ ràng để kết thúc một giao dịch đang hoạt động. Theo hành vi cơ bản của chúng, nếu bất kỳ lệnh DDL hoặc DCL nào được thực thi trong phiên cơ sở dữ liệu, hãy thực hiện giao dịch đang hoạt động trong phiên đó. Nếu phiên bản cơ sở dữ liệu gặp sự cố bất thường, giao dịch sẽ bị dừng.
COMMIT, ROLLBACK và SAVEPOINT là ngôn ngữ điều khiển giao dịch. COMMIT áp dụng các thay đổi dữ liệu vĩnh viễn vào cơ sở dữ liệu trong khi ROLLBACK thực hiện hoạt động chống cam kết. SAVEPOINT kiểm soát chuỗi giao dịch bằng cách đặt các điểm đánh dấu ở các giai đoạn giao dịch khác nhau. Người dùng có thể quay trở lại giao dịch hiện tại về điểm lưu mong muốn, đã được đặt trước đó.
COMMIT- Cam kết kết thúc giao dịch đang hoạt động hiện tại bằng cách áp dụng các thay đổi dữ liệu vĩnh viễn vào các bảng cơ sở dữ liệu. COMMIT là lệnh TCL kết thúc giao dịch một cách rõ ràng. Tuy nhiên, lệnh DDL và DCL thực hiện giao dịch ngầm.
SAVEPOINT- Savepoint được sử dụng để đánh dấu một điểm cụ thể trong giao dịch hiện tại trong phiên. Vì nó là điểm đánh dấu logic trong giao dịch, các điểm lưu không thể được truy vấn trong từ điển dữ liệu.
ROLLBACK- Lệnh ROLLBACK được sử dụng để kết thúc toàn bộ giao dịch bằng cách loại bỏ các thay đổi dữ liệu. Nếu giao dịch có chứa các điểm lưu được đánh dấu, chỉ có thể sử dụng ROLLBACK TO SAVEPOINT [tên] để khôi phục giao dịch tới điểm lưu được chỉ định. Do đó, tất cả dữ liệu thay đổi đến điểm lưu được chỉ định sẽ bị loại bỏ.
Trình diễn
Hãy xem xét bảng NHÂN VIÊN được điền thông tin chi tiết về nhân viên mới được thuê trong quý đầu tiên hàng năm. Nhân viên văn thư nối từng chi tiết của nhân viên với một điểm lưu, để khôi phục dữ liệu bị lỗi bất kỳ lúc nào trong quá trình cung cấp dữ liệu. Lưu ý rằng anh ta giữ tên điểm lưu giống như tên nhân viên.
INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);
SAVEPOINT Allen;
INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);
SAVEPOINT Kate;
INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);
SAVEPOINT McMan;Giả sử, nhà điều hành cung cấp dữ liệu nhận ra rằng anh ta đã nhập sai tiền lương của 'Kate' và 'McMan'. Anh ta chuyển giao dịch đang hoạt động trở lại điểm lưu Kate và nhập lại thông tin chi tiết về nhân viên cho Kate và McMan.
ROLLBACK TO SAVEPOINT Kate;
INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);
SAVEPOINT Kate;
INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);
SAVEPOINT McMan;Sau khi hoàn tất việc nhập dữ liệu, anh ta có thể cam kết toàn bộ giao dịch bằng cách đưa ra COMMIT trong phiên hiện tại.
Đọc nhất quán
Oracle duy trì sự nhất quán giữa những người dùng trong mỗi phiên về quyền truy cập dữ liệu và hành động đọc / ghi.
Khi một DML xảy ra trên bảng, các giá trị dữ liệu ban đầu được thay đổi bởi hành động được ghi lại trong bản ghi hoàn tác cơ sở dữ liệu. Miễn là giao dịch không được cam kết vào cơ sở dữ liệu, bất kỳ người dùng nào trong phiên khác sau đó truy vấn dữ liệu đã sửa đổi sẽ xem các giá trị dữ liệu ban đầu. Oracle sử dụng thông tin hiện tại trong khu vực toàn cầu của hệ thống và thông tin trong các bản ghi hoàn tác để xây dựng một chế độ xem nhất quán đọc dữ liệu của bảng cho một truy vấn. Chỉ khi một giao dịch được cam kết, các thay đổi của giao dịch mới có hiệu lực vĩnh viễn. Giao dịch là chìa khóa cho chiến lược của Oracle để cung cấp tính nhất quán cho việc đọc.
Điểm bắt đầu cho chế độ xem nhất quán được tạo ra thay mặt cho người đọc
Các kiểm soát khi dữ liệu được sửa đổi có thể được nhìn thấy bởi các giao dịch khác của cơ sở dữ liệu để đọc hoặc cập nhật