alan_beaulieu-learning_sql-ru (865932), страница 40

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 40)

Поскольку для некоторых типов счетов,таких как ссуды коммерческим предприятиям, общий остаток можетравняться нулю, если на текущий момент все ссуды полностью выплачены, лучше всего включить выражение case, гарантирующее, чтознаменатель никогда не будет равен нулю.Условные обновленияПри обновлении строк таблицы вам иногда придется принимать решения относительно того, какие значения должны быть заданы в определенных столбцах. Например, после вставки новой транзакции должны измениться столбцы avail_balance, pending_balance и last_activity_date таблицы account. С обновлением двух последних столбцов проблем нет, но чтобы правильно изменить столбец avail_balance, надопроверить столбец funds_avail_date таблицы transaction и выяснить,сразу ли доступны фонды транзакции.

Допустим, только что вставлена транзакция с ID 999, тогда изменить три столбца таблицы accountможно с помощью следующего выражения update:1 UPDATE account2SET last_activity_date = CURRENT_TIMESTAMP(),3pending_balance = pending_balance +4(SELECT t.amount *5CASE t.txn_type_cd WHEN 'DBT' THEN 1 ELSE 1 END6FROM transaction t7WHERE t.txn_id = 999),8avail_balance = avail_balance +9(SELECT10CASE11WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 012ELSE t.amount *13CASE t.txn_type_cd WHEN 'DBT' THEN 1 ELSE 1 END14END15FROM transaction t16WHERE t.txn_id = 999)228Глава 11.

Условная логика17 WHERE account.account_id =18 (SELECT t.account_id19FROM transaction t20WHERE t.txn_id = 999);Здесь всего три выражения case: два из них (строки 5 и 13) служат дляизменения знака суммы транзакции для дебетовых транзакций,а третье выражение case (строка 10) предназначено для проверки датыдоступности фондов. Если эта дата еще не наступила, к доступному остатку добавляется нуль; в противном случае добавляется сумма транзакции.Обработка значений NullХотя значения null удобны для хранения в таблицах неизвестных значений столбцов, они не всегда подходят для отображения или использования в выражениях. Например, в окне ввода данных вы, скореевсего, предпочтете отображать слово «unknown», а не оставлять пустоеполе.

При извлечении данных выражение case позволяет вместо значения null подставлять строку:SELECT emp_id, fname, lname,CASEWHEN title IS NULL THEN 'Unknown'ELSE titleENDFROM employee;Значения null в вычислениях часто являются причиной результатаnull, как показывает следующий пример:mysql> SELECT (7 * 5) / ((3 + 14) * null);++| (7 * 5) / ((3 + 14) * null) |++|NULL |++1 row in set (0.08 sec)Проводя вычисления, полезно преобразовать значения null в число(обычно 0 или 1) с помощью выражения case, чтобы обеспечить результат вычисления, отличный от null. Например, при вычислении с участием столбца account.avail_balance можно было бы подставить 0 (присложении или вычитании) или 1 (при умножении или делении) для техсчетов, которые уже открыты, но средства на них еще не помещены:SELECT <some calcuation> +CASEWHEN avail_balance IS NULL THEN 0ELSE avail_balanceEND+ <rest of calculation>...Упражнения229Если числовой столбец может содержать значения null, хорошо бы использовать условную логику при любых вычислениях с участием этого столбца, чтобы гарантировать значимые результаты.УпражненияПроверьте свою способность применять условную логику с помощьюследующих примеров.

Выполнив задание, сравните свои решения с ответами, приведенными в приложении С.11.1Перепишите следующий запрос, использующий простое выражениеcase, таким образом, чтобы получить аналогичные результаты с помощью выражения case с перебором вариантов. Попытайтесь свестик минимуму количество блоков when.SELECT emp_id,CASE titleWHEN 'President' THEN 'Management'WHEN 'Vice President' THEN 'Management'WHEN 'Treasurer' THEN 'Management'WHEN 'Loan Manager' THEN 'Management'WHEN 'Operations Manager' THEN 'Operations'WHEN 'Head Teller' THEN 'Operations'WHEN 'Teller' THEN 'Operations'ELSE 'Unknown'ENDFROM employee;11.2Перепишите следующий запрос так, чтобы результирующий набор содержал всего одну строку и четыре столбца (по одному для каждого отделения).

Назовите столбцы branch_1, branch_2 и т. д.mysql> SELECT open_branch_id, COUNT(*)> FROM account> GROUP BY open_branch_id;+++| open_branch_id | COUNT(*) |+++|1 |8 ||2 |7 ||3 |3 ||4 |6 |+++4 rows in set (0.00 sec)ТранзакцииДо сих пор все примеры в данной книге были примерами одиночныхSQLвыражений. В этой главе рассматриваются требования и среда, необходимые для совместного выполнения нескольких SQLвыражений.Многопользовательские базы данныхСистемы управления базами данных разрешают обращаться к данными изменять их не только одному пользователю, но и нескольким одновременно. Если каждый пользователь выполняет запросы, как этопроисходит с хранилищем данных в течение обычных рабочих часов,для сервера БД это не создает больших проблем. Однако если некоторые пользователи добавляют и/или изменяют данные, серверу приходится сохранять довольно много промежуточных результатов.К примеру, создается отчет, представляющий доступный остаток всехтекущих счетов, открытых в отделении.

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

Так, какие цифры должны появиться в отчете? Ответ отчасти зависит от того, как сервер реализовывает блокировку (locking) –механизм управления одновременным использованием ресурсов данных. Большинство серверов БД применяют одну из двух стратегийблокировки:Многопользовательские базы данных231•Пользователи, осуществляющие запись в БД, должны запрашиватьи получать от сервера блокировку записи (write lock) для измененияданных.

А пользователи, считывающие данные из БД, должны запрашивать и получать от сервера блокировку чтения (read lock)для осуществления запросов к данным. В то время как чтение может осуществляться одновременно несколькими пользователями,для каждой таблицы (или ее части) одновременно выдается толькоодна блокировка записи, и запросы на чтение блокируются до техпор, пока не будет снята блокировка записи.•Пользователи, осуществляющие запись в БД, для изменения данных должны запрашивать и получать от сервера блокировку записи, но пользователи, считывающие данные, для запроса данных ненуждаются ни в каком типе блокировки. Вместо этого сервер гарантирует, что читатель видит непротиворечивое представление данных (данные представляются неизменными, даже несмотря на то,что другие пользователи могут их модифицировать), начиная с момента начала запроса до его завершения.

Этот подход известен какконтроль версий (versioning).У обеих стратегий есть свои достоинства и недостатки. При первом подходе время ожидания может оказаться длительным, если одновременно поступило много запросов на чтение и запись. Второй подход можетсоздать проблемы в случае длительных запросов, поскольку происходит изменение данных. В данной книге обсуждаются три сервера: Microsoft SQL Server использует первый подход, Oracle Database – второй, а MySQL – оба подхода (в зависимости от выбранного пользователем механизма хранения (storage engine), который обсуждается немного позже).Также есть ряд различных стратегий блокировки ресурса. Блокирование может выполняться на одном из трех разных уровней, или с однойиз трех детализаций (granularities):Блокирование таблицыПредотвращает одновременное изменение несколькими пользователями данных одной таблицы.Блокирование страницыПредотвращает одновременное изменение несколькими пользователями данных одной страницы таблицы (страница – сегмент памяти, обычно от 2 до 16 Кбайт).Блокирование строкиПредотвращает одновременное изменение несколькими пользователями одной строки таблицы.У этих подходов тоже есть свои плюсы и минусы.

При блокировке всейтаблицы возникает очень мало промежуточных результатов, но по мерероста числа пользователей такая блокировка очень быстро приводит232Глава 12. Транзакциик недопустимым временам ожидания. С другой стороны, в случае блокировки строки сохраняется намного больше промежуточных результатов, но такая блокировка позволяет многим пользователям вноситьизменения в одну таблицу, если это касается разных строк.

Из трехсерверов, обсуждаемых в этой книге, Microsoft SQL Server используетблокировки страницы и строки, Oracle Database – блокировку строки,а MySQL может блокировать таблицу, страницу или строку (опять жев зависимости от выбранного механизма хранения).Возвращаясь к отчету: данные, появляющиеся на его страницах, будутотражать состояние БД или на момент начала создания отчета (еслисервер использует контроль версий), или на момент осуществления сервером блокировки чтения (если сервер использует блокировки и чтения, и записи).Что такое транзакция?Если бы серверы БД работали безостановочно, если бы пользователи всегда позволяли программам завершать выполнение и если бы приложения всегда завершались без неустранимых ошибок, прерывающих выполнение, то незачем было бы обсуждать параллельный доступ к базамданных.

Однако ни на одну из перечисленных ситуаций рассчитыватьнельзя. Следовательно, чтобы несколько пользователей могли осуществлять доступ к одним и тем же данным, необходим еще один элемент.Этой дополнительной деталью пазла параллелизма является транзакция (transaction) – механизм группировки нескольких SQLвыражений, позволяющий успешно выполниться всем или ни одному из них.Если клиент пытается перевести 500 долларов со сберегательного счетана текущий, он немного расстроится, если деньги будут успешно снятыс первого счета, но не внесены на второй. Какой бы ни была причинасбоя (сервер был выключен для проведения работ по техническому обслуживанию, истекло время ожидания запроса на блокировку страницы таблицы account и др.), клиент захочет вернуть свои 500 долларов.Чтобы защититься от ошибок такого рода, программа, обрабатывающая запрос на перевод, сначала начинает транзакцию, затем выполняет SQLвыражения, необходимые для перемещения денег со сберегательного счета на текущий, и, если все проходит успешно, завершаеттранзакцию, формируя команду commit (фиксировать).

Однако еслипроисходит чтото непредвиденное, программа выдает команду rollback(откат), которая указывает серверу отменить все изменения, внесенныес момента начала транзакции. Весь процесс может выглядеть так:START TRANSACTION;/* Снять деньги с первого счета, обеспечив достаточный остаток */UPDATE account SET avail_balance = avail_balance 500WHERE account_id = 9988AND avail_balance > 500;Что такое транзакция?233IF <Предыдущим выражением была изменена ровно одна строка> THEN/* Внести деньги на следующий счет */UPDATE account SET avail_balance = avail_balance + 500WHERE account_id = 9989;IF <Предыдущим выражением была изменена ровно одна строка> THEN/* Все получилось, сделать изменения постоянными */COMMIT;ELSE/* Чтото не так, отменить все изменения, сделанные в данной транзакции */ROLLBACK;END IF;ELSE/* Недостаток средств на счете или при обновлении возникла ошибка */ROLLBACK;END IF;Хотя предыдущий фрагмент кода может показаться похожимна один из процедурных языков программирования, предоставляемых основными компаниямипроизводителями БД, такимикак PL/SQL от Oracle или Transact SQL от Microsoft, он написанна псевдокоде и не пытается имитировать ни один конкретныйязык.Приведенный выше фрагмент кода начинается с запуска транзакции.После этого делается попытка удалить 500 долларов с текущего счетаи затем добавить 500 долларов на сберегательный счет.

Характеристики

Список файлов ответов (шпаргалок)