Common Oracle modification statements and oracle modification statements

Common Oracle modification statements and oracle modification statements
I. Table

Test Table: MY_TEST_TABLE

-- Create tablecreate table MY_TEST_TABLE(  A VARCHAR2(30),  B NUMBER(10) default 1,  C DATE)tablespace BOSSWG_CFG  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );-- Add comments to the table comment on table MY_TEST_TABLE  is 'test';-- Add comments to the columns comment on column MY_TEST_TABLE.A  is 'aaaa';comment on column MY_TEST_TABLE.B  is 'bbbb';comment on column MY_TEST_TABLE.C  is 'cccc';

1. Modify the table field name

Alter table MY_TEST_TABLE rename column A to D; // change the field name of a to D.

2. Modify the table Field Type

Alter table MY_TEST_TABLE modify B varchar2 (10); // modify the number (10) type of field B to varchar2 (10)

3. Modify the default table field values.

Alter table MY_TEST_TABLE modify B default 2; // change the default value of field B 1 to 2.

4. Modify the non-empty attribute of the table Field

Alter table MY_TEST_TABLE modify B not null; // change field B to non-empty

5. Modify Table field remarks

Comment on column MY_TEST_TABLE. B // modify the comment value of field B
Is 'bbbb2 ';

6. Primary Key operations

Alter table MY_TEST_TABLE
Add constraint PK_MY_TEST_TABLE primary key (A); // add A primary key

Alter table MY_TEST_TABLE
Drop constraint PK_MY_TEST_TABLE cascade; // Delete the primary key

// If You Want To modify the primary key, you must first Delete the primary key and then add the primary key.

7. check Constraints

Alter table MY_TEST_TABLE
Add constraint CKC_MY_TEST_TABLE_ B // add Constraints
Check (B in (1, 2, 3 ));

Alter table MY_TEST_TABLE
Drop constraint CKC_MY_TEST_TABLE_ B; // Delete the constraint

// If You Want To modify the constraint, you must first Delete the constraint and then add the constraint.

8. Foreign key operations

Alter table MY_TEST_TABLE
Add constraint FK_MY_TEST_DTABLE_A foreign key (A) // add A foreign key
References MY_TEST_TABLE2 (A) on delete cascade;

Alter table MY_TEST_TABLE
Drop constraint FK_MY_TEST_DTABLE_A; // delete a foreign key

// If You Want To modify the foreign key, you must first Delete the foreign key and then add the foreign key

Ii. Sequence SEQ

Test sequence: MY_TEST_SEQ

create sequence MY_TEST_SEQminvalue 1maxvalue 9999999999start with 1increment by 1cache 20;

1. Modify minvalue and maxvalue values:

Alter sequence MY_TEST_SEQ
Minvalue 20
Maxvalue 99999999999;

2. Modify the nextvalue:

Alter sequence MY_TEST_SEQ increment by 8 nocache; // changed from nextvalue to 2 and nextvalue to 10
Select MY_TEST_SEQ.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; // This statement must exist. Otherwise, the cache and growth rate will be reset.

Alter sequence MY_TEST_SEQ increment by-8 nocache; // changed from nextvalue to 10 and nextvalue to 2
Select MY_TEST_SEQ.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; // This statement must exist. Otherwise, the cache and growth rate will be reset.

How to change a field value of a table in an Oracle statement?

Update "table_name" set "salary" = "salary" + 1000;
Commit work;

ORACLE Field Value Replacement change statement

Update table set a = case when a = '1' then 'a 'when A = '2' then 'B' when a = '3' then 'C' when a =' 4 'thend ';
I wonder if this is the result you want?