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

SQLServerCh14

d34ae5df913c34b13e8070f6e27b8ec2
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:
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 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