金莎娱乐手机版mssql server 成立数据库到存储进程与顾客自定义函数

此间大致的笔记了关于mssql server
创造数据库到存储进程与客户自定义函数方法,有需求的爱人参照他事他说加以考察一下。

复制代码 代码如下:create database MyDb on
( name=mainDb, filename=’c:\MyDb\mainDb.mdf’, size=10, maxsize=100,
filegrowth=4 ), ( name=secondDb, filename=’C:\MyDb\secondDb.ndf’,
size=15, maxsize=28, filegrowth=2 ) log on ( name=log_Db,
filename=’C:\MyDb\log_Db’, size=20, filegrowth=10% State of Qatar–创设数据库的常常格式 use mydb create 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(4State of Qatar not null check(stusex
in(‘F’,’M’卡塔尔卡塔尔(قطر‎, stuDept varchar(20卡塔尔 check( stuDept
in(‘软工系’,’环境艺术系’,’电商系’卡塔尔国卡塔尔(قطر‎, stuAddress varchar(20卡塔尔 not null 卡塔尔国drop table student select * from student insert into student values
(‘孙业宝’,22,’M’,’软工系’,’浙江省江门市’卡塔尔 insert into student values
(‘孙婷’,20,’F’,’电商系’,’山西省绵阳湾股市’State of Qatar insert into student values
(‘孟几’,22,’F’,’电商系’,’青海省咸阳湾股市’State of Qatar insert into student values
(‘小五’,22,’M’,’软工系’,’西藏省革要市’卡塔尔国 insert into student values
(‘王丹丹’,22,’M’,’软工系’,’河南省秦皇岛市’卡塔尔国 insert into student values
(‘陈海波’,22,’M’,’软工系’,’黑龙江省曼海姆市’卡塔尔(قطر‎–单大器晚成的输入输出参数的仓库储存进度, create proc Myproc @Dept
varchar(20State of Qatar,@count int output As if not exists(select * from student
where Studept=@deptState of Qatar print ‘未有一些名项指标上学的小孩子存在!!’ else select
@count=Count(*金莎娱乐手机版,State of Qatar from student where studept=@dept drop proc myproc
–施行该存款和储蓄进度 declare @result int Exec myproc ‘软工系’,@result output
print @result –多输入输出的积累进度. create proc Searchstu @area
varchar(20卡塔尔国,@Sex varchar(2卡塔尔国,@count int output,@avg_age int output as
select @count=count(*),@avg_age=Avg(stuage卡塔尔(قطر‎ from student where
stuaddress=@area and stusex=@sex –推行该存款和储蓄进度 declare @stuNo int
,@stuAvg_age int exec searchstu ‘甘肃省西宁湾股市’,’M’,@stuNo
output,@stuAvg_age output select @stuNo as 学子总量,@stuavg_age as
平均岁数 –客商自定义的函数(求立方体体积定义标题函数重返单生龙活虎值State of Qatar create
function dbo.CubicVolume (@CubeLength int,@CubeHenght int,@CubeWidth
intState of Qatar Returns int as begin return (@CubeLength*@CubeHenght*@CubeWidth卡塔尔end drop function CubicVolume –调用该措施 select
dbo.CubicVolume(10,10,10卡塔尔 –顾客自定义的函数(内嵌表格局,再次来到贰个表)create function f_stuInfo(@studept varchar(20)) returns table as return
( 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) ) as begin insert into @t_stuDetailInfo select
Stuname,stuage, Case stusex when ‘M’ then ‘男’ when ‘F’ then ‘女’ end
from student where stuDept=@studept return end –调用该方法函数 select
* from dbo.f_stuTye(‘软工系’)

相关文章