第一篇：SQL Sever 2005教案第13章 存儲過程及自定義函數(shù)

什么是存儲過程，在存儲在服務器上的T-SQL語句的命名集合，是封裝性任務的方法，支持變量及條件的編程。

SQL Server的存儲過程與其他編程語言中的過程（包括函數(shù)）類似，可以包含數(shù)據(jù)庫操作（調用其他過程）的編程語句，可以接受參數(shù)，可以返回狀態(tài)值以表明成功或失敗，以輸出參數(shù)的形式將多個值返回至調用過程

SQL Server支持五種類型的存儲過程：

系統(tǒng)存儲過程（sp_）：存儲在master數(shù)據(jù)庫中。

本地存儲過程：在單獨的用戶數(shù)據(jù)庫中。

臨時存儲過程：局部的以#開頭，全局的以##開頭。

遠程存儲過程：分布式查詢支持此功能。

擴展存儲過程：在SQL Server環(huán)境外執(zhí)行。

存儲過程的優(yōu)點 封裝商務邏輯，若規(guī)則或策略改變只需修改存儲過程就可以直接使用，屏蔽數(shù)據(jù)庫的詳細資料，用戶不需要訪問底層數(shù)據(jù)庫和數(shù)據(jù)庫對象。提供安全機制，只需要提供存儲過程的權限而不需要提供整個數(shù)據(jù)庫中數(shù)據(jù)的一個權限。另外，存儲過程能夠通過預編譯的語句來確定執(zhí)行哪一部分而不是都執(zhí)行。在傳輸過程中傳輸?shù)拇鎯^程而不是數(shù)據(jù)，減少了通信量，能夠實現(xiàn)一個較快的執(zhí)行速度。

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

在存儲過程里可以包含任何數(shù)目和類型的T-SQL語句，但不能包含create proc、create trigger、create view 執(zhí)行創(chuàng)建存儲過程的用戶必須是sysadmin、db_owner 或 db_ddladmin角色的成員，或必須擁有 CREATE PROCEDURE 權限 存儲過程有大小的限制，最大為128M

存儲過程可以傳遞參數(shù)，創(chuàng)建存儲過程，定義兩個浮點型的參數(shù)，無返回值 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

/*執(zhí)行語句,輸入兩個價格值作為參數(shù)值*/ exec titlespro 10,19

指定參數(shù)的依據(jù)和指導原則

所有的輸入?yún)?shù)值都應該在存儲過程開始的時候進行檢查，以盡早捕獲缺失值和非法值

應該為參數(shù)提供合適的默認值，可以未指定參數(shù)值的基礎上執(zhí)行存儲過程

一個存儲過程最多可以有1024個參數(shù)

不同存儲過程可以使用相同的參數(shù)名 使用參數(shù)的指導原則

可以使用@參數(shù)=值的格式來指定參數(shù)，此方法可以按任意順序來傳遞參數(shù)

對于有默認值的參數(shù)在調用存儲過程的過程中可以不指定參數(shù)值

存儲過程可以使用輸出參數(shù)

--創(chuàng)建存儲過程輸入兩個輸入?yún)?shù)，定義一個輸出參數(shù) CREATE PROCEDURE Mathadd @m1 int, @m2 int, @result int OUTPUT--定義輸出參數(shù) AS SET @result=@m1+@m2 GO

調用過程如下：

declare @resultvalues int

exec mathadd 12,16,@resultvalues output--輸出參數(shù)的值賦給變量 print 'The result is: '+convert(char,@resultvalues)

存儲過程通過輸出參數(shù)向調用它的存儲過程或客戶端返回信息，通過輸出參數(shù)，存儲過程的運行結果可以保留到程序運行結束。輸出參數(shù)接受需要注意：

調用語句必須包含一個變量名，以接受返回值。

可以在T-SQL語句中使用返回變量 輸出參數(shù)可以是任意類型，除了text和image 輸出參數(shù)可以是游標

對于已經(jīng)創(chuàng)建好的存儲過程，如果存儲過程需要修改，可以顯式地重新編譯，但應盡量少做。

在創(chuàng)建存儲過程之前通常會檢查此存儲過程是否存在，如果存在可以選擇刪除后重建，或改變名字

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，因為存儲過程屬于數(shù)據(jù)庫對象

drop procedure pro_titles

對于數(shù)據(jù)庫中的所有存儲過程或者觸發(fā)器，如果需要將其全部重新編譯，那么可以使用以下語句，EXEC sp_recompile titles--重新編譯所有的存儲過程或者觸發(fā)器

在程序中通常會出現(xiàn)因為運行錯誤而出現(xiàn)的各種錯誤提示，其大部分都不能被用戶容易讀懂，因此可以自定義錯誤信息，來提示用戶錯誤的具體原因。--自定義錯誤信息

EXEC sp_addmessage

@msgnum = 50010,---錯誤編號 @severity = 10,----嚴重級別

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

創(chuàng)建好自定義錯誤信息之后，就可以在程序中使用錯誤信息

--調用自定義錯誤信息

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

創(chuàng)建存儲過程的方法上面已經(jīng)介紹結束，如果想要查看存儲過程的代碼，就需要使用到以下的關鍵字

--查看存儲過程

EXEC sp_helptext UpdateCustomerPhone

而對于以存在的存儲過程，在調用的過程由于某些原因需要重新命名的化，使用以下語句

--重命名存儲過程

EXEC sp_rename reptq1, newproc

用戶自定義函數(shù)部分

函數(shù)和存儲過程同樣都有輸入和輸出參數(shù)，單數(shù)輸入和輸出參數(shù)的聲明方式有些區(qū)別--創(chuàng)建函數(shù) 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--結束函數(shù)定義 GO-

使用函數(shù)參數(shù)，當使用命名函數(shù)的時候，參數(shù)的次序不必按照在函數(shù)中聲明的次序，但必須包含所有參數(shù)，不能省略任何參數(shù)

使用函數(shù)的過程同以前學習過的系統(tǒng)函數(shù)的使用方法想類似，在輸出或查詢語句中使用函數(shù)名及相應的床底若干參數(shù)即可。

--使用函數(shù)

SELECT pubs.dbo.workyearwage('1991-7-1',getdate(),15)--傳參并輸出結果

As work_year_wage

在函數(shù)的返回值方面可以直接聲明返回參數(shù)，或者聲明返回值類型--計算立方體函數(shù)

CREATE FUNCTION CubicVolume(@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1))--定義輸入?yún)?shù) RETURNS decimal(12,3)--定義返回值類型 AS BEGIN RETURN(@CubeLength * @CubeWidth * @CubeHeight)--返回結果 END

除特定類型的返回值之外，還可以返回表類型的結果集

--內聯(lián)表值型用戶自定義函數(shù)

CREATE FUNCTION orderfirms(@productid INT)--定義一個整型參數(shù) RETURNS TABLE--返回值為表的結果集 AS RETURN(SELECT * FROM jobs WHERE min_lvl>@productid)--返回查詢結果 GO

或者返回值是一張臨時表

--多聲明表值型用戶自定義函數(shù) USE Northwind go CREATE FUNCTION my_function(@regionParameter varchar(25))--定義一個函數(shù)的參數(shù)

returns @my_table TABLE--函數(shù)返回值為表的結果集，并定義表的結果集如下(city varchar(15)null, companyName varchar(40)not null, contactName varchar(30)null)AS BEGIN INSERT @my_table--從表customers中查詢數(shù)據(jù)插入到@my_table中 SELECT city,companyName,contactName FROM Customers WHERE city=@regionParameter RETURN--返回結果集 END

表值函數(shù)：

函數(shù)體內只允許如下語句：

賦值語句

流程控制語句

用于定義函數(shù)局部數(shù)據(jù)變量和游標的declare語句

Select語句，將其后的表達式將賦予函數(shù)的局部變量

游標操作，僅允許使用fetch語句通過into子句給局部變量賦值，不允許使用fetch語句將數(shù)據(jù)返回到客戶端

針對上面的例子（內嵌表值函數(shù)）需要注意的是：

return子句在括號中包含單個select語句，select語句的結果集構成函數(shù)所返回的表 函數(shù)體不由begin和end分隔

Return指定table作為返回的數(shù)據(jù)類型

不必定義返回變量的格式，因為它由return子句中的select語句的結果集的格式設置

查看創(chuàng)建用戶自定義函數(shù)的方式有兩種 查看特定用戶自定義函數(shù)

--查看用戶自定義函數(shù) USE pubs go EXEC sp_helptext my_function--調用存儲過程 GO

查看所有用戶自定義函數(shù)

--通過系統(tǒng)表查看用戶自定義函數(shù) use pubs go select name, type, crdate from sysobjects where type='FN'--go

當創(chuàng)建的用戶自定義函數(shù)需要修改時，使用如下語句

--修改函數(shù)cubicvolume函數(shù)

ALTER FUNCTION CubicVolume--這句表示聲明修改用戶自定義函數(shù)，其他語句不變

(@CubeLength decimal(4,1), @CubeWidth decimal(4,1))--定義輸入?yún)?shù) RETURNS decimal(12,3)--定義返回值類型 AS BEGIN RETURN(@CubeLength * @CubeWidth)--返回結果 END 基本語法和創(chuàng)建的類型，只需要將create更改為alter即可

刪除存儲過程，由于存儲過程為數(shù)據(jù)庫對象因此刪除使用drop drop function cubicvolume

第二篇：第5章 視圖、存儲過程和用戶自定義函數(shù)復習題范文

第5章 視圖、存儲過程和用戶自定函數(shù)

1．什么是基本表？什么是視圖？兩者的區(qū)別和聯(lián)系是什么？

答：

基本表是本身獨立存在的表，在 sQL 中一個關系就對應一個表。視圖是從一個或幾個基本表導出的表。視圖本身不獨立存儲在數(shù)據(jù)庫中，是一個虛表。即數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導出視圖的基本表中。視圖在概念上與基本表等同，用戶可以如同基本表那樣使用視圖，可以在視圖上再定義視圖。．試述視圖的優(yōu)點。

答

(l）視圖能夠簡化用戶的操作；(2）視圖使用戶能以多種角度看待同一數(shù)據(jù)；(3）視圖對重構數(shù)據(jù)庫提供了一定程度的邏輯獨立性；(4）視圖能夠對機密數(shù)據(jù)提供安全保護。．所有的視圖是否都可以更新？為什么？

答:

不是。視圖是不實際存儲數(shù)據(jù)的虛表，因此對視圖的更新，最終要轉換為對基本表的更新。因為有些視圖的更新不能惟一有意義地轉換成對相應基本表的更新，所以，并不是所有的視圖都是可更新的.4 ．哪類視圖是可以更新的？哪類視圖是不可更新的？各舉一例說明。

答：基本表的行列子集視圖一般是可更新的。若視圖的屬性來自集函數(shù)、表達式，則該視圖肯定是不可以更新的。

5．教材P89習題1-5。

第三篇：vba自定義函數(shù)小結

小結：

函數(shù)參數(shù)前面標記byval，實現(xiàn)的是實參與形參之間的值傳遞，即形參具有了實參的值或者說建立了一個實參的副本給了形參，這樣對形參的操作不會影響實參；

函數(shù)參數(shù)前面標記byref，實現(xiàn)的是實參與形參之間的引用傳遞，這樣對形參的操作會影響實參，或者說對形參的操作等同于對實參的操作；

在函數(shù)內部使用“r=形參”，實現(xiàn)的是把形參的副本給r，在函數(shù)中對r操作不會影像形參，如果使用“set r=形參”，那么對r的操作會影響形參，這里實現(xiàn)的效果是r只是形參的一個別名而已，所以對r的操作等同于對形參的操作。

在函數(shù)中形參前面不標記byref和byval，默認是引用傳遞byref，但是這種情況下如果實參是excel中worksheet的range區(qū)域，則默認是值傳遞。

有時調試結果不符合預期，比如值傳遞，對””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函數(shù)，在vb中則是val函數(shù) 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就是 幾行幾列的數(shù)組。注意a1是一個variant數(shù)組，即a1中的不同元素可以屬性不同，a1中某個元素是字符，另一個可以是整數(shù)。

b1 = b

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

' UBound(a1, 1)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) For j = 1 To UBound(a1, 2)

' UBound(a1, 2)指的是數(shù)組a1第2維度的大小，即a1的列數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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)指的是數(shù)組a1第1維度的大小，即a1的行數(shù) 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-變量作用域、自定義函數(shù)匯總（定稿）

VFP-04-06變量作用域、自定義函數(shù).doc 8-1 變量作用域、自定義函數(shù)

4.5.3 內存變量的作用域

變量的作用域：變量的作范圍。程序由模塊（主、子程序）組成，模塊中有內存變量，內存變量有作用范圍。變量的作用域，從定義變量處開始，一直向下。

1．定義全局內存變量

全局變量既可以是單個變量，也可以是數(shù)組。分別以下列格式定義。

格式1：Public <內存變量表> 格式2：Public <數(shù)組名1>(上界1[，上界2[,...])[，?] 功能：定義全局變量。

①<內存變量表>中既可含普通變量，也可含數(shù)組。

②全局變量不會自動釋放。只能用release命令顯式釋放，或退出VFP。③VFP命令窗口中定義的變量，默認為全局變量。④全局數(shù)組的每個元素都是全局的。

第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變量作用域、自定義函數(shù).doc 8-2 格式1：Private <內存變量表> 格式2：Private <數(shù)組名1>(上界1[，上界2[,...])[，?] 功能：定義局部變量。

①未經(jīng)定義的變量，默認是局部(Private)的。

③無論全局還是局部變量，無初值的，一律自動賦初值.F.。③局部變量作用域的子模塊擴展規(guī)則

Private變量的作用域，通過調用子模塊而擴展到子模塊中。

圖4-20 Private變量作用域子模塊擴展規(guī)則

第117頁，例 4.36 子程序中的局部變量，在主程序中找不到。R=100 &&默認為Private變量 Do Sub1 &&調用子程序

?P &&主程序中找不到這個變量 Return *-------------------procedure Sub1 P=2*3.14*R &&主程序中的Private變量,子程序中可用 return 3．Private隱藏內存變量的功能

(1)同名變量就近使用規(guī)則（原理）

程序中，對于同名的變量，默認使用最近的。因此，①字段變量（因為在當前工作區(qū)內）優(yōu)先于（即隱藏）同名內存變量； 注：什么叫隱藏？就是被遮住，看不見，用不上。

圖4-22 字段變量隱藏同名內存變量

例如 use student.dbf use student.dbf VFP-04-06變量作用域、自定義函數(shù).doc 8-3 ?學號 &&結果是字段變量“學號”值,如“960106” 學號=“abcd” &&“=”號賦值，只給內存變量賦值 ?學號 &&結果還是優(yōu)先使用字段變量

?m.學號 &&特別用“m.”指明內存變量，結果才是“abcd” ②同一模塊內，小局部存變量優(yōu)先于（即隱藏）大局部同名內存變量；

圖4-23 Private變量隱藏同模塊同名變量

③不同模塊中，子模塊局部內存變量優(yōu)先于（即隱藏）主模塊同名內存變量。

圖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變量作用域、自定義函數(shù).doc 8-4 有，Local變量，即本地變量。如：Local x,y,z。小提示：采用Local變量，是向C語言靠攏。

4．調用過程時的數(shù)據(jù)傳遞

教學提示：VFP的參數(shù)傳遞，過程中默認傳址，函數(shù)中默認傳值。向過程傳遞數(shù)據(jù)，有兩種方法。

(1)利用Private變量的作用域擴展規(guī)則，不傳而傳

過程中，可以直接使用主程序中的Private變量，不必傳。第118頁，例 4.38 計算矩形面積。G=8 &&長 K=6 &&寬 mj=0 &&面積 do sub3 ?Mj return *--------------procedure sub3 mj=G*K return(2)在過程第一句用Parameters接收參數(shù)

主程序中傳出參數(shù)格式：Do <過程名> With <實際參數(shù)表> 子程序中接收參數(shù)格式： Parameters <內存變量表> 說明：

①子程序中，Parameters必須是第一句。Parameters變量是Private變量。②傳值：Parameters后的變量與主程序中對應的實際參數(shù)無關。

③傳引用：Parameters后的變量與主程序中對應的實際參數(shù)是同一個變量，名稱可能不同而已，同時變化。

④引用隱藏實參規(guī)則

子模塊中，引用參數(shù)隱藏實際參數(shù)，是因為引用參數(shù)與實際參數(shù)是同一個變量，只是在子模塊中另取了一個名字（別名）而已。因此，原來的實際參數(shù)被隱藏，才不會混亂，包括實際參數(shù)是Public變量的情況。

⑤如何決定傳值、傳地址？由實際參數(shù)決定。過程的實際參數(shù)默認引用。要傳值須實際參數(shù)加“()”，或者寫成表達式。VFP-04-06變量作用域、自定義函數(shù).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 自定義函數(shù) VFP-04-06變量作用域、自定義函數(shù).doc 8-6 1．自定義函數(shù)的結構

自定義函數(shù)實際上是一個過程，只不過其Return語句后帶有表達式，能向主函數(shù)返回值。

格式：

Function <函數(shù)名> [Parameters <形式參數(shù)表>] <語句序列> Return [<表達式>] 說明：

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

②與過程一樣，自定義函數(shù)，可以單獨以同名程序文件存儲，也可以存入過程文件中。

2．自定義函數(shù)的調用

小提示：過程中叫實際參數(shù)的，函數(shù)中叫自變量。格式：[[因變量]=] <函數(shù)名>(自變量表)說明：

函數(shù)查找規(guī)則

調用函數(shù)時，先在內部函數(shù)中找；找不到再到打開的過程文件中找；再找不到，在當前文件夾中找；再找不到，出錯。

①若自定義函數(shù)與內部函數(shù)同名，將不被找到，用不到。②參數(shù)表要與自變量表相對應，包括類型和個數(shù)。

③自變量表中，可以是變量，也可以是表達式。自變量默認傳值。要傳引用，須在自變量前面加“@”，或SET UDFPARMS TO REFERENCE設置默認為傳引用。

④函數(shù)可以當過程用，調用格式為“do <函數(shù)名> with 自變量表”。但要注意，當過程用時，默認傳引用，因為過程默認傳引用；且放棄返回值。

小提示：自變量默認傳值，是向C語言靠攏。小提示：過程默認傳引用，函數(shù)默認傳值。第122頁，例 4.41 定義一個函數(shù)，將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 用自定義函數(shù)計算組合數(shù)。VFP-04-06變量作用域、自定義函數(shù).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 &&組合數(shù)conbination parameters m,n y=int(FAC(m)/(FAC(n)*FAC(m-n)))return y C(m,n)?*附加 4.5.5 自定義函數(shù)的括號參數(shù)格式

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

1．自函數(shù)的“（）式”結構

格式：

Function <函數(shù)名>（[<參數(shù)表>]）<語句序列> Return [<表達式>] 說明：這是自定義函數(shù)的另一種格式。它只是將Function行和Parameter行合并成一行，其它并無差別；其調用方式也沒有區(qū)別。

小提示：表單自定義函數(shù)不能用括號參數(shù)格式，故一般不學、不用。例 自定義函數(shù)fun1(x,y,z)，分別將其當過程和“()式”函數(shù)調用。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)&&當函數(shù)用，默認傳值，但B和C傳引用 ?“D=”,D Return *-------------------VFP-04-06變量作用域、自定義函數(shù).doc 8-8 Function fun1(X,Y,Z)x=x+10000 y=y+10000 z=z+“12345” return X+Y

第五篇：教學：數(shù)據(jù)庫-存儲過程資料

教學三：存儲過程

一、教學目的

（1）掌握T-SQL流控制語句；（2）掌握創(chuàng)建存儲過程的方法；（3）掌握存儲過程的執(zhí)行方法；（4）掌握存儲過程的管理和維護。

二、教學內容

1、創(chuàng)建簡單存儲過程

（1）創(chuàng)建一個名為stu_pr的存儲過程，該存儲過程能查詢出051班學生的所有資料，包括學生的基本信息、學生的選課信息（含未選課同學的信息）。要求在創(chuàng)建存儲過程前請判斷該存儲過程是否已創(chuàng)建，若已創(chuàng)建則先刪除，并給出“已刪除！”信息，否則就給出“不存在，可創(chuàng)建！”的信息。

if exists(select name from sysobjects where name='stu_pr'and type='p')begin print '已刪除！' drop procedure stu_pr end else print '不存在，可創(chuàng)建！' 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、創(chuàng)建帶參數(shù)的存儲過程

（1）創(chuàng)建一個名為stu_proc1的存儲過程，查詢某系、某姓名的學生的學號、姓名、年齡，選修課程名、成績。系名和姓名在調用該存儲過程時輸入，其默認值分別為“%”與“林%”。執(zhí)行該存儲過程，用多種參數(shù)加以測試。

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

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

print '不存在,可創(chuàng)建!' 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）創(chuàng)建一個名為Student_sc的存儲過程，可查詢出某段學號的同學的學號、姓名、總成績。（學號起始號與終止號在調用時輸入，可設默認值）。執(zhí)行該存儲過程。if exists(select name from sysobjects where name='Student_sc'and type='p')begin print '已刪除！' drop procedure student_sc end else print '不存在，可創(chuàng)建！' 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、創(chuàng)建帶輸出參數(shù)的存儲過程

（1）創(chuàng)建一個名為Course_sum的存儲過程，可查詢某門課程考試的總成績。總成績可以輸出，以便進一步調用。

if exists(select name from sysobjects where name='Course_sum'and type='p')begin print '已刪除！' drop procedure Course_sum end else print '不存在，可創(chuàng)建！' 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 '高數(shù)',@ping output print '高數(shù)的考試總成績?yōu)椋?+cast(@ping as varchar(20))

（2）創(chuàng)建一執(zhí)行該存儲過程的批處理，要求當總成績小于100時，顯示信息為：“XX課程的總成績?yōu)椋篨X,其總分未達100分”。超過100時，顯示信息為：“XX課程的總成績?yōu)椋篨X”。

declare @sum int

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

if @sum <100 print cast(@cname as varchar)+'課程的總成績?yōu)?'+cast(@sum as varchar)+ ',其總分未達分'

else

print cast(@cname as varchar)+'課程的總成績?yōu)?'+cast(@sum as varchar)end

declare @sum int

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

if @sum <100 print cast(@cname as varchar)+'課程的總成績?yōu)?'+cast(@sum as varchar)+ ',其總分未達分'

else

print cast(@cname as varchar)+'課程的總成績?yōu)?'+cast(@sum as varchar)end

4、創(chuàng)建帶重編譯及加密選項的存儲過程

創(chuàng)建一個名為update_sc、并帶重編譯及加密選項的存儲過程，可更新指定學號、指定課程號的學生的課程成績。（學號、課程號由調用時輸入）

if exists(select name from sysobjects where name='update_sc'and type='p')begin print '已刪除！' drop procedure update_sc end else print '不存在，可創(chuàng)建！' 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)+'課程成績?yōu)椋?+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中重新創(chuàng)建剛刪除的存儲過程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

【完】