[SQL][MCSA]70-461 筆記 (四)
Modify Data (24%)
-
Create and alter stored procedures (simple statements).
- May include but not limited to: write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL
-
Modify data by using INSERT, UPDATE, and DELETE statements.
- May include but not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
-
Combine datasets.
- May include but not limited to: difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements
-
Work with functions.
- May include but not limited to: understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)
Store Procedure
建立的語法
--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
刪除的語法
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
修改的語法
--Transact-SQL Stored Procedure Syntax
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
優點 ( MSDN : http://msdn.microsoft.com/zh-tw/library/ms190782(v=sql.110).aspx )
-
安全性
- 多個使用者和用戶端程式都可以透過程序，在基礎資料庫物件上執行作業，即使使用者和程式不具備這些基礎物件的直接權限亦可。 程序也會控制要執行哪些程序和活動，並保護基礎資料庫物件。 這可避免在個別物件層級授與權限的需求，而簡化安全層。
- EXECUTE AS 子句可指定在 CREATE PROCEDURE 陳述式中模擬其他使用者，或讓使用者或應用程式執行特定資料庫活動，而不需要具備基礎物件和指令的直接權限。
-
提升效能
- 預設第一次編譯程序時，將建立執行計畫並執行，以利後續的執行中重複使用。 由於查詢處理器不需要建立新計畫，通常可以花較少的時間來處理程序。
RECOMPLIER : There are three ways to do this:
-
- WITH RECOMPILE option in the procedure definition or when the procedure is called,
- the RECOMPILE query hint on individual statements
- Using the sp_recompile system stored procedure.
案例說明
-- grants EXECUTE permission on the stored procedure to an application role named Recruiting11.
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
TO Recruiting11;
-- stored procedure with an input and an output parameter.
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
-- Declare the variable to receive the output value of the procedure.
DECLARE @SalesYTDBySalesPerson money;
-- Execute the procedure specifying a last name for the input parameter
-- and saving the output value in the variable @SalesYTDBySalesPerson
EXECUTE Sales.uspGetEmployeeSalesYTD
N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
EXECUTE AS
- 可以定義使用者自訂模組 ( Function 、Stored Procedure 、Queue和 Trigger )的執行內容。
- CALLER : 預設值，指定模組內的陳述式，是在該模組的呼叫者內容當中執行。
- SELF : 相當於 EXECUTE AS user_name，其中指定的使用者，就是建立或變更模組的人。
- OWNER : 指定模組內的陳述式，是在該模組目前擁有者的內容中執行。 如果該模組沒有指定的擁有者，則會採用該模組的結構描述擁有者。
DML
INSERT
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table
| execute_statement
| <dml_table_source>
| DEFAULT VALUES
}
}
}
[;]
- 在 SQL 2008 之後可允許一次 Insert 多組
CREATE TABLE dbo.Table1
(ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 30.0);
UPDATE
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM{ <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }