DDL
Data Definition Language
An Oracle database is comprised of the physical structures used to store,
manage, secure, and retrieve data.
A tablespace is the logical structure that allows you to manage a group of
datafiles.
Datafiles are the physical datafiles on disk.
The table is the object that stores data in a database.
Oracle provides a wide variety of table types. The default table type is heaporganized.
For most applications, a heap-organized table is an effective
structure for storing and retrieving data.
More on Datatypes---
¡Note If you insert values into a NUMBER(n,m) column and you exceed precision n, you get an error
message. If you exceed scale m, the Oracle DBMS rounds the value.
The Oracle DBMS supports many datatype synonyms for portability with other DBMS
implementations and for compliance with the ANSI/ISO standard. For example, CHARACTER is
identical to CHAR; DECIMAL(n,m) is identical to NUMBER(n,m); and NUMBER even has multiple synonyms,
such as INTEGER, REAL, and SMALLINT.
Each Oracle datatype has its own precision or length limits
shows 2000 and 4000 for the CHAR and VARCHAR2
datatype limits, respectively. You might wonder in which unit these numbers are expressed. That
depends on the value of the NLS_LENGTH_SEMANTICS parameter. The default for the
Oracle DBMS is to use BYTE length semantics
Creating a Heap-Organized Table
You use the CREATE TABLE statement to create tables. When creating a table, at minimum you must
specify the table name, column name(s), and data types associated with the columns. The Oracle default
table type is heap-organized. The term heap means that the data isnft stored in a specific order in the
table (instead, itfs a heap of data). Herefs a simple example of creating a heap-organized table with four
columns:
create table d_sources(
d_source_id number not null,
source_type varchar2(32),
create_dtt date default sysdate not null,
update_dtt timestamp(5)
);
If you donft specify a tablespace, then the table is created in the default permanent tablespace of the
user that creates the table
Implementing Virtual Columns
With Oracle Database 11g and higher, you can create a virtual column as part of your table
definition. A virtual column is based on one or more existing columns from the same table
and/or a combination of constants, SQL functions, and user-defined PL/SQL functions.
Virtual columns arenft stored on disk; theyfre evaluated at runtime when the SQL query
executes. Virtual columns can be indexed and can have stored statistics.
create table inv(
inv_id number
,inv_count number
,inv_status as (
case when inv_count <= 100 then 'GETTING LOW'
when inv_count > 100 then 'OKAY'
end)
);
SQL> insert into inv (inv_id, inv_count) values (1,100);
SQL> select * from inv;
Here is some sample output:
INV_ID INV_COUNT INV_STATUS
---------- ---------- -----------
1 100 GETTING LOW
Creating a Table from a Query
create table cwp_user_profile_101910
as select * from cwp_user_profile;
create table cwp_user_profile_test
as select * from cwp_user_profile
where 1=2;
Modifying a Table
Renaming a Table
There are a couple of reasons for renaming a table:
....To make the table conform to standards
....To better determine whether the table is being used before you drop it
This example renames a table from INV_MGMT to INV_MGMT_OLD:
SQL> rename inv_mgmt to inv_mgmt_old;
Adding a Column
Use the ALTER TABLE ... ADD statement to add a column to a table. This example adds a column to the
INV table:
SQL> alter table inv add(inv_count number);
Altering a Column
Occasionally, you need to alter a column to adjust its size or change its data type. Use the
ALTER TABLE
... MODIFY statement to adjust the size of a column. This example changes the size of a
column to 256
characters:
SQL> alter table inv modify inv_desc varchar2(256);
If any rows have a NULL value for the column youfre modifying to NOT NULL, then you must
first update the column to contain a value. Here is an example of modifying a column to
NOT NULL:
SQL> alter table inv modify(inv_desc not null);
You can also alter the column to have a default value. The default value is used any time a
record is
inserted into the table but no value is provided for a column:
SQL> alter table inv modify(inv_desc default 'No Desc');
If you want to remove the default value of a column, then set it to be NULL:
SQL> alter table inv modify(inv_desc default NULL);
Renaming a Column
There are a couple of reasons to rename a column:
....Sometimes requirements change, and you want to modify the column name to
better reflect what the column is used for.
....If youfre planning to drop a column, it doesnft hurt to rename the column first to
better determine whether any users or applications are accessing the column.
SQL> alter table inv rename column inv_count to inv_amt;
Dropping a Column
SQL> alter table inv drop (inv_name);
SQL> alter table inv set unused (inv_name);
SQL> alter table inv drop unused columns;
Displaying Table DDL
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
Dropping a Table
SQL> drop table inventory;
If you attempt to drop a parent table that has a primary key or unique keys referenced as a foreign
key in a child table, you see an error such as
ORA-02449: unique/primary keys in table referenced by foreign keys
You need to either drop the referenced foreign-key constraint(s) or use the CASCADE CONSTRAINTS
option when dropping the parent table:
SQL> drop table inventory cascade constraints;
You must be the owner of the table or have the DROP ANY TABLE system privilege to drop a table. If
you have the DROP ANY TABLE privilege, you can drop a table in a different schema by prepending the
schema name to the table name:
SQL> drop table inv_mgmt.inventory;
Undropping a Table
Suppose you accidentally drop a table, and you want to restore it. First, verify that the table you want to
restore is in the recycle bin:
SQL> show recyclebin;
Here is some sample output:
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
PURCHASES BIN$YzqK0hN3Fh/gQHdAPLFgMA==$0 TABLE 2009-02-18:17:23:15
Next, use the FLASHBACK TABLE...TO BEFORE DROP statement to recover the dropped table:
SQL> flashback table purchases to before drop;
Note You canft FLASHBACK TABLE...TO BEFORE DROP for a table created in the SYSTEM tablespace
Removing Data from a Table
You can use either the DELETE statement or the TRUNCATE statement to remove records from a table. You
need to be aware of some important differences between these two approaches
Features of DELETE and TRUNCATE
Using DELETE
One big difference is that the DELETE statement can be either committed or rolled back. Committing a
DELETE statement makes the changes permanent:
SQL> delete from inv;
SQL> commit;
If you issue a ROLLBACK statement instead of COMMIT, the table contains data as it was before the
DELETE was issued.
Using TRUNCATE
TRUNCATE is a DDL statement. This means Oracle automatically commits the statement (and the current
transaction) after it runs, so there is no way to roll back a TRUNCATE statement. If you need the option of
choosing to roll back (instead of committing) when removing data, then you should use the DELETE
statement. However, the DELETE statement has the disadvantage that it generates a great deal of undo
and redo information. Thus for large tables, a TRUNCATE statement is usually the most efficient way to
remove data.
SQL> truncate table computer_systems;
Data Manipulation.
how to change the contents of an Oracle database. The SQL commands to change the database contents are commonly referred to
as Data Manipulation Language (DML) commands.
The first four sections of this chapter cover the DML commands INSERT, UPDATE, DELETE,
and MERGE.
The INSERT Command
Standard INSERT Commands
The standard INSERT command supports the following two ways to insert rows:
. Use the VALUES clause, followed by a list of column values (between parentheses).
This method allows you to insert only one row at a time per execution of the INSERT
command.
. Formulate a subquery, thus using existing data to generate new rows.
You can use the reserved word NULL to specify a null value for a specific column. You can also
specify the reserved word DEFAULT to instruct the Oracle DBMS to insert the default value associated
with the corresponding column
If you donft specify a value for a specific column in your INSERT
statement, there are two possibilities:
. If the column has an associated DEFAULT value, the Oracle DBMS will insert that value.
. If you did not define a DEFAULT value for the column, the Oracle DBMS inserts a null
value (provided, of course, that the column allows null values).
.
QL> CREATE TABLE SampleUPD
2 (
3 SampID NUMBER(2)
4 DEFAULT 99,
5 SampName VARCHAR2(10)
6 DEFAULT 'NOT GIVEN',
7 SampDate DATE
8 DEFAULT SYSDATE
9 );
Table created.
SQL> INSERT INTO SampleUPD
2 VALUES(1, 'SAMPLE01', '26-JUN-09');
1 row created.
SQL> INSERT INTO SampleUPD
2 VALUES(2, 'SAMPLE02', '27-JUN-09');
1 row created.
1 INSERT INTO SampleUPD(SampID, SampName)
2* VALUES(3, 'SAMPLE03')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 INSERT INTO SampleUPD(SampID, SampDate)
2* VALUES(3, '21-JUN-09')
SQL> /
1 INSERT INTO SampleUPD(SampName, SampDate)
2* VALUES('SAMPLE04', '21-JUN-09')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 INSERT INTO SampleUPD(SampID, SampName, SampDate)
2* VALUES(4, 'SAMPLE04', DEFAULT)
SQL> /
1 row created.
SQL> SELECT * FROM SampleUPD;
SAMPID SAMPNAME SAMPDATE
---------- ---------- ---------
1 SAMPLE01 26-JUN-09
2 SAMPLE02 27-JUN-09
3 SAMPLE03 29-JUN-09
3 NOT GIVEN 21-JUN-09
99 SAMPLE04 21-JUN-09
4 SAMPLE04 29-JUN-09
SQL> CREATE TABLE MyBonus
2 (
3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );
Table created.
SQL> SELECT E.Empno
2 FROM Emp E
3 WHERE Job = 'SALESMAN';
EMPNO
----------
7654
7499
7844
7521
SQL> INSERT INTO MyBonus(Empno)
2 (SELECT E.Empno
3 FROM Emp E
4 WHERE Job = 'SALESMAN');
4 rows created.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 100
7499 100
7844 100
7521 100
The UPDATE Command
You can change column values of existing rows in your tables with the UPDATE command
. UPDATE: The table you want to update
. SET: The change you want to apply
. WHERE: The rows to which you want to apply the change
the SET clause offers two alternatives:
. You can specify a comma-separated list of single-column changes. With this approach, you can use the DEFAULT keyword as
an expression. This allows you to change column default values in the data dictionary at any point in time without the need to
change the UPDATE commands in your applications.
. You can drive the change with a subquery. The subquery must provide the right number of values for the list of column names
specified between the parentheses. Of course, the datatypes should also match, or the Oracle DBMS should at least be able to
convert values to the appropriate datatypes on the fly.
1 UPDATE EMP
2 SET
3* Sal = NULL
SQL> UPDATE EMP
2 SET
3 Comm = NULL
4 WHERE Job = 'CLERK';
SQL> ROLLBACK;
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
ALLEN 1600 30 SALESMAN
SQL> UPDATE Emp
2 SET
3 Job = 'MANAGER',
4 Sal = 2500,
5 Deptno = 10
6 WHERE Ename = 'ALLEN';
1 row updated.
SQL> ed
Wrote file afiedt.buf
1 UPDATE Emp
2 SET
3 Job = 'MANAGER',
4 Sal = 2500,
5* Deptno = 10
SQL> /
14 rows updated.
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp;
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
KING 2500 10 MANAGER
BLAKE 2500 10 MANAGER
CLARK 2500 10 MANAGER
JONES 2500 10 MANAGER
MARTIN 2500 10 MANAGER
ALLEN 2500 10 MANAGER
TURNER 2500 10 MANAGER
JAMES 2500 10 MANAGER
WARD 2500 10 MANAGER
FORD 2500 10 MANAGER
SMITH 2500 10 MANAGER
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
SCOTT 2500 10 MANAGER
ADAMS 2500 10 MANAGER
MILLER 2500 10 MANAGER
14 rows selected.
SQL> ROLLBACK;
Rollback complete.
QL> UPDATE Emp
2 SET
3 Job = 'CLERK',
4 Sal = 600,
5 Deptno = 10
6 WHERE Ename = 'ALLEN';
1 row updated.
SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5 WHERE Ename = 'BLAKE'
6
SQL> SELECT Ename, Sal
2 FROM Emp
3 WHERE Ename = 'BLAKE';
ENAME SAL
---------- ----------
BLAKE 2850
SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5 WHERE Ename = 'BLAKE';
1 row updated.
SQL> SELECT Ename, Sal
2 FROM Emp
3 WHERE Ename = 'BLAKE';
ENAME SAL
---------- ----------
BLAKE 3850
SQL> ed
Wrote file afiedt.buf
1 UPDATE Emp
2 SET
3 Job = (SELECT Job
4 FROM Emp
5 WHERE Ename = 'MILLER'),
6 Sal = 1000 + (SELECT Sal
7 FROM Emp
8 WHERE Ename = 'ALLEN'),
9 Deptno = (SELECT Deptno
10 FROM Emp
11 WHERE Ename = 'TURNER')
12* WHERE Ename = 'BLAKE'
SQL> /
1 row updated.
SQL> UPDATE Emp
2 SET
3 (Job, Deptno) = (SELECT Job, Deptno
4 FROM Emp
5 WHERE Empno = 7499)
6 WHERE Empno = 7698;
1 row updated.
The DELETE Command
The simplest data manipulation command is DELETE, This command also operates at the table level, and you use the WHERE
clause to restrict the set of rows you want to delete from the table. If you omit the WHERE clause, the DELETE command
results in an empty table.
Note the difference between the following two commands:
SQL> drop table departments;
SQL> delete from departments;
The DROP TABLE command not only removes the contents of the table, but also the table
itself, including all dependent objects/structures such as indexes and privileges. DROP TABLE is a
data definition (DDL) command. The DELETE command does not change the database structure,
but only the contents.it is a data manipulation (DML) command. Moreover, the effects of a
DROP TABLE command cannot be undone with a ROLLBACK command, as opposed to the effects of
a DELETE command, which can.
Example of a DELETE Command
SQL> delete from salgrades
2 where grade = 5;
1 row deleted.
SQL> rollback;
Rollback complete.
SQL>
Alternative DELETE Command,Using a Subquery
SQL> delete from (select *
2 from salgrades
3 where grade = 5);
1 row deleted.
SQL> rollback;
Rollback complete.
SQL>
The TRUNCATE command allows you to delete all rows of a table in a
more efficient way than with the DELETE command. The TRUNCATE command belongs to the
category of the data definition (DDL) commands,
The MERGE Command
It is able to perform insertions, updates, and deletions in a single statement. This makes
the MERGE command very efficient in data warehouse environments, where the tables
are often populated/updated from external sources.
SQL> cl scr
SQL> CREATE TABLE MyBonus
2 (
3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );
Table created.
SQL> SELECT E.Empno
2 FROM Emp E
3 WHERE Job = 'SALESMAN';
EMPNO
----------
7654
7499
7844
7521
SQL> INSERT INTO MyBonus(Empno)
2 (SELECT E.Empno
3 FROM Emp E
4 WHERE Job = 'SALESMAN');
4 rows created.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 100
7499 100
7844 100
7521 100
SQL> SELECT Empno, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 30;
EMPNO SAL DEPTNO
---------- ---------- ----------
7698 2850 30
7654 1250 30
7499 1600 30
7844 1500 30
7900 950 30
7521 1250 30
6 rows selected.
SQL> MERGE INTO MyBonus B
2 USING (SELECT Empno, Sal, Deptno
3 FROM Emp
4 WHERE Deptno = 30) S
5 ON (B.Empno = S.Empno)
6 WHEN MATCHED THEN
7 UPDATE
8 SET B.Bonus = B.Bonus + S.Sal * 0.1
9 DELETE
10 WHERE (S.Sal > 4000)
11 WHEN NOT MATCHED THEN
12 INSERT(B.Empno, B.Bonus)
13 VALUES(S.Empno, S.Sal * 0.1)
14 WHERE(S.Sal <= 4000);
6 rows merged.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 225
7499 260
7844 250
7521 225
7698 285
7900 95
6 rows selected
Savepoints
You can define ginterim points,h known as savepoints, within your transactions. During a
transaction, you can roll back to such savepoints without rolling back the transaction as a whole,
thus maintaining the changes you made before the savepoint.
Using Savepoints Within a Transaction
SQL> delete from history where empno=7654;
2 rows deleted.
SQL> delete from employees where empno=7654;
1 row deleted.
SQL> savepoint ONE;
Savepoint created.
SQL> delete from offerings where course='ERM';
1 row deleted.
SQL> delete from courses where code ='ERM';
1 row deleted.
SQL> rollback to savepoint ONE;
Rollback complete.
SQL> select description
2 from courses
3 where code='ERM';
DESCRIPTION
----------------------------------------------
Data modeling with ERM
SQL> rollback;
Rollback complete.
Data Definition Language
An Oracle database is comprised of the physical structures used to store,
manage, secure, and retrieve data.
A tablespace is the logical structure that allows you to manage a group of
datafiles.
Datafiles are the physical datafiles on disk.
The table is the object that stores data in a database.
Oracle provides a wide variety of table types. The default table type is heaporganized.
For most applications, a heap-organized table is an effective
structure for storing and retrieving data.
More on Datatypes---
¡Note If you insert values into a NUMBER(n,m) column and you exceed precision n, you get an error
message. If you exceed scale m, the Oracle DBMS rounds the value.
The Oracle DBMS supports many datatype synonyms for portability with other DBMS
implementations and for compliance with the ANSI/ISO standard. For example, CHARACTER is
identical to CHAR; DECIMAL(n,m) is identical to NUMBER(n,m); and NUMBER even has multiple synonyms,
such as INTEGER, REAL, and SMALLINT.
Each Oracle datatype has its own precision or length limits
shows 2000 and 4000 for the CHAR and VARCHAR2
datatype limits, respectively. You might wonder in which unit these numbers are expressed. That
depends on the value of the NLS_LENGTH_SEMANTICS parameter. The default for the
Oracle DBMS is to use BYTE length semantics
Creating a Heap-Organized Table
You use the CREATE TABLE statement to create tables. When creating a table, at minimum you must
specify the table name, column name(s), and data types associated with the columns. The Oracle default
table type is heap-organized. The term heap means that the data isnft stored in a specific order in the
table (instead, itfs a heap of data). Herefs a simple example of creating a heap-organized table with four
columns:
create table d_sources(
d_source_id number not null,
source_type varchar2(32),
create_dtt date default sysdate not null,
update_dtt timestamp(5)
);
If you donft specify a tablespace, then the table is created in the default permanent tablespace of the
user that creates the table
Implementing Virtual Columns
With Oracle Database 11g and higher, you can create a virtual column as part of your table
definition. A virtual column is based on one or more existing columns from the same table
and/or a combination of constants, SQL functions, and user-defined PL/SQL functions.
Virtual columns arenft stored on disk; theyfre evaluated at runtime when the SQL query
executes. Virtual columns can be indexed and can have stored statistics.
create table inv(
inv_id number
,inv_count number
,inv_status as (
case when inv_count <= 100 then 'GETTING LOW'
when inv_count > 100 then 'OKAY'
end)
);
SQL> insert into inv (inv_id, inv_count) values (1,100);
SQL> select * from inv;
Here is some sample output:
INV_ID INV_COUNT INV_STATUS
---------- ---------- -----------
1 100 GETTING LOW
Creating a Table from a Query
create table cwp_user_profile_101910
as select * from cwp_user_profile;
create table cwp_user_profile_test
as select * from cwp_user_profile
where 1=2;
Modifying a Table
Renaming a Table
There are a couple of reasons for renaming a table:
....To make the table conform to standards
....To better determine whether the table is being used before you drop it
This example renames a table from INV_MGMT to INV_MGMT_OLD:
SQL> rename inv_mgmt to inv_mgmt_old;
Adding a Column
Use the ALTER TABLE ... ADD statement to add a column to a table. This example adds a column to the
INV table:
SQL> alter table inv add(inv_count number);
Altering a Column
Occasionally, you need to alter a column to adjust its size or change its data type. Use the
ALTER TABLE
... MODIFY statement to adjust the size of a column. This example changes the size of a
column to 256
characters:
SQL> alter table inv modify inv_desc varchar2(256);
If any rows have a NULL value for the column youfre modifying to NOT NULL, then you must
first update the column to contain a value. Here is an example of modifying a column to
NOT NULL:
SQL> alter table inv modify(inv_desc not null);
You can also alter the column to have a default value. The default value is used any time a
record is
inserted into the table but no value is provided for a column:
SQL> alter table inv modify(inv_desc default 'No Desc');
If you want to remove the default value of a column, then set it to be NULL:
SQL> alter table inv modify(inv_desc default NULL);
Renaming a Column
There are a couple of reasons to rename a column:
....Sometimes requirements change, and you want to modify the column name to
better reflect what the column is used for.
....If youfre planning to drop a column, it doesnft hurt to rename the column first to
better determine whether any users or applications are accessing the column.
SQL> alter table inv rename column inv_count to inv_amt;
Dropping a Column
SQL> alter table inv drop (inv_name);
SQL> alter table inv set unused (inv_name);
SQL> alter table inv drop unused columns;
Displaying Table DDL
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
Dropping a Table
SQL> drop table inventory;
If you attempt to drop a parent table that has a primary key or unique keys referenced as a foreign
key in a child table, you see an error such as
ORA-02449: unique/primary keys in table referenced by foreign keys
You need to either drop the referenced foreign-key constraint(s) or use the CASCADE CONSTRAINTS
option when dropping the parent table:
SQL> drop table inventory cascade constraints;
You must be the owner of the table or have the DROP ANY TABLE system privilege to drop a table. If
you have the DROP ANY TABLE privilege, you can drop a table in a different schema by prepending the
schema name to the table name:
SQL> drop table inv_mgmt.inventory;
Undropping a Table
Suppose you accidentally drop a table, and you want to restore it. First, verify that the table you want to
restore is in the recycle bin:
SQL> show recyclebin;
Here is some sample output:
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
PURCHASES BIN$YzqK0hN3Fh/gQHdAPLFgMA==$0 TABLE 2009-02-18:17:23:15
Next, use the FLASHBACK TABLE...TO BEFORE DROP statement to recover the dropped table:
SQL> flashback table purchases to before drop;
Note You canft FLASHBACK TABLE...TO BEFORE DROP for a table created in the SYSTEM tablespace
Removing Data from a Table
You can use either the DELETE statement or the TRUNCATE statement to remove records from a table. You
need to be aware of some important differences between these two approaches
Features of DELETE and TRUNCATE
Using DELETE
One big difference is that the DELETE statement can be either committed or rolled back. Committing a
DELETE statement makes the changes permanent:
SQL> delete from inv;
SQL> commit;
If you issue a ROLLBACK statement instead of COMMIT, the table contains data as it was before the
DELETE was issued.
Using TRUNCATE
TRUNCATE is a DDL statement. This means Oracle automatically commits the statement (and the current
transaction) after it runs, so there is no way to roll back a TRUNCATE statement. If you need the option of
choosing to roll back (instead of committing) when removing data, then you should use the DELETE
statement. However, the DELETE statement has the disadvantage that it generates a great deal of undo
and redo information. Thus for large tables, a TRUNCATE statement is usually the most efficient way to
remove data.
SQL> truncate table computer_systems;
Data Manipulation.
how to change the contents of an Oracle database. The SQL commands to change the database contents are commonly referred to
as Data Manipulation Language (DML) commands.
The first four sections of this chapter cover the DML commands INSERT, UPDATE, DELETE,
and MERGE.
The INSERT Command
Standard INSERT Commands
The standard INSERT command supports the following two ways to insert rows:
. Use the VALUES clause, followed by a list of column values (between parentheses).
This method allows you to insert only one row at a time per execution of the INSERT
command.
. Formulate a subquery, thus using existing data to generate new rows.
You can use the reserved word NULL to specify a null value for a specific column. You can also
specify the reserved word DEFAULT to instruct the Oracle DBMS to insert the default value associated
with the corresponding column
If you donft specify a value for a specific column in your INSERT
statement, there are two possibilities:
. If the column has an associated DEFAULT value, the Oracle DBMS will insert that value.
. If you did not define a DEFAULT value for the column, the Oracle DBMS inserts a null
value (provided, of course, that the column allows null values).
.
QL> CREATE TABLE SampleUPD
2 (
3 SampID NUMBER(2)
4 DEFAULT 99,
5 SampName VARCHAR2(10)
6 DEFAULT 'NOT GIVEN',
7 SampDate DATE
8 DEFAULT SYSDATE
9 );
Table created.
SQL> INSERT INTO SampleUPD
2 VALUES(1, 'SAMPLE01', '26-JUN-09');
1 row created.
SQL> INSERT INTO SampleUPD
2 VALUES(2, 'SAMPLE02', '27-JUN-09');
1 row created.
1 INSERT INTO SampleUPD(SampID, SampName)
2* VALUES(3, 'SAMPLE03')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 INSERT INTO SampleUPD(SampID, SampDate)
2* VALUES(3, '21-JUN-09')
SQL> /
1 INSERT INTO SampleUPD(SampName, SampDate)
2* VALUES('SAMPLE04', '21-JUN-09')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 INSERT INTO SampleUPD(SampID, SampName, SampDate)
2* VALUES(4, 'SAMPLE04', DEFAULT)
SQL> /
1 row created.
SQL> SELECT * FROM SampleUPD;
SAMPID SAMPNAME SAMPDATE
---------- ---------- ---------
1 SAMPLE01 26-JUN-09
2 SAMPLE02 27-JUN-09
3 SAMPLE03 29-JUN-09
3 NOT GIVEN 21-JUN-09
99 SAMPLE04 21-JUN-09
4 SAMPLE04 29-JUN-09
SQL> CREATE TABLE MyBonus
2 (
3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );
Table created.
SQL> SELECT E.Empno
2 FROM Emp E
3 WHERE Job = 'SALESMAN';
EMPNO
----------
7654
7499
7844
7521
SQL> INSERT INTO MyBonus(Empno)
2 (SELECT E.Empno
3 FROM Emp E
4 WHERE Job = 'SALESMAN');
4 rows created.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 100
7499 100
7844 100
7521 100
The UPDATE Command
You can change column values of existing rows in your tables with the UPDATE command
. UPDATE: The table you want to update
. SET: The change you want to apply
. WHERE: The rows to which you want to apply the change
the SET clause offers two alternatives:
. You can specify a comma-separated list of single-column changes. With this approach, you can use the DEFAULT keyword as
an expression. This allows you to change column default values in the data dictionary at any point in time without the need to
change the UPDATE commands in your applications.
. You can drive the change with a subquery. The subquery must provide the right number of values for the list of column names
specified between the parentheses. Of course, the datatypes should also match, or the Oracle DBMS should at least be able to
convert values to the appropriate datatypes on the fly.
1 UPDATE EMP
2 SET
3* Sal = NULL
SQL> UPDATE EMP
2 SET
3 Comm = NULL
4 WHERE Job = 'CLERK';
SQL> ROLLBACK;
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
ALLEN 1600 30 SALESMAN
SQL> UPDATE Emp
2 SET
3 Job = 'MANAGER',
4 Sal = 2500,
5 Deptno = 10
6 WHERE Ename = 'ALLEN';
1 row updated.
SQL> ed
Wrote file afiedt.buf
1 UPDATE Emp
2 SET
3 Job = 'MANAGER',
4 Sal = 2500,
5* Deptno = 10
SQL> /
14 rows updated.
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp;
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
KING 2500 10 MANAGER
BLAKE 2500 10 MANAGER
CLARK 2500 10 MANAGER
JONES 2500 10 MANAGER
MARTIN 2500 10 MANAGER
ALLEN 2500 10 MANAGER
TURNER 2500 10 MANAGER
JAMES 2500 10 MANAGER
WARD 2500 10 MANAGER
FORD 2500 10 MANAGER
SMITH 2500 10 MANAGER
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
SCOTT 2500 10 MANAGER
ADAMS 2500 10 MANAGER
MILLER 2500 10 MANAGER
14 rows selected.
SQL> ROLLBACK;
Rollback complete.
QL> UPDATE Emp
2 SET
3 Job = 'CLERK',
4 Sal = 600,
5 Deptno = 10
6 WHERE Ename = 'ALLEN';
1 row updated.
SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5 WHERE Ename = 'BLAKE'
6
SQL> SELECT Ename, Sal
2 FROM Emp
3 WHERE Ename = 'BLAKE';
ENAME SAL
---------- ----------
BLAKE 2850
SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5 WHERE Ename = 'BLAKE';
1 row updated.
SQL> SELECT Ename, Sal
2 FROM Emp
3 WHERE Ename = 'BLAKE';
ENAME SAL
---------- ----------
BLAKE 3850
SQL> ed
Wrote file afiedt.buf
1 UPDATE Emp
2 SET
3 Job = (SELECT Job
4 FROM Emp
5 WHERE Ename = 'MILLER'),
6 Sal = 1000 + (SELECT Sal
7 FROM Emp
8 WHERE Ename = 'ALLEN'),
9 Deptno = (SELECT Deptno
10 FROM Emp
11 WHERE Ename = 'TURNER')
12* WHERE Ename = 'BLAKE'
SQL> /
1 row updated.
SQL> UPDATE Emp
2 SET
3 (Job, Deptno) = (SELECT Job, Deptno
4 FROM Emp
5 WHERE Empno = 7499)
6 WHERE Empno = 7698;
1 row updated.
The DELETE Command
The simplest data manipulation command is DELETE, This command also operates at the table level, and you use the WHERE
clause to restrict the set of rows you want to delete from the table. If you omit the WHERE clause, the DELETE command
results in an empty table.
Note the difference between the following two commands:
SQL> drop table departments;
SQL> delete from departments;
The DROP TABLE command not only removes the contents of the table, but also the table
itself, including all dependent objects/structures such as indexes and privileges. DROP TABLE is a
data definition (DDL) command. The DELETE command does not change the database structure,
but only the contents.it is a data manipulation (DML) command. Moreover, the effects of a
DROP TABLE command cannot be undone with a ROLLBACK command, as opposed to the effects of
a DELETE command, which can.
Example of a DELETE Command
SQL> delete from salgrades
2 where grade = 5;
1 row deleted.
SQL> rollback;
Rollback complete.
SQL>
Alternative DELETE Command,Using a Subquery
SQL> delete from (select *
2 from salgrades
3 where grade = 5);
1 row deleted.
SQL> rollback;
Rollback complete.
SQL>
The TRUNCATE command allows you to delete all rows of a table in a
more efficient way than with the DELETE command. The TRUNCATE command belongs to the
category of the data definition (DDL) commands,
The MERGE Command
It is able to perform insertions, updates, and deletions in a single statement. This makes
the MERGE command very efficient in data warehouse environments, where the tables
are often populated/updated from external sources.
SQL> cl scr
SQL> CREATE TABLE MyBonus
2 (
3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );
Table created.
SQL> SELECT E.Empno
2 FROM Emp E
3 WHERE Job = 'SALESMAN';
EMPNO
----------
7654
7499
7844
7521
SQL> INSERT INTO MyBonus(Empno)
2 (SELECT E.Empno
3 FROM Emp E
4 WHERE Job = 'SALESMAN');
4 rows created.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 100
7499 100
7844 100
7521 100
SQL> SELECT Empno, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 30;
EMPNO SAL DEPTNO
---------- ---------- ----------
7698 2850 30
7654 1250 30
7499 1600 30
7844 1500 30
7900 950 30
7521 1250 30
6 rows selected.
SQL> MERGE INTO MyBonus B
2 USING (SELECT Empno, Sal, Deptno
3 FROM Emp
4 WHERE Deptno = 30) S
5 ON (B.Empno = S.Empno)
6 WHEN MATCHED THEN
7 UPDATE
8 SET B.Bonus = B.Bonus + S.Sal * 0.1
9 DELETE
10 WHERE (S.Sal > 4000)
11 WHEN NOT MATCHED THEN
12 INSERT(B.Empno, B.Bonus)
13 VALUES(S.Empno, S.Sal * 0.1)
14 WHERE(S.Sal <= 4000);
6 rows merged.
SQL> SELECT * FROM MyBonus;
EMPNO BONUS
---------- ----------
7654 225
7499 260
7844 250
7521 225
7698 285
7900 95
6 rows selected
Savepoints
You can define ginterim points,h known as savepoints, within your transactions. During a
transaction, you can roll back to such savepoints without rolling back the transaction as a whole,
thus maintaining the changes you made before the savepoint.
Using Savepoints Within a Transaction
SQL> delete from history where empno=7654;
2 rows deleted.
SQL> delete from employees where empno=7654;
1 row deleted.
SQL> savepoint ONE;
Savepoint created.
SQL> delete from offerings where course='ERM';
1 row deleted.
SQL> delete from courses where code ='ERM';
1 row deleted.
SQL> rollback to savepoint ONE;
Rollback complete.
SQL> select description
2 from courses
3 where code='ERM';
DESCRIPTION
----------------------------------------------
Data modeling with ERM
SQL> rollback;
Rollback complete.
No comments:
Post a Comment