Новые операторы языка манипулирования данными (DML)
СОДЕРЖАНИЕ: CTE - выражения для упрощения читаемости запросов. Однократный вызов CTE и использование CTE для рекурсивного прохода по дереву. Операторы Pivot, Unpivot, Apply. Функции ранжирования. Создание хранимой процедуры с использованием нового обработчика ошибок.Факультет информационных технологий
Кафедра компьютерной инжинерий
Дисциплина: Проектирование и администрирование базы данных
Лабораторная работа №2 и №3
НОВЫЕ ОПЕРАТОРЫ ЯЗЫКА МАНИПУЛИРОВАНИЯ ДАННЫМИ ( DML )
Выполнила : Смайлова Сая.
Проверила: Найзабаева Л.
Алматы- 2010
create table Parfums(
parfum_name varchar(20),
parfum_id int,
parfum_type varchar(20),
price int,
use_time datetime,
destination varchar(20),
primary key(parfum_id)
)
insert into Parfums
values (Eclat,100,Cold aroma,5600,10/5/2010,For women)
insert into Parfums
values (Nina Richy,101,Sweet aroma,11500,7/15/2012,For women)
insert into Parfums
values (Calvin Klein,102,Hot aroma,8900,3/21/2011,For men)
insert into Parfums
values (Princess,104,Sweet aroma,4200,11/30/2014,For children)
create table Cosmetics(
cosmetic_id int,
cosmetic_name varchar(20),
cosmetic_type varchar(20),
price int,
amount int,
use_time datetime,
firma varchar(20),
representive varchar (20)
)
insert into Cosmetics
values(200,Garnier Light,Day cream,1900,120,3/8/2011,Avon,Nurzhanova Asel)
insert into Cosmetics
values(201,MaxFactor,Eyelash,2300,209,7/8/2010,Oriflame,Smailova Saya)
insert into Cosmetics
values(202,Pharma,Makeupremover,3000,260,11/18/2010,Maybeeline,Tanabaeva Gulzada)
insert into Cosmetics
values(203,Baby Body,Lotion,300,80,9/20/2012,Nivea,Erimbetova Laura)
1) CTE - выражения для упрощения читаемости запросов
with first as
(
select *
from Parfums
where destination like for women
)
select * from first
order by price;
2) Однократный вызов CTE
WITH
maxi AS (SELECT (max (amount))AS v FROM Cosmetics),
mini AS (SELECT (min (price))AS v FROM Cosmetics)
SELECT cosmetic_id, cosmetic_name, amount, price
FROM Cosmetics as co, mini, maxi
WHERE co.amount=maxi.v or co.price= mini.v;
3) Использование CTE для рекурсивного прохода по дереву
WITH alphavit AS(
SELECT ASCII(A) code, CHAR(ASCII(A)) letter
UNION ALL
SELECT code+1, CHAR(code+1) FROM alphavit
WHERE code+1 = ASCII(Z)
)
SELECT letter, cosmetic_name FROM alphavit,Cosmetics
where cosmetic_name like letter+%;
4) Оператор PIVOT
SELECT cosmetic_name, [Avon],[Oriflame],[Nivea]
--INTO tmpUnpivot
FROM Cosmetics
PIVOT (
sum(amount)
FOR [firma] IN ([Avon],[Oriflame],[Nivea])
)PVT;
5) Оператор UNPIVOT
SELECT cosmetic_name, firma, amount
FROM tmpUnpivot pvt
UNPIVOT ( amount FOR firma
IN([Avon],[Oriflame],[Nivea])
)unpvt;
6) Оператор CROSS APPLY
alter FUNCTION parf (@cos_id as int)
RETURNS TABLE AS
RETURN
SELECT top(1) cosmetic_name,cosmetic_type
FROM Cosmetics
WHERE cosmetic_id=@cos_id;
SELECT mro.*,price
FROM Parfums
CROSS APPLY parf(parfum_id) as mro;
7) Оператор OUTER APPLY
SELECT mro.*,price
FROM Parfums
OUTER APPLY parf(parfum_id) as mro;
8) Функции ранжирования
select parfum_name,destination, price,Rank()
over (Partition BY destination order by price DESC)
as Rank
from Parfums
9) DENSE_RANK()
select parfum_name,destination, price,DENSE_RANK()
over (Partition BY destination order by price DESC)
as Rank
from Parfums
10) Row_Number()
select parfum_name,destination, price,Row_Number()
over (Partition BY destination order by price DESC)
as Rank
from Parfums
11) Ntile()
select parfum_name,destination, price, Ntile(3)
over (Partition BY destination order by price DESC)
as Rank
from Parfums
12) Оператор TOP
DECLARE @var1 AS int , @var2 AS int;
SET @var1=1;
SET @var2=2;
SELECT TOP (@var1*@var2) *
FROM Parfums;
13) Использование предложения TABLESAMPLE
SELECT *
FROM Cosmetics SYSTEM TABLESAMPLE (100 PERCENT);
SELECT parfum_name,parfum_type
FROM Parfums p TABLESAMPLE(100 percent)
join Cosmetics c TABLESAMPLE(100 percent)
on c.cosmetic_id=p.parfum_id
14) Создание хранимой процедуры с использованием нового обработчика ошибок
CREATE PROCEDURE saya
AS
BEGIN
BEGIN TRY
SELECT * FROM Parfums;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE()
END CATCH
END;
GO
exec saya
15) Создание хранимой процедуры с использованием функции, возвращающей состояние транзакции
CREATE PROCEDURE lovely
AS
BEGIN TRY
SELECT * FROM Cosmetics;
END TRY
BEGIN CATCH
IF (XACT_STATE())= -1 ROLLBACK TRANSACTION;
IF (XACT_STATE())= 1 COMMIT TRANSACTION;
END CATCH
