存储过程------数据库（SQL）

 

create proc 过程名

as

  语句

 

exec 过程名

 

create proc selectproduct

@price money

as

select productname,unitprice

from Products

where unitprice>@price

exec selectproduct 10

 

create procedure selectproduct

as

 select productname,unitprice

 from products

 where unitprice>50

 

exec selectproduct

 

create procedure selectproduct

@inputprice money

as

 select productname,unitprice

 from products

 where unitprice>@inputprice

 

exec selectproduct 10

 

select employeeid

from employees

where lastname='fuller'

 

create proc selectempid

( @ln varchar(10),@empid int output)

as

 select @empid=employeeid

from employees

where lastname=@ln

 

declare @empid int

exec selectempid 'fuller',@empid output

select @empid

 

create proc selecttname

( @tid nchar(10),@tname nchar(10) output)

as

 select @tname=tname

 from teacher

 where tid=@tid

 

declare @tname nchar(10)

exec selecttname '004',@tname output

select @tname

 

select productname,unitprice

from products

where productname='tofu'

 

create proc selectprice

( @pname nvarchar(40),@uprice money output)

as

select @uprice=unitprice

from products

where productname=@pname

 

declare @uprice money

exec selectprice 'Queso Manchego La Pastora',@uprice output

print @uprice

 

create proc selectnamesex

as

 select sname,ssex

 from student

execute selectnamesex

 

declare @maxprice money,@minprice money

set @maxprice=50

set @minprice=20

select productname,unitprice

from products

where unitprice >=@minprice and unitprice<=@maxprice

 

create proc selectnameprice

( @minprice money,@maxprice money)

as

 select productname,unitprice

from products

where unitprice >=@minprice and unitprice<=@maxprice

 

exec  selectnameprice 10,50

 

create proc selectname

( @begindate datetime,@enddate datetime)

as

 select lastname,firstname,hiredate

from employees

where hiredate>=@begindate and hiredate<=@enddate

 

exec selectname '1-1-1993','12-31-1994'

 

create proc [dbo].[selecttname]

( @tid nchar(10),@tname nchar(10) output)

as

 select @tname=tname

 from teacher

 where tid=@tid

declare @tname1 nvarchar(20)

exec [selecttname] '004',@tname1 output

set @tname1=@tname1+' 大坏蛋 '

select @tname1

 

create proc selectcount

( @cateid int,@pcount int output)

as

select @pcount=count(*)

from products

where categoryid=@cateid

 

declare @count int,@cateid int

set @cateid=8

exec selectcount @cateid,@count output

print ' 第 ' + convert ( varchar ( 5),@cateid)+

    ' 类有 ' + convert ( varchar ( 5),@count)+' 种商品 '

 

create proc selectcount

( @sex nchar(10),@person int output)

as

select @person=count(*)

from student

where ssex=@sex

 

declare @sex nchar(1),@rs int

set @sex=' 女 '

exec selectcount @sex,@rs output

print ' 学校有 ' + @sex+' 生 ' + convert ( varchar ( 5),@rs)+' 人 '

 

 

 

alter proc selectcount

( @nameid char(11),@ncount int output)

as

select @ncount=count(*)

from LendBook

where Reader_ID=@nameid

 

declare @ncount int

exec selectcount '20081504114',@ncount output

select @ncount

 

select *

from sc

where sid='001'

 

alter procedure selectscore

 @st_id nchar(10),@c_id nchar(10) ,@score int output

as

select @score=score

from sc

where sid=@st_id and cid=@c_id

 

declare @s int,@st nchar(10),@course nchar(10)

set @st='004'

set @course='004'

exec selectscore @st,@course,@s output

print ' 第 ' + @st+' 号同学 ' + ' 第 ' + @course+

     ' 号课程成绩 ' + convert ( varchar ( 10),@S)

 

create proc selectbirthday

@ln nvarchar(20),@fn nvarchar(10),@birth datetime output

as

 select @birth=birthdate

 from employees

 where lastname=@ln and firstname=@fn

 

declare @birth datetime

exec selectbirthday 'Leverling','Janet',@birth output

select @birth

 

create proc selectstname

@tname nchar(10)

as

select s.sname

from student s inner join sc on s.sid=sc.sid

               inner join course c on sc.cid=c.cid

               inner join teacher t on c.tid=t.tid

where t.tname=@tname

 

exec selectstname ' 张江 '

 

create proc deletescore

@st_id nchar(10),@c_id nchar(10)

as

delete from sc

where sid=@st_id and cid=@c_id

 

exec deletescore '004','005'

 

create proc insertscore

@st_id nchar(10),@c_id nchar(10),@score int

as

 insert into sc(sid,cid,score)

   values(@st_id,@c_id,@score)

 

exec insertscore '004','004',100

 

update sc

 set score=99

where sid='004' and cid='004'

 

create proc updatescore

@st_id nchar(10),@c_id nchar(10),@newscore int

as

 update sc

 set score=@newscore

 where sid=@st_id and cid=@c_id

 print ' 记录已更新！ '

 

exec updatescore '001','001',100