过程化SQL

变量和常量的定义

变量

1
2
3
4
5
6
7
DELCARE @变量名 数据类型
--定义实例
DECLARE @name NVARCHAR(10) -- 使用DECLARE 语句声明局部变量
SET @name = 'TOM' -- 使用 SET 语句赋值
PRINT @name -- PRINT 在查询编辑器上显示结果
SELECT * FROM employee
PRINT @@ROWCOUNT

常量

流程控制

IF…ELSE语句

1
2
3
4
IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ]

BEGIN…AND语句

BEGIN...END 用来设定一个程序块，将在 BEGIN...END 内的所有程序设为一个单元执行；
BEGIN...END 经常在条件语句（如 IF...ELSE）中使用，在 BEGIN...END 中可嵌套另外的 BEGIN...END 来定义另一程序块；
语法格式如下：

1
2
3
4
5
6
BEGIN
{
    sql_statement
    | statement_block    
}
END

CASE语句

1
2
3
4
5
6
7
8
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE @score INT
SET @score = 100

SELECT CASE @score 
            WHEN 100 THEN '满分'
            WHEN 60 THEN '及格'
        END
        AS '成绩'
DECLARE @score INT
SET @score = 100

SELECT CASE 
            WHEN @score >= 90 THEN '优秀'
            WHEN @score >= 80 THEN '良好'
            WHEN @score >= 70 THEN '中等'
            WHEN @score >= 60 THEN '及格'
            ELSE '不及格'
        END
        AS '成绩'

WHILE语句

1
2
WHILE Boolean_expression 
     { sql_statement | statement_block | BREAK | CONTINUE }

##WAITFOR语句

1
2
3
4
5
6
7
WAITFOR 
{
    DELAY 'time_to_pass' 
  | TIME 'time_to_execute' 
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ] 
    [ , TIMEOUT timeout ]
}

DELAY参数

DELAY参数指定了等待的时间段。不能指定天数，只能指定小时数、分钟数和秒数。允许延迟的最长时间为24小时。

1
WAITFOR DELAY '01：00'

将运行WAITFOR语句前的任何代码，然后到达WAITFOR语句，停止1小时，之后继续执行下一条语句中的代码。

TIME参数

TIME参数指定到达指定时间的等待时间。

1
WAITFOR TIME '01：00'

　　将运行WAITFOR语句前的任何代码，然后到达WAITFOR语句，直到凌晨1点停止执行，之后执行WAITFOR语句后的下一条语句。

存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

优点

  • 存储过程不像解释执行的sql语句那样在提出操作请求时才进行语法分析和优化工作，因而运作效率高，它提供了在服务器端快速执行sql语句的有效途径。
  • 存储过程降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出存储过程名字和参数，就可以让关系型数据库管理系统执行其中的多条sql语句并进行数据处理。只有最终的处理结果才返回客户端。
  • 方便实施企业规则。可以在企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库管理系统管理，既有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只要修改存储过程即可，无需修改其它应用程序。

创建存储过程

1
2
3
4
5
6
7
8
9
CREATE PROC | PROCEDURE pro_name
    [{@参数 数据类型} [=默认值] [OUTPUT],
     {@参数 数据类型} [=默认值] [OUTPUT],
     ....
    ]
[ WITH{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS
    SQL_statements

修改存储过程

1
2
3
4
5
6
7
ALTER PROC | PROCEDURE pro_name
[{@参数 数据类型} [=默认值] [output],
     {@参数 数据类型} [=默认值] [output],
     ....
    ]
AS
	SQL_statements

重命名

1
ALTER PROC | PROCEDURE pro_name RENAME TO new_name;

删除存储过程

1
DROP PROC | PROCEDURE pro_name;

调用存储过程

1
2
EXEC | EXECUTE pro_name;
EXEC | EXECUTE pro_name [@参数名='value'或者'value'] ...;