Видалення даних за допомогою оператора truncate table

Видалення даних оператором DELETE

Оператор DELETE видаляє з таблиці або відображення один або кілька рядків. У спрощеному вигляді синтаксис оператора DELETE виглядає так:

DELETE таблиця _або_ відображення

FROM вихідна_таблиця

WHERE умова_пошуку

Таблиця_або_відображення визначає ім'я таблиці або відображення, звідки потрібно видалити рядки. Видаляються всі рядки таблиці або відображення, які відповідають умові пошуку, заданій конструкцією WHERE. Якщо конструкцію WHERE не задано, то віддаляються всі рядки таблиці або відображення. Конструкція FROM задає додаткові таблиці, відображення або умови з'єднання, які можна використати в предикатах умови пошуку конструкції WHERE, що визначає рядки, що підлягають видаленню з таблиці або відображення. Рядки не видаляються з названої в конструкції FROM таблиці, а тільки з таблиці, названої в конструкції DELETE. Будь-яка таблиця, з якої вилучені всі рядки, залишається в базі даних. Дія оператора DELETE поширюється тільки на рядки таблиці. Самі таблиці видаляються з бази даних оператором DROP TABLE. Розглянемо наступний оператор DELETE:

USE Pubs

DELETE NewBooks

FROM Titles

WHERE NewBooks.BookTitle = Titles.Title

AND Titles.Royalty = 10

Цей оператор видаляє рядки з таблиці NewBooks, якщо гонорар за ці книги становить 10%. Сума гонорару залежить від значень стовпця Royalty таблиці Titles.

Видалення даних за допомогою API і курсорів

ADO, OLE DB і API ODBC підтримують видалення поточного рядка в результуючому наборі, отриманим додатком. Крім того, сценарії Transact-SQL, збережені процедури і тригери можуть використовувати для видалення рядка, на якій у цей час встановлений курсор, конструкцію WHERE CURRENT OF в операторі DELETE.

Оператор TRUNCATE TABLE - швидкий метод видалення з таблиці всіх рядків. Цей метод практично завжди працює швидше оператора DELETE, у якого не задані умови, оскільки DELETE протоколює видалення кожного рядка, a TRUNCATE TABLE - лише звільнення цілих сторінок даних. Оператор TRUNCATE TABLE негайно звільняє все місце, зайняте даними і індексами таблиці. Наступний оператор TRUNCATE TABLE видаляє всі рядки з таблиці NewBooks у базі даних Pubs:

USE Pubs

TRUNCATE TABLE NewBooks

Як і у випадку оператора DELETE, після застосування оператора TRUNCATE TABLE визначення таблиці (з її індексами й асоційованими об'єктами) залишається в базі даних. Для видалення визначення таблиці варто використати оператор DROP TABLE.

Практичні приклади модифікації даних у БД SQL Server

Нижче ми створимо таблицю в базі даних BookShopDB і заповнимо її даними. Далі модифікуємо додані в таблицю дані, а потім видалимо їх. Закінчивши цю операцію, ви видалите таблицю з бази даних.

Створення таблиці в базі даних BookShopDB

USE BookShopDB

CREATE TABLE Test1

(

RowID INT IDENTITY(1,1) NOT NULL,

Title VARCHAR(50) NOT NULL,

Type CHAR(12) NOT NULL DEFAULT ('Unknown'),

City VARCHAR(50) NULL,

Cost MONEY NULL

)

Цей оператор створює таблицю за назвою Test1, що складається з п'яти стовпців. Виконаємо оператор Transact-SQL.

Додавання до таблиці Test1 даних за допомогою оператора INSERT... VALUES

INSERT INTO Testl (Title, Type, Cost)

VALUES ('Test Title', 'business', 27,00)

Цей оператор вставляє рядок у таблицю Test1. У рядку вміщуються значення полів Title, Type і Cost. Виконаємо оператор Transact-SQL.

Напишемо оператор SELECT, що дозволить переглянути всі дані з таблиці Test1. Який оператор варто використати для цього? Виконаємо оператор SELECT. Звернемо увагу, що в таблиці є тільки один рядок - той, який ми додали за допомогою оператора INSERT. SQL Server автоматично згенерував значення поля RowID. Значення поля City - порожнє, оскільки воно не було визначено.

Додавання даних у таблицю Testl за допомогою оператора INSERT... SELECT

INSERT INTO Test1 (Title, Type, Cost)

SELECT Title, Type, Price

FROM Pubs.dbo.Titles

Цей оператор бере дані з таблиці Titles у базі даних Pubs і вставляє їх у таблицю Test1. Виконаємо оператор Transact-SQL. Скористаймося оператором SELECT для перегляду дані таблиці Test1. Зверніть увагу, що значення поля RowID згенеровані автоматично, а в поле City кожного рядка вміщується порожнє значення.

Модифікація даних за допомогою оператора UPDATE

UPDATE Test1

SET Cost = Cost * 2

WHERE Type = 'business'

Цей оператор у два рази збільшує значення поля Cost у порівнянні з вихідним значенням для книг типу business. Виконаємо оператор Transact-SQL. Скористаймося оператором SELECT для перегляду дані таблиці Test1. Зверніть увагу на подвоєне значення поля Cost для кожної книги типу business.

Видалення даних з таблиці Test1 за допомогою оператора DELETE

DELETE Test1

WHERE Title = 'Test Title'

Цей оператор видаляє всі рядки з таблиці, у стовпці Title якої зазначене значення Test Title. Виконаємо оператор Transact-SQL. Скористаймося оператором SELECT для перегляду дані таблиці Test1. Зверніть увагу, що рядок Test Title вилучений з таблиці.

Уведемо у вікні запитів наступний код на Transact-SQL:

DELETE Test1

Цей оператор видаляє всі рядки таблиці Test1. Виконаємо оператор Transact-SQL. Скористайтеся оператором SELECT для перегляду даних таблиці Test1. Зверніть увагу на відсутність даних у таблиці Test1.

Видалення з бази дані таблиці Test1 за допомогою оператора DROP TABLE

DROP TABLE Test1

Цей оператор видаляє таблицю Test1 з бази даних BookShopDB. Виконаємо оператор Transact-SQL. Якими засобами можна перевірити, що таблицю Test1 вилучено з бази даних?

Тема 6. Збережені процедури

Збережені процедури підвищують продуктивність і безпеку баз даних, а також розширюють мову Transact-SQL можливостями, недоступними без використання цих об'єктів баз даних.

1. Основні відомості про збережені процедури

Підвищується продуктивність - за рахунок локального (стосовно бази даних) зберігання, перекомпіляції вихідного тексту і хешування. Можливості програмування розширюються завдяки застосуванню таких розповсюджених засобів програмування, як використання вхідних і вихідних параметрів, а також завдяки багаторазовому використанню процедур. Функції безпеки мають на увазі шифрування тексту процедури і обмеження привілеїв. У результаті користувачі одержують обмежений доступ до внутрішньої структури бази даних, однак їм дозволено запускати збережені процедури, що виконують різні дії над базою даних.

Продуктивність

При пересиланні кожної команди (або пакета команд) Transact-SQL на сервер для обробки останній повинен визначити, чи є у відправника права на виконання цих команд і чи припустимі самі команди. Перевіривши права доступу і синтаксис команд, SQL Server будує план виконання запиту.

Збережені процедури в цьому випадку більше ефективні. При створенні вони зберігаються в SQL Server, тому при виклику збереженої процедури її вміст відразу ж обробляється сервером. Єдиний оператор дозволяє викликати складний сценарій Transact-SQL, що втримується в збереженій процедурі, який дозволяє уникнути пересилання через мережу сотень команд.

Перед створенням збереженої процедури її команди проходять синтаксичну перевірку. Якщо при цьому не виявлено жодної помилки, ім'я процедури зберігається в таблиці SysObjects, а її текст - у таблиці SysComments. При першому запуску збереженої процедури створюється план виконання і збережена процедура компілюється. Надалі її обробка здійснюється швидше, оскільки SQL Server не доводиться перевіряти синтаксис команд, створювати план виконання і компілювати текст процедури. До створення нового плану в хеші перевіряється наявність існуючого плану виконання.

Відносний приріст продуктивності, за рахунок розміщення планів виконання збережених процедур у хеші процедур, зменшується. При виконанні оператора Transact-SQL по можливості використовує існуючий план виконання.

Можливості програмування

Створену збережену процедуру можна викликати в будь-який момент, при виникненні найменшої необхідності. Це забезпечує модульність і стимулює повторне використання коду. Останнє полегшує супровід бази даних, тому що вона ізольована від бізнесі-правил, які скасовуються. Модифікувати збережену процедуру відповідно до нових правил можна в будь-який момент. Після цього всі додатки, що використають її, автоматично прийдуть у відповідність із новими бізнес-правилами без безпосередньої модифікації.

Подібно програмам, написаним на інших мовах програмування, збережені процедури здатні приймати вхідні параметри, повертати значення вихідних параметрів, підтримувати зворотний зв'язок з користувачем за допомогою виводу кодів стану й текстових повідомлень, а також викликати інші процедури. Наприклад, одна збережена процедура може повертати інший код стану, залежно від якого остання виконує ті або інші дії.

Якщо розроблювачам зручно писати складні програми на таких мовах, як C++, то потім ці програми можна викликати з SQL Server засобами збережених процедур особливого типу, які називаються розширеними збереженими процедурами.

Збережену процедуру пишуть для рішення якої-небудь одного завдання - у результаті її можна використати в декількох базах даних. Наприклад, збережена процедура sp_rename призначена для зміни імен створених користувачем об'єктів (наприклад, таблиці, поля або користувальницького типу даних) у поточній базі даних. В одній базі даних її використовують для перейменування таблиці, в іншій - стовпця таблиці й т.д.

Дата добавления: 2015-06-29