自定义变量、分支结构、存储过程函数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：

实验题目：程序流程控制和函数应用

实验目的：掌握基本程序流程，熟练使用基本语法编写较为复杂的程序，程序模块化