Презентация на тему: ОСНОВНЫЕ ОБЪЕКТЫ БАЗЫ ORACLE

Реклама. Продолжение ниже
ОСНОВНЫЕ ОБЪЕКТЫ БАЗЫ ORACLE
Основные объекты
Таблицы
Пример (основные операции с датами)
Работа с таблицами ( SQL DDL)
Системные представления (словарь данных) для просмотра таблиц
Задание 1
Правила целостности
Работа с правилами целостности ( SQL DDL )
Примеры
Системные представления для просмотра правил целостности
Задание 2
Индексы
B-tree индекс
BITMAP индекс ( содержимое исходной таблицы )
BITMAP индекс (структура индекса)
BITMAP индекс (обработка запроса) select count(*) from FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN (' central','west ')
Создание и удаление индексов ( SQL DDL )
Таблицы, организованные как индексы
Создание таблиц - индексов ( SQL DDL )
Пример
Системные представления для просмотра индексов
Задание 3
Представления Представление - это именованное правило выборки данных.
Создание и удаление представлений ( SQL DDL )
Примеры
Ограничения на создание редактируемых представлений
Пример (использование check option )
Пример (использование check option )
Системные представления
Задание 4
Секвенции
Создание и удаление секвенций ( SQL DDL )
Примеры
Использование секвенций
Примеры
Системные представления для секвенций
Задание 5
Домашнее задание 2 (10 баллов)
Процедуры, функции, пакеты
Определение функций
Пример
Задание 6
Задание 7
Определение процедур
Пример
Пример
Как сохранять в базе результаты работ процедур и функций ?
Пример
Задание 8
ОСНОВНЫЕ ОБЪЕКТЫ БАЗЫ ORACLE
Системные представления для процедур и функций
Обработка динамических ошибок
Пример (процедура для фиксации динамических ошибок)
Пример (вызовы процедуры LogInfo )
Задание 9
Пакеты
Определение спецификации и тела пакета
Пример
Пример
Преимущества пакетов
Обращение к содержимому пакета
Домашнее задание 3 (10 баллов)
1/63
Средняя оценка: 4.7/5 (всего оценок: 94)
Код скопирован в буфер обмена
Скачать (200 Кб)
Реклама. Продолжение ниже
1

Первый слайд презентации: ОСНОВНЫЕ ОБЪЕКТЫ БАЗЫ ORACLE

Графеева Н.Г. 201 4

Изображение слайда
1/1
2

Слайд 2: Основные объекты

Таблицы Правила целостности Индексы Представления Секвенции Процедуры Функции Пакеты Триггеры

Изображение слайда
1/1
3

Слайд 3: Таблицы

При определении таблицы главное – определение имен столбцов таблицы и задание их типов. Основные типы данных, используемые при создании таблиц : CHAR(размер ) Символьные данные фиксированной длины. Максимальный размер - 255 байт. Умалчиваемый размер - 1 байт. VARCHAR2 (размер) Символьные данные переменной длины. Максимальный размер - 2000 байт. Умалчиваемый размер - 1 байт. NUMBER( p,s ) Числовые данные переменной длины. Точность p (общее количество цифр) может задаваться от 1 до 38. Масштаб s (число цифр после десятичной точки) может быть от -84 до 127 DATE Значение даты и времени фиксированной длины в интервале от 1 января 4712 г. до н.э. до 31 декабря 4712 г. н.э. TIMESTAMP Дата и время с точностью до миллисекунд ===================================================================================== Над всеми типами данных предусмотрен достаточный набор операций, но не все операции над типами данных так очевидны. Наиболее часто возникают проблемы с типом DATE.

Изображение слайда
1/1
4

Слайд 4: Пример (основные операции с датами)

SELECT SYSDATE CURRENT_DATE, SYSDATE + 1 CURRENT_DATE_PLUS_1_DAY, SYSDATE + 1/24 CURRENT_DATE_PLUS_1_HOUR, EXTRACT(YEAR FROM SYSDATE) CURRENT_YEAR, EXTRACT(MONTH FROM SYSDATE) CURRENT_MONTH, TO_CHAR(SYSDATE, ‘DD/MM/YYYY HH24:MI:SS’) FROM DUAL

Изображение слайда
1/1
5

Слайд 5: Работа с таблицами ( SQL DDL)

CREATE TABLE table -name (field-name type [ options ] [, field-name type [ options ] ] * ) ============================================================ ALTER TABLE table -name {ADD|DROP|ALTER}[COLUMN] (field-name [type] [ options ]) ============================================================ DROP TABLE table_name [CASCADE CONSRAINTS] ------------------------------------------------------------------------------------------------- Примечание : опции – это правила целостности, значения по умолчанию и пр.

Изображение слайда
1/1
6

Слайд 6: Системные представления (словарь данных) для просмотра таблиц

USER_TABLES ALL_TABLES DBA _ TABLES (только для администраторов)

Изображение слайда
1/1
7

Слайд 7: Задание 1

Взгляните на содержимое системных табличных представлений, доступных в вашей схеме (в APEX, SQL Commans ).

Изображение слайда
1/1
Реклама. Продолжение ниже
8

Слайд 8: Правила целостности

В Oracle используются следующие правила целостности: NOT NULL - запрет пустых значений UNIQUE - контроль уникальности PRIMARY KEY - первичный ключ FOREIGN KEY - внешний ключ CHECK - контроль допустимых значений

Изображение слайда
1/1
9

Слайд 9: Работа с правилами целостности ( SQL DDL )

ALTER TABLE table_name {ADD|DROP|MODIFY} CONSTRAINT [ const_name ] [ const_definition ] ALTER TABLE table_name RENAME CONSTRAINT const_name TO const_name ALTER TABLE table_name {ENABLE|DISABLE} [CONSTRAINT] [ const_name ] [ const_definition ]

Изображение слайда
1/1
10

Слайд 10: Примеры

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY ( empno ); ============================================================= ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID) ============================================================= ALTER TABLE emp DROP CONSTRAINT dept_fkey ===================================== ======================== ALTER TABLE dept DROP UNIQUE (dname, loc) ============================================================= ALTER TABLE dept RENAME CONSTRAINT dname_ukey TO dname_unikey ===================================== ======================== ALTER TABLE dept DISABLE CONSTRAINT dname_ukey ===================================== ======================== ALTER TABLE dept ENABLE dname_ukey

Изображение слайда
1/1
11

Слайд 11: Системные представления для просмотра правил целостности

USER_CONSTRAINTS ALL_CONSTRAINTS DBA _ CONSTRAINTS (только для администраторов)

Изображение слайда
1/1
12

Слайд 12: Задание 2

Взгляните на содержимое системных представлений правил целостности в вашей схеме (в APEX, SQL Commans ).

Изображение слайда
1/1
13

Слайд 13: Индексы

Индекс – это структура, связанная с таблицей и используемая в первую очередь для ускорения доступа к строкам таблицы. Основные формы организации индексов : B-tree индексы BITMAP индексы

Изображение слайда
1/1
14

Слайд 14: B-tree индекс

Изображение слайда
Изображение для работы со слайдом
1/2
Реклама. Продолжение ниже
15

Слайд 15: BITMAP индекс ( содержимое исходной таблицы )

Изображение слайда
Изображение для работы со слайдом
1/2
16

Слайд 16: BITMAP индекс (структура индекса)

Изображение слайда
Изображение для работы со слайдом
1/2
17

Слайд 17: BITMAP индекс (обработка запроса) select count(*) from FROM CUSTOMER WHERE MARITAL STATUS = 'married' AND REGION IN (' central','west ')

Изображение слайда
Изображение для работы со слайдом
1/2
18

Слайд 18: Создание и удаление индексов ( SQL DDL )

CREATE [UNIQUE] [BITMAP] INDEX index -name ON table-name(field-name {, field-name }*) DROP INDEX index -name

Изображение слайда
1/1
19

Слайд 19: Таблицы, организованные как индексы

Изображение слайда
Изображение для работы со слайдом
1/2
20

Слайд 20: Создание таблиц - индексов ( SQL DDL )

CREATE TABLE table -name (field-name type [options] [UNIQUE (field-list)] {,field-name type[options] [UNIQUE (field-list)]}*) ORGANIZATION INDEX

Изображение слайда
1/1
21

Слайд 21: Пример

CREATE TABLE COUNTRIES ( COUNTRY_ID CHAR(2) CONSTRAINT COUNTRY_ID_NN NOT NULL, COUNTRY_NAME VARCHAR2(40), REGION_ID NUMBER, CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID) ) ORGANIZATION INDEX

Изображение слайда
1/1
22

Слайд 22: Системные представления для просмотра индексов

USER_INDEXES ALL_INDEXES DBA_INDEXES (доступно администратору)

Изображение слайда
1/1
23

Слайд 23: Задание 3

Проиндексируйте таблицы в своей схеме. Взгляните на содержимое представлений, связанных с индексами в своей схеме (в APEX, SQL Commands ).

Изображение слайда
1/1
24

Слайд 24: Представления Представление - это именованное правило выборки данных

Изображение слайда
Изображение для работы со слайдом
1/2
25

Слайд 25: Создание и удаление представлений ( SQL DDL )

Синтаксис : CREATE VIEW view -name [(field-list)] AS {SELECT-statement| UNION-statement} [ WITH CHECK OPTION ] DROP VIEW view -name

Изображение слайда
1/1
26

Слайд 26: Примеры

CREATE VIEW STAFF AS SELECT employee_id, last_name, job_id, manager_id, department_id FROM EMPLOYEES ====================================================== ==== CREATE VIEW JOB_IDS AS SELECT DISTINCT job_id FROM EMPLOYEES ======================================================== == CREATE VIEW STAFF_MANAGER_101 AS SELECT employee_id, last_name, job_id, manager_id, department_id FROM EMPLOYEES WHERE manager_id = 101 ===========================================================

Изображение слайда
1/1
27

Слайд 27: Ограничения на создание редактируемых представлений

Запрещены : Опция DISTINCT Агрегатные функции: AVG, COUNT, MAX, MIN, SUM Операции: UNION, UNION ALL, INTERSECT, MINUS Конструкты : GROUP BY или HAVING Псевдостолбец ROWNUM Использование нескольких таблиц после FROM

Изображение слайда
1/1
28

Слайд 28: Пример (использование check option )

CREATE VIEW Sales_staff AS SELECT Empno, Ename, Deptno FROM Emp_tab WHERE Deptno = 10 WITH CHECK OPTION

Изображение слайда
1/1
29

Слайд 29: Пример (использование check option )

INSERT INTO Sales_staff VALUES (7584, 'OSTER', 10) -- ok INSERT INTO Sales_staff VALUES(7591, 'WILLIAMS', 30) --not ok

Изображение слайда
1/1
30

Слайд 30: Системные представления

USER_VIEWS ALL_VIEWS DBA_VIEWS (доступно администратору)

Изображение слайда
1/1
31

Слайд 31: Задание 4

Создайте несколько представлений. Взгляните на содержимое соответствующих системных представлений в своей схеме (в APEX, SQL Commands ).

Изображение слайда
1/1
32

Слайд 32: Секвенции

Секвенция - это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания. Для последовательности можно указывать как положительные, так и отрицательные целые числа. Последовательности применяют для самых разных целей, но в основном для автоматической генерации первичных ключей. Тем не менее к первичному ключу таблицы последовательность никак не привязана. При определении секвенции указывается следующая информация : •имя последовательности • стартовое значение (опционально) • интервал между числами • максимальное значение (опционально) • минимальное значение (опционально) • размер кэша для очередного набора сгенерированных чисел (опционально)

Изображение слайда
1/1
33

Слайд 33: Создание и удаление секвенций ( SQL DDL )

CREATE SEQUENCE seq -name [START WITH start-value] INCREMENT BY step-value [MAXVALUE max-value] [MINVALUE min-value] [CYCLE] [CACHE cache -size] ALTER SEQUENCE seq -name [START WITH start-value] INCREMENT BY step-value [MAXVALUE max-value] [MINVALUE min-value] [CYCLE] [CACHE cache -size] DROP SEQUENCE seq -name

Изображение слайда
1/1
34

Слайд 34: Примеры

CREATE SEQUENCE sequence_1 INCREMENT BY 10 ================================================= CREATE SEQUENCE sequence_2 START WITH 20 INCREMENT BY –1 MAXVALUE 20 MINVALUE 0 CYCLE CACHE 2 ================================================== DROP SEQUENCE sequence_1

Изображение слайда
1/1
35

Слайд 35: Использование секвенций

Seq-name.NEXTVAL - генерирует очередное значение секвенции Seq-name.CURRVAL - текущее значение секвенции

Изображение слайда
1/1
36

Слайд 36: Примеры

SELECT departments_seq.NEXTVAL FROM DUAL ===================================================================== INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID ) VALUES ( DEPARTMENTS_SEQ.NEXTVAL, 'DEPARTMENT' || TO_CHAR(DEPARTMENTS_SEQ.CURRVAL), 1700 )

Изображение слайда
1/1
37

Слайд 37: Системные представления для секвенций

USER_SEQUENCES ALL_SEQUENCES DBA_SEQUENCES

Изображение слайда
1/1
38

Слайд 38: Задание 5

Создайте подходящие секвенции для объектов своей базы. Взгляните на системные представления (в APEX, SQL Commands ).

Изображение слайда
1/1
39

Слайд 39: Домашнее задание 2 (10 баллов)

Создайте в своей схеме таблицы, индексы, секвенции, представления для своей предметной области. Результат оформите в виде скрипта Результат отправьте по адресу N.Grafeeva@spbu.ru. Тема письма – DB_Applcation_2014_job2. Примечание : задание должно быть отправлено в течение 30 дней. За более позднее отправление будут сниматься штрафные баллы ( по баллу за каждый штрафной месяц).

Изображение слайда
1/1
40

Слайд 40: Процедуры, функции, пакеты

Хранимые процедуры ( stored procedure ) и функции — это подпрограммы, которые выполняют некоторые действия с информацией в базе данных и при этом сами хранятся в базе данных. В Oracle хранимые процедуры и функции можно писать на языках PL/SQL (процедурное расширение SQL ) и Java. Хранимые процедуры и функции никогда не передаются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Процедуры и функции могут быть с параметрами и без параметров. Способы передачи параметров: IN – параметр используется как параметр, передающий начальное значение от фактического параметра формальному при старте процедуры. Этот способ передачи параметра используется по умолчанию, т.е. когда способ передачи в явном виде не задан. OUT – параметр передает значение в конце работы процедуры/функции от формального параметра фактическому IN OUT – при старте процедуры/функции передает начальное значение от фактического параметра формальному, а конце работы процедуры/функции от формального параметра фактическому

Изображение слайда
1/1
41

Слайд 41: Определение функций

CREATE OR REPLACE FUNCTION name[(parameters…)] RETURN type IS [variables….] BEGIN … RETURN … … [EXCEPTION WHEN … THEN ……..] END [name]

Изображение слайда
1/1
42

Слайд 42: Пример

Определение функции : CREATE OR REPLACE FUNCTION summ ( a IN NUMBER, b in NUMBER) RETURN NUMBER IS var_result NUMBER; BEGIN var_result := a + b; RETURN var_result ; END summ ; Вызов функции : SELECT summ (2,3) FROM DUAL

Изображение слайда
1/1
43

Слайд 43: Задание 6

Создайте и вызовите хранимую функцию, вычисляющую факториал числа. create or replace function factorial( N NUMBER ) RETURN NUMBER IS BEGIN IF(N <= 1) THEN return 1; END IF; return N * factorial(N - 1); END ; Кокорев А. 341

Изображение слайда
1/1
44

Слайд 44: Задание 7

Создайте и вызовите функцию, определяющую количество служащих в демонстрационной базе ORACLE ( таблица EMP). CREATE OR REPLACE FUNCTION count_records RETURN int IS cnt int ; BEGIN SELECT COUNT(*) INTO cnt FROM EMP; RETURN cnt ; END ; Назаренко В. 341

Изображение слайда
1/1
45

Слайд 45: Определение процедур

CREATE OR REPLACE PROCEDURE name [(parameters…)] IS [variables…] BEGIN ….. [EXCEPTION WHEN … THEN ……..] END [name]

Изображение слайда
1/1
46

Слайд 46: Пример

Определение процедуры : CREATE OR REPLACE PROCEDURE avgnumbers ( a IN NUMBER, b IN NUMBER, ar OUT NUMBER, geom OUT NUMBER ) IS BEGIN ar := (a + b) / 2; geom := sqrt (a * b); END avgnumbers ;

Изображение слайда
1/1
47

Слайд 47: Пример

Вызов процедуры : DECLARE A NUMBER; B NUMBER; AR NUMBER; GEOM NUMBER; BEGIN A := 2; B := 3; AVGNUMBERS ( A, B, AR, GEOM ); dbms_output.put_line ('AR=' || AR); dbms_output.put_line ('GEOM =' || GEOM); END;

Изображение слайда
1/1
48

Слайд 48: Как сохранять в базе результаты работ процедур и функций ?

Создать вспомогательную таблицу, например : debug_log (id, LogTime, Message, inSource ) Результаты работы процедуры фиксировать в debug_log.

Изображение слайда
1/1
49

Слайд 49: Пример

Вызов процедуры : DECLARE A NUMBER; B NUMBER; AR NUMBER; GEOM NUMBER; BEGIN A := 2; B := 3; AVGNUMBERS ( A, B, AR, GEOM ); INSERT INTO debug_log (id, LogTime, Message) VALUES( debug_log_seq.nextval, sysdate, ‘AR =’ || AR || ‘ GEOM=‘ || GEOM); END;

Изображение слайда
1/1
50

Слайд 50: Задание 8

Создайте и вызовите процедуру, определяющую, даты приема на работу сотрудника, который работает дольше всех и сотрудника, который работает меньше всех. Результаты работы процедуры зафиксируйте в debug_log. CREATE OR REPLACE PROCEDURE dates ( a out DATE, b out DATE ) IS BEGIN select min(HIREDATE), max(HIREDATE) into a,b from EMP; END dates; CREATE TABLE debug_log (id NUMBER, LogTime DATE, Message varchar2(1024), insource varchar2(32))

Изображение слайда
1/1
51

Слайд 51

DECLARE A DATE; B DATE; BEGIN DATES ( A, B ); INSERT INTO debug_log (id, LogTime, Message) VALUES( debug_log_seq.nextval, sysdate, 'A = ' || to_char ( A, ' dd /mm/ yyyy ')|| ' B = ' || to_char ( B, ' dd /mm/ yyyy ')); END; SELECT * from debug_log Гарипов Эмиль 341

Изображение слайда
1/1
52

Слайд 52: Системные представления для процедур и функций

USER_PROCEDURES ALL_PROCEDURES DBA_PROCEDURES

Изображение слайда
1/1
53

Слайд 53: Обработка динамических ошибок

…. EXCEPTION [WHEN NO_DATA_FOUND THEN….] [WHEN ZERO_DIVIDE THEN… ] [WHEN TOO_MANY_ROWS THEN …] ………….. [WHEN OTHERS THEN…] END

Изображение слайда
1/1
54

Слайд 54: Пример (процедура для фиксации динамических ошибок)

create or replace procedure LogInfo ( inInfoMessage in varchar2, inSource in varchar2 ) is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into debug_log (id, LogTime, Message) values ( seq_debug_log.nextval, sysdate, inInfoMessage, inSource ); commit; exception when others then return; end LogInfo ;

Изображение слайда
1/1
55

Слайд 55: Пример (вызовы процедуры LogInfo )

create or replace procedure Calculate(...) is …… begin ……. LogInfo (‘A=‘ || A, ‘Calculate’); ...... exception when others then LogInfo ( substr ( sqlerrm, 1, 100), 'Calculate'); end;

Изображение слайда
1/1
56

Слайд 56: Задание 9

Создайте процедуру для фиксации динамических ошибок. Создайте функцию или процедуру, которая может привести к появлению динамической ошибки. Спровоцируйте появление и фиксацию динамической ошибки в журнале debug_log. create or replace procedure LookForManager ( injob varchar2) is varname varchar2(32); begin select ENAME into varname from EMP where JOB = injob ; LogInfo ( varname,'LookForManager '); exception when others then LogInfo ( substr ( sqlerrm, 1, 100), ' LookForManager '); end LookForManager ; create or replace procedure LogInfo ( inInfoMessage in varchar2, inSource in varchar2 ) is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into debug_log (id, LogTime, Message, inSource ) values ( debug_log_seq.nextval,sysdate, inInfoMessage, inSource ); commit; exception when others then return; end LogInfo ;

Изображение слайда
1/1
57

Слайд 57: Пакеты

ПАКЕТ - это объект базы данных, который группирует логически связанные типы,. Пакеты обычно состоят из двух частей, спецификации и тела, хотя иногда в теле нет необходимости. СПЕЦИФИКАЦИЯ пакета - объявляет типы, переменные, константы и подпрограммы, доступные для использования в пакете. ТЕЛО пакета полностью определяет подпрограммы, тем самым реализуя спецификацию пакета.

Изображение слайда
1/1
58

Слайд 58: Определение спецификации и тела пакета

PACKAGE имя A S -- спецификация (видимая часть) -- объявления общих типов и объектов -- спецификации подпрограмм END [имя]; ============================================================= PACKAGE BODY имя A S -- тело (скрытая часть) -- тела подпрограмм END [имя];

Изображение слайда
1/1
59

Слайд 59: Пример

CREATE PACKAGE emp_actions AS -- спецификация пакета TYPE EmpRecTyp IS RECORD ( emp_id INTEGER, salary REAL); PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER ); PROCEDURE fire_employee ( emp_id NUMBER ); END emp_actions ;

Изображение слайда
1/1
60

Слайд 60: Пример

CREATE PACKAGE BODY emp_actions AS -- тело PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER ) IS BEGIN INSERT INTO emp VALUES ( empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno ); END hire_employee ; PROCEDURE fire_employee ( emp_id NUMBER ) IS BEGIN DELETE FROM emp WHERE empno = emp_id ; END fire_employee ; END emp_actions ;

Изображение слайда
1/1
61

Слайд 61: Преимущества пакетов

Модульность Облегчение проектирования Скрытие информации Совместное использование Улучшенная производительность Облегчение администрирования

Изображение слайда
1/1
62

Слайд 62: Обращение к содержимому пакета

имя_пакета.имя_типа имя_пакета.имя_объекта имя_пакета.имя_подпрограммы

Изображение слайда
1/1
63

Последний слайд презентации: ОСНОВНЫЕ ОБЪЕКТЫ БАЗЫ ORACLE: Домашнее задание 3 (10 баллов)

Создайте пакет EMP_PACK с набором процедур и функций, выдающим информацию по сотрудникам ( вычисление количества сотрудников, общее повышение зарплаты на заданное количество процентов для всех с отрудников, повышение зарплаты по должностям на заданное количество процентов ). Создайте пакет DEPT_PACK с набором процедур и функций, выдающим информацию по департаментам (количество департаментов, суммарная зарплата и комиссионные по выбранному департаменту). В каждой процедуре пакета предусмотрите вызов процедуры для фиксации динамических ошибок. Результат отправьте по адресу N.Grafeeva@spbu.ru. Тема письма – DB_Applcation_2014_job3. Примечание : задание должно быть отправлено в течение 30 дней. За более позднее отправление будут сниматься штрафные баллы ( по баллу за каждый штрафной месяц).

Изображение слайда
1/1