Oracle PL/SQL Tutorial/Table/Add Column
Содержание
Add a column with NOT NULL constraint
<source lang="sql">
SQL> SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> SQL> alter table registrations
2 add (entered_by number(4) default 7839 not null);
Table altered. SQL> SQL> drop table registrations; Table dropped.</source>
Add columns
<source lang="sql">
SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY, 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE 6 );
Table created. SQL> SQL> INSERT INTO product VALUES ("Product 1", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 3", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 4", 25, 10000, null); 1 row created. SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04"); 1 row created. SQL> INSERT INTO product VALUES ("Product 6", 45, 1, TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M.")); 1 row created. SQL> SQL> DESC product;
Name Null? Type ----------------------------------------------------- -------- ------------------------------------ PRODUCT_NAME NOT NULL VARCHAR2(25) PRODUCT_PRICE NUMBER(4,2) QUANTITY_ON_HAND NUMBER(5) LAST_STOCK_DATE DATE
SQL> SQL> ALTER TABLE product ADD data_load_date VARCHAR2(8) NULL; Table altered. SQL> SQL> DESC product;
Name Null? Type ----------------------------------------------------- -------- ------------------------------------ PRODUCT_NAME NOT NULL VARCHAR2(25) PRODUCT_PRICE NUMBER(4,2) QUANTITY_ON_HAND NUMBER(5) LAST_STOCK_DATE DATE DATA_LOAD_DATE VARCHAR2(8)
SQL> SQL> drop table product; Table dropped. SQL></source>
Add more columns
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (
2 Name VARCHAR(50) PRIMARY KEY NOT NULL, 3 PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", 4 SSN VARCHAR(15) NOT NULL, 5 CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));
Table created. SQL> SQL> SQL> SQL> ALTER TABLE myTable ADD (EMail VARCHAR(25), ICQ VARCHAR(15)); Table altered. SQL> SQL> drop table myTable; Table dropped. SQL></source>
Add new column with default value and not null
<source lang="sql">
SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key (attendee,course,begindate) 7 ) ;
Table created. SQL> SQL> SQL> alter table registrations
2 add (entered_by number(4) default 9 not null);
Table altered. SQL> SQL> SQL> drop table registrations; Table dropped.</source>
