Fundamentals of Transact-SQL

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:10

Thêm vào BST

Báo xấu

76
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Fundamentals of Transact-SQL In this section, you'll learn some of the essential programming constructs available in TSQL. Specifically, you'll see how to use variables, comments

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Fundamentals of Transact-SQL

Fundamentals of Transact-SQL In this section, you'll learn some of the essential programming constructs available in T- SQL. Specifically, you'll see how to use variables, comments, and conditional logic. You'll also see how to use a number of statements that allow you to perform jumps and loops. Finally, you'll examine cursors, which allow you to process rows returned from the database one at a time. Let's start by looking at variables. Using Variables A variable allows you to store a value in the memory of a computer. Each variable has a type that indicates the kind of value that will be stored in that variable. You can use any of the types shown earlier in Table 2.3 of Chapter 2, "Introduction to Databases." You declare a variable using the DECLARE statement, followed by the variable name and the type. You place an at character (@) before the start of the variable name. The following syntax illustrates the use of the DECLARE statement: DECLARE @name type Where name is the name of your variable, and type is the variable type. For example, the following statements declare two variables named MyProductName and MyProductID: DECLARE @MyProductName nvarchar(40) DECLARE @MyProductID int As you can see, MyProductName is of the nvarchar type, and MyProductID is of the int type. You can place more than one variable declaration on the same line. For example: DECLARE @MyProductName nvarchar(40), @MyProductID int Variables are initially set to null. You set a variable's value using the SET statement. For example, the following statements set MyProductName to Chai and MyProductID to 7: SET @MyProductName = 'Chai' SET @MyProductID = 7
The following SELECT statement then uses these variables in the WHERE clause: SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID = @MyProductID OR ProductName = @MyProductName; You can execute T-SQL using Query Analyzer, and Figure 4.1 shows the output from the examples shown in this section. Figure 4.1: Executing T-SQL using Query Analyzer Using Comments You add comments to describe your code, making it more understandable for both yourself and other programmers. You might think you understand your own code inside out, but when you return to it for maintenance six months later, you might have forgotten the intricacies of your own creation! The point is that you should add comments to your code to aid understanding, but don't think you have to comment every line. Use comments judiciously. You need to mark your comments with specific characters so SQL Server ignores them and doesn't try to process them as code. There are two types of comments: single-line and multi-line. A single-line comment uses two negative signs (--) and may span only one line, as shown here: -- A single-line comment may only span one line. The -- tells SQL Server to ignore everything up to the end of that line. A multi-line comment begins with an open comment mark (/*) and ends with a close comment mark (*/): /* A multi-line comment
may span more than one line. */ The /* tells SQL Server to ignore everything up to the next */ mark, no matter how many lines forward it is. If you were to use single-line comments in this example, you would have to add -- characters at the beginning of every line that made up the comment. Multi-line comments can of course also span only one line: /* Another comment */ Using Conditional Logic Conditional logic allows you to execute different branches of code based on the Boolean true or false value of a given expression. For example, you might want to check if an error condition is true and display a message. You use the IF and optional ELSE keywords to perform conditional logic. The following syntax illustrates the use of conditional logic: IF condition statement1 [ELSE statement2] Where condition is a Boolean expression that evaluates to true or false. If condition is true, then statement1 is executed, otherwise statement2 is executed. Note You can replace a single statement with multiple statements by placing those statements within BEGIN and END statements. This rule applies to all T-SQL programming constructs. The following syntax shows the replacement of single statements with a block of statements placed within BEGIN and END: IF condition BEGIN statements1 END ELSE BEGIN statements2 END
Where statements1 and statements2 are multiple statements. You can also use an optional ELSE statement to execute a different branch of code if the condition is false. Note You can nest IF statements to any level. The following example displays the ProductID, ProductName, and UnitPrice columns for any rows from the Products table that have a UnitPrice of less than $5. You'll notice the use of the PRINT statement to output a line in this example. IF (SELECT COUNT(*) FROM Products WHERE UnitPrice < 5) > 0 BEGIN PRINT 'The following products have a UnitPrice of less than $5:' SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice < 5 END ELSE BEGIN PRINT 'There are no products that have a UnitPrice of less than $5' END Using CASE Statements You use the CASE statement to compare a value against a list of values and execute one or more statements when a match is found. For example, the following CASE statement returns Massachusetts: CASE 'MA' WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York' END The next example uses a SELECT statement to retrieve the value Massachusetts returned by the CASE statement: DECLARE @State nchar(2) SET @State = 'MA' DECLARE @StateName nvarchar(15) SELECT CASE @State WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York'
END You can store the value retrieved by the SELECT statement in a variable, as shown in the next example: DECLARE @State nchar(2) SET @State = 'MA' DECLARE @StateName nvarchar(15) SELECT @StateName = CASE @State WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York' END PRINT @StateName The output from this example is as follows: Massachusetts You can also compare a column value in a CASE statement. For example: SELECT Price = CASE WHEN UnitPrice IS NULL THEN 'Unknown' WHEN UnitPrice < 10 THEN 'Less than $10' WHEN UnitPrice = 10 THEN '$10' ELSE 'Greater than $10' END FROM Products You'll notice from this example that you can also supply a catchall ELSE condition in a CASE statement. Using WHILE Loops You use a WHILE loop to run one or more statements multiple times. A WHILE loop runs until a specified condition evaluates to false. The syntax for a WHILE loop is as follows: WHILE condition statement
The following example shows a WHILE loop: DECLARE @count int SET @count = 5 WHILE (@count > 0) BEGIN PRINT 'count = ' + CONVERT(nvarchar, @count) SET @count = @count -1 END This loop runs until the count variable reaches 0, and the output from this code is as follows: count = 5 count = 4 count = 3 count = 2 count = 1 The CONVERT() function is used to convert a value from one type to another. For example, CONVERT (nvarchar, @count) converts the count variable to the nvarchar type, which can then be used with the PRINT statement. CONTINUE Statement You use the CONTINUE statement to start the next iteration of a WHILE loop immediately, skipping over any remaining code in the loop. The CONTINUE statement causes execution to jump back to the start of the loop. The following example shows a WHILE loop that uses the CONTINUE statement to start the next iteration of the loop if the count variable is equal to 2: DECLARE @count int SET @count = 5 WHILE (@count > 0) BEGIN PRINT 'count = ' + CONVERT(nvarchar, @count) SET @count = @count -1 IF (@count = 2) BEGIN SET @count = @count 1 CONTINUE
END END The output from this code is as follows: count = 5 count = 4 count = 3 count = 1 You'll notice that the display of count = 2 is missing. This is because the CONTINUE statement skips that iteration. BREAK Statement You use the BREAK statement to end a WHILE loop immediately. The BREAK statement causes execution to jump out of the loop and continue executing any statements after the loop. The following example shows a WHILE loop that uses the BREAK statement to end the loop if the count variable is equal to 2: DECLARE @count int SET @count = 5 WHILE (@count > 0) BEGIN PRINT 'count = ' + CONVERT(nvarchar, @count) SET @count = @count -1 IF (@count = 2) BEGIN BREAK END END The output from this code is as follows: count = 5 count = 4 count = 3 Using Labels and the GOTO Statement You use the GOTO statement to jump to a specified label in your code; you use a label to identify a statement in your code. You must define the label before issuing the GOTO to
that label. Before I show you the details of the GOTO statement, you should be aware that its use is considered poor programming practice, and you should avoid it if at all possible. It is usually possible to structure code so that you don't need to use the GOTO statement. Having said that, I've included it in this chapter for completeness. As mentioned, the GOTO statement requires that you create a label in your program. You do this by placing an identifier containing the label name in your code, followed by a colon (:). The following example creates a label named myLabel: myLabel: You may then use the GOTO statement to jump to that label, for example: GOTO myLabel The following example shows the use of a label and the GOTO statement: DECLARE @count int SET @count = 5 myLabel: PRINT 'count = ' + CONVERT(nvarchar, @count) SET @count = @count -1 IF (@count > 0) BEGIN GOTO myLabel END The output from this code is as follows: count = 5 count = 4 count = 3 count = 2 count = 1 Using RETURN Statements You use the RETURN statement to exit from a stored procedure or group of statements. Any statements that follow your return are not executed. You can also return a value using the RETURN statement. The syntax for the RETURN statement is as follows:
RETURN [int_expression] Where int_expression is any expression that evaluates to an int value. Note You can return a value only when using the RETURN statement with a stored procedure. You'll see an example of that later in the "Introducing Stored Procedures" section. The following example shows the use of the RETURN statement: DECLARE @count int SET @count = 5 WHILE (@count > 0) BEGIN PRINT 'count = ' + CONVERT(nvarchar, @count) SET @count = @count -1 IF (@count = 2) BEGIN RETURN END END The output from this code is as follows: count = 5 count = 4 count = 3 Using WAITFOR Statements There are times when you want your program to pause before running some code to perform a specific action, such as running a batch program at night to update customer records. You use the WAITFOR statement to specify a time interval or time to wait until continuing execution of code. The syntax for the WAITFOR statement is as follows: WAITFOR {DELAY 'time interval' | TIME 'actual time'} You can specify the time interval to wait using the DELAY keyword, or you can specify the actual time to wait until using the TIME keyword. You can specify a time interval or an actual time in the format HH:MM:SS, where HH is the hour (in 24-hour format), MM is the minute, and SS is the second.
Here are some examples: • WAITFOR DELAY '00:00:05' waits for a time interval of 5 seconds. • WAITFOR DELAY '23:10:25' waits for a time interval of 23 hours, 10 minutes, and 25 seconds. • WAITFOR TIME '20:15:10' waits until 10 seconds after 10:15 PM. The following example prints a message after 5 seconds have elapsed: BEGIN WAITFOR DELAY '00:00:05' PRINT '5 seconds have elapsed' END Using RAISERROR Statements You use the RAISERROR statement to generate an error message. You'll typically want to do this if an error occurs in one of your stored procedures, which you'll see how to use later in the section "Creating Stored Procedures." The simplified syntax for the RAISERROR statement is as follows: RAISERROR ({number | description}{, severity, state}) Where number is the error number, which must be between 50,001 and 2,147,483,648. The description is a message that cannot exceed 400 characters. The severity is the degree of the error and must be between 0 and 18 (18 is the most severe error). The state is an arbitrary value that must be between 1 and 127, and represents information about the invocation state of the error. The following examples show the use of the RAISERROR statement: RAISERROR (50001, 15, 1) RAISERROR ('No row with that ProductID was found', 10, 1)