SQL Server 存储过程 简略手记

创建时的基本定义与调用

1> CREATE PROCEDURE HelloWorld AS
2> BEGIN
3> PRINT 'Hello World';
4> END;
5> go

1> DECLARE @RC int
2> EXECUTE @RC = HelloWorld
3> PRINT @RC
4> go
Hello World
0

修改存储过程
5> ALTER PROCEDURE HelloWorld AS
6> BEGIN
7> PRINT 'Hello World V2.0';
8> END;
9> go

1> DECLARE @RC int
2> EXECUTE @RC = HelloWorld
3> PRINT @RC
4> go
Hello World V2.0
0

参数定义
单个参数
1> CREATE PROCEDURE HelloWorld1
2> @UserName VARCHAR(10)
3> AS
4> BEGIN
5> PRINT 'Hello' + @UserName + '!';
6> END;
7> go
1> DECLARE @RC int;
2> EXECUTE @RC = HelloWorld1 'Edward' ;
3> PRINT @RC;
4> go
HelloEdward!
0

IN、OUT、IN OUT
注：
SQL Server 的 OUTPUT 需要写在变量数据类型后面。

SQL Server 没有 IN OUT 关键字
OUTPUT 已经相当于 IN OUT 了。

1> CREATE PROCEDURE HelloWorld2
2> @UserName VARCHAR(10),
3> @OutVal VARCHAR(10) OUTPUT,
4> @InoutVal VARCHAR(10) OUTPUT
5> AS
6> BEGIN
7> PRINT 'Hello ' + @UserName + @InoutVal + '!';
8> SET @OutVal = 'A';
9> SET @InoutVal = 'B';
10> END;
11> go

1>
2> DECLARE @RC int, @OutVal VARCHAR(10), @InoutVal VARCHAR(10);
3> BEGIN
4> SET @InoutVal = '~Hi~';
5> EXECUTE @RC = HelloWorld2 'Edward', @OutVal OUTPUT, @InoutVal OUTPUT;
6> PRINT @RC;
7> PRINT [email protected]=' + @OutVal;
8> PRINT [email protected]=' + @InoutVal;
9> END
10> go
Hello Edward~Hi~!
0
@OutVal=A
@InoutVal=B

参数的默认值
1> CREATE PROCEDURE HelloWorld3
2> @UserName VARCHAR(10),
3> @Val1 VARCHAR(20) = ' Good Moning,',
4> @Val2 VARCHAR(20) = ' Nice to Meet you'
5> AS
6> BEGIN
7> PRINT 'Hello ' + @UserName + @Val1 + @Val2 + '!';
8> END;
9> go
1>
2> DECLARE @RC int;
3> BEGIN
4> EXECUTE @RC = HelloWorld3 'Edward';
5> PRINT @RC;
6> EXECUTE @RC = HelloWorld3 'Edward', ' Good Night,';
7> PRINT @RC;
8> EXECUTE @RC = HelloWorld3 'Edward', ' Good Night,', ' Bye';
9> PRINT @RC;
10> END
11> go
Hello Edward Good Moning, Nice to Meet you!
0
Hello Edward Good Night, Nice to Meet you!
0
Hello Edward Good Night, Bye!
0

指定参数名称调用
此部分使用 “参数默认值”那一小节的存储过程。
用于说明当最后2个参数是有默认的时候，如何跳过中间那个。
1> DECLARE @RC int;
2> BEGIN
3> EXECUTE @RC = HelloWorld3 'Edward';
4> PRINT @RC;
5> EXECUTE @RC = HelloWorld3 'Edward', @Val1=' Good Night,';
6> PRINT @RC;
7> EXECUTE @RC = HelloWorld3 'Edward', @Val1=' Good Night,', @Val2=' Bye';
8> PRINT @RC;
9> EXECUTE @RC = HelloWorld3 'Edward', @Val2=' HeiHei ';
10> PRINT @RC;
11> END
12> go
Hello Edward Good Moning, Nice to Meet you!
0
Hello Edward Good Night, Nice to Meet you!
0
Hello Edward Good Night, Bye!
0
Hello Edward Good Moning, HeiHei !
0

存储过程返回结果集

SQL Server的存储过程，可以在里面写一段SQL语句，然后其它的开发语言比如C#，可以在调用存储过程的后，获取到存储过程执行所返回的结果集。

此方法在SQL Server中可用，在Oracle中不可用。

CREATEPROCEDURE testProc

AS

BEGIN

SELECT'Hello 1'AS A,'World 1'AS B UNIONALL

SELECT'Hello 2'AS A,'World 2'AS B;

END

BEGIN

DECLARE @RC int;

EXECUTE @RC = testProc;

PRINT @RC;

END

A B

------- -------

Hello 1 World 1

Hello 2 World 2

(2 行受影响)

0

然后 C# 调用存储过程的代码，可能像下面这个样子：

/// <summary>
/// 测试执行存储过程. 返回结果集合.
/// </summary>

/// <param name="conn"></param>
private void CallProcedureWithReturnData(SqlConnection conn)
{
// 创建一个 Command.
SqlCommand testCommand = conn.CreateCommand();

// 定义需要执行的SQL语句.
testCommand.CommandText = "testProc";

// 定义好，本次执行的类型，是存储过程.
testCommand.CommandType = CommandType.StoredProcedure;

// 执行SQL命令，结果存储到Reader中.
SqlDataReader testReader = testCommand.ExecuteReader();

// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}

// 关闭Reader.
testReader.Close();
}

执行的结果将是：

调用函数:testProc; 返回:Hello 1 - World 1
调用函数:testProc; 返回:Hello 2 - World 2

（注意，只调用一次，返回2行结果。不是调用2次。）

对于 Oracle 来说，同样的存储过程，无法编译通过。

SQL> CREATE OR REPLACE PROCEDURE testProc

2 AS

3 BEGIN

4 SELECT 'Hello 1' AS A, 'World 1' AS B FROM DUAL UNION ALL

5 SELECT 'Hello 2' AS A, 'World 2' AS B FROM DUAL ;

6 END;

7 /

警告:创建的过程带有编译错误。

SQL> show err;

PROCEDURE TESTPROC出现错误:

LINE/COL ERROR

-------- -----------------------------

4/3 PLS-00428: 在此 SELECT 语句中缺少 INTO 子句