SQL Server存储过程的用法和实例
首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数
据库中的,还可以根据条件执行不同SQL语句, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完
成命令。
请大家来了解一下存储过程的语法。
以下是代码片段:
Create PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
一、参数简介
procedure_name
新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。
要创建局部临时过程,可以在 procedure_name 前面加一个编号符 (#procedure_name),要创建全局临时过程,可以在 procedure_name 前面加
两个编号符 (##procedure_name)。完整的名称(包括 # 或 ##)不能超过 128 个字符。指定过程所有者的名称是可选的。
;number
是可选的整数,用来对同名的过程分组,以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的
过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标
识符中,只应在 procedure_name 前后使用适当的定界符。
@parameter
过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的
默认值)。存储过程最多可以有 2100 个参数。
使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它
过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。
data_type
参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。
如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。
说明:对于可以是cursor 数据类型的输出参数,没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。
default
参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字,那
么默认值中可以包含通配符(%、_、[] 和 [^])。
OUTPUT
表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作
OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。
n
表示最多可以指定 2100 个参数的占位符。
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不会缓存该过程的计划,该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行
计划时,请使用 RECOMPILE 选项。
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 Create PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL
Server 复制的一部分发布。
说明:在升级过程中,SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选,且只能在复制过程中
执行。本选项不能和 WITH RECOMPILE 选项一起使用。
AS
指定过程要执行的操作。
sql_statement
过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
n
是表示此过程可以包含多条 Transact-SQL 语句的占位符。
注释
/*和*/之间的为注释,可以包含一行和多行的说明文字。
其他说明
存储过程的最大大小为 128 MB。
二、存储过程的优点都有哪些呢?
1.存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存
储过程可提高数据库执行速度。
2.经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行
Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率
,减少程序的执行时间,这一点在较大数据量的数据库的操作中是非常重要的。在代码上看,SQL语句和程序代码语句的分离,可以提高程序代码的可
读性。
3.存储过程可以设置参数,可以根据传入参数的不同重复使用同一个存储过程,从而高效的提高代码的优化率和可读性。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类:
(1)系统存储过程:以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
(2)扩展存储过程 以XP_开头,用来调用操作系统提供的功能exec master..xp_cmdshell 'ping 10.8.16.1'
(3)用户自定义的存储过程,这是我们所指的存储过程常用格式
模版:Create procedure procedue_name [@parameter data_type][output][with]{recompile|encryption} as sql_statement
解释:output:表示此参数是可传回的
with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。
三、实例讲解
实例1:只返回单一记录集的存储过程。
要求1:查询表bankMoney的内容的存储过程
以下是代码片段:
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注* 在使用过程中只需要把中的SQL语句替换为存储过程名,就可以了很方便吧!
实例2(向存储过程中传递参数):
加入一笔记录到表bankMoney,并查询此表中userID= Zhangsan的所有存款的总金额。
以下是代码片段:
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as
insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
go
在SQL Server查询分析器中执行该存储过程的方法是:
以下是代码片段:
declare @total_price int
exec insert_bank '004','Zhangsan','男',100,@total_price output
print '总余额为'+convert(varchar,@total_price)
go
在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。
实例3:使用带有复杂 Select 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。
以下是代码片段:
USE pubs
IF EXISTS (Select name FROM sysobjects
Where name = 'au_info_all' AND type = 'P')
Drop PROCEDURE au_info_all
GO
Create PROCEDURE au_info_all
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all 存储过程可以通过以下方法执行:
以下是代码片段:
EXECUTE au_info_all
-- or
EXEC au_info_all
如果该过程是批处理中的第一条语句,则可使用:
以下是代码片段:
au_info_all
实例4:使用带有参数的简单过程
以下是代码片段:
Create PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存储过程可以通过以下方法执行:
以下是代码片段:
EXECUTE au_info 'Dull', 'Ann'
-- or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- or
EXEC au_info 'Dull', 'Ann'
-- or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
如果该过程是批处理中的第一条语句,则可使用:
以下是代码片段:
au_info 'Dull', 'Ann'
-- or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
au_info @firstname = 'Ann', @lastname = 'Dull'
实例5:使用带有通配符参数的简单过程
以下是代码片段:
Create PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:
以下是代码片段:
EXECUTE au_info2
-- or
EXECUTE au_info2 'Wh%'
-- or
EXECUTE au_info2 @firstname = 'A%'
-- or
EXECUTE au_info2 '[CK]ars[OE]n'
-- or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- or
EXECUTE au_info2 'H%', 'S%'
分享到:
相关推荐
内容概要:简单的C# winform调用存储过程实例,创建存储过程入参,通过SqlConnection对象和SqlCommand对象调用存储过程,获取存储过程的出参并显示出来,详细代码注释,希望对用到C#调用存储过程的小伙伴有帮助 ...
资源名称:SQL_Server存储过程调试指南内容简介: 存储过程( Stored Procedure)是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来...
Informatica调用存储过程图文流程 Informatica 是一款功能强大的数据集成工具,能够帮助用户快速、可靠地集成各种数据源。调用存储过程是 Informatica 中的一种常用功能,下面将详细介绍 Informatica 调用存储过程...
针对 SQL Server 2008 存储过程通过 With Encryption 加密方式的解密 SQL Server 2008 存储过程中使用 With Encryption 加密方式可以对存储过程进行加密保护,以防止未经授权的访问和修改。但是,在某些情况下,...
在了解这个问题之前,我们通常认为存储过程具有以下优点:首先,存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的 SQL 语句每执行一次就编译一次,所以使用存储过程可以...
sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程
SqlServer存储过程的导出导入 近日在研究SQL的存储过程,这里我陆续将我在使用存储过程中碰到的问题及解决办法发布到网上,请各位大虾指正。 SqlServer存储过程的导出导入,网上相关资料特别少,经过摸索,这里写...
1、资料包含了作者编写的实际应用场景中使用的存储过程代码,共计118个案例,有完整注释说明。旨在帮助读者通过案例学习,快速掌握存储过程的应用方法和技巧。 2、博主博客:...
创建一个名为stu_pr的存储过程,该存储过程能查询出051班学生的所有资料,包括学生的基本信息、学生的选课信息(含未选课同学的信息)。要求在创建存储过程前请判断该存储过程是否已创建,若已创建则先删除,并给出...
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
birt报表中调用存储过程的方法. 目录 1. 概述 3 2. BIRT支持的存储过程返回值类型 3 3. BIRT调用存储过程的语法 4 4. 创建存储过程数据源/集 4 5. 在BIRT中使用存储过程 5 调用返回单结果集的存储过程。 5 调用...
"实验八 存储过程的使用(学生)" 本实验的目的是让学生掌握创建存储过程的基本方法,并学会使用 Transact-SQL 编写存储过程的方法。实验中,学生需要创建多个存储过程来完成不同的数据库操作,例如检索学生基本...
阅读实验教材《SQL Server 2012数据库管理与开发》第137页到155页的内容,掌握存储过程的概念、了解存储过程的类别(系统存储过程,用户自定义存储过程,扩展存储过程);掌握存储过程的建立;掌握存储过程的两种...
存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...
创建存储过程1 课程目标 1)了解 ——存储过程的优点; 2)理解 —— 存储过程的概念; 3)掌握 —— 存储过程的创建方法; 存储过程优点 使用存储过程的优点有: (1)存储过程在服务器端运行,执行速度快。 (2)...
可以将SQL Server存储过程转为oracle存储过程的工具
db2 存储过程 db2 存储过程 db2 存储过程
oracle 存储过程实例 oracle存储过程实例
SAP HANA 中调试存储过程 SAP HANA 中调试存储过程 SAP HANA 中调试存储过程