Tạo các đối tượng lược đồ khác

Ngoài bảng, các đối tượng lược đồ thiết yếu khác là khung nhìn, chuỗi, chỉ mục và từ đồng nghĩa. Khung nhìn là một bảng logic hoặc bảng ảo. Từ đồng nghĩa chỉ đơn giản là tên bí danh cho các đối tượng cơ sở dữ liệu, từ đồng nghĩa cũng đơn giản hóa việc viết truy vấn và cung cấp một yếu tố bảo mật hệ thống bằng cách ngụy trang tên thực của đối tượng cơ sở dữ liệu. tạo các giá trị khóa chính cho các bảng. Các chỉ mục được tạo trên các cột của bảng để tạo điều kiện nhanh chóng cho việc truy xuất thông tin từ các bảng.

Lượt xem

Chế độ xem cơ sở dữ liệu là một bảng logic hoặc bảng ảo dựa trên một truy vấn. Các chế độ xem được truy vấn giống như các bảng Điều này có nghĩa là từ quan điểm của bạn với tư cách là nhà phát triển hoặc từ quan điểm của người dùng hệ thống cơ sở dữ liệu, một chế độ xem trông giống như một bảng. như một đối tượng được lưu trữ trong từ điển dữ liệu của cơ sở dữ liệu; tuy nhiên, một khung nhìn không lưu trữ dữ liệu chính nó. Cơ sở dữ liệu cũng lưu trữ kế hoạch thực thi để tạo một khung nhìn - điều này có nghĩa là dữ liệu có thể được truy xuất nhanh chóng thông qua việc sử dụng một khung nhìn mặc dù dữ liệu thực tế được trình bày bởi truy vấn SELECT của một khung nhìn không được lưu trữ như một phần của một khung nhìn. Đúng hơn, dữ liệu được "tập hợp lại với nhau" mỗi khi một khung nhìn được truy vấn từ các bảng cơ sở dữ liệu mà một khung nhìn được xác định - chúng được gọi là các bảng cơ sở.

Cú pháp chung được đưa ra dưới đây.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

Từ cú pháp,

Tùy chọn FORCE cho phép một dạng xem được tạo ngay cả khi một bảng cơ sở mà dạng xem tham chiếu chưa tồn tại. Tùy chọn này được sử dụng để tạo dạng xem trước khi tạo thực tế các bảng cơ sở và dữ liệu đi kèm.

Tùy chọn NOFORCE ngược lại với FORCE và cho phép người dùng hệ thống tạo chế độ xem nếu họ có các đặc quyền cần thiết để tạo chế độ xem và nếu bảng từ đó chế độ xem được tạo đã tồn tại. Đây là tùy chọn mặc định.

Tùy chọn WITH READ ONLY cho phép tạo một dạng xem chỉ đọc. Bạn không thể sử dụng các lệnh DELETE, INSERT hoặc UPDATE để sửa đổi dữ liệu cho dạng xem chỉ đọc.

Mệnh đề WITH CHECK OPTION cho phép cập nhật các hàng có thể được chọn thông qua khung nhìn, đồng thời cho phép bạn chỉ định các ràng buộc về giá trị. Mệnh đề CONSTRAINT hoạt động cùng với mệnh đề WITH CHECK OPTION để cho phép người quản trị cơ sở dữ liệu gán một tên duy nhất tới CHECK OPTION Nếu quản trị viên cơ sở dữ liệu bỏ qua mệnh đề CONSTRAINT, Oracle sẽ tự động gán cho ràng buộc một tên do hệ thống tạo ra sẽ không có ý nghĩa lắm.

Các loại chế độ xem

Một khung nhìn Đơn giản chỉ được tạo trên đầu một bảng. Đây là một truy vấn SELECT đơn giản không có hàm hoặc mệnh đề nhóm, mà chỉ lựa chọn các cột từ bảng mà không có bất kỳ chuyển đổi nào. Nếu một DML được thực hiện trên khung nhìn, nó sẽ được phản ánh ngay lập tức trong bảng cơ sở.

Dạng xem phức hợp được tạo trên nhiều bảng bằng cách sử dụng phép nối, nó có thể chứa các hàm SQL, Nhóm theo hàm, nhưng vì dạng xem nằm trên nhiều dữ liệu và việc chọn cột cũng không đơn giản nên nó không cho phép thao tác DML trên đó.

Hình minh họa

Simple View: Chế độ xem đơn giản dưới đây chọn tên nhân viên, id bộ phận và mức lương cho các nhân viên có ID CÔNG VIỆC là DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

Complex view: Ví dụ dưới đây hiển thị tên bộ phận, mức lương trung bình được tính trong bộ phận và số lượng nhân viên làm việc trong bộ phận đó.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [view name]mô tả cấu trúc khung nhìn. Các cột được liệt kê theo trình tự giống như trong định nghĩa dạng xem.

Hoạt động DML trên Chế độ xem

Các thao tác DML có thể được thực hiện dễ dàng trên các khung nhìn đơn giản Như đã nêu trước đó, các thao tác chèn, cập nhật và xóa thực sự diễn ra trên bảng cơ sở.

Khi bạn thực thi một câu lệnh UPDATE, DELETE hoặc INSERT DML trên một dạng xem, bạn thực sự đang thao tác các hàng dữ liệu cho bảng cơ sở hoặc các bảng mà dạng xem được xác định. với các khung nhìn. Đầu tiên, để sử dụng câu lệnh UPDATE, DELETE hoặc INSERT với một chế độ xem, chế độ xem phải có thể cập nhật được. Một chế độ xem có thể cập nhật nếu mệnh đề SELECT không chỉ định bất kỳ hàm tổng hợp nào trong danh sách CHỌN. Ngoài ra, chế độ xem không thể cập nhật đã được tạo thông qua việc sử dụng mệnh đề GROUP BY, DISTINCT hoặc UNION. Nó được phép sử dụng các hàm tổng hợp trong truy vấn con SELECT trong mệnh đề FROM. Ngoài ra, dạng xem không thể có bất kỳ cột dẫn xuất nào trong danh sách CHỌN. Tiếp theo, nếu một dạng xem được tạo do kết quả của một phép toán JOIN (một dạng xem nối), thì các câu lệnh UPDATE và INSERT chỉ có thể sửa đổi hoặc chèn các hàng vào một trong các bảng cơ sở tại một thời điểm. Bạn không thể sửa đổi các hàng từ hai hoặc nhiều bảng bằng một câu lệnh ngôn ngữ thao tác dữ liệu (DML). Cuối cùng, câu lệnh DELETE chỉ có thể thực thi đối với một dạng xem nếu một bảng được tham chiếu trong mệnh đề FROM. Điều này chỉ đơn giản có nghĩa là bạn không thể xóa các hàng khỏi bảng chưa được chỉ định.

VỚI điều khoản CHECK OPTION

VỚI CHỌN KIỂM TRA là một mệnh đề tùy chọn chỉ định mức độ kiểm tra được thực hiện khi chèn hoặc cập nhật dữ liệu thông qua một chế độ xem. Nếu một chế độ xem được tạo bằng mệnh đề VỚI CHỌN KIỂM TRA, mọi hàng được chèn hoặc cập nhật trong bảng cơ sở thông qua chế độ xem phải tuân theo định nghĩa khung nhìn. Lưu ý rằng không thể chỉ định tùy chọn nếu chế độ xem được tạo ở dạng chỉ đọc.

Ví dụ: dạng xem V_EMP_DEV được tạo cho nhân viên là nhà phát triển (JOB_ID = DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

Một người dùng cố gắng cập nhật lương của một nhân viên nhân sự thông qua chế độ xem nhưng gặp phải một ngoại lệ. Đó là vì chế độ xem được tạo VỚI LỰA CHỌN KIỂM TRA.

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

Nếu đó là một chế độ xem đơn giản, câu lệnh UPDATE sẽ không nêu ra bất kỳ ngoại lệ nào.

Bỏ chế độ xem

Người quản trị cơ sở dữ liệu (DBA) hoặc chủ sở hữu chế độ xem có thể bỏ một chế độ xem bằng câu lệnh DROP VIEW. Nếu một chế độ xem có các ràng buộc được xác định, thì bạn cần chỉ định mệnh đề CASCADE CONSTRAINTS khi bỏ một chế độ xem; nếu không, câu lệnh DROP VIEW không xử lý được. Nếu một dạng xem khác hoặc đối tượng cơ sở dữ liệu khác như dạng xem đồng nghĩa hoặc dạng vật chất hóa (cả hai đối tượng này sẽ được thảo luận sau trong chương này) tham chiếu đến dạng xem bị loại bỏ, Oracle sẽ không loại bỏ các đối tượng cơ sở dữ liệu này; Thay vào đó, Oracle đánh dấu chúng là không hợp lệ. Bạn có thể loại bỏ các đối tượng không hợp lệ này hoặc xác định lại chúng để làm cho chúng hợp lệ trở lại.

Lệnh DROP VIEW bên dưới loại bỏ chế độ xem EMP_VU khỏi cơ sở dữ liệu.

DROP VIEW EMP_VU;

Trình tự

Oracle cung cấp khả năng tạo chuỗi các số duy nhất cho kiểu sử dụng này và chúng được gọi là chuỗi. Nói chung, chuỗi được sử dụng để tạo các giá trị số nguyên tuần tự, duy nhất được sử dụng làm giá trị khóa chính trong bảng cơ sở dữ liệu. được tạo theo thứ tự tăng dần hoặc giảm dần. Lưu ý rằng một số đã được tạo theo trình tự không thể được cuộn lại.

Cú pháp

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

Từ cú pháp,

Câu lệnh CREATE SEQUENCE phải chỉ định một tên chuỗi duy nhất. Đây là mệnh đề bắt buộc duy nhất trong câu lệnh. Nếu bạn không chỉ định bất kỳ mệnh đề nào khác, tất cả các số thứ tự được tạo sẽ tuân theo cài đặt mặc định của Oracle.

Mệnh đề INCREMENT BY xác định cách một chuỗi tăng lên khi mỗi số được tạo. Mức tăng mặc định là một; tuy nhiên, nếu bạn có lý do chính đáng để một chuỗi bỏ qua số, bạn có thể chỉ định một khoảng tăng khác. Khoảng tăng số dương tạo ra các số thứ tự tăng dần với khoảng thời gian bằng khoảng bạn chọn. Khoảng tăng số âm tạo ra số thứ tự giảm dần.

Mệnh đề START WITH chỉ định giá trị số bắt đầu cho chuỗi - số bắt đầu mặc định là một. Ngoài ra, bạn phải chỉ định giá trị bắt đầu nếu bạn đã có một số hàng với dữ liệu trong cột bây giờ sẽ lưu trữ giá trị chuỗi.

Mệnh đề MAXVALUE chỉ định giá trị lớn nhất mà một chuỗi có thể được tăng lên. Trong trường hợp không có MAXVALUE, giá trị cho phép tối đa có thể được tạo cho một chuỗi là khá lớn, từ 10 đến lũy thừa thứ 27 - 1. Giá trị mặc định là NOMAXVALUE.

Mệnh đề MINVALUE chỉ định giá trị nhỏ nhất của một chuỗi đối với một chuỗi giảm dần (một mệnh đề tạo ra các số theo thứ tự giảm dần). Giá trị mặc định là NOMINVALUE.

Mệnh đề CYCLE chỉ định rằng các giá trị trình tự có thể được sử dụng lại nếu trình tự đạt đến MAXVALUE được chỉ định. Nếu chuỗi chu kỳ, các số được tạo lại bắt đầu từ giá trị BẮT ĐẦU VỚI.

Mệnh đề CACHE có thể cải thiện hiệu suất hệ thống bằng cách cho phép Oracle tạo một loạt các số theo trình tự cụ thể để được lưu trữ trong bộ nhớ đệm.

Nếu bạn chỉ định CACHE mà không chỉ định số, kích thước bộ đệm mặc định là 20 số thứ tự. Tùy chọn, bạn có thể chỉ định NOCACHE để ngăn bộ nhớ cache của số thứ tự.

Mệnh đề ORDER chỉ định rằng số thứ tự được phân bổ theo thứ tự thời gian chính xác mà chúng được yêu cầu.

NEXTVAL và CURRVAL

Giá trị trình tự được tạo thông qua việc sử dụng hai cột giả có tên là currval và giá trị tiếp theo. Một cột giả hoạt động giống như cột bảng, nhưng cột giả không thực sự được lưu trữ trong bảng. Lần đầu tiên bạn chọn cột giả giá trị tiếp theo, giá trị ban đầu trong trình tự được trả về. Các lựa chọn tiếp theo của cột giả giá trị tiếp theo làm cho trình tự tăng lên như được chỉ định trong mệnh đề INCREMENT BY và trả về giá trị trình tự mới được tạo. Cột giả giá trị trả về giá trị hiện tại của chuỗi, là giá trị được trả về bởi tham chiếu cuối cùng đến nextval.

Trong một phiên, NEXTVAL chứ không phải CURRVAL phải là hành động đầu tiên trên chuỗi. Điều này là do trong một phiên, khi NEXTVAL tạo ra số đầu tiên của phiên từ chuỗi, Oracle sẽ giữ giá trị hiện tại trong CURRVAL.

Cú pháp:

Sequence.NEXTVAL
Sequence.CURRVAL

Những điểm cần lưu ý -

• CURRVAL và NEXTVAL chỉ có thể được sử dụng trong SQL ngoài của một câu lệnh select.

• CURRVAL và NEXTVAL có thể được sử dụng trong câu lệnh INSERT để thay thế khóa chính của cột. Nó có thể được sử dụng như một mệnh đề truy vấn con và cũng như trong mệnh đề VALUES.

• CURRVAL và NEXTVAL có thể được sử dụng để cập nhật các giá trị trong bảng.

• CURRVAL và NEXTVAL không thể có trong danh sách chọn VIEW, với từ khóa DISTINCT, với các mệnh đề GROUP BY, HAVING hoặc ORDER BY và biểu thức DEFAULT trong câu lệnh CREATE TABLE hoặc ALTER TABLE.

Sửa đổi trình tự

Chủ sở hữu chuỗi chỉ có thể sửa đổi một chuỗi để thay đổi các thuộc tính như INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE hoặc CACHE chỉ các mệnh đề. Lưu ý rằng những thay đổi được thực hiện sẽ được phản ánh trong các số sắp tới.

Cú pháp:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Bỏ trình tự

Lệnh DROP SEQUENCE bỏ các chuỗi cần được tạo lại hoặc không còn cần thiết.

DROP SEQUENCE [sequence name]

Chỉ mục

Chỉ mục là các đối tượng cơ sở dữ liệu được sử dụng để điều chỉnh hiệu suất của truy vấn SELECT. Có các loại chỉ mục khác nhau bao gồm các loại chỉ mục được sử dụng để thực thi các ràng buộc khóa chính, chỉ mục duy nhất, chỉ mục không duy nhất và chỉ mục nối, trong số các loại khác. Các truy vấn sẽ yêu cầu Oracle quét tất cả các hàng trong bảng để trả về các hàng cần thiết cho bảng kết quả. Một chỉ mục được tạo trên các cột của bảng, sau đó sẽ lưu trữ tất cả các giá trị của cột dưới phân đoạn chỉ mục. cụ thể. Chúng sẽ tự động bị loại bỏ khi bảng đã bị loại bỏ.

Các chỉ mục có thể được tạo tự động hoặc thủ công. Khi bạn chỉ định ràng buộc CHÍNH CHÍNH hoặc ràng buộc DUY NHẤT, Oracle sẽ tự động tạo một chỉ mục duy nhất để hỗ trợ truy xuất dữ liệu nhanh chóng cho bảng được chỉ định.

Ngoài ra, người dùng có thể tạo chỉ mục theo cách thủ công để tối ưu hóa hiệu suất truy vấn. Các chỉ mục được tạo thủ công có thể là duy nhất hoặc không duy nhất. Chỉ mục không duy nhất có thể là B-Tree, Bitmap hoặc chỉ mục dựa trên Hàm. Theo mặc định, Oracle tạo chỉ mục B-Tree trên các cột . Đây là cú pháp

Cú pháp

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

Lưu ý rằng UNIQUE và BITMAP chỉ phải được chỉ định cho các chỉ mục bitmap và duy nhất. Theo mặc định, Oracle tạo chỉ mục B-Tree cho các chỉ mục bình thường.

Chỉ mục tổng hợp (còn được gọi là chỉ mục nối) là chỉ mục được tạo trên nhiều cột của bảng. Các cột trong chỉ mục tổng hợp có thể xuất hiện theo bất kỳ thứ tự nào và không cần phải là các cột liền kề trong bảng. Chỉ mục tổng hợp nâng cao tốc độ truy xuất hàng cho các truy vấn trong đó mệnh đề WHERE tham chiếu đến tất cả hoặc phần đầu của các cột trong chỉ mục tổng hợp. Một chỉ mục có thể chứa tối đa 32 cột.

Ví dụ: người dùng tạo chỉ mục IDX_EMP trên cột HIRE_DATE của bảng EMPLOYEES. Việc sử dụng chỉ mục sẽ giảm I / O đĩa bằng cách quét qua đường dẫn được lập chỉ mục và tìm dữ liệu được lọc trên cột HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Giảm chỉ số

Chỉ mục không thể được sửa đổi nhưng có thể được thay đổi cho các mục đích phân tích, xây dựng lại hoặc tính toán số liệu thống kê. Nếu định nghĩa chỉ mục phải được sửa đổi, nó phải được loại bỏ và tạo lại. Cú pháp của lệnh DROP INDEX rất đơn giản.

DROP INDEX index_name;

Từ đồng nghĩa

Từ đồng nghĩa là một bí danh, có nghĩa là, một dạng viết tắt được sử dụng để đơn giản hóa nhiệm vụ tham chiếu đến một đối tượng cơ sở dữ liệu. Khái niệm này tương tự như việc sử dụng biệt hiệu cho bạn bè và người quen. được bắt đầu với nó. Với sự trợ giúp của một từ đồng nghĩa, bạn sẽ giảm bớt nỗ lực tham chiếu đối tượng cùng với tên lược đồ. Bằng cách này, từ đồng nghĩa mang lại sự minh bạch về vị trí vì tên đồng nghĩa ẩn tên đối tượng thực và chủ sở hữu của nó.

Có hai loại từ đồng nghĩa, công khai và riêng tư. Từ đồng nghĩa công khai có thể được sử dụng để cho phép dễ dàng truy cập vào một đối tượng cho tất cả người dùng hệ thống. Trên thực tế, cá nhân tạo từ đồng nghĩa công khai không sở hữu từ đồng nghĩa - đúng hơn, nó sẽ thuộc về nhóm người dùng PUBLIC tồn tại trong Oracle. Mặt khác, từ đồng nghĩa riêng tư thuộc về người dùng hệ thống tạo ra chúng và cư trú trong đó lược đồ của người dùng.

Cú pháp

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

Người dùng hệ thống có thể cấp đặc quyền sử dụng các từ đồng nghĩa riêng tư mà họ sở hữu cho những người dùng hệ thống khác. Để tạo từ đồng nghĩa, bạn cần có đặc quyền TẠO ĐỒNG BỘ. Nếu một từ đồng nghĩa được khai báo là công khai, thì tên đồng nghĩa đó không thể được sử dụng làm từ đồng nghĩa công khai. Cố gắng tạo một từ đồng nghĩa công khai đã tồn tại sẽ khiến lệnh CREATE PUBLIC SYNONYM không thành công và Oracle sẽ trả về ORA-00955: tên đã được sử dụng bởi một thông báo lỗi đối tượng hiện có.

Hình minh họa

Hãy xem xét hai người dùng U1 và U2.U1 có quyền truy cập vào bảng NHÂN VIÊN. Vì vậy, để cho phép truy cập trên bảng EMPLOYEES tới U2, một từ đồng nghĩa có thể được tạo trong lược đồ U2. Quyền truy cập phải được cấp bởi U1 đến U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Bỏ từ đồng nghĩa

Một uer có thể bỏ từ đồng nghĩa mà nó sở hữu. Để loại bỏ một từ đồng nghĩa công khai, bạn phải có đặc quyền DROP PUBLIC SYNONYM.

DROP SYNONYM EMP_SYN;