從創立數據庫到存儲進程與用戶自界說函數的小感。本站提示廣大學習愛好者：（從創立數據庫到存儲進程與用戶自界說函數的小感）文章只能為提供參考，不一定能成為您想要的結果。以下是從創立數據庫到存儲進程與用戶自界說函數的小感正文
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%
)
--創立數據庫的普通格局
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('軟工系')
