sql server 2008学习之存储过程

存储过程
定义：存储过程是一组为了完成特定功能的SQL语句集合，它经编译后存储在数据库中，用户通过制定存储过程的名称并给出相应的参数就可以对其进行执行
SQLSERVER 2008主要包括用户自定义存储过程，扩展存储过程和系统存储过程
接受输入参数并以输出参数的格式向调用过程或者批处理返回多个值
包含用于在数据库中执行操作(包括调用其他存储过程) 的编程语句
向调用过程或者批处理返回状态值，以知明成功或者失败

用户自定义存储过程
用户自定义存储过程是指封装了可重用代码的模块或者例程。存储过程可以接受输入参数，向客户端返回表格或者标量结果和消息，调用数据定义语言DDL和数据操作语言DML语句，然后返回输出参数。2008中用户自定义的存储过程有两种类型：Transact-SQL或CLR

Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以几首和返回用户提供的参数
CLR存储过程是指对Microsoft.NET Frabnmework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。

扩展存储过程允许使用编程语言创建自己的外部例程。扩展存储过程是指08的实例可以动态加载和运行DLL。在sql08实例的地址空间中运行，可以使用SQL SERVER 扩展存储过程API完成编程

系统存储过程
SQL SERVER 2008许多管理活动都通过一种特殊的存储过程执行，这种存储过程称为系统存储过程。从物理意义上来说，系统存储过程存储在源数据库中，并且都带有sq_前缀。从逻辑意义上来讲，系统存储过程出现在每个系统自动以数据库和用户自定义数据库的sys架构中
SQL SERVER 08中系统存储过程放在master数据库中，但是仍可以在其他数据库中对其进行调用，而且在调用时不必在存储过程名钱加上数据库名。
1.sp_who存储过程
用于查看当前用户，会话和进程的信息。可以筛选信息以便只返回那些属于特定用户或者特定会话的非空闲进程。具体语法格式如下：
sp_who [ [@loginame = ] ‘login‘ | session ID | ‘ACTIVE‘]
其中login用于标识属于特定登录名的进程，session ID是属于sql server实例的会话标识号，ACTIVE排除正在等待用户发出下一个命令的会话
下面是我查看的信息
use web
go
EXEC sp_who
go

select count(title)
FROM
       dbo.web

2.sp_helpdb存储过程
sp_helpdb 存储过程用于报告有关指定数据库或所有数据库的信息，具体语法格式如下

sp_helpdb [ [@dbname= ] ‘name‘]
当然上面的语句是刚才执行的最后一个就是啦
use web
go
EXEC sp_who
go

select count(title)
FROM
       dbo.web
EXEC sp_helpdb web
sp_monitor存储过程
用于显示有关Microsoft SQL Server的统计信息，语法格式如下
sp_monitor
这里我的权限太小了，报错信息如下
EXEC sp_monitor

[Err] 42000 - [SQL Server]拒绝了对对象 ‘sp_monitor‘ (数据库 ‘mssqlsystemresource‘，架构 ‘sys‘)的 EXECUTE 权限。

执行该操作必须有sysadmin固定服务器角色的成员身份

创建存储过程
可以使用 CREATE PROCEDURE语句来创建存储过程。必须有CREATE PROCEDURE权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。在创建存储过程时，应该指定所有的输入参数，执行数据库操作的编程语句，返回至调用过程或者批处理时以示成或是失败的状态值，俘获和处理潜在错误时错误处理语句

存储过程创建规则
存储过程创建规则如下
(1) CREATE PROCEDURE定义自身包括任意数量和类型的SQL语句，但下表中除外，不能在存储过程任何位置使用这些语句

CREATE AGGREGATE                CREATE RULE
CREATE DEFAULT                    CREATE SCHEMA
CREATE 或者ALTER FUNCTION        CREATE 或者 ALTER TRIGGER
CREATE 或者ALTER PROCEDURE         CREATE 或者 ALTER VIEW
SET PARSEONLY                    SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT                SET SHOWPLAN_XML
USE database_name

(2)可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可
(3)可以在存储过程内引用临时表
(4)如果在存储过程内创建内地临时表，则临时表仅为该存储过程而存在
(5)如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象
(6)如果执行远程Microsoft SQL Server 2008 实例更改远程存储过程，则不能回滚更改
(7)存储过程中参数的最大数目为 2100
(8)存储过程中的局部变量最大数目仅受可用内存的限制
(9)根据可用内存的不同，存储过程最大可达128MB

创建简单存储过程
语法如下
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 用于指定存储过程的名称
number         用于指定对同名的过程分组
@parameter    用于指定存储过程中的参数
data_type    用于指定参数 的数据类型
VARYING     指定作为输出参数支持的结果集，仅适用于游标参数
OUTPUT        指示参数是输出参数
RECOMPILE    指示数据库引擎不缓存该过程的计划，该过程在运行时编译
ENCRYPTION 指示SQL Server加密syscomments标识包含CREATE PROCEDURE语句文本的 条目
FOR REPLICATION 指定不能在订阅服务器上执行为复制的存储过程
sql_statement 要包含的过程中的一个或者多个Transact-SQL语句

下面创建一个简单的存储过程
use web
GO
CREATE PROCEDURE Pro_基本信息
AS
SELECT 学号,姓名,出生日期,所属班级
FROM 学生信息
WHERE 性别=‘女‘
EXEC Pro_基本信息

-- ----------------------------
-- Table structure for 学生信息
-- ----------------------------
create TABLE [dbo].[学生信息]
GO
CREATE TABLE [dbo].[学生信息] (
[学号] nchar(10) NULL ,
[姓名] nvarchar(50) NULL ,
[出生日期] date NULL ,
[所属班级] nchar(10) NULL ,
[性别] nchar(10) NULL
)
GO
-- ----------------------------
-- Records of 学生信息
-- ----------------------------
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘1         ‘, N‘张三‘, N‘2014-04-17‘, N‘一年三班      ‘, N‘男         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘5         ‘, N‘天八‘, N‘2014-04-17‘, N‘一年三班      ‘, N‘女         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘3         ‘, N‘王五‘, N‘2014-04-13‘, N‘一年三班      ‘, N‘女         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘7         ‘, N‘狗仔‘, N‘2014-04-12‘, N‘一年三班      ‘, N‘男         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘2         ‘, N‘李四‘, N‘2014-04-17‘, N‘一年三班      ‘, N‘女         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘6         ‘, N‘王小明‘, N‘2014-04-13‘, N‘一年三班      ‘, N‘男         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘4         ‘, N‘陈东‘, N‘2014-04-12‘, N‘一年三班      ‘, N‘男         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘12        ‘, N‘陈哥‘, N‘2014-04-17‘, N‘一年三班      ‘, N‘女         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘13        ‘, N‘王兄‘, N‘2014-04-13‘, N‘一年三班      ‘, N‘男         ‘)
GO
GO
INSERT INTO [dbo].[学生信息] ([学号], [姓名], [出生日期], [所属班级], [性别]) VALUES (N‘15        ‘, N‘吕雉‘, N‘2014-04-12‘, N‘一年三班      ‘, N‘中性        ‘)
GO
GO

sql server 2008学习之存储过程,古老的榕树,5-wow.com