Заполнение и модификация данных в Oracle

 

Цель и порядок работы

1.1 Цель работы

Ознакомиться со структурой и синтаксисом основных команд модификации данных.

 

1.2 Порядок выполнения работы

- Ознакомиться с описанием работы;

- Выполнить задание для самостоятельной работы;

- Ответить на контрольные вопросы;

- Представить результаты работы преподавателю.

 

2. Общие сведения

 

Модификация таблиц

Команда alter table

Для изменения структуры таблицы так же служит команда alter table. Эта команда позволяет:

- добавлять столбцы;

- изменять тип, размер и/или значение по умолчанию существующих столбцов;

- удалять столбцы из таблицы;

- добавлять, уничтожать, включать и выключать правила целостности;

- запрещать/разрешать выполнение триггеров для таблицы;

- изменять параметры управления дисковой памятью для таблицы.

 

Ниже приведен синтаксис команды alter table для вышеперечисленных действий. Добавление столбца:

alter table <имя> add (<описание столбца> {,<описание столбца>})

 

Описание столбца подчиняется тем же правилам, что описание столбца в команде create table.

Например,

alter table employee add (firedate date check (firedate>hiredate))

 

Удаление столбца:

alter table <имя> drop column <имя столбца>

 

Для изменения столбца используется команда alter table с предложением modify:

alter table <имя> modify (<описание столбца> {,<описание столбца>})

Например:

alter table employee modify (remark varchar2 (2000), salary number (8,2) not null

)

При этом все правила целостности (в частности, check (salary>=83.94)) остаются в силе. Для того, чтобы добавить правило целостности, используется команда

alter table <имя> add (<правило целостности> {,<правило целостности>}), где описание правила целостности совпадает с описанием правила целостности в команде create table. Имейте в виду, что новый столбец с правилом целостности not null или primary key не может быть создан командой alter table, если в таблице есть хоть одна строка. Если вам нужно создать такой столбец, то сначала создайте его без правила целост­ности, затем заполните его данными и создайте правило.

 

Для удаления правила целостности используется команда

alter table <имя> drop constraint <имя правила целостности>

 

Включение и отключение правил целостности производится командами:

alter table <имя> disable constraint <имя правила целостности>

и

alter table <имя> enable constraint <имя правила целостности>

 

Если имя правила целостности при создании таблицы явно не задавалось, можно по­смотреть его в системном представлении USER_C0NSTRAINTS.

Изменять правила целостности нельзя. Например, если в таблице employee надо изме­нить правило внешнего ключа с "delete cascade" на "set null", то нужно выполнить две коман­ды:

alter table employee drop constraint SYS_C00915 -- имя, сгенерированное системой

alter table employee add (

foreign key(mgrisn) references employee(isn) on delete set null

)

 

Уничтожается таблица командой drop:

drop table <имя>

 

При этом уничтожаются все правила целостности, триггеры, индексы и объектные приви­легии, связанные с таблицей.

Промежуточное положение между командами DDL и DML занимает команда truncate table. Она уничтожает все данные в таблице и может освободить занимаемое таб­лицей дисковое пространство. Операция truncate table не может быть отменена путем от­ката транзакции. При выполнении этой операции не будут выполняться триггеры, срабаты­вающие на удаление строки. В то же время команда truncate table не будет выполнена, ес­ли таблица является родительской для какого-либо правила foreign key — перед выполнением этой команды нужно отключить или уничтожить все такие правила, за исключением тех, ко­торые связывают таблицу саму с собой. В простейшем случае команда записывается как

truncate table < имя>

Таблица может быть переименована командой rename:

rename < имя> to < новое имя>

 

Oracle позволяет хранить в словаре данных комментарии к таблицам или отдельным столбцам. Они записываются при помощи команды comment:

comment on table <имя таблицы или представления> is '<текст комментария>'

и

comment on column <имя таблицы или представления>.<имя столбца> is '<текст комментария>'

 

Прочитать комментарии можно при помощи системных представлений user_tab_comments (комментарии к таблицам) и user_col_comments (комментарии к столб­цам).

 

Команда INSERT

Оператор INSERT, служит для заполнения таблиц БД данными и является достаточно простым в использовании. Давайте посмотрим, на синтаксис оператора INSERT:

INSERT INTO таблица(имя столбца таблицы),

VALUES (выражение); Оператор выбора

Понятно, что "таблица" - это имя таблицы, куда вводятся данные. "имя столбца" - это список столбцов, в которые вводятся данные. "Выражение" - это собственно сами данные. "Оператор выбора" - это предложение SELECT, для заполнения таблицы. Используется без части VALUES.

Пример:

INSERT INTO REGIONS (region_id,region_name)

values(6,'test6')

Результат посмотрим в таблице(рисунок №17)

Рисунок №17 – Результат выполнения команды

 

Как мы видим, была добавлена верхняя строка

Имя таблицы должно быть предварительно определено в команде CREATE TABLE, а каждое значение в команде должно совпадать с типом данных столбца, в который оно вставляется.

Предложение INSERT служит для того, чтобы указать таблицу, в которую будут добавляться записи. Помимо этого, если вы не собираетесь добавлять значения во все столбцы новой записи, можно перечислить имена добавляемых столбцов. Предложение VALUES указывает данные, которые мы собираемся добавлять. VALUES – это обязательное ключевое слово, используемое для создания списка значений для каждого столбца, перечисленного в списке столбцов или таблицы. Предложение DEFAULT VALUES добавляет запись, которая будет содержать значения по умолчанию для каждого столбца. При этом записи в столбце со свойством IDENTITY будет присвоено следующее подходящее значение. Для тех столбцов, которым присваиваются значения по умолчанию, естественно, будут присвоены эти значения. Если значения по умолчанию столбцам не присвоены, но записи могут принимать значение NULL, будет возвращена ошибка и команда INSERT не выполнится.

 

Вставка пустого значения

Если вам нужно ввести пустое значение (NULL), вы вводите его точно так же, как и обычное значение.

Например:

INSERT INTO REGIONS (region_id,region_name)

values(6,null)

 

Команда DELETE

Для удаления значения из таблицы используется команда DELETE. Синтаксис ее выглядит следующим образом:

DELETE FROM таблица

(имя столбца таблицы)

WHERE условие

Как мы видим, в операторе DELETE так же есть часть определения условия WHERE. То есть все, что мы использовали ранее для описания работы WHERE в операторе SELECT, применимо и здесь. Если не указать в предложении WHERE условие отбора, будут удалены все записи таблицы.

Например:

DELETE FROM regions

Данный пример удаляет все записи из таблицы regions.

Следующий пример удаляет по выборке запись с значением 6:

delete from regions

where region_id=6

 

Удалить записи со значением Null можно следующим образом:

DELETE FROM regions

WHERE region_name IS NULL

 

Удаление с вложенным подзапросом

Для того чтобы удалить записи таблицы с подзапросом необходимо выполнить следующую команду:

DELETE

FROM countries

WHERE region_id IN

(SELECT region_id

FROM regions

WHERE region_id = '5')

 

Команда UPDATE

Данные в таблицах БД изменяются с помощью оператора DML - UPDATE. Синтаксис этого оператора таков:

UPDATE таблица

SET имя столбца = выражение

WHERE условие

Синтаксически все довольно просто, как видим, в операторе UPDATE так же присутствует предложение WHERE, а следовательно, применимы все правила получения результирующего набора. Давайте посмотрим на практике, что можно сделать с таблицей с помощью оператора DML - UPDATE. Например, введите примерно следующее:

update regions

set region_name='test6'

where region_name='6test'

Мы получили обновление значений

Использование UPDATE с предложением FROM

Рассмотрим более сложный пример обновления значений, с использованием предложения FROM:

update regions

set region_name='test7'

FROM countries

WHERE region_id IN

(SELECT region_id

FROM regions

WHERE region_id = '5')

 

Обновление нескольких значений

Например:

update regions

set region_name='test6,

region_id='7'

where region_name='6test'

Так же иногда бывает необходимо выполнить обнуление значения:

update regions

set region_name=NULL

where region_name='6test'