存储过程Word文档格式.docx

存储过程Word文档格式.docx

《存储过程Word文档格式.docx》由会员分享，可在线阅读，更多相关《存储过程Word文档格式.docx（8页珍藏版）》请在冰点文库上搜索。

Ø

系统存储过程

系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。

系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。

尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。

有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

常用系统存储过程有：

execsp_databases;

--查看数据库

execsp_tables;

--查看表

execsp_columnsstudent;

--查看列

execsp_helpIndexstudent;

--查看索引

execsp_helpConstraintstudent;

--约束

execsp_stored_procedures;

execsp_helptext'

sp_stored_procedures'

;

--查看存储过程创建、定义语句

execsp_renamestudent,stuInfo;

--修改表、索引、列的名称

execsp_renamedbmyTempDB,myDB;

--更改数据库名称

execsp_defaultdb'

master'

'

myDB'

--更改登录名的默认数据库

execsp_helpdb;

--数据库帮助，查询数据库信息

execsp_helpdbmaster;

系统存储过程示例：

--表重命名

execsp_rename'

stu'

stud'

select*fromstud;

--列重命名

stud.name'

sName'

column'

execsp_help'

--重命名索引

execsp_renameN'

student.idx_cid'

N'

idx_cidd'

index'

student'

--查询所有存储过程

select*fromsys.objectswheretype='

P'

select*fromsys.objectswheretype_desclike'

%pro%'

andnamelike'

sp%'

用户自定义存储过程

1、创建语法

createproc|procedurepro_name

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

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

....

]

as

SQL_statements

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

--创建存储过程

if（exists（select*fromsys.objectswherename='

proc_get_student'

））

dropprocproc_get_student

go

createprocproc_get_student

select*fromstudent;

--调用、执行存储过程

execproc_get_student;

3、修改存储过程

--修改存储过程

alterprocproc_get_student

select*fromstudent;

4、带参存储过程

--带参存储过程

if（object_id（'

proc_find_stu'

）isnotnull）

dropprocproc_find_stu

createprocproc_find_stu（@startIdint,@endIdint）

select*fromstudentwhereidbetween@startIdand@endId

execproc_find_stu2,4;

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

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

proc_findStudentByName'

dropprocproc_findStudentByName

createprocproc_findStudentByName（@namevarchar（20）='

%j%'

@nextNamevarchar（20）='

%'

）

select*fromstudentwherenamelike@nameandnamelike@nextName;

execproc_findStudentByName;

execproc_findStudentByName'

%o%'

t%'

6、带输出参数存储过程

proc_getStudentRecord'

dropprocproc_getStudentRecord

createprocproc_getStudentRecord（

@idint,--默认输入参数

@namevarchar（20）out,--输出参数

@agevarchar（20）output--输入输出参数

select@name=name,@age=agefromstudentwhereid=@idandsex=@age;

--

declare@idint,

@namevarchar（20）,

@tempvarchar（20）;

set@id=7;

set@temp=1;

execproc_getStudentRecord@id,@nameout,@tempoutput;

select@name,@temp;

print@name+'

#'

+@temp;

7、不缓存存储过程

--WITHRECOMPILE不缓存

proc_temp'

dropprocproc_temp

createprocproc_temp

withrecompile

execproc_temp;

8、加密存储过程

--加密WITHENCRYPTION

proc_temp_encryption'

dropprocproc_temp_encryption

createprocproc_temp_encryption

withencryption

execproc_temp_encryption;

9、带游标参数存储过程

proc_cursor'

dropprocproc_cursor

createprocproc_cursor

@curcursorvaryingoutput

set@cur=cursorforward_onlystaticfor

selectid,name,agefromstudent;

open@cur;

--调用

declare@exec_curcursor;

@ageint;

execproc_cursor@cur=@exec_curoutput;

--调用存储过程

fetchnextfrom@exec_curinto@id,@name,@age;

while（@@fetch_status=0）

begin

fetchnextfrom@exec_curinto@id,@name,@age;

print'

id:

'

+convert（varchar,@id）+'

name:

+@name+'

age:

+convert（char,@age）;

end

close@exec_cur;

deallocate@exec_cur;

--删除游标

10、分页存储过程

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

pro_page'

createprocpro_page

@startIndexint,

@endIndexint

selectcount（*）fromproduct

select*from（

selectrow_number（）over（orderbypid）asrowId,*fromproduct

）temp

wheretemp.rowIdbetween@startIndexand@endIndex

--dropprocpro_page

execpro_page1,4

--

--分页存储过程

dropprocpro_stu

createprocedurepro_stu（

@pageIndexint,

@pageSizeint

declare@startRowint,@endRowint

set@startRow=（@pageIndex-1）*@pageSize+1

set@endRow=@startRow+@pageSize-1

select*,row_number（）over（orderbyidasc）asnumberfromstudent

）t

wheret.numberbetween@startRowand@endRow;

execpro_stu2,2;

Raiserror

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

语法如下：

Raiserror（{msg_id|msg_str|@local_variable}

{,severity,state}

[,argument[,…n]]

[withoption[,…n]]

#msg_id:

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

#msg_str:

用户定义的信息，信息最大长度在2047个字符。

#severity：

用户定义与该消息关联的严重级别。

当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。

任何用户可以指定0-18直接的严重级别。

只有sysadmin固定服务器角色常用或具有altertrace权限的用户才能指定19-25直接的严重级别。

19-25之间的安全级别需要使用withlog选项。

#state：

介于1至127直接的任何整数。

State默认值是1。

raiserror（'

iserror'

16,1）;

select*fromsys.messages;

--使用sysmessages中定义的消息

raiserror（33003,16,1）;

raiserror（33006,16,1）;