Oracle. Сохранение и выборка данных

Именование таблиц и столбцов

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

Правила

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

• Максимальная длина имени таблицы или столбца равна 30 символам.
• Имена таблиц и столбцов могут содержать буквы, цифры и символ подчеркивания (_). (Есть еще пара специальных символов, которые можно использовать в случае острой необходимости, но в обычной работе это не принесет ничего, кроме проблем, поэтому лучше ограничиться буквами, цифрами и символом подчеркивания.
• Имена таблиц и столбцов должны начинаться с буквенного символа. Имя может содержать цифры или символы подчеркивания, но в любом случае должно начинаться с буквы.
• Символы верхнего и нижнего регистров в именах таблиц и столбцов считаются одинаковыми.
• Имя таблицы или столбца не должно содержать пробелы.
• В Oracle таблицы присваиваются пользователям; по умолчанию они присваиваются тому пользователю, который их создал . Каждая из таблиц должна иметь имя, отличное от имен других таблиц этого пользователя. Иными словами, у пользователя не может быть двух таблиц с одним и тем же именем. (Однако разные пользователи могут без проблем создавать таблицы с одинаковыми именами.) Все столбцы в пределах таблицы должны иметь уникальные имена.
• Некоторые слова представляют собой команды и параметры Oracle, a следовательно, не могут использоваться в качестве имен таблиц или столбцов.

Совет

     Единственный способ гарантировать, что имя таблицы никогда не совпадет с зарезервированным словом Oracle, — это предварять его аббревиатурой, обозначающей систему, к которой относится таблица. Например, в системе Accounts Payable («счета кредиторов») имя каждой таблицы может начинаться с «AP_».

 

Рекомендации

     Перечисленные ниже пункты полезно учитывать при проектировании таблиц.
• Имена таблиц следует записывать в единственном, а не множественном числе. Всем и так понятно, что таблица PRODUCT будет содержать записи о многих товарах, поэтому нет необходимости отмечать это в имени таблицы. К тому же, глядя на диаграммы с таблицами базы данных (они рассматриваются в главе 7), вы сможете переходить от таблицы к таблице, говоря себе примерно следующее: «PURCHASE ORDER ссылается на PRODUCT…».
• Не включайте слова TABLE или DATA в имя таблицы. Опытные пользователи понимают, что объектом базы данных, хранящим информацию, является таблица, а таблицы содержат данные. Не нужно напоминать им об этом.

 

Совет

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

 

Как в Oracle хранится текст

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

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

     Для чего может потребоваться помещать число в текстовый столбец, если с ним нельзя проводить вычисления? Дело в том, что в ряде случаев числа используются не только для вычислений. Пример — телефонные номера. Возьмем такой номер:
(800)555-1212.

Совет

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

 

Как Oracle сохраняет числа

     Для определения столбцов, в которых будут храниться только числа, используется тип данных NUMBER. При определении столбца типа NUMBER также указывается, сколько цифр должен хранить столбец. Эта спецификация может состоять из двух частей: количества цифр до десятичной точки и количества цифр после десятичной точки.

     Тип данных NUMBER позволяет хранить поистине огромные числа: наибольшее значение составляет 999999999999999999999999999 999 999 999 990 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000. Число цифр после десятичной точки может достигать 127. Такой диапазон значений рассчитан на промышленное применение и является одной из черт, отличающих серьезные базы данных, такие, как Oracle, от офисных продуктов, к которым относятся электронные таблицы.

 

Как Oracle сохраняет даты

     С датами, хранимыми в виде текста, проводить вычисления нельзя, поскольку в текстовом представлении они не имеют числовых значений. Это просто строки символов, которые мы, люди, интерпретируем как даты. Для вычислений с датами их нужно как-то преобразовать в числа. Поскольку большинство таких вычислений включает подсчет дней, самый удобный подход — присвоить каждому дню уникальный номер так, чтобы номер завтрашнего дня был на единицу больше номера сегодняшнего дня. Если теперь вычесть более раннюю дату из более поздней, разность будет равна числу дней между ними. Такой способ счета дней существует давно: это юлианский календарь. Когда система использует юлианский календарь, начальному дню присваивается номер 1, следующий за ним называется днем 2 и т. д. Поскольку каждый последующий день увеличивает счетчик на единицу, календарь такого типа идеально подходит для вычислений с датами. Oracle поддерживает юлианский календарь, где начальной датой является 1 января 4712 г. до н.э. Преобразование дат между визуальным форматом (например, ’08-MAY-2004′) и его юлианским эквивалентом производится автоматически. Мы просто вводим даты в привычном текстовом представлении, затем Oracle преобразует их в свой внутренний, юлианский, формат, а при выборке этих дат из таблицы они снова отображаются виде дней, месяцев и лет. Нам никогда не потребуется просматривать даты в юлианском формате.

Примечание

     В SQL-операторах даты должны заключаться в одиночные кавычки, как и текстовые строки.

 

Определение структуры таблицы

     Создавая свою собственную таблицу, вы знаете ее структуру… до поры до времени. Потом вам придется заниматься другими вещами, и вы забудете подробности того, что делали раньше. Если же таблицу создал кто-то другой, вы вообще не имеете представления об ее структуре. Следовательно, нужно иметь возможность определять структуру существующей таблицы. Вероятно, вас не очень удивит известие о том, что в Oracle есть команда, предназначенная имен но для этой цели. Она называется DESCRIBE (сокращенно — DESC) и имеет следующий синтаксис:

DESC имя_таблицы

     Это одна из немногих команд, которые не требуется завершать точкой с запятой. С другой стороны, наличие точки с запятой ничему не повредит, так что можете ее ставить — просто для закрепления привычки.

 

Столбцы NULL и NOT NULL

     При разработке таблиц для хранения информации определенного назначения (давайте вместо слова «назначение» употребим «приложение») вы можете в довольно широких пределах контролировать, что может, а что не может попа- дать в эти таблицы. Коль скоро вам предоставлена такая возможность, на вас ложится ответственность за ее использование таким образом, чтобы обеспечить максимально возможное качество данных в таблицах. Одним из решений, которые следует принять в первую очередь, является решение о том, какие столбцы записи должны содержать данные, а какие могут оставаться пустыми.

 

Примечание

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

 

Математические операторы

     На техническом языке математические символы, обозначающие операции, называются операторами (operators). Например, знаки плюса и минуса — это операторы. Oracle поддерживает четыре стандартные арифметические операции — сложение, вычитание, умножение и деление. Умножение обозначается звездочкой (*). Для деления используется символ /, а для сложения и вычитания — символы + и -, соответственно.

 

Что такое выражение?

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

 

Приоритеты операторов

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

 

Соединение двух и более частей текста

     При работе с базами данных часто возникают ситуации, когда желательно показать содержимое двух и более текстовых столбцов в одной строке, продолжая хранить части текста в разных столбцах. Например, на почтовой наклейке в одной строке указываются имя, фамилия, город, штат и почтовый индекс (или аналогичная информация, принятая в вашей стране), но изначально эти данные хранятся в разных столбцах таблицы. Соединение двух частей текста называется конкатенацией (concatenation). Чтобы указать на необходимость соединения двух столбцов в операторе SELECT, нужно поместить между их именами две вертикальные черты (||).

 

Присваивания столбцам псевдонимов

     Как вы могли заметить, в результате последней команды заголовок столбца совсем «отбился от рук». По умолчанию заголовками столбцов служат их имена. Однако при выполнении оператора SELECT, в котором используется конкатенация столбцов, в качестве заголовка отображается все выражение. Обычно это выглядит непривлекательно и редко приносит пользу. SQL позволяет определить, что будет помещено на вершине столбца, выбранного оператором SELECT. Все, что для этого нужно, — ввести после имени столбца (или выражения) текст, который вы хотите видеть в качестве заголовка.

     Имя-заменитель, которое вы указываете для столбца, называется псевдонимом столбца (alias). Если заключить псевдоним в двойные кавычки, в нем можно будет использовать пробелы и буквы нижнего регистра. (Двойные кавычки необходимы для того, чтобы Oracle не пытался интерпретировать псевдоним как имя столбца, подлежащего выборке.)

 

Итоги

     Имя таблицы или столбца может иметь длину до 30 символов. Оно должно начинаться с буквы и может содержать буквы, цифры и ограниченный набор специальных символов, наиболее полезным из которых является символ подчеркивания «_» позволяющий визуально разделять слова в имени. Пробелы в именах таблиц и столбцов недопустимы. Oracle рассматривает символы верхнего и нижнего регистров как одинаковые.

     Определенное количество слов зарезервировано и не может использоваться в качестве имен таблиц или столбцов. В частности, к ним относятся команды (например, CREATE) и имена объектов (например, ROW). Зарезервированных слов слишком много, и запомнить их все большинству людей не под силу. Случайно употребив одно из них, вы сразу узнаете об этом, поскольку вместо нормального завершения команды Oracle выдаст сообщение об ошибке с фразой «Invalid table name» или «Invalid column name». Если проблема связана с именем таблицы, добавьте в начале имени аббревиатуру, обозначающую систему, частью которой является таблица (например, AP_ADMIN вместо ADMIN). Если проблема связана с именем столбца, добавьте к имени одно-два слова, чтобы лучше описать содержимое столбца.

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

     Имена таблиц лучше всего записывать в единственном, а не множественном числе: например, таблицу служащих следует назвать EMPLOYEE, а не EMPLOYEES. Кроме того, не нужно включать в имя таблицы слова TABLE и DATA, поскольку все, о чем они сообщают, следует из самого факта работы с таблицей. При создании таблицы вы должны указать тип данных и длинукаждого столбца. Таблицы Oracle могут хранить любые разновидности данных — текст, числа, даты, изображения, звуковые файлы и т. д. Каждый тип данных имеет определенный набор свойств. Самые распространенные типы данных таблицы — это текст, числа и даты.

     Текстовый столбец может содержать буквы, цифры, пробелы и специальные символы — все, что можно ввести с клавиатуры. Когда в текстовый столбец вводится число, оно также рассматривается как текст. Числа в текстовых столбцах невозможно складывать, усреднять или выполнять над ними какие-либо другие математические операции. В текстовые столбцы обычно помещаются числа, содержащие нецифровые символы — знаки математических операций, буквенные символы или пробелы. Широко известными примерами текстовых значений, содержащих большое количество чисел, являются телефонные номера, номера социального обеспечения и счетов. Если числовое значение никогда не предполагается использовать в математических операциях, оно является хорошим кандидатом на помещение в текстовый столбец.

     Существуют два основных типа текстовых столбцов: с фиксированной и переменной длиной. Для создания столбца фиксированной длины нужно указать в команде CREATE TABLE тип данных CHAR. Длина текста в столбцах CHAR всегда равна длине, указанной в объявлении столбца. Более короткие данные дополняются пробелами. Это может привести к бесполезной трате места, поэтому столбцы CHAR уместны только в тех случаях, когда все данные будут иметь одинаковую длину, как, например, обозначения пола или коды штатов.

     Большинство текстовых столбцов будут содержать данные переменной длины, и здесь следует использовать тип VARCHAR2. Если длина текста, который вы намереваетесь хранить в столбце, превышает 2000 символов (это максимум для типа VARCHAR2), можно воспользоваться типом LONG, который позволяет хранить до двух миллиардов символов в одном поле.

     Текстовое значение обычно называется строкой. В SQL-командах строки всегда заключаются в одиночные кавычки. Простой текст, содержащий только те символы, которые можно найти на клавиатуре (без символов форматирования, вставляемых текстовыми процессорами и электронными таблицами), часто называется ASCJI-текстом. (ASCII — это сокращение от American Standard Code for Information Interchange.) Стандарт ASCII предназначен для приведения информации к «общему знаменателю», чтобы обеспечить ее перенос между компьютерами.

     Для числовых столбцов в отличие от текстовых Oracle предлагает всего один базовый тип данных с названием NUMBER. При создании числового столбца вы просто указываете максимальное количество цифр, которое он может содержать, вместе с требуемым количеством цифр после запятой. Наибольшее число, которое можно хранить, составляет 999 999 999 999 999 999 999 999 999 999 999 999 990 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000.

     Для хранения даты и времени Oracle предоставляет тип данных DATE. Значения дат (которые, как и любой текст, заключаются в одиночные кавычки) при сохранении преобразуются в юлианские даты. Преобразование дат между визуальным форматом (например, ’08-MAY-2004′) и его юлианским эквивалентом производится автоматически. Мы просто вводим даты в привычном текстовом представлении, затем Oracle преобразует их в свой внутренний, юлианский, формат, а при выборке этих дат из таблицы они снова будут отображены в виде дней, месяцев и лет. Нам никогда не потребуется просматривать даты в юлианском формате.

     Тип данных DATE можно использовать для вычислений с датами. Например, чтобы получить дату, отстоящую на неделю от некоторой известной даты, нужно просто добавить 7 к этой известной дате. Oracle также проверяет достоверность дат; например, если кто-то попытается вставить дату 29 февраля 2002 г. в столбец с датами, то Oracle не позволит это сделать, поскольку 2002 г. не является високосным, а следовательно, не содержит 29 дней в феврале.

     Время хранится в виде десятичной дроби, показывающей, какая часть дня прошла к этому моменту. Например, если некоторому дню соответствует юлианская дата 33333, то время 18:00 этого дня будет сохранено как 33333.75 (.75
показывает, что к 18:00 прошло 75% дня).

     После изучения самых распространенных типов данных Oracle вы познакомились с тем, как использовать команду DESC для просмотра структуры существующей таблицы. Вы также узнали, что в команде CREATE TABLE можно указать спецификацию NOT NULL для любого столбца (или всех столбцов), в результате чего вставка или обновление записей будут возможны только при указании значений для этих столбцов.

     При добавлении или изменении данных в таблицах, столбцы которых допускают null-значения, можно избежать ввода значения в столбец, если указать NULL в том месте SQL-оператора, где должно находиться значение столбца. Пропустить столбцы в команде INSERT можно и другим способом — явно перечислить все заполняемые столбцы, не указывая те, в которые не предполагается вводить данные. Чтобы вставить данные с апострофами при помощи программы SQL*Plus, предварите команду INSERT командой SET SCAN OFF. Закончив вставку данных с апострофами, введите команду SET SCAN ON, что бы вернуть систему в нормальный режим.

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

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

     Если вы хотите сложить не числа, а текстовые строки (иначе говоря, выполнить конкатенацию двух текстовых столбцов), поместите между именами столбцов в операторе SELECT две вертикальные черты (||). Чтобы разделить две
части текста пробелом, поместите между именами столбцов комбинацию || ‘ ‘ ||. В результате этих действий заголовок столбца может оказаться слишком длинным и неудобным для чтения. В таком случае следует присвоить столбцу псевдоним, указав его после выражения, содержащего оператор конкатенации.

 

Примеры

1. Создадим таблицу table1:

CREATE TABLE table1
(
‘product_name’  VARCHAR2(25) NOT NULL,
‘product_price’ NUMBER(4,2) NOT NULL,
‘product_date’ DATE
);

2. Вставим данные в таблицу table1:

INSERT INTO table1 VALUES (‘ProductName1′, 1, ’5-NOV-2000′);
INSERT INTO table1 VALUES (‘ProductName2′, 2.5, ’29-JUN-2001′);
INSERT INTO table1 VALUES (‘ProductName3′, 50.75, ’10-DEC-2002′);
INSERT INTO table1 VALUES (‘ProductName4′, 1, NULL);

3. Выберем все поля (столбцы)  из таблицы table1:

SELECT * FROM table1;

4. Выберем некоторые поля из таблицы table:

SELECT product_name, product_price FROM table1;

5. Удалим таблицу table1:

DROP TABLE table1;