Раздел 4 Другие команды языка манипулирования данными dмl и обработка транзакций

4.1. Команды языка манипулирования данными DМL (INSERT, UPDATE, DELETE).

Q4_1

Включение в таблицу одной строки

SQL>insertintoEMP

(EMPNO,ename,job,mgr,hiredate,sal,comm,DEPTNO)

values(7658,'BUGSON','ANALYST',7566,

to_date('14-APR-1997','DD-MM-YYYY'),3000,null,20);

1 row created.

SQL> select * from EMP

where job='ANALYST';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7658 BUGSON ANALYST 7566 14-APR-97 3000 20

Q4_2

Создание последовательности

SQL> create sequence EMPseq start with 8000;

Sequencecreated.

Q4_3

Использование последовательности для автоматической генерации значений

SQL> insert into EMP(EMPNO,ename,job,sal,comm,DEPTNO)

values(EMPseq.nextval,'COOPER','MANAGER',2.3e3,null,40);

1 row created.

SQL> select * from EMP

where DEPTNO=40;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

8000 COOPER MANAGER 2300 40

Q4_4

Предотвращение ввода неверных значений WITH CHECK OPTION

SQL> insert into

(select EMPNO,ename,job,sal,comm,DEPTNO from EMP

where DEPTNO = 40 with check option)

values(7890,'COOPER','MANAGER',2.3e3,null,50);

(select EMPNO,ename,job,sal,comm,DEPTNO from EMP

*

ERROR at line 2:

ORA-01402: view WITH CHECK OPTION where-clause violation

--Вставка не проходит из-за того, что для условия «whereDEPTNO= 40»

--добавлено «withcheckoption». Это добавление означает, что новое

--вставляемое значение «DEPTNO» тоже должно быть 40. Если оно будет

--другим, последует сообщение об ошибке, что мы и увидели.

--Иной вариант вставки пройдет:

[e-mail]> insert into

(select EMPNO,ename,job,sal,comm,DEPTNO from EMP

where DEPTNO = 40 with check option)

values(7890,'COOPER','MANAGER',2.3e3,null,40);

1 row created.

Q4_5

Включение в таблицу нескольких строк

SQL> insert into bonus2_19(ename,job,sal,comm)

select 'ALL',job,sum(sal),sum(comm) from EMP

group by job;

5 rows created.

SQL> select * from bonus2_19;

ENAME JOB SAL COMM

---------- --------- --------- ---------

ALL ANALYST 9000

ALL CLERK 4150

ALL MANAGER 10575

ALL PRESIDENT 5000

ALL SALESMAN 5600 2200

Q4_6

SQL> create table commission(EMPNO number(4) not null,comm number(7,2));

Table created.

SQL> insert into commission values(7499,1100);

1 row created.

SQL> insert into commission values(7654,500);

1 row created.

SQL> insert into commission values(7844,3500);

1 rowcreated.

Q4_7

Изменение строк таблицы UPDATE

Перенос в таблицу EMPзначений комиссионных из таблицыCOMMISSION

SQL> update EMP set comm = (select comm from commission c

where c.EMPNO=EMP.EMPNO)

where EMPNO in (select EMPNO from commission);

3 rows updated.

Q4_8

SQL> update commission set comm=500

where EMPNO=7844;

1 row updated.

SQL> insert into commission values(7844,1500);

1 row created.

SQL> insert into commission values(7844,1500);

1 rowcreated.

Q4_9

Суммарное значение комиссионных для каждого служащего

SQL> update EMP set comm = (select sum(comm) from commission c

where c.EMPNO=EMP.EMPNO)

where EMPNO in (select EMPNO from commission);

3 rowsupdated.

Q4_10

Добавление суммарного значения комиссионных премий каждого служащего

SQL> update EMP set comm = (select sum(comm)+EMP.comm from commission c

where c.EMPNO=EMP.EMPNO)

where exists (select 1 from commission c1

where c1.EMPNO = EMP.EMPNO);

3 rowsupdated.

Q4_11

Служащие переводяться из Далласа и Нью-Йорка в Бостон

SQL> update EMP e set

DEPTNO = (select DEPTNO from DEPT where loc = 'BOSTON'),

(sal,comm) = (select 1.1*avg(sal),1.5*avg(comm)

from EMP e1 where e.DEPTNO=e1.DEPTNO)

where DEPTNO in (select DEPTNO from DEPT

where loc='DALLAS' or loc='NEW YORK');

8rowsupdated.

Q4_12

Удаление строк из таблицы DELETE

Удаление сведений обо всех служащих отдела 40

SQL> delete from(select * from EMP where DEPTNO=40);

8rowsdeleted.

Q4_13

Удаление сведений об отделах, в которых нет сотрудников

SQL> delete from DEPTNO x

where not exists (select 1 from EMP where DEPTNO=x.DEPTNO);

2 rowsdeleted.

Q4_14

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

SQL> delete from commission x

where rowid > (select min(rowid)

from commission y

where x.EMPNO=y.EMPNO

and x.comm=y.comm);

1 row deleted.

4.2. Обработка транзакций

Q4_15

Управление транзакциями

SQL> savepoint del;

Savepoint created.

SQL> delete from commission;

4 rows deleted.

SQL> select * from commission;

no rows selected

SQL> rollback to del;

Rollback complete.

SQL> select * from commission;

EMPNO COMM

--------- ---------

7499 1100

7654 500

7844 500

7844. 1500

SQL> commit;

Commit complete.