II.1. 2. Создание пользователя Oracle и таблиц его схемы

Большинство примеров по освоению SQL запросов в настоящем пособии «привязаны» к незначительно скорректированной предметной области пользователя DEMO, поставлявшейся в ранних версиях Oracle. ERD диаграмма этой предметной области представлена на рис.1. Корретировка заключается в сокращении размера нескольких записей в таблице «CUSTOMER», сокращении размера трех столбцов и удалении одного столбца этой таблицы – для обеспечения более удобного для восприятия вывода

Рис.1 Server Model Diagram предметной области, используемой в этом учебном пособии для освоения SQL

информации из этой таблицы. Скрипты создания и наполнения таблиц пользователя DEMO представлены в приложении 1.

Создание предметной области состоит из нескольких шагов:

a. Создание пользователя

b. Назначение привилегий пользователю

с. Соединение созданного пользователя с Oracle и запуск на исполнение скрипта,

создающего и заполняющего таблицы предметной области

Для создания пользователя DEMO надо «войти» в SQL*Plus пользователем SYSTEM. По умолчанию при установке Oracle этот пользователь имеет пароль manager (отметим

здесь, что регистрозависимый пароль «появился» только в Oracle11g, поэтому пароль, как и имя пользователя, можно вводить на любом регистре). Подсказка «system@10g» появится, если предвариательно создать и разместить в соответствующем месте файл glogin.sql в соотвтетствии с п. II.1.1.

system@10g> Create user d7 identified by d7

Default tablespace users

Temporary tablespace temp

Quota 1m on users;

Пользователь создан.

system@10g> grant connect, resource, create view to d7;

Привилегии предоставлены.

Эта команда позволит пользователю DEMO соединяться с сервером Oracle и создавать объекты (таблицы, программные объекты, представления и т.д.) базы данных.

system@10g> @connect demo/demo

Эта команда будет выполнена успешно, если предварительно выполнена настройка утилиты SQL*Plus в соответствии с пунктом II.1. 1. (созданы и размещены в соответствующих местах файлы glogin.sql и connect.sql)

demo@10g>

Пользователь DEMO теперь должен запустить скрипт создания и заполнения таблиц.

Чтобы такой скрипт «появился», скопируйте текст приложения 1 в блокнот (notepad.exe) и разместите его в папке c:\tmp, например, с именем demo.sql. После этого выполните команду:

demo@10g>@c:\tmp\demo.sql

II.1.3.Переменные привязки в среде SQL*Plus

В SQL*Plus можно также выделять именованную область памяти для хранения некоторой информации. Такая область используется внутри программ PL/SQL и SQL-операторов, однако находится вне программных блоков, поэтому можно по очереди выделять ее разным программным блокам и после выполнения каждого из них выводить ее содержимое. Эта именованная область памяти называется переменной привязки /2/ (связываемой переменной /1/)— bind variable. Перемен­ная привязки (в приведенном ниже примере «var1»)в SQL*Plus выделяется с помощью команды VARIABLE, кото­рая действует только тогда, когда указана в ответ на подсказку SQL*Plus, но не внутри программы PL/SQL. Внутри нее переменная привязки ограничивается двоеточием.

Q1_1 (Query 1 по теме 1) Создание, обращение к и вывод переменной привязки

DEMO@10G>variable varl number

DEMO@10G>begin

:varl:=10;

end;

/

PL/SQL procedure successfully completed.

DEMO@10G>print var1

VA R1

-----

Использование переменных привязки в работе программных кодов с повторяющимися циклами способствует эффективному выполнению этих кодов.

II.1.4.Переменные подстановки в среде SQL*Plus

Q1_2 (Query 2 по теме 1) Использование переменной подстановки («table_name» в данном случае) для имени таблицы и значения столбца («n_dept» в данном случае) DEPARTMENT_ID в ней при извлечении информации . Знак «&» предшествует имени переменной подстановки

demo@10g> demo@10g> select * from &table_name where DEPARTMENT_ID=&n_dept;

Введите значение для table_name: department

Введите значение для n_dept: 10

прежний 1: select * from &table_name where DEPARTMENT_ID=&n_dept

новый 1: select * from department where DEPARTMENT_ID=10

Q1_3 Использование переменной подстановки («1» в данном случае) для значения столбца JOB_ID

demo@10g> select EMPLOYEE_ID,LAST_NAME,SALARY from EMPLOYEE

where JOB_ID='&1';

Введите значение для 1: 669

прежний 2: where JOB_ID='&1'

новый 2: where JOB_ID='669'

Сохраним последний запрос в файл Q3_1.sql

demo@10g> save Q1_3

Создано file Q1_3.sql

Посмотрим значение параметра SQL*Plus termout

demo@10g> show termout

termout OFF

Параметр отключен

Посмотрим значение параметра SQL*Plus echo

demo@10g> show echo

echo OFF

Параметр отключен. Заметим здесь: если в ответ на эту команду результат «echo ON» для совпадения результатов выполнения последующих команд с приведенными в тексте выполните команду «set echo off».

Выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669

demo@10g> @Q1_3 669

demo@10g>

Скрипт выполнен, но ни выполняемая команда SQL, ни результат выполнения этой команды SQL*Plus не вывел

Задействуем параметр termout и выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669 еще раз

demo@10g> set termout on

demo@10g> @Q1_3 669

прежний 2: where JOB_ID='&1'

новый 2: where JOB_ID='669'

Выведен результат выполнения SQL команды, но сама команда не выведена.

Задействуем теперь параметр echo и выполним скрипт Q1_3 с вводимым значением столбца JOB_ID = 669 еще раз

demo@10g> set echo on

demo@10g> @Q1_3 669

demo@10g> select EMPLOYEE_ID,LAST_NAME,SALARY from EMPLOYEE

2 where JOB_ID='&1'

3 /

прежний 2: where JOB_ID='&1'

новый 2: where JOB_ID='669'

Выведены SQL команда и результат ее выполнения

Отметим здесь, что при отключенном параметре termout вывод полностью отключается вне зависимости от значения параметра echo

demo@10g> set termout off

demo@10g> @Q1_3 669

demo@10g>

Не выводится не только результат выполнения команды, но и сама команда, не смотря на установленный в «ON» параметр echo

Q1_4 Использование переменных подстановки («loc_number», «reg_group» в данном случае) для вставки значений столбцов LOCATION_ID, REGIONAL_GROUP в таблицу location

demo@10g> insert into location(LOCATION_ID,REGIONAL_GROUP) values(&loc_number,&reg_group);

Введите значение для loc_number: 125

Введите значение для reg_group: 'МОСКВА'

прежний 1: insert into location(LOCATION_ID,REGIONAL_GROUP) values(&loc_number,&reg_group)

новый 1: insert into location(LOCATION_ID,REGIONAL_GROUP) values(125,'МОСКВА')

1 строка создана.

Для возврата содержимого таблицы к исходному состоянию ликвидируем последнюю вставку командой «rollback»

demo@10g>rollback;

откат завершен

Q1_5 Использование двойного знака «&&» для переменной подстановки («table_name» в данном случае). Значение введенной после сдвоенного знака «&» переменной подстановки запоминается в SQL*Plus, и в следующий раз при обращении к этой переменной подстановки ее значение вводится автоматически

demo@10g> select * from &&table_name;

Введите значение для table_name: location

прежний 1: select * from &&table_name

новый 1: select * from location

demo@10g> select * from &&table_name;

прежний 1: select * from &&table_name

новый 1: select * from location

Здесь уже отсутствует приглашение для ввода значения table_name.

Покажем теперь, как изменить значение для переменной подстановки «table_name».

Сначала выведем текущее значение этой перменной:

demo@10g> define table_name

DEFINE TABLE_NAME = "location" (CHAR)

demo@10g>

Теперь присвоим этой перменной подстановки новое значение:

demo@10g> DEFINE TABLE_NAME = "DEPARTMENT"

demo@10g> select * from &&table_name;

прежний 1: select * from &&table_name

новый 1: select * from DEPARTMENT