Oracle PL/SQL/Table/Column Default Value
Версия от 13:45, 26 мая 2010; (обсуждение)
Содержание
- 1 column with default as sysdate
- 2 Create table with column having the default value
- 3 Default char value
- 4 Default varchar value
- 5 null with default value column
- 6 Override the defaults by specifying a value for the columns
- 7 Use char function to build default column value
- 8 Use rpad to fill default value to a column
column with default as sysdate
SQL>
SQL> create table employee_history
2 ( employee_id number(6) not null,
3 salary number(8,2),
4 hire_date date default sysdate,
5 termination_date date,
6 termination_desc varchar2(4000),
7 constraint emphistory_pk
8 primary key (employee_id, hire_date)
9 )
10 /
Table created.
SQL> describe employee_history
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
SALARY NUMBER(8,2)
HIRE_DATE NOT NULL DATE
TERMINATION_DATE DATE
TERMINATION_DESC VARCHAR2(4000)
SQL>
SQL>
SQL> drop table employee_history;
Table dropped.
SQL>
Create table with column having the default value
SQL> CREATE TABLE order_status (
2 order_status_id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL> --Override the defaults by specifying a value for the columns
SQL>
SQL> INSERT INTO order_status (order_status_id, status, last_modified)
2 VALUES (2, "Order shipped", to_date("19960917","YYYYMMDD"));
1 row created.
SQL>
SQL> INSERT INTO order_status (order_status_id)
2 VALUES (2 );
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM order_status;
ORDER_STATUS_ID STATUS LAST_MODIFI
--------------- -------------------- -----------
2 Order shipped 1996-SEP-17
2 Order placed 2006-SEP-17
SQL>
SQL> drop table order_status;
Table dropped.
SQL>
SQL>
SQL>
Default char value
SQL> create table t
2 (id number primary key,
3 status char(1) default "Y")
4 /
Table created.
SQL>
SQL> create sequence s;
Sequence created.
SQL>
SQL> insert into t
2 (id)
3 values(s.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> insert into t
2 (id, status)
3 values(s.nextval, "N");
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> drop table t;
Table dropped.
SQL> drop sequence s;
Sequence dropped.
SQL>
Default varchar value
SQL>
SQL>
SQL> CREATE TABLE myTable (
2 Name VARCHAR(50) NOT NULL,
3 PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable (Name, PhoneNo) VALUES ("Tom", "555 2323");
1 row created.
SQL> INSERT INTO myTable (Name) VALUES ("Tom");
1 row created.
SQL> INSERT INTO myTable (Name, PhoneNo) VALUES ("Tom", NULL);
INSERT INTO myTable (Name, PhoneNo) VALUES ("Tom", NULL)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("sqle"."MYTABLE"."PHONENO")
SQL>
SQL> DROP TABLE myTable;
Table dropped.
null with default value column
SQL>
SQL>
SQL> create table t
2 (id number primary key,
3 status char(1) default "Y")
4 /
Table created.
SQL>
SQL> create sequence s;
Sequence created.
SQL>
SQL> insert into t
2 (id)
3 values(s.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> insert into t
2 (id, status)
3 values(s.nextval, "N");
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> drop table t;
Table dropped.
Override the defaults by specifying a value for the columns
SQL>
SQL> CREATE TABLE order_status (
2 order_status_id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL> --Override the defaults by specifying a value for the columns
SQL>
SQL> INSERT INTO order_status (order_status_id, status, last_modified)
2 VALUES (2, "Order shipped", to_date("19960917","YYYYMMDD"));
1 row created.
SQL>
SQL>
SQL> SELECT * FROM order_status;
ORDER_STATUS_ID STATUS LAST_MODIFI
--------------- -------------------- -----------
2 Order shipped 1996-SEP-17
SQL>
SQL> drop table order_status;
Table dropped.
SQL>
Use char function to build default column value
SQL>
SQL> create table t
2 ( a int,
3 b varchar2(4000) default rpad("*",4000,"*"),
4 c varchar2(3000) default rpad("*",3000,"*")
5 )
6 /
Table created.
SQL> insert into t (a) values ( 1);
1 row created.
SQL> insert into t (a) values ( 2);
1 row created.
SQL> insert into t (a) values ( 3);
1 row created.
SQL>
SQL> delete from t where a = 2 ;
1 row deleted.
SQL>
SQL> insert into t (a) values ( 4);
1 row created.
SQL> drop table t;
Table dropped.
SQL>
Use rpad to fill default value to a column
SQL>
SQL> set echo on
SQL>
SQL> create table t
2 ( a int,
3 b varchar2(40) default rpad("*",40,"*"),
4 c varchar2(30) default rpad("*",30,"*" )
5 )
6 /
Table created.
SQL>
SQL> insert into t(a) select rownum from all_users;
14 rows created.
SQL> update t set b = null, c = null;
14 rows updated.
SQL>
SQL> set serveroutput on
SQL>
SQL> exec show_space( "T" )
Free Blocks
1
Total Blocks
8
Total Bytes
65536
Unused Blocks
6
Unused Bytes
49152
Last Used Ext FileId
1
Last Used Ext BlockId
33145
Last Used Block
2
PL/SQL procedure successfully completed.
SQL>
SQL> insert into t(a) select rownum+1000 from all_users;
14 rows created.
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid), a from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
33146 1
33146 2
33146 3
33146 4
33146 5
33146 6
33146 7
33146 8
33146 9
33146 10
33146 11
33146 12
33146 13
33146 14
33146 1001
33146 1002
33146 1003
33146 1004
33146 1005
33146 1006
33146 1007
33146 1008
33146 1009
33146 1010
33146 1011
33146 1012
33146 1013
33146 1014
28 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --