SQL语法基础与存储过程

SQL语法基础与存储过程

一、SQL语法基础

（一）注释

-- 单行注释，从这到本行结束为注释，类似C++，c#中//

/* „ */ 多行注释，类似C++，C#中/* „ */

（二）变量

int， smallint， tinyint， decimal，float，real， money ，

smallmoney，text ，image， char， varchar„„

语法：

DECLARE

{

{@local_variable data_type}

} [，...n]

例如：declare @ID int –声明一个名为@ID的变量，类型为int型

（三）在SQL Server窗口中打印出变量的值

语法：

PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr

（四）变量赋值

例如： --从数据表中取出第一行数据的ID，赋值给变量@id，然后打印出来

Declare @ID int

Set @ID = (select top(1) sno from student)

Print @ID

在SQL中，我们不能像代码那样直接给变量赋值，例如@id = 1，如果要达到这样的功能，可以这样写：

Declare @ID int

Set @ID = (select 1) -- 类似 @ID=1

Select @id=1 -- 类似 @ID=1

Print @ID

（五）变量运算(+，-，*，/，„„) 以下必要时候省略变量声明

Set @ID = (select 1+5) --类似 @ID=1+5

Set @ID=(select 1-@ID) --类似 @ID=1-@ID

（六）语句块：Begin „ end

将多条语句作为一个块，类似与C++，C#中的{ }

例如：

Begin

Set @ID1 = (select 1)

Set @ID2 = (select 2)

End

（八）条件语句If， if„else„

语法：

IF Boolean_expression

{sql_statement | statement_block}

[ELSE

{sql_statement | statement_block}]

例如：

If @id is not null

Print ‘@id is not null

if @ID = 1

begin

Set @ID = (select 1 + 1)

End

Else

Begin

set @ID=(select 1+2)

end

上面的例子用到了比较符，语句块，和IF的语法。

二、存储过程

（一）概念 存储过程(Stored Procedure)把我们经常用到的一串复杂sql语句保存成一个数据库对象，并给它起一个名字。每次使用存储过程只需要使用如下的形式即可：exec proc 存储过程名

另外，存储过程还可以带参数运行：

exec proc 存储过程名 参数值[, 参数值...]

（二）存储过程的分类

 1、系统存储过程 system stored procedure 前缀sp_

例如sp_help sp、helpdb

 2、扩展存储过程 extended stored procedure 前缀xp_

例如xp_cmdshell

 3、用户自定义存储过程 user-defined stored procedure

也就是我们自己创建的

（三）用户自定义存储过程的语法

1、创建存储过程

create proc 存储过程名 --无参数的存储过程

as

批处理语句

go

2、修改存储过程

alter proc 存储过程名 --无参数的存储过程

as

批处理语句

go

3、删除存储过程

drop proc 存储过程名

（四）创建存储过程例题

1、创建不带参数的存储过程

例[1]：创建一个“计算机”系学生资料的存储过程proc_1。

CREATE PROCEDURE proc_1

AS

SELECT * FROM STUDENT

WHERE SDEPT=’计算机’ 执行该存储过程，可在查询分析器中执行下面的语句：

EXEC proc_1

2、创建带输入参数的存储过程

输入参数是指由调用程序向存储过程传递的参数。

语法：CREATE PROCEDURE 过程名

@参数名 数据类型= 默认值，

„„„

AS

SQL语句

 @参数名：存储过程的输入参数名，必须以@符号为前缀。执行该存储过程时，应该向输入参数提供相应的值。

[例2]：创建一个按系别查询学生资料的存储过程proc_2，要查询的系名通过执行语句中的输入参数传递给存储过程。

CREATE PROCEDURE proc_2

@dept_name VARCHAR(20)

AS

SELECT * FROM STUDENT

WHERE sdept=@dept_name

执行该存储过程：EXEC proc_2 ‘计算机’

[例3]：创建一个求1到k的和的存储过程 proc_sum.

CREATE proc proc_sum

@k int

AS

DECLARE @i int,@sum int

SELECT @i=1,@sum=0

WHILE @i<=@k

BEGIN

SELECT @sum=@sum+@i

SELECT @i=@i+1

END

select @sum

执行该存储过程：exec proc_sum 100 3、创建带输出参数的存储过程

语法：CREATE PROCEDURE 过程名

@参数名 数据类型= 默认值 [OUTPUT][,„„.]

AS

SQL语句

RETURN

注意：在CREATE PROCEDURE和EXECUTE语句中，都必须使用OUTPUT关键字。

[例4]：创建一个从STUDENT表中查询某位同学的年龄的存储过程proc_3。

CREATE PROCEDURE proc_3

@name VARCHAR(8)=NULL,

@age tinyint OUTPUT

AS

IF @name=NULL

begin

PRINT '查找哪位同学的年龄?'

RETURN

end

ELSE

SELECT @age=SAGE

FROM STUDENT

WHERE SNAME=@name

RETURN

在本例中，存储过程接收两个参数：@name和@age。@age作为输出参数，表示该过程执行结束后，它的值可以返回到调用它的过程中。过程在STUDENT表中找到@name值所在的行，然后把@name相对应的年龄的值存储到@age参数中。

执行存储过程，输出“刘天海”的年龄。

DECLARE @a tinyint

EXEC proc_3 ‘刘天海’,@a OUTPUT

SELECT @a

三、游标

我们可以在存储过程中用Select语句取出每一行数据进行操作，这就需要用到游标。

语法： DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name [，...n]]]

例如：

DECLARE @sno varchar(10)， @sname varchar(8) –声明变量

--声明一个游标

DECLARE st_cursor CURSOR FOR

SELECT sno， sname FROM student

--打开游标

OPEN st_cursor

--取出值

FETCH NEXT FROM st_cursor INTO @sno， @sname

--循环取出游标的值

WHILE @@FETCH_STATUS = 0

BEGIN

Print @sno

Print @sname

Print ‘ ’

FETCH NEXT FROM st_cursor

INTO @sno， @sname

END

CLOSE st_cursor –关闭游标

DEALLOCATE st_cursor --释放游标