SQLServerCh14
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:37:52 | Được cập nhật: 1 tháng 3 lúc 5:18:01 Kiểu file: PPTX | Lượt xem: 198 | Lượt Download: 1 | File size: 1.129751 Mb
Nội dung tài liệu
Tải xuống
Link tài liệu:
Các tài liệu liên quan
Có thể bạn quan tâm
Thông tin tài liệu
Chapter 14
How to code scripts
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given a Transact-SQL script written as a single batch, insert GO
commands to divide the script into appropriate batches.
Given the specification for a database problem, write a script that
solves it.
Use the SQLCMD utility to execute a query or a script.
Knowledge
Describe the use of scripts.
Describe the difference between a scalar variable and a table
variable.
Describe the scope of a local variable.
Describe the scopes of temporary tables, table variables, and
derived tables.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 2
Objectives (cont.)
Describe the use of dynamic SQL.
Given a Transact-SQL script, explain what each statement in the
script does.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 3
A script with two batches
/*
Creates three tables in a database named ClubRoster.
Author:
Bryan Syverson
Created: 2006-08-12
Modified: 2008-09-26
*/
CREATE DATABASE ClubRoster;
GO
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 4
A script with two batches (continued)
USE ClubRoster;
CREATE TABLE Members
(MemberID int NOT NULL IDENTITY PRIMARY KEY,
LastName varchar(75) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) NULL);
CREATE TABLE Committees
(CommitteeID int NOT NULL IDENTITY PRIMARY KEY,
CommitteeName varchar(50) NOT NULL);
CREATE TABLE CommitteeAssignments
(MemberID int NOT NULL REFERENCES Members(MemberID),
CommitteeID int NOT NULL REFERENCES
Committees(CommitteeID));
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 5
Statements that must be in their own batch
CREATE VIEW
CREATE TRIGGER
CREATE PROCEDURE
CREATE SCHEMA
CREATE FUNCTION
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 6
Transact-SQL statements
for controlling the flow of execution
IF...ELSE
BEGIN...END
WHILE
BREAK
CONTINUE
TRY...CATCH
GOTO
RETURN
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 7
Other Transact-SQL statements
for script processing
USE
PRINT
DECLARE
SET
EXEC
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 8
The syntax of the USE statement
USE database
The syntax of the PRINT statement
PRINT string_expression
A script that uses some of the statements
for script processing
USE AP;
DECLARE @TotalDue money;
SET @TotalDue =
(SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal)
FROM Invoices);
IF @TotalDue > 0
PRINT 'Total invoices due =
$' + CONVERT(varchar,@TotalDue,1);
ELSE
PRINT 'Invoices paid in full';
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 9
Terms
Script
Batch
Transact-SQL (T-SQL)
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 10
The syntax of the DECLARE statement
for scalar variables
DECLARE @variable_name_1 data_type
[, @variable_name_2 data_type]...
The syntax of the SET statement
for a scalar variable
SET @variable_name = expression
An alternate syntax for setting a variable’s value
in a select list
SELECT @variable_name_1 = column_specification_1
[, @variable_name_2 = column_specification_2]...
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 11
A SQL script that uses variables
USE AP;
DECLARE @MaxInvoice money, @MinInvoice money;
DECLARE @PercentDifference decimal(8,2);
DECLARE @InvoiceCount int, @VendorIDVar int;
SET @VendorIDVar = 95;
SET @MaxInvoice = (SELECT MAX(InvoiceTotal) FROM Invoices
WHERE VendorID = @VendorIDVar);
SELECT @MinInvoice = MIN(InvoiceTotal), @InvoiceCount =
COUNT(*)
FROM Invoices
WHERE VendorID = @VendorIDVar;
SET @PercentDifference = (@MaxInvoice - @MinInvoice) /
@MinInvoice * 100;
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 12
A SQL script that uses variables (continued)
PRINT 'Maximum invoice is $' +
CONVERT(varchar,@MaxInvoice,1) + '.';
PRINT 'Minimum invoice is $' +
CONVERT(varchar,@MinInvoice,1) + '.';
PRINT 'Maximum is ' + CONVERT(varchar,@PercentDifference) +
'% more than minimum.';
PRINT 'Number of invoices: ' +
CONVERT(varchar,@InvoiceCount) + '.';
The response from the system
Maximum invoice is $46.21.
Minimum invoice is $16.33.
Maximum is 182.97% more than minimum.
Number of invoices: 6.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 13
The syntax of the DECLARE statement
for a table variable
DECLARE @table_name TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...
[, table_attributes])
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 14
A SQL script that uses a table variable
USE AP;
DECLARE @BigVendors table
(VendorID int,
VendorName varchar(50));
INSERT @BigVendors
SELECT VendorID, VendorName
FROM Vendors
WHERE VendorID IN
(SELECT VendorID FROM Invoices
WHERE InvoiceTotal > 5000);
SELECT * FROM @BigVendors;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 15
A script that uses a local temporary table
SELECT TOP 1 VendorID, AVG(InvoiceTotal) AS AvgInvoice
INTO #TopVendors
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC;
SELECT Invoices.VendorID, MAX(InvoiceDate) AS LatestInv
FROM Invoices JOIN #TopVendors
ON Invoices.VendorID = #TopVendors.VendorID
GROUP BY Invoices.VendorID;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 16
Create a global temporary table
of random numbers
CREATE TABLE ##RandomSSNs
(SSN_ID int IDENTITY,
SSN char(9) DEFAULT
LEFT(CAST(CAST(CEILING(RAND()*10000000000) AS bigint)
AS varchar),9));
INSERT ##RandomSSNs VALUES (DEFAULT);
INSERT ##RandomSSNs VALUES (DEFAULT);
SELECT * FROM ##RandomSSNs;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 17
Terms
Variable
Scalar variable
Local variable
Table variable
Temporary table
Local temporary table
Global temporary table
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 18
The five types of Transact-SQL table objects
Type
Standard table
Temporary table
Table variable
Derived table
View
Murach's SQL Server 2012, C14
Scope
Available within the system until
explicitly deleted.
Available within the system while the
current database session is open.
Available within a script while the
current batch is executing.
Available within a statement while
the current statement is executing.
Available within the system until
explicitly deleted.
© 2012, Mike Murach & Associates, Inc.
Slide 19
The syntax of the IF...ELSE statement
IF Boolean_expression
{statement|BEGIN...END}
[ELSE
{statement|BEGIN...END}]
A script that uses an IF statement
USE AP;
DECLARE @EarliestInvoiceDue smalldatetime;
SELECT @EarliestInvoiceDue = MIN(InvoiceDueDate)
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
IF @EarliestInvoiceDue < GETDATE()
PRINT 'Outstanding invoices overdue!';
The response from the system
Outstanding invoices overdue!
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 20
How to code scripts
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given a Transact-SQL script written as a single batch, insert GO
commands to divide the script into appropriate batches.
Given the specification for a database problem, write a script that
solves it.
Use the SQLCMD utility to execute a query or a script.
Knowledge
Describe the use of scripts.
Describe the difference between a scalar variable and a table
variable.
Describe the scope of a local variable.
Describe the scopes of temporary tables, table variables, and
derived tables.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 2
Objectives (cont.)
Describe the use of dynamic SQL.
Given a Transact-SQL script, explain what each statement in the
script does.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 3
A script with two batches
/*
Creates three tables in a database named ClubRoster.
Author:
Bryan Syverson
Created: 2006-08-12
Modified: 2008-09-26
*/
CREATE DATABASE ClubRoster;
GO
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 4
A script with two batches (continued)
USE ClubRoster;
CREATE TABLE Members
(MemberID int NOT NULL IDENTITY PRIMARY KEY,
LastName varchar(75) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) NULL);
CREATE TABLE Committees
(CommitteeID int NOT NULL IDENTITY PRIMARY KEY,
CommitteeName varchar(50) NOT NULL);
CREATE TABLE CommitteeAssignments
(MemberID int NOT NULL REFERENCES Members(MemberID),
CommitteeID int NOT NULL REFERENCES
Committees(CommitteeID));
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 5
Statements that must be in their own batch
CREATE VIEW
CREATE TRIGGER
CREATE PROCEDURE
CREATE SCHEMA
CREATE FUNCTION
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 6
Transact-SQL statements
for controlling the flow of execution
IF...ELSE
BEGIN...END
WHILE
BREAK
CONTINUE
TRY...CATCH
GOTO
RETURN
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 7
Other Transact-SQL statements
for script processing
USE
DECLARE
SET
EXEC
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 8
The syntax of the USE statement
USE database
The syntax of the PRINT statement
PRINT string_expression
A script that uses some of the statements
for script processing
USE AP;
DECLARE @TotalDue money;
SET @TotalDue =
(SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal)
FROM Invoices);
IF @TotalDue > 0
PRINT 'Total invoices due =
$' + CONVERT(varchar,@TotalDue,1);
ELSE
PRINT 'Invoices paid in full';
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 9
Terms
Script
Batch
Transact-SQL (T-SQL)
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 10
The syntax of the DECLARE statement
for scalar variables
DECLARE @variable_name_1 data_type
[, @variable_name_2 data_type]...
The syntax of the SET statement
for a scalar variable
SET @variable_name = expression
An alternate syntax for setting a variable’s value
in a select list
SELECT @variable_name_1 = column_specification_1
[, @variable_name_2 = column_specification_2]...
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 11
A SQL script that uses variables
USE AP;
DECLARE @MaxInvoice money, @MinInvoice money;
DECLARE @PercentDifference decimal(8,2);
DECLARE @InvoiceCount int, @VendorIDVar int;
SET @VendorIDVar = 95;
SET @MaxInvoice = (SELECT MAX(InvoiceTotal) FROM Invoices
WHERE VendorID = @VendorIDVar);
SELECT @MinInvoice = MIN(InvoiceTotal), @InvoiceCount =
COUNT(*)
FROM Invoices
WHERE VendorID = @VendorIDVar;
SET @PercentDifference = (@MaxInvoice - @MinInvoice) /
@MinInvoice * 100;
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 12
A SQL script that uses variables (continued)
PRINT 'Maximum invoice is $' +
CONVERT(varchar,@MaxInvoice,1) + '.';
PRINT 'Minimum invoice is $' +
CONVERT(varchar,@MinInvoice,1) + '.';
PRINT 'Maximum is ' + CONVERT(varchar,@PercentDifference) +
'% more than minimum.';
PRINT 'Number of invoices: ' +
CONVERT(varchar,@InvoiceCount) + '.';
The response from the system
Maximum invoice is $46.21.
Minimum invoice is $16.33.
Maximum is 182.97% more than minimum.
Number of invoices: 6.
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 13
The syntax of the DECLARE statement
for a table variable
DECLARE @table_name TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...
[, table_attributes])
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 14
A SQL script that uses a table variable
USE AP;
DECLARE @BigVendors table
(VendorID int,
VendorName varchar(50));
INSERT @BigVendors
SELECT VendorID, VendorName
FROM Vendors
WHERE VendorID IN
(SELECT VendorID FROM Invoices
WHERE InvoiceTotal > 5000);
SELECT * FROM @BigVendors;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 15
A script that uses a local temporary table
SELECT TOP 1 VendorID, AVG(InvoiceTotal) AS AvgInvoice
INTO #TopVendors
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC;
SELECT Invoices.VendorID, MAX(InvoiceDate) AS LatestInv
FROM Invoices JOIN #TopVendors
ON Invoices.VendorID = #TopVendors.VendorID
GROUP BY Invoices.VendorID;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 16
Create a global temporary table
of random numbers
CREATE TABLE ##RandomSSNs
(SSN_ID int IDENTITY,
SSN char(9) DEFAULT
LEFT(CAST(CAST(CEILING(RAND()*10000000000) AS bigint)
AS varchar),9));
INSERT ##RandomSSNs VALUES (DEFAULT);
INSERT ##RandomSSNs VALUES (DEFAULT);
SELECT * FROM ##RandomSSNs;
The result set
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 17
Terms
Variable
Scalar variable
Local variable
Table variable
Temporary table
Local temporary table
Global temporary table
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 18
The five types of Transact-SQL table objects
Type
Standard table
Temporary table
Table variable
Derived table
View
Murach's SQL Server 2012, C14
Scope
Available within the system until
explicitly deleted.
Available within the system while the
current database session is open.
Available within a script while the
current batch is executing.
Available within a statement while
the current statement is executing.
Available within the system until
explicitly deleted.
© 2012, Mike Murach & Associates, Inc.
Slide 19
The syntax of the IF...ELSE statement
IF Boolean_expression
{statement|BEGIN...END}
[ELSE
{statement|BEGIN...END}]
A script that uses an IF statement
USE AP;
DECLARE @EarliestInvoiceDue smalldatetime;
SELECT @EarliestInvoiceDue = MIN(InvoiceDueDate)
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
IF @EarliestInvoiceDue < GETDATE()
PRINT 'Outstanding invoices overdue!';
The response from the system
Outstanding invoices overdue!
Murach's SQL Server 2012, C14
© 2012, Mike Murach & Associates, Inc.
Slide 20