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数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

    常用系统存储过程有：

<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;

    系统存储过程示例：

--表重命名
<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、 创建语法

<span>create</span> <span>proc</span> | <span>procedure</span> pro_name
    [{@参数数据类型} [=默认值] [<span>output</span>],
     {@参数数据类型} [=默认值] [<span>output</span>],
     ....
    ]
<span>as</span>
    SQL_statements

 

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

--创建存储过程
<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、 修改存储过程

--修改存储过程
<span>alter</span> <span>proc</span> proc_get_student
<span>as</span>
<span>select</span> * <span>from</span> student;

   4、 带参存储过程

--带参存储过程
<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、 带通配符参数存储过程

--带通配符参数存储过程
<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、 带输出参数存储过程

<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、 不缓存存储过程

--<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、 加密存储过程

--加密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、 带游标参数存储过程

<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、 分页存储过程

---存储过程、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返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。

   语法如下：

<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。

<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);

<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>