sql conditional statement

2010-07-21  来源:本站原创  分类:Database  人气:161 

Transact-SQL language used in process control commands and a common programming language similar to the main there are several control commands.
4.6.1 IF ... ELSE
The syntax is as follows:
IF <conditional expression>
"Command-line or block>
[ELSE [conditional expression]
"Command-line or block>]
Where <conditional expression> may be a combination of various expressions, but the value of the expression must be a logical value "true" or "false." ELSE clause is optional, the easiest part of the IF statement is not ELSE clause. IF ... ELSE conditions used to determine when the establishment of an implementation of certain procedures, conditions are not established procedures for the implementation of another paragraph. If you do not use the blocks, IF or ELSE can only execute a command. IF ... ELSE can be nested.

Cases 4-9
Declare @ x int, @ y int, @ z int
select @ x = 1, @ y = 2, @ z = 3
if @ x> @ y
print'x> y '- print the string' x> y '
else IF @ y> @ z
print'y> z '
else print'z> y '
The results are
z> y
Note: In the Transact-SQL can be nested up to 32.
4.6.2 BEGIN ... END
The syntax is as follows:
BEGIN
"Command-line or block>
END
BEGIN ... END block is used to set up a program will BEGIN ... END all the procedures within the unit as a regular implementation of BEGIN ... END statement in the conditions, such as IF ... ELSE use. In the BEGIN ... END can be nested in other BEGIN ... END block to define another.
4.6.3 CASE
There are two statements CASE command format:
CASE <expression>
WHEN <expression> THEN <expression>
...
WHEN <expression> THEN <expression>
[ELSE <expression>]
END
CASE
WHEN <conditional expression> THEN <expression>
WHEN <conditional expression> THEN <expression>
[ELSE <expression>]
END
CASE command can be nested to SQL command.
Cases 4-10: Adjusting wages, working-level "1" and up 8% of working-level "2" and up 7% of working-level "3" and up 6%, other 5% rise.
use pangu
update employee
set e_wage =
case
when job_level = '1 'then e_wage * 1.08
when job_level = '2 'then e_wage * 1.07
when job_level = '3 'then e_wage * 1.06
else e_wage * 1.05
end
Note: The implementation of the CASE clause, only to run the first matching sub-name.
4.6.4 WHILE ... CONTINUE ... BREAK
The syntax is as follows:
WHILE <conditional expression>
BEGIN
"Command-line or block>
[BREAK]
[CONTINUE]
[Command line or block]
END
WHILE command will set the conditions for setting up repeat the command line or block. CONTINUE command command after the program to skip the CONTINUE statement, WHILE loop back to the first line of command. BREAK command will let the program completely out of circulation, end WHILE command execution. WHILE statements can be nested.
Cases 4-11:
declare @ x int @ y int @ c int
Cases 4-11:
declare @ x int, @ y int, @ c int
select @ x = 1, @ y = 1
while @ x <3
begin
print @ x - prints the value of the variable x
while @ y <3
begin
select @ c = 100 * @ x + @ y
print @ c - prints the value of the variable c
select @ y = @ y + 1
end
select @ x = @ x + 1
select @ y = 1
end
Run the following results
1
101
102
2
201
202

4.6.5 WAITFOR
The syntax is as follows:
WAITFOR (DELAY <'time'> | TIME <'time'>
| ERROREXIT | PROCESSEXIT | MIRROREXIT)
WAITFOR command is used to temporarily stop the program execution, wait until the set time has elapsed or the set time has come for it to continue down the execution. Which 'time' must DATETIME types of data, such as: '11: 15:27 '
But not including the date of the keyword meanings are as follows:
· DELAY is used to set the waiting time of up to 24 hours;
· TIME used to set the end of time waiting;
· ERROREXIT until the process is interrupted abnormally;
· PROCESSEXIT until interrupt handling normal and abnormal;
· MIRROREXIT until the mirror device failure.
Patients waiting 4-12 1 hour 2 minutes 3 seconds after the implementation of the SELECT statement
waitfor delay '01: 02:03 '
select * from employee
Cases 4-13: 8 until 23:00 pm after the implementation of the SELECT statement
waitfor time '23: 08:00 '
select * from employee
4.6.6 GOTO
Syntax is as follows:
GOTO identifier
GOTO command is used to change the procedures for the implementation of the process, make the program jump to the specified identifier marked line and then continue down the implementation process. Identifier as a jump target for the combination of numbers and characters, but must ":" at the end, such as '12: 'or' a_1: '. In the GOTO command line, after the identifier do not like ":."
Patients with 4-14 branches print characters '1 ', '2', '3 ', '4', '5 '
declare @ x int
select @ x = 1
label_1
print @ x
select @ x = @ x + 1
while @ x <6
goto label_1
4.6.7 RETURN
Syntax
RETURN [integer]
RETURN command to end the current execution of the program, calling it a return to the program or other programs. Within the brackets to specify a return value.
Cases 4-15
declare @ x int @ y int
select @ x = 1 @ y = 2
if x> y
return 1
else
return 2
If the return value is not specified, SQL Server system will process the results of the implementation of a pre-determined return value, as shown in Table 4-5.

If you run the process led to multiple errors, SQL Server system will return the largest absolute value; If the user then defines the return value, Zeyi return user-defined value. RETURN statement can not return NULL values.

相关文章
  • sql conditional statement 2010-07-21

    Transact-SQL language used in process control commands and a common programming language similar to the main there are several control commands. 4.6.1 IF ... ELSE The syntax is as follows: IF <conditional expression> "Command-line or block>

  • PL / SQL conditional control (b) 2010-11-29

    1.IF conditional statement In the PL / SQL statement block, IF statements can contain IF, ELSIF, ELSE, THEN, END IF, etc. keyword, its complete syntax is: IF condition1 THEN ... ... ... ...; [ELSIF condition2 THEN ... ... ... ...;] [ELSE ... ... ...

  • SQL action statement 2010-05-10

    Mssql statement following statement in part, can not be used in access. SQL Category: DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE) DML-Data Manipulation Language (SELECT, DELETE, UPDATE, INSERT) DCL-Data Control Language (GRANT, REVOKE

  • SQL INSERT statement in the use of techniques 2011-08-06

    In the SQL database operations, insert one of the most common operation, the following SQL statement to introduce you to some of the techniques to use INSERT statement for your reference, hope you find inspiration. (Insert) to the table to add a new

  • SQL Select statement is a complete implementation of the order: 2011-04-12

    SQL Select statement is a complete implementation of the order: 1, from clause assemble data from different data sources; 2, where clause based on specified criteria to filter the rows of records; 3, group by clause divides the data into multiple pac

  • sql replace statement batch changes. increase to delete the field contents 2011-09-25

    sql replace statement, with the bulk of the command can replace the contents of a field, you can batch add content in the original field or remove the character. Order total solution: update table name set the Replace field = REPLACE (Replace field,

  • SQL (select) statement optimization 2011-09-29

    About select * from O1, O2 ...... As to why the database should be optimized, not repeat them here. Doing the project, how to maximize the efficiency of the sql query should be a topic we always: Here, the spirit of caution, simple manner, a little b

  • SQL paging statement 2010-02-04

    Many pages of information about SQL, and some use of stored procedures, and some use the cursor. I do not like to use a cursor, I think it cost and low efficiency; use the stored procedure is a good choice, because the stored procedure is precompiled

  • mysql limit the variables after the issue, sql prepare statement 2010-03-27

    MySQL 5.0 from the beginning, Support a new SQL syntax: PREPARE stmt_name FROM preparable_stmt; EXECUTE stmt_name [USING @ var_name [, @ var_name] ...]; (DEALLOCATE | DROP) PREPARE stmt_name; Through it, we can achieve similar MS SQL to perform dynam

  • sql like statement wild card: a percent sign. underscores and escape 2010-03-31

    As we usually use the * and?, Sql's like statement, use% and _ to represent any number of characters and a character # % Represents any number of characters select * from user where username like '%huxiao'; select * from user where username like 'hux

  • java.sql.SQLException: statement handle not implemented 2010-06-01

    Failed to Find: sql = select type,name,description,remark,tableinfoid from tablefield where tableinfoid = ( select id from tableinfo where name = 'NEWSPAPER') order by name java.sql.SQLException: Does not execute the statement handle at oracle.jdbc.d

  • To Excel, the data into sql Insert statement 2010-06-29

    1. Or use sql Server 2000 there is to import the mdb file, Access 2003 in the way but there is a series of questions. excel table with A, B, C three columns of data, users want to import into a database table, the corresponding fields are name, sex,

  • How to use the SQL UPDATE statement to update the joint table 2010-09-06

    In the development of the database back and forth exchange, and some key has different syntax, which is a big headache for developers to do. This paper summarizes the Update statement updating multiple tables in SQL Server, Oracle, MySQL database usa

  • instance of sql server statement optimization 2010-11-10

    SQL statement optimization a very important way: Where the conditions in the expression of the query fields do not function as much as possible, because the query field of the index will not have access. Here there is a leading example of a database

  • How to use the SQL UPDATE statement to update the contingency table 2010-12-09

    This article reproduced from http://wing123.javaeye.com/blog/756273 In the development of the database back and forth exchange, and some key grammar and different, this is a headache for developers to do. This paper summarizes the Update statement to

  • To_char sql database statement optimization [mysql and oracle] 2011-01-10

    This problem was first discovered in the Maimaiti analysis, if used in a database sql statement select * from table where to_char ('table in the date field', 'YYYY-MM-DD') = '2011-01-11 ', If the limited data in the table, then execute the statement,

  • Oracle SQL classic statement 2011-04-07

    First, the basic 1, Description: Create a database CREATE DATABASE database-name 2, Description: Removes database drop database dbname 3, Description: backup sql server --- Create a backup of data deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBac

  • sql injection statement preparedstatement 2011-09-23

    sql injection principle statement preparedstatement http://blog.sina.com.cn/s/blog_6a384fce0100n95f.html ------ The following switched ikon.iteye.com/blog/1132255 First, let's look at the sql execution: In Oracle executes a SQL statement, the general

  • Sql query statement in chronological (date type field is) 2010-03-25

    Question: database a table, define fields as id (int), name (varchar), birthday (date) Recorded as follows: id name birthday 1 zhangsan 2009-12-30 00:00:00 2 lisi 2010-01-30 00:00:00 3 yy 2009-12-02 00:00:00 If I need to check the records in December

  • sql insert statement dealing with special characters 2011-10-04

    Reproduced: http://bbs2.chinaunix.net/archiver/tid-617054.html How to insert special symbols to the database. sql = "insert into webPageInfo (webAddrees, content) values ​​('" + thisURL +"','"+ Webtext +"')"; Webtext is the f