PL SQL

1. Транзакция - одна команда DDL или DCL,
или команды DML, выполняющие единое согласованное
изменение данных

2. COMMIT -- фиксация изменений

3. ROLLBACK -- откат транзакции

4. ....
   SAVEPOINT name_savepoint;
   ....
   ROLLBACK TO name_savepoint;
 
5. Сегменты отката (UNDO) - размер, время хранения

6. SELECT.... FOR UPDATE - блоировка записей, соответствующих условиям WHERE,
снимается после ROLLBACK или COMMIT, WAIT n - ждет n секунд, если записи уже заняты
другим пользователем на запись
SELECT ..... WHERE ..... FOR UPDATE WAIT 10 ORDER BY ....

DDL -Data Definition Language

7. CREATE TABLE
CREATE TABLE CONTACT_BOOK_2
(LAST_NAME VARCHAR2(20BYTE),PHONE_NUMBER VARCHAR2(20BYTE),EMAIL VARCHAR2(20BYTE));

8. ROWID
SELECT rowid FROM CONTACT_BOOK;
output
AAAE5sAAEAAAAFVAAA
AAAE5sAAEAAAAFVAAB
AAAE5sAAEAAAAFVAAC
AAAE5sAAEAAAAFVAAD
AAAE5sAAEAAAAFVAAE
AAAE5sAAEAAAAFVAAF

9. Ограничения на поля
   NOT NULL
   UNIQUE - допустимы пустые поля
   PRIMARY KEY
   FOREIGN KEY
   CHECK
 
CREATE TABLE COUNTRIES_3
  (COUNTRY_ID2 CHAR(2BYTE), REGION_ID NUMBER(3), POST_INDEX NUMBER(6),
    CONSTRAINT COUNTR_ID3 PRIMARY KEY (COUNTRY_ID2),
    CONSTRAINT REG_ID3 FOREIGN KEY (REGION_ID)  REFERENCES REGIONS(REGION_ID),
    CONSTRAINT POST_ID3 CHECK(POST_INDEX > 100000) );

DISABLE - временное выключение ограничения
ALTER TABLE COUNTRIES_3 DISABLE CONSTRAINT POST_ID3;
ENABLE - включение ограничения
ALTER TABLE COUNTRIES_3 ENABLE CONSTRAINT POST_ID3;


10. ALTER TABLE - модификация таблицы
ALTER TABLE COUNTRIES_3 ADD (EMAIL3 VARCHAR2(15),
    CONSTRAINT EM_ID3 UNIQUE(EMAIL3));

11.  блокировка таблицы
ALTER TABLE COUNTRIES_3 READ ONLY;
ALTER TABLE COUNTRIES_3 READ WRITE;

12. DROP TABLE COUNTRIES_3; -- удаление таблицы в корзину
SHOW RECYCLEBIN;        -- просмотр корзины 
PURGE RECYCLEBIN; -- очистка корзины

DROP TABLE COUNTRIES_3 PURGE; - удаление без помещения в корзину

ОБЪЕКТЫ БАЗЫ ДАННЫХ

13. VIEW представления
SELECT * FROM USER_VIEWS; -- список всех представлений пользователя

CREATE OR REPLACE VIEW EMP_VIEW
  AS SELECT FIRST_NAME,LAST_NAME,PHONE_NUMBER,EMAIL
  FROM EMPLOYEES;

SELECT * FROM EMP_VIEW WHERE PHONE_NUMBER LIKE '515.%';

UPDATE EMP_VIEW SET EMAIL = EMAIL||'@YA.RU'
WHERE PHONE_NUMBER LIKE '515.%'; -- данные меняются в самой таблице

CREATE VIEW .....
WITH READ ONLY; -- запрет команд DML над представлением
DROP VIEW EMP_VIEW;

14. SEQUENCE последовательности

CREATE SEQUENCE JOB_SEQ
INCREMENT BY 5
START WITH 10
NOMAXVALUE;

SELECT * FROM USER_SEQUENCES; -- все последовательности схемы

INSERT INTO JOBS VALUES (JOB_SEQ.NEXTVAL, 'IT_worker', 15000, 25000);

ALTER SEQUENCE JOB_SEQ
INCREMENT BY 10
MAXVALUE 1000;

DROP SEQUENCE JOB_SEQ;

15. INDEX индексы
Создаются автоматически для таблиц с PRIMARY KEY или UNIQUE
Вручную:
CREATE INDEX PHONE_IDX ON CONTACT_BOOK (PHONE_NUMBER);
пустые значения не хранятся
DROP INDEX PHONE_IDX;
SELECT * FROM USER_INDEXES;      -- все индексы схемы
SELECT * FROM USER_IND_COLUMNS;  -- по какому полю построен индекс

16. SYNONYM синонимы
CREATE SYNONYM JOB_TABLE FOR very_looooooooooooooong_name_table;
or
CREATE SYNONYM TABLE_ALEXEY FOR name_scheme.name_table;


19. DESCRIBE -- просмотр структуры таблицы
DESCRIBE JOB_GRADES;

INSERT
20. по одной строке
INSERT INTO JOB_GRADES VALUES (1, 1000, 1500);
INSERT INTO JOB_GRADES VALUES (2, 1501, 3500);

INSERT INTO JOBS(JOB_ID, JOB_TITLE)VALUES ('IT_ADM', 'System Engineer');
or
INSERT INTO JOBS VALUES ('IT_ADM', 'System Engineer', null, null);

21. с помощью скрипта по одной строке
INSERT INTO JOB_GRADES VALUES (&grade, &low_sal, &high_sal);

22. несколько строк из существующей таблицы
INSERT INTO CONTACT_BOOK(LAST_NAME,PHONE_NUMBER,EMAIL)
  SELECT LAST_NAME, PHONE_NUMBER, EMAIL FROM EMPLOYEES
  WHERE EMPLOYEE_ID > 200;
 
UPDATE 
23.
UPDATE CONTACT_BOOK
SET EMAIL = EMAIL||'@GMAIL.COM'
WHERE PHONE_NUMBER LIKE '515.%';

24.

25. DELETE
DELETE CONTACT_BOOK
WHERE PHONE_NUMBER NOT LIKE '515.%';

26. ROLLBACK -- отмена DELETE


27. удаление всех строк с сохранением структуры таблицы
TRUNCATE TABLE CONTACT_BOOK;





50. case

SELECT FIRST_NAME AS "Name",LAST_NAME AS "Family", SALARY AS "salary",
CASE
  WHEN salary < 5000 THEN '< 5000'
  WHEN salary < 7500 THEN 'at 5000 to 7500'
  ELSE '> 7500'
END
AS "Compare"
FROM EMPLOYEES;

51. decode

SELECT COUNTRY_ID AS "Abbreviation", COUNTRY_NAME AS "Country",
DECODE (REGION_ID, 1, 'Europa',
                   2, 'America',
                   3, 'Asia and Oceania',
                   4, 'Africa',
                      'unknown')
AS "Region"
FROM COUNTRIES
ORDER BY 3



GROUP FUNCTIONS

60. AVG
SELECT ROUND(AVG(MIN_SALARY),5) FROM JOBS;
output 6573.05263

61. COUNT
SELECT COUNT(JOB_ID) FROM JOBS;
output 19

62. MAX
SELECT MAX(MAX_SALARY) FROM JOBS;
output 40000

63. MAX
SELECT  MAX(JOB_TITLE) FROM JOBS;
output Stock Manager

64. MIN
SELECT MIN(MIN_SALARY) FROM JOBS;
output 2008

65. MIN
SELECT  MIN(JOB_TITLE) FROM JOBS;
output Accountant

66. SUM
SELECT (SUM(MAX_SALARY))*12 - (SUM(MIN_SALARY))*12 FROM JOBS
WHERE JOB_TITLE='Sales Manager';
output 120960

67. DISTINCT
SELECT COUNT(DISTINCT REGION_ID) FROM COUNTRIES;
output 4

68. GROUP BY
SELECT DEPARTMENT_ID, ROUND(AVG(salary),2) AS "mean" FROM EMPLOYEES
GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID;
output
DEPARTMENT_ID mean
10 4400
20 9500
30 4150
40 6500
50 3475.56
60 5760
70    10000
80 8955.88
90 19333.33
100 8601.33
110    10154
(null) 7000

69. GROUP BY
SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(salary),2) AS "mean" FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID ORDER BY 1, 2;
output
DEPARTMENT_ID JOB_ID mean
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_CLERK 2780
30 PU_MAN 11000
40 HR_REP 6500
50 SH_CLERK 3215
50 ST_CLERK 2785
50 ST_MAN 7280
60 IT_PROG 5760
70 PR_REP 10000
80 SA_MAN 12200
80 SA_REP 8396.55
90 AD_PRES 24000
90 AD_VP 17000
100 FI_ACCOUNT 7920
100 FI_MGR 12008
110 AC_ACCOUNT 8300
110 AC_MGR 12008
(null) SA_REP 7000

70. GROUP BY ... HAVING
SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(salary),2) AS "mean" FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID HAVING AVG(salary) >8000
ORDER BY 1, 2;
output
20 MK_MAN 13000
30 PU_MAN 11000
70 PR_REP 10000
80 SA_MAN 12200
80 SA_REP 8396.55
90 AD_PRES 24000
90 AD_VP 17000
100 FI_MGR 12008
110 AC_ACCOUNT 8300
110 AC_MGR 12008

71. GROUP BY ... HAVING
SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(salary),2) AS "mean" FROM EMPLOYEES
WHERE DEPARTMENT_ID < 100
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING AVG(salary) >8000 AND DEPARTMENT_ID <> 80
ORDER BY 1, 2;
output
20 MK_MAN 13000
30 PU_MAN 11000
70 PR_REP 10000
90 AD_PRES 24000
90 AD_VP 17000


UNIONS

80. NATURAL JOIN
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
       FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;
output
Neena Kochhar 90 Executive
Lex De  Haan 90 Executive
Bruce Ernst 60 IT
David Austin 60 IT
Valli Pataballa 60 IT
Diana Lorentz 60 IT
Daniel Faviet 100 Finance
John Chen 100 Finance
Ismael Sciarra 100 Finance
....
30 Anthony Cabrio 50 Shipping
31 Pat Fay 20 Marketing
32 William Gietz 110 Accounting

81. JOIN ON
SELECT L.CITY, L.COUNTRY_ID, C.COUNTRY_ID, C.COUNTRY_NAME
FROM LOCATIONS L JOIN COUNTRIES C
ON L.COUNTRY_ID = C.COUNTRY_ID;
output
Sydney AU AU Australia
Sao Paulo BR BR Brazil
Toronto CA CA Canada
Whitehorse CA CA Canada
Geneva CH CH Switzerland
Bern CH CH Switzerland
Beijing CN CN China
Munich DE DE Germany
Bombay IN IN India
Roma IT IT Italy
....

82. two JOIN ON
SELECT L.CITY,
   L.COUNTRY_ID, C.COUNTRY_ID,
   C.COUNTRY_NAME,
       C.REGION_ID, R.REGION_ID,
       R.REGION_NAME
FROM LOCATIONS L
JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
JOIN REGIONS R   ON C.REGION_ID  = R.REGION_ID;
output

Stretford UK UK United Kingdom 1 1 Europe
Oxford UK UK United Kingdom 1 1 Europe
London UK UK United Kingdom 1 1 Europe
Utrecht NL NL Netherlands 1 1 Europe
Venice IT IT Italy 1 1 Europe
Roma IT IT Italy 1 1 Europe
Munich DE DE Germany 1 1 Europe
Bern CH CH Switzerland 1 1 Europe
Geneva CH CH Switzerland 1 1 Europe
Seattle US US United States of America 2 2 Americas
South Brunswick US US United States of America 2 2 Americas
....

83. JOIN ON из одной таблицы
SELECT E.LAST_NAME AS EMP, M.LAST_NAME AS MAN, E.EMPLOYEE_ID, E.MANAGER_ID
FROM EMPLOYEES E JOIN EMPLOYEES M
ON M.EMPLOYEE_ID = E.MANAGER_ID;
output
Kumar Cambrault 173 148
Bates Cambrault 172 148
Smith Cambrault 171 148
Fox Cambrault 170 148
Bloom Cambrault 169 148
Ozer Cambrault 168 148
Hunold De Haan 103 102
Banda Errazuriz 167 147
Ande Errazuriz 166 147
Lee Errazuriz 165 147
....

84. LEFT JOIN
85.
86.



87. RIGHT JOIN
88.
89.



90. FULL JOIN
91.
92.



93. без JOIN
SELECT E.FIRST_NAME, E.LAST_NAME, J.START_DATE
FROM   EMPLOYEES E, JOB_HISTORY J
WHERE  E.EMPLOYEE_ID = J.EMPLOYEE_ID;
output
Neena Kochhar 28-OCT-01
Neena Kochhar 21-SEP-97
Lex De Haan 13-JAN-01
Den Raphaely 24-MAR-06
Payam Kaufling 01-JAN-07
Jonathon Taylor 01-JAN-07
Jonathon Taylor 24-MAR-06
Jennifer Whalen 01-JUL-02
Jennifer Whalen 17-SEP-95
Michael Hartstein 17-FEB-04

94. внешнее соединение (+)
SELECT E.FIRST_NAME, E.LAST_NAME, J.START_DATE
FROM   EMPLOYEES E, JOB_HISTORY J
WHERE  E.EMPLOYEE_ID = J.EMPLOYEE_ID(+);
выводим все записи E.EMPLOYEE_ID, в т.ч. для которых нет J.EMPLOYEE_ID
output
Neena Kochhar 21-SEP-97
Neena Kochhar 28-OCT-01
Lex De Haan 13-JAN-01
Den Raphaely 24-MAR-06
Payam Kaufling 01-JAN-07
Jonathon Taylor 24-MAR-06
Jonathon Taylor 01-JAN-07
....
107 Steven King (null)
108 Alberto Errazuriz (null)
109 Allan McEwen (null)
110 Douglas Grant (null)

ПОДЗАПРОСЫ

100 однострочный подзапрос
SELECT * FROM JOBS WHERE MIN_SALARY <
(SELECT JOBS.MIN_SALARY FROM JOBS WHERE MAX_SALARY = 10000); -- результат подзапроса: 4000
output
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_ASST Administration Assistant 3000 6000
PU_CLERK Purchasing Clerk 2500 5500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500

101. многострочный подзапрос
SELECT * FROM JOBS WHERE MIN_SALARY < ANY
(SELECT JOBS.MIN_SALARY FROM JOBS WHERE MAX_SALARY = 15000); -- результат подзапроса: 8000 и 90000
output
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
PU_CLERK Purchasing Clerk 2500 5500
AD_ASST Administration Assistant 3000 6000
IT_PROG Programmer 4000 10000
HR_REP Human Resources Representative 4000 9000
MK_REP Marketing Representative 4000 9000
FI_ACCOUNT Accountant 4200 9000
AC_ACCOUNT Public Accountant 4200 9000
PR_REP Public Relations Representative 4500 10500
ST_MAN Stock Manager 5500 8500
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
AC_MGR Accounting Manager 8200 16000
FI_MGR Finance Manager 8200 16000


ОБЪЕДИНЕНИЯ

110. UNION - элементы входящие в любое из множеств без дубликатов
SELECT COUNTRY_ID FROM COUNTRIES
UNION
SELECT COUNTRY_ID FROM LOCATIONS;
output
AR
AU
BE
....
SG
UK
24 US
25 ZM

111. UNION ALL - элементы входящие в любое из множеств с дубликатами, нет сортировки
SELECT COUNTRY_ID FROM COUNTRIES
UNION ALL
SELECT COUNTRY_ID FROM LOCATIONS;
output
....
40 UK
41 UK
42 UK
43 DE
44 BR
45 CH
46 CH
47 NL
48 MX

112. INTERSECT - элементы входящие в оба множества
SELECT COUNTRY_ID FROM COUNTRIES
INTERSECT
SELECT COUNTRY_ID FROM LOCATIONS;
output
AU
BR
CA
CH
CN
DE
IN
IT
JP
MX
NL
SG
13 UK
14 US

113. MINUS -вывод первого множества не входящего во второе
SELECT COUNTRY_ID FROM COUNTRIES
MINUS
SELECT COUNTRY_ID FROM LOCATIONS;
output
AR
BE
DK
EG
FR
IL
KW
ML
NG
ZM
11 ZW

114. MINUS
SELECT COUNTRY_ID FROM LOCATIONS
MINUS
SELECT COUNTRY_ID FROM COUNTRIES;
output
пусто



Контроль доступа пользователя

120. CREATE USER
CREATE USER hr_2 IDENTIFIED BY aA999999;
output
user HR2 created.

change password user
ALTER USER hr_2 IDENTIFIED BY Aa999999 PASSWORD EXPIRE;


Системные привилегии

121.  Привилегия создания сессия
GRANT CREATE SESSION to hr_2;

122. Привилегия создания таблиц, последовательностей и пр.
GRANT CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE to hr_2;
Квота на создание таблиц
ALTER USER hr_2 QUOTA UNLIMITED ON USERS;



125. ROLE
CREATE ROLE manager;
CREATE ROLE bookkeeper_role;
CREATE ROLE hr_role;
CREATE ROLE seller_role;
CREATE ROLE storehouse_role;
CREATE ROLE auditor_role;
SELECT * FROM DBA_ROLES;

GRANT CREATE SEQUENCE, CREATE VIEW to hr_role;
CREATE USER hr_3 IDENTIFIED BY aA999999;
GRANT hr_role TO hr_3;

Просмотр привилегий роли
DESCRIBE ROLE_SYS_PRIVS;
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'HR_ROLE';
output
ROLE PRIVILEGE ADMIN_OPTION
HR_ROLE CREATE SEQUENCE NO
HR_ROLE CREATE VIEW     NO


126. Объектные привилеги:

GRANT ALTER|DELETE|EXECUTE|INDEX|INSERT|REFERENCES|SELECT|UPDATE(list_columns)
      ON name_scheme.names_objects TO hr_3;
на select выбрать поля не получится, можно создать преставление с необходимыми полями
и дать привилегии на это представление




130. возможность передачи своих привилегий пользователем
 системных - GRANT .....  WITH ADMIN OPTION;
 объектных - GRANT .....  WITH GRANT OPTION; каскадное удаление привилегий
 
131. REVOKE - отмена привилегий
REVOKE привилегия1 [, привилегия2....] |ALL
ON object
FROM name_user1[, name_user2...];


132. ALTER TABLE  name_table ADD|MODIFY|DROP (name_column data_type [DEFAULT]) используется для:
- добавления столбца
- изменения столбца
- удаления столбца
ALTER TABLE HR.CONTACT_BOOK ADD    (SKYPE NUMBER(15));
ALTER TABLE HR.CONTACT_BOOK MODIFY (SKYPE NUMBER(30));
ALTER TABLE HR.CONTACT_BOOK DROP   (SKYPE);

133.SET UNUSED - пометить столбцы как неиспользуемые:
ALTER TABLE HR.CONTACT_BOOK ADD (SKYPE NUMBER(15));
ALTER TABLE HR.CONTACT_BOOK SET UNUSED COLUMN SKYPE;
.....
ALTER TABLE HR.CONTACT_BOOK DROP UNUSED COLUMNS; -- удаление отмеченных столбцов в дальнейшем,
-- например, в ночное время

134. ON DELETE CASCADE - при удалении строки с primary key удалятся все записи,
в которых foreign key ссылался на это значение primary key

135. RENAME COLUMN - переименование столбца
ALTER HR.CONTACT_BOOK RENAME COLUMN EMAIL TO E-CONTACTS;

136. RENAME CONSTRAINT - пе ограничения
ALTER HR.REGIONS RENAME CONSTRAINT REGION_ID_NN TO REGION_ID;

137. восстановить таблицу из корзины
DROP TABLE HR.CONTACT_BOOK_2; -- delete table
FLASHBACK TABLE HR.CONTACT_BOOK TO BEFORE DROP; -- восстановление таблицы

138. Временные таблицы
удаляемая после commit:
CREATE GLOBAL TEMPORARY TABLE name_temporary_table (structure_table)
ON COMMIT DELETE ROWS;


CREATE GLOBAL TEMPORARY TABLE name_table
ON COMMIT PRESERVE ROWS AS
SELECT ....

Внешние таблицы

139. создание внешних таблиц с использованием:
ORACLE_LOADER



ORACLE_DATAPUMP




140. Словарь данных
DESCRIBE DICTIONARY; -- представление словаря
output
Name       Null Type         
---------- ---- --------------
TABLE_NAME      VARCHAR2(30) 
COMMENTS        VARCHAR2(4000)


базовые таблицы и представления

список users
SELECT * FROM SYS.USER$;
SELECT * FROM DBA_USERS;

список таблиц пользователя
SELECT * FROM USER_TABLES;
список таблиц, к которым предоставлен доступа
SELECT * FROM ALL_TABLES;

список объектов пользователя
SELECT * FROM USER_OBJECTS;

описание столбцов
DESCRIBE USER_TAB_COLUMNS;

список ограничений пользователя
SELECT * FROM USER_CONSTRAINTS;
состав ограничений пользователя
SELECT * FROM USER_CONS_COLUMNS;

описание представлений пользователя
DESCRIBE USER_VIEWS;
список представлений пользователя
SELECT * FROM USER_VIEWS;

описание последовательностей, синонимов, индексов пользователя
DESCRIBE USER_SEQUENCES, USER_SYNONYMS, USER_INDEXES;

добавить комментарий к таблице
COMMENT ON TABLE CONTACT_BOOK_2 IS 'Employees contact information ';
просмотр комментариев к таблицам
SELECT * FROM USER_TAB_COMMENTS;

добавить комментарий к столбцу
COMMENT ON COLUMN JOB_GRADES.GRADE_LEVEL
  IS 'grades from 1 at manager to 15 to worker';
просмотр комментариев к столбцам таблицы
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'JOB_GRADES';


141. Многотабличная команда INSERT
INSERT ALL
INSERT FIRST

142. MERGE
UPDATE для существующих записей
INSERT для новых записей

143. EXTRACT - извлечение компонент из дат (SYSDATE, SYSTIMESTAMP) и интервальных типров данных
SELECT SYSDATE, EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
output
03-DEC-17 2017

144.






SELECT * FROM V$.....;






Oracle Data Miner Classic - проверить наличие SELECT * FROM v$option;


sqlplus включить показ длинных строк
set long 20000;
set wrap on;
Example:
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = 'EMP_DETAILS_VIEW';











250 trigger срабатывает
DML - INSERT, DELETE, UPDATE, MERGE  (BEFORE, AFTER, INSTEAD OF - привязан к представлению)
DDL - CREATE, ALTER, DROP
LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR

CREATE [OR REPLACE] TRIGGER name_trigger
BEFORE | AFTER | INSTEAD OF
INSERT | DELETE | UPDATE
ON table_name
[FOR EACH ROW] строчный триггер (можно использовать квалификаторы NEW | OLD)
WHEN ...
DECLARE
BEGIN
....
END name_trigger;

DELETING, INSERTING, UPDATING