Графеева Н.Г. 201 4
Таблицы Правила целостности Индексы Представления Секвенции Процедуры Функции Пакеты Триггеры
При определении таблицы главное – определение имен столбцов таблицы и задание их типов. Основные типы данных, используемые при создании таблиц : CHAR(размер ) Символьные данные фиксированной длины. Максимальный размер - 255 байт. Умалчиваемый размер - 1 байт. VARCHAR2 (размер) Символьные данные переменной длины. Максимальный размер - 2000 байт. Умалчиваемый размер - 1 байт. NUMBER( p,s ) Числовые данные переменной длины. Точность p (общее количество цифр) может задаваться от 1 до 38. Масштаб s (число цифр после десятичной точки) может быть от -84 до 127 DATE Значение даты и времени фиксированной длины в интервале от 1 января 4712 г. до н.э. до 31 декабря 4712 г. н.э. TIMESTAMP Дата и время с точностью до миллисекунд ===================================================================================== Над всеми типами данных предусмотрен достаточный набор операций, но не все операции над типами данных так очевидны. Наиболее часто возникают проблемы с типом DATE.
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
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] ------------------------------------------------------------------------------------------------- Примечание : опции – это правила целостности, значения по умолчанию и пр.
USER_TABLES ALL_TABLES DBA _ TABLES (только для администраторов)
Взгляните на содержимое системных табличных представлений, доступных в вашей схеме (в APEX, SQL Commans ).
В Oracle используются следующие правила целостности: NOT NULL - запрет пустых значений UNIQUE - контроль уникальности PRIMARY KEY - первичный ключ FOREIGN KEY - внешний ключ CHECK - контроль допустимых значений
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 ]
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
USER_CONSTRAINTS ALL_CONSTRAINTS DBA _ CONSTRAINTS (только для администраторов)
Взгляните на содержимое системных представлений правил целостности в вашей схеме (в APEX, SQL Commans ).
Индекс – это структура, связанная с таблицей и используемая в первую очередь для ускорения доступа к строкам таблицы. Основные формы организации индексов : B-tree индексы BITMAP индексы
CREATE [UNIQUE] [BITMAP] INDEX index -name ON table-name(field-name {, field-name }*) DROP INDEX index -name
CREATE TABLE table -name (field-name type [options] [UNIQUE (field-list)] {,field-name type[options] [UNIQUE (field-list)]}*) ORGANIZATION INDEX
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
USER_INDEXES ALL_INDEXES DBA_INDEXES (доступно администратору)
Проиндексируйте таблицы в своей схеме. Взгляните на содержимое представлений, связанных с индексами в своей схеме (в APEX, SQL Commands ).
Синтаксис : CREATE VIEW view -name [(field-list)] AS {SELECT-statement| UNION-statement} [ WITH CHECK OPTION ] DROP VIEW view -name
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 ===========================================================
Запрещены : Опция DISTINCT Агрегатные функции: AVG, COUNT, MAX, MIN, SUM Операции: UNION, UNION ALL, INTERSECT, MINUS Конструкты : GROUP BY или HAVING Псевдостолбец ROWNUM Использование нескольких таблиц после FROM
CREATE VIEW Sales_staff AS SELECT Empno, Ename, Deptno FROM Emp_tab WHERE Deptno = 10 WITH CHECK OPTION
INSERT INTO Sales_staff VALUES (7584, 'OSTER', 10) -- ok INSERT INTO Sales_staff VALUES(7591, 'WILLIAMS', 30) --not ok
USER_VIEWS ALL_VIEWS DBA_VIEWS (доступно администратору)
Создайте несколько представлений. Взгляните на содержимое соответствующих системных представлений в своей схеме (в APEX, SQL Commands ).
Секвенция - это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания. Для последовательности можно указывать как положительные, так и отрицательные целые числа. Последовательности применяют для самых разных целей, но в основном для автоматической генерации первичных ключей. Тем не менее к первичному ключу таблицы последовательность никак не привязана. При определении секвенции указывается следующая информация : •имя последовательности • стартовое значение (опционально) • интервал между числами • максимальное значение (опционально) • минимальное значение (опционально) • размер кэша для очередного набора сгенерированных чисел (опционально)
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
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
Seq-name.NEXTVAL - генерирует очередное значение секвенции Seq-name.CURRVAL - текущее значение секвенции
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 )
USER_SEQUENCES ALL_SEQUENCES DBA_SEQUENCES
Создайте подходящие секвенции для объектов своей базы. Взгляните на системные представления (в APEX, SQL Commands ).
Создайте в своей схеме таблицы, индексы, секвенции, представления для своей предметной области. Результат оформите в виде скрипта Результат отправьте по адресу N.Grafeeva@spbu.ru. Тема письма – DB_Applcation_2014_job2. Примечание : задание должно быть отправлено в течение 30 дней. За более позднее отправление будут сниматься штрафные баллы ( по баллу за каждый штрафной месяц).
Хранимые процедуры ( stored procedure ) и функции — это подпрограммы, которые выполняют некоторые действия с информацией в базе данных и при этом сами хранятся в базе данных. В Oracle хранимые процедуры и функции можно писать на языках PL/SQL (процедурное расширение SQL ) и Java. Хранимые процедуры и функции никогда не передаются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Процедуры и функции могут быть с параметрами и без параметров. Способы передачи параметров: IN – параметр используется как параметр, передающий начальное значение от фактического параметра формальному при старте процедуры. Этот способ передачи параметра используется по умолчанию, т.е. когда способ передачи в явном виде не задан. OUT – параметр передает значение в конце работы процедуры/функции от формального параметра фактическому IN OUT – при старте процедуры/функции передает начальное значение от фактического параметра формальному, а конце работы процедуры/функции от формального параметра фактическому
CREATE OR REPLACE FUNCTION name[(parameters…)] RETURN type IS [variables….] BEGIN … RETURN … … [EXCEPTION WHEN … THEN ……..] END [name]
Определение функции : 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
Создайте и вызовите хранимую функцию, вычисляющую факториал числа. 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
Создайте и вызовите функцию, определяющую количество служащих в демонстрационной базе ORACLE ( таблица EMP). CREATE OR REPLACE FUNCTION count_records RETURN int IS cnt int ; BEGIN SELECT COUNT(*) INTO cnt FROM EMP; RETURN cnt ; END ; Назаренко В. 341
CREATE OR REPLACE PROCEDURE name [(parameters…)] IS [variables…] BEGIN ….. [EXCEPTION WHEN … THEN ……..] END [name]
Определение процедуры : 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 ;
Вызов процедуры : 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;
Создать вспомогательную таблицу, например : debug_log (id, LogTime, Message, inSource ) Результаты работы процедуры фиксировать в debug_log.
Вызов процедуры : 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;
Создайте и вызовите процедуру, определяющую, даты приема на работу сотрудника, который работает дольше всех и сотрудника, который работает меньше всех. Результаты работы процедуры зафиксируйте в 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))
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
USER_PROCEDURES ALL_PROCEDURES DBA_PROCEDURES
…. EXCEPTION [WHEN NO_DATA_FOUND THEN….] [WHEN ZERO_DIVIDE THEN… ] [WHEN TOO_MANY_ROWS THEN …] ………….. [WHEN OTHERS THEN…] END
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 ;
create or replace procedure Calculate(...) is …… begin ……. LogInfo (‘A=‘ || A, ‘Calculate’); ...... exception when others then LogInfo ( substr ( sqlerrm, 1, 100), 'Calculate'); end;
Создайте процедуру для фиксации динамических ошибок. Создайте функцию или процедуру, которая может привести к появлению динамической ошибки. Спровоцируйте появление и фиксацию динамической ошибки в журнале 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 ;
ПАКЕТ - это объект базы данных, который группирует логически связанные типы,. Пакеты обычно состоят из двух частей, спецификации и тела, хотя иногда в теле нет необходимости. СПЕЦИФИКАЦИЯ пакета - объявляет типы, переменные, константы и подпрограммы, доступные для использования в пакете. ТЕЛО пакета полностью определяет подпрограммы, тем самым реализуя спецификацию пакета.
PACKAGE имя A S -- спецификация (видимая часть) -- объявления общих типов и объектов -- спецификации подпрограмм END [имя]; ============================================================= PACKAGE BODY имя A S -- тело (скрытая часть) -- тела подпрограмм END [имя];
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 ;
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 ;
Модульность Облегчение проектирования Скрытие информации Совместное использование Улучшенная производительность Облегчение администрирования
имя_пакета.имя_типа имя_пакета.имя_объекта имя_пакета.имя_подпрограммы
Создайте пакет EMP_PACK с набором процедур и функций, выдающим информацию по сотрудникам ( вычисление количества сотрудников, общее повышение зарплаты на заданное количество процентов для всех с отрудников, повышение зарплаты по должностям на заданное количество процентов ). Создайте пакет DEPT_PACK с набором процедур и функций, выдающим информацию по департаментам (количество департаментов, суммарная зарплата и комиссионные по выбранному департаменту). В каждой процедуре пакета предусмотрите вызов процедуры для фиксации динамических ошибок. Результат отправьте по адресу N.Grafeeva@spbu.ru. Тема письма – DB_Applcation_2014_job3. Примечание : задание должно быть отправлено в течение 30 дней. За более позднее отправление будут сниматься штрафные баллы ( по баллу за каждый штрафной месяц).
Slide-Share