自定义变量、分支结构、存储过程函数procedure/function、--十二、十三、十四次课
十二次课
第七部分 存储过程（函数）_1 _应用重点
1 存储过程/函数：procedure/function
通俗说，存储过程是个模块化的程序段，完成一定功能，可被反复调用，可用于解决复杂的问题，减少程序冗余度。
这里的过程和函数就是C语言的方法/函数。过程一般不直接返回值，但可以通过实参和形参返回给主调程序计算结果；函数在SQLSERVER中，它都要返回一个值。（统一看，C语言中的返回void的函数在这里叫过程，有返回类型值的函数在这里叫函数）
2 预备知识---程序的流程
（1）变量的定义:
为了区分系统内存变量（包括表的列名和系统环境参数）和用户自定义变量，要求用户自定义变量时前加@符号
当打开一个表时，表的所有列名如学号，姓名是变量，控制系统内部的参数设置的量也是变量-----系统变量和列变量。
如要定义一个自定义变量保存杨春同学学号:
declare @xh nchar(10)
select @xh=学号 from 学生表 where 姓名='杨春'
print @xh
为什么要用@xh，而不是xh，因为表列名有可能是“xh”，避免冲突误会
修改上面程序段，也正确：
declare @学号 nchar(10)
select @学号=学号 from 学生表 where 姓名='杨春'
print @学号
小结：定义变量
Declare @变量名 类型[(宽度),类型…..]
(2)赋值:
Set @变量名=值/表达式
Select @变量名=值/表达式 from 表…..
例：定义变量存放性别和姓氏，把这些变量代入查询语句并输出结果
declare @xs nchar(1),@xb nchar(1)
set @xs='李'
set @xb='男'
select * from 学生表 where left(姓名,1)=@xs and 性别=@xb
例：如果在C#/java中如何拼凑这个查询命令？ 字符串和日期常量数据都要加单引号，数值型不加
String s=”select * from 学生表 where left(姓名,1)=’”+xs+”’ and 性别=’”+xb+”’ and 身高>”+sg;
利用上例的数据，结果：
S的结果：=”select * from 学生表 where left(姓名,1)=’李’ and 性别=’男’ and 身高>175”
(调用软件时，判断不清查询命令格式是否正确，可：
Response.write(s)； 再在输出的网页复制这条命令去sqlserver测试是否正确
（3）分支结构
（1） 格式：只能用在过程或函数中，不能嵌套到其它sql命令中
If(条件)
Begin
成立时语句序列
End
Else
Begin
条件不成立时的语句序列
End
(begin …end ，等效于C语句中{ }； 当语句序列退化成一句时，这个begin…end是可以省写的 )
例：根据姓名(@xm变量)查询此人101课程的成绩值，再根据本成绩值判断等级，[80，100]为优秀，[60，80）为合格，60以下为不合格.
declare @xm nchar(10),@cj int,@dj nchar(3)
set @xm='李大方'
select @cj=成绩 from 成绩表 where 课程号='101' and 学号=(select 学号 from 学生表 where 姓名=@xm)
if(@cj is null)
begin
set @dj='没选课'
end
else
begin
if(@cj>=80)
set @dj='优秀'
else
if(@cj>=60)
set @dj='合格'
else
set @dj='不合格'
end
print Rtrim(@xm)+'同学的课程成绩为:'+convert(nchar(3),@cj)+',等级为:'+@dj
(2)case when 用法（直接多分支，类似于switch功能，但它不是独立的命令语句 ）
格式1：
Case 表达式
When 常量1 then 结果1
When 常量2 then 结果2
…
When 常量n then 结果n
Else 结果n+1
End
与switch不大一样的是：每个then 后面的结果不需要加break;本系统只执行一个条件，不会从条件入口进来后，还要往下执行
例：把数据库中读出来星期数值（0-6表示星期天—星期六）
declare @xq smallint,@xqre nchar(3)
set @xq=15
select @xqre=
case @xq
when 0 then '星期日'
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
else '无效值'
end
print @xqre
格式2： 用法比格式1要灵活得多
Case
When 条件1 then 结果1
When 条件2 then 结果2
…..
When 条件n then 结果n
Else 结果n+1
End
declare @xq smallint,@xqre nchar(3)
set @xq=1
select @xqre=
case
when @xq=0 then '星期日'
when @xq=1 then '星期一'
when @xq=2 then '星期二'
when @xq=3 then '星期三'
when @xq=4 then '星期四'
when @xq=5 then '星期五'
when @xq=6 then '星期六'
else '无效值'
end
print @xqre
例：用格式2解决上面成线等级问题(比if写法直接，易读易懂)
declare @xm nchar(10),@cj int,@dj nchar(3)
set @xm='李大方'
select @cj=成绩 from 成绩表 where 课程号='101' and 学号=(select 学号 from 学生表 where 姓名=@xm)
select @dj=
case
when @cj>=80 and @cj<=100 then '优秀'
when @cj>=60 then '合格'
when @cj>=0 then '不合格'
else '无效值'
end
print Rtrim(@xm)+'同学的课程成绩为:'+convert(nchar(3),@cj)+',等级为:'+@dj
如@cj为85，满足了@cj>60 @cj>=0，与c不同的是，只会从上向下找到第一个满足条件来执行，后面即使再有满足条件的情况也不执行，跳到end后
例：查出李大方的101课程成绩情况，列出数据：
学号，姓名，课程名，成绩，实得学分
declare @xh char(10),@xm nchar(6),@kcm nchar(10),@cj int,@sdxf int
select @xh=a.学号,@xm=姓名,@kcm=名称,@cj=成绩,
@sdxf=学分 from 学生表 a join 成绩表 b on a.学号=b.学号
join 课程表 c on b.课程号=c.课程号
if(@cj<60)
set @sdxf=0
select @xh,@xm,@kcm,@cj,@sdxf
再例：查出李大方和李四方的101和102两门课程成绩情况，列出数据：
学号，姓名，课程名，成绩，实得学分
select a.学号,姓名,名称,成绩,
case
when 成绩>=60 then 学分
else 0
end as 实得学分
from 学生表 a join 成绩表 b on a.学号=b.学号
join 课程表 c on b.课程号=c.课程号
where 姓名 in('李大方','李四方') and c.课程号 in('101','102')
例：身高在1.70以上为高，否则为矮；把表中表示是否党员的1、0转换为“党员”，“群众”；把籍贯值前含有四川，成都字样的人列出数据：
学号，姓名，身高，身高标准，党员否
select 学号,姓名,身高,
case when 身高>=1.7 then '高'
else '矮'
end as 身高标准
, case when 团员否=1 then'学员'else'非党员' end as 党员否 from 学生表
where 家庭住址 like '四川%' or 家庭住址 like '成都%'
4 循环
While(条件)
Begin
循环体语句序列
End
在这里循环使用较少，因为数据库的查询 select本身就是从首行自动循环到尾行。
例：输入一个n求 s=1+2+3+…n
declare @n int,@s int,@i int
set @n=100
set @s=0
set @i=1
while(@i<=@n)
begin
set @s=@s+@i
set @i=@i+1
end
print @s
数据库应用参考代码
Regxs.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="regxs.aspx.cs" Inherits="regxs" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
学生注册<br />
<table style="width:50%;border:dotted;border-width:1px;text-align:center">
<tr><td>欢迎新人注册</td><td> </td> </tr>
<tr><td>学号：</td><td><asp:TextBox ID="xh" Text="001" runat="server"></asp:TextBox> </td> </tr>
<tr><td>姓名：</td><td><asp:TextBox ID="xm" Text="张三" runat="server"></asp:TextBox></td> </tr>
<tr><td>性别：</td><td><asp:RadioButton ID="xb1" Checked="true" Text="男" runat="server"/><asp:RadioButton ID="xb2" Text="女" runat="server"/></td> </tr>
<tr><td>出生日期:</td>
<td><asp:DropDownList ID="yy" runat="server" ></asp:DropDownList>年
<asp:DropDownList ID="mm" runat="server" style="width: 95px" AutoPostBack="True" OnSelectedIndexChanged="mm_SelectedIndexChanged"></asp:DropDownList>月
<asp:DropDownList ID="dd" runat="server"></asp:DropDownList>日
</td>
</tr>
<tr><td><asp:Button ID="bt1" Text="提交" runat="server" OnClick="bt1_Click" />
</td></tr>
</table>
</div>
</form>
</body>
</html>
Regxs.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class regxs : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int year = DateTime.Now.Year;
for (int y = year; y > year - 50; y--)
{ yy.Items.Add(y.ToString());
}
for (int m = 1; m <= 12; m++) mm.Items.Add(m.ToString());
}
}
protected void mm_TextChanged(object sender, EventArgs e)
{
}
protected void bt1_Click(object sender, EventArgs e)
{
Response.Write(111);
}
protected void mm_SelectedIndexChanged(object sender, EventArgs e)
{
int d = 31;
int y = int.Parse(yy.Text);
int m = int.Parse(mm.Text);
Response.Write(y.ToString() + m.ToString());
if (m == 4 || m == 6 || m == 9 || m == 11)
d = 30;
if (m == 2) //假设是闰年，且2月为29天
{
if (y % 4 == 0) d = 29;
else d = 28;
}
dd.Items.Clear();
for (int i = 1; i <= d; i++) dd.Items.Add(i.ToString());
}
}
十三次课
第七部分 存储过程（函数）_2 _应用重点
一 存储过程应用
create procedure 过程名 as
过程体语句(可是若干条命令)
类似老的C：void 函数名( ) { 函数体}
过程本身不通过过程名带回来计算结果，所以类似于void（并不是通过形参的改变返回数据给实参）
（1） 无参传递过程，主调程序不需要传递实参给过程
例：调用过程查询杨春的数据。
create procedure p1 as
declare @xm nchar(6)
set @xm='杨春'
select 学号,姓名,生日 from 学生表 where 姓名=@xm
完成过程的建立，然后在其它主调程序中调用：exec 过程名
execute p1
(2)有参过程：实用性强，通用性好
例：输入一个姓名，课程号 ,调用过程查询该人该科成绩值
create procedure p2 @xm nchar(6),@kch nchar(3)
as
select 成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
execute p2 '李大方','101'
以上两例无返回值，在过程本身内部把结果显示出来而已，并不是把计算结果返回给调用处。
（3）有参数，且把计算结果返回给调用处
例：修改上例，把计算得到成绩值结果返回给调用处
分析：过程不返回值，但可以通过形参把结果返回给过程的实参（要多定义专用变量返回结果-----类似于C++中的引用调用，传地址调用）
int f1(int x)
{ x=x*x; retrun x;}
int n=f1(5);
create procedure p3 @xm nchar(6),@kch nchar(3),
@cj int output
as
declare @cj0 int
select @cj0=成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
set @cj=@cj0
调用
declare @cj int
execute p3 '李大方','101',@cj output
print @cj
说明：需要返回值的实参和形参后面都要跟上output.掌握此概念后，调用一个过程随便返回什么数据，随便返回多少个数据。
可见：过程就是一个小功能模板，程序段，一般是参数的输入，输出，与外界进行接口。
完善上例：
create procedure p3
@xm nchar(6),@kch nchar(3),@cj int output,@err nchar(10) output
as
declare @cj0 int
set @err=''
select @cj0=成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
if(@cj0 is null) set @err='姓名或课程号错误'
set @cj=@cj0
调用
declare @cj int ,@err nchar(10)
execute p3 '李大方','101',@cj output,@err output
print @cj
print @err
课后练习：继续完善上例。要输出错误的具体项，究竟是姓名还是课程号错误
二 函数(存储过程应用频率相对比函数小些)
函数可以单独使用，也可以套在select查询语句中使用，灵活得多
一个函数定义后，当库函数一样使用
分类：
1 聚合函数：
sum(列)、avg(列)、count()、max( )、min( )
用于数据的统计的，针对数据库表操作的，是系统类的函数
2 系统内置函数:
sql提供的通用函数，不是用于表的统计的，与上面聚合函数不一样。如sin( )、convert( )、year( )、getdate( )
（1）日期时间类 :
1> Getdate( )
2> Year(日期)：返回日期的年份；month(日期)；day(日期)
3> Dateadd(日期时间的部分量，偏差值, 日期) 返回指定日期的指定分量的偏移后的日期结果
例： dateadd(day,95,getdate()) 求今天后的95天后是哪一天？
其中的日期部分量：year, month,quarter，day,week,hour,minute,second,millisecond
select GETDATE()+105
select DATEADD(day,105,getdate())
select DATEADD(hour,200,getdate())
select DATEADD(hour,200,'1990-2-1 11:05:30')
select 姓名,出生日期,DATEADD(day,100,出生日期) from 学生表
4>datediff(日期时间部分量,起始日期,结束日期) 求两个日期的指定部分偏移量 (是上面dateadd反操作，根据两个日期求出指定部分的偏差量)
print Datediff(month,'1986-12-1','2001-9-2')
declare @d1 datetime,@d2 datetime
set @d1='2021-4-20 12:00:00'
select @d2=GETDATE()
print datediff(minute,@d1,@d2)
（2）字符串函数
1> left(串，n) 取串左n个字符
2> right(串，n) 取串右n个字符
3>substring(串,m,n) 从串的第m个字符开始共取n个字符出来(如果要取的字符串超过的实际，取完即可，容错处理)
4>len(串) 求串长
5>Ltrim(串):去掉串左边多余的空格;Rtrim(串):去掉串的右边多余空格
select RIGHT(学号,3) from 学生表
select SUBSTRING(学号,2,3) from 学生表
select REPLACE('很多坏人信XXX，在中国XXX是不允许','XXX',REPLICATE('*',len('XXX')))
(3)数据类型转换
1>convert(目标类型及宽度，被转换前原值[,其它附加参数]
Convert(nchar(5), n)
Convert(nchar(10),生日,21) 其中21是针对日期的格式参数
2> cast(表达式 as 新类型[长度])
select CAST(身高 as numeric(4,1)) from 学生表
select 姓名+'同学的身高是:'+convert(nchar(4),身高) from 学生表
select 姓名+'同学的身高是:'+cast(身高 as NCHAR(4)) from 学生表
附加代码：C#处理一些函数
private void test()
{
String s = "78|aaaa|1.75|四川成都";
String[] s2 = s.Split('|');
Response.Write(s2[2]);
DateTime d1 = DateTime.Now;
DateTime d2 = DateTime.Parse("1989-12-5");
TimeSpan t = d2 - d1;
Response.Write(t.Days);
int L1 = s.Length;
int L2 = s2.Length;
String fn = "d:\\12\abc\\照片\\123.ab.jpeg";
Response.Write(fn.Substring(fn.LastIndexOf('.') + 1));
}
三: 数据库应用
新数据学生入库
第七部分 存储过程（函数）_3 应用重点
一 函数 function
3 自定义函数
(1) 标量函数：返回的是一个标准量，如一般数据类型int, char. 这种函数相对简单，通过函数体计算，返回调用处一个标量
与其它语言类似，有参数时通用函数名后面参数表带去，也可以无参函数（但空括号不能省）
定义格式：
Create function 函数名(形式参数表) returns 返回值的数据类型
Begin
函数体语句
End
引例：输入两个整数a,b，返回之和.
C：
int f1(int a,int b)
{ int t; t=a+b;
return t;
}
Sql中：
create function f1(@a int,@b int) returns int
begin
declare @t int
set @t=@a+@b
return @t
end
print dbo.f1(5,1)
说明：C，java为把早期过程和函数概念合在一起，当函数不需要返回数据时，定义全时函数类型为void。 不需要返回值（所有输出返回操作都是过程内完成）的函数就是过程procedure
例：传入一个正整数n，使用函数jc(n)求n! , f(n)=n!
create function jc(@n int) returns bigint
begin
declare @t bigint,@i int
set @t=1
set @i=1
while(@i<=@n)
begin
set @t=@t*@i
set @i=@i+1
end
return @t
end
print dbo.jc(5)
完整: 如要经常根据成绩值计算实得学分（cfx(课程号,成绩）），根据成绩值评等级，90及以上
为优秀，70-90为良好，60-70为合格，60重修（DJPD（成绩）
create function djpd(@cj int) returns nchar(2)`
begin
declare @dj nchar(2)
if(@cj>=90) set @dj='优秀'
else if(@cj>=70) set @dj='良好'
else if(@cj>=60) set @dj='合格'
else set @dj='重修'
return @dj
end
create function cxf(@kch char(3),@cj int) returns int
begin
declare @xf int
select @xf=学分 from 课程表 where 课程号=@kch
if(@cj<60) set @xf=0
return @xf
end
select 学号,课程号,成绩,dbo.cxf('103',成绩) from 成绩表
where 课程号='103'
select 学号,课程号,成绩,dbo.cxf(课程号,成绩)as 实得学分,
dbo.djpd(成绩)as 等级 from 成绩表
补：drop function dbo.jc 可删除一个函数
注意：函数调用时，最后加上名字空间的。
例：把上面XXX例子函数化
create function myrepl(@str1 nvarchar(800),@str2 nvarchar(10))
returns nvarchar(800)
begin
declare @strT nvarchar(800)
set @strT=replace(@str1,@str2,replicate('*',len(@str2)))
return @strT
end
select dbo.myrepl('abcdefcdaax','cd')
select 学号,姓名,家庭住址,dbo.myrepl(家庭住址,'成都') from 学生表
例：定义一个函数dj(n)判断成绩的等级。90以上为优，80以上为良，70以上中，60以上及格，60以下为差
create function dj(@n int) returns nchar(2)
begin
declare @dj0 nchar(2)
if(@n>=90) set @dj0='优'
else if(@n>=80) set @dj0='良'
else if(@n>=70) set @dj0='中'
else if(@n>=60) set @dj0='及格'
else set @dj0='差'
return @dj0
end
select *,dbo.dj(成绩) as 等级 from 成绩表
例：编写函数实现：根据学生学号，课程名称，查出该生该课的实际所得学分（60及以上得学分，不及格该科学分为0）
create function sdxf(@xm nchar(6),@kcm nchar(10))returns int
begin
declare @xf int,@cj int
select @cj=成绩 from 成绩表 where 学号=(select 学号 from 学生表
where 姓名=@xm) and 课程号=(select 课程号 from 课程表 where 名称=@kcm)
if(@cj>=60)
select @xf=学分 from 课程表 where 名称=@kcm
else set @xf=0;
return @xf
end
select 学号,姓名,dbo.sdxf(姓名,'大学英语')as 实得学分 from 学生表
（2）表值函数: 内联表值函数
一般函数返回的一个标量值，表值函数返回的是一个表(视图)，由此得名
这种表是一个关系，在结构表头与一般的物理表相没区别，可以随意存放多行多列数据---故使用表值函数可以返回大量数据
表值函数内部函数体的核心任务是，把所有需要的返回给调用处的数据计算出来，存放到内存表中。最后一次性返回。
格式：create function 函数名(形式参数表) returns table
As
Begin
函数体
End
引例：输入一个姓氏，查出该姓所有人的学号，姓名，生日,该姓氏比姓杨的人多几个？
理论上看，一个函数无法返回这么大一堆数据的，现在可以使用表值函数返回多个数
create function my15(@xs nchar(1)) returns
@table1 table(学号 CHAR(11),XM nchar(6),生日 smalldatetime,人数 int ,多于杨人数 int)
as
begin
insert into @table1(学号,XM,生日) select 学号,姓名,出生日期 from 学生表 where LEFT(姓名,1)=@xs
declare @rs int ,@rsY int
select @rs=COUNT(*) from 学生表 where left(姓名,1)=@xs
select @rsY=COUNT(*) from 学生表 where left(姓名,1)='杨'
insert into @table1(人数,多于杨人数)values(@rs,@rs-@rsY)
return
end
select * from dbo.my15('李')
因为内部的select查询的结果本身就是表视图，生成的返回临时表也它查询结果一致，不需要再自定义临时表
对表值函数的理解：当表一样的用，是一个带参数的，带条件查询的结果，返回的满足条件的结果
例：多语句表值函数
输入一个课程名，返回此课程平均分，最高分，选课人数, 总实得学分，不及格门数,该门课最高分的学生姓名.
分析：以上结果数据太多，难以一句sql命名搞定，又在不增加访问物理数据库次数情况下，减少服务器与客户端的数据来往次数，一次性在服务器完成所有计算，把结果一次性返回给客户端----使用表值函数
create function fun11(@kcm nchar(10))
returns @t table(课程名 nchar(10),平均分 numeric(4,1),最高分 numeric(4,1), 选课人数 int,得到学分人数 int,不及格比例 numeric(4,1))
as
begin
insert into @t(课程名) values(@kcm)
update @t set 平均分=(select AVG(成绩) from 成绩表 where
课程号=(select 课程号 from 课程表 where 名称=@kcm))
update @t set 最高分=(select MAX(成绩) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm))
update @t set 选课人数=(select COUNT(*) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm))
update @t set 得到学分人数=(select COUNT(*) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm) and 成绩>=60 )
Update @t set 不及格比例=（((select COUNT(*) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm))-
select COUNT(*) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm) and 成绩>=60)
/(select COUNT(*) from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm)))*100
Delcalre @xm0 nchar(10)
Select @xm0=姓名 from 学生表 where 学号=(select top 1 学号 from 成绩表 where 课程号
=(select 课程号 from 课程表 where 名称=@kcm) order by 成绩 desc )
insert into @t(课程名)values(@xm0)
return
end
select * from dbo.fun11('大学英语')
应用：
1 数据非查询操作（增，删，改）
（1）增加记录
string xh="20190101", xm="张十一", sr="1996-12-1", xb="男";
string sql = "insert into 学生表(学号,姓名,出生日期,性别) values('" + xh + "','" + xm + "','" + sr + "','" + xb + "')";
string cnstr = "server=LAPTOP-D6A7VHU8; database=StudentDB; uid=sa;pwd=sasasa;";
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
cmd.Clone();
实验4：
实验题目：程序流程控制和函数应用
实验目的：掌握基本程序流程，熟练使用基本语法编写较为复杂的程序，程序模块化
