mssql server 创制数据库到存款和储蓄进度与客户自

2019-12-23 10:53栏目:网站首页

这里简单的笔记了关于mssql server 创建数据库到存储过程与用户自定义函数方法,有需要的朋友参考一下。

复制代码 代码如下:create database MyDb on ( 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 mydb create table student ( stuId int primary key identity , stuName varchar not null, stuAge int not null check(stuAge between 20 and 50), stuSex varchar not null check, stuDept varchar check( stuDept in, stuAddress varchar drop table student select * from student insert 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,@count int output As if not exists(select *威尼斯人app, from student where Studept=@dept) print '没有指定类型的学生存在!!' else select @count=Count from student where studept=@dept drop proc myproc --执行该存储过程 declare @result int Exec myproc '软工系',@result output print @result --多输入输出的存储过程. create proc Searchstu @area varchar,@count int output,@avg_age int output as select @count=count,@avg_age=Avg 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 平均年龄 --用户自定义的函数 create function dbo.CubicVolume (@CubeLength int,@CubeHenght int,@CubeWidth int) Returns int as begin return (@CubeLength*@CubeHenght*@CubeWidth) end drop function CubicVolume --调用该方法 select dbo.CubicVolume --用户自定义的函数 create function f_stuInfo returns table as return ( select * from student where studept=@studept ) --调用该方法 select * from dbo.f_stuInfo --用户自定义的函数(多语句表值函数,返回一个用户想要显的部分数据的表) create function f_stuSexTye returns @t_stuDetailInfo table, stuAge int , stuSex varchar 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

create database MyDb

on

(

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 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(4) 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','电子商务系','河北省邢台市')

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 output

As

if not exists(select * from student where Studept=@dept)

print '没有指定类型的学生存在!!'

else

select @count=Count(*) 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 平均年龄

--用户自定义的函数(求立方体体积定义标题函数返回单一值)

create function dbo.CubicVolume

(@CubeLength int,@CubeHenght int,@CubeWidth int)

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('软工系')

版权声明:本文由威尼斯人app发布于网站首页,转载请注明出处:mssql server 创制数据库到存款和储蓄进度与客户自