首頁 資料庫 mysql教程 SQLServer 存储过程

SQLServer 存储过程

Jun 07, 2016 pm 03:52 PM
sqlserver 儲存 過程

Transact-SQL中的存储过程,非常类于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 ? 存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQ

Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。

? 存储过程的概念

    存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。

    存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。

 

    1、 存储过程的优点

        A、 存储过程允许标准组件式编程

        存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。

        B、 存储过程能够实现较快的执行速度

        如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。

        C、 存储过程减轻网络流量

        对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。

        D、 存储过程可被作为一种安全机制来充分利用

        系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。

 

? 系统存储过程

    系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

    常用系统存储过程有:

1

2

3

4

5

6

7

8

9

10

11

12

<span>exec</span> sp_databases; --查看数据库

<span>exec</span> sp_tables;        --查看表

<span>exec</span> sp_columns student;--查看列

<span>exec</span> sp_helpIndex student;--查看索引

<span>exec</span> sp_helpConstraint student;--约束

<span>exec</span> sp_stored_procedures;

<span>exec</span> sp_helptext <span>'sp_stored_procedures'</span>;--查看存储过程创建、定义语句

<span>exec</span> sp_rename student, stuInfo;--修改表、索引、列的名称

<span>exec</span> sp_renamedb myTempDB, myDB;--更改数据库名称

<span>exec</span> sp_defaultdb <span>'master'</span>, <span>'myDB'</span>;--更改登录名的默认数据库

<span>exec</span> sp_helpdb;--数据库帮助,查询数据库信息

<span>exec</span> sp_helpdb master;

登入後複製


    系统存储过程示例:

1

2

3

4

5

6

7

8

9

10

11

12

13

--表重命名

<span>exec</span> sp_rename <span>'stu'</span>, <span>'stud'</span>;

<span>select</span> * <span>from</span> stud;

--列重命名

<span>exec</span> sp_rename <span>'stud.name'</span>, <span>'sName'</span>, <span>'column'</span>;

<span>exec</span> sp_help <span>'stud'</span>;

--重命名索引

<span>exec</span> sp_rename N<span>'student.idx_cid'</span>, N<span>'idx_cidd'</span>, N<span>'index'</span>;

<span>exec</span> sp_help <span>'student'</span>;

 

--查询所有存储过程

<span>select</span> * <span>from</span> sys.objects <span>where</span> type = <span>'P'</span>;

<span>select</span> * <span>from</span> sys.objects <span>where</span> type_desc <span>like</span> <span>'%pro%'</span> <span>and</span> name <span>like</span> <span>'sp%'</span>;

登入後複製

 

? 用户自定义存储过程

   1、 创建语法

1

2

3

4

5

6

7

<span>create</span> <span>proc</span> | <span>procedure</span> pro_name

    [{@参数数据类型} [=默认值] [<span>output</span>],

     {@参数数据类型} [=默认值] [<span>output</span>],

     ....

    ]

<span>as</span>

    SQL_statements

登入後複製

 

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

1

2

3

4

5

6

7

8

9

10

--创建存储过程

<span>if</span> (<span>exists</span> (<span>select</span> * <span>from</span> sys.objects <span>where</span> name = <span>'proc_get_student'</span>))

    <span>drop</span> <span>proc</span> proc_get_student

<span>go</span>

<span>create</span> <span>proc</span> proc_get_student

<span>as</span>

    <span>select</span> * <span>from</span> student;

 

--调用、执行存储过程

<span>exec</span> proc_get_student;

登入後複製

   3、 修改存储过程

1

2

3

4

--修改存储过程

<span>alter</span> <span>proc</span> proc_get_student

<span>as</span>

<span>select</span> * <span>from</span> student;

登入後複製

   4、 带参存储过程

1

2

3

4

5

6

7

8

9

10

--带参存储过程

<span>if</span> (object_id(<span>'proc_find_stu'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_find_stu

<span>go</span>

<span>create</span> <span>proc</span> proc_find_stu(@startId <span>int</span>, @endId <span>int</span>)

<span>as</span>

    <span>select</span> * <span>from</span> student <span>where</span> id <span>between</span> @startId <span>and</span> @endId

<span>go</span>

 

<span>exec</span> proc_find_stu 2, 4;

登入後複製

   5、 带通配符参数存储过程

1

2

3

4

5

6

7

8

9

10

11

--带通配符参数存储过程

<span>if</span> (object_id(<span>'proc_findStudentByName'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_findStudentByName

<span>go</span>

<span>create</span> <span>proc</span> proc_findStudentByName(@name <span>varchar</span>(20) = <span>'%j%'</span>, @nextName <span>varchar</span>(20) = <span>'%'</span>)

<span>as</span>

    <span>select</span> * <span>from</span> student <span>where</span> name <span>like</span> @name <span>and</span> name <span>like</span> @nextName;

<span>go</span>

 

<span>exec</span> proc_findStudentByName;

<span>exec</span> proc_findStudentByName <span>'%o%'</span>, <span>'t%'</span>;

登入後複製

   6、 带输出参数存储过程

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<span>if</span> (object_id(<span>'proc_getStudentRecord'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_getStudentRecord

<span>go</span>

<span>create</span> <span>proc</span> proc_getStudentRecord(

    @id <span>int</span>, --默认输入参数

    @name <span>varchar</span>(20) <span>out</span>, --输出参数

    @age <span>varchar</span>(20) <span>output</span>--输入输出参数

)

<span>as</span>

    <span>select</span> @name = name, @age = age  <span>from</span> student <span>where</span> id = @id <span>and</span> sex = @age;

<span>go</span>

 

<span>-- </span>

<span>declare</span> @id <span>int</span>,

        @name <span>varchar</span>(20),

        @temp <span>varchar</span>(20);

<span>set</span> @id = 7;

<span>set</span> @temp = 1;

<span>exec</span> proc_getStudentRecord @id, @name <span>out</span>, @temp <span>output</span>;

<span>select</span> @name, @temp;

<span>print</span> @name + <span>'#'</span> + @temp;

登入後複製


   7、 不缓存存储过程

1

2

3

4

5

6

7

8

9

10

11

--<span>WITH</span> RECOMPILE 不缓存

<span>if</span> (object_id(<span>'proc_temp'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_temp

<span>go</span>

<span>create</span> <span>proc</span> proc_temp

<span>with</span> recompile

<span>as</span>

    <span>select</span> * <span>from</span> student;

<span>go</span>

 

<span>exec</span> proc_temp;

登入後複製

   8、 加密存储过程

1

2

3

4

5

6

7

8

9

10

11

12

13

--加密WITH ENCRYPTION

<span>if</span> (object_id(<span>'proc_temp_encryption'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_temp_encryption

<span>go</span>

<span>create</span> <span>proc</span> proc_temp_encryption

<span>with</span> encryption

<span>as</span>

    <span>select</span> * <span>from</span> student;

<span>go</span>

 

<span>exec</span> proc_temp_encryption;

<span>exec</span> sp_helptext <span>'proc_temp'</span>;

<span>exec</span> sp_helptext <span>'proc_temp_encryption'</span>;

登入後複製

   9、 带游标参数存储过程

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<span>if</span> (object_id(<span>'proc_cursor'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_cursor

<span>go</span>

<span>create</span> <span>proc</span> proc_cursor

    @cur <span>cursor</span> <span>varying</span> <span>output</span>

<span>as</span>

    <span>set</span> @cur = <span>cursor</span> forward_only <span>static</span> <span>for</span>

    <span>select</span> id, name, age <span>from</span> student;

    <span>open</span> @cur;

<span>go</span>

--调用

<span>declare</span> @exec_cur <span>cursor</span>;

<span>declare</span> @id <span>int</span>,

        @name <span>varchar</span>(20),

        @age <span>int</span>;

<span>exec</span> proc_cursor @cur = @exec_cur <span>output</span>;--调用存储过程

<span>fetch</span> <span>next</span> <span>from</span> @exec_cur <span>into</span> @id, @name, @age;

<span>while</span> (@@fetch_status = 0)

<span>begin</span>

    <span>fetch</span> <span>next</span> <span>from</span> @exec_cur <span>into</span> @id, @name, @age;

    <span>print</span> <span>'id: '</span> + <span>convert</span>(<span>varchar</span>, @id) + <span>', name: '</span> + @name + <span>', age: '</span> + <span>convert</span>(<span>char</span>, @age);

<span>end</span>

<span>close</span> @exec_cur;

<span>deallocate</span> @exec_cur;--删除游标

登入後複製


   10、 分页存储过程

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

---存储过程、row_number完成分页

<span>if</span> (object_id(<span>'pro_page'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> proc_cursor

<span>go</span>

<span>create</span> <span>proc</span> pro_page

    @startIndex <span>int</span>,

    @endIndex <span>int</span>

<span>as</span>

    <span>select</span> <span>count</span>(*) <span>from</span> product

;   

    <span>select</span> * <span>from</span> (

        <span>select</span> row_number() <span>over</span>(<span>order</span> <span>by</span> pid) <span>as</span> rowId, * <span>from</span> product

    ) temp

    <span>where</span> temp.rowId <span>between</span> @startIndex <span>and</span> @endIndex

<span>go</span>

--<span>drop</span> <span>proc</span> pro_page

<span>exec</span> pro_page 1, 4

<span>--</span>

<span>--分页存储过程</span>

<span>if</span> (object_id(<span>'pro_page'</span>, <span>'P'</span>) <span>is</span> <span>not</span> <span>null</span>)

    <span>drop</span> <span>proc</span> pro_stu

<span>go</span>

<span>create</span> <span>procedure</span> pro_stu(

    @pageIndex <span>int</span>,

    @pageSize <span>int</span>

)

<span>as</span>

    <span>declare</span> @startRow <span>int</span>, @endRow <span>int</span>

    <span>set</span> @startRow = (@pageIndex - 1) * @pageSize +1

    <span>set</span> @endRow = @startRow + @pageSize -1

    <span>select</span> * <span>from</span> (

        <span>select</span> *, row_number() <span>over</span> (<span>order</span> <span>by</span> id <span>asc</span>) <span>as</span> number <span>from</span> student

    ) t

    <span>where</span> t.number <span>between</span> @startRow <span>and</span> @endRow;

 

<span>exec</span> pro_stu 2, 2;

登入後複製


? Raiserror

Raiserror返回用户定义的错误信息,可以指定严重级别,设置系统变量记录所发生的错误。

   语法如下:

1

2

3

4

5

<span>Raiserror</span>({msg_id | msg_str | @local_variable}

  {, severity, <span>state</span>}

  [,argument[,…n]]

  [<span>with</span> <span>option</span>[,…n]]

)

登入後複製

   # msg_id:在sysmessages系统表中指定的用户定义错误信息

   # msg_str:用户定义的信息,信息最大长度在2047个字符。

   # severity:用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时,raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。

    任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。

   # state:介于1至127直接的任何整数。State默认值是1。

1

2

3

4

5

<span>raiserror</span>(<span>'is error'</span>, 16, 1);

<span>select</span> * <span>from</span> sys.messages;

--使用sysmessages中定义的消息

<span>raiserror</span>(33003, 16, 1);

<span>raiserror</span>(33006, 16, 1);

登入後複製

1

 

登入後複製
登入後複製
登入後複製

1

 

登入後複製
登入後複製
登入後複製

1

 

登入後複製
登入後複製
登入後複製

1

2

3

4

5

6

<span><span><strong>存储过程有以下几个优点:</strong></span></span><br><span>1、执行速度比普通的SQL语句快

</span><span>      再运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成,所以存储过程能以极快的速度执行。</span><br><span>2、便于集中控制

</span><span>      当企业规则变化时,只需要在数据库的服务器中修改相应的存储过程,而不需要逐个的在应用程序中修改,应用程序保持不变即可,这样就省去了修改应用程序工作量。</span><br><span>3、可以降低网络的通信量</span><br><p><span>4、保证数据库的安全性和完整性

</span>      通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库,保证数据的安全;而且可以使相关的动作在一起发生,从而可以维护数据库的完整性。

<span>5、灵活性

</span>      存储过程可以用流控制语句编写,具有很强的灵活性,可以完成复杂的判断和运算,可以根据条件执行不通SQL语句。</p>

登入後複製
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱門文章

倉庫:如何復興隊友
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章

倉庫:如何復興隊友
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章標籤

記事本++7.3.1

記事本++7.3.1

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

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

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

sqlserver資料庫中已存在名為的物件怎麼解決 sqlserver資料庫中已存在名為的物件怎麼解決 Apr 05, 2024 pm 09:42 PM

sqlserver資料庫中已存在名為的物件怎麼解決

sqlserver怎麼匯入mdf文件 sqlserver怎麼匯入mdf文件 Apr 08, 2024 am 11:41 AM

sqlserver怎麼匯入mdf文件

sqlserver服務無法啟動怎麼辦 sqlserver服務無法啟動怎麼辦 Apr 05, 2024 pm 10:00 PM

sqlserver服務無法啟動怎麼辦

怎麼查看sqlserver連接埠號 怎麼查看sqlserver連接埠號 Apr 05, 2024 pm 09:57 PM

怎麼查看sqlserver連接埠號

sqlserver誤刪資料庫怎麼恢復 sqlserver誤刪資料庫怎麼恢復 Apr 05, 2024 pm 10:39 PM

sqlserver誤刪資料庫怎麼恢復

sqlserver資料庫在哪裡 sqlserver資料庫在哪裡 Apr 05, 2024 pm 08:21 PM

sqlserver資料庫在哪裡

華為明年將推創新 MED 儲存產品:機架容量超過 10 PB,功耗低於 2 kW 華為明年將推創新 MED 儲存產品:機架容量超過 10 PB,功耗低於 2 kW Mar 07, 2024 pm 10:43 PM

華為明年將推創新 MED 儲存產品:機架容量超過 10 PB,功耗低於 2 kW

sqlserver安裝失敗怎麼樣刪除乾淨 sqlserver安裝失敗怎麼樣刪除乾淨 Apr 05, 2024 pm 11:27 PM

sqlserver安裝失敗怎麼樣刪除乾淨

See all articles