CREATE PROC [ EDURE ] procedure_name [ ; number ] 
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] 
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS
[ begin ] T-SQL 语句 [ end ]

create database test_0425 -- 创建数据库
go

use test_0425
go

-- 建表
create table student
(
stno int primary key,
stsex char(4) not null,
stname char(10) not null,
stspecialty char(20) not null,
stscore int not null
)
go

-- 设置检查约束，性别只能输入 男或女
alter table student
add constraint Check_stsex check(stsex in ('男','女'))
go
-- 设置默认约束，未输入性别时候 默认男
alter table student
add constraint Default_stsex_man default '男' for stsex
go

insert into student values(001,'男','赵一','数学',99)
insert into student values(002,'男','赵二','数学',88)
insert into student values(003,'男','赵三','数学',97)
insert into student values(004,'男','赵四','数学',86)
insert into student values(005,'男','赵五','数学',45)
insert into student values(006,'男','赵六','数学',66)
insert into student values(007,'女','赵器','数学',77)
insert into student values(008,'女','赵把','语文',99)
insert into student values(009,'女','赵久','语文',100)
insert into student values(010,'女','赵时','语文',85)
insert into student values(011,'女','赵事宜','语文',75)
insert into student values(013,'女','赵十二','语文',66)
insert into student values(015,'女','赵十三','语文',55)
insert into student values(018,'女','赵十四','语文',32)
insert into student values(020,'女','赵失误','语文',0)
insert into student (stno,stsex,stname,stspecialty,stscore)
values(017,'女','赵时期','语文',86)

select *from student 

if exists(select *from sysobjects where name='porc_fun_avge' and type='p')
    drop procedure porc_fun_avge
go

-- 设计计算专业平均值的存储过程
create proc porc_fun_avge
(
@fun_stspecial char(20)
)
as
select @fun_stspecial as '学科',AVG(stscore) as '平均分' from student where stspecialty=@fun_stspecial

exec porc_fun_avge '数学'
exec porc_fun_avge '语文'

-- 设计分段存储过程
if exists(select *from sysobjects where name='porc_fun_avge' and type='p')
    drop procedure proc_subsection_stscore
go
create proc proc_subsection_stscore
(
@score int,
@name char(10) out
)
as
if @score>60 
select stname as '姓名',stscore as '分数', '及格' as '评价' from student
else 
select stname as '姓名',stscore as '分数', '不及格' as '评价' from student
go

-- 调用/执行存储过程
declare @stscore int,
        @stsname char(10)

set @stscore=65

exec proc_subsection_stscore @stscore,@stsname out

----------------------------------------------------------------------------------------------
-- 常用系统存储过程

-- 查询数据库
use master
exec sp_databases

-- 查看数据库中的表
use sixstardb
exec sp_tables

-- 查看数据表中的字段
exec sp_columns student

-- 查看索引
exec sp_helpindex student

-- 查看约束
exec sp_helpconstraint student

-- 查看数据库相关信息
exec sp_helpdb sixstardb

-- 通过存储过程对表进行重命名
exec sp_rename 'cous','course'


----------------------------------------------------------------------------------------------
-- 自定义存储过程
select *from student

-- 1、创建不带参数的存储过程
use sixstardb 
go

if exists(select *from sysobjects where name='proc_getstudent' and type='p')
    drop procedure proc_getstudent
go

create proc proc_getstudent
as 
    select *from student

-- 调用/执行存储过程
exec proc_getstudent


-- 2、创建带有参数的存储过程
if exists(select *from sysobjects where name='proc_Findstno_student' and type='p')
    drop procedure proc_Findstno_student
go

create proc proc_Findstno_student(@fstno char(4))
as
    select *from student where stno=@fstno
go
    
-- 调用/执行存储过程
exec proc_Findstno_student 1007

-- 3、创建带通配符参数存储过程

if exists(select *from sysobjects where name='proc_FindBystname_student' and type='p')
    drop procedure proc_FindBystname_student
go

create proc proc_FindBystname_student(@name char(8)='%小%')
as 
    select *from student where stname like @name
go
    
-- 调用/执行存储过程
exec proc_FindBystname_student '%小%'


-- 4、创建带输入输出参数的存储过程
if exists(select *from sysobjects where name='proc_FindBystname_student' and type='p')
    drop procedure proc_FindBystname_student
go

create proc proc_Inputsno_getInfo_student
(
@id char(4),    -- 默认为输入参数
@name char(8) out,  -- 输出参数
@speciality char(10) out, -- 输出参数
@sum int out -- 输出参数
)
as
    select @name=stname,@speciality=stspeciality,@sum=stsum 
    from student
    where stno=@id
go


select *from student
-- 调用/执行存储过程
declare @stid char(4),
        @stsname char(8),
        @stspeciality char(10),
        @stsum int

set @stid=1002

exec proc_Inputsno_getInfo_student @stid,@stsname out,@stspeciality out,@stsum out

select @stsname as '姓名',@stspeciality  as '专业',@stsum  as '分数'