Manipulating Data
11
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć2
Schedule: Timing Topic
45 minutes Lecture
40 minutes Practice
85 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: l11sel.sql, l11upd.sql
Practice: None
Manipulating Data 11Ć3
Objectives
Once your tables have been created, you will need to add new rows, make
changes to rows in a table, or delete rows by using data manipulation
commands. This lesson covers using SQL commands to make changes to data. A
number of these data manipulation commands make up a transaction, which
you may either save or delete using transaction controls.
At the end of this lesson, you should be able to
D
Insert new rows into a table.
D
Update existing rows in a table.
D
Delete rows from a table.
D
Explain transaction controls and their importance.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć4
Manipulating Data 11Ć5
Overview
Data manipulation language (DML) is a core part of SQL. When you want to add,

update, or delete data in the database, you execute a DML statement. A collection of
DML statements that have not yet been made permanent is called a transaction, or a
logical unit of work.
Technical Note:
The only way to manipulate data within the Oracle database is by means of
the data manipulation SQL statements listed here. The statements can be
issued directly in SQL*Plus or SQL*DBA, performed automatically by
tools such as Developer/2000 and SQL*Loader, or programmed with tools
such as the 3GL Precompilers. There is one exception: SQL*Loader has a
Direct Mode option for the Oracle7 Server that loads rows into the database
without using the INSERT statement.
Every table has INSERT, UPDATE, and DELETE privileges associated
with it. These privileges are automatically granted to the creator of the
table, but in general they must be explicitly granted to other users.
A new feature to Oracle 7.2 is that you can place a subquery in the place of
the table name, essentially the same way a view is used. For example,
UPDATE (SELECT * FROM s_dept)
SET id = 50
WHERE id = 60;
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć6
Manipulating Data 11Ć7
Adding a New Row to a Table
You can add new rows to a table by issuing the INSERT command.
Syntax
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
where: table is the table name.
column is the name of the column in the table to
populate.
value is the corresponding value for the column.

Note: This command with the VALUES clause adds only one row at a time to a
table.
Inserting a Row into a Table
Because you can insert a new row that contains values for each column, therefore the
column list is not required in the INSERT clause. However, the values must be listed
according to the default order of the columns in the table.
SQL> DESCRIBE s_dept
Name Null? Type
------------------------- -------- --------------
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)
SQL> INSERT INTO s_dept
2 VALUES (11, ’Finance’, 2);
1 row created.
For clarity, use the column list in the INSERT clause.
Enclose character and date values within single quotation marks; do not enclose
numeric values within single quotation marks.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć8
Class Management Note:
Common errors that can occur during user input:
1.Mandatory value missing for a NOT NULL column
2.Duplicate value violates uniqueness constraint
3.Foreign key constraint violated
4.CHECK constraint violated
5.Datatype mismatch
6.Value too wide to fit in column
Manipulating Data 11Ć9
Adding a New Row to a Table
continued

Inserting Null Values
Method
Description
Implicit Omit the column from the column list.
Explicit Specify the NULL keyword in the VALUES list.
Specify the empty string (‘’) in the VALUES list; for
character strings and dates only.
Example
Enter a new department omitting the region number. Because the region number is
not listed in the INSERT clause, a null value is entered implicitly for the region
number in this row.
SQL> INSERT INTO s_dept (id, name)
2 VALUES (12, ’MIS’);
1 row created.
Example
Alternatively, you can enter a null value into a row explicitly by using the NULL
keyword for the value.
SQL> INSERT INTO s_dept
2 VALUES (13, ’Administration’, NULL);
1 row created.
Be sure that the targeted column allows null values by verifying the Null? status from
the SQL*Plus DESCRIBE command.
Technical Note:
The Oracle7 Server automatically enforces all datatypes, data ranges, and
data integrity constraints. Any column that is not listed explicitly obtains a
null value in the new row.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć10
Manipulating Data 11Ć11
Adding a New Row to a Table
continued

Inserting Special Values by Using SQL Functions
You can use pseudocolumns to enter special values in your table. Specify USERID to
enter the current user name. SYSDATE enters the current date and time.
Example
Record information for the student in the S_EMP table. Supply the current user name
for the USERID column and the current date and time in the START_DATE column.
SQL> INSERT INTO s_emp (id, first_name,
2 last_name, userid, salary, start_date)
3 VALUES (26, ’Donna’,
4 ’Smith’, USER, NULL, SYSDATE);
1 row created.
Confirming Additions to the Table
To verify that the rows were inserted into the table, you can write a SELECT
statement.
Example
SQL> SELECT id, last_name, first_name,
2 userid, start_date, salary
3 FROM s_emp
4 WHERE id = 26;
ID LAST_NAME FIRST_NAME USERID START_DAT
-- --------- ---------- -------- ---------
SALARY
------
26 Smith Donna SFCL26 01-JAN-96
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć12
Manipulating Data 11Ć13
Adding a New Row to a Table
continued
Inserting Specific Date and Time Values
When inserting a date value, the format DD-MON-YY is usually used. With this

format, recall that the century defaults to the current century. Because the date also
contains time information, the default time is midnight (00:00:00).
If a date is required to be entered in another century and a specific time is also
required, use the TO_DATE function.
Example
Record information for the student in the S_EMP table. Supply the current user name
for the USERID column. Set the START_DATE to be January 1, 1996, 8:00 A.M.
SQL> INSERT INTO s_emp (id, first_name,
2 last_name, userid, salary, start_date)
3 VALUES (26, ’Donna’,
4 ’Smith’, USER, NULL,
5 TO_DATE(’01-JAN-96 08:00’,
6 ’DD-MON-YY HH:MI’));
1 row created.
If the RR format is set, the century may not be the current one.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć14
Class Management Note:
Be sure to mention the following points about the example:
1.The names of the SQL*Plus substitution parameters do not have to
match the corresponding column names.
2.Substitution parameters are lexical variables. Whatever characters the
user enters are substituted as text for the variable name.
3.The SQL*Plus SET VERIFY command lists the substitution prior to
executing the statement.
Manipulating Data 11Ć15
Adding a New Row to a Table
continued
Inserting Values by Using Substitution Variables
You can produce an INSERT command that allows the user to add values
interactively by using SQL*Plus substitution variables.

Example
Record information for a department in the S_DEPT table. Prompt the user for the
department number, department name, and region number.
SQL> INSERT INTO s_dept (id, name,
2 region_id)
3 VALUES (&department_id, ‘&department_name’,
4 &region_id);
Enter value for department_id: 61
Enter value for department_name: Accounting
Enter value for region_id: 2
1 row created.
For date and character values, the ampersand and the variable name are enclosed in
single quotation marks.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć16
Class Management Note:
Be sure to mention the following points about the script:
1.Do not prefix the SQL*Plus substitution parameter with the ampersand
in the ACCEPT command.
2.Use a dash to continue a SQL*Plus command on the next line.
3.Add a space after the colon in the PROMPT command.
Manipulating Data 11Ć17
Adding a New Row to a Table
continued
Creating a Script to Manipulate Data
You can save your command with substitution variables to a file and execute the file.
Each time you execute the command, it will prompt you for new values. Customize
the prompts by using the SQL*Plus ACCEPT command.
Example
Record information for a department in the S_DEPT table. Prompt the user for the
department number, department name, and region number. Customize the prompt

messages.
ACCEPT department_id PROMPT ’Please
enter the department number:’
ACCEPT department_name PROMPT ’Please
enter the department name:’
ACCEPT region_id PROMPT ’Please
enter the region number:’
INSERT INTO s_dept (id, name, region_id)
VALUES (&department_id, ’&department_name’,
&region_id);
Please enter the department number: 61
Please enter the department name: Accounting
Please enter the region number: 2
1 row created.
Do not prefix the SQL*Plus substitution parameter with the ampersand (&) when
referencing it in the ACCEPT command. Use a dash (-) to continue a SQL*Plus
command on the next line.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć18
Manipulating Data 11Ć19
Copying Rows fromAnother Table
You can use the INSERT statement to add rows to a table where the values are
derived from existing tables. In place of the VALUES clause, you use a subquery.
Syntax
INSERT INTO table [column (, column)]
subquery;
where: table is the table name.
column is the name of the column in the table to
populate.
subquery is the subquery that returns rows into the table.
For more information, see

Oracle7 Server SQL Reference, Release 7.3, “SELECT,” Subqueries section.
Example
Copy selected rows from the S_EMP table into the HISTORY table.
SQL> INSERT INTO HISTORY (id, last_name, salary,
2 title, start_date)
3 SELECT id, last_name, salary,
4 title, start_date
5 FROM s_emp
6 WHERE start_date < ’01-JAN-94’;
10 rows created.
The number of columns in the column list of the INSERT clause must match the
number of values in the subquery.
Class Management Note:
Please do not get into too many details on copying rows from another table.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć20
Manipulating Data 11Ć21
Updating Rows
You can modify existing rows by using the UPDATE command.
Syntax
UPDATE table
SET column = value [, column = value...]
[WHERE condition];
where: table is the table name.
column is the name of the column in the table to
populate.
value is the corresponding value or subquery for the
column.
condition identifies the rows to be updated and is
composed of column names, expressions,
constants, subqueries, and comparison

operators.
Confirm the update operation by querying the table to display the updated rows.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “UPDATE.”
Technical Note:
In general, use the primary key to identify a single row. Using other
columns may unexpectedly cause several rows to be updated. For example,
identifying a single row in the S_EMP table by last name is dangerous
because several employees may have the same last name.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć22
Class Management Note:
DEMO: l11sel.sql
PURPOSE: Show the students the initial values.
DEMO: l11upd.sql
PURPOSE: Perform the updates and view the results.
Manipulating Data 11Ć23
Updating Rows
continued
Example
Transfer employee number 2 to department 10. Transfer employee number 1 to
department 32 and change the employee’s salary to 2550.
SQL> UPDATE s_emp
2 SET dept_id = 10
3 WHERE id = 2;
1 row updated.
SQL> UPDATE s_emp
2 SET dept_id = 32, salary = 2550
3 WHERE id = 1;
1 row updated.
Confirm both data changes.

SQL> SELECT id, last_name, salary, dept_id
2 FROM s_emp
3 WHERE id IN (1,2);
ID LAST_NAME SALARY DEPT_ID
------- --------- ------ -------
2 Ngao 1450 10
1 Velasquez 2550 32
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder11Ć24
Manipulating Data 11Ć25
Updating Rows
continued
If you do not include a WHERE clause in your UPDATE command, all rows in the
table will be updated.
Example
Give a commission of 10 percent to every employee in the company. Confirm the
changes.
SQL> UPDATE s_emp
2 SET commission_pct = 10;
25 rows updated.
SQL> SELECT id, commission_pct
2 FROM s_emp;
ID COMMISSION_PCT
------- --------------
1 10
2 10
3 10
4 10
5 10
6 10
...

25 rows selected.