Cộng đồng chia sẻ tri thức Lib24.vn

SQLServerCh15

e78b5bfd2aca09445286f62b35bad400
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:38:27 | Được cập nhật: 25 tháng 3 lúc 13:27:00 Kiểu file: PPTX | Lượt xem: 214 | Lượt Download: 1 | File size: 1.548837 Mb

Nội dung tài liệu

Tải xuống
Link tài liệu:
Tải xuống

Các tài liệu liên quan


Có thể bạn quan tâm


Thông tin tài liệu

Chapter 15 How to code stored procedures, functions, and triggers Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 1 Objectives Applied  Given the specifications for a database problem, write a stored procedure that solves it. Include data validation when necessary.  Given a formula or expression, write a scalar-valued user-defined function based on the formula or expression.  Given a SELECT statement with a WHERE clause, write a tablevalued user-defined function that replaces it.  Given the specifications for a database problem that could be caused by an action query, write a trigger that prevents the problem.  Given the specifications for a database problem that could be caused by a DDL statement, write a trigger that prevents the problem. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 2 Objectives (cont.) Knowledge  Explain why a stored procedure executes faster than an equivalent SQL script.  Describe the basic process for validating data within a stored procedure.  Describe the basic purpose of the system stored procedures.  Describe the two types of user-defined functions.  Describe the two types of triggers.  Describe the effects of the WITH ENCRYPTION and WITH SCHEMABINDING clauses on a stored procedure, user-defined function, or trigger.  Explain why you’d want to use the ALTER statement rather than dropping and recreating a procedure, function, or trigger. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 3 Objectives (cont.)  Given a stored procedure, user-defined function, or trigger, explain what each statement does. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 4 A comparison of the different types of procedural SQL programs How it’s Batches stored Multiple In a file on a disk One only In an object in the database Accepts parameters No User-defined function One only Yes Trigger One only Type Script Stored procedure Murach's SQL Server 2012, C15 How it’s executed Within a client tool By a program or within a SQL script In an object in By a program the database or within a SQL script In an object in Automatically the database by the server when an action query is executed © 2012, Mike Murach & Associates, Inc. Yes No Slide 5 How stored procedures are used  By SQL programmers to control who accesses the database and how  By application programmers to simplify their use of the database How user-defined functions are used  Most often used by SQL programmers within the stored procedures and triggers they write  Can also be used by application programmers and end users How triggers are used  By SQL programmers to prevent database errors when an action query is executed  By SQL programmers to provide for updatable views Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 6 A script that creates a stored procedure USE AP; GO CREATE PROC spInvoiceReport AS SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal – CreditTotal – PaymentTotal > 0 ORDER BY VendorName; The response from the system Command(s) completed successfully. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 7 A statement that calls the procedure EXEC spInvoiceReport; The result set created by the procedure Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 8 The syntax of the CREATE PROC statement CREATE {PROC|PROCEDURE} procedure_name [parameter_declarations] [WITH [RECOMPILE] [, ENCRYPTION] [, EXECUTE_AS_clause]] AS sql_statements A script that creates a stored procedure that copies a table USE AP; IF OBJECT_ID('spCopyInvoices') IS NOT NULL DROP PROC spCopyInvoices; GO CREATE PROC spCopyInvoices AS IF OBJECT_ID('InvoiceCopy') IS NOT NULL DROP TABLE InvoiceCopy; SELECT * INTO InvoiceCopy FROM Invoices; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 9 The syntax for declaring parameters @parameter_name_1 data_type [= default] [OUTPUT] [, @parameter_name_2 data_type [= default] [OUTPUT]]... Typical parameter declarations -- Input parameter that accepts a date/time value @DateVar smalldatetime -- Optional input parameter that accepts a character value @VendorVar varchar(40) = NULL -- Output parameter that returns a monetary value @InvTotal money OUTPUT Note  It’s a good programming practice to code CREATE PROC statements so they list required parameters first, followed by optional parameters. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 10 A procedure that uses an input and an output parameter CREATE PROC spInvTotal1 @DateVar smalldatetime, @InvTotal money OUTPUT AS SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 11 A procedure that uses an optional parameter CREATE PROC spInvTotal2 @DateVar smalldatetime = NULL AS IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate) FROM Invoices; SELECT SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 12 A procedure that includes three parameters CREATE PROC spInvTotal3 @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate) FROM Invoices; SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar); Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 13 Code that passes the parameters by position DECLARE @MyInvTotal money; EXEC spInvTotal3 @MyInvTotal OUTPUT, '2012-02-01', 'P%'; Code that passes the parameters by name DECLARE @MyInvTotal money; EXEC spInvTotal3 @DateVar = '2012-02-01', @VendorVar = 'P%', @InvTotal = @MyInvTotal OUTPUT; Code that omits one optional parameter DECLARE @MyInvTotal money; EXEC spInvTotal3 @VendorVar = 'M%', @InvTotal = @MyInvTotal OUTPUT; Code that omits both optional parameters DECLARE @MyInvTotal money; EXEC spInvTotal3 @MyInvTotal OUTPUT; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 14 The syntax of the RETURN statement for a stored procedure RETURN [integer_expression] A stored procedure that returns a value CREATE PROC spInvCount @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate) FROM Invoices; DECLARE @InvCount int; SELECT @InvCount = COUNT(InvoiceID) FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar); RETURN @InvCount; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 15 A script that calls the stored procedure DECLARE @InvCount int; EXEC @InvCount = spInvCount '2012-02-01', 'P%'; PRINT 'Invoice count: ' + CONVERT(varchar, @InvCount); The response from the system Invoice count: 6 Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 16 The syntax of the THROW statement THROW [error_number, message, state] A stored procedure that tests for a valid foreign key CREATE PROC spInsertInvoice @VendorID int, @InvoiceNumber varchar(50), @InvoiceDate smalldatetime, @InvoiceTotal money, @TermsID int, @InvoiceDueDate smalldatetime AS IF EXISTS(SELECT * FROM Vendors WHERE VendorID = @VendorID) INSERT Invoices VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, @InvoiceTotal, 0, 0, @TermsID, @InvoiceDueDate, NULL); ELSE THROW 50001, 'Not a valid VendorID!', 1; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 17 A script that calls the procedure BEGIN TRY EXEC spInsertInvoice 799,'ZXK-799','2012-05-01',299.95,1,'2012-06-01'; END TRY BEGIN CATCH PRINT 'An error occurred.'; PRINT 'Message: ' + CONVERT(varchar, ERROR_MESSAGE()); IF ERROR_NUMBER() >= 50000 PRINT 'This is a custom error message.'; END CATCH; The response from the system An error occurred. Message: Not a valid VendorID! This is a custom error message. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 18 A THROW statement coded within a block BEGIN ; THROW 50001, 'Not a valid VendorID!', 1; END; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 19 Terms  Temporary stored procedure  Local stored procedure  Input parameter  Output parameter  Optional parameter  Pass parameters by position  Pass parameters by name  Data validation Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 20 A stored procedure that validates the data in a new invoice (part 1) /* Handles insertion of new invoices into AP database, including data validation. Author: Bryan Syverson Created: 2002-07-17 Modified: 2008-07-29 by Joel Murach 2012-05-09 by Anne Boehm Return value: InvoiceID for the new row if successful, 0 if unsuccessful */ USE AP; GO IF OBJECT_ID('spInsertInvoice') IS NOT NULL DROP PROC spInsertInvoice; GO Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 21 A stored procedure that validates the data in a new invoice (part 2) CREATE PROC spInsertInvoice @VendorID int = NULL, @InvoiceNumber varchar(50) = NULL, @InvoiceDate smalldatetime = NULL, @InvoiceTotal money = NULL, @PaymentTotal money = NULL, @CreditTotal money = NULL, @TermsID int = NULL, @InvoiceDueDate smalldatetime = NULL, @PaymentDate smalldatetime = NULL AS IF NOT EXISTS (SELECT * FROM Vendors WHERE VendorID = @VendorID) THROW 50001, 'Invalid VendorID.', 1; IF @InvoiceNumber IS NULL THROW 50001, 'Invalid InvoiceNumber.', 1; IF @InvoiceDate IS NULL OR @InvoiceDate > GETDATE() OR DATEDIFF(dd, @InvoiceDate, GETDATE()) > 30 THROW 50001, 'Invalid InvoiceDate.', 1; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 22 A stored procedure that validates the data in a new invoice (part 3) IF @InvoiceTotal IS NULL OR @InvoiceTotal <= 0 THROW 50001, 'Invalid InvoiceTotal.', 1; IF @PaymentTotal IS NULL SET @PaymentTotal = 0; IF @CreditTotal IS NULL SET @CreditTotal = 0; IF @CreditTotal > @InvoiceTotal THROW 50001, 'Invalid CreditTotal.', 1; IF @PaymentTotal > @InvoiceTotal - @CreditTotal THROW 50001, 'Invalid PaymentTotal.', 1; IF NOT EXISTS (SELECT * FROM Terms WHERE TermsID = @TermsID) IF @TermsID IS NULL SELECT @TermsID = DefaultTermsID FROM Vendors WHERE VendorID = @VendorID; ELSE -- @TermsID IS NOT NULL THROW 50001, 'Invalid TermsID.', 1; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 23 A stored procedure that validates the data in a new invoice (part 4) IF @InvoiceDueDate IS NULL SET @InvoiceDueDate = @InvoiceDate + (SELECT TermsDueDays FROM Terms WHERE TermsID = @TermsID); ELSE -- @InvoiceDueDate IS NOT NULL IF @InvoiceDueDate < @InvoiceDate OR DATEDIFF(dd, @InvoiceDueDate, @InvoiceDate) > 180 THROW 50001, 'Invalid InvoiceDueDate.', 1; IF @PaymentDate < @InvoiceDate OR DATEDIFF(dd, @PaymentDate, GETDATE()) > 14 THROW 50001, 'Invalid PaymentDate.', 1; INSERT Invoices VALUES (@VendorID, @InvoiceNumber, @InvoiceDate, @InvoiceTotal, @PaymentTotal, @CreditTotal, @TermsID, @InvoiceDueDate, @PaymentDate); RETURN @@IDENTITY; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 24 A SQL script that calls the stored procedure BEGIN TRY DECLARE @InvoiceID int; EXEC @InvoiceID = spInsertInvoice @VendorID = 799, @InvoiceNumber = 'RZ99381', @InvoiceDate = '2012-04-12', @InvoiceTotal = 1292.45; PRINT 'Row was inserted.'; PRINT 'New InvoiceID: ' + CONVERT(varchar, @InvoiceID); END TRY BEGIN CATCH PRINT 'An error occurred. Row was not inserted.'; PRINT 'Error number: ' + CONVERT(varchar, ERROR_NUMBER()); PRINT 'Error message: ' + CONVERT(varchar, ERROR_MESSAGE()); END CATCH; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 25 The response for a successful insert Row was inserted. New InvoiceID: 115 The response when a validation error occurs An error occurred. Row was not inserted. Error number: 50001 Error message: Invalid VendorID. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 26 The syntax for creating a user-defined table type CREATE TYPE TableTypeName AS TABLE table_definition A statement that creates a user-defined table type CREATE TYPE LineItems AS TABLE (InvoiceID INT NOT NULL, InvoiceSequence SMALLINT NOT NULL, AccountNo INT NOT NULL, ItemAmount MONEY NOT NULL, ItemDescription VARCHAR(100) NOT NULL, PRIMARY KEY (InvoiceID, InvoiceSequence)); Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 27 A stored procedure that accepts a table as a parameter CREATE PROC spInsertLineItems @LineItems LineItems READONLY AS INSERT INTO InvoiceLineItems SELECT * FROM @LineItems; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 28 Statements that pass a table to the stored procedure DECLARE @LineItems LineItems; INSERT INTO @LineItems VALUES (114, 1, 553, 127.75, 'Freight'); INSERT INTO @LineItems VALUES (114, 2, 553, 29.25, 'Freight'); INSERT INTO @LineItems VALUES (114, 3, 553, 48.50, 'Freight'); EXEC spInsertLineItems @LineItems; The response from the system (1 (1 (1 (3 row(s) row(s) row(s) row(s) Murach's SQL Server 2012, C15 affected) affected) affected) affected) © 2012, Mike Murach & Associates, Inc. Slide 29 The syntax of the DROP PROC statement DROP {PROC|PROCEDURE} procedure_name [, ...] The syntax of the ALTER PROC statement ALTER {PROC|PROCEDURE} procedure_name [parameter declarations] [WITH [RECOMPILE] [, ENCRYPTION] [, EXECUTE_AS_clause]] AS sql_statements Note  When you delete a procedure, any security permissions that are assigned to the procedure are also deleted. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 30 A statement that creates a procedure CREATE PROC spVendorState @State varchar(20) AS SELECT VendorName FROM Vendors WHERE VendorState = @State; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 31 Change the parameter defined by the procedure ALTER PROC spVendorState @State varchar(20) = NULL AS IF @State IS NULL SELECT VendorName FROM Vendors; ELSE SELECT VendorName FROM Vendors WHERE VendorState = @State; Delete the procedure DROP PROC spVendorState; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 32 Commonly used system stored procedures  sp_Help [name]  sp_HelpText name  sp_HelpDb [database_name]  sp_Who [login_ID]  sp_Columns name Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 33 How to use sp_HelpText USE AP; EXEC sp_HelpText spInvoiceReport; The results returned by the procedure The results if WITH ENCRYPTION is used The text for object 'spInvoiceReport' is encrypted. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 34 The three types of user-defined functions Function type Scalar-valued function Simple table-valued function Multi-statement table-valued function Murach's SQL Server 2012, C15 Description Returns a single value of any T-SQL data type. Returns a table that’s based on a single SELECT statement. Returns a table that’s based on multiple statements. © 2012, Mike Murach & Associates, Inc. Slide 35 A statement that creates a scalar-valued function CREATE FUNCTION fnVendorID (@VendorName varchar(50)) RETURNS int BEGIN RETURN (SELECT VendorID FROM Vendors WHERE VendorName = @VendorName); END; A statement that invokes the scalar-valued function SELECT InvoiceDate, InvoiceTotal FROM Invoices WHERE VendorID = dbo.fnVendorID('IBM'); A statement that invokes a table-valued function SELECT * FROM dbo.fnTopVendorsDue(5000); Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 36 The syntax for creating a scalar-valued function CREATE FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS data_type [WITH [ENCRYPTION] [, SCHEMABINDING] [, EXECUTE_AS_clause]] [AS] BEGIN [sql_statements] RETURN scalar_expression END Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 37 A statement that creates a scalar-valued function CREATE FUNCTION fnBalanceDue() RETURNS money BEGIN RETURN (SELECT SUM(InvoiceTotal – PaymentTotal – CreditTotal) FROM Invoices WHERE InvoiceTotal – PaymentTotal – CreditTotal > 0); END; A script that invokes the function PRINT 'Balance due: $' + CONVERT(varchar, dbo.fnBalanceDue(), 1); The response from the system Balance due: $32,020.42 Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 38 The syntax for creating a simple table-valued function CREATE FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS TABLE [WITH [ENCRYPTION] [, SCHEMABINDING]] [AS] RETURN [(] select_statement [)] A statement that creates a simple table-valued function CREATE FUNCTION fnTopVendorsDue (@CutOff money = 0) RETURNS table RETURN (SELECT VendorName, SUM(InvoiceTotal) AS TotalDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 GROUP BY VendorName HAVING SUM(InvoiceTotal) >= @CutOff); Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 39 A SELECT statement that invokes the function SELECT * FROM dbo.fnTopVendorsDue(5000); The result set Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 40 Use the function in a join operation SELECT Vendors.VendorName, VendorCity, TotalDue FROM Vendors JOIN dbo.fnTopVendorsDue(DEFAULT) AS TopVendors ON Vendors.VendorName = TopVendors.VendorName; The result set Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 41 The syntax for creating a multi-statement table-valued function CREATE FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS @return_variable TABLE (column_name_1 data_type [column_attributes] [, column_name_2 data_type [column_attributes]]...) [WITH [ENCRYPTION] [, SCHEMABINDING] [, EXECUTE_AS_clause]] [AS] BEGIN sql_statements RETURN END Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 42 Create a multi-statement table-valued function CREATE FUNCTION fnCreditAdj (@HowMuch money) RETURNS @OutTable table (InvoiceID int, VendorID int, InvoiceNumber varchar(50), InvoiceDate smalldatetime, InvoiceTotal money, PaymentTotal money, CreditTotal money) BEGIN INSERT @OutTable SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0; WHILE (SELECT SUM(InvoiceTotal - CreditTotal - PaymentTotal) FROM @OutTable) >= @HowMuch UPDATE @OutTable SET CreditTotal = CreditTotal + .01 WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0; RETURN; END; Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 43 A SELECT statement that uses the function SELECT VendorName, SUM(CreditTotal) AS CreditRequest FROM Vendors JOIN dbo.fnCreditAdj(25000) AS CreditTable ON Vendors.VendorID = CreditTable.VendorID GROUP BY VendorName; The response from the system Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 44 The syntax of the DROP FUNCTION statement DROP FUNCTION [schema_name.]function_name [, ...] Note  When you delete a function, any security permissions that are assigned to the function and any dependencies between the function and the tables and views it uses are also deleted. Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 45 The syntax of the ALTER FUNCTION statement for a scalar-valued function ALTER FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS data_type [WITH [ENCRYPTION] [, SCHEMABINDING] [, EXECUTE_AS_clause]] BEGIN [sql_statements] RETURN scalar_expression END Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 46 The syntax for altering a simple table-valued function ALTER FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS TABLE [WITH [ENCRYPTION] [, SCHEMABINDING]] RETURN [(] select_statement [)] Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 47 The syntax for altering a multi-statement table-valued function ALTER FUNCTION [schema_name.]function_name ([@parameter_name data_type [= default]] [, ...]) RETURNS @return_variable TABLE (column_name_1 data_type [column_attributes] [, column_name_2 data_type [column_attributes]]...) [WITH [ENCRYPTION] [, SCHEMABINDING] [, EXECUTE_AS_clause]] BEGIN sql_statements RETURN END Murach's SQL Server 2012, C15 © 2012, Mike Murach & Associates, Inc. Slide 48