6812

Манипулирование данными в SQL Oracle

Лабораторная работа

Информатика, кибернетика и программирование

Манипулирование данными в SQLOracle Цели лабораторной работы Изучить возможности SQL Oracle по вставке, обновлению и удалению строк в таблице. Приобрести практический опыт по вставке, обновлению и удалению строк в таблице с и...

Русский

2013-01-08

121 KB

8 чел.

Манипулирование данными в SQL Oracle

  1.  Цели лабораторной работы
  •  Изучить возможности SQL Oracle по вставке, обновлению и удалению строк в таблице.
  •  Приобрести практический опыт по вставке, обновлению и удалению строк в таблице с использованием SQL*Plus.
  1.  Теоретические основы

В SQL имеются следующие три предложения, которые позволяют манипулировать строками таблиц:

  •  Предложение INSERT – позволяет добавлять новые строки в таблицу.
  •  Предложение UPDATE – позволяет изменять значения столбцов существующих строк таблицы.
  •  Предложение DELETE – позволяет удалять строки таблиц.
    1.  Вставка строк в таблицу
      1.  Синтаксис

  1.  Назначение

Вставка новых строк в таблицу.

  1.  Предварительные требования

Чтобы иметь возможность вставлять строки в таблицу, принадлежащую вашей схеме, вы должны иметь привилегию INSERT по отношению к этой таблице.

Для вставки строк в таблицы других схем необходима системная привилегия INSERT ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

Имя таблицы, в которую вставляются строки.   

column  

 

Столбец таблицы. Во вставляемой строке содержатся значения из столбцов, указанных в списке. Эти значения указываются во фразе VALUES или получаются в результате вычисления подзапроса.  

Если в списке опущено имя столбца, то ему будет присвоено ьзначение по умолчанию, заданное при определении таблицы. Для получения дополнительной информации о значениях по умолчанию см. лабораторную работу 1. Если такой столбец имеет ограничение NOT NULL и для него не определено значение по умолчанию, то Oracle выдаст ошибку, указывающую, что нарушено ограничение NOT NULL, и произведет полный откат результатов выполнения предложения INSERT.

Если список столбцов отсутствует, то фраза VALUES или подзапрос должны содержать значения для всех столбцов таблицы.  

values_clause  

specifies a row of values to be inserted into the table or view. You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.  

subquery  

 

 

Подзапрос, которые возвращает строки, вставляемые в таблицу. Если подзапрос не возвращает строк, Oracle не вставляет ни одной строки в таблицу.

Если подзапрос специфицирован с фразой VALUES, то подзапрос возвращает значения, вставляемые в одну строку.

Если подзапрос специфицирован без фразы VALUES, то подзапрос возвращает значения, вставляемые в боле, чем одну строку.

Подзапрос может ссылаться на любую таблицу, включая и ту, в которую производится вставка строк в предложении INSERT. Список во фразе SELECT этого подзапроса должен иметь такое же количество столбцов, какое указано в списке столбцов предложения INSERT. Если список столбцов опущен, то подзапрос должен иметь значения для всех столбцов таблицы

t_alias  

Предоставляет имя-синоним для таблицы, которое может использоваться в любом месте предложения INSERT.  

  1.  Примеры

Примеры с использованием фразы VALUES

В следующем примере вставляется стока в таблицу FACULTY. Так как вставляются значения всех столбцов, список столбцов опускается:

INSERT INTO FACULTY   

  VALUES (1, 'informatics', 'NULL', '5', 93517);

В следующем примере вставляются значения в 7 столбцов таблицы TEACHER. Двум столбцам явно присваивается значение NULL. Значения двух столбцов (Tel и Commission) не определены. Поэтом столбцу Tel присваивается значение NULL, а Commission – значение по умолчанию 0. Последний столбец указывает, что данный преподаватель имеет в качестве своего руководителя преподавателя с первичным ключом 2:

INSERT INTO TEACHER (TchPK, DepFK, name, post, hiredate, salary, ChiefFK)

  VALUES (15, 7, 'Ivanov', NULL, NULL, TO_DATE('25.07.2001', 'DD.MM.YYYY'), 350.00, 2);

Пример с использование подзапроса

В следующем примере производится копирование значений всех столбцов для преподавателей-профессоров с надбавками, превышающими 25% ставки, в таблицу BONUS:

INSERT INTO bonus

  SELECT *

  FROM TEACHER

  WHERE Commission > 0.25 * salary AND post 'proessor';

  1.  Обновление строк в таблице
    1.  Синтаксис

  1.  Назначение

Изменение существующих значений столбцов в соответствующих строках таблицы.

  1.  Предварительные требования 

Чтобы иметь возможность обновлять значения в таблице, принадлежащей вашей схеме, вы должны иметь привилегию UPDATE по отношению к этой таблице.

Для обновления строк в таблицах других схем необходима системная привилегия UPDATE ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

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

set_clause  

 

 

column  

Имя столбца таблицы, значение которого должно быть обновлено. Если столбец отсутствует во фразе the set_clause, значение столбца не изменяется.

subquery  

Подзапрос, который возвращает точно одну строку для каждой обновляемой строки.

Если вы определили только один столбец во фразе set_clause, подзапрос должен возвратить только одно значение.

Если вы определили множество столбцов во фразе set_clause, подзапрос должен возвратить столько значений, сколько указано столбцов.

Если подзапрос не возвратил ни одной строки, то указанным столбцам присваивается значение NULL. 

expr  

Новое значение, присваиваемое соответствующему столбцу. 

where_clause  

Ограничивает строки, которые подвергаются обновлению. Строка обновляется, если указанное условие condition принимает значение TRUE. Если эта фраза опущена,  то Oracle обновляет все строки таблицы. Синтаксис условий приведен в лабораторной работе 2 . Для любой строки, которая удовлетворяет условию фразы where_clause, столбцам в левой части оператора присваивания (=) фразы set_clause присваиваются значения соответствующих выражений в правой части. Выражения вычисляются в момент обновления строки.

  1.  Примеры

Простые примеры

В следующем примере всем преподавателям на должности assistant устанавливается значение NULL столбца Commission:

UPDATE TEACHER

   SET Commission = NULL

   WHERE post = 'ассистент';

В следующем примере преподаватель Jones переводится на кафедру 20 с увеличением ставки на 100 (предполагаем, что у нас имеется только один преподаватель Jones):

UPDATE TEACHER

   SET DepNo =20, salary = salary + 100

   WHERE name = 'jones';

Сложный пример

В следующем примере показывается использование следующих синтаксических конструкций предложения UPDATE:

  •  Обе формы фразы set_clause в одном предложении.
  •  Коррелированный подзапрос.
  •  Фраза where_clause, ограничивающая обновляемые строки.
  •  Использование алиаса таблицы.
  •  Обновление нескольких столбцов (salary, Commission) одновременно.

UPDATE TEACHER a

 SET DepNo =

 (SELECT DepNo

  FROM deptartment

  WHERE name = 'DBMS'),

(salary, Commission) =

 (SELECT 1.1*AVG(salary), 1.5*AVG(Commission)

  FROM TEACHER b

  WHERE a.DepNo = b.DepNo)

WHERE DepNo IN

 (SELECT DepNo

  FROM DEPARTMENT

  WHERE FacNo = 1);

Это предложение UPDATE выполняет следующие действия:

  •  Обновляет значения только у тех преподавателей, которые работают на факультете, имеющем FacNo = 1
  •  Устанавливает этим преподавателям значение DepNo, равным DepNo кафедры 'DBMS' (то есть все преподаватели переводятся на эту кафедру)
  •  Устанавливает ставку этим учителям, в 1.1 раз большую средней ставки их предыдущей кафедры.
  •  Устанавливает надбавку этим учителям, в 1.5 раз большую средней надбавки их предыдущей кафедры.
    1.  Удаление строк в таблице 
      1.  Синтаксис

  1.  Назначение 

Удаляет указанные строки в таблице.

  1.  Предварительные требования 

Чтобы иметь возможность удалять строки в таблице, принадлежащей вашей схеме, вы должны иметь привилегию DELETE по отношению к этой таблице.

Для удаления строк в таблицах других схем необходима системная привилегия DELETE ANY TABLE.

  1.  Ключевые слова и параметры 

schema  

Имя схемы, содержащей таблицу. Если схема опущена, то Oracle предполагает, что таблица находится в вашей схеме.  

table

Имя таблицы, из которой удаляются строки.

where_clause  

 

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

Если фраза where_clause опущена, Oracle удаляет все строки таблицы.  

t_alias  

Предоставляет имя-синоним для таблицы, которое может использоваться в любом месте предложения DELETE.

  1.  Примеры

В следующем примере удаляются все строки таблицы DEPARTMENT.

DELETE FROM DEPARTMENT;

В следующем примере в таблице TEACHER удаляются строки, имеющие отношение к профессорам с надбавкой менее 100:

DELETE FROM TEACHER

   WHERE post = 'professor' AND Commission  < 100;

  1.  Задание
    1.  Вставка строк в таблицу

Вставьте следующие строки в таблицы.

FACULTY

FacPK

Name

DeanFK

Building

Fund

1

informatics

NULL

5

57398.00

2

economy

NULL

3

NULL

3

linguistics

NULL

4

NULL

DEPARTMENT

DepPK

FacFK

Name

HeadFK

Building

Fund

1

1

SE

NULL

5

14378.00

2

1

CAD

NULL

5

15000.00

3

1

DBMS

NULL

4

22000.00

4

2

Accounts

NULL

3

NULL

TEACHER

TchPK

DepFK

Name

Post

Tel

Hiredate

Salary

Commission

ChiefFK

1

1

Andrew

ассистент

2281319

01.02.2001

250

80

NULL

2

1

John

профессор

2281550

01.07.2001

400

150

1

3

2

Bill

преподаватель

NULL

17.11.2002

240

80

1

4

2

Albert

ассистент

NULL

11.11.2001

260

100

4

SGROUP

GrpPK

DepFK

Course

Num

Quantity

Curator

Rating

1

1

1

101

33

4

20

2

1

1

102

35

4

22

3

3

2

205

20

1

15

4

3

3

305

25

NULL

40

5

3

4

405

25

2

37

SUBJECT

SbjPK

Name

1

pascal

2

C

3

OS

4

inernet

5

dbms

ROOM

RomPK

Num

Seates

Floor

Building

1

101

20

1

5

2

316

150

3

5

3

201

150

2

2

4

202

30

2

5

LECTURE

TchFK

GrpFK

SbjFK

RomFK

Type

Day

Week

Lesson

1

1

1

1

лекция

пон

1

1

1

2

2

1

лабораторная

пон

1

1

2

3

3

1

лекция

втр

1

3

2

4

4

2

практика

срд

1

3

4

4

5

2

практика

чет

2

4

4

4

5

3

семинар

пят

2

1

Вставьте в таблицу FACULTY информацию о вашем факультете. В связи с тем, что к этому времени сведения о преподавателях еще не введены, в качестве внешнего ключа, ссылающегося на декана факультета, укажите NULL.

Вставьте в таблицу DEPARTMENT информацию о вашей кафедре и всех тех кафедрах, на которых работают обучающие вас преподаватели. В связи с тем, что к этому времени сведения о преподавателях еще не введены, в качестве внешних ключей, ссылающихся на заведующих кафедр, укажите NULL.

Вставьте в таблицу TEACHER информацию о тех преподавателях, которые вас обучают, включая декана факультета и заведующих всех кафедр. Подчиненность преподавателей устанавливаются следующим образом:

  •  деканы факультетов никому не подчиняются:
  •  заведующие кафедр подчиняются деканам факультетов (если зав кафедры является деканом, то он никому не подчиняется)
  •  преподаватели, читающие лекции, подчиняются заведующим кафедр
  •  преподаватели, проводящие лабораторные работы по дисциплине, подчиняются преподавателям, читающим лекции по  этой дисциплине.

Обновите таблицу FACULTY, заменяя NULL в столбце DeanFK на внешние ключи соответствующих деканов

Обновите таблицу DEPARTMENT, заменяя NULL в столбце HeadFK на внешние ключи соответствующих заведующих кафедр.

Вставьте в таблицу SGROUP информацию о вашей группе.

Вставьте в таблицу SUBJECT все изучаемые вами в текущий момент дисциплины.

Вставьте в таблицу ROOM информацию о тех аудиториях, где проводятся у вас занятия.

Вставьте в таблицу LECTURE расписание всех ваших занятий.

  1.  Обновление строк в таблицах

Сделайте следующие обновления в таблицах:

  •  В таблице FACULTY для факультета economy установите декана “Bob” и фонд = 3467.00
  •  В таблице DEPARTMENT для кафедры с DepNo =  3 измените заведующего на «Frank» и корпус на ‘3’
  •  В таблицеIn TEACHER для всех преподавателей на должности «assistant» установите значение столбца Commission равным 25% от значения столбца Salary.
  •  В таблице SGROUP установите значение rating = 0 для всех групп первого курса.
  •  В таблице SUBJECT измените название предмета с “internet” на “html
  1.  Контрольные вопросы

Ответьте на следующие вопросы:

  1.  Необходимо ли, чтобы порядок перечисления столбцов в предложении INSERT соответствовал порядку их определения при создании таблицы?
  2.  Какие значение присваиваются столбцу, если он не присутствует в списке столбцов предложения INSERT?
  3.  Что произойдет, если столбец отсутствует в списке столбцов предложения INSERT и этот столбец имеет ограничение целостности NOT NULL и для него не определено значение по умолчанию?
  4.  Что означает отсутствие списка столбцов в предложении INSERT?
  5.  Можно ли указать значение NULL во фразе VALUES?
  6.  Какова цель использования имен-синонимов (алиасов) таблиц?
  7.  Что означает отсутствие фразы WHERE в предложении UPDATE?
  8.  Что означает отсутствие фразы WHERE в предложении DELETE?

PAGE  5