в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:02Z

1 версия

Новая страница

== Add a column with default value==



<source lang="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>

== Date type column with current date as the default value==



<source lang="sql">
SQL>
SQL>
SQL> create table WORK_T (
2 id number,
3 code varchar2(30),
4 name varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL> drop table WORK_T;
Table dropped.
SQL>
SQL>
SQL></source>

== Insert record into a table with default value==



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable (id)
2 VALUES (1);
1 row created.
SQL>
SQL> select * from myTable;
ID STATUS LAST_MODI
---------- -------------------- ---------
1 Order placed 02-JUN-07
SQL>
SQL> drop table myTable;
Table dropped.
SQL></source>

== null with default value column==



<source lang="sql">
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.</source>

== Override the defaults by specifying a value for the columns==



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO myTable (id, status, last_modified)
2 VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL> select * from myTable;
ID STATUS LAST_MODI
---------- -------------------- ---------
2 Order shipped 10-JUN-04
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== Update a column and set it back to the default using the DEFAULT keyword in an UPDATE statement==



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable (id, status, last_modified)
2 VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL>
SQL> UPDATE myTable
2 SET status = DEFAULT;
1 row updated.
SQL>
SQL> select * from myTable
2
SQL> drop table myTable;
Table dropped.
SQL></source>

== Update statement with default==



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO myTable (id, status, last_modified)
2 VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL>
SQL>
SQL> UPDATE myTable
2 SET status = DEFAULT;
1 row updated.
SQL>
SQL> select * from myTable
2
SQL> drop table myTable;
Table dropped.
SQL></source>

== Using Default Values==

<p>Define a default value for a column.</p>
<p>last_modified column is defaulted to the value returned by the SYSDATE function.</p>



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 status VARCHAR2(20) DEFAULT "Order placed" NOT NULL,
4 last_modified DATE DEFAULT SYSDATE
5 );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Table/Default Column Value - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия