Лабораторное занятие № 1. Простые SQL-запросы

Стр 1 из 9Следующая ⇒

Н. А. Капанов

БАЗЫ ДАННЫХ САПР

Лабораторный практикум

для студентов специальности

I–53 01 07 «Информационные технологии и управление
в технических системах»

Минск 2006

СОДЕРЖАНИЕ

ВВЕДЕНИЕ 4

Лабораторное занятие № 1. Простые SQL-запросы 5

· Теоретические сведения 5

Описание учебной базы данных 5

Запросы на извлечение информации 7

Запросы с объединением таблиц 9

Итоговые запросы 10

Представления 10

· Варианты заданий 11

· Контрольные вопросы 13

Лабораторная работа № 2. Создание баз данных посредством SQL 13

· Теоретические сведения 13

Условия целостности данных 13

Создание таблиц 15

· Варианты заданий 17

· Контрольные вопросы 17

Лабораторная работа № 3. Процедуры и функции PL/SQL 18

· Теоретические сведения 18

Структура программ PL/SQL 18

Объявления 19

Функциональные возможности программ 20

Обработка исключительных ситуаций 23

Типы программ PL/SQL. Хранимые процедуры и функции. 25

· Варианты заданий 27

· Контрольные вопросы 29

Лабораторная работа № 4. Триггеры баз данных 29

· Теоретические сведения 29

· Варианты заданий 31

· Контрольные вопросы 32

Лабораторная работа № 5. Динамический SQL 32

· Теоретические сведения 32

Модули 32

Модуль DBMS_OUTPUT 34

Обзор динамического SQL 36

· Варианты заданий 43

· Контрольные вопросы 44

Индивидуальная работа. Создание приложений баз данных средствами ODBC 45

· Теоретические сведения 45

Архитектура ODBC 45

Основы ODBC API 46

Коды возврата 46

Основной алгоритм программ ODBC 48

Функции инициализации и завершения 49

Выполнение операторов 52

Функции управления каталогом. 53

Непосредственное выполнение 56

Подготавливаемое выполнение 57

Использование параметров при выполнении 58

Выборка результатов 63

Выборка информации о результирующем множестве 64

Базовые функции выборки данных 65

· Варианты заданий 68

Литература 72

ВВЕДЕНИЕ

Целью настоящего методического пособия является оказание помощи студентам специальности “автоматическое управление в технических системах” при выполнении ими лабораторных работ по курсу “ Базы данных САПР ”

При составлении настоящего пособия не ставилась задача описания в полной мере теоретических сведений по реляционной модели баз данных, а также особенностей построения баз данных систем автоматизированного проектирования. Данный материал сугубо теоретический и предполагает рассмотрение на практических занятиях. Здесь преследуется цель – дать общие указания, т. е. отметить характерные особенности построения и функционирования информационных систем с базами данных и систематизировать справочный материал, что может быть полезно при разработке информационных систем. В свою очередь, особенности построения требуют определенных практических навыков программирования приложений баз данных, приобретение которых и является целью лабораторных занятий по данному курсу.

Лабораторное занятие № 1. Простые SQL-запросы

Цель занятия – изучить назначение простых запросов SQL, возможностей задания, а также ограничений задания запросов для безошибочной их обработки СУБД.

· Теоретические сведения

Запросы на извлечение информации

Инструкция SQL select извлекает информацию из базы данных и возвращает её в виде таблицы результатов запроса. Данная инструкция состоит из шести синтаксических единиц, называемых зачастую предложениями. Предложения select и from являются обязательными, остальные четыре включаются в запрос при необходимости.

- в предложении select указывается список столбцов, которые должны быть возвращены инструкцией.

- в предложении from указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.

Например следующий запрос извлекает из таблицы Staff три столбца:

Вывести для каждого сотрудника имя, фамилию и занимаемую должность

selectfname, lname, position

fromstaff;

Помимо этого в предложении select может содержаться и так называемый “вычисляемый столбец”, например:

Выдать строки сотрудников с указанием зарплаты с 10 % надбавкой

selectfname, lname, position, ( salary + 0.1*salary ) aspercent

fromstaff;

В данном запросе percent определяет название столбца в результирующей таблице.

- предложение where показывает, что в результаты запроса следует включать только некоторые строки. В данном предложении вслед за ключевым словом where следует условие отбора, которое и определяет какие строки должны включаться в результирующий набор.

В SQL обычно используются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами)

- сравнение

- проверка на принадлежность диапазону

- проверка на членство в множестве

- проверка на соответствие шаблону

- проверка на равенство значению NULL.

Приведем примеры использования различных условий отбора в предложении where

Найти служащих, родившихся до 1988 года

selectfname, lname

fromstaff

where DOB< ’01-Jan-88’;

Здесь следует обратить внимание на формат записи данных типа дата. В различных СУБД формат записи даты неодинаков. Используемый в примере формат поддерживается в ORACLE и специфичен для него. Даты в ORACLE так же как и строковые константы заключаются в парные одинарные кавычки.

Найти служащих, родившихся в интервале времени с 1октября 1963 по 31 декабря 1971

selectfname, lname

fromstaff

where DOB between ’01-Oct-63’ and ’31-Dec-71’;

Здесь следует отметить, что проверка на принадлежность диапазону не расширяет возможностей SQL, поскольку её можно выразить в виде двух сравнений, т. е. выражение A between B and C эквивалентно (A> =B) and (A< =C)

Вывести информацию об офисах, расположенных в Минске, Витебске и Бресте

select address, tel_no

from branch

where city in (‘Минск’, ‘Витебск’, ‘Брест’);

Проверка in не добавляет новых возможностей, так же как и between and, так как условие X in (A, B, C) полностью эквивалентно условию (X=A) or (X=B) or (X=C)

- Вывести информацию о всех сотрудников фамилии которых начинаются на букву К

select lname, address, tel_no

from staff

where lname like ‘K%’

Здесь также следует обратить внимание на запись шаблона строки сравнения в условии like. Указанный способ задания строки шаблона характерен для диалекта ORACLE и отличен от регламентированного стандартом. Символ ‘%’ - замещает произвольную последовательность символов, а ‘_’ – замещает одиночный символ. Строки-шаблоны так же как и обыкновенные строки-константы заключаются в парные одинарные кавычки.

Запросы с объединением таблиц

Если необходимо получить информацию более чем из одной таблицы, то можно либо применить подзапрос, либо выполнить соединение таблиц. Для выполнения соединения достаточно в предложении from указать имена объединяемых таблиц, а в предложении where указать столбцы соединения таблиц.

Составить список всех сотрудников, работающих в Минском отделении.

select fname, lname, position, S.tel_no

from Branch B, Staff S

where B.bno=S.bno and city = ‘Минск’;

Тот же запрос можно выполнить с помощью подзапроса

select fname, lname, position, tel_no

from staff where bno in (select bno from branch where city= ‘Минск’);

В связи с подчиненными запросами можно выделить ряд особенностей:

- Таблица результатов подчиненного запроса всегда состоит из одного столбца

- В подчиненный запрос не может включаться предложение order by

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

Итоговые запросы

Результирующую таблицу итогового запроса можно рассматривать как некий отчет. Для получения подобных отчетов в запросе на получение итоговой информации требуется указывать предложение group by и возможное having для отбора групп. Ограничением при выполнении итоговых запросов является то, что здесь в предложении select могут употребляться лишь столбцы группировки (т.е. те которые указываются в предложении group by), строковые константы и статистические функции. Таких функций в SQL пять:

- sum() – для вычисления суммы всех значений столбца-аргумента

- avg() – для вычисления среднего значения столбца

- min() – определяет минимальное значение столбца

- max() – определяет максимальное значение столбца

- count() – подсчитывает число всех определенных значений столбца

- count(*) – подсчитывает число строк таблицы.

Определить сколько в среднем получают сотрудники в зависимости от занимаемой ими должности.

select position, avg(salary)

from staff

group by position;

Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек.

select bno, count(sno)

from staff

group by bno;

having count(sno)> 2;

Представления

Представление – объект базы данных, представляющий собой именованный и сохраненный запрос. Часто представления также называют “виртуальными таблицами”. В случае если определение представления простое, СУБД выполняет его “на лету”, в обратном же случае СУБД приходится “материализовать ” представление, т.е. сохранять его результаты во временной таблице. Создаются представления посредством инструкции create view. Использование данной инструкции продемонстрируем на примере.

Создать представление, включающее в себя список сотрудников, работающих в отделениях Минска.

create view Minsk as select fname, lname, address, position, tel_no, sex, dob

from staff

where bno in (select bno

from branch

where city=’Минск’);

· Варианты заданий

1. Получить список сотрудников с зарплатой от 200 до 300.

Получить список сотрудников, работающих в офисах Бреста и Гомеля.

Определить суммарную и среднюю зарплату сотрудников в зависимости от занимаемой ими должности.

Создать представление с информацией о офисах в Бресте.

2. Определить адреса и телефоны офисов, расположенных в Минске и Гродно.

Кто из сотрудников предлагает для аренды 3-х комнатные квартиры.

Вывести итоговый отчет о средней и суммарной зарплатах в зависимости от половой принадлежности сотрудников.

Создать представление с информацией о директорах отделений.

3. Определить адреса всех 3-х комнатных квартир, предлагаемых в аренду.

Получить список арендаторов, осматривавших объекты аренды 20 октября 1999 года.

Определить минимальную и максимальную зарплаты сотрудников различных отделений.

Создать представление с информацией о владельцах, чьи дома или квартиры осматривались потенциальными арендаторами.

4. Вывести номера домашних телефонов всех директоров.

Составить список владельцев всех 3-х комнатных квартир.

Подсчитать количество сотрудников в каждом из отделений.

Создать представление сотрудниках и объектов, которые они предлагают в аренду.

5. Вывести список сотрудников, родившихся до 1980 года.

Подсчитать сколько сотрудников работает в отделении в Бресте

Вывести количество арендаторов, желающих арендовать 3-х комнатные и 4-х комнатные квартиры.

Создать представление об объектах с минимальной рентной стоимостью.

6. Определить адреса всех квартир с рентной стоимостью не более 300

Подсчитать сколько менеджеров работает в Минске

Получить итоговый список с количеством домов и квартир сдаваемых в аренду.

Создать представление о арендаторах, желающих арендовать 3-х комнатные квартиры.

Создать представление об отделении с максимальным количеством работающих сотрудников.

7. Вывести домашние телефоны всех потенциальных арендаторов, желающих арендовать дома.

Вывести телефоны владельцев, дома или квартиры которых осматривались 12 сентября 2001года.

Определить квартиры и дома минимальной рентной стоимости.

Создать представление о женщинах директорах.

8. Вывести список всех женщин-менеджеров

Определить максимальную зарплату сотрудников в отделении в Гродно

Определить количество осмотров с группировкой по датам.

Создать представление о количестве сделанных осмотров с комментариями.

9. Определить сколько объектов было осмотрено потенциальными арендаторами за Октябрь 1996 года

Создать список сотрудников предлагающих объекты недвижимости в Минске.

Определить суммарную рентную стоимость объектов в Минске и Гродно.

Создать представление о сотрудниках, чьи фамилии начинаются с буквы ‘О’.

10. Кто из арендаторов желает снять 4-хкомнатные квартиры.

Определить сколько потенциальных арендаторов осмотрели предлагаемые им квартиры или дома.

Определить какие из офисов имеют более 3-х сотрудников.

Создать представление, содержащее информацию об отделении, где предлагаются в аренду наиболее недорогие 2-х комнатные квартиры в смысле их средней стоимости.

· Контрольные вопросы

1. Как вы понимаете значение NULL?

2. Какова общая структура запроса на извлечение информации

3. Перечислите особенности итоговых запросов.

4. Что такое представление и для чего создаются такие объекты базы данных.

Условия целостности данных

Для сохранения непротиворечивости и правильности хранимой информации, СУБД поддерживает так называемые условия целостности данных, которые значения могут быть записаны или изменены в базе данных, а также удалены без последствий целостности, хранимых в базе данных.

Как правило, в реляционных БД используются следующие условия целостности:

- Обязательное наличие данных. Некоторые столбцы базы данных должны обязательно содержать некоторые определенные значения, т.е. для них не допускаются значения NULL. В качестве примера из рассмотренного выше примера учебной базы данных такими столбцами являются, очевидно, столбцы Fname, Lname и некоторые другие. Стандарт ANSI/ISO и большинство коммерческих СУБД (ORACLE в частности) поддерживают выполнение подобного условия посредством ограничения NOT NULL.

- Условие на значение. Во многих коммерческих СУБД при создании базы данных для некоторых столбцов таблиц можно назначить условия на принимаемые ими значения. Данные условия задаются в инструкциях по созданию таблиц посредством так называемых check conditions. В качестве примера можно рассмотреть фрагмент инструкции create table для создания таблиц:

create table staff (sno integer not null,

age integer,

check (sno between 101 and 199),

check (age > =21));

- Целостность таблицы (сущности). Первичный ключ таблицы должен в каждой строке иметь уникальное значение и не допускать значений NULL. Создание первичных ключей таблиц в БД поддерживается СУБД посредством ограничения primary key.

- Деловые правила. Обновление информации в базе данных может быть ограничено так называемыми деловыми правилами. Так в системе можно запретить принимать заказы на определенный вид продукции, в случае если на складе не имеется её достаточного количества. Реализация данных правил в СУБД может реализовываться посредством автоматически запускаемых процедур, запуск которых осуществляется после выполнения некоторых из операций по изменению содержимого записей базы. Такие процедуры сохраняются в откомпилированном виде в базе и являются наряду с таблицами также объектами базы. Эти процедуры называются триггерами, и при создании “прикрепляются ” к определенным таблицам, а также настраиваются на определенные операции изменения записей этих таблиц.

- Непротиворечивость. Многие реальные операции вызывают в базе данных несколько изменений одновременно. Например, операция увольнения сотрудника должна реально сопровождаться переводом всех выполняемых им работ на какого-то другого сотрудника, либо путем временного задания значений NULL или значений по умолчанию для некоторых предназначенных для этого столбцов. Зачастую последовательное выполнение инструкций, в совокупности решающих общую задачу по изменению информации в базе данных с соблюдением конечного непротиворечивого состояния базы данных, решается СУБД посредством поддержки механизма транзакций. Транзакция считается успешно завершенной, если каждая из операций входящих в неё выполнена успешно. Подтверждением успешного завершения является выполнение инструкции commit. В случае неуспешного завершения хотя бы одной из операций, транзакция должна отменить все произведённые ранее изменения в базе и перевести её в начальное непротиворечивое состояние посредством инструкции rollback. Выполнение инструкции rollback вызывает “откат” текущей транзакции.

- Ссылочная целостность. В рассматриваемом контексте нарушение целостности может случится при выполнении операций удаления или обновления над связанными строками различных таблиц базы данных. Стандартом ANSI/ISO регламентированы правила удаления и обновления связанных строк таблиц. Таких правил четыре:

- restrict – Правило, запрещающее удалять или обновлять строки-предки, в случае если на них ссылаются строки-потомки из других таблиц базы данных. В некоторых СУБД данное правило носит название no action. Следует также иметь в виду, что данное правило зачастую воспринимается СУБД по умолчанию.

- cascade – Правило, в соответствии с которым СУБД производит автоматическое (каскадное) удаление или обновление значений в некоторых столбцах строк-потомков при удалении или изменении соответствующих им столбцов в строках-предках. Данное правило опасно в употреблении, в случае если в базе существуют разнообразные множественные связи. Правило реализуется посредством ограничения foreign key посредством фраз on delete или on update cascade.

- set null и set default – Правила, регламентируемые стандартом ANSI/ISO, однако не поддерживаемые СУБД ORACLE. В соответствии с ними при удалении или изменении первичного ключа строки-предка во внешнем ключе строки-потомка устанавливаются значения null или значения по умолчанию.

Создание таблиц

Инструкция create table SQL позволяет создавать таблицы БД и ограничения на значения столбцов, а также создавать связи типа первичный ключ - внешний ключ между таблицами.

Синтаксическая диаграмма данной инструкции представлена на рисунке 1.

Рисунок 1 Cсинтаксическая диаграмма create table

Создание таблицы ORDERS посредством create table можно продемонстрировать следующим примером, на рисунке 2 продемонстрированы таблицы и предполагаемые связи, создаваемые между ними.

Рисунок 2 Пример именованных таблиц и именованных связей между ними

create table orders (ord_n integer not null,

ord_date date not null,

cust_n varchar2(5) not null,

sale_n varchar2(5) not null,

prod_n varchar2(10) not null,

qty integer,

amount integer,

primary key (ord_n),

unique (ord_n),

constraint placed_by foreign key (cust_n) references customers on delete cascade,

constraint taken_by foreign key (sale_n) references salesreps,

constraint is_for foreign key (prod_n) references products);

· Варианты заданий

1. Создать базу данных малого коммерческого предприятия (таблицы сотрудников, заказчиков, продукции, заказов) со всеми необходимыми ограничениями и именованными связями между таблицами.

2. Создать базу данных произвольного сборочного производства. Предварительно зарисовать иерархическую структуру используемых деталей. Уровни иерархии выдерживать по этапам сборки. Перенести иерархию на таблицы реляционной базы данных с организацией связей между таблицами, отражающими реальные связи между этапами сборки.

3. Создать базу данных для системы визуального проектирования радиоэлектронных устройств. Предусмотреть таблицы различных радиоэлектронных модулей с соответствующими атрибутами типа диапазонов входных токов (напряжений, сопротивлений) и др. В результирующей таблице готовых устройств отмечать ссылки на используемые модули.

· Контрольные вопросы

1. Какие типы ограничений, используются при создании таблиц?

2. Какие правила можно использовать для обеспечения ссылочной целостности при создании связанных таблиц БД?

3. Объясните порядок задания определения столбцов и ограничений при создании таблиц в инструкции create table?

Структура программ PL/SQL

Собственно SQL – это лишь язык доступа к данным, дающий возможность приложениям помещать данные в базы данных и извлекать их оттуда. Другими словами SQL не является полнофункциональным языком программирования, которым можно пользоваться для разработки эффективных приложений баз данных. Для создания приложений необходимо применять процедурные языки, которые будут охватывать SQL и таким образом взаимодействовать с базами данных. Таким языком является “собственный” процедурный язык ORACLE под названием PL/SQL. При его изучении необходимо вначале ознакомится со структурной организацией программ PL/SQL.

Базовой единицей PL/SQL является блок. Блоки имеют следующую структуру:

declare

/*раздел объявлений (переменные, типы, курсоры и др.)*/

begin

/*Основной раздел блока, называемый выполняемым. Содержит процедурные и SQL - операторы*/

exception

/*Раздел обработки исключительных ситуаций. Содержит операторы обработки ошибок*/

end;

Обязателен только выполняемый раздел, кроме того он должен содержать по крайней мере один выполняемый оператор.

Язык программирования PL/SQL разработан на базе языка третьего поколения Ada. Одним из общих свойств этих языков и является их блочная структура.

Объявления

В блоке программ PL/SQL можно объявлять конструкции различных типов. Здесь рассмотрим, как объявляются переменные и константы; подтипы, определяемые пользователями; курсоры.

Переменные и именованные константы могут иметь любой тип данных ORACLE или ANSI/ISO. В следующем примере объявляются переменная и именованная константа при помощи ANSI – типа integer:

declare

id integer;

standard constant integer: =500;

Когда в программе объявляется переменная, ей может быть присвоено начальное значение, либо значение по умолчанию:

declare

id integer: =0;

standard integer default 500;

Подтипы, определяемые пользователями. Пример объявления пользовательских подтипов и соответствующих переменных удобно пояснить на примере.

declare

varchar2_50 varchar2(50);

subtype description is varchar2_50;

current_description description default ‘unknown’

Тип varchar2() – тип данных ORACLE, предназначенный для задания строковых переменных переменной длины.

Атрибуты В программах PL/SQL можно использовать атрибуты %type и %rowtype. Данные атрибуты используются для объявления переменных, констант и даже определяемые пользователями подтипы и составные типы, соответствующие свойствам столбцов и таблиц баз данных. Использование атрибутов не только упрощает объявление программных конструкций, но и делает программы более удобными для модификации баз данных.

Так с помощью атрибута %type можно объявлять тип данных, принадлежащий другой программной конструкции или столбцу таблицы базы данных.

declare

id parts.id %type;

unit_price parts.price %type;

В приведенном выше фрагменте предполагается, что parts является таблицей базы данных, а id и price её атрибутами.

С помощью атрибута %rowtype можно объявлять переменные, имеющие тип записи, и другие конструкции:

declare

type parts_table is table of parts %rowtype;

current_part parts_table;

Здесь объявляется “агрегированный” тип parts_table и переменная current_part этого типа.

Курсоры. В литературе [2] курсоры иногда называют рабочей областью SQL – оператора. Однако точнее его можно определить как указатель на текущую строку результирующего множества оператора. Операторы select возвращающие одну строку обрабатываются СУБД автоматически, т. е. она сама создаёт курсор и считывает данные посредством него из результирующего вектора. Для обработки строк запроса, возвращающего несколько строк, приложение должно объявлять курсор явно, указав его имя, а затем ссылаться на него при обработке строк по очереди. При обработке строк следует иметь в виду, что курсор устанавливается перед первой строкой результирующего множества, сформированного СУБД по выполнении запроса и сохраненного на сервере БД. Следующий пример демонстрирует процедуру объявления курсоров в блоке объявлений программPL/SQL.

declare

cursor part_cur is select * from parts;

cursor cust_cur (state_id char) is

select id, l_name, f_name, phone

from customers

where state=state_id;

Здесь part_cur – это простой курсор, соответствующий всем строкам и столбцам таблицы parts, cust_cur – пример параметризованного курсора с параметром state_id.

Модули

Модуль (package) – это группа процедур, функций и других конструкций, хранимых вместе в базе данных как одна единица. Модули особенно полезны для компоновки нескольких процедур и функций, имеющих отношение к конкретному приложению баз данных.

Модуль состоит из двух частей: описания и тела.

- Описание модуля определяет интерфейс связи с этим модулем. В описании модуля объявляются все переменные и именованные константы, курсоры, процедуры, функции и другие конструкции модуля, которые необходимо сделать доступными для программ, внешних по отношению к этому модулю. Другими словами, всё объявленное в описании модуля является общим.

- В теле модуля (package body) определяются все общие процедуры и функции, объявленные в описании модуля. Кроме того, в тело модуля могут включаться определения других конструкций, не указанных в его описании. Такие конструкции модуля являются частными, т. е. доступными только для программ внутри модуля.

Ниже приведен пример определения описания модуля и его тела:

сreate or replace package part_mgmt is

--глобальная переменная

сurrent_part parts %rowtype;

--процедуры и функции

--insert_part вводит новый элемент ассортимента в таблицу parts

--update_part_unitprice обновляет цену элемента ассортимента

--delete_part удаляет элемент ассортимента

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

procedure insert_part (part_record parts %rowtype);

procedure update_part_unitprice (part_id in integer, new_price in number);

procedure delete_part (part_id in integer);

end part_mgmt;

сreate or replace package body part_mgmt is

procedure insert_part (part_record parts %rowtype) is

dup_primary_key exception;

begin

insert into parts

values (part_record.id, part_record.unitprice, part_record.description);

exception

when dup_ primary_key then

raise_application_error (-20001, ’Дубликат ID’);

when others then

raise_application_error (-20002, ’Неопределенная ошибка’);

end insert_part;

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

…определение других процедур и функций модуля…

end part_mgmt;

Обращение к процедурам и функциям модуля происходит посредством уточняющей записи через точку.

Модуль DBMS_OUTPUT

С помощью процедур модуля 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);

Данные процедуры переопределяются типом параметра.

Буфер организован в виде строк, каждая из которых может состоять не более чем из 255 байт. PUT_LINE добавляет к аргументу символ новой строки, сообщая о конце строки; PUT же этого не делает. Вызов PUT_LINE аналогичен вызову PUT с последующим вызовом NEW_LINE.

Процедура GET_LINE вызывается следующим образом:

procedure GET_LINE (line out varchar2, status out integer);

где line представляет собой последовательность символов, из которых состоит одна строка буфера, а status указывает на то, успешно или нет была считана эта строка. Максимальная длина строки – 255 байт. Если строка считана, то в переменной status находится 0, если в буфере больше нет строк для считывания, то в status – 1.

Аргументом процедуры GET_LINES является индексная таблица. Тип таблицы и вызов данной процедуры выглядят следующим образом:

type CHARARR is table of varchar2(255) index by binary integer;

procedure GET_LINES (lines out chararr, numlines in out integer);

где numlines – число запрошенных строк, на входе в GET_LINES указывается число запрошенных строк, на выходе – число фактически возвращаемых строк.

Тип CHARARR определен в модуле DBMS_OUTPUT, поэтому если GET_LINES вызывается явным образом, нужно объявлять переменную с типом DBMS_OUTPUT.CHARARR. Например:

declare

v_Data DBMS_OUTPUT.CHARARR;

v_NumLines number;

begin

DBMS_OUTPUT.ENABLE (1000000);

DBMS_OUTPUT.PUT_LINE (‘Line one’);

DBMS_OUTPUT.PUT_LINE (‘Line two’);

DBMS_OUTPUT.PUT_LINE (‘Line three’);

v_NumLines: =3;

DBMS_OUTPUT.GET_LINES( v_Data, v_NumLines);

for v_Counter in 1..3 v_NumLines loop

insert into temp_table (char_col)

values (v_Data(v_Counter));

end loop;

end;

Процедура ENABLE задает размер буфера в байтах, по умолчанию задается размер 20000 байт, а максимальный размер – 1000000 байт. Если объявлена процедура DISABLE, то содержимое буфера уничтожается и последующие вызовы PUT и PUT_LINE бесполезны.

По существу, модуль DBMS_OUTPUT реализует алгоритм “первым пришел – первым обслужен”. В утилите SQL*Plus имеется средство, называемое SERVEROUTPUT (серверный вывод), команда SQL*Plus, называемая SET SERVEROUTPUT ON, неявно вызывает процедуру DBMS_OUTPUT.ENABLE, которая устанавливает внутренний буфер серверного вывода. Если нужно, можно указать размер буфера с помощью команды:

SET SERVEROUTPUT ON SIZE размер_буфера

где размер_буфера – первоначальный размер буфера (аргумент процедуры DBMS_OUTPUT.ENABLE, вызываемой по умолчанию). Процедура DBMS_OUTPUT.GET_LINES вызывается после окончания блока PL/SQL. Это означает, что результаты будут выводится на экран после завершения блока, а не вовремя его выполнения.

Рисунок 3. Использование SERVEROUTPUT и PUT_LINE

Обзор динамического SQL

Все программы, приведенные выше, являются статическими. Это означает, что структура SQL – операторов известна уже во время компиляции программы (механизм раннего связывания). Динамический SQL позволяет выполнять SQL – операторы, создавая их динамически, во время выполнения программы (механизм позднего связывания), а потом производить их грамматический разбор и обработку.

Выполнять SQL – конструкции можно двумя способами:

- с помощью модуля DBMS_SQL

- посредством внутреннего динамического SQL (для ORACLE 8i).

В данном пособии рассматриваются вопросы написания приложений, использующих только модуль 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.

Обработка операторов DML посредством DBMS_SQL

Для обработки операторов update, delete, insert средствами модуля DBMS_SQL необходимо последовательно выполнить следующие действия:

1. Открыть курсор

Осуществляется посредством вызова процедуры OPEN_CURSOR, описание которой в модуле выглядит следующим образом:

OPEN_CURSOR return integer

Параметры в данной процедуре отсутствуют.

Каждый вызов возвращает целое число, представляющее собой идентификационный номер курсора. Этот номер используется в последующих вызовах курсора. В границах одного курсора можно по очереди обрабатывать несколько SQL – операторов или выполнять один и тот же оператор несколько раз.

2. Выполнить грамматический разбор оператора

При выполнении грамматического разбора оператор направляется на сервер БД. Сервер проверяет его синтаксис и семантику и возвращает ошибку (устанавливая исключительную ситуацию), если нарушены требования грамматики. Кроме того во время разбора определяется план выполнения оператора. Осуществляется грамматический разбор посредством вызова процедуры DBMS_SQL.PARSE, описание которой в модуле имеет следующий вид:

procedure PARSE (c in integer,

statement in varchar2,

language_flag in integer).

Здесь с – идентификационный номер курсора, предварительно должен быть открыт посредством OPEN_CURSOR,

statement – оператор, грамматический разбор которого выполняется,

language flag – указывает как трактовать оператор, значение NATIVE – режим установленный для той базы данных, с которой выполнено соединение.

3. Привязка входных переменных

При выполнении этой операции, заполнители указанные в нем связываются с фактическими переменными. Имена заполнители обычно предваряют символом двоеточия. Процедура BIND_VARIABLE выполняет привязку и объявление имен заполнителей. Размер и тип данных фактических переменных также устанавливается BIND_VARIABLE посредством набора переопределенных вызовов:

procedure BIND_VARIABLE (c in integer,

name in varchar2,

value in number),

procedure BIND_VARIABLE (c in integer,

name in varchar2,

value in varchar2),

procedure BIND_VARIABLE (c in integer,

name in varchar2,

value in varchar2,

out_value_size in integer).

Здесь параметр name – это имя заполнителя, с которым будет связана переменная, value – реальные данные, которые будут привязываться, тип и размер этой переменной также считываются. При необходимости данные, содержащиеся в этой переменной будут преобразованы. Параметр out_value_size – параметр, задаваемый при привязке переменных varchar2 и char , если указан, то это максимальный ожидаемый размер значения в байтах, если не указан, то используется размер указанный в параметре value.

4. Выполнение оператора

Осуществляется посредством функции EXECUTE. Описание её в модуле выглядит следующим образом:

function EXECUTE (c in integer) return integer;

Здесь с – идентификатор предварительно открытого курсора.

12 3 4 5 6 7 8 9 Следующая ⇒