当前位置:Gxlcms > 数据库问题 > 14、SQL Server 存储过程

14、SQL Server 存储过程

时间:2021-07-01 10:21:17 帮助过:4人阅读

存储过程类似函数,可以重复使用。相对于函数,存储过程拥有更强大的功能和更高的灵活性。

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

存储过程带来的好处:

1、性能的提升

    存储过程执行时,第一次会进行编译和优化。但批处理T-SQL语句每次执行都需要预编译和优化,所以没有存储过程快。

2、易于维护

    存储过程创建后存储在数据库中,可以被程序多次调用执行。当需要修改存储过程时,对应用程序代码毫无影响。

3、安全性

    应用程序只需要调用存储过程名,给几个参数,而不是直接访问基础对象。需要赋予的不是增删改的权限,而是exec的权限。

系统存储过程

系统存储过程主要存储在master数据库中,以sp_开头,可以在所有数据库对象中使用。

常用的系统存储过程

  1. <span style="color: #0000ff;">exec</span> sp_databases <span style="color: #008080;">--</span><span style="color: #008080;">查看所有数据库</span>
  2. <span style="color: #0000ff;">exec</span> sp_tables <span style="color: #008080;">--</span><span style="color: #008080;">查看所有数据表</span>
  3. <span style="color: #0000ff;">exec</span> sp_columns student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的所有列</span>
  4. <span style="color: #0000ff;">exec</span> sp_helpIndex student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的索引</span>
  5. <span style="color: #0000ff;">exec</span> sp_helpconstraint student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的约束</span>
  6. <span style="color: #0000ff;">exec</span> sp_helptext <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sp_databases</span><span style="color: #ff0000;">‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">查看定于语句</span>
  7. <span style="color: #0000ff;">exec</span> sp_rename oldName,newName <span style="color: #008080;">--</span><span style="color: #008080;">修改表、索引、列的名称</span>
  8. <span style="color: #0000ff;">exec</span> sp_renamedb webDB,newDB <span style="color: #008080;">--</span><span style="color: #008080;">修改数据库名称</span>
  9. <span style="color: #0000ff;">exec</span> sp_helpdb webDB <span style="color: #008080;">--</span><span style="color: #008080;">查看数据库信息</span>

用户定义存储过程

语法:

  1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">procedure</span><span style="color: #000000;"> proc_name
  2. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">
  3. {@parameter1 data_type} [=default</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">out | output</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  4. {</span><span style="color: #008000;">@parameter2</span> data_type} <span style="color: #ff0000;">[</span><span style="color: #ff0000;">=default</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">out | output</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  5. ]
  6. </span><span style="color: #0000ff;">as</span>
  7. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">begin</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  8. T</span><span style="color: #808080;">-</span><span style="color: #000000;">SQL代码
  9. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">end</span><span style="color: #ff0000;">]</span>

不带参数

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">创建create 修改alter</span>
  5. <span style="color: #0000ff;">as</span>
  6. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> id <span style="color: #0000ff;">desc</span>
  7. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  8. <span style="color: #0000ff;">exec</span> proc_test

技术分享

执行存储过程使用execute关键字,可以简写为exec。在SQL Server 2012中得到加强,可以修改结果集中列名和类型。

  1. <span style="color: #0000ff;">execute</span><span style="color: #000000;"> proc_test
  2. </span><span style="color: #0000ff;">with</span><span style="color: #000000;"> result sets
  3. (
  4. (
  5. 序号 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
  6. 姓名 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">),
  7. 性别 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
  8. 年龄 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
  9. 邮箱 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">)
  10. )
  11. )</span>

技术分享

输入参数

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> proc_test (<span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">)
  5. </span><span style="color: #0000ff;">as</span>
  6. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #008000;">@id</span>
  7. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  8. <span style="color: #0000ff;">exec</span> proc_test <span style="color: #800000; font-weight: bold;">10</span>

默认参数

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> proc_test (<span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">)
  5. </span><span style="color: #0000ff;">as</span>
  6. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #008000;">@id</span>
  7. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  8. <span style="color: #0000ff;">exec</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">10</span>
  9. <span style="color: #0000ff;">exec</span> proc_test <span style="color: #800000; font-weight: bold;">15</span> <span style="color: #008080;">--</span><span style="color: #008080;">15</span>

输出参数

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test (
  5. </span><span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span>, <span style="color: #008080;">--</span><span style="color: #008080;">输入参数</span>
  6. <span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>) out, <span style="color: #008080;">--</span><span style="color: #008080;">输出参数</span>
  7. <span style="color: #008000;">@age</span> <span style="color: #0000ff;">int</span> output <span style="color: #008080;">--</span><span style="color: #008080;">输入输出参数</span>
  8. <span style="color: #000000;">)
  9. </span><span style="color: #0000ff;">as</span>
  10. <span style="color: #0000ff;">begin</span> <span style="color: #008080;">--</span><span style="color: #008080;">可写可不写</span>
  11. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@name</span> <span style="color: #808080;">=</span> name,<span style="color: #008000;">@age</span> <span style="color: #808080;">=</span> age <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #008000;">@id</span>
  12. <span style="color: #0000ff;">end</span>
  13. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  14. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #008000;">@age</span> <span style="color: #0000ff;">int</span>
  15. <span style="color: #0000ff;">exec</span> proc_test <span style="color: #800000; font-weight: bold;">10</span>,<span style="color: #008000;">@name</span> out,<span style="color: #008000;">@age</span><span style="color: #000000;"> output
  16. </span><span style="color: #0000ff;">select</span> <span style="color: #008000;">@name</span>,<span style="color: #008000;">@age</span>

不缓存

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test
  5. </span><span style="color: #0000ff;">with</span> recompile <span style="color: #008080;">--</span><span style="color: #008080;">不缓存,每次都编译</span>
  6. <span style="color: #0000ff;">as</span>
  7. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> id <span style="color: #0000ff;">desc</span>
  8. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  9. <span style="color: #0000ff;">exec</span> proc_test

加密

  1. <span style="color: #0000ff;">if</span>(<span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  2. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">删除</span>
  3. <span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test
  5. </span><span style="color: #0000ff;">with</span> encryption <span style="color: #008080;">--</span><span style="color: #008080;">加密后无法查看</span>
  6. <span style="color: #0000ff;">as</span>
  7. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> id <span style="color: #0000ff;">desc</span>
  8. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  9. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> proc_test
  10. </span><span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_helptext proc_test
  11. </span><span style="color: #008080;">--</span><span style="color: #008080;">提示对象 ‘proc_test‘ 的文本已加密。</span>

 

14、SQL Server 存储过程

标签:

人气教程排行