1 / 50

SQL Server :: Transact– SQL #2

Laboratorium Perancangan / Pemrograman Basis Data Komposisi oleh Budi Prayitno April 2012 Batam. SQL Server :: Transact– SQL #2. SYNONYM. SYNONYM adalah objek basis data yang berfungsi sebagai nama alternatif/alias terhadap objek basis data lain. Syntax CREATE SYNONYM.

emele
Télécharger la présentation

SQL Server :: Transact– SQL #2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LaboratoriumPerancangan/Pemrograman Basis Data Komposisioleh Budi Prayitno April 2012 Batam SQL Server :: Transact–SQL #2

  2. SYNONYM • SYNONYM adalah objek basis data yang berfungsi sebagai nama alternatif/alias terhadap objek basis data lain

  3. Syntax CREATE SYNONYM CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>

  4. CREATE SYNONYM USE tempdb; GO CREATE SYNONYM MyProduct FOR Production.Product; GO

  5. Pemanggilan SYNONYM USE tempdb; GO SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5; GO

  6. VIEW • View adalah objek basis data yang merupakan representasi logis sebagian atau keseluruhan data [SELECT] yang berasal dari satu atau beberapa table [FROM] berdasarkan kriteria tertentu [WHERE]

  7. Syntax CREATE VIEW CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ] • WITH CHECK OPTION digunakan untuk memastikan bahwa DML statement yang dieksekusi terhadap VIEW memenuhi kriteria yang ditentukan dalam SELECT statement

  8. CREATE VIEW USE AdventureWorks ; GO IF OBJECT_ID ('hiredate_view', 'view') IS NOT NULL DROP VIEW hiredate_view ; GO CREATE VIEW hiredate_view AS SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ; GO

  9. Pemanggilan VIEW SELECT hiredate_view hv WHERE hv. EmployeeID = ‘101’

  10. SEQUENCE • SEQUENCE adalah objek basis data yang berfungsi sebagai generator tipe data INTEGER [serta DECIMAL dan NUMERIC tertentu]

  11. CREATE SEQUENCE Syntax CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ] [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } ] [ ; ]

  12. Tipe Data SEQUENCE • TINYINT [0 .. 255] • SMALLINT [-32768 .. 32767] • INT [-2147483648 .. 2147483647] • BIGINT [-9223372036854775808 .. 9223372036854775807] • DECIMAL dan NUMERIC dengan scale 0. • Tipe data user-defined yang berbasis tipe data di atas

  13. CREATE SEQUENCE CREATE SEQUENCE Test.DecSeq AS decimal(3,0) START WITH 125 INCREMENT BY 25 MINVALUE 100 MAXVALUE 200 CYCLE;

  14. Pemanggilan SEQUENCE SELECT NEXT VALUE FOR Test.DecSeq;

  15. TRIGGER • TRIGGER adalah objek basis data yang melakukan intersepsi terhadap eksekusi sebuah DML/DDL statement dan melakukan aksi tertentu terkait atau tidak terkait dengan DML/DDL yang diintersepsi tersebut • TRIGGER untuk DML diimplementasikan pada INSERT, UPDATE atau DELETE statement terhadap TABLE atau VIEW • TRIGGER untuk DDL diimplementasikan pada CREATE, ALTER, DROP, GRANT, DENY, REVOKE atau UPDATE STATISTICS statement

  16. TRIGGER, Lebih Lanjut

  17. CREATE TRIGGER, untuk DML #1 CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

  18. CREATE TRIGGER, untuk DML #2 CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader p JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID WHERE v.CreditRating = 5) BEGIN RAISERROR ('A vendor''s credit rating is too low.', 16, 1); ROLLBACK TRANSACTION; RETURN END; GO

  19. CREATE TRIGGER, untuk DML #3 CREATE TRIGGER tr_iud_t ON dbo.t AFTER INSERT, UPDATE, DELETE AS BEGIN IF @@ROWCOUNT = 0 RETURN; SELECT i.Id AS [Inserted], i.DataValue AS [NewValue], d.Id AS [Deleted], d.DataValue AS [OldValue] FROM inserted i FULL OUTER JOIN deleted d ON i.Id = d.Id END; GO

  20. CREATE TRIGGER, untuk DML #4 CREATE TRIGGER Trig_Amt_Upd ON TXNMaster FOR UPDATE AS BEGIN UPDATE m SET AMOUNT = b.AMOUNT FROM TXNMaster m INNER JOIN inserted a ON m.ID = a.ID INNER JOIN deleted b ON m.ID = a.ID WHERE a.AMOUNT > b.AMOUNT END

  21. Pemanggilan TRIGGER, DML INSERT INTO PurchaseOrderHeader (RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES (2,3,261,1652,4,GETDATE() ,GETDATE(),44594.55,3567.564,1114.8638 ); GO

  22. CREATE TRIGGER untuk DDL #1 CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

  23. CREATE TRIGGER untuk DDL#2 CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS PRINT 'You must disable Trigger "safety" to drop synonyms!' ROLLBACK GO

  24. INDEX • INDEX adalah objek basis data yang berisi indeks sebuah TABLE • INDEX digunakan untuk meningkatkan performa SELECT statement tertentu

  25. CREATE INDEX CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ] [ ; ]

  26. Contoh #1 CREATE INDEX USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ProductVendor_VendorID') DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor; GO CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID); GO

  27. Contoh #2 CREATE INDEX USE AdventureWorks GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD') DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ; GO CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD); GO

  28. Contoh #3 CREATE UNIQUE INDEX USE AdventureWorks; GO IF EXISTS (SELECT name from sys.indexes WHERE name = N'AK_UnitMeasure_Name') DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure; GO CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO

  29. Contoh #4 CREATE INDEX CREATE VIEW Sales.vOrders WITH SCHEMABINDING AS SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o WHERE od.SalesOrderID = o.SalesOrderID GROUP BY OrderDate, ProductID; GO --Create an index on the view. CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (OrderDate, ProductID);

  30. FUNCTION • FUNCTION adalah Transact-SQL atau CLR routine yang mengembalikan sebuah nilai • FUNCTION user-defined tidak dapat digunakan untuk melakukan perubahan terhadap basis data

  31. FUNCTION, Lebih Lanjut • Scalar Function • Inline Table–valued function • Multistatement Table–valued function • Common Language Runtime [CLR] Function

  32. ScalarFUNCTION CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]

  33. CREATE Scalar FUNCTION CREATE FUNCTION ScalarTrim(@StrToTrim NVARCHAR(200)) RETURNS NVARCHAR (200) AS BEGIN RETURN LTRIM (RTRIM (@StrToTrim)) END GO

  34. Pemanggilan ScalarFUNCTION SELECT LTRIM (RTRIM (TEXTCol)) FROM FunctionTest SELECT dbo.ScalarTrim (TEXTCol) FROM FunctionTest

  35. Inline Table–Valued FUNCTION CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]

  36. CREATE ITV FUNCTION CREATE FUNCTION GetUnshippedOrders () RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO SELECT * FROM dbo.GetUnshippedOrders ()

  37. ITVuntuk Scalar CREATE FUNCTION ITVLTrimR(@StrToTrim NVARCHAR(200)) RETURNS TABLE AS RETURN (SELECT LTRIM(RTRIM(@StrToTrim)) AS Trimmed) GO

  38. ITVuntuk Scalar SELECT LTRIM (RTRIM (Column_Name)) FROM Table_Name SELECT dbo. TVFTrim (TEXTCol) FROM Table_Name SELECT Trm.Trimmed FROM Table_NameTblNm CROSS APPLY dbo.ITVLTrimR (TblNm.Column_Name) AS Trm

  39. Scalar untuk ITV CREATE FUNCTION GIS_Get_Lat (@City VARCHAR(30),    @State CHAR(2)) RETURNS INT WITH EXECUTE AS CALLER ASBEGIN DECLARE @LAT INT   SET @LAT = ( SELECT TOP 1 Lattitude FROM GIS_Location WHERE State = @State AND City = @City) RETURN @LAT END

  40. MS Table–valued FUNCTION CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN END [ ; ]

  41. CREATE MSTV FUNCTION #1 CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID

  42. CREATE MSTV FUNCTION #2 INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO

  43. CLR FUNCTION CREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ) RETURNS { return_data_type | TABLE <clr_table_type_definition> } [ WITH <clr_function_option> [ ,...n ] ] [ AS ] EXTERNAL NAME <method_specifier> [ ; ]

  44. PROCEDURE • Procedure adalah koleksi Transact-SQL statement [atau referensi ke CLR method Microsoft .NET Framework] yang disimpan sebagai objek basis data serta dapat menerima dan mengembalikan parameter dari dan kepada pengguna

  45. CREATE PROCEDURE CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] [ FOR REPLICATION ] AS { <sql_statement> [;][ ...n ] | <method_specifier> } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE_AS_Clause ]

  46. PROCEDURE, Contoh #1 CREATE PROCEDURE GetAllLoc AS SELECT LOC.CITY, COU.COUNTRY_NAME FROM LOCATIONS LOC JOIN COUNTRIES COU ON LOC.COUNTRY_ID = COU.COUNTRY_ID GetAllLoc -- Hanya untuk statement pertama dalam batch EXECUTE GetAllLoc EXEC GetAllLoc

  47. PROCEDURE, Contoh #2 CREATE PROCEDURE GetEmpByName @lastname varchar(40), @firstname varchar(20) AS SELECT EMP.FIRST_NAME + ' ' + EMP.LAST_NAME EMPLOYEE_NAME, DEP.DEPARTMENT_NAME FROM EMPLOYEES EMP JOIN DEPARTMENTS DEP ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID AND EMP.FIRST_NAME = @firstname AND EMP.LAST_NAME = @lastname

  48. PROCEDURE, Pemanggilan #2 GetEmpByName @firstname = 'Steven', @lastname = 'King' EXECUTE GetEmpByName @firstname = 'Steven', @lastname = 'King' EXECUTE GetEmpByName @lastname = 'King', @firstname = 'Steven' EXEC GetEmpByName @firstname = 'Steven', @lastname = 'King' EXEC GetEmpByName @lastname = 'King', @firstname = 'Steven'

  49. PROCEDURE, Contoh #3 CREATE PROCEDURE GetEmpBySalary @minsalary INT, @maxsalary INT, @avgsalary INT OUTPUT, @actualavgsalary INT OUT AS SELECT EMP.FIRST_NAME + ' ' + EMP.LAST_NAME EMPLOYEE_NAME,EMP.SALARY FROM EMPLOYEES EMP WHERE EMP.SALARY BETWEEN @minsalary AND @maxsalary; SET @actualavgsalary = ( SELECT AVG (EMP.SALARY) FROM EMPLOYEES EMP WHERE EMP.SALARY BETWEEN @minsalary AND @maxsalary); SET @avgsalary = (@minsalary + @maxsalary) / 2;

  50. PROCEDURE, Pemanggilan #3 DECLARE @avgsalary INT, @actualavgsalary INT EXECUTE GetEmpBySalary 5000, 10000, @avgsalary OUT, @actualavgsalary OUTPUT BEGIN PRINT 'These Employees share actual average salary of '+ RTRIM (CAST (@actualavgsalary AS varchar(20))) +' meanwhile the average for input parameters is ' + RTRIM (CAST (@avgsalary AS varchar(20))) + '.' END

More Related