Задание №1 Простые SQL-запросы Цель работы – изучить назначение простых запросов SQL, возможностей задания, а также ограничений задания запросов для безошибочной их обработки СУБД.
Теоретическая часть Особенностью большинства современных СУБД является то, что они позволяют как определять базу данных с помощью языка определения данных (в дальнейшем DDL – data definition language), так и добавлять, изменять и извлекать информацию из базы данных посредством языка управления данными (в дальнейшем DML – data manipulation language). Наиболее распространенным и стандартизированным языком управления и определения является язык структурированных запросов (в дальнейшем SQL – structured query language). Язык SQL в настоящее время поддерживается большинством СУБД с различными и незначительными отклонениями от стандарта, регламентирующего общие правила задания запросов. В этом случае можно говорить о “диалектах” SQL поддерживаемых конкретными СУБД. Заметим, что существенных отличий данных диалектов от стандарта SQL не имеется, так же отсутствуют существенные различия между этими диалектами. Однако стоит отметить, что SQL является языком четвертого поколения. Это означает, что он описывает то, что нужно выполнить, а не то, как это должно быть сделано. Языки четвертого поколения, подобные SQL, как правило, проще (по сравнению с языками третьего поколения) и содержат меньшее число команд. Кроме того, они изолируют пользователя от базовых структур данных и алгоритмов, реализуемых исполняющей системой. Однако в некоторых случаях процедурные конструкции языков 3GL полезны для более точного описания программы. Именно для этого применяется PL/SQL, который объединяет мощь и гибкость SQL (языка 4GL) и процедурные конструкции языка 3GL. PL/SQL означает Procedural Language/SQL (процедурный язык/SQL). Как видно из названия, PL/SQL расширяет возможности SQL, добавляя в него такие конструкции процедурных языков, как: • Переменные и типы данных (как предварительно определенные, так и определяемые пользователями) • Управляющие структуры, такие как условные операторы и циклы • Процедуры и функции • Объектные типы и методы Процедурные конструкции объединяются с Oracle SQL, что дает в результате структурированный и эффективный язык программирования. Допустимыми операторами DML являются SELECT (выбрать), INSERT (вставить), UPDATE (обновить) и DELETE (удалить). Каждый оператор действует в соответствии со своим названием: с помощью SELECT в таблице выбираются строки, указанные в условии WHERE; посредством INSERT к таблице базы данных добавляются строки; с помощью UPDATE модифицируются строки, указанные в условии WHERE; с помощью DELETE эти строки удаляются. Помимо условия WHERE, в этих операторах могут применяться и другие конструкции (см. ниже). Когда SQL-операторы выполняются из SQL*Plus, результаты выводятся на экран. Для операторов UPDATE, INSERT и DELETE SQL*Plus возвращает число обработанных строк, а для оператора SELECT — строки, указанные в запросе. Рассмотрим оператор UPDATE: UPDATE CLASSES SET num_credits = 3 WHERE department = 'HIS' AND course = 101; Все значения, используемые для изменения содержимого таблицы classes, жестко программируются, т.е. они известны во время написания этого оператора. В PL/SQL ограничения на применение переменных отсутствуют. Переменные могут указываться в любом месте SQL-оператора, где разрешены выражения. Переменные, используемые таким образом, называются переменными присваивания или переменными привязки (bind variable). С помощью оператора SELECT данные выбираются в базе данных и записываются в переменные PL/SQL. Общий вид оператора SELECT приведен ниже.
Предложение SELECT: Элемент_списка_выбора Выбранный столбец (или выражение). Каждый элемент списка выбора отделяется запятой и при желании может быть идентифицирован с помощью псевдонима. Полный набор элементов списка выбора называется списком выбора (select list). Символ * является кратким условным обозначением всей строки. При его использовании выбираются все поля строки таблицы в том порядке, в котором они определены. Переменная Переменная PL/SQL, в которую заносится выбранный столбец. Каждая переменная должна быть совместима по типу с соответствующим элементом списка выбора. Число элементов списка выбора и выходных переменных должно совпадать. Запись_PL/SQL Может использоваться вместо списка переменных. Запись должна содержать поля, которые соответствуют списку выбора. Запись обеспечивает более простое управление возвращаемыми данными. При помощи записей связанные поля группируются в одну синтаксическую единицу, поэтому к полям записи можно обращаться как к единому целому либо по отдельности. Если в качестве списка выбора указан символ *, то запись может быть определена как ссылка_на_таблицу% ROWTYPE. Ссылка_на_таблицу Определяет таблицу, из которой выбираются данные. Один запрос может выбирать данные из более чем одной таблицы, в этом случае ссылки на таблицу разделяются запятыми. Запрос такого типа называется соединением. Ссылка_на_таблицу не обязательно должна быть именем таблицы базы данных. Это может быть синоним либо таблица, принадлежащая удаленной базе данных, на которую указывает связь баз данных, представление, столбец вложенной таблицы или inline-представление. Условие WHERE Критерий запроса, С помощью этого условия задается строка, которая будет возвращена запросом. Условие WHERE состоит из логических условий, соединенных знаками логических операций. При помощи оператора SELECT, описанного выше, можно выбрать не более одной строки. Заданный критерий выбора будет сопоставляться с каждой строкой таблицы, и если условию будет удовлетворять несколько строк, PL/SQL вернет сообщение об ошибке. В этом случае следует воспользоваться курсором и выбрать каждую строку по отдельности. Синтаксис оператора INSERT приведен ниже. В операторе отсутствует условие WHERE.
Здесь ссылка_на_таблицу указывает на таблицу Oracle, имя_столбца — на столбец этой таблицы, а выражение является выражением SQL или PL/SQL. Если в операторе INSERT имеется оператор_выбора, то список выбора должен соответствовать столбцам, в которые вносится информация. Запрос такого типа называется подзапросом, так как он находится внутри другого оператора SQL. Оператор UPDATE имеет следующий синтаксис:
Здесь ссылка_на_таблицу указывает на обновляемую таблицу (или представление), имя_столбца — на столбец, значение которого будет изменено, а выражение — это выражение SQL. Если в операторе содержится оператор_выбора, то список выбора должен соответствовать столбцам команды SET (установить). Специальный синтаксис 'CURRENT OF курсор' используется вместе с определением курсора. При помощи оператора DELETE удаляются строки из таблицы базы данных. Условие WHERE оператора указывает на те строки, которые должны быть удалены. Синтаксис оператора DELETE приведен на рисунке ниже.
Здесь ссылка_на_таблицу указывает на таблицу Oracle (или некоторое представление), a условие_where определяет группу строк, которые будут удалены. Специальный синтаксис 'CURRENT OF курсор' используется вместе с определением курсора. В состав операторов SELECT, UPDATE и DELETE в качестве важной части входит условие WHERE. Оно определяет активный набор (active set) - множество строк, которое возвращается запросом SELECT или на которое воздействует оператор UPDATE или DELETE. Предложение WHERE строится из условий, соединенных логическими операциями AND (и), OR (или) и NOT (не). Эти условия обычно имеют форму сравнений.
Практическая часть
Задание: Вывести номера домашних телефонов всех директоров. Составить список владельцев всех 3-комнатных квартир. Подсчитать количество сотрудников в каждом из отделений. Создать представление сотрудниках и объектов, которые они предлагают в аренду.
Создадим нужные для выполнения лабораторной работы таблицы.
INSERT INTO branch VALUES (1,'1352431'); INSERT INTO branch VALUES (2,'1246355'); INSERT INTO staff VALUES (1,'lvanov','lgor','1234567','director',1); INSERT INTO staff VALUES (2,'Sidorov','Vova','1234677','sluj',1); INSERT INTO staff VALUES (3,'Kulagina','Vera','3526718','director',2); INSERT INTO staff VALUES (4,'Burova','Anna','2738467','sluj',2); INSERT INTO staff VALUES (5,'Nosov','lgor','4634567','slujr',2); INSERT INTO owner VALUES (1,'Jukov','lgor','3634567'); INSERT INTO owner VALUES (2,'Panova','lrina','5748567'); INSERT INTO owner VALUES (3,'Risova','lnna','8543567'); INSERT INTO property_for_rent VALUES (1,'Krasnaya','Minsk','flat',3,1,1,1); INSERT INTO property_for_rent VALUES (2,'Kirova','Minsk','flat',4,2,2,1); INSERT INTO property_for_rent VALUES (3,'Kirova','Minsk','house',2,3,3,2);
Выполним запросы:
1. Вывести номера домашних телефонов всех директоров.
SELECT fname,lname,tel_no FROM staff WHERE position='director';
2. Составить список владельцев всех 3-комнатных квартир.
SELECT fname,lname,tel_no FROM owner WHERE ono in(SELECT ono FROM property_for_rent WHERE rooms=3);
3. Подсчитать количество сотрудников в каждом из отделений.
SELECT bno,COUNT(sno) FROM staff GROUP BY bno;
4. Создать представление сотрудников и объектов, которые они предлагают в аренду.
CREATE VIEW pred AS SELECT fname,lname,tel_no,type,street,city FROM staff S,property_for_rent P WHERE S.sno=P.sno;
Экранные формы:
Запросы
Вывод: Язык SQL позволяет выполнять простые запросы, а именно запросы на извлечение информации, запросы с соединением таблиц, итоговые запросы, а также представления. С его помощью есть возможность задания, а также ограничений задания запросов для безошибочной их обработки СУБД.
Задание № 2 Создание баз данных посредством SQL Цель работы – изучить возможности создания баз данных посредством инструкций SQL, правила задания ограничений различных типов, а также запросов на добавление, обновление, удаление информации из базы данных Теоретическая часть
Инструкция create table позволяет создавать таблицы БД и ограничения на значения столбцов, а также создавать связи типа первичный ключ - внешний ключ между таблицами. Синтаксическая диаграмма данной инструкции представлена на рис. Создание таблицы
Определение столбца
Определение ограничений
Определение первичного ключа
Определение внешнего ключа
Определение уникальности
Условие на назначение
Создание таблицы ORDERS посредством create table можно продемонстрировать следующим примером. На рис. приведены таблицы и предполагаемые связи, создаваемые между ними.
Практическая часть
Задание: Создать базу данных произвольного сборочного производства. Предварительно зарисовать иерархическую структуру используемых деталей. Уровни иерархии выдерживать по этапам сборки. Перенести иерархию на таблицы реляционной базы данных с организацией связей между таблицами, отражающими реальные связи между этапами сборки.
CREATE TABLE notebook (notebookid NUMBER(6) PRIMARY KEY, series VARCHAR2(10) NOT NULL, resolution CHAR(4), motheboard_id NUMBER(6), CPU_id NUMBER(6) NOT NULL, HDD_id NUMBER(6) NOT NULL, RAM_id NUMBER(6) NOT NULL, serial_number VARCHAR2(7) UNIQUE, CONSTRAINT motheboard_fk FOREIGN KEY(motheboard_id) REFERENCES motheboard(motheboard_id) ON DELETE CASCADE, CONSTRAINT cpu_fk FOREIGN KEY(CPU_id) REFERENCES CPU(CPU_ID), CONSTRAINT hdd_fk FOREIGN KEY(HDD_id) REFERENCES HDD(HDD_id), CONSTRAINT ram_fk FOREIGN KEY(RAM_id) REFERENCES RAM(RAM_id) ); INSERT INTO notebook VALUES(1,'5740G','15,6',1,1,1,1,'n000001'); INSERT INTO notebook VALUES(2,'5742G','15,6',2,2,2,2,'n000002'); INSERT INTO notebook VALUES(3,'5745ZG','17,3',3,3,3,3,'n000003'); INSERT INTO notebook VALUES(4,'ONE','10,1',4,4,4,4,'n000004'); INSERT INTO notebook VALUES(5,'3690','15,4',5,5,5,5,'n000005');
CREATE TABLE motheboard (motheboard_id NUMBER(6) PRIMARY KEY, platform VARCHAR2(15) NOT NULL, memory CHAR(4) NOT NULL, USB_2 NUMBER(1), LAN VARCHAR2(5), WiFi CHAR(2), serial_number VARCHAR2(7) UNIQUE );
INSERT INTO motheboard VALUES(1,'Intel Cappela','DDR3',3,'OK','OK','m000001'); INSERT INTO motheboard VALUES(2,'AMD Tigris','DDR3',3,'OK','NO','m000002'); INSERT INTO motheboard VALUES(3,'AMD Puma','DDR3',4,'OK','OK','m000003'); INSERT INTO motheboard VALUES(4,'Intel Atom','DDR2',2,'OK','OK','m000004'); INSERT INTO motheboard VALUES(5,'AMD V','DDR2',2,'NO', 'OK','m000005');
CREATE TABLE CPU (CPU_id NUMBER(6) PRIMARY KEY, producer VARCHAR2(10) NOT NULL, number_core NUMBER(2), frequency NUMBER(5), cache VARCHAR2(7), serial_number VARCHAR2(7) UNIQUE, CONSTRAINT fre_max CHECK(frequency< 10000));
INSERT INTO CPU VALUES(1,'Intel',2,2130,'3 MB', 'c000001'); INSERT INTO CPU VALUES(2,'AMD',4,3000,'6 MB','c000002'); INSERT INTO CPU VALUES(3,'AMD',2,2000,'l MB','c000003'); INSERT INTO CPU VALUES(4,'Intel', 1,1700,'1 MB','c000004'); INSERT INTO CPU VALUES(5,'AMD',2,1860,'3 MB','c000005');
INSERT INTO HDD VALUES(1,'Samsung','SATA',320,5400,'h000001'); INSERT INTO HDD VALUES(2,'Samsung','SATA',500,7200,'h000002'); INSERT INTO HDD VALUES(3,'Seagate','TDE',120,5400,'h000003'); INSERT INTO HDD VALUES(4,'Toshiba','IDE',250,5400,'h000004'); INSERT INTO HDD VALUES(5,'Samsung','SATA',320,7200,'h000005');
CREATE TABLE RAM (RAM_id NUMBER(6) PRIMARY KEY, producer VARCHAR2(10) NOT NULL, type VARCHAR2(4), volume NUMBER(4), serial_number VARCHAR2(7) UNIQUE);
INSERT INTO RAM VALUES(1,'Hynix','DDR3',4096,'r000001'); INSERT INTO RAM VALUES(2,'Digma','DDR3',8192,'r000002'); INSERT INTO RAM VALUES(3,'Kingston','DDR3',2048, 'r000003'); INSERT INTO RAM VALUES(4,'Kingston','DDR2',1024,'r000004'); INSERT INTO RAM VALUES(5,'Samsung','DDR2',1024,'r000005'); Экранная форма:
Вывод: Были изучены принципы построения и объявления таблиц и из заполнение. Так же ознакомились с уникальностью используемой при создании БД.
Задание № 3 Процедуры и функции PL/SQL Цель работы – изучить возможности и основные программные конструкции языка PL/SQL. Приобретение навыков разработки хранимых процедур и функций баз данных ORACLE.
Теоретическая часть
В ORACLE различают три типа программ PL/SQL: анонимные блоки, хранимые процедуры и функции. Анонимные блоки – неименованные блоки PL/SQL не хранящиеся в базе данных. В процессе работы приложение посылает такой блок серверу, и после его обработки блок прекращает свое существование. Процедуры, функции PL/SQL помогают легко реализовывать сложную бизнес-логику модульным способом (т.е. компонент за компонентом, причем одни компоненты многократно используются другими). Сохранение их на сервере Oracle дает двоякую выгоду: возможность повторного использования с предсказуемыми результатами и очень быстрое выполнение, поскольку серверные операции почти или совсем не требуют обращения к сети. Процедура — это определенный набор инструкций, написанных на языке PL/SQL. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому и называется хранимой. Хранимая процедура может выполнять SQL-операторы и манипулировать данными в таблицах. Ее можно вызывать из другой хранимой процедуры PL/SQL, хранимой функции или триггера, а также непосредственно из строки приглашения SQL*Plus. По мере чтения главы вы научитесь использовать все перечисленные методы вызова. Процедура состоит из двух основных частей: спецификации и тела. Спецификация процедуры (procedure specification) включает в себя имя процедуры и описание ее входных и выходных данных. Эти входные и выходные данные называются формальными параметрами (formal parameters) или формальными аргументами (formal arguments). Если при вызове процедуры указываются параметры командной строки или другие входные данные, эти значения называются фактическими (actual) параметрами или фактическими аргументами. Создание процедур. Процедуры создаются посредством инструкции create procedure: сreate[or replace] procedure имя_процедуры [(аргумент1 [{in | out | in out}] тип, … аргумент2 [{in | out | in out}] тип)] {is|as} тело процедуры/ Чтобы изменить текст процедуры, её необходимо удалить и повторно создать её. Во время разработки процедур эта операция повторяется достаточно часто, поэтому ключевые слова or replace позволяют выполнить такую операцию за один раз. Если процедура существует, то она удаляется без всякого предупреждения (в данном случае вызов инструкции drop procedure не требуется), если же не существовала, то она просто создаётся. Параметры in, out и in out используются как при создании процедур, так и функций. Смысл параметра in в следующем: Значение физического параметра передаётся в функцию. Внутри процедуры формальный параметр рассматривается в качестве константы PL/SQL (параметр только для чтения) и не может быть изменен. Когда процедура завершается и управление программой передаётся в вызывающую среду, фактический параметр не изменяется. При использовании параметра out любое значение, которое имеет фактический параметр при вызове процедуры, игнорируется. Внутри процедуры формальный параметр рассматривается в качестве неинициализированной переменной, т.е содержит null-значение, и можно как записать в него значение, так и считать значение из него. Когда управление передаётся в вызывающую среду, содержание формальной переменной присваивается фактическому параметру. Параметр in out ─ это комбинация параметров in и out. В данном случае формальный параметр рассматривается в качестве инициализированной переменной. Функция PL/SQL похожа на процедуру PL/SQL: она также имеет спецификацию и тело. Главное различие между процедурой и функцией в том, что функция предназначена для возврата значения, которое может использоваться в более крупном SQL-Операторе. Рассмотрим в качестве примера функцию, предназначенную для вычисления процентного различия между двумя числами. Спецификация этой функции может выглядеть таким образом: calcjpercent (value_l NUMBER, value_2 NUMBER) return NUMBER Эта функция принимает в качестве входных параметров два числа, ссылаясь на них внутри себя как на VALUE_1 и VALUE_2. После написания тела функции ее можно вызывать в SQL-операторе следующим образом: INSERT INTO employee VALUES (3000, CALC PERCENT(300, 3000)); Создание функций. Создание функций отличается от создания процедур практически названием инструкции и наличием оператора return. сreate[or replace] function имя_функции [(аргумент1 [{in | out | in out}] тип, … аргумент2 [{in | out | in out}] тип)] return возвращаемый тип{is|as} тело функции оператор return/ Оператор return имеет общий синтаксис: return выражение. Выражение ─ это возвращаемое значение. Значение выражения преобразуется в тип, указанный в команде return при описании функции, если, конечно, это значение уже не имеет данный тип. Ниже приведен пример создания простой хранимой функции:
create or replace function get_customer_address (last in varchar2, firsr in varchar2) return varchar2 is addr varchar2(20); begin select address into addr from customers where lname=last and fname=first; return addr; exception when others then return NULL end get_customer_address;
Практическая часть Задание: Создать процедуру, выполняющую подсчет количества клиентов, обратившихся в каждый из офисов. Для вывода информации воспользоваться модулем DBMS_OUTPUT для отладки программ. CREATE TABLE ofice( dateID date NOT NULL, First_department INTEGER, Second_department INTEGER, Third_department INTEGER, PRIMARY KEY(dateID));
INSERT INTO ofice VALUES('1/10/10', 3,1,5); INSERT INTO ofice VALUES('2/10/10', 10,4,3); INSERT INTO ofice VALUES('3/10/10',5,4,4); INSERT INTO ofice VALUES('4/10/10', 7,9,1); INSERT INTO ofice VALUES ('5/10/10', 6,4,3);
select* from ofice;
CREATE OR REPLACE PROCEDURE client2 is first_dep NUMBER ; s_dep NUMBER; third_dep NUMBER; BEGIN select sum(first_department) into first_dep from ofice; select sum(second_department) into s_dep from ofice; select sum(third_department) into third_dep from ofice; dbms_output.put_line('First_department'||' '||first_dep); dbms_output. put_line(' Seconddepartment'||' '|| s_dep); dbms_output.put_line('Third_department'||' '||third_dep); END;
set serveroutput on; execute client2;
Задание: Создать функцию, которая возвращала бы среднее количество сотрудников, работающих в каждом из отделений фирмы.
INSERT INTO otdel VALUES('otdel_prodaj', 13); INSERT INTO otdel VALUES('otdel_razrabotki', 21); INSERT INTO otdel VALUES('otk',23);
CREATE OR REPLACE FUNCTION prcnt(name in varchar2) RETURN float IS rab_count INTEGER; summa INTEGER; BEGIN SELECT sum(kolvo) INTO summa FROM otdel; SELECT kolvo INTO rab_count FROM otdel WHERE (imia_otdela=name); RETURN (rab_count/summa); END;
CREATE OR REPLACE PROCEDURE simple_stat IS begin dbms_output.put_line('otdel1'||' - '||TO_CHAR(prcnt('otdel_prodaj')*100, '$99.0')||'%'); dbms_output.put_line('otdel2'||' - '||TO_CHAR(prcnt('otdel_razrabotki')*100, '$99.0')||'%'); dbms_output.put_line('otdel3'||' - '||TO_CHAR(prcnt('otk')*100, - '||$99.0')|| '%'); end; /
set serveroutput on; execute simple_stat;
Вывод: Были изучены возможности и основные программные конструкции язык PL/SQL. Получены навыки разработки хранимых процедур и функций баз данных ORACLE.
Задание № 4 Триггеры баз данных Цель работы – изучить предназначение и особенности создания триггеров баз данных по сравнению с хранимыми процедурами и функциями.
Теоретическая часть
Триггер — это процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием (triggering event). Например, можно писать триггеры, срабатывающие при выполнении над таблицей операций INSERT, UPDATE или DELETE; при выдаче команд DDL; при входе пользователя в систему или его выходе из системы; при запуске или останове базы данных; при возникновении ошибок. Между триггерами и процедурами PL/SQL есть три различия: • Триггеры нельзя вызывать из кода программы. Oracle вызывает их автоматически в ответ на определенное событие. • Триггеры не имеют списка параметров. • Спецификация триггера немного отличается от спецификации процедуры. Триггер лучше всего описать как процедуру, которая автоматически выполняется при возникновении некоторого события, указанного в определении триггера, — триггерного события (triggering event). Сходство между триггерами и процедурами состоит в следующем: • Тело триггера выглядит точно так же, как и тело процедуры . • Триггер не возвращает никаких значений. • Триггеры можно использовать для выполнения разнообразных задач. • Триггеры автоматически генерируют производные значения столбцов. • Триггеры помогают предотвращать неверные транзакции. • Триггеры можно использовать для реализации сложных процедур авторизации. • Триггеры используются для реализации сложных бизнес-правил. • Триггеры могут обеспечивать прозрачное протоколирование событий. • Триггеры могут обеспечивать сложный аудит. • Триггеры могут собирать статистику доступа к таблицам. Типы триггеров Момент срабатывания определяет, когда будет срабатывать триггер: до (BEFORE) или после (AFTER) наступления триггерного события (выполнения запускающего оператора). Если указано значение BEFORE, триггер выполняется до каких-либо проверок ограничений на строки, затрагиваемые триггерным событием. Никакие строки не блокируются. Триггер этого типа называется, соответственно, BEFORE-триггером (BEFORE trigger). Если выбрать ключевое слово AFTER, то триггер будет срабатывать после того, как запускающий оператор завершит свою работу.и будут выполнены проверки всех ограничений. В этом случае затрагиваемые строки блокируются на время выполнения триггера. Триггер этого типа называется AFTER-триггером (AFTER trigger). Триггерное событие может принимать значения INSERT, UPDATE или DELETE. Триггерное ограничение — это одно и более дополнительных условий, которые должны быть выполнены для срабатывания триггера. Необязательный набор ключевых слов FOR EACH ROW указывает на необходимость выполнить тело триггера для каждой строки, затрагиваемой запускающим оператором. Такие триггеры называются строчными (row triggers). Если опция FOR EACH ROW отсутствует, то при наступлении триггерного события триггер выполняется только один раз. В этом случае он называется операторным триггером (statement trigger), поскольку выполняется только один раз для каждого запускающего оператора. Часть кода между DECLARE и END имя триггера представляет собой обычный базовый блок PL/SQL. Различные триггерные события можно комбинировать с помощью оператора OR. Например: DELETE OR INSERT остальные_операторы. В случае использования UPDATE можно указать список столбцов: UPDATE ОРстолбец_1, столбец_2,... При использовании UPDATE в операторах PL/SQL обращение к новой и старой строкам выполняется с помощью слов "new" и "old", предваренных двоеточием. Вне зависимости от типа триггера все триггеры создаются одинаково. Общий синтаксис создания таков: сreate [or replace] trigger имя_триггера {before | after | instead of} активизирующее_событие on имя_таблицы [for each row] тело_триггера Здесь активизирующее_событие указывает событие активации триггера (далее указывается конкретная таблица или представление).
Практическая часть
Задание: Создать триггер, настроенный на ввод новых строк в таблицу с описанием сотрудников. Реакцией на добавление должна быть проверка количества работающих сотрудников в заданном отделении и занесение (путем разового обновления данных соответствующего столбца ) этого количества с учетом нового сотрудника во вспомогательную таблицу. В случае превышения вычисленного значения 5 в строку с данным отделением во вспомогательной таблице следует добавить некоторое информирующее сообщение.
CREATE TABLE NumberOfWorker( nno NUMBER(2) NOT NULL, numberWorker VARCHAR2(20));
INSERT INTO NumberOfWorker VALUES(1,' 1'); INSERT INTO NumberOfWorker VALUES(2,' 1'); INSERT INTO NumberOfWorker VALUES(3,' 1'); INSERT INTO NumberOfWorker VALUES(4,' 1'); INSERT INTO NumberOfWorker VALUES(5,' 1');
CREATE OR REPLACE TRIGGER numberStaff AFTER INSERT ON Staff DECLARE N NUMBER(2):=1; newIndex NUMBER(2); newNumber VARCHAR2(2); currentIndex NUMBER(2); currentNumber VARCHAR2 (2);
CURSOR aboutOffice IS SELECT Staff.bno, NumberOfWorker. numberWorker FROM Staff, NumberOfWorker WHERE Staff.bno= NumberOfWorker.nno ORDER BY Staff.bno;
BEGIN OPEN aboutOffice; FETCH aboutOffice INTO newIndex, newNumber; currentIndex:=newIndex; currentNumber:= newNumber; WHILE(aboutOffice%found) LOOP FETCH aboutOffice INTO newIndex, newNumber; IF(currentIndex= newIndex) THEN N:=N+1; ELSE IF(currentNumber <>'Error') THEN IF(N<>TO_NUMBER(currentNumber)) THEN IF(N<=5) THEN UPDATE NumberOfWorker SET NumberWorker=TO_CHAR(N) WHERE nno= currentIndex; ELSE UPDATE NumberOfWorker SET NumberWorker='Error' WHERE nno= currentIndex; END IF; END IF; END IF; currentIndex:= newIndex; currentNumber:= newNumber; N:=1; END IF;
END LOOP; CLOSE aboutOffice; END numberStaff;
Экранная форма:
Вывод: Были изучены назначение и особенности создания триггеров баз данных. Изначально была создана таблица, с которой связана работа триггера. Также создана дополнительная таблица для отображения результатов.
Задание № 5 Динамический SQL Цель работы – ознакомиться с таким объектом баз данных, как модули, а также с системными модулями DBMS_OUTPUT и DBMS_SQL, предназначенных для отладки приложений PL/SQL и написания процедур динамической обработки SQL-операторов.
Теоретическая часть
Перед тем как рассмотреть использование модуля DBMS_SQL для организации “динамических” процедур, следует рассмотреть такие объекты баз данных, как модули. Модуль (package) – это группа процедур, функций и других конструкций, хранимых вместе в базе данных как одна единица. Модули особенно полезны для компоновки нескольких процедур и функций, имеющих отношение к конкретному приложению баз данных. Модуль состоит из двух частей: описания и тела. ─ Описание модуля определяет интерфейс связи с этим модулем. В описании модуля объявляются все переменные и именованные константы, курсоры, процедуры, функции и другие конструкции модуля, которые необходимо сделать доступными для программ, внешних по отношению к этому модулю. Другими словами, всё объявленное в описании модуля является общим. ─ В теле модуля (package body) определяются все общие процедуры и функции, объявленные в описании модуля. Кроме того, в тело модуля могут включаться определения других конструкций, не указанных в его описании. Такие конструкции модуля являются частными, т. е. доступными только для программ внутри модуля. PL/SQL использует раннее связывание для выполнения операторов SQL. Следствием этого является то, что только операторы DML могут непосредственно включаться в блоки PL/SQL. Однако можно решить эту проблему с помощью динамического SQL. Динамический SQL разбирается и исполняется во время выполнения, а не синтаксического разбора блока PL/SQL. Существуют два способа выполнения динамического SQL в PL/SQL. Первый применяет модуль DBMS_SQL. Второй способ был введен в Oracle8i и предлагает использование встроенного динамического SQL. Встроенный динамический SQL является составной частью самого языка. Вследствие этого он значительно проще в применении и быстрее, чем модуль DBMS_SQL. Модуль DBMS_OUTPUT PL/SQL не имеет встроенных функций ввода/вывода.. Чтобы исправить ситуацию, SQL*Plus совместно с модулем DBMS_OUTPUT обеспечивает возможность вывода сообщений на экран. Это делается в два этапа: 1. Нужно включить режим вывода в SQL*Plus с помощью команды set serveroutput . Эта команда имеет формат: SETSERVEROUTPUT{ON|OFF} [SIZE n] где п — размер выходного буфера. Если n не определено, по умолчанию используется 2000 байт. Максимальный размер равен 1 000 000 байт. Ключевые слова ON и OFF включают и выключают вывод. 2. В программе PL/SQL используется процедура BMS_OUTPUT.PUT_LINE, которая помещает свои аргументы в выходной буфер. По завершении этих действий и выполнения блока SQL*Plus выведет результат на экран. Во время выполнения буфер заполняется вызовами DBMS_OUTPUT.PUT_LINE. SQL*Plus не извлекает содержимое из буфера и выводит его, когда управление возвращается SQL*Plus, т.е. после завершения выполнения блока. С помощью процедур модуля DBMS_OUTPUT реализованы две базовые операции: GET и PUT. Операция PUT берет свои аргументы и помещает во внутренний буфер для хранения. Операция GET считывает этот буфер и возвращает его содержимое процедуре в качестве аргумента. Размер буфера устанавливается с помощью процедуры ENABLE. Выполнение операции PUT обеспечивается процедурами PUT, PUT_LINE и NEW_LINE, а выполнение операции GET – процедурами GET_LINE и GET_LINES. Управляют буфером процедуры ENABLE и DISABLE. Процедуры PUT и PUT_LINE вызываются следующим образом:
procedure PUT (a varchar2); procedure PUT (a number); procedure PUT (a date);
procedure PUT_LINE (a varchar2); procedure PUT_LINE (a number); procedure PUT_LINE (a date); Модуль DBMS_SQL DBMS_SQL используется для выполнения динамического SQL в PL/SQL. Он не встроен непосредственно в язык и поэтому менее эффективен, чемвстроенный динамический SQL (который доступен в Огас1е8г и выше). Модуль DBMS_SQL позволяет непосредственно управлять обработкой операторов в курсоре, выполнять синтаксический разбор оператора, связывать входные переменные и определять выходные переменные. Следующий пример показывает некоторые применения модуля DBMS_SQL. Алгоритм выполнения операторов с помощью DBMS_SQL следующий: 1. Преобразование SQL-оператора в строку символов, 2. Грамматический разбор строки символов с помощью DBMS_SQL.PARSE, 3. Привязка всех входных переменных с помощью DBMS_SQL.BIND_VARIABLE, 4. Если выполняемый оператор ─ это оператор DML (update, delete, insert),─ выполнение его с помощью DBMS_SQL.EXECUTE с последующим считыванием выходных переменных привязки с помощью DBMS_SQL.VARIABLE_VALUE (если нужно), 5. Если оператор является оператором извлечения (select) ─ описание выходных переменных с помощью DBMS_SQL.DEFINE_COLUMN, 6. Выполнения запроса на выборку с помощью DBMS_SQL.EXECUTE и выборка результатов при помощи DBMS_SQL.FETCH_ROWS и DBMS_SQL.COLUMN_VALUE.
Практическая часть
Задание: Создать динамическую процедуру, изменяющую значения заработной платы сотрудников на указанный в аргументе процент, адрес офиса с сотрудниками получающими надбавку; указывать в качестве второго входного аргумента.
Создаем модуль, в котором будет находится наша процедура:
CREATE OR REPLACE PACKAGE my_paketik AS PROCEDURE nadbavka(p_percent IN NUMBER, p_address IN branch.bno%TYPE); END my_paketik; /
Создаем тело модуля с нужной процедурой:
CREATE OR REPLACE PACKAGE BODY my_paketik AS PROCEDURE nadbavka(p_percent IN NUMBER, p_address IN branch.bno%TYPE) IS p_cursor INTEGER; p_text VARCHAR2(255); p_update NUMBER; BEGIN p_cursor := DBMS_SQL.OPEN_CURSOR; p_text := 'UPDATE staff SET salary=salary*(l+:nec_percent/100) WHERE bno IN (SELECT bno FROM branch WHERE bno=:nec_address) '; DBMS_SQL.PARSE(p_cursor, p_text, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(p_cursor, ':nec_percent', p_percent); DBMS_SQL.BIND_VARIABLE(p_cursor, ':nec_address', p_address); p_update:= DBMS_SQL.EXECUTE(p_cursor); DBMS_SQL.CLOSE_CURSOR(p_cursor); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(p_cursor); RAISE; END nadbavka; END my_paketik; /
Выполняем процедуру в модуле:
EXECUTE my_paketik.nadbavka(10,4);
Экранная форма:
Вывод: В результате выполнения работы были изучены объекты баз данных, такие как модули, а также системные модули DBMSOUTPUT и DBMS SQL, предназначенных для отладки приложений PL/SQL и написания процедур динамической обработки SQL-операторов. Динамическая процедура INFORMATION была создана для обработки и представления средствами серверного вывода информации о сотрудниках из таблицы STAFFX. При вызове процедуры появляется список сотрудников, по соответствующей профессии, а также количество данных записей.
