SqlServer和Oracle中一些常用的sql语句6 存储过程

2016-12-30 09:54:56来源:oschina作者:深圳大道人点击

--不带参数的存储过程
CREATE procedure proc_sql1
as
begin
declare @i int
set @i=0
while @i<26
begin
print char(ascii('a')+@i)+'的ASCII码是:'+cast(ascii('a')+@i as varchar(50))
set @i=@i+1
end
end
execute proc_sql1
--数据查询不带参数的储存过程
create procedureproc_sql4
as
begin
select * from 职工 where 姓名 like '%张%'
select * from 仓库 where 仓库号 in(
select 仓库号 from 职工 where 姓名 like '%张%' )
end
go
execute proc_sql4
--带有输入参数的存储过程
create proc proc_sql5
@x1 int,
@x2 int,
@x3 int
as
begin
declare @max int
if @x1>@x2
set @max=@x1
else
set @max=@x2 if @x3>@max
set @max=@x3 print '3个数中最大的数是:'+cast(@max as varchar(50))
end
execute proc_sql5 15,28,39
--带有输入参数的查询存储过程
create proc proc_sql7
@mingz int,
@maxgz int
as
select * from 职工 where 工资 between @mingz and @maxgz
execute proc_sql7 1500,1800
--带输入和输出参数的存储过程
create proc proc_sql9
@changkuhao varchar(50),
@maxgzint output,
@avggzreal output
as
begin
select * from 职工 where 仓库号=@changkuhao
select @maxgz=max(工资) from 职工 where 仓库号=@changkuhao
select @avggz=avg(工资) from 职工 where 仓库号=@changkuhao
end
--显示指定仓库号的职工信息及该仓库号的最大工资和平均工资
declare @x1 int,@x2 real
execute proc_sql9 'wh1',@x1 output,@x2 output
select @x1 as wh1职工最大工资,@x2 as wh1职工平均工资---带登陆判断功能的存储过程
create proc proc_sql10
@hyuser varchar(50),
@hypwdvarchar(50)
as
begin
declare @msg varchar(50)
if @hyuser='hystu1'
begin
if @hypwd='111'
set @msg='用户名与密码正确,成功登录!'
else
set @msg='密码不正确,请重新输入!'
end
else if @hyuser='hystu2'
begin
if @hypwd='222'
set @msg='用户名与密码正确,成功登录!'
else
set @msg='密码不正确,请重新输入!'
end
else if @hyuser='hystu3'
begin
if @hypwd='333'
set @msg='用户名与密码正确,成功登录!'
else
set @msg='密码不正确,请重新输入!'
end
else
set @msg='用户名不正确,请重新输入!'
print @msg
end
execute proc_sql10 'hystu1','111'--带有判断条件的插入功能的存储过程
create proc proc_sql13
@zghao varchar(30),
@ckhao varchar(30),
@sname varchar(50),
@sex varchar(10),
@gzint
as
begin
if exists(select * from 职工 where 职工号=@zghao)
print '该职工已存在,请重新输入职工号!'
else
begin
if exists(select * from 仓库 where 仓库号=@ckhao)
begin
insert into 职工(职工号,仓库号,姓名,性别,工资) values(@zghao,@ckhao,@sname,@sex,@gz)
print '成功的插入一条记录'
end
else
print '输入的仓库号不合法,请重新输入仓库号!'
end
end
GO
execute proc_sql13 'zg1','wh1','张平','女',1350
execute proc_sql13 'zg42','wh11','张平','女',1350
execute proc_sql13 'zg42','wh1','张平','女',1350
ALTER PROCEDURE [dbo].[sp_TNotePost]
@blockID INT,
@title nvarchar (200),
@content TEXT,
@userID bigint,
@ip nvarchar (50),
@Result int out
AS
BEGIN
DECLARE @fdate date,@id bigint, @fcontent nvarchar(200),@iErrorCount int
BEGIN TRAN
SET @fdate = GETDATE()
--插入新帖
INSERT INTO T_Note (blockID,title,content,userID,ip) VALUES (@blockID,@title,@content,@userID,@ip)
SET @iErrorCount=@iErrorCount+@@error
SET @id = @@IDENTITY
select @fcontent=SUBSTRING(content,0,100) from T_Note where id=@id
--更新版块信息
UPDATE T_Topic SET num = num + 1,lastNoteID =@id,lastNoteDate=@fdate,lastNoteTitle=@fcontent where blockID=@blockID
SET @iErrorCount=@iErrorCount+@@error
--更新个人其他基本信息
UPDATE T_User_OtherSET noteCount=noteCount+1 ,lastNoteTime=@fdate,lastNoteTitle=@fcontent where userID=@userID
SET @iErrorCount=@iErrorCount+@@error
IF @iErrorCount <> 0--发生错误
BEGIN
ROLLBACK TRAN
SET @Result=0
END
ELSE
BEGIN
COMMIT TRAN
SET @Result=@id--执行成功
END
END

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台