首頁 資料庫 mysql教程 MSSQL之九 存储过程与函数

MSSQL之九 存储过程与函数

Jun 07, 2016 pm 02:49 PM
mssql 函數 儲存 開發 資料庫 過程

作为数据库开发人员,你可能需要一起执行一系列SQL语句,SQL Sever允许你创建能一起执行的多个语句的批处理,批处理中可以包含控制流语句以及在执行语句之前检查条件的条件逻辑。 当你需要在不同时间重复的执行批处理时,可以把批处理保存为存储过程和函数的

作为数据库开发人员,你可能需要一起执行一系列SQL语句,SQL Sever允许你创建能一起执行的多个语句的批处理,批处理中可以包含控制流语句以及在执行语句之前检查条件的条件逻辑。

当你需要在不同时间重复的执行批处理时,可以把批处理保存为存储过程和函数的数据库对象。这些数据库包含一个预编译的批处理,它可以不需要再编译而执行很多次。

本章解释如何创建批处理以执行多个SQL语句以及如何在SQL Sever2008实现存储过程和存储函数。

重点

 

?          实现批处理

?          实现存储过程

?          实现函数

预习功课

 

?        创建批处理

?        创建存储过程

?        创建带参数的存储过程

?        从存储过程返回值

?        创建标量、表值、内联表值函数

 

 

 

 

 

 创建批处理

 

 

批处理是一组一起提交给SQL  Sever执行的SQL语句。当执行批处理时SQL Sever将批处理的语句编译到一个称为执行计划的可执行单元。这样可以节省执行时间。

为了创建批处理,你可以写出多个SQL语句,后面在结尾跟着关键字Go,Go是一个命令,它指定批处理的结束。


使用变量

   创建批处理的时候,你需要在执行的时候保存一些临时值,为存储这些临时值你可以声明变量并且为它们指定值.

定义变量

  declare 变量名称 数据类型

给变量赋值

(1)  直接赋值

set 变量名称=值

(2)  从表中获得值

select 变量名称=值 from 表名

使用结构

If…else条件选择结构

   If 

    

  else

     

CASE结构

  CASE

  WHEN  THEN 

   [[WHEN    THEN ] […]]

   [ELSE  ]

 END

BEGIN…END语句块

  BEGIN

       

   END

WHILE循环结构

  WHILE

   BEGIN

       

      [BREAK]

      [CONTNUE]

      [SQL语句或程序块]

   END

 创建存储过程

 

 

什么是存储过程

   存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句,经编译后存储在数据库中的SQL程序。

.

当创建存储过程时,需要确定存储过程的三个组成部分:

 (1)所有的输入参数以及传给调用者的输出参数。

 (2)被执行的针对数据库的操作语句,包括调用其他存储过程的语句。

 (3)返回给调用者的状态值,以指明调用是成功还是失败。

常用的系统存储过程 

定义不带参数存储过程的语法

  CREATE PROCEDURE  存储过程名

            AS            --注释:表示后面是存储过程要执行的语句

            SQL语句

    GO

?       定义带参数存储过程的语法

  CREATE PROCEDURE  存储过程名

              @参数1  数据类型[ = 默认值 OUTPUT],

               …… ,

              @参数n  数据类型[ = 默认值 OUTPUT]

            AS            --注释:表示后面是存储过程要执行的语句

            SQL语句

    GO

OUTPUT:指定存储过程必须返回一个参数.该存储过程的匹配参数也必须由关键字OUTPUT创建.使用游标变量作为参数时使用该关键字.

【例9-1】创一个存储过程,以简化对sc表的数据添加工作,使得在执行该存储过程时,其参数值作为数据添加到表中。

程序清单如下:

CREATE  PROCEDURE [dbo].[ pr1_sc_ins]

@Param1 char(10),@Param2char(2),@Param3 real

AS

BEGIN

      insert into sc(sno,cno,score) values(@Param1,@Param2,@Param3)

END

【例9-2】创建一个带有参数的简单存储过程,从视图中返回指定的雇员(提供名和姓)及其职务和部门名称,该存储过程接受与传递的参数精确匹配的值

程序清单如下。

USE AdventureWorks;

GO

CREATE  PROCEDURE GetEmployees

    @lastname varchar(40),

    @firstname varchar(20)

AS

    SELECT LastName, FirstName, JobTitle,Department

    FROM HumanResources.vEmployeeDepartment

    WHERE FirstName = @firstname AND LastName =@lastname;

GO

 

?       调用存储过程语法

EXEC   PROCEDURE 存储过程名

  【例9-3】执行存储过程au_infor_all。

au_infor_all 存储过程可以通过以下方法执行:

EXECUTE(EXEC) au_infor_all

【例9-4】使用 EXECUTE 命令传递参数,执行例9-1定义的存储过程pr1_sc_ins。

sc_ins存储过程可以通过以下方法执行:

EXEC pr1_sc_ins ‘3130040101’,’c1’,85

当然,在执行过程中变量可以显式命名:

EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’,@Param3=85

【例9-5】 执行例9-2定义的存储过程GetEmployees。

GetEmployees存储过程可以通过以下方法执行:

EXECUTE(EXEC) GetEmployees'Dull', 'Ann'  或者

EXECUTE(EXEC) GetEmployees@lastname = 'Dull', @firstname = 'Ann'  或者

EXECUTE(EXEC) GetEmployees@firstname = 'Ann', @lastname = 'Dull'

修改存储过程


重命名存储过程

      例: 把存储名称p_name修改为 p-address

        EXEC sp_rename ‘p_name’,’p_address’

 删除存储过程

      例: 删除存储过程p_name

          DROP  PROCEDURE p_name

 实现函数

 

 

你可以创建函数来永久存储一系列SQL语句,根据函数返回值形式的不同,用户定义的函数有标量函数和表值函数.

?       创建函数的语法:

CreateFunction[schema_name] function_name

([{@parameter_name[AS][type_schema_name.]

  parameter_data_type

  [=default] }

  [,…n ]

 ]

 )

Returnsreturn_data_type

[WIIH[,…n] ]

[AS]

BEGIN

function_body

        return expression

END

 

@parameter_name 是在函数中的参数.可以有一个或多个被声明的参数.

[typr_schema_name] parameter_data_type 是参数的数据类型,和可选的它的所属的模式.

[=default] 是参数的默认值.

return_data_type 是一个标量的用户定义函数的返回值.

function body 指定一系列T-SQL语句.

?       创建标量函数

标量函数接受一个参数并且返回在RETURNS从句中指定的类型的一个数据值。标量函数可以返回除了文本、ntext、图片、光标和时间戳之外的任何数据类型。有些标量函数,例如current_timestamp,不需要任何参数。

语法

create function 函数名(@变量名1 数据类型)

returns 返回值的数据类型

as

begin

 declare @变量名2 数据类型

 select @变量名2=sum(列名) from 表名1 where 主键名=@变量2

 return @变量名2

end

 

select 函数名(主键名) from 表名2

例如:

     CREATE FUNCTION HumanResources.MonthlySal  (@PayRate float)

     RETURNS float

     AS

     BEGIN

     RETURN (@PayRate * 8 * 30)

     END

例如:

     DECLARE @PayRate float

     SET @PauRate = HumanResources.MonthlySal(12.25)

     PRINT @PauRate

注释:上述代码中,@PayRate是一个变量,它将存储MonthlySal函数返回的值。

创建表值函数

内联表值函数从一个SELECT语句的结果集返回一个表数据的变量。内联函数不再BEGIN和END语句中包含函数体。

例子1:

  Create function fx_Department_name(@grnamenavarchar(20))

  Return table

As

Return(

Select *

FromHumanResources.Department

WhereGroupName=@grname

)

GO

这里的内联表,接收一组名称作为参数并且饭回来自Department表属于组的部门的详情。

使用 SELECT*FROM fx_Department_name 可以查看上述代码的输出。

多语句表值函数 

多语句表值函数使用多个语句来创建表,它被返回给调用语句。函数体包含BEGIN.。。。END块,它保存一系列T-SQL语句以创建和插入行盗临时表。临时表被在结果集中返回,并且基于函数中提到的规范创建。

语法:

create  function 函数名(@变量名1 数据类型)

returns @变量名2 table

(

  和创建表中的内容一样

)

as

begin

 insert @变量名2 select表中的列名 from  表名 ----指把表中的内容加到新创建的函数表中

 where 表中的另一个列名>@变量名1

  insert @变量名2values(.......)

end

select * from 函数名(表中的另一个列中的内容)

 

 

例如:

    CREATE  FUNCTION PayRate (@rate money)

    RETURNS  @table TABLE

   (EmployeeID  int  NOT NULL,

   RateChangeDate  datetime  NOT NULL,

    Ratemoney  NOT NULL,

   PayFrequency  tinyint  NOT NULL,

   ModifiedDate  datetime  NOTNULL)

    AS

    BEGIN

    INSERT@table

    SELECT *

    FROMHumanResources.EmployeePayHistory

    WHERE Rate> @rate

    RETURN

    END

语句:

    SELECT *FROM PayRate(45)

注释:

     函数以在函数内创建的临时表@table,的形式返回一个结果集。以上的语句执行函数。

 

 

 

 

                                                                                     

实践问题

 

1、批处理的用途是什么?

2、在批处理中本地变量的范围是什么?

3、存储过程如何返回值?

4、下面的哪个结构被使用,当你需要重复执行一系列T-SQL语句的时候?

   A、try – catch块

   B、while语句

   C、if-else语句

   D、case语句

 

小结

 

1、批处理是一系列一起提交到服务器执行的SQL语句。

2、你可以使用变量存储一个临时值。

3、你可以使用print语句来在屏幕上显示一个变量的内容。

4、你可以在批处理中使用注释给代码写注释。

5、你可以使用 if –else语句从条件执行SQL语句。

6、CASE语句求一系列条件的值并且返回各种可能结果中的一个。

7、你可以在批处理中使用WHILE语句以允许一系列T-SQL语句重复执行,只要给定条件为真。

8、BREAK语句导致从WHILE循环中退出。

9、存储过程是各种T-SQL语句的集合,它被存储在一个名字下,并且作为一个单元执行。

10、存储过程可以使用CREATE PROCEDURE语句创建。

11、存储过程允许你声明参数、变量和使用T-SQL语句并且编程逻辑。

12、存储过程提供更好的性能、安全性和准确性并且减少网络拥塞

13、存储过程通过输入参数接受数据。

14、存储过程通过输出参数或返回语句返回数据。

15、存储过程可以使用EXECUTE语句执行。

16、存储过程使用alter procedute语句执行修改

17、用户定函数是一个数据库对象,它包含一系列T-SQL语句。

18、用户定义函数可以返回一个单一标量值或结果集。
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1664
14
CakePHP 教程
1422
52
Laravel 教程
1316
25
PHP教程
1267
29
C# 教程
1239
24
iOS 18 新增「已復原」相簿功能 可找回遺失或損壞的照片 iOS 18 新增「已復原」相簿功能 可找回遺失或損壞的照片 Jul 18, 2024 am 05:48 AM

蘋果公司最新發布的iOS18、iPadOS18以及macOSSequoia系統為Photos應用程式增添了一項重要功能,旨在幫助用戶輕鬆恢復因各種原因遺失或損壞的照片和影片。這項新功能在Photos應用的"工具"部分引入了一個名為"已恢復"的相冊,當用戶設備中存在未納入其照片庫的圖片或影片時,該相冊將自動顯示。 "已恢復"相簿的出現為因資料庫損壞、相機應用未正確保存至照片庫或第三方應用管理照片庫時照片和視頻丟失提供了解決方案。使用者只需簡單幾步

excel函數公式大全 excel函數公式大全 May 07, 2024 pm 12:04 PM

1. SUM函數,用於對一列或一組單元格中的數字進行求和,例如:=SUM(A1:J10)。 2、AVERAGE函數,用於計算一列或一組儲存格中的數字的平均值,例如:=AVERAGE(A1:A10)。 3.COUNT函數,用於計算一列或一組單元格中的數字或文字的數量,例如:=COUNT(A1:A10)4、IF函數,用於根據指定的條件進行邏輯判斷,並返回相應的結果。

在PHP中使用MySQLi建立資料庫連線的詳盡教學 在PHP中使用MySQLi建立資料庫連線的詳盡教學 Jun 04, 2024 pm 01:42 PM

如何在PHP中使用MySQLi建立資料庫連線:包含MySQLi擴充(require_once)建立連線函數(functionconnect_to_db)呼叫連線函數($conn=connect_to_db())執行查詢($result=$conn->query())關閉連線( $conn->close())

如何在PHP中處理資料庫連線錯誤 如何在PHP中處理資料庫連線錯誤 Jun 05, 2024 pm 02:16 PM

PHP處理資料庫連線報錯,可以使用下列步驟:使用mysqli_connect_errno()取得錯誤代碼。使用mysqli_connect_error()取得錯誤訊息。透過擷取並記錄這些錯誤訊息,可以輕鬆識別並解決資料庫連接問題,確保應用程式的順暢運作。

Golang 函數接收 map 參數時的注意事項 Golang 函數接收 map 參數時的注意事項 Jun 04, 2024 am 10:31 AM

在Go中傳遞map給函數時,預設會建立副本,對副本的修改不影響原map。如果需要修改原始map,可透過指標傳遞。空map需小心處理,因為技術上是nil指針,傳遞空map給期望非空map的函數會發生錯誤。

如何在 Golang 中使用資料庫回呼函數? 如何在 Golang 中使用資料庫回呼函數? Jun 03, 2024 pm 02:20 PM

在Golang中使用資料庫回呼函數可以實現:在指定資料庫操作完成後執行自訂程式碼。透過單獨的函數新增自訂行為,無需編寫額外程式碼。回調函數可用於插入、更新、刪除和查詢操作。必須使用sql.Exec、sql.QueryRow或sql.Query函數才能使用回呼函數。

如何在 Golang 中將 JSON 資料保存到資料庫中? 如何在 Golang 中將 JSON 資料保存到資料庫中? Jun 06, 2024 am 11:24 AM

可以透過使用gjson函式庫或json.Unmarshal函數將JSON資料儲存到MySQL資料庫中。 gjson函式庫提供了方便的方法來解析JSON字段,而json.Unmarshal函數需要一個目標類型指標來解組JSON資料。這兩種方法都需要準備SQL語句和執行插入操作來將資料持久化到資料庫中。

如何用 Golang 連接遠端資料庫? 如何用 Golang 連接遠端資料庫? Jun 01, 2024 pm 08:31 PM

透過Go標準庫database/sql包,可以連接到MySQL、PostgreSQL或SQLite等遠端資料庫:建立包含資料庫連接資訊的連接字串。使用sql.Open()函數開啟資料庫連線。執行SQL查詢和插入操作等資料庫操作。使用defer關閉資料庫連線以釋放資源。

See all articles