电脑安全

您当前的位置:主页 > 电脑安全 >

mssql server 创建数据库到存储过程与用户自定义函

来源:[db:来源]标题:   河北省    编辑:电脑技术全能网 时间:2019-09-19 12:33

原标题:mssql server 创建数据库到存储过程与用户自定义函
   这里简略的条记了对于mssql server 创立数据库到存储进程与用户自界说函数方式,有须要的友人参考一下。

create database MyDbon(name=mainDb,filename='c:MyDbmainDb.mdf',size=10,maxsize=100,filegrowth=4),(name=secondDb,filename='C:MyDbsecondDb.ndf',size=15,maxsize=28,filegrowth=2)log on(name=log_Db,filename='C:MyDblog_Db',size=20,filegrowth=10%)--创立数据库教程的个别格局use mydbcreate table student(stuId int primary key identity (1,1),stuName varchar (20) not null,stuAge int not null check(stuAge between 20 and 50),stuSex varchar(4) not null check(stusex in('F','M')),stuDept varchar(20) check( stuDept in('软工系','环艺系','电子商务系')),stuAddress varchar(20) not null)drop table studentselect * from studentinsert into student values ('孙业宝',22,'M','软工系','河北省邢台市')insert into student values ('孙婷',20,'F','电子商务系','河北省邢台市')insert into student values ('孟几',22,'F','电子商务系','河北省邢台市')insert into student values ('小五',22,'M','软工系','河北省革要市')insert into student values ('王丹丹',22,'M','软工系','河北省阜阳市')insert into student values ('陈海波',22,'M','软工系','河北省合肥市')--繁多的输出输入参数的存储进程,create proc Myproc@Dept varchar(20),@count int outputAsif not exists(select * from student where Studept=@dept)print '没有指定范例的先生存在!!'elseselect @count=Count(*) from student where studept=@deptdrop proc myproc--履行该存储进程declare @result intExec myproc '软工系',@result outputprint @result--多输出输入的存储进程.create proc Searchstu@area varchar(20),@Sex varchar(2),@count int output,@avg_age int outputasselect @count=count(*),@avg_age=Avg(stuage) from studentwhere stuaddress=@area and stusex=@sex--履行该存储进程declare @stuNo int ,@stuAvg_age intexec searchstu '河北省邢台市','M',@stuNo output,@stuAvg_age outputselect @stuNo as 先生总数,@stuavg_age as 均匀年纪--用户自界说的函数(求立方体体积界说题目函数前往繁多值)create function dbo.CubicVolume(@CubeLength int,@CubeHenght int,@CubeWidth int)Returns intasbeginreturn (@CubeLength*@CubeHenght*@CubeWidth)enddrop function CubicVolume--挪用该方式select dbo.CubicVolume(10,10,10)--用户自界说的函数(内嵌表情势,前往一个表)create function f_stuInfo(@studept varchar(20))returns tableasreturn(select * from student where studept=@studept)--挪用该方式select * from dbo.f_stuInfo('软工系')--用户自界说的函数(多语句表值函数,前往一个用户想要显的局部数据的表)create function f_stuSexTye(@stuDept varchar(10))returns @t_stuDetailInfo table(stuName varchar(20),stuAge int ,stuSex varchar(4))asbegininsert into @t_stuDetailInfo select Stuname,stuage, Case stusex when 'M' then '男' when 'F' then '女' endfrom studentwhere stuDept=@studeptreturnend--挪用该方式函数select * from dbo.f_stuTye('软工系')