首页 > 代码库 > 存储过程--create procedure
存储过程--create procedure
1.Transact-SQL语言基本用法：
1 变量
2 流程控制命令
3 其它命令
4 常用函数
1.1变量
局部变量声明和赋值
declare @变量名 变量类型 [，@变量名 变量类型]--变量的声明
selelct @变量=变量值
或
set @变量=变量值 --变量赋值
【例】声明一个长度为8个字符的变量id，并赋值。
declare@id char（8）
select@id =‘10010001’
1.2流程控制
1.2.1 流程控制种类
a.begin.........end
b.if......else
c.case
d.while.......continue....break
e.waitfor
f.goto
g.return
h.use
1.2.1-a
其语法如下：
BEGIN
<命令行或程序块块>
END
BEGIN…END 用来设定一个程序块，将在BEGIN…END 内的所有程序视为一个单元执行。
BEGIN…END 经常在条件语句（如IF…ELSE）中使用。
在BEGIN…END 中可嵌套另外的BEGIN…END 来定义另一程序块。
1.2.1-b
其语法如下：
IF<条件表达式>
<命令行或程序块>
[ELSE[条件表达式]
<命令行或程序块>]
其中:
v<条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。
vELSE 子句是可选的，最简单的IF语句没有ELSE子句部分。
vIF…ELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。
v如果不使用程序块，IF或ELSE只能执行一条命令。IFELSE 可以进行嵌套，在Transact-SQL中最多可嵌套32级
eg:
从SC数据表中求出学号为S1同学的平均成绩，如果此平均成绩大于或等于60分，则输出“pass”信息。
if (select avg(score) from sc where sid=‘s1‘ group by sid)>=60
begin
print ‘pass‘
end
1.2.1-c
CASE 命令有两种语句格式：
格式1：
CASE<运算式>
WHEN <运算式>THEN <运算式>
…
WHEN <运算式>THEN <运算式>
[ELSE <运算式>]
END
该语句的执行过程是：v将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，
v如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。
vELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。
eg:
从学生表S中，选取SNO，SEX，如果SEX为“男”则输出“M”，如果为“女”输出“F”。
select sno,sex=
case sex
when ‘男‘ then ‘M‘
when ‘女‘ then ‘F‘
end
from s
格式2：CASE
WHEN <条件表达式>THEN <运算式>
…
WHEN <条件表达式>THEN <运算式>
[ELSE <运算式>]
END
eg:
从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”.
select sno,cno,
score=
case
when score is NULL then ‘未考‘
when score < 60 then ‘不及格‘
when score >=60 and score <70 then ‘及格‘
when score >=70 and score<90 then ‘良好‘
when score>=90 then ‘优秀‘
end
from s
1.2.1-d
其语法如下：
WHILE<条件表达式>
BEGIN
<命令行或程序块>
[BREAK]
[CONTINUE]
[命令行或程序块]
END
WHILE命令在设定的条件成立时，会重复执行命令行或程序块。
CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行，继续进行下一次循环。
BREAK命令则让程序完全跳出循环，结束WHILE命令的执行。
WHILE语句也可以嵌套。
eg:
以下程序计算1－100之间所有能被3整除的数的个数及总和。
declare @s smallint,@i smallint,@nums smallint
set @s=0
set @i=1
set @nums=0
while(@i<=100)
begin
if (@i%3=0)
begin
set @s=@s+@i
set @nums=@nums+1
end
set @i=@i+1
end
end
print @s
print @nums
1.2.1-e
eg:
等待1 小时2 分零3 秒后才执行SELECT语句。
waitfordelay ‘01:02:03’
Select * from employee
1.2.1-f
如：求1＋2＋3＋…＋10的总和。
DECLARE@S SMALLINT,@I SMALLINT
SET@I=1
SET@S=0
BEG:
IF(@I<=10)
BEGIN
SET @S=@S+@I
SET @I=@I+1
GOTO BEG
END
PRINT@S
1.2.1-h
USE {databasename}
USE teach
1.3创建存储过程
1.3.1有参无返
如：在teach数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向数据表s中插入一条记录，新记录的值由参数提供。
USE teach
CREATE PROCEDURE InsertRecord
(
@sno char(6),
@sn char(20),
@age numeric(5),
@sex char(2),
@dept char(10)
)
AS
INSERT INTO s VALUES(@sno,@sn,@sex,@age,@dept)
1.3.2有参有返
定义能够返回值的存储过程。
如：在teach数据库中，创建一个名称为Query_Study的存储过程，该存储过程的功能是从数据表s中根据学号查询某一同学的姓名和系别。
USE teach
GO
CREATE PROCEDURE Query_Study
(
@sno char(6),
@sn char(20) OUTPUT,--返回值参数定义
@dept char(10) OUTPUT
)
AS
SELECT@sn=sn,@dept=dept
FROM s
WHERE sno=@sno
GO
DECLARE @sn char(20)
DECLARE @dept char(10)
EXECUTE Query_Study ‘S10‘,@sn OUTPUT,@dept OUTPUT
SELECT‘姓名‘=@sn,‘系别‘=@dept
1.3.3-调用
1.无参调用
EXECUTE myproc
2.执行teach库中存储过程InsertRecord (带参调用) 。
EXECUTE InsertRecord @sno =‘S1’, @sn = ‘王大利’,@sex = ‘男’,@age = 18,@dept= ‘计算机系’
3.执行teach库中的存储过程InsertRecordDefa(含默认值调用)。
EXECUTE InsertRecordDefa @sno =‘S10‘,@sn = ‘高平‘,@sex = ‘女‘,@age = 18
4.执行teach库中的存储过程Query_Study (含有输出参数)。
DECLARE @sn char(20)
DECLARE @dept char(10)
EXECUTE Query_Study ‘S10‘,@sn OUTPUT,@dept OUTPUT
SELECT‘姓名‘=@sn,‘系别‘=@dept
本文出自 “8756526” 博客，谢绝转载！
存储过程--create procedure