时间:2021-07-01 10:21:17 帮助过:4人阅读
存储过程类似函数,可以重复使用。相对于函数,存储过程拥有更强大的功能和更高的灵活性。
存储过程中可以包含逻辑控制语句和数据操作语句,可以接受参数,输出参数,返回单个值或多个结果集。
存储过程带来的好处:
1、性能的提升
存储过程执行时,第一次会进行编译和优化。但批处理T-SQL语句每次执行都需要预编译和优化,所以没有存储过程快。
2、易于维护
存储过程创建后存储在数据库中,可以被程序多次调用执行。当需要修改存储过程时,对应用程序代码毫无影响。
3、安全性
应用程序只需要调用存储过程名,给几个参数,而不是直接访问基础对象。需要赋予的不是增删改的权限,而是exec的权限。
系统存储过程
系统存储过程主要存储在master数据库中,以sp_开头,可以在所有数据库对象中使用。
常用的系统存储过程
- <span style="color: #0000ff;">exec</span> sp_databases <span style="color: #008080;">--</span><span style="color: #008080;">查看所有数据库</span>
- <span style="color: #0000ff;">exec</span> sp_tables <span style="color: #008080;">--</span><span style="color: #008080;">查看所有数据表</span>
- <span style="color: #0000ff;">exec</span> sp_columns student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的所有列</span>
- <span style="color: #0000ff;">exec</span> sp_helpIndex student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的索引</span>
- <span style="color: #0000ff;">exec</span> sp_helpconstraint student <span style="color: #008080;">--</span><span style="color: #008080;">查看student表的约束</span>
- <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>
- <span style="color: #0000ff;">exec</span> sp_rename oldName,newName <span style="color: #008080;">--</span><span style="color: #008080;">修改表、索引、列的名称</span>
- <span style="color: #0000ff;">exec</span> sp_renamedb webDB,newDB <span style="color: #008080;">--</span><span style="color: #008080;">修改数据库名称</span>
- <span style="color: #0000ff;">exec</span> sp_helpdb webDB <span style="color: #008080;">--</span><span style="color: #008080;">查看数据库信息</span>
用户定义存储过程
语法:
- <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
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">
- {@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;">,
- {</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;">
- ]
- </span><span style="color: #0000ff;">as</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">begin</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
- T</span><span style="color: #808080;">-</span><span style="color: #000000;">SQL代码
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">end</span><span style="color: #ff0000;">]</span>
不带参数
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <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>
- <span style="color: #0000ff;">as</span>
- <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>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <span style="color: #0000ff;">exec</span> proc_test
执行存储过程使用execute关键字,可以简写为exec。在SQL Server 2012中得到加强,可以修改结果集中列名和类型。
- <span style="color: #0000ff;">execute</span><span style="color: #000000;"> proc_test
- </span><span style="color: #0000ff;">with</span><span style="color: #000000;"> result sets
- (
- (
- 序号 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
- 姓名 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">),
- 性别 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
- 年龄 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
- 邮箱 </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">)
- )
- )</span>
输入参数
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <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;">)
- </span><span style="color: #0000ff;">as</span>
- <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>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <span style="color: #0000ff;">exec</span> proc_test <span style="color: #800000; font-weight: bold;">10</span>
默认参数
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <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;">)
- </span><span style="color: #0000ff;">as</span>
- <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>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <span style="color: #0000ff;">exec</span> proc_test <span style="color: #008080;">--</span><span style="color: #008080;">10</span>
- <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>
输出参数
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test (
- </span><span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span>, <span style="color: #008080;">--</span><span style="color: #008080;">输入参数</span>
- <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>
- <span style="color: #008000;">@age</span> <span style="color: #0000ff;">int</span> output <span style="color: #008080;">--</span><span style="color: #008080;">输入输出参数</span>
- <span style="color: #000000;">)
- </span><span style="color: #0000ff;">as</span>
- <span style="color: #0000ff;">begin</span> <span style="color: #008080;">--</span><span style="color: #008080;">可写可不写</span>
- <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>
- <span style="color: #0000ff;">end</span>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <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>
- <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
- </span><span style="color: #0000ff;">select</span> <span style="color: #008000;">@name</span>,<span style="color: #008000;">@age</span>
不缓存
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test
- </span><span style="color: #0000ff;">with</span> recompile <span style="color: #008080;">--</span><span style="color: #008080;">不缓存,每次都编译</span>
- <span style="color: #0000ff;">as</span>
- <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>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <span style="color: #0000ff;">exec</span> proc_test
加密
- <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;">))
- </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>
- <span style="color: #0000ff;">go</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_test
- </span><span style="color: #0000ff;">with</span> encryption <span style="color: #008080;">--</span><span style="color: #008080;">加密后无法查看</span>
- <span style="color: #0000ff;">as</span>
- <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>
- <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
- <span style="color: #0000ff;">exec</span><span style="color: #000000;"> proc_test
- </span><span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_helptext proc_test
- </span><span style="color: #008080;">--</span><span style="color: #008080;">提示对象 ‘proc_test‘ 的文本已加密。</span>
14、SQL Server 存储过程
标签: