PL/SQL procedure successfully completed.

Листинг 11. Выполнение в SQL*Plus блока PL/SQL

Для просмотра ошибок при компиляции программ PL/SQL можно использовать команду SHOW ERRORS. Любое SQL-выражение или программу PL/SQL можно сохранить в текстовом файле из SQL*Plus с помощью команды SAVE. Для редактирования этого файла можно использовать редактор SQL*Plus. Для того чтобы снова сохранить его, требуется ввести команду SAVE... REPLACE. Для использования другого текстового редактора требуется выполнить команду EDIT. При этом запустится указанный в настройках SQL*Plus текстовый редактор. Если имя файла не указывать, то SQL*Plus сохранит содержимое SQL-буфера в файл и откроет этот файл в редакторе. После редактирования и сохранения изменений в файле его содержимое SQL*Plus перепишет в SQL-буфер. Чтобы загрузить файл в буфер, используйте команду GET. Примеры этих команд приведены ниже.

SAVE имя_файла

SAVE имя_файла REPLACE

EDIT имя_файла

GET имя_файла

В операторах SQL и анонимных блоках PL/SQL можно использовать средства подстановки переменных SQL*Plus. SQL* Plus будет запрашивать значение каждой переменной, имеющей префикс &, перед выполнением оператора SQL или блока PL/SQL. Например, перед выполнением следующего запроса SQL*Plus запросит значение atl:

SQL> SELECT ZVANIE FROM GRUP236

2 WHERE ID=& ID;

Enter value for atl: 3

old.1: SELECT COUNT(l) с FROM GRUP236 WHERE ID=& ID

new 1: SELECT COUNT(1) с FROM GRUP236 WHERE ID=’2'

ZVANIE

Efreitor

Листинг 12. Использование переменных подстановки SQL*Plus

Из SQL*Plus можно вызывать хранимые программы с помощью команды EXECUTE, не используя конструкции BEGIN... END, В следующем примере вызывается процедура DBMS OUPUT.PUT LINE:

SQL> set serveroutput on

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Hello, word');

Hello, word

Листинг 13. Использование команды EXECUTE

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

SQL> set line 250

SQL> col sid format a3

SQL> col serf format a5

SQL> col program format a40

SQL> col username format a8

SQL> col osuser format a8

, SQL> col machine format a8

SQL> col command format alO

SQL> set heading on

SQL> SELECT s.sid sid,

S.serial sertt,

" 3 s.username username, s.osuser, s.machine,

Substr (upper (s.program), 1, 60) program,,

Substr(a.name, 1, 13) command

6.FROM v$session s, sys.audit_actions a

7 WHERE a.action=s.command

8 AND s.sid> 5

ORDER BY 1, 2

10./

SID SER# USERNAME OSUSER MACHINE PROGRAM COMMAND

U1 UD DFORT PLUS80W SELECT

Листинг 14. Форматирование отчетов в SQL*Plus

Здесь мы с помощью команд COLUMN и SET HEADING. установили вывод заголовков столбцов и их ширину. Более полное руководство по использованию SQL*Plus можно получить в разделе документации Oracle SQL*Plus User's Guide and Reference. Следует отметить, что для работы с Oracle существуют более удобные интегрированные средства. В частности, это программные продукты фирмы Quest Software: SQL Navigator, Instance Monitor, TOAD и другие. Ознакомительные версии программ находятся на сайте компании —www.quest.com.

Информация о результатах выполнения операции

Выполнение какой-либо операции клиентского приложения или оператора SQL на сервере может завершиться с различными итогами. Успешность завершения операции определяется значениями возвращаемых ею параметров, характеризующих итог выполнения операции, или сообщениями об ошибках. Сообщения об ошибках специфичны для различных продуктов Oracle. Во всех сообщениях об ошибках указывается префикс, указывающий на то, какое приложение выдает ошибку. Например, префикс IMP указывает на то, что ошибка произошла при импорте данных с использованием утилиты импорта. Для сервера Oracle ошибки имеют префикс ORA, а соответствующие параметры передаются в форме двух кодов завершения: SQLCODE, SQLERRM. Значение SQLERRM содержит развернутое текстовое сообщение об ошибке с указанием ее причин. Пятисимвольное значение параметра SQLCODE определяется международным стандартом, формируемым Международной организацией стандартизации (ISO). Нулевое значение параметра свидетельствует об успешном завершении операции, отрицательные значения соответствуют завершению операции с некоторой ошибкой, а положительные — завершению с предупреждением о возможной ошибке или предназначены для передачи параметров, характеризующих особенности завершения операции (например, отсутствие данных, удовлетворяющих критерию запроса). Таким образом, это — две различные формы представления информации, характеризующей успешность завершения оператора языка SQL. Для облегчения поиска причин ошибок и их исправления в сообщениях об ошибках указываются имена объектов, при операциях над которыми произошли ошибки, типы ошибок и другая диагностическая информация. Иногда пользователь может получить стек ошибок. Стек ошибок — это последовательность ошибок, произошедших на различных уровнях приложения при выполнении команды или оператора SQL. В этом случае следует искать причину появления стека ошибок в первых сообщениях об ошибках. В следующем примере при передаче утилите импорта в командной строке в качестве параметра неверного имени пользователи или пароля был получен следующий стек ошибок:

IMP-00058: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon denied

IMP-00005: all allowable logon attempts failed

IMP-00000: Import terminated unsuccessfully

При подключении к базе данных произошла ошибка, о чем выдал соответствующее сообщение сервер Oracle (сообщение с префиксом ORA), затем свои сообщения об ошибках выдала утилита импорта (сообщения с префиксом IMP). Для анализа причин возникновения ошибки иногда бывает важно сохранить весь стек сообщений, а не довольствоваться последним из них. Список типов ошибок сервера Oracle включает в себя несколько тысяч типов. Они обеспечивают точную диагностику при самых различных программных ошибках.

Однако в таком большом и сложном программном комплексе могут возникать и неописанные внутренние ошибки, например, при нарушении целостности словаря данных. При возникновении внутренней ошибки выдается сообщение вида:

ORA-00600: internal error code,

arguments: [num], [ ], [ ], [ ], [ ], [ ]

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

ТИПЫ ДАННЫХ ORACLE

Данный раздел представляет типы данных, поддерживаемые Oracle. Все типы данных, перечисленных в стандарте ANSI SQL92, полностью поддерживаются в OracleS. Типы данных, специфичные для сервера Oracle, отмечены строкой [Только для Oracle].

Строки символов

Тип CHARACTER используется для хранения строк фиксированной длины. Для хранения строк резервируется определенное в параметре длина пространство. При необходимости короткая строка дополняется пробелами.

Синтаксис: CHARACTER[(длина)], СНAR длина)].

Если длина строки не указана явно, она полагается равной 1. Максимальное значение параметра длина — 255 символов.

Пример

strl CHAR(10)

Str2 CHARACTER

Тип VARCHAR используется для хранения строк переменной длины. Для хранения строк резервируется реально необходимое пространство.

Синтаксис: VARCHAR [(длина)], CHAR VARYING [(длина)], CHARACTER VARYING [(длина)].' Если длина строки не указана явно, она полагается равной 1. Максимальное значение параметра длина — 4000 символов.

Пример

varstrl VARCHAR(10)

varstr2 CHARACTER VARYING(5)

Тип VARCHAR2 [Только для Oracle] используется для хранения строк переменной длины. Для хранения строк резервируется реально необходимое пространство. Основная причина введения типа VARCHAR2 состоит в том, что фирма-производитель декларирует неизменность этого типа в более поздних реализациях Oracle, в то время как тип VARCHAR будет соответствовать требованиям стандартов SQL.

Синтаксис: VARCHAR2 (длина). Длина строки должна быть указана явно. Минимальное значение параметра длина — 1 символ, максимальное значение параметра длина — 4000 символов.

Пример

.. strlora VARCHAR2(10)

Между этими типами есть различие — данные типа CHAR имеют фиксированную длину и могут содержать до 255 символов, a VARCHAR2 имеет переменную длину и может содержать до 4000 символов. По умолчанию в SQL все строковые литералы имеют тип CHAR. Значение любого столбца типа CHAR(255) занимает 255 байт дискового пространства и также 255 байтов в листьевом наборе индекса. Эти различия следует учитывать при проектировании структуры таблиц, выделении места для хранения данных и сравнениях строк в SQL-выражениях. Если две строки имеют разную длину, то при сравнении более короткая строка дополняется пробелами до длины второй строки. В том случае, если ограничение в 4000 символов существенно и есть необходимость хранить более длинные тексты именно в VARCHAR2, можно создать таблицу, каждая запись которой будет содержать одну строку текста и для ускорения доступа поместить ее в однотабличный кластер. Для хранения символьных данных с использованием национальных алфавитов предназначены типы NCHAR и NVARCHAR2.

Тип LONG [Только для Oracle] используется для хранения больших строк переменной длины. Для хранения строк резервируется реально необходимое пространство. Использование переменных типа LONG имеет следующие ограничения:

— таблица не может содержать более одного столбца с типом данных LONG;

— для столбцов типа LONG не разрешено построение индекса;

— столбцы типа LONG не могут включаться в ограничения целостности (кроме ограничения NULL или NOT NULL);

— их нельзя использовать в выражениях вида INSERT INTO... SELECT... FROM;

— столбцы этого типа нельзя использовать в конструкциях GROUP BY, CONNECT BY.

Фирма-производитель считает тип LONG устаревшим и не рекомендует его использовать. Тип данных LONG может не поддерживаться в более поздних версиях Oracle.

Синтаксис: LONG [(длина)]. Если длина строки не указана явно, она полагается равной 2 мегабайтам. Максимальное значение параметра длина — 2 гигабайта символов.

Пример

longstr LONG(10000)

morelongstr LONG

Числовые типы

Тип INTEGER используется для представления целых чисел в диапазоне от -231 до 231.

Синтаксис: INTEGER, INT.

Пример

varintl INTEGER

varint2 I-NT

Тип NUMBER[Только для Oracle] используется для представления чисел с заданной точностью.

Синтаксис: NUMBER [(точность [, масштаб])]. Если значение параметра точность не указано явно, оно полагается равным 38. Значение параметра масштаб по умолчанию предполагается равным 0. Значение параметра точность может изменяться от 1 до 38, значение параметра масштаб может изменяться от -84 до 127. Использование отрицательных значений масштаба означает сдвиг десятичной точки в сторону старших разрядов. Например, определение NUMBER (7, -3) означает округление до тысяч.

Пример

varcounter NUMBER

В таблице 2 приведены представления числа 123456.789 с различной точностью и масштабом.

Таблица 2. Представления числа 123456.789 с различной точностью и масштабом

Определение Представление

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

NUMBER 123456

NUMBER(7, 1) 123456.7

NUMBER(5, 2) ошибка в данных

NUMBER(8, 3) 1234456.789

NUMBER(7, -2) 1234500

Для совместимости с другими СУБД Oracle поддерживает типы данных DECIMAL, DOUBLE_PRECISION, NUMERIC, DEC и REAL. Все типы числовых данных реально хранятся в одном и том же внутреннем формате Oracle.

Типы DECIMAL И NUMERICполностью эквивалентны типу NUMBER.

Синтаксис: DECIMAL [(точность [, масштаб])], DEC [(точность [, масштаб])], NUMERIC [(точность [, масштаб])].

Пример

vardecl DEC

vardec2 DEC(5)

vardecS D'ECIMAL (8, 3)

varnum NUMERIC

Тип ROWID — специальный тип данных, который служит для представления указателей на запись в таблице. При создании строки в таблице ей сразу присваивается ROWID, который остается неизменным до ее удаления или реорганизации данных. Использование ROWID — самый быстрый способ доступа к строке в таблице.

SQL > SELECT ROWID FROM GRUP236 WHERE ROWNUM=1;

ROWID

AAADFlAADAAAGnPABX

Листинг 15. Вычисление ROWID

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

SQL> CREATE TABLE GRUP236 (ID NUMBER, FIO VARCHAR2(30), DOLGNOST VARCHAR2(20), ZVANIE VARCHAR2(20), OKLAD NUMBER);

GRUP236e created.

SQL> INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

Row created.

SQL> INSERT INTO GRUP236 VALUES(2, ’Anshukov Anton Olegovich’, ’ryadovoi’, ’efreitor’, 3100);

Row created.

SQL> INSERT INTO GRUP236 VALUES(3, ’Bagenov Petr Igorevich’, ’ryadovoi’, ’efreitor’, 3000);

Row created.

SQL> INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

Row created.

SQL> DELETE FROM GRUP236

WHERE ROWID NOT IN (SELECT MIN(ROWID)

FROM GRUP236

GROUP BY ID)

5 /

Row deleted.

Листинг 16. Пример удаления записей-дубликатов

Битовые строк

Тип RAW[Только для Oracle] используется для хранения двоичных строк переменной длины. Отличие типа RAW от типов CHAR, VARCHAR2 состоит в том, что для типов символьных строк Oracle производит автоматическое преобразование данных при их передаче между клиентом и сервером. Утилиты Import и Export также производят автоматическое преобразование символьных строк при логической разгрузке и загрузке баз данных в соответствии с настройками средств поддержки национальных языков. Oracle выдает данные типа RAW в шестнадцатеричном виде.

Синтаксис: RAW [(длина)]. Параметр длина измеряется в байтах. Максимальное значение параметра длина — 2000 байт.

Пример

bitarrayl RAW(10)

Тип LONG RAW [Только для Oracle] используется для хранения больших битовых строк переменной длины.

Синтаксис: LONG RAW [(длина)]. Параметр длина измеряется в байтах. Если длина строки не указана явно, онаполагается равной 2 мегабайтам. Максимальное значение параметра длина — 2 гигабайта символов. Для переменных типаLONG RAW невозможно построение индекса.

Пример

verylongstrl LONG RAW(1000000)

Дата и время

Тип DATE [Только для Oracle] используется для хранения даты и времени. Допускаются даты с 1 января 4712 г. дон.э. до 31 декабря 4712 г. н.э. Для формирования значения типа DATE в SQL и PL/SQL обычно используется встроенная функция ТО_ОАТЕ(произвольная_строка_даты', 'формат_даты *). При определении даты без уточнения времени по умолчанию принимается время полуночи. Функция SYSDATE возвращает текущее значение даты и времени. Значение функции определяется средствами операционной системы компьютера, на котором работает сервер Oracle.

Синтаксис: DATE.

Пример

birthday DATE

Наличие специального типа для хранения даты и времени позволяет поддерживать специальную арифметику дат и времен. Добавление к переменной типа DATE целого числа интерпретируется Oracle как определение более поздней даты, а вычитание выполняется как определение более ранней. Рассмотрим несколько примеров:

SQL > SELECT SYSDATE FROM dual;

SYSDATE

13-07-2007

SQL> SELECT SYSDATE+10 " sysd+10" FROM dual;

SYSD+10

23-07-2007

SQL> SELECT SYSDATE-10 " sysd-10" FROM dual;

SYSD-10

03-07-2007

Листинг 17. Запросы, иллюстрирующие арифметику дат Oracle

Также возможно использование юлианской даты. Юлианская дата — это число дней, прошедших с 1 января 4712 г. до нашей эры. Реализация юлианской даты в Oracle не имеет компоненты времени. Для использования юлианской даты в функциях TO_CHAR и TO_DATE применяется маска формата " J".

SQL> SELECT TO_CHAR(TO_DATE('13-07-2007',

2 'DD-MM-YYYY'), 'J') JDATE FROM dual;

JDATE

-------

Листинг 18. Использование юлианской даты

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

LOB-объекты

Тип BLOB [Только для Oracle] используется для хранения двоичных данных размером до 4 гигабайт. Для работы с большими двоичными объектами используется стандартный пакет DBMS_LOB, о котором рассказывается в разделе " PL/SQL — процедурное расширение языка SQL".

Синтаксис: BLOB.

Пример

doc BLOB

Тип CLOB [Только для Oracle] используется для хранения символьных данных переменной длины размером до 4 гигабайт, использующих однобайтовую кодировку.

Синтаксис: CLOB.

Пример

cdoc CLOB

Тип NCLOB [Только для Oracle] используется для хранения символьных данных размером до 4 гигабайт, использующих одно- или многобайтовую кодировку.

Синтаксис: NCLOB.

Пример

ncdoc NCLOB

Тип BFILE [Только для Oracle] используется для хранения указателей на двоичные данные, находящиеся во внешних по отношению к СУБД файлах. Сами файлы хранятся в файловой системе.

Синтаксис: BFILE.

Пример

file doc BFILE

⇐ Предыдущая 123 4 5 Следующая ⇒