Transact-SQL
Instrukcje sterujące (poziom instrukcji)
DECLARE @x int = 10
BEGIN
IF @x > 10
PRINT 'Zmienna jest > 10'
ELSE
BEGIN
PRINT 'Zmienna jest < 10'
SET @x = 0 -- zerowanie zmiennej
END
DECLARE @x int
END
BEGIN
SET @x = 0
WHILE @x < 10
BEGIN
SET @x = @x + 1
IF @x = 6 BREAK --wyjście z pętli
IF @x = 2 CONTINUE --rozpoczęcie kolejnej interacji
PRINT  Number ' + CAST(@x AS varchar(4))
END
END
Instrukcje sterujące (poziom wiersza)
USE AdventureWorks2012;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Instrukcje sterujące (poziom wiersza)
USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under
$250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN
'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
Kursory
DECLARE nazwa_kursora CURSOR [ { LOCAL | GLOBAL } ]
[ { FORWARD_ONLY | SCROLL } ]
[ { STATIC | KEYSET | DYNAMIC | FAST_FORWARD } ]
[ { READ_ONLY | SCROLL_LOCKS | OPTIMISTIC } ]
[ TYPE_WARNING ]
FOR wyrazenie_select
[ FOR UPDATE [ OF nazwa_kolumny [,& n] ] ]
[;]
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM Employees WHERE LastName like '[A-F]%'
BEGIN
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
END
Obsługa błędów
SET LANGUAGE POLISH
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
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() AS ErrorMessage;
END CATCH;
GO
Obsługa błędów - rejestrowanie
create table TabelaBledow
(ErrorID int IDENTITY(1,1) PRIMARY KEY,
ErrorNumber int NOT NULL,
ErrorMessage nvarchar(200) NOT NULL,
ErrorDate datetime DEFAULT GETDATE(),
ErrorUser varchar(50) DEFAULT SUSER_SNAME())
GO
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
INSERT TabelaBledow(ErrorNumber, ErrorMessage)
SELECT ERROR_NUMBER(), ERROR_MESSAGE();
RAISERROR('Nie dzielimy przez zero',16,1)
END CATCH;
GO
select * from TabelaBledow
Dynamiczne tworzenie instrukcji
DECLARE @dbname varchar(30),
@tblname varchar(30)
SET @dbname = 'northwind'
SET @tablename = 'categories'
EXECUTE('USE ' + @dbname + ' SELECT * FROM dbo.'
+ @tablename)
Stronicowanie danych
SELECT P.ProductName, p.UnitPrice, p.Cena
FROM (
SELECT ProductName, unitprice,
ROW_NUMBER() OVER(ORDER BY unitprice desc,
productname) AS Cena FROM Products
) AS P
WHERE P.Cena BETWEEN 10 AND 20
Procedury składowane
Procedury przechowywane
CREATE { PROC | PROCEDURE }
[nazwa_schematu.]nazwa_procedury
[ @parametr typ_danych[,& n] ]
[ WITH [,& n] ]
[ FOR REPLICATION ]
AS
cialo_procedury
::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
Procedury przechowywane (bez parametrów)
CREATE PROCEDURE GetAllEmployees
AS
SELECT LastName, FirstName, Address
FROM Employees;
GO
-- Wywołanie procedury
execute GetAllEmployees;
GO
exec GetAllEmployees;
GO
GetAllEmployees;
Zadanie:
zdefiniować procedurę, która podaje sumaryczna sprzedaż wszystkich produktów
Procedury przechowywane (z parametrami wejściowymi)
CREATE PROCEDURE GetEmployees
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%',
@Count int OUT --OUTPUT
AS
SELECT LastName, FirstName, Address
FROM Employees where LastName like @LastName
and FirstName like @FirstName;
GO
-- Wywołanie procedury
exec GetEmployees @Lastname ='k%', @Firstname='%';
exec GetEmployees @Firstname ='%', @Lastname='k%';
exec GetEmployees 'k%','%';
exec GetEmployees 'k%';
exec GetEmployees 'k%', DEFAULT;
exec GetEmployees DEFAULT, '%'; --inna
Zadanie:
zdefiniować procedurę, która podaje sumaryczna sprzedaż wszystkich produktów
w danym roku podanym jako parametr
Procedury przechowywane (z parametrami wejściowymi i wyjściowymi)
CREATE PROCEDURE dbo.GetOutEmployees
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%',
@Count int OUT --OUTPUT
AS
SET @Count = (SELECT count(*) FROM Employees
where LastName like @LastName
and FirstName like @FirstName);
GO -- Wywołanie procedury
DECLARE @OUT1 INT;
BEGIN
exec dbo.GetOutEmployees @Lastname = '%',
@Firstname = '%',
@Count = @OUT1 OUT;
Print 'Liczba prac. ='+CAST(@OUT1 as VARCHAR(5));
END
Zadanie:
zdefiniować procedurę, która podaje sumaryczna sprzedaż wszystkich produktów
w danym roku podanym jako parametr, a wynik przekazywany jest na zewnątrz
Wykonywanie rozszerzonych procedur przechowywanych
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options ,1;
GO
-- To update the currently configured value for
advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for
this feature.
RECONFIGURE;
GO
EXEC master..xp_cmdshell 'dir c:\temp
Wyzwalacze
(Trigger)
DML Trigger
Trigger : INSERT, UPDATE or DELETE statement to
a table or view (DML Trigger)
CREATE TRIGGER
[schema_name .]trigger_name
ON { table | view }]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ]}
Zadanie:
zdefiniować wyzwalacz na tabeli [Order Details], który przy zakupie danego
towaru automatycznie zostaje on zdjęty ze stanu magazynu. Przy modyfikacji
rekordu stan magazynu musi być aktualny a przy kasowaniu rekordu pojawia się
informacja o braku możliwości wykonania takiej możliwości. Jeżeli stan
magazynu jest < 0 to pojawia się komunikat o braku towaru.
BEGIN TRY
INSERT INTO [dbo].[Order Details]
([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount])
DML Trigger
VALUES (10248,6,10,5,0)
INSERT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
ALTER TRIGGER [dbo].[MagazynINS]
ON [dbo].[Order Details]
AFTER INSERT
AS
BEGIN
BEGIN TRY --UnitsInStock musi być >=0 (CHECK)
update Products
set UnitsInStock = UnitsInStock-inserted.Quantity
from Products, inserted
where products.productid=inserted.ProductID
END TRY
BEGIN CATCH
ROLLBACK TRAN;
raiserror('Brakuje towaru w magazynie',16,1);
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
BEGIN TRY
UPDATE [dbo].[Order Details]
SET [Quantity]=4
DML Trigger
where [OrderID] = 10248 and [ProductID] = 6
UPDATE
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
ALTER TRIGGER [dbo].[MagazynUpdate]
ON [dbo].[Order Details]
AFTER UPDATE
AS
BEGIN
BEGIN TRY --unitinstock musi być >=0 (ograniczenie CHECK)
update Products
set UnitsInStock=UnitsInStock+deleted.Quantity-inserted.Quantity
from Products, inserted, deleted
where products.productid=inserted.ProductID and
products.productid = deleted.ProductID
END TRY
BEGIN CATCH
ROLLBACK TRAN;
raiserror(Brak towaru w magazynie po aktualizacji',16,1);
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
BEGIN TRY
DELETE [dbo].[Order Details]
DML Trigger
where [OrderID] = 10248 and [ProductID] = 6
DELETE
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
ALTER TRIGGER [dbo].[MagazynDel]
ON [dbo].[Order Details]
AFTER DELETE
AS
BEGIN
RAISERROR ('Nie można kasować pozycji na fakturze', 16, 10);
ROLLBACK
END
ALTER TRIGGER [dbo].[MagazynDel]
ON [dbo].[Order Details]
INSTEAD OF DELETE
AS
BEGIN
RAISERROR ('Nie można kasować pozycji na fakturze', 16, 10);
--ROLLBACK
END
Database Trigger,
Server Trigger
DDL Trigger
Trigger on a CREATE, ALTER, DROP, GRANT,
DENY, REVOKE or UPDATE STATISTICS statement
(DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ]}
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
{ FOR | AFTER } LOGON AS { sql_statement [ ; ]}
BEGIN TRY
drop table [dbo].[Table_1]
Database Trigger
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
ALTER TRIGGER table_alter_drop_safety ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT IS_MEMBER ('db_owner')
IF IS_MEMBER ('db_owner') = 0
BEGIN
PRINT 'You must ask your DBA to drop or alter tables!'
ROLLBACK TRANSACTION
END
----------------------------------------------------------
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('You must disable Trigger "safety" to drop
synonyms!',10, 1)
ROLLBACK
Server Trigger
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
Funkcje użytkownika
(UDF)
Funkcje użytkownika (UDF): Scalar-valued Function
CREATE FUNCTION Function1
(@p1 int)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = @p1*@p1
RETURN @Result
END
GO
select dbo.Function1(8)
GO
DECLARE @a1 int
SET @a1 = dbo.Function1(8)
PRINT @a1
Funkcje użytkownika (UDF): Inline Table-valued Function
CREATE FUNCTION dbo.Test2(@employeeID int)
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees
WHERE EmployeeID > @employeeID)
GO
select * from dbo.Test2(5)
Funkcje użytkownika (UDF): Multi-statement Table-valued Function
ALTER FUNCTION Test3
-- Add the parameters for the function here
( @p1 int, @p2 char )
RETURNS
@Table_Var TABLE
-- Add the column definitions for the TABLE variable here
( c1 int, c2 varchar(40), c3 money)
AS
BEGIN
INSERT INTO @Table_Var
SELECT productid, productname, unitprice from Products
where unitprice > @p1 and productname like @p2
RETURN
END
GO -- wywołanie funkcji
select * from dbo.Test3(100,'%')
Widoki
Synonimy
Sekwencje