存储过程

--创建不带参数的存储过程

--获取所有的学生成绩信息
if exists(select * from sysobjects where name='Proc_GetAllStuMark')
drop proc Proc_GetAllStuMark
go
create proc Proc_GetAllStuMark
as
    select a.StuName,b.Subject,b.Score 
    from StuInfo a,StuMarks b
    where a.StuID=b.StuID
go
exec Proc_GetAllStuMark
execute Proc_GetAllStuMark --使用execute 执行存储过程


--创建带有参数的存储过程

--根据学生姓名，获得学生成绩
if  exists(select * from sysobjects where name='Proc_GetStuMarkByStuName')
drop proc Proc_GetStuMarkByStuName
go
create proc Proc_GetStuMarkByStuName
@stuname varchar(20)
as
    select a.StuName,b.Subject,b.Score
    from StuInfo a,StuMarks b
    where a.StuID=b.StuID
    and a.StuName=@stuname
go
exec Proc_GetStuMarkByStuName '李四'

--创建参数有默认值的存储过程

--添加学生信息表的数据
if  exists(select * from sysobjects where name='Proc_InsertStuInfo')
drop proc Proc_InsertStuInfo
go
create proc Proc_InsertStuInfo
@stuname varchar(20),
@stusex char(2)='男',
@classid int = 2
as
    insert into StuInfo(StuName,StuSex,ClassID)
    values(@stuname,@stusex,@classid)
go
--调用参数有默认值的存储过程
exec Proc_InsertStuInfo '唐僧'
exec Proc_InsertStuInfo '猪八戒',@classid=1


-- 创建带输出参数的存储过程

--根据学生姓名查找学员java分数

if exists(select * from sysobjects where name='Proc_GetCMarkByStuName')
drop proc Proc_GetCMarkByStuName
go
create proc Proc_GetCMarkByStuName
@stuname varchar(20),
@cmark int output --output定义当前的参数为输出参数
as
    --打印初始值
    print '分数的初始值：'+convert(varchar,@cmark)

    select @cmark=b.Score from StuInfo a,StuMarks b
    where a.StuID=b.StuID
    and b.Subject='java' and a.StuName=@stuname
go

--调用该存储过程
declare @cmark int
set @cmark=100
exec Proc_GetCMarkByStuName '李四',@cmark output
print '李四的java分数为：'+convert(varchar,@cmark)

--列出服务器上的所有数据库
EXEC sp_databases
--更改数据库的名称
exec sp_renamedb 'lession05','lession5'
use Lession3
go
--返回当前环境下可查询的对象的列表
exec sp_tables
--返回某个表列的信息
exec sp_columns StuInfo
--查看某个表的所有信息
exec sp_help StuInfo
--查看某个表的约束
exec sp_helpconstraint StuInfo
--查看某个表的索引
exec sp_helpindex StuMarks
--显示视图实际文本
exec sp_helptext 'view_stuInfo_stuMarks'
--列出当前环境中的所有存储过程
exec sp_stored_procedures

--实现以零作除数错误

--自定义错误
--raiserror(错误消息，严重级别，状态)
if exists(select * from sysobjects where name='Proc_Devide')
drop proc Proc_Devide
go
create proc Proc_Devide
@a int,
@b int
as  
    declare @c int
    if(@b=0)
    begin
        raiserror('以零作除数错误',15,2)
        return
    end
    set @c=@a/@b
go
exec Proc_Devide 10,0
select @@error as 错误编号

--练习1
--使用xp_cmdshell创建Student数据库，StuInfo数据表
USE master
exec xp_cmdshell 'md d:\MyDB',no_output
go

create database Student
on(
    name='student',
    filename='d:\MyDB\student.mdf'
)
log on(
    name='student_log',
    filename='d:\MyDB\student_log.ldf'
)
go
use Student
create table StuInfo(
    sid int identity(1,1) primary key,
    sname varchar(10),
    sex varchar(2)

)
go

--练习2
--创建向表Stuinfo插入数据的存储过程
create proc InsertRecord
@sname varchar(10),
@sex char(2)
as
insert into StuInfo values(@sname,@sex)

go
--调用存储过程，插入数据

exec InsertRecord'张明仁','男'
go
exec InsertRecord'李朋鸣','男'
go
exec InsertRecord'罗瑞红','女'
go

--练习3

--创建表StuScore
use Student
create table StuScore(
    id int identity(1,1) primary key,
    sid int references StuInfo(sid) not null,
    exam int not null
)
go

--创建向表StuScore插入数据的存储过程
create proc InsertScore
@sid int,
@eaxm int
as
insert into StuScore values(@sid,@eaxm)
 
go
--插入数据
exec InsertScore 1,85
go
exec InsertScore 2,65
go


if exists (select * from sysobjects where name='proc_name')
drop proc proc_name
go
create proc proc_name
@sname varchar(10)='*',
@count int=0
as  
    if  @sname = '*'
        begin
        select sname,exam from StuInfo a,StuScore b where a.sid = b.sid
        end
    else
        begin
        select @count = count(*) from StuInfo a,StuScore b where a.sid = b.sid and sname=@sname
        if @count = 0
            begin 
                print convert(varchar(10),@sname)+'学生不存在'
            end
        else
            begin
            select sname,exam from StuInfo a,StuScore b where a.sid = b.sid and sname=@sname
            end
        
        end
go


exec proc_name'张仁'
go



exec proc_name