Oracle - MS Server / BD
.pdfСхема данных – логическая область в БД для группировки объектов БД под одним именем, т.е. образуют пространство имен.
GRANT SELECT
ON SCHEMA stockSchema TO Ivan
Каждая БД после создания имеет как минимум одну схему – автоматически создаваемую – поумолчаниюdbo
CREATE SCHEMA stockSchema;
ALTER USER Ivan WITH DEFAULT_SCHEMA=stockShema;
Изменение/удаление
ALTERSCHEMA
DROPSCHEMA (если нет объектов)
17. Особенности оператора SELECT (язык T-SQL).
SELECT *, столбцы и выражения [FROMтаблица]
[JOINтаблица ONусловие]
[WHERE условие] [GROUP BYстолбцы] [HAVING условие] [ORDERBY столбцы]
Источники данных.
таблицы БД
вложенный запрос (подзапросы)
общие табличные выражения
представления
пользовательские функции, возвращающие таблицы
распределенные источники данных
источник данных XML
Если имя таблицы содержит пробел или совпадает с ключевым словом – используем[]. Для ссылки на объект используется 4-уровневая иерархия. server.database.schema.object
Задать текущую БД: USEимя_бд
LIKE
1,2. _-задает символ % - любая последовательность символов
‘a’ LIKE ‘[a-g]’
18. Основные формы подзапросов: простой подзапрос, связанный (коррелированный) подзапрос, общее табличное выражение (CTE).
Простой подзапрос.
-Подзапрос на месте выражения. … WHERESumD=(SELECT…)
-Подзапрос на месте списка.
… WHERE NOT IN (SELECT…)
-Наместетаблицы.
…FROM (SELECT…)
Обязательно указываем псевдоним таблицы.
1.выполняется 1 раз.
2.результаты передаются внешнему запросу.
3.внешний запрос выполняется 1 раз.
Связанный (коррелированный) подзапрос – выполняется многократно.
SELECT Nom, FIO, Adr
FROM dbo.Person as P
WHERE NOT EXISTS (SELECT * FROM dbo.TPhone AS T
WHERE P.Adr=T.Adr);
1.внешний запрос выполняется 1 раз.
2.подзапрос выполняется по 1 разу для каждой строки внешнего запроса, используя значения соотв. столбцов для текущей строки.
3.результаты подзапроса определяют данные, включаемые в таблицу результатов внешнего запроса.
Общее табличное выражение (СТЕ).
Описывает временное представление, которое будет использовано в запросе как обычное представление. Задается в WITH, которое содержит имя, псевдонимы столбцов и подзапрос.
После этого основной запрос может обратиться к подзапросу как к источнику данных.
WITHCTEName (список псевдонимов столбцов)
AS простойподзапрос
SELECT …
FROM CTEName;
WITH Inform (Id,Qty)
AS (SELECT Id, Count(Nom) AS Quantity
FROM dbo.Have_D
GROUP BY ID)
SELECT Id,Qty
FROM Inform
WHERE Qty=(SELECT MAX(Qty) FROM Inform)
Ограничения CTE.
1.Не могут быть вложены друг в друга.
2.Не могут ссылаться на основной запрос, поскольку построены на базе простых запросов.
19. Особенности операторов вставки, обновления и удаления данных (язык T-SQL).
Особенности вставки данных.
INSERT/VALUES Вставить 1 строкузначений. Обычно используется для ввода данных пользователей.
INSERT/SELECT |
Вставить в таблицу результирующий набор данных вложенного |
|
запроса. |
INSERT/EXEC |
Вставить результат хранимой процедуры |
INSERT DEFAULT |
Создать новую строку, в которой все значения по умолчанию |
SELECT INTO |
Создать новую таблицу из результатов данной операции SELECT |
1.INSERT [INTO] схема.таблица
[(стоблцы)]VALUES [(значения)]
2.INSERT [INTO] схема.таблица
SELECT …
3.INSERT [INTO] схема.таблица [столбцы] EXECхранимая_процедура
4.INSERT [INTO] схема.таблицаDEFAULT VALUE
5.SELECTстолбцы
INTOновая_таблица FROM источник [WHERE условие] и т.д.
Особенности обновления данных. UPDATEсхема.таблица
SET столбец=значение/выражение/столбец [FROMисточник_данных] [WHEREусловие_поиска]
Особенности удаления данных
DELETE [FROM] схема.таблица
[FROM источник данных] [WHERE условие поиска]
20. Возвращение и сохранение данных, модифицированных операторами вставки, обновления и удаления данных (язык T-SQL).
Предложение OUTPUTв операцияхDELETE, INSERT, UPDATEпозволяет получить доступ к виртуальным таблицам вставки и удаления.
DELETED – таблица со стертыми данными
INSERTED – таблица со вставленными и обновленными данными.
OUTPUTспособно извлечь все или избранные столбцы.
INSERT INTO dbo.Have_D (Nom, Id, Comment)
OUTPUT Inserted
VALUES (1,9,’Student’)
UPDATE dbo.Have_D
SET Comment=’Student3’
OUTPUT DELETED.Comment as OldCom
OUTPUT INSERTED.Comment as NewCom
WHERE Nom=1 and Id=9
DELETE FROM dbo.Have_D
OUTPUT Deleted.Nom, Deleted.Id
WHEREComment=’Student3’
Сохранение возвращенных данных в табличной переменной (INTO) Объявление табличной переменной
DECLARE @DeletedDateTABLE
(Nom INT, Id INT, Comment VARCHAR(50));
Использование табличной переменной.
DELETEFROMdbo.Have_D OUTPUT Deleted.*
INTO @DeletedData
WHERE Comment=’Student3’;
SELECTFROM @DeletedData
21. Основы программирования на языке T-SQL: запрос, пакет, переменные (значения по умолчанию и область определения, использование переменных в SQL-запросах).
Язык T-SQL – для управления наборами данных.
Запрос – любой оператор SQL.
Пакет – последовательность операторов.
Разделитель пакета – GO.
Терминатор (GO – по умолчанию).
Операторы DDL – первые в пакете.
DDL – Data Definition Language – языкописанияданных.
Переключение между БД при помощи USE.
Выполнение пакетов.
Окончание оператора – (;)или начало следующего.
Комментарии – (--) или (/*…*/)
Переменные.
DECLARE @имя_переменнойтип_переменной.
Область определения переменных распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат значения NULL.
DECLARE @Test INT, @TestTwoNVARCHAR(50);
SELECT @Test, @TestTwo;
SET @Test=1;
SET @TestTwo=’FFFF’;
SELECT @Test, @TestTwo;
GO
SELECT @Test, @TestTwo – ошибка
Отладка пакетов.
Когда обнаруживается ошибка, сообщение информирует о ее характере и номере строки. Дважды щелкнув мы перейдем на строку.
Для трассировки процесса выполнения пакетов можно использовать оператор Print.
Print ‘Пакет1’
DECLARE…
Print ‘Пакет 1 выполнен’
23. Локальные и глобальные временные таблицы (язык T-SQL).
Они полезны в качестве средства перемещения данных между другими объектами.
Предусмотрено использование локальных и глобальных временных таблиц, которые хранятся в системной БД tempDB.
Локальная временная таблица.
Доступна только создающему пользователю и существует до конца текущего сеанса работы пользователя.
CREATE TABLE #имя_таблицы
CREATE TABLE #personTemp (NomINT, …);
Глобальная временная таблица.
К ней могут обращаться все пользователи. Существует до окончания сеанса работы последнего пользователя, обращенного к ней.
CREATETABLE ##имя_таблицы
24.Табличные переменные (язык T-SQL).
Аналогичны временным таблицам, т.к. хранятся в системной БД tempDB
Отличие – табличные переменные имеют ту же область определения, что и обычные переменные. Но они видимы только в пакете или хранимой процедуре, в которой созданы.
1.Не могут быть созданы оператором SELECT * INTO
2.Не могут быть созданы внутри функций.
3.Не могут иметь зависимых объектов.
4.Недопустимы условия проверки и внешние ключи.
DECLARE @название_переменнойTABLE (столбцы)
25. Хранимые процедуры (язык T-SQL): преимущества; создание и изменение; методы передачи данных в процедуру; значения параметров, заданные по умолчанию; способы получения данных из процедуры.
1.Хранятся в компилированном виде, поэтому выполняются быстрее, чем пакеты или запросы.
2.Выполняется на сервере, а не на клиентском компьютере, значит снижает нагрузку на комп.сеть.
3.Имеют модульный вид = легко внедрять или изменять. Если клиентские приложения вызывают хранимую процедуру для выполнения некоторых операций, то модификация, производимая в 1 месте, влияет на ее выполнение у всех пользователей.
4.Можно рассмотреть как важный компонент системы обеспечения безопасности БД. Если все клиенты осуществляют доступ к данным с помощью хранимых процедур, то прямой доступ к таблице может быть запрещен, и все действия пользователя будут находиться под контролем.
CREATE, ALTER, DROP
USE Zgrad;
CREATE PROCEDURE PersonList
AS
SELECT Nom, FIO,Adr
FROM Person;
RETURN;
GO
Возвращениенаборастрок
EXEC PersonList
Компиляция хранимых процедур выполняется автоматически при первом запуске, после чего скомпилированный вариант сохраняется в памяти.
Дляперекомпиляции – EXEC sp_recompilePersonList
Передачаданных
USE Zgrad
CREATE PROCEDURE PersonData (@PersonNom INT)
AS
SELECT Nom, FIO, Adr FROM dbo.Person
WHERE Nom=@PersonNom; RETURN;
GO EXECPersonData 5
Если параметров несколько, то порядок их определения важен, нужно его сохранить. Можно передавать параметры по имени в любом порядке.
Если два метода смешиваются, то происходит ошибка.
CREATE PROCEDURE StoredProcedure (@Par1 INT,@Par2 CHAR(1))
AS
PRINT @Par1;
PRINT @Par2;
GO
EXEC StoredProcedure @Par1=1; @Par2=2;
EXECStoredProcedure 2, ‘n’;
Значение параметров по умолчанию.
CREATE PROCEDURE FindPersonData (@Find INT = NULL)
--@Find = NULL – вывод сведений обо всех --@Find= значение – выполнение поиска
AS
SETNOCOUTNON;
SELECT…
4 способа получения данных:
1.SELECT
2.PAISERROR
3.RETURN
4.Выходные параметры
3 и 4 передают значения локальным переменным вызывающего пакета.
1 и 2 передаются клиентскому приложению. Сама вызывающая процедура или пакет ничего не знают об этих значениях.