SQL Sever 2005教案第13章 存储过程及自定义函数

第一篇：SQL Sever 2005教案第13章 存储过程及自定义函数

什么是存储过程，在存储在服务器上的T-SQL语句的命名集合，是封装性任务的方法，支持变量及条件的编程。

SQL Server的存储过程与其他编程语言中的过程（包括函数）类似，可以包含数据库操作（调用其他过程）的编程语句，可以接受参数，可以返回状态值以表明成功或失败，以输出参数的形式将多个值返回至调用过程

SQL Server支持五种类型的存储过程：

系统存储过程（sp_）：存储在master数据库中。

本地存储过程：在单独的用户数据库中。

临时存储过程：局部的以#开头，全局的以##开头。

远程存储过程：分布式查询支持此功能。

扩展存储过程：在SQL Server环境外执行。

存储过程的优点 封装商务逻辑，若规则或策略改变只需修改存储过程就可以直接使用，屏蔽数据库的详细资料，用户不需要访问底层数据库和数据库对象。提供安全机制，只需要提供存储过程的权限而不需要提供整个数据库中数据的一个权限。另外，存储过程能够通过预编译的语句来确定执行哪一部分而不是都执行。在传输过程中传输的存储过程而不是数据，减少了通信量，能够实现一个较快的执行速度。

create proc liuhaoran as select price from titles where price>15 select title from titles where price<=15

在存储过程里可以包含任何数目和类型的T-SQL语句，但不能包含create proc、create trigger、create view 执行创建存储过程的用户必须是sysadmin、db_owner 或 db_ddladmin角色的成员，或必须拥有 CREATE PROCEDURE 权限 存储过程有大小的限制，最大为128M

存储过程可以传递参数，创建存储过程，定义两个浮点型的参数，无返回值 CREATE PROCEDURE titlespro @Beginningprice float,@Endingprice float AS IF @Beginningprice IS NULL OR @Endingprice IS NULL BEGIN

print 'no price is exits'

RETURN END SELECT price FROM titles WHERE price BETWEEN @Beginningprice AND @Endingprice GO

/*执行语句,输入两个价格值作为参数值*/ exec titlespro 10,19

指定参数的依据和指导原则

所有的输入参数值都应该在存储过程开始的时候进行检查，以尽早捕获缺失值和非法值

应该为参数提供合适的默认值，可以未指定参数值的基础上执行存储过程

一个存储过程最多可以有1024个参数

不同存储过程可以使用相同的参数名 使用参数的指导原则

可以使用@参数=值的格式来指定参数，此方法可以按任意顺序来传递参数

对于有默认值的参数在调用存储过程的过程中可以不指定参数值

存储过程可以使用输出参数

--创建存储过程输入两个输入参数，定义一个输出参数 CREATE PROCEDURE Mathadd @m1 int, @m2 int, @result int OUTPUT--定义输出参数 AS SET @result=@m1+@m2 GO

调用过程如下：

declare @resultvalues int

exec mathadd 12,16,@resultvalues output--输出参数的值赋给变量 print 'The result is: '+convert(char,@resultvalues)

存储过程通过输出参数向调用它的存储过程或客户端返回信息，通过输出参数，存储过程的运行结果可以保留到程序运行结束。输出参数接受需要注意：

调用语句必须包含一个变量名，以接受返回值。

可以在T-SQL语句中使用返回变量 输出参数可以是任意类型，除了text和image 输出参数可以是游标

对于已经创建好的存储过程，如果存储过程需要修改，可以显式地重新编译，但应尽量少做。

在创建存储过程之前通常会检查此存储过程是否存在，如果存在可以选择删除后重建，或改变名字

IF EXISTS(SELECT name FROM sysobjects WHERE name='author_infor' AND type='p')DROP PROCEDURE author_infor GO

修改存储过程使用关键字alter

alter procedure pro_titles @values money--修改存储过程pro_titles as select price,pub_id from titles where price=@values go

删除存储过程使用关键字drop proc，因为存储过程属于数据库对象

drop procedure pro_titles

对于数据库中的所有存储过程或者触发器，如果需要将其全部重新编译，那么可以使用以下语句，EXEC sp_recompile titles--重新编译所有的存储过程或者触发器

在程序中通常会出现因为运行错误而出现的各种错误提示，其大部分都不能被用户容易读懂，因此可以自定义错误信息，来提示用户错误的具体原因。--自定义错误信息

EXEC sp_addmessage

@msgnum = 50010,---错误编号 @severity = 10,----严重级别

@msgtext = 'Customer cannot be deleted.',--错误文本信息 @with_log='true', @lang='us_english'

创建好自定义错误信息之后，就可以在程序中使用错误信息

--调用自定义错误信息

EXEC sp_addmessage 50010,10,'CustomerID not found.',@replace='replace' USE Northwind GO CREATE PROC UpdateCustomerPhone @CustomerID nchar(5)=NULL, @Phone nvarchar(24)=NULL AS IF @CustomerID IS NULL BEGIN

PRINT 'You must supply a valid CustomerID.'

RETURN END /*确认提供了合法的CustomerID */ IF NOT EXISTS(SELECT * FROM Customers WHERE CustomerID=@CustomerID)

BEGIN

RAISERROR(50010,10,1)--该客户不存在。

RETURN

END BEGIN TRANSACTION UPDATE Customers SET Phone =@Phone WHERE CustomerID=@CustomerID /*显示CompanyName的电话号码已更新的消息*/ SELECT 'The phone number for'+@CustomerID +'has been updated to'+ @Phone COMMIT TRANSACTION GO

创建存储过程的方法上面已经介绍结束，如果想要查看存储过程的代码，就需要使用到以下的关键字

--查看存储过程

EXEC sp_helptext UpdateCustomerPhone

而对于以存在的存储过程，在调用的过程由于某些原因需要重新命名的化，使用以下语句

--重命名存储过程

EXEC sp_rename reptq1, newproc

用户自定义函数部分

函数和存储过程同样都有输入和输出参数，单数输入和输出参数的声明方式有些区别--创建函数 USE pubs go CREATE FUNCTION WorkYearWage(@hiredate datetime,--hiredate 表示雇佣日期

@today datetime, @per_wage money)--today 表示当前的日期per_wage 表示每一年工龄应得的工资额 RETURNS money--返回值类型 AS BEGIN DECLARE @WorkYearWage money SET @WorkYearWage =(year(@today)-year(@hiredate))*@per_wage RETURN(@WorkYearWage)--返回的变量 END--结束函数定义 GO-

使用函数参数，当使用命名函数的时候，参数的次序不必按照在函数中声明的次序，但必须包含所有参数，不能省略任何参数

使用函数的过程同以前学习过的系统函数的使用方法想类似，在输出或查询语句中使用函数名及相应的床底若干参数即可。

--使用函数

SELECT pubs.dbo.workyearwage('1991-7-1',getdate(),15)--传参并输出结果

As work_year_wage

在函数的返回值方面可以直接声明返回参数，或者声明返回值类型--计算立方体函数

CREATE FUNCTION CubicVolume(@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1))--定义输入参数 RETURNS decimal(12,3)--定义返回值类型 AS BEGIN RETURN(@CubeLength * @CubeWidth * @CubeHeight)--返回结果 END

除特定类型的返回值之外，还可以返回表类型的结果集

--内联表值型用户自定义函数

CREATE FUNCTION orderfirms(@productid INT)--定义一个整型参数 RETURNS TABLE--返回值为表的结果集 AS RETURN(SELECT * FROM jobs WHERE min_lvl>@productid)--返回查询结果 GO

或者返回值是一张临时表

--多声明表值型用户自定义函数 USE Northwind go CREATE FUNCTION my_function(@regionParameter varchar(25))--定义一个函数的参数

returns @my_table TABLE--函数返回值为表的结果集，并定义表的结果集如下(city varchar(15)null, companyName varchar(40)not null, contactName varchar(30)null)AS BEGIN INSERT @my_table--从表customers中查询数据插入到@my_table中 SELECT city,companyName,contactName FROM Customers WHERE city=@regionParameter RETURN--返回结果集 END

表值函数：

函数体内只允许如下语句：

赋值语句

流程控制语句

用于定义函数局部数据变量和游标的declare语句

Select语句，将其后的表达式将赋予函数的局部变量

游标操作，仅允许使用fetch语句通过into子句给局部变量赋值，不允许使用fetch语句将数据返回到客户端

针对上面的例子（内嵌表值函数）需要注意的是：

return子句在括号中包含单个select语句，select语句的结果集构成函数所返回的表 函数体不由begin和end分隔

Return指定table作为返回的数据类型

不必定义返回变量的格式，因为它由return子句中的select语句的结果集的格式设置

查看创建用户自定义函数的方式有两种 查看特定用户自定义函数

--查看用户自定义函数 USE pubs go EXEC sp_helptext my_function--调用存储过程 GO

查看所有用户自定义函数

--通过系统表查看用户自定义函数 use pubs go select name, type, crdate from sysobjects where type='FN'--go

当创建的用户自定义函数需要修改时，使用如下语句

--修改函数cubicvolume函数

ALTER FUNCTION CubicVolume--这句表示声明修改用户自定义函数，其他语句不变

(@CubeLength decimal(4,1), @CubeWidth decimal(4,1))--定义输入参数 RETURNS decimal(12,3)--定义返回值类型 AS BEGIN RETURN(@CubeLength * @CubeWidth)--返回结果 END 基本语法和创建的类型，只需要将create更改为alter即可

删除存储过程，由于存储过程为数据库对象因此删除使用drop drop function cubicvolume

第二篇：第5章 视图、存储过程和用户自定义函数复习题范文

第5章 视图、存储过程和用户自定函数

1．什么是基本表？什么是视图？两者的区别和联系是什么？

答：

基本表是本身独立存在的表，在 sQL 中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。．试述视图的优点。

答

(l）视图能够简化用户的操作；(2）视图使用户能以多种角度看待同一数据；(3）视图对重构数据库提供了一定程度的逻辑独立性；(4）视图能够对机密数据提供安全保护。．所有的视图是否都可以更新？为什么？

答:

不是。视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新，所以，并不是所有的视图都是可更新的.4 ．哪类视图是可以更新的？哪类视图是不可更新的？各举一例说明。

答：基本表的行列子集视图一般是可更新的。若视图的属性来自集函数、表达式，则该视图肯定是不可以更新的。

5．教材P89习题1-5。

第三篇：vba自定义函数小结

小结：

函数参数前面标记byval，实现的是实参与形参之间的值传递，即形参具有了实参的值或者说建立了一个实参的副本给了形参，这样对形参的操作不会影响实参；

函数参数前面标记byref，实现的是实参与形参之间的引用传递，这样对形参的操作会影响实参，或者说对形参的操作等同于对实参的操作；

在函数内部使用“r=形参”，实现的是把形参的副本给r，在函数中对r操作不会影像形参，如果使用“set r=形参”，那么对r的操作会影响形参，这里实现的效果是r只是形参的一个别名而已，所以对r的操作等同于对形参的操作。

在函数中形参前面不标记byref和byval，默认是引用传递byref，但是这种情况下如果实参是excel中worksheet的range区域，则默认是值传递。

有时调试结果不符合预期，比如值传递，对””r=形参”中的r操作，居然影响形参了，这可能是由于excel一时程序错乱导致，关闭重启就好了。

Public Function tjbcf(rng)

r = rng

'Set r = rng'注意带着set是错误的 k = 0 If UBound(r, 1)> 1 And UBound(r, 2)= 1 Then For i = 1 To UBound(r, 1)f = 0 For j = 1 To i1 If r(1, i)= r(1, j)Then f = 1 Exit For End If Next j If f = 0 And r(1, i)<> “" Then k = k + 1 End If Next i End If

If UBound(r, 1)> 1 And UBound(r, 2)> 1 Then For i = 1 To UBound(r, 1)For j = 1 To UBound(r, 2)If r(i, j)= ”“ Then f = 1 GoTo kk End If f = 0 For m = 1 To i1 If r(i, j)= r(i, n)Then f = 1 GoTo kk End If Next n kk: If f = 0 Then k = k + 1 End If Next j Next i End If

tjbcf = k

End Function

Public Function getnum(str, m)ss = ”“ For i = m To Len(str)If InStr(”0123456789.“, Mid(str, i, 1))<> 0 Then ss = ss & Mid(str, i, 1)Else GoTo kk End If Next i kk: getnum = Val(ss)'注意是val不是value,在sheet中是value函数，在vb中则是val函数 End Function Public Function getnum2(str, m)ss = ”“ f = 0 For i = m To Len(str)If InStr(”0123456789.“, Mid(str, i, 1))<> 0 Then ss = ss & Mid(str, i, 1)Else If f = 1 And ss <> ”“ Then GoTo kk End If f = 1 End If Next i kk: getnum2 = Val(ss)End Function

Public Function NewMmult(a, b)

a1 = a

'把range a的值赋给a1，a是几行几列的range，a1就是 几行几列的数组。注意a1是一个variant数组，即a1中的不同元素可以属性不同，a1中某个元素是字符，另一个可以是整数。

b1 = b

'把range b的值赋给b1 For i = 1 To UBound(a1, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 For j = 1 To UBound(a1, 2)

' UBound(a1, 2)指的是数组a1第2维度的大小，即a1的列数 If a1(i, j)= ”“ Then

'判断a1(i,j)是不是空白，如果是，那就赋0值。a1(i, j)= 0 End If Next j Next i

For i = 1 To UBound(b1, 1)For j = 1 To UBound(b1, 2)If b1(i, j)= ”“ Then b1(i, j)= 0 End If Next j Next i

NewMmult = Application.MMult(a1, b1)

End Function

Public Function sim(str1, str2)

If Len(str2)= 0 Then

sim = 0

GoTo kk

End If

sim = 0

For i = 1 To Len(str2)

If InStr(str1, Mid(str2, i, 1))<> 0 Then

sim = sim + 1

End If

Next i

sim = sim / Len(str2)kk: End Function Public Function sima(ByVal str1, ByVal str2)

If Len(str2)= 0 Then

sima = 0

GoTo kk

End If

sima = 0

l = Len(str2)

For i = 1 To Len(str2)

If InStr(str1, Mid(str2, i, 1))<> 0 Then

sima = sima + 1

str1 = Application.WorksheetFunction.Substitute(str1, Mid(str2, i, 1), ”“, 1)

End If

Next i

sima = sima / l kk: End Function

Public Function mcc(rng, rng1, str1, Optional rng2 = ”“, Optional str2, Optional rng3 = ”“, Optional str3, Optional rng4 = ”“, Optional str4, Optional rng5 = ”“, Optional str5)r = rng r1 = rng1 r2 = rng2 r3 = rng3 r4 = rng4 r5 = rng5 mcc = ”“

If Application.CountA(rng2)= 1 Then For i = 1 To UBound(r, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(i, 1)= str1)Then mcc = r(i, 1)GoTo kk End If Next i End If

If Application.CountA(rng3)= 1 Then For i = 1 To UBound(r, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(i, 1)= str1, r2(i, 1)= str2)Then mcc = r(i, 1)GoTo kk End If Next i End If

If Application.CountA(rng4)= 1 Then For i = 1 To UBound(r, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(i, 1)= str1, r2(i, 1)= str2, r3(i, 1)= str3)Then mcc = r(i, 1)GoTo kk End If Next i End If

If Application.CountA(rng5)= 1 Then For i = 1 To UBound(r, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(i, 1)= str1, r2(i, 1)= str2, r3(i, 1)= str3, r4(i, 1)= str4)Then mcc = r(i, 1)GoTo kk End If Next i End If

For i = 1 To UBound(r, 1)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(i, 1)= str1, r2(i, 1)= str2, r3(i, 1)= str3, r4(i, 1)= str4, r5(i, 1)= str5)Then mcc = r(i, 1)GoTo kk End If Next i

kk: End Function

Public Function mccd(rng, rng1, str1, Optional rng2 = ”“, Optional str2, Optional rng3 = ”“, Optional str3, Optional rng4 = ”“, Optional str4, Optional rng5 = ”“, Optional str5)

r = rng r1 = rng1 r2 = rng2 r3 = rng3 r4 = rng4 r5 = rng5 mccd = ”"

If Application.CountA(rng2)= 1 Then

For i = 1 To UBound(r, 2)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(1, i)= str1)Then

mccd = r(1, i)GoTo kk End If Next i End If

If Application.CountA(rng3)= 1 Then For i = 1 To UBound(r, 2)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(1, i)= str1, r2(1, i)= str2)Then mccd = r(1, i)GoTo kk End If Next i End If

If Application.CountA(rng4)= 1 Then For i = 1 To UBound(r, 2)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(1, i)= str1, r2(1, i)= str2, r3(1, i)= str3)Then mccd = r(1, i)GoTo kk End If Next i End If

If Application.CountA(rng5)= 1 Then For i = 1 To UBound(r, 2)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(1, i)= str1, r2(1, i)= str2, r3(1, i)= str3, r4(1, i)= str4)Then mccd = r(1, i)GoTo kk End If Next i End If

For i = 1 To UBound(r, 2)

' UBound(a1, 1)指的是数组a1第1维度的大小，即a1的行数 If Application.WorksheetFunction.And(r1(1, i)= str1, r2(1, i)= str2, r3(1, i)= str3, r4(1, i)= str4, r5(1, i)= str5)Then mccd = r(1, i)GoTo kk End If Next i

kk: End Function

Public Function nsim(str, rng)'r1 = rng 'r2 = rng 'str1 = str 'str2 = str

v = sima(str, r(1, 1))+ sima(r(1, 1), str2)'str1 = str

‘这里不再需要赋值，因为sima设置的是值传递byval，sima调用实参str和r(1,1)不影响实参。'str2 = str k = 1 For i = 2 To UBound(r, 1)m =(sima(str, r(i, 1))+ sima(r(i, 1), str))'str1 = str 'str2 = str If v < m Then k = i v = m End If Next i nsim = r(k, 1)End Function

第四篇：VFP-04-06-变量作用域、自定义函数汇总（定稿）

VFP-04-06变量作用域、自定义函数.doc 8-1 变量作用域、自定义函数

4.5.3 内存变量的作用域

变量的作用域：变量的作范围。程序由模块（主、子程序）组成，模块中有内存变量，内存变量有作用范围。变量的作用域，从定义变量处开始，一直向下。

1．定义全局内存变量

全局变量既可以是单个变量，也可以是数组。分别以下列格式定义。

格式1：Public <内存变量表> 格式2：Public <数组名1>(上界1[，上界2[,...])[，„] 功能：定义全局变量。

①<内存变量表>中既可含普通变量，也可含数组。

②全局变量不会自动释放。只能用release命令显式释放，或退出VFP。③VFP命令窗口中定义的变量，默认为全局变量。④全局数组的每个元素都是全局的。

第116页，例 4.35 主程序中使用子程序中定义的全局变量。*文件名Main.prg set talk off clear clear memory &&清除所有内存变量 I=2 &&默认是私有的 Do ABC ?“主程序中的输出结果:” ?“I=”+str(I,2)+“ J=”+str(J,2)set talk on return *---------------------Procedure ABC public J J=I*8 J=J+5 ?“过程中的输出结果:” ?“I=”+str(I,2)+“ J=”+str(J,2)return 小提示：要检测全局变量，请先clear memory以排除干扰。

2．定义局部内存变量

(1)什么叫局部？

更小的范围就是局部。对于一个模块，更后的部分是局部。对于主程序，子程序是局部。

(2)Private定义局部变量 VFP-04-06变量作用域、自定义函数.doc 8-2 格式1：Private <内存变量表> 格式2：Private <数组名1>(上界1[，上界2[,...])[，„] 功能：定义局部变量。

①未经定义的变量，默认是局部(Private)的。

③无论全局还是局部变量，无初值的，一律自动赋初值.F.。③局部变量作用域的子模块扩展规则

Private变量的作用域，通过调用子模块而扩展到子模块中。

图4-20 Private变量作用域子模块扩展规则

第117页，例 4.36 子程序中的局部变量，在主程序中找不到。R=100 &&默认为Private变量 Do Sub1 &&调用子程序

?P &&主程序中找不到这个变量 Return *-------------------procedure Sub1 P=2*3.14*R &&主程序中的Private变量,子程序中可用 return 3．Private隐藏内存变量的功能

(1)同名变量就近使用规则（原理）

程序中，对于同名的变量，默认使用最近的。因此，①字段变量（因为在当前工作区内）优先于（即隐藏）同名内存变量； 注：什么叫隐藏？就是被遮住，看不见，用不上。

图4-22 字段变量隐藏同名内存变量

例如 use student.dbf use student.dbf VFP-04-06变量作用域、自定义函数.doc 8-3 ?学号 &&结果是字段变量“学号”值,如“960106” 学号=“abcd” &&“=”号赋值，只给内存变量赋值 ?学号 &&结果还是优先使用字段变量

?m.学号 &&特别用“m.”指明内存变量，结果才是“abcd” ②同一模块内，小局部存变量优先于（即隐藏）大局部同名内存变量；

图4-23 Private变量隐藏同模块同名变量

③不同模块中，子模块局部内存变量优先于（即隐藏）主模块同名内存变量。

图4-26 用Private隐隐藏变量

小提示：主程序中的private变量，子程序同样可以用private屏蔽。(3)为什么大范围定义的局部变量，小范围中再定义为全局变量会出错？ 因为那样，与Private的隐藏功能相矛盾。

第118页，例 4.37 子程序中的同名局部变量隐藏主程序中同名变量。R=100 &&默认为Private变量 P=10 &&默认为Private变量 Do Sub2 &&调用子程序

?P &&仍是主程序中的值 Return *-------------------procedure Sub2 Private P &&局部变量,主程序中的同名变量被屏蔽 P=2*3.14*R &&主程序中的Private变量,子程序中可用 Return *(5)有没有不通过子程序扩展作用域的变量？

VFP-04-06变量作用域、自定义函数.doc 8-4 有，Local变量，即本地变量。如：Local x,y,z。小提示：采用Local变量，是向C语言靠拢。

4．调用过程时的数据传递

教学提示：VFP的参数传递，过程中默认传址，函数中默认传值。向过程传递数据，有两种方法。

(1)利用Private变量的作用域扩展规则，不传而传

过程中，可以直接使用主程序中的Private变量，不必传。第118页，例 4.38 计算矩形面积。G=8 &&长 K=6 &&宽 mj=0 &&面积 do sub3 ?Mj return *--------------procedure sub3 mj=G*K return(2)在过程第一句用Parameters接收参数

主程序中传出参数格式：Do <过程名> With <实际参数表> 子程序中接收参数格式： Parameters <内存变量表> 说明：

①子程序中，Parameters必须是第一句。Parameters变量是Private变量。②传值：Parameters后的变量与主程序中对应的实际参数无关。

③传引用：Parameters后的变量与主程序中对应的实际参数是同一个变量，名称可能不同而已，同时变化。

④引用隐藏实参规则

子模块中，引用参数隐藏实际参数，是因为引用参数与实际参数是同一个变量，只是在子模块中另取了一个名字（别名）而已。因此，原来的实际参数被隐藏，才不会混乱，包括实际参数是Public变量的情况。

⑤如何决定传值、传地址？由实际参数决定。过程的实际参数默认引用。要传值须实际参数加“()”，或者写成表达式。VFP-04-06变量作用域、自定义函数.doc 8-5

图4-27 传值与传引用

第119页，例 4.39 写程序运行结果。set talk off x=1 y=3 do sub4 with x,(y),5 ?x,y return *----------------Procedure sub4 parameters a,b,c a=a+b+c b=a+b-c return &&答： 9, 3 第120页，例 4.40 利用一个过程计算矩形面积，要求在主程序输出该面积值。set talk off clear input “矩形长：” to L input “矩形宽：” to W S=0 do Area with L,W,S ?“矩形面积：”,S return *--------------Procedure Area Parameters C,K,M M=C*K return

4.5.4 自定义函数 VFP-04-06变量作用域、自定义函数.doc 8-6 1．自定义函数的结构

自定义函数实际上是一个过程，只不过其Return语句后带有表达式，能向主函数返回值。

格式：

Function <函数名> [Parameters <形式参数表>] <语句序列> Return [<表达式>] 说明：

①缺省[<表达式>]返回.T.。

②与过程一样，自定义函数，可以单独以同名程序文件存储，也可以存入过程文件中。

2．自定义函数的调用

小提示：过程中叫实际参数的，函数中叫自变量。格式：[[因变量]=] <函数名>(自变量表)说明：

函数查找规则

调用函数时，先在内部函数中找；找不到再到打开的过程文件中找；再找不到，在当前文件夹中找；再找不到，出错。

①若自定义函数与内部函数同名，将不被找到，用不到。②参数表要与自变量表相对应，包括类型和个数。

③自变量表中，可以是变量，也可以是表达式。自变量默认传值。要传引用，须在自变量前面加“@”，或SET UDFPARMS TO REFERENCE设置默认为传引用。

④函数可以当过程用，调用格式为“do <函数名> with 自变量表”。但要注意，当过程用时，默认传引用，因为过程默认传引用；且放弃返回值。

小提示：自变量默认传值，是向C语言靠拢。小提示：过程默认传引用，函数默认传值。第122页，例 4.41 定义一个函数，将day()日期转成“公元 年 月 日”格式。Y=DA()?Y return *-------------Function DA D=“公元”+LTRIM(STR(YEAR(DATE())))+“年” D=D+LTRIM(STR(MONTH(DATE())))+“月” D=D+LTRIM(STR(DAY(DATE())))+“日” Return D 第122页，例 4.42 用自定义函数计算组合数。VFP-04-06变量作用域、自定义函数.doc 8-7 m!

n!(mn)!y=c(5,3)?y return *-------------------Function FAC &&阶乘factor parameters x f=1 for k=1 to x f=f*k endfor return f *-------------------Function C &&组合数conbination parameters m,n y=int(FAC(m)/(FAC(n)*FAC(m-n)))return y C(m,n)*附加 4.5.5 自定义函数的括号参数格式

“()”在VFP中是间接引用符，有传值之意。

1．自函数的“（）式”结构

格式：

Function <函数名>（[<参数表>]）<语句序列> Return [<表达式>] 说明：这是自定义函数的另一种格式。它只是将Function行和Parameter行合并成一行，其它并无差别；其调用方式也没有区别。

小提示：表单自定义函数不能用括号参数格式，故一般不学、不用。例 自定义函数fun1(x,y,z)，分别将其当过程和“()式”函数调用。A=123 B=234 C=“abc” clear do fun1 with A,(B),C &&当过程用，默认传引用，但B传值 ?“A=”,A,“B=”,B,“C=”,C D=fun1(A,@B,@C)&&当函数用，默认传值，但B和C传引用 ?“D=”,D Return *-------------------VFP-04-06变量作用域、自定义函数.doc 8-8 Function fun1(X,Y,Z)x=x+10000 y=y+10000 z=z+“12345” return X+Y

第五篇：教学：数据库-存储过程资料

教学三：存储过程

一、教学目的

（1）掌握T-SQL流控制语句；（2）掌握创建存储过程的方法；（3）掌握存储过程的执行方法；（4）掌握存储过程的管理和维护。

二、教学内容

1、创建简单存储过程

（1）创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。

if exists(select name from sysobjects where name='stu_pr'and type='p')begin print '已删除！' drop procedure stu_pr end else print '不存在，可创建！' go create procedure stu_pr as select * from Student_20103322 left outer join SC_20103322

on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where classno='051' 1

exec stu_pr

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

（1）创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。

if exists(select name from sysobjects where name='stu_proc1' and type='p')begin

print '已删除!' drop procedure stu_proc1 end else

print '不存在,可创建!' go create procedure stu_proc1 @Sdept char(8)='%',@Sname varchar(8)='林%' as select Sdept,Student_20103322.Sno,Sname,DATEDIFF(YEAR,Birth,GETDATE())age,Cname,Grade from Student_20103322,SC_20103322,Course_20103322 where Student_20103322.Sno=SC_20103322.Sno and Course_20103322.Cno=SC_20103322.Cno and Sdept like @Sdept and Sname like @Sname

execute stu_proc1 '计算机系','林红' 3

execute stu_proc1 '信息安全','胡光璟'

（2）创建一个名为Student_sc的存储过程，可查询出某段学号的同学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可设默认值）。执行该存储过程。if exists(select name from sysobjects where name='Student_sc'and type='p')begin print '已删除！' drop procedure student_sc end else print '不存在，可创建！' go create procedure Student_sc @Sno1 char(8),@Sno2 char(8)as select Student_20103322.Sno,Sname,SUM(Grade)总成绩 from Student_20103322,SC_20103322,Course_20103322 where Student_20103322.Sno=SC_20103322.Sno and Course_20103322.Cno=SC_20103322.Cno and Student_20103322.Sno>=@Sno1 and Student_20103322.Sno<=@Sno2 group by Student_20103322.Sno,Sname

execute Student_sc '20110000','20110003' 5

3、创建带输出参数的存储过程

（1）创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。总成绩可以输出，以便进一步调用。

if exists(select name from sysobjects where name='Course_sum'and type='p')begin print '已删除！' drop procedure Course_sum end else print '不存在，可创建！' go create procedure Course_sum @Cname varchar(20),@sum int output as select @sum=sum(Grade)from SC_20103322,Course_20103322 where Course_20103322.Cno=SC_20103322.Cno and Cname=@Cname

group by SC_20103322.Cno,Cname

declare @ping int exec Course_sum '高数',@ping output print '高数的考试总成绩为：'+cast(@ping as varchar(20))

（2）创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为：“XX课程的总成绩为：XX,其总分未达100分”。超过100时，显示信息为：“XX课程的总成绩为：XX”。

declare @sum int

declare @Cname varchar(20)Exec Course_sum @cname,@sum out begin

if @sum <100 print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)+ ',其总分未达分'

else

print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)end

declare @sum int

declare @Cname varchar(20)set @Cname='高数' Exec Course_sum @cname,@sum out begin

if @sum <100 print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)+ ',其总分未达分'

else

print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)end

4、创建带重编译及加密选项的存储过程

创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号由调用时输入）

if exists(select name from sysobjects where name='update_sc'and type='p')begin print '已删除！' drop procedure update_sc end else print '不存在，可创建！' go create procedure update_sc

@sno char(8),@cno char(3),@grade tinyint with RECOMPILE , ENCRYPTION as update SC_20103322 set Grade=@grade

where Sno=@sno and Cno=@cno

declare @sno char(8),@cno char(3),@grade tinyint set @sno='20103322' set @cno='003' set @grade='100' exec update_sc @sno,@cno,@grade begin print cast(@sno as varchar)+'的'+cast(@cno as varchar)+'课程成绩为：'+cast(@grade as varchar)end

5、使用T-SQL语句管理和维护存储过程

（1）使用sp_helptext查看存储过程Student_sc的定义脚本

exec sp_helptext student_sc

（2）使用select语句查看Student_sc存储过程的定义脚本（提示：通过查询表sysobjects和表syscomments）

select *

from sysobjects,syscomments where name = 'Student_sc'

（3）将存储过程stu_pr改为查询学号为2011001的学生的详细资料。

alter procedure stu_pr as select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001'

（4）删除存储过程stu_pr。

drop procedure stu_pr

6、使用SQL Server Management Studio管理存储过程

（1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_pr create procedure stu_pr as begin select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001' end

（2）查看存储过程stu_pr，并将该过程修改为查询051班女生的所有资料。

ALTER procedure [dbo].[stu_pr] as begin select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001' and Sex='女' end

（3）删除存储过程stu_pr

【完】