По курсу «информационные технологии. Информационное обеспечение асоиу»

Методические указания

к выполнению лабораторных работ

По курсу «Информационные технологии. Информационное обеспечение АСОИУ»

Москва 2010 год.

Методическое указание ориентировано на изучение процедурного расширения языка SQL СУБД ORACLE – PL/SQL. Отдельно рассматриваются вопросы управления безопасностью баз данных и резервного копирования. Каждая лабораторная работа состоит из теоретической и практической частей. Теоретическая часть подробно описывает рассматриваемую тему и приводит примеры команд. Практическая часть содержит задания для выполнения студентами в компьютерном классе. В заключительной части методического указания приведен перечень рекомендуемой литературы для самостоятельного изучения.

Данное учебное пособие предназначено для студентов специальности 230102 факультета ИСТАС, изучающих дисциплину «Информационные технологии. Информационное обеспечение АСОИУ», с целью ориентации их на самостоятельное овладение знаниями и навыками при выполнении ими лабораторно-практических работ.

 

Составитель

Старший преподаватель кафедры «Информационные системы и технологии управления в строительстве»

И.В. Бучацкий

 

 

СОДЕРЖАНИЕ:

Предисловие. 4

Лабораторная работа №1. Последовательности и триггеры. 8

Лабораторная работа №2. Хранимые процедуры. Логические операторы. Циклы. 17

Лабораторная работа №3. Курсоры. 26

Лабораторная работа №4. Секция исключений. Функции. 33

Лабораторная работа №5. Управление пользователями. Роли. Синонимы. 43

Лабораторная работа №6. Импорт, экспорт, загрузка данных. 53

Библиографический список. 65

Предисловие

Цель данного методического пособия состоит в изложении дополнительных возможностей, имеющихся в СУБД ORACLE, по управлению данными. В первой части лабораторных работ по курсу «Базы данных» были рассмотрены основы работы с реляционными базами данных, в частности, базовые команды языка структурированных запросов (SQL). Вторая часть лабораторных работ, в первую очередь, направлена на изучение процедурного расширения языка SQL - языка PL/SQL (Procedure Language SQL), который используется при написании подпрограмм. К подпрограммам PL/SQL относятся: хранимые процедуры, хранимые функции, триггеры и т.д. В отличие от динамического SQL модули PL/SQL являются статическими объектами базы данных: они компилируются и хранятся в базе данных. Перечислим преимущества использования подпрограмм PL/SQL:

· Модульное проектирование. Все прикладные программы, работающие с одной БД, совместно используют разработанные подпрограммы PL/SQL. Таким образом, централизуются бизнес-правила, достигается повторное использование кода, сокращается размер и сложность приложений.

· Удобная поддержка. Внесение изменение в единую подпрограмму PL/SQL требует меньше трудозатрат, чем внесение изменений в каждую конкретную прикладную программу в отдельности.

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

· Дополнительная функциональность. Язык PL/SQL включает ряд различных дополнительных операндов, операторов и функций (циклы, логические операторы, курсоры, локальные переменные и т.д.), которые недоступны в «чистом» SQL. За счет их применения многие задачи разработчиков требуют гораздо меньше времени на их решение.

Кроме PL/SQL, несколько лабораторных работ посвящены вопросам безопасности баз данных ORACLE. Это и управление пользователями (ролями), и резервное копирование баз данных.

Для выполнения лабораторных работ на рабочей машине должна быть установлена серверная либо клиентская версия СУБД Oracle 10g XE. Если на рабочей машине стоит серверная версия СУБД, то для подключения используются следующие параметры подключения:

· логин “student”;

· пароль “istas”;

· имя сервера “localhost”.

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

В качестве прикладной программы для выполнения SQL-запросов можно использовать либо утилиту командной строки SQL*Plus, либо браузер (работа строится через веб-интерфейс). Второй вариант предпочтительнее, т.к. нагляден и имеет возможности для работы с помощью мыши. Рассмотрим два варианта подключения по отдельности:

1. Подключение с помощью SQL*Plus. Запуск программы: кнопка «Пуск», «Программы», «Oracle Client 10g Express Edition», «Run SQL Command Line». Для подключения к учебной БД необходимо ввести команду:

connect student/istas@edu;

Вместо параметра edu необходимо подставить имя либо ip-адрес сервера СУБД. Если сервер установлен локально, то вместо edu необходимо написать localhost.

2. Подключение через веб-интерфейс. Для данного типа подключения необходимо, прежде всего, запустить браузер (Internet Explorer). В адресной строке надо написать адрес веб-сервера и открыть начальную страницу:

http://edu:8080/apex

Вместо параметра edu необходимо подставить имя либо ip-адрес сервера СУБД. Если сервер установлен локально, то вместо edu необходимо написать localhost. На начальной странице необходимо ввести параметры подключения и нажать кнопку «Login»:

Username = student
Password = istas

На следующей странице нажать кнопку «SQL», «SQL Commands».

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

 

 

Импорт таблицы с данными.

IMP USERID=HR/1@LOCALHOST FILE=C:\IMP.DAT LOG=C:\IMP.LOG TABLES=ABC IGNORE=Y.

Импорт схемы пользователя.

IMP USERID=HR/1@LOCALHOST FILE=C:\IMP.DAT LOG=C:\IMP.LOG FROMUSER=STUDENT TOUSER=UTEST IGNORE=Y.

Импорт БД.

IMP USERID=SYS/1@LOCALHOST FILE=C:\DB.DAT LOG=C:\DBIMP.LOG FULL=Y.

SQL*Loader. Одной из типичных проблем, с которой часто сталкиваются администраторы БД, является перемещение данных из внешних источников в БД ORACLE. Сложность этой задачи возрастает с появлением хранилищ данных, приходится перемещать уже не мегабайты данных, а гигабайты, а в некоторых случаях и терабайты. ORACLE предусмотрел для решения этой задачи специальную утилиту. SQL*Loader - универсальное инструментальное средство, которое загружает внешние данные в таблицы БД ORACLE. Утилита SQL*Loader является гибкой и настраиваемой до такой степени, что обычно удается обойтись без процедур на языках третьего поколения с внедренными операторами SQL.

Для работы утилиты SQL*Loader необходимы входные параметры двух типов:

1. внешние данные, которые находятся на диске;

2. управляющая информация, которая содержится в управляющем файле и описывает характеристики внешних данных.

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

Таблица 6. Основные параметры утилиты SQL*Loader

Параметр	Описание
USERID	Указывает имя и пароль пользователя, который осуществляет процесс загрузки. Формат параметра — «имя пользователя/пароль@сервер».
CONTROL	Управляющий файл.
BAD	Параметр указывает путь к файлу, в который будут записаны незагруженные записи.
DATA	Параметр указывает на файл с внешними данными для загрузки в БД.
ERRORS	Максимальное количество допустимых ошибок при выполнении загрузки.
LOG	Файл протокола, который после выполнения загрузки содержит подробное описание процесса загрузки.

Внешние данные. Утилита SQL*Loader может обрабатывать файлы данных практически любого типа и поддерживает собственные типы данных почти для любой платформы. Данные обычно считываются из одного или нескольких файлов данных. Допускается располагать данные для загрузки непосредственно в управляющем файле.

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

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

Таблица 7. Основные параметры управляющего файла

Параметр	Описание
LOAD DATA CHARACTERSET	Кодировка символов, в которой производится загрузка данных. Для кириллицы значение этого параметра следует задать CL8MSWIN1251.
INFILE	INFILE "Путь\Имя файла" указывает файл с данными INFILE * данные для загрузки находятся в управляющей файле.
REPLACE (APPEND)	Заменить существующие записи таблицы данными из файла (Добавить данные к существующим записям таблицы).
INTO TABLE ИМЯ ТАБЛИЦЫ	Указывают таблицу для загрузки данных.
FIELDS TERMINATED BY 'разделитель'	Указывает на разделитель между полями в файле данных
(СПИСОК_КОЛОНОК) (A,B,C,D)	Список колонок, в которые будут загружаться данные.
BEGINDATA	Начало данных в управляющем файле

Пример 1.

Файл данных:

1,3,5

2,4,2

3,21,02

Управляющий файл:

load data

infile "c:\load.txt"

append

into table abc

fields terminated by ',' (a,b,c)

Вызов SQL*Loader:

SQLLdr userid=student/istas@localhost control=c:\upr.txt log=c:\loader.log bad=c:\bad.txt.

Пример 2.

Управляющий файл:

load data

CHARACTERSET CL8MSWIN1251

infile *

append

into table Sotr

fields terminated by ','

(FirstName, LastName, Age)

begindata

Семен,Иванов,24

Иван,Петров,42

Олег,Сидоров,23 (a,b,c)

Вызов SQL*Loader:

SQLLdr userid=student/istas@localhost control=c:\upr.txt log=c:\loader.log bad=c:\bad.txt.

 

Задания для самостоятельной работы:

1. Подключитесь к учебной БД под учетной записью student. Создайте двух новых пользователей (USER1) и (USER2). Создайте новую роль. Присвойте роли права подключаться, создавать таблицы, создавать последовательности, создавать триггеры и роль DBA.

2. Создайте таблицу-справочник стран: ID (первичный ключ), название страны (символьное, уникальное). Добавьте в таблицу две-три записи.

3. Создайте таблицу-справочник городов: ID (первичный ключ), страна (внешний ключ к таблице стран), название города (символьный). Создайте последовательность. Создайте триггер к таблице, который перед вставкой записи заполняет первичный ключ. Добавьте в таблицу пять записей.

4. Экспортируйте таблицу стран вместе с данными. Импортируйте таблицу из файла в схему пользователя USER1. Подключитесь к учебной БД под учетной записью USER1. Напишите запрос, который бы возвращал все записи таблицы стран. Добавьте три записи в таблицу. Удалите таблицу стран.

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

6. Экспортируйте всю схемe пользователя student в файл. Импортируйте схему пользователя student из файла в схему пользователя USER2. Подключитесь к учебной БД под учетной записью USER2. Напишите запрос, который бы возвращал все записи таблицы городов. Добавьте три записи в таблицу. Удостоверьтесь, что триггер заполнил первичный ключ.

7. Создайте текстовый файл или новый документ в EXCEL. Заполните десять строк для переноса в таблицу городов с помощью SQL*Loader. Создайте в текстовом редакторе управляющий файл и импортируйте в таблицу городов данные. Напишите запрос, который бы возвращал все записи таблицы городов. Убедитесь, что после импорта появились новые записи.

Библиографический список

 

1. Oracle Database 10g Release 2 - Administrator's Guide, 2005.

2. Oracle Database 10g Release 2 - Application Developer's Guide – Fundamentals, 2005.

3. Oracle Database 10g Release 2 - Concepts, 2005.

4. Oracle Database 10g Release 2 – SQL Reference, 2005.

5. Oracle Database 10g Release 2 – PL/SQL User's Guide and Reference, 2005.

6. Аллен, Кристофер. 101: Oracle PL/SQL: Как писать мощные и гибкие программы на PL/SQL / Кристофер Аллен; [Пер. с англ. Т. Москалев]. - [М.]: Лори, [2001]. - 350 с.: ил.; 23 см. - ISBN 5-85582-139-0.

7. Грабер, Мартин. Справочное руководство по SQL: [Перевод] / Мартин Грабер. - М.: Лори, Б. г. (1998). - 291 с.; 24 см.

8. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт; [пер. с англ. и ред. К. А. Птицына]. - 8-е изд. - Москва [и др.]: Вильямс, 2008. - 1327 с.: ил., табл.; 24 см. - ISBN 978-5-8459-0788-2.

9. Фейерштейн, Стивен. Oracle PL/SQL для профессионалов / С. Фейерштейн, Б. Прибыл; [Пер. с англ. О. Здир]. - 3-е изд. - М. [и др.]: Питер, 2003. - 940 с.: ил.; 25 см. - (Программирование для Oracle) (Серия "Для профессионалов"). - ISBN 5-318-00528-4.

Методические указания

к выполнению лабораторных работ

по курсу «Информационные технологии. Информационное обеспечение АСОИУ»

Москва 2010 год.

Методическое указание ориентировано на изучение процедурного расширения языка SQL СУБД ORACLE – PL/SQL. Отдельно рассматриваются вопросы управления безопасностью баз данных и резервного копирования. Каждая лабораторная работа состоит из теоретической и практической частей. Теоретическая часть подробно описывает рассматриваемую тему и приводит примеры команд. Практическая часть содержит задания для выполнения студентами в компьютерном классе. В заключительной части методического указания приведен перечень рекомендуемой литературы для самостоятельного изучения.

Данное учебное пособие предназначено для студентов специальности 230102 факультета ИСТАС, изучающих дисциплину «Информационные технологии. Информационное обеспечение АСОИУ», с целью ориентации их на самостоятельное овладение знаниями и навыками при выполнении ими лабораторно-практических работ.

 

Составитель

Старший преподаватель кафедры «Информационные системы и технологии управления в строительстве»

И.В. Бучацкий

 

 

СОДЕРЖАНИЕ:

Предисловие. 4

Лабораторная работа №1. Последовательности и триггеры. 8

Лабораторная работа №2. Хранимые процедуры. Логические операторы. Циклы. 17

Лабораторная работа №3. Курсоры. 26

Лабораторная работа №4. Секция исключений. Функции. 33

Лабораторная работа №5. Управление пользователями. Роли. Синонимы. 43

Лабораторная работа №6. Импорт, экспорт, загрузка данных. 53

Библиографический список. 65

Предисловие

Цель данного методического пособия состоит в изложении дополнительных возможностей, имеющихся в СУБД ORACLE, по управлению данными. В первой части лабораторных работ по курсу «Базы данных» были рассмотрены основы работы с реляционными базами данных, в частности, базовые команды языка структурированных запросов (SQL). Вторая часть лабораторных работ, в первую очередь, направлена на изучение процедурного расширения языка SQL - языка PL/SQL (Procedure Language SQL), который используется при написании подпрограмм. К подпрограммам PL/SQL относятся: хранимые процедуры, хранимые функции, триггеры и т.д. В отличие от динамического SQL модули PL/SQL являются статическими объектами базы данных: они компилируются и хранятся в базе данных. Перечислим преимущества использования подпрограмм PL/SQL:

· Модульное проектирование. Все прикладные программы, работающие с одной БД, совместно используют разработанные подпрограммы PL/SQL. Таким образом, централизуются бизнес-правила, достигается повторное использование кода, сокращается размер и сложность приложений.

· Удобная поддержка. Внесение изменение в единую подпрограмму PL/SQL требует меньше трудозатрат, чем внесение изменений в каждую конкретную прикладную программу в отдельности.

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

· Дополнительная функциональность. Язык PL/SQL включает ряд различных дополнительных операндов, операторов и функций (циклы, логические операторы, курсоры, локальные переменные и т.д.), которые недоступны в «чистом» SQL. За счет их применения многие задачи разработчиков требуют гораздо меньше времени на их решение.

Кроме PL/SQL, несколько лабораторных работ посвящены вопросам безопасности баз данных ORACLE. Это и управление пользователями (ролями), и резервное копирование баз данных.

Для выполнения лабораторных работ на рабочей машине должна быть установлена серверная либо клиентская версия СУБД Oracle 10g XE. Если на рабочей машине стоит серверная версия СУБД, то для подключения используются следующие параметры подключения:

· логин “student”;

· пароль “istas”;

· имя сервера “localhost”.

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

В качестве прикладной программы для выполнения SQL-запросов можно использовать либо утилиту командной строки SQL*Plus, либо браузер (работа строится через веб-интерфейс). Второй вариант предпочтительнее, т.к. нагляден и имеет возможности для работы с помощью мыши. Рассмотрим два варианта подключения по отдельности:

1. Подключение с помощью SQL*Plus. Запуск программы: кнопка «Пуск», «Программы», «Oracle Client 10g Express Edition», «Run SQL Command Line». Для подключения к учебной БД необходимо ввести команду:

connect student/istas@edu;

Вместо параметра edu необходимо подставить имя либо ip-адрес сервера СУБД. Если сервер установлен локально, то вместо edu необходимо написать localhost.

2. Подключение через веб-интерфейс. Для данного типа подключения необходимо, прежде всего, запустить браузер (Internet Explorer). В адресной строке надо написать адрес веб-сервера и открыть начальную страницу:

http://edu:8080/apex

Вместо параметра edu необходимо подставить имя либо ip-адрес сервера СУБД. Если сервер установлен локально, то вместо edu необходимо написать localhost. На начальной странице необходимо ввести параметры подключения и нажать кнопку «Login»:

Username = student
Password = istas

На следующей странице нажать кнопку «SQL», «SQL Commands».

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