27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb_user
(
use_id INT PRIMARY KEY,
use_name VARCHAR(20) NOT NULL,
use_sex VARCHAR(2) NOT NULL,
use_address VARCHAR(50) NOT NULL
);
INSERT INTO tb_user VALUES( '1', '陈小', '女', '广东省' );
INSERT INTO tb_user VALUES( '2', '钟天', '男', '四川省' );
INSERT INTO tb_user VALUES( '3', '李四', '男', '云南省' );
INSERT INTO tb_user VALUES( '4', '叶茂', '男', '广东省' );
INSERT INTO tb_user VALUES( '5', '吴名', '男', '黑龙江' );
CREATE TABLE tb_score
(
use_id INT,
sco_subject VARCHAR(20) NOT NULL,
sco_score SMALLINT NOT NULL,
FOREIGN KEY(use_id) REFERENCES tb_user(use_id)
);
INSERT INTO tb_score VALUES( '1', '数学', '97' );
INSERT INTO tb_score VALUES( '2', '英语', '97' );
INSERT INTO tb_score VALUES( '3', '物理', '97' );
INSERT INTO tb_score VALUES( '4', '数学', '97' );
INSERT INTO tb_score VALUES( '5', '英语', '97' );
/*********************/
/* 更新 */
/********************/
IF OBJECT_ID('pro_Update_values')>0
DROP PROCEDURE pro_Update_values
GO
CREATE PROCEDURE pro_Update_values
@inp_use_id INT,
@inp_use_name NVARCHAR(20),
@inp_use_sex NVARCHAR(2),
@inp_use_address NVARCHAR(50),
@inp_sco_subject NVARCHAR(20),
@inp_sco_score SMALLINT,
@out_message NVARCHAR(50) OUTPUT
AS
BEGIN TRAN A
BEGIN TRY
UPDATE tb_score
SET sco_subject = @inp_sco_subject, sco_score = @inp_sco_score
WHERE use_id = @inp_use_id
SET @out_message= 'tb_score表更新'+CASE WHEN @@ROWCOUNT>0 THEN '成功' ELSE '失败' END+char(13)
UPDATE tb_user
SET use_name = @inp_use_name, use_sex = @inp_use_sex, use_address = @inp_use_address
WHERE use_id = @inp_use_id
SET @out_message = @out_message+ 'tb_user表更新'+CASE WHEN @@ROWCOUNT>0 THEN '成功' ELSE '失败' END
END TRY
BEGIN CATCH
SET @out_message = '更新失败'
ROLLBACK TRAN A
END CATCH
COMMIT TRAN A
GO
--数据库存在@use_id=1
DECLARE @out_message NVARCHAR(50)
EXEC pro_Update_values 1, '张三', '男', '广东省', '语文', 97, @out_message OUTPUT
PRINT @out_message
/*
tb_score表更新成功
tb_user表更新成功
*/
--数据库不存在@use_id=10
DECLARE @out_message NVARCHAR(50)
EXEC pro_Update_values 10, '李四', '男', '广东省', '语文', 97, @out_message OUTPUT
PRINT @out_message
/*
tb_score表更新失败
tb_user表更新失败
*/
declare @out_message nvarchar(50)
EXEC pro_Update_values 1,'张三','男','广东省','语文','a',@out_message output print @out_message
CREATE PROCEDURE pro_Update_values
@inp_use_id int,
@inp_use_name nvarchar(20),
@inp_use_sex nvarchar(2),
@inp_use_address nvarchar(50),
@inp_sco_subject nvarchar(20),
@inp_sco_score smallint,
@out_message nvarchar(50) output
AS
BEGIN TRAN A
BEGIN TRY
IF EXISTS(SELECT 1 FROM tb_score WHERE use_id=@inp_use_id )
begin
UPDATE tb_score SET sco_subject=@inp_sco_subject,sco_score=@inp_sco_score WHERE use_id=@inp_use_id
UPDATE tb_user SET use_name=@inp_use_name,use_sex=@inp_use_sex,use_address=@inp_use_address WHERE use_id=@inp_use_id
SET @out_message='更新成功'
end
ELSE
begin
SET @out_message='更新失败'
end
END TRY
BEGIN CATCH
SET @out_message='更新失败'
END CATCH
IF @@ERROR<>0
ROLLBACK TRAN A
ELSE
COMMIT TRAN A
go
CREATE PROCEDURE pro_Update_values
@inp_use_id int,
@inp_use_name nvarchar(20),
@inp_use_sex nvarchar(2),
@inp_use_address nvarchar(50),
@inp_sco_subject nvarchar(20),
@inp_sco_score smallint,
@out_message nvarchar(50) output
AS
set @out_message=''
BEGIN TRAN A
BEGIN TRY
UPDATE tb_score SET sco_subject=@inp_sco_subject,sco_score=@inp_sco_score WHERE use_id=@inp_use_id
if @@rowcount=0
set @out_message='tb_score表,数据不存在!'+char(10)
UPDATE tb_user SET use_name=@inp_use_name,use_sex=@inp_use_sex,use_address=@inp_use_address WHERE use_id=@inp_use_id
if @@rowcount=0
set @out_message=@out_message+'tb_user表,数据不存在'
END TRY
BEGIN CATCH
SET @out_message='更新失败'
END CATCH
IF @@ERROR<>0
ROLLBACK TRAN A
ELSE
if @out_message='' SET @out_message='更新成功'
COMMIT TRAN A
go
CREATE PROCEDURE pro_Update_values
@inp_use_id int,
@inp_use_name nvarchar(20),
@inp_use_sex nvarchar(2),
@inp_use_address nvarchar(50),
@inp_sco_subject nvarchar(20),
@inp_sco_score smallint,
@out_message nvarchar(50) output
AS
BEGIN TRAN A
BEGIN TRY
IF EXISTS(SELECT 1 FROM tb_score WHERE use_id=@inp_use_id )
UPDATE tb_score SET sco_subject=@inp_sco_subject,sco_score=@inp_sco_score WHERE use_id=@inp_use_id
UPDATE tb_user SET use_name=@inp_use_name,use_sex=@inp_use_sex,use_address=@inp_use_address WHERE use_id=@inp_use_id
SET @out_message='更新成功'
ELSE
SET @out_message='更新失败'
END TRY
IF @@ERROR<>0
ROLLBACK TRAN A
ELSE
COMMIT TRAN A
go
BEGIN TRY
IF EXISTS(SELECT 1 FROM tb_score WHERE use_id=@inp_use_id )
UPDATE tb_score SET sco_subject=@inp_sco_subject,sco_score=@inp_sco_score WHERE use_id=@inp_use_id
UPDATE tb_user SET use_name=@inp_use_name,use_sex=@inp_use_sex,use_address=@inp_use_address WHERE use_id=@inp_use_id
SET @out_message='更新成功'
ELSE
SET @out_message='更新失败'
END TRY