Создание простой таблицы
Таблицы могут храниться в базе данных несколькими способами. Простейший из них это heap таблица. Таблица может хранить строки различной длины в случайном порядке. Может быть какая-либо корреляция между порядком записи и порядком в котором данные хранятся но на это нельзя рассчитывать. Более сложные структуры таблицы, такие как представленные ниже, могут влиять на порядок и группировку строк или использовать случайное распределение
Использование сложных структур таблицы не вляет на SQL. Любой SQL запрос выполненные к таблицам созданным с этими опциями вернёт абсолютно одинаковый результат такой же как будто таблица являются обычными heap таблицами. Но если программист понимает как они работаеют то можно существенно увеличить производительность.
Создание таблицы с указанием столбцов
Для создания таблицы используется следующий синтаксис
CREATE TABLE [schema.]tablename [ORGANIZATION HEAP]
(columnname datatype [DEFAULT expression]
[,columnname datatype [DEFAULT expression]);
Как минимум необходимо указать имя таблицы (по умолчанию таблицы создаются в схеме текущего аккаунта, если вы не указываете другую) и один столбец с типом данных. Практически никто не указывает явно ORGANIZATION HEAP, так как это значение по умолчанию и стандарт SQL. Ключевое слово DEFAULT в определении столбца позволяет указать выражение которое будет использоваться при выполнении команды INSERT если не указано значение для этого столбца.
CREATE TABLE SCOTT.EMP
HIREDATE DATE DEFAULT TRUNC(SYSDATE),
COMM NUMBER(7,2) DEFAULT 0.03);
Создаем таблицу EMP в схеме SCOTT. Этот запрос выполнится если подключен аккаунт SCOTT (и тогда указание схемы необязательно) или другой пользователь у которого есть доступ на создание таблиц в схеме SCOTT. Столбцы
EMPNO число длиной 4 цифры без дробной части. Если будет указана дробная часть при выполнении команды INSERT значение будет округлено к ближайшему целому.
ENAME строка с максимальной длиной в 10 символов
HIREDATE возможно использовать значение любой даты с указанием времени, если время не указано используется значение полночь
SAL предназначен для хранения зарплаты, принимает число длиной 7 цифр. Цифры после семи будут обрезаны а значение округлено
COMM имеет значение по умолчанию 0.03, которое будет использоваться если явно не указанть значение при выполнении команды INSERT
После создания таблицы можно записывать данные и выполнять команду SELECT
Обратите внимание что столбцы не указанные при команде INSERT использовали значения указанные в DEFAULT. Если бы не были указаны значения по умолчанию в секции DEFAULT использовалось бы значение NULL. Также обратите внимание что значение SAL было округлено.
The DEFAULT clause can be useful, but it is of limited functionality. You cannot use a subquery to generate the default value: you can only specify literal values or functions.
Создание таблиц с использованием подзапросов
Вместо создания таблиц с нуля и затем вставки строк (как мы только что рассмотрели) таблицы можно создавать используя другие таблицы – с помозью подзапросов. Этот метод позволяет определить таблицу и записать данные используя один запрос. Любой запрос может быть использован как источник для описания столбцов и как набор данных (строк). Синтаксис команды
CREATE TABLE [schema.]tablename AS subquery;
Любой запрос возвращает двумерный набор строк; результат хранится как новая таблица. Просто пример создания таблицы используя подзапрос
create table employees_copy as select * from employees;
Данный запрос создаст таблицу EMPLOYEES_COPY, которая будет полной копией таблицы EMPLOYEES, идентичной как по структуре столбцов и так и хранящихся строк. Все проверки на обязательность значения и значения по умолчанию также применяются к новой таблице, но первичные ключи, уникальные или внешние ключи не создаются (ограничения constraints обсудим далее), потому что эти три типа ограничений требуют создания индексов которые могут быть не нужными пользователю.
Рассмотрим более сложный пример
create table emp_dept as select
last_name ename,department_name dname,round(sysdate — hire_date) service
from employees natural join departments order by dname,ename;
Строки в новой таблице это результат объекдинения двух таблиц-источников, и два столбца изменяют название. Новый столбец SERVICE будет заполнен результатом арифметической операции и получено количество дней с момента приёма на работу. Строки буду вставлены в определённом порядке. Этот порядок не будет управляться последующими DML командами, но предполагая что в схеме HR данные по умолчанию новая таблица будет выглядеть следующим образом
Подзапрос конечно может включать секцию WHERE для ограничего строк для добавления в новую таблицу. Для создания таблицы без строк помжно использовать условие WHERE которое никогда не выполняется.
create table no_emps as select * from employees where 1=2;
Изменение таблицы после создания
Доступно много изменений которые возможны после создания таблицы. Изменения которые затрагивают физическую структуру ложатся на плечи администратора БД, но большинство изменения являются логическими и могут быть применены SQL разработчиками. Раасмотрим примеры (большинтсво из них говорят сами за себя)
- Добавление столбца
alter table emp add (job_id number);
- Изменение столбца
alter table emp modify (commission_pct number(4,2) default 0.05);
- Удаление столбца
alter table emp drop column commission_pct;
- Маркировка столбца как неиспользуемого
alter table emp set unused column job_id;
- Переименование столбца
alter table emp rename column hire_date to recruited;
- Включение режима только-чтения для таблицы
alter table emp read only;
Все изменения этих DDL команд содержат встроенный COMMIT. Эти изменения нельзя отменить и они не смогут отработать если имеется активная транзакция к таблице. Также эти операции происходят практически мгновенно за исключением удаления столбца. Удаление столбца может затребовать длительное время так как поскольку удаляется столбец, каждая строка должна быть изменена. Команда SET UNUSED делает столбец недоступным для SQL команд, и часто является более лучшей альтернативой в сочетании с командой
ALTER TABLE tablename DROP UNUSED COLUMNS;
которая удалит все неиспользуемы столбцы за один проход таблицы.
После перевода таблицы в режим только-чтения все DML команды не выполнятся. Но таблица может быть удалена, так как удаление всего лишь удалит строки из словаря данных.
Удаление и сокращение таблицы (drop и truncate)
Команда TRUNCATE TABLE удаляет вссе строки из таблицы и оставляет определение таблицы. Команда DROP TABLE удаляет также и определение таблицы (саму таблицу вместе с данными). Синтаксис команды
DROP TABLE [schema.]tablename ;
Если схема не указана удалится таблица с этим именем в схеме текущего аккаунта.
SQL не выдает предупреждений перед выполнением команды DROP и как и любая команда DDL содержит встроенный COMMIT. То есть удаление таблицы нельзя отменить. При выполнении определенных условий, удаление может быть отменено используя определенные методики.
У команды DROP есть несколько ограничений: команда не выполнится если существует транзакция к этой таблице, или если данные этой таблицы используются как внешний ключ другой таблица, тогда для удаления исходной таблица необходимо вначале удалить ссылающуюся таблица или внешний ключ.
- Создание и использование временных таблиц
- Синонимы
- Создание и управление профилями
- Создание и управление ролями
- Создание и управление пользовательскими аккаунтами
Создание Новой Таблицы: Пример
В диалоговом окне Create Table, если Вы не устанавливаете флажок Advanced, можно быстро создать таблицу, определяя столбцы и некоторые часто используемые функции.
Если Вы устанавливаете флажок Advanced, диалоговое окно Create Table изменяется — добавляются дополнительные опции используя которые можно определить расширенный набор функций при создании таблицы.
Пример на рисунке показывает, как создать новую таблицу DEPENDENTS , устанавливая флажок Advanced.
Чтобы составить новую таблицу, выполните следующие шаги:
- В Навигаторе Соединений щелкните правой кнопкой по Tables.
- Выберите Create Table.
- В диалоговом окне Create Table выберите Advanced.
- Укажите информацию о столбце.
- Нажмите OK.
Хотя это не обязательно, следует также определить первичный ключ при использовании вкладки Primary Key в диалоговом окне. Можно отредактировать таблицу, которую Вы создали; чтобы сделать это, щелкните правой кнопкой по таблице в Навигаторе Соединений и выберите Edit.
Далее: Команда SERVEROUTPUT
Post Views: 457
Похожие записи
Предложение WITH
Используя предложение WITH, можно определить блок запроса до его применения в запросе. Предложение WITH (формально называется subquery_factoring_clause) позволяет многократно использовать один и тот же блок запроса в инструкции SELECT, когда она встречается более одного раза в сложном запросе. Это особенно. Читать далее
Оператор WITH в sql
Оператор WITH в SQL — это чрезвычайно полезный инструмент для создания временных таблиц и использования их внутри других запросов. Это позволяет упростить код и улучшить производительность запросов. Оператор WITH (также известный как Common Table Expression) используется для создания временных таблиц. Читать далее
Выполнение запросов внешних таблиц
Внешняя таблица не описывает никаких данных, которые хранятся в базе данных. Внешняя таблица не описывает порядок хранения данных во внешнем источнике. Вместо этого она описывает, как уровень внешней таблицы должен представлять данные для сервера. За преобразования, которые требуется выполнять над. Читать далее
Инструкция FLASHBACK TABLE
Позволяет восстанавливать таблицы до состояния на заданный момент времени с помощью одной инструкции. Восстанавливает табличные данные вместе со связанными индексами и ограничениями. Позволяет возвращать таблицу и ее содержимое в состояние, существовавшее на определенный момент времени, или к изменению системы, определенному. Читать далее
Создание внешней таблицы
Внешние таблицы создаются с помощью предложения ORGANIZATION EXTERNAL инструкции CREATE TABLE. В действительности таблица не создается. Точнее, создаются метаданные в словаре данных, который можно использовать для доступа к внешним данным. Предложение ORGANIZATION применяется для указания порядка, в котором сохраняются строки. Читать далее
Инструкция DROP TABLE … PURGE
В базе данных Oracle имеется функция для удаления таблиц. При удалении таблицы база данных не сразу освобождает пространство, занимаемое таблицей. Точнее, база данных переименовывает таблицу и помещает ее в корзину, где таблица позже может быть восстановлена с помощью инструкции FLASHBACK. Читать далее
Создание внешней таблицы с помощью драйвера доступа ORACLE_LOADER
Рассмотрим, как создаются внешние таблицы посредством драйвера доступа ORACLE_LOADER. Предположим, что существует текстовый файл, в котором имеются записи в следующем формате: 10,jones,11-Dec-1934 20,smith,12-Jun-1972 Записи разделяются символом новой строки, и все поля заканчиваются запятой ( , ). Имя файла: /emp_dir/emp.dat. Читать далее
Пример создание внешней таблицы с помощью драйвера доступа ORACLE_DATAPUMP
Используя драйвер доступа ORACLE_DATAPUMP, можно выполнять с внешними таблицами операции выгрузки и повторной загрузки. Примечание. В контексте внешних таблиц загрузка данных обозначает операцию чтения данных из внешней таблицы и их загрузку в таблицу базы данных. Под выгрузкой данных понимается чтение. Читать далее
Установка Предпочтений SQL Developer
Можно настроить много аспектов интерфейса и среды SQL Developer, изменяя предпочтения SQL Developer согласно Вашим потребностям. Чтобы изменить предпочтения SQL Developer, выберите Tools, а затем Preferences. Настройте интерфейс SQL Developer и среду. В меню Tools выберите Preferences. Предпочтения группируется в. Читать далее
Резюме по SQL Developer
В этой рубрике было рассмотрено использование SQL Developer, чтобы выполнять следующие задачи: Просматривать, создавать и редактировать объекты базы данных Выполнять SQL-операторы и сценарии на Рабочем листе SQL Создавать и сохранять пользовательские отчеты SQL Developer является бесплатным графическим инструментом, позволяющим упростить. Читать далее
Добавление таблицы в набор данных с помощью SQL-инструкции
Можно написать SQL-инструкцию SELECT с учетом источника, чтобы запросить данные подключения и создать таблицу в наборе данных. Этот вариант запроса вручную доступен для подключений большинства типов.
Используйте данный вариант запроса вручную для создания таблиц, если вы хотите использовать в наборе данных не таблицы подключения, а определенную информацию. Или используйте этот вариант запроса вручную, если подключение источника данных не предоставляет список схем. Используемая SQL-инструкция определяется типом подключения источника данных.
- На главной странице нажмите Навигатор и выберите Данные .
- Выберите вкладку Наборы данных .
- Найдите набор данных, который требуется открыть, откройте меню Действия и выберите команду Открыть .
Убедитесь в том, что набор данных, основанный на таблицах баз данных, открыт. Для их отображения используется значок базы данных .
.png
Как создать таблицу в Oracle, если её еще нет?
@jmu, это ничего (в многопользовательской среде) не меняет. Сейчас таблицы нет, а через миллисекунду она уже есть.
8 июн 2012 в 15:01
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Зависит от того какие действия необходимо предпринимать если таблица уже есть. Если эти действия лежат в рамках СУБД то смысла и реализовывать клиентским ексепшенном — нет, если же действия должен предпринять клиент или же СУБД используется исключительно как хранилище данных то бизнес логику лучше проверять на клиенте, но я не думаю что это случай оракла — использовать его как просто хранилище — идиотизм.
Но в любом случае чтобы проверить есть ли таблица или нет можно например таким запросом:
select count(*) from ALL_TABLES where table_name='table_name' В случае с ораклом наверное правильнее будет написать код на pl/sql который будет отлавливать эксепшен оракла о том что таблица уже есть.
P.S. В оракле действительно нету конструкции «if table not exists», видимо чтобы продвигать объектно ориентированный подход.
P.S.S. хотя если ваш java код является встроенным в СУБД (сам является частью базы) то такой подход правильный.
Отслеживать
ответ дан 8 июн 2012 в 14:50
9,053 1 1 золотой знак 17 17 серебряных знаков 40 40 бронзовых знаков
@Чад count(*) — зло! Здесь как раз подошёл бы select с exception. 🙂
26 июн 2012 в 19:45
А чем зло то?
27 июн 2012 в 8:20
Тем, что всю таблицу сканит. count нужен там, где нужен. Ну или в sqlplus.
20 июл 2012 в 3:56
@orkaan, в оракле сканит всю таблицу. @renegator, разве в оракле это есть? «IF NOT EXISTS»
20 июл 2012 в 6:32
@Чад, согласитесь в Java реакцию на exeption в любом случае надо делать. Тогда зачем нужен select (пусть даже быстрый) и проверка результата (а ведь exception тоже надо обработать)? Кода больше, выполняется дольше. Просто Вам с проверкой select кажется более логично ?
20 июл 2012 в 7:31
TL;DR Нормально таблицы и многие другие БД объекты постоянны, то есть создаются один раз и больше не удаляются и не пересоздаются, тем более из клиентского кода.
Возможно поэтому Oracle пока не спешит реализовать такие фичи как: предикат IF NOT EXISTS или статический DDL в PL/SQL. Процитирую @ConnorMcDonald на Спроси Тома:
DDL is a rare event in Oracle (unlike some other databases). If you need to run lots of DDL from PL/SQL, I’d be interested in seeing the justification for that.
В исключительных случаях, можно создать процедуру, например:
create or replace procedure createTableIfNotExists (clauses varchar2) is alreadyExists exception; pragma exception_init (alreadyExists, -955); begin execute immediate 'create table ' || clauses; exception when alreadyExists then null; end createTableIfNotExists; / SQL> exec createTableIfNotExists ('testtab (id number, memo varchar (32))') PL/SQL procedure successfully completed. SQL> desc testtab Name Null Type ---- ---- ------------ ID NUMBER MEMO VARCHAR2(32) Не следует забывать, что вызов DDL произведёт неявный commit текущей транзакции, если таковая имеется.