掌握Visual Basic .NET与Transact-SQL（T-SQL）：服务器端复杂任务编程

需积分: 8 65 浏览量更新于2024-07-17 收藏 205KB PDF 举报

"《Sybex - Mastering Visual Basic .NET Transact SQL (VBL)》一书深入介绍了如何在Visual Basic .NET环境中利用Transact-SQL (T-SQL)进行数据库操作。章节20主要讲解了如何编写SQL语句来检索和更新数据库中的数据，以及对SELECT语句的各种变体的掌握。然而，有些复杂的搜索限制不能仅仅通过WHERE子句表达，这时程序员可能倾向于编写应用程序，通过先从数据库中获取所需数据，然后进行处理，比如计算汇总、格式化查询结果、涉及多行表格的复杂计算等。 T-SQL作为SQL Server的扩展，增强了SQL的功能，使其更接近于编程语言。它支持从一个或多个表中获取数据，包括流程控制语句如IF…ELSE和WHILE，以及各种字符串、数值和日期处理函数，类似于Visual Basic中的函数。通过T-SQL，不仅可以执行标准SQL的所有操作，还可以进行程序化的任务。例如，可以将复杂的SQL语句存储为存储过程，通过名称调用，甚至可以使用参数，以便同一个存储过程能处理不同数据。本章是T-SQL的概述，主要聚焦于使用存储过程来在服务器上执行复杂的任务，如使用COMPUTE BY语句对检索到的数据进行群体总计。虽然这种语句与标准SQL类似，但并非其组成部分。对于初学者，这是学习T-SQL的基础，因为它是SQL Server的核心语言，对于在SQL Server上工作或打算转换的开发者来说，了解和掌握T-SQL是必不可少的。此外，通过对比T-SQL和Visual Basic的基本操作实现，读者能更好地理解数据库编程的原理。阅读这一章节，不仅能提升T-SQL技能，还能间接提升Visual Basic编程能力，因为T-SQL的语法和逻辑与VB有很多相似之处。理解这两种语言在数据库操作中的协作，有助于构建更高效和灵活的数据库应用。"

COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *

[Order Details].UnitPrice), 2))

BY Customers.CustomerID, Orders.OrderID

COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *

[Order Details].UnitPrice), 2))

BY Customers.CustomerID

Because this is not actually a SQL statement, the first time you execute it, it will not return the list

of invoices. Instead, it will add the AllInvoices procedure to the current database—so be sure to

select the Northwind database in the DB drop-down list, or use the USE keyword to make North-

wind the active database.

If the procedure exists already, you can’t create it again. You must either drop it from the data-

base with the DROP PROCEDURE statement, or modify it with the ALTER PROCEDURE

statement. The syntax of the ALTER PROCEDURE statement is identical to that of the CRE-

ATE PROCEDURE statement. By replacing the CREATE keyword with the ALTER keyword,

you can replace the definition of an existing procedure.

A common approach is to test for the existence of a stored procedure and drop it if it exists. Then,

you can add a new procedure with the CREATE PROCEDURE statement. For example, if you are

not sure the

myProcedure

procedure exists, use the following statements to find and modify it:

USE DataBase

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ‘myProcedure’)

DROP PROCEDURE myProcedure

CREATE PROCEDURE myProcedure

. . .

The SELECT statement retrieves the name of the desired procedure from the database objects

(again, be sure to execute it against the desired database). If a procedure by the name

myProcedure

exists already, EXISTS returns True and drops the procedure definition from the database. Then it

proceeds to add the revised definition.

Once you’ve entered Listing 4 into the Query Analyzer, press Ctrl+E to execute the procedure’s

declaration. If you haven’t misspelled any keywords, the message “The command(s) completed suc-

cessfully.” will appear in the lower pane of the Query Analyzer’s window. When you execute a stored

procedure’s definition, you add it to the database, but the procedure’s statements are not executed.

To execute a stored procedure, you must use the EXECUTE statement (or its abbreviation,

EXEC) followed by the name of the procedure. Assuming that you have created the AllInvoices pro-

cedure, here’s how to execute it.

1. First, clear the Query pane of Query Analyzer, or open a new window in the Query Analyzer.

2. In the fresh Query pane, type:

USE Northwind

EXECUTE AllInvoices

and press Ctrl+E. The result of the query will appear in the Results pane of the Query Analyzer.

Bonus Reference TRANSACT-SQL

chT8

The first time you execute the procedure, SQL Server will put together an execution plan, so it

will take a few seconds. After that, the procedure’s execution will start immediately, and the rows will

start appearing on the Results pane as soon as they become available.

Tip If a procedure takes too long to execute, or it returns too many rows, you can interrupt it by clicking the Stop but-

ton (a red rectangular button on SQL Server’s toolbar). If you execute an unconditional join by mistake, for example, you

can stop the execution of the query and not have to wait until all rows arrive.

The USE statement isn’t necessary, as long as you remember to select the proper database in the

Analyzer’s window. Since the stored procedure is part of the database, it’s very unlikely that you will

call a stored procedure that doesn’t belong to the current database.

Executing Command Strings

In addition to executing stored procedures, you can use the EXECUTE statement to execute strings

with valid T-SQL statements. If the variable

@TSQLcmd

contains a valid SQL statement, you can

execute it by passing it as an argument to the EXECUTE procedure:

EXECUTE (@TSQLcmd)

The parentheses are required. If you omit them, SQL Server will attempt to locate the

@TSQLcmd

stored procedure. Here’s a simple example of storing SQL statements into variables and executing

them with the EXECUTE method:

DECLARE @Country varchar(20)

DECLARE @TSQLcmd varchar(100)

SET @Country = ‘Germany’

SET @TSQLcmd = ‘SELECT City FROM Customers WHERE Country=”’ + @Country + ‘“‘

EXECUTE (@TSQLcmd)

Tip All T-SQL variables must begin with the @ symbol.

All T-SQL variables must be declared with the DECLARE statement, have a valid data type, and

be set with the SET statement. You will find more information on the use of variables in the follow-

ing sections of this chapter.

The EXECUTE statement with a command string is commonly used to build SQL statements

on the fly. You’ll see a more practical example of this technique in the section “Building SQL State-

ments on the Fly,” later in this chapter.

Tip Statements that are built dynamically and executed with the help of a command string as explained in this section do

not take advantage of the execution plan. Therefore, you should not use this technique frequently. Use it only when you

can’t write the stored procedure at design time.

Why Use Stored Procedures?

Stored procedures are far more than a programming convenience. When a SQL statement, especially

a complicated one, is stored in the database, the database management system (DBMS) can execute

it efficiently. To execute a SQL statement, the query engine must analyze it and put together an

execution plan. The execution plan is analogous to the compilation of a traditional application. The

chT9

STORED PROCEDURES

剩余40页未读，继续阅读

drjiachen

粉丝: 172
资源: 2138

掌握Visual Basic .NET与Transact-SQL（T-SQL）：服务器端复杂任务编程

Sybex - Mastering Visual Basic .NET (VBL).pdf

Sybex - Mastering Visual Basic .NET VB NET Functions (VBL).pdf

Sybex.Mastering.Microsoft.Visual.Basic.2008.Mar.2008

Sybex.-.Mastering.Windows.Server.2003.part1

Sybex.-.Mastering.Windows.Server.2003.part2

SYBEX - Mastering UML with Rational Rose

Sybex.Mastering.VMware.vSphere.4.Aug.2009.pdf

SYBEX-Mastering_Active_Directory_For_Windows_Server_2003

Sybex.Mastering.SQL.Server.2008

Sybex.Visual.C.Sharp.Dot.NET.Programming.eBook-LiB

最新资源