T-SQL的存储过程

1.简介

　　　　存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。而且这个代码块是编译过了放在数据库端的，后台的代码比如php CI框架可以直接调用它：

<?php
    $sql="exec prec_xxx";
    $query = $this->db->query($sql);
?>

　　　　由于数据库是先编译后执行的，所以存储过程是一个编译过的代码块的话，执行效率要比在程序里用T-SQL语句高。

　　　　当然存储过程还有其他优点比如上面的例子只传送了13个字节，相比于打断的T-sql语句要节省带宽。

2.分类

　　　存储过程包括系统存储过程和用户自定义的存储过程。

　　系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。例如下列表单中就包含一些系统存储过程，可以使用exec执行：

1. 查看数据库的版本 
select @@version 

2. 查看数据库所在机器操作系统参数 
exec master..xp_msver 

3. 查看数据库启动的参数 
sp_configure 

4. 查看数据库启动时间 
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 

查看数据库服务器名和实例名 
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME) 
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME) 
5. 查看所有数据库名称及大小 
sp_helpdb 

重命名数据库用的SQL 
sp_renamedb 'old_dbname', 'new_dbname' 

6. 查看所有数据库用户登录信息 
sp_helplogins 

查看所有数据库用户所属的角色信息 
sp_helpsrvrolemember 

修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 

更改某个数据对象的用户属主 
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner' 

注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 

把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 

7. 查看链接服务器 
sp_helplinkedsrvlogin 

查看远端数据库用户登录信息 
sp_helpremotelogin

8.查看某数据库下某个数据对象的大小 
sp_spaceused @objname 

还可以用sp_toptables过程看最大的N(默认为50)个表 

查看某数据库下某个数据对象的索引信息 
sp_helpindex @objname 

还可以用SP_NChelpindex过程查看更详细的索引情况 
SP_NChelpindex @objname 

clustered索引是把记录按物理顺序排列的，索引占的空间比较少。 
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。 
查看某数据库下某个数据对象的的约束信息 
sp_helpconstraint @objname 

9.查看数据库里所有的存储过程和函数 
use @database_name 
sp_stored_procedures 
查看存储过程和函数的源代码 
sp_helptext '@procedure_name' 

查看包含某个字符串@str的数据对象名称 
select distinct object_name(id) from syscomments where text like '%@str%' 

创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数 

解密加密过的存储过程和函数可以用sp_decrypt过程

10.查看数据库里用户和进程的信息 
sp_who 
查看SQL Server数据库里的活动用户和进程的信息 
sp_who 'active' 
查看SQL Server数据库里的锁的情况 
sp_lock 

　　　　用户自定义存储过程，创建语法：

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

3.举例

　　下例为一个T-SQL存储过程（例如是prec_xxxx)，用于将一系列表中的一些字段提取出来，返回给php程序。

as
BEGIN
declare @rluid uniqueidentifier 
--创建临时表
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
   DROP TABLE #tab
select convert(varchar(50),rl_uid) rl_uid ,rl_name, convert(varchar(300),'') menunames,
convert(varchar(2000),'') usernames,isnull(ve.emp_name,cve.NAME)emp_name,convert(varchar(10),rl_updatedate,120) rl_updatedate 
into #tab
from t_role tr 
left join v_employee ve on ve.emp_ssn = tr.rl_createuser
left join ttm.dbo.chrm_view_employee cve on cve.EMPLOYEE_ID = tr.rl_createuser 
--创建游标
declare rolelist cursor 
for
select rl_uid 
from t_role order by rl_updatedate desc
open rolelist 
    fetch next from rolelist into @rluid 
        while(@@fetch_status=0)
        begin
                --角色对应的菜单
                declare @menunames varchar(300)
                declare @muname varchar(50)
                set @menunames = ''
                declare menulist cursor 
                for
                select tu.mu_name from t_role_menu tm
                inner join t_menu tu on tm.mu_uid = tu.mu_uid
                where tm.rl_uid = @rluid
                open menulist 
                    declare @i int
                    set @i = 0
                    fetch next from menulist into @muname 
                        while(@@fetch_status=0)
                        begin
                            if @i=0
                            begin
                                set @menunames = @menunames+@muname
                            end
                            else
                            begin
                                set @menunames = @menunames+' , '+@muname
                            end
                        set @i = @i +  1 
                        fetch next from menulist into @muname 
                        end
                close menulist
                deallocate menulist
                update #tab set menunames = @menunames where rl_uid = @rluid
                --角色对应的用户
                declare @usernames varchar(2000)
                declare @username varchar(50)
                set @usernames = ''
                declare userlist cursor 
                for
                select isnull(ve.emp_name,cve.name)emp_name  from t_role_user tu
                left join v_employee ve on tu.emp_ssn = ve.emp_ssn
                left join ttm.dbo.chrm_view_employee cve on cve.EMPLOYEE_ID = tu.emp_ssn
                where rl_uid =  @rluid
                open userlist 
                    declare @j int
                    set @j = 0
                    fetch next from userlist into @username 
                        while(@@fetch_status=0)
                        begin
                            if @j=0
                            begin
                                set @usernames = @usernames+@username
                            end
                            else
                            begin
                                set @usernames = @usernames+' , '+@username
                            end
                        set @j = @j +  1 
                        fetch next from userlist into @username 
                        end
                close userlist
                deallocate userlist
                update #tab set usernames = @usernames where rl_uid = @rluid
        fetch next from rolelist into @rluid
        end
close rolelist
deallocate rolelist    
select rl_uid,rl_name,menunames,convert(text,usernames)usernames,emp_name,rl_updatedate from #tab
END

 

 上面的代码看完一定会对SQL之所以被称为语言表示强烈认可，各种变量呀，循环呀，很像其他编程语言的feel。当然有很多地方可能不够简洁，有人说是它是面向集合的语言。。。有意思。