sql server 存储过程

时间:2021-06-03 00:40:44

---恢复内容开始---

1、常用的系统存储过程

1 exec sp_datebases --列出当前系统中的数据库
1 exec sp_renamedb 'studb','studb1'   --修改数据库名称
use stuDB
exec sp_tables --返回当前环境下可查询的对象的列表
exec columns stuInfo --返回表stuInfo 列的信息
exec sp_help stuInfo --返回表stuInfo 的所有信息
1 exec sp_helpconstraint stuInfo --查看表stuInfo的约束
exec sp_helpindex stuInfo 列出表索引
EXEC sp_helptext 'view_stuInfo_stuMarks'
--查看显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本
exec sp_stored_procedures  --查看当前数据库的存储过程
--创建数据库bankDB,要求保存在D:\bank

USE master
GO
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
IF EXISTS(SELECT * FROM sysdatabases
                            WHERE name='bankDB')
   DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
 (
  …
)
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件

2.--.使用系统存储过程.查看用户表的约束、索引信息,并在D:\project下创建子文件夹test

1 use bbsDB
2 go
3 --启用xp_cmdshell命令
4 exec sp_configure 'show advanced options',1;reconfigure;exec sp_configure 'xp_cmdshell',1'reconfigure;
1 exec sp_helpconstraint bbsuser
exec sp_helpindex bbsuser

3.

--2、创建带参存储过程.请编写存储过程proc_find1,实现查找某个用户(假定为可卡因)的发贴情况,界面如下所示(上半部分为发表主贴的情况,下面为发表回贴的情况):

--2、创建带参存储过程.请编写存储过程proc_find1,实现查找某个用户(假定为可卡因)的发贴情况,界面如下所示(上半部分为发表主贴的情况,下面为发表回贴的情况):
drop proc proc_find1
create procedure proc_find1
    @Uname varchar(15)
    as
        print '该用户发帖情况:'
        select Ttime,TclickCount,Ttopic,Tcontents 
        from bbsTopic
        inner join bbsUsers
        on bbsUsers.UID = bbsTopic.TuID
        where Uname=@Uname

        print '-------------------'

        print '该用户回帖情况:'
        select Rtime,RclickCount,Rcontents
        from bbsReply
        inner join bbsUsers
        on bbsUsers.UID = bbsReply.RuID
        where Uname= @Uname
    go
    EXEC proc_find1 @Uname='可卡因'
--3、带输出参数的存储过程.编写存储过程proc_find2,查找某个用户的发贴情况,并返回发贴数和回贴数,界面如下所示(上半部分为发表主贴的情况,下面为发表回贴的情况):
drop proc proc_find2
create procedure proc_find2
    @num int output,
    @Uname varchar(15)
    as
        print '该用户发帖情况:'
        select Ttime,TclickCount,Ttopic,Tcontents 
        from bbsTopic
        inner join bbsUsers
        on bbsUsers.UID = bbsTopic.TuID
        where Uname=@Uname

        print '-------------------'

        print '该用户回帖情况:'
        select Rtime,RclickCount,Rcontents
        from bbsReply
        inner join bbsUsers
        on bbsUsers.UID = bbsReply.RuID
        where Uname= @Uname

        select @num= count(Ttopic)+count(Rcontents)
        from bbsTopic
        inner join bbsReply
        on bbsReply.RuID= bbsTopic.TuID
        where bbsReply.RuID=(select UID from bbsUsers where Uname=@Uname) and bbsTopic.TuID=(select UID from bbsUsers where Uname=@Uname)

    go
    --调用存储过程
    declare @sum int
    EXEC proc_find2 @sum output,'可卡因'

    print '小弟发帖和回帖较多,看来比较关心民众疾苦'
    print '总贴数:'+convert(varchar(5),@sum)
    go
--4.带默认参数的存储过程.要求创建存储过程proc_find3,查询某个用户在某个版块的发贴情况(主贴+回贴),并返回发贴主数和从贴数。如果调用者没指定具体的版块,则默认为所有版块,界面如下所示(上半部分为“.NET方向”发表主贴的情况,下面为“.NET方向”发表回贴的情况):
create procedure proc_find3
    @sum int output, --此参数只用于将信息从存储过程传输回应用程序。 
    @Uname varchar(15),
    @section varchar(32)='.NET'
    as
                print @Uname+'该用户'+@section+'版块发帖情况:'
                select Ttime,TclickCount,Ttopic,Tcontents 
                from bbsTopic
                inner join bbsUsers
                on bbsUsers.UID = bbsTopic.TuID
                where Uname=@Uname and bbsTopic.TsID=(select SID from bbsSection where Sname= @section)

                print @Uname+'该用户'+@section+'版块回帖情况:'
                select Rtime,RclickCount,Rcontents
                from bbsReply
                inner join bbsUsers
                on bbsUsers.UID = bbsReply.RuID
                where Uname=@Uname and bbsReply.RsID=(select SID from bbsSection where Sname= @section)

                select @sum= count(TuID)+count(RuID)
                from bbsTopic
                inner join bbsReply
                on bbsReply.RuID= bbsTopic.TuID
                where (bbsReply.RuID=(select UID from bbsUsers where Uname=@Uname)and bbsReply.RsID=(select SID from bbsSection where Sname=@section)) 
                    and (bbsTopic.TuID=(select UID from bbsUsers where Uname=@Uname)and bbsTopic.TsID =(select SID from bbsSection where Sname=@section))
    go
    --调用带参数默认值的存储过程
    declare @sum int
    EXEC proc_find3 @sum output,'可卡因'

    print '小弟发帖和回帖较多,看来比较关心民众疾苦'
    print '总贴数:'+str(@sum)
    go

    drop proc proc_find3

--5、带默认参数的存储过程.编写存储过程proc_findReply,查询某个主贴的所有回贴,并返回回贴的数量,界面如下所示:
create  proc proc_findReply
    @total int output ,
    @TID int=6,
    @RSID int=2
    as
        select RID, RtID, RsID, RuID, Rface, Rcontents, Rtime, RclickCount from bbsReply
        inner join bbsTopic on bbsTopic.TsID =bbsReply.RsID
        where @RSID=RSID and @TID=TID
        select @total=count(RsID) from bbsReply where @RSID=RSID
    go
    declare @totalsum int 
    exec proc_findReply @totalsum output
    print '总贴数为'+str(@totalsum)

    drop  proc proc_findReply 

 

---恢复内容结束---