Skip to content

ddl.md

Latest commit

 

History

History
228 lines (199 loc) · 9.37 KB

ddl.md

File metadata and controls

228 lines (199 loc) · 9.37 KB
  • order by 1: 1 là index của cột

Insert All

  • insert all => cần select * from dual
  • cú pháp 
    insert all
into store (a, b) values (c, d)
into store (a, b) values (c, d)
into store (a, b) values (c, d)
select * from dual

hoặc

insert all
into store (a, b) values (c, d)
into store (a, b) values (c, d)
into store (a, b) values (c, d)
select a from b
  • thêm điều kiện
insert all
when sal < 500 then
into store (a, b) values (c, d)
into store (a, b) values (c, d)
into store (a, b) values (c, d)
when sal > 500 then
into store (a, b) values (c, d)
into store (a, b) values (c, d)
else
into store (a, b) values (c, d)			
select a from b
  • khi sử dụng insert all, tất cả các điều kiện của when đều sẽ được tính toán xem true/false mà không quan tâm các when khác

insert first

  • tính toán từng điều kiện của when dựa vào thứ tự nó xuất hiện
  • khi gặp 1 điều kiện phù hợp, nó thực hiện phần statement trong then và bỏ qua hết các when khác. Nếu không, nó tiếp tục chạy các when khác

alter

  • sửa cột:
alter table {table} modify {column} {data type} not null

hoặc

alter table {table} modify ({column1} {data type} not null,
  {column2} {data type} not null)

rename

alter table {table} rename {column1} to {column2}

CTAS (CREATE TABLE AS SELECT)

  • CREATE TABLE AS: tạo bảng bằng cách copy dữ liệu từ các bảng khác sang
  • https://www.techonthenet.com/oracle/tables/create_table2.php
  • In a CTAS operation:
    • NOT NULL (Exception) constraints are preserved, if present in the source table.
    • DEFAULT values can be specified in the column definitions of the new table.
    • Foreign keys and other constraints (like primary keys, unique, check) are not inherited.

SET UNUSED

  • cột sẽ trở nên invisible và inaccessible
alter table {table_name} set unused colum {column_name} [online]

hoặc

alter table {table_name} set unused ({column_name1}, {column_name2})
  • drop:
alter table {table_name} drop unused colums
  • ONLINE: ở cuối câu, chỉ định rằng DML được phép khi đang mark unused, còn nếu không sẽ lock lại
  • Sau khi set unused: A new column can be added to the table with the same name as an unused column, as the unused column's name is removed from the data dictionary.
  • Dropping a column from a table will cause all columns marked as UNUSED in that table to be dropped at the same time.
  • used column có tính vào limit column của bảng (tối đa 1000 cột cho 1 bảng)

READ ONLY TABLE

  • chỉ đọc
alter table {table_name} read only
  • cho phép ghi trở lại
alter table {table_name} read write

COMMENT

  • cú pháp:
comment on column {table_name}.{column_name} IS {comment}

Pivoting insert

savepoint

  • tạo điểm để quay về
  • khi gọi rollback, nếu không có savepoint đi kèm => trở về commit gần nhất
  • commit wil delete all save point

MERGE

  • sử dụng để insert new record, và update/delete record đã tồn tại tùy thuộc vào điều kiện
  • cú pháp
MERGE INTO {dest_table}
USING {source_table}
ON {join condition}
WHEN MATCHED THEN
[UPDATE SET WHERE]
[DELETE WHERE]
WHEN NOT MATCHED THEN
 INSERT {} VALUES {}
  • INSERT is not allowed in WHEN MATCHED THEN
  • The WHERE clause cannot be used directly inside INSERT in MERGE. => có vẻ ko đúng
  • Oracle does not allow the primary key column to be updated in a MERGE statement if that column is also part of the join condition (ON (c.customer_id = cv.customer_id)) — which it is here.
  • https://www.oracletutorial.com/oracle-basics/oracle-merge/

alter index ... rebuild compute statistics

check constraint

  • A check constraint allows you to specify a condition on each row in a table.
  • A check constraint can NOT be defined on a SQL View.
  • The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
  • A check constraint can NOT include a SQL Subquery.
  • A check constraint can be defined in either a SQL CREATE TABLE statement or a SQL ALTER TABLE statement.
  • cú pháp:
CREATE TABLE table_name
(
column1 datatype null/not null CHECK colum1 id in (10,20), -- check colum
column2 datatype null/not null,

...

CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] -- check table

);
  • The CREATE TABLE order_items statement fails because the CHECK constraint on the expiry_date column references the SYSDATE function, which is not allowed in a CHECK constraint. The SYSDATE function is non-deterministic and cannot be used in a CHECK constraint.

disable constraint

  • khi disable unique constraint, oracle remove unique index. Khi enable => tạo lại. Nếu muốn giữ index => sử dụng keyword KEEP INDEX
    • nếu disable cascade => tự động disable foreign key và khi enable cần chủ động enable lại vì ko có enable cascade
  • cú pháp:
alter table {table} disable|enable [validate|novalidate] constraint {name} [cascade]
  • validate: đảm bảo exist data phù hợp điều kiện constraint
  • novalidate: dừng việc check => 4 cú pháp:
    • enable validate: validate lại với các row cũ và sẽ insert thêm
    • enable novalidate: ko validate row cũ, chỉ validate row insert thêm
    • disable validate: constraint disable, nhưng còn hiệu lực và DML trên bảng này là không được phép (read only)
    • disable novalidate: default của disable

deferring constraint

  • tạm thời dừng việc validate constraint cho đến khi transaction kết thúc
  • có 2 trạng thái
    • deferrable: cho phép dừng check. có 2 mode
      • initially immediate (default): check ngay sau mỗi câu query
      • initially deferred: check khi commit
    • not deferrable (default): không cho dừng, và ko thể alter để chuyển thành deferrable
  • tạo
alter table {table} add constraint {constraint} primary key (column) [not] deferrable [initially deferred]
  • thay đổi mode
set constraint {constraint} {column1},{column2} immediate|deferred
set constraint all immediate|deferred

drop table

  • Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.
  • All indexes and triggers associated with a table are dropped.
  • All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable).
  • All synonyms for a dropped table remain, but return an error when used.
  • All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster.
  • All sequences for a dropped table remain and become orphaned but are not affected by dropping a table.

truncate

  • truncate là DDL và ko thể rollback (khác delete)
  • truncate cần quyền DROP_ANY_TABLE
  • truncate not require cascade
  • TRUNCATE is a DDL (Data Definition Language) command that removes all rows from a table faster and with less undo/redo than a DELETE.
  • It cannot be rolled back.
  • It preserves structure, constraints, and indexes, but does not fire triggers and does not log row-level changes. => xem alij
  • DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.
    • DELETE can have a WHERE clause to target specific rows:
    • TRUNCATE removes all rows from a table without a WHERE clause.
    • TRUNCATE is not selective — it's a bulk operation.
    • For large tables TRUNCATE is faster than DELETE.

commit

  • ddl thường sẽ thêm commit trước và sau khi thực hiện. ví dụ create table thì sẽ có 2 câu commit ngầm: trước khi tạo bảng và sau khi tạo bảng
    • nếu câu tạo bảng bị lỗi => chỉ có 1 câu commit trước khi thực hiện
  • If a DDL statement like CREATE TABLE AS SELECT encounters a syntax error, it fails and does not implicitly commit. Only successful DDL operations cause an implicit commit.

other

  • delete: from not required
  • khi thực hiện update/delete mà chưa commit/rollback => lock row
  • FOR UPDATE: chỉ định lock row
    • NOWAIT: không đợi
    • for update skip locked: bỏ qua các row bị lock
  • number(5,2): số chữ số trước dấu phẩy là 3, sau dấu phẩy là 2, tổng là 5
  • cú pháp tạo primary key khi tạo bảng: CONSTRAINT name PRIMARY KEY (column)
  • when insert to varchar2 column, empty string ('') treat as null value
  • DDL statements do not include GRANT and REVOKE privileges and roles
    • In Oracle site Types of SQL Statements Grant and Revoke belongs to DDL but in lot of other sites GRANT and REVOKE are considered DCL.
    • In most (but not all) cases you are best off following Oracle's definition unless you have a strong reason to do otherwise, especially since you are preparing for an Oracle exam.