I can select @@error and @@rowcount just after insert /update statement in sql server in one line, how to handle or write this procedure in oracle 12c or upper version
so that it can handle error and rowcount just after insert /update statement like sql server in one line.
I have describe my procedure with sample data and execution process in sql server.
USE [Test]
GO
CREATE TABLE [dbo].[Employee](
[Employee_id] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Father_Name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0001', N'Khan', N'Bahadur')
GO
INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0002', N'Musa', N'Misha')
GO
INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0003', N'Don', N'Donald')
GO
USE [Test]
GO
CREATE PROCEDURE [dbo].[Proc_InsUpdEmployee]
(
@VEmp_id varchar(50),
@VName varchar(50),
@VFather_Name varchar(50),
@VVMSG VARCHAR(100) OUTPUT,
@Vmsg_code VARCHAR(10) OUTPUT
)
AS
SET NOCOUNT ON;
DECLARE @RowsCount int, @err int
BEGIN TRANSACTION
if exists ( select 1 from Employee WHERE Employee_id = @VEmp_id)
begin
UPDATE Employee
SET Name=@VName, Father_Name = @VFather_Name
WHERE Employee_id = @VEmp_id;
SELECT @RowsCount = @@ROWCOUNT,@err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT @Vmsg_code = 'ERR',
@VVMSG = 'Update Failed! Data Rollback.';
RETURN;
END
IF @RowsCount = 0
BEGIN
ROLLBACK TRANSACTION
SELECT @Vmsg_code = 'ERR',
@VVMSG = 'NO Rows Updated! Data Rollback.';
RETURN;
END
COMMIT TRANSACTION;
SELECT @Vmsg_code = 'Success',
@VVMSG = 'Data Updated Successfully.';
RETURN;
end
else
begin
insert into Employee(Employee_id,Name,Father_Name)
select @VEmp_id,@VName,@VFather_Name;
SELECT @RowsCount = @@ROWCOUNT,@err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT @Vmsg_code = 'ERR',
@VVMSG = 'Insert Failed! Data Rollback.';
RETURN;
END
IF @RowsCount = 0
BEGIN
ROLLBACK TRANSACTION
SELECT @Vmsg_code = 'ERR',
@VVMSG = 'NO Rows Inserted! Data Rollback.';
RETURN;
END
COMMIT TRANSACTION;
SELECT @Vmsg_code = 'Success',
@VVMSG = 'Data Inserted Successfully.';
RETURN;
end
go
--- Execute procedure
DECLARE @return_value int,
@VVMSG varchar(100),
@Vmsg_code varchar(10)
EXEC @return_value = [dbo].[Proc_InsUpdEmployee]
@VEmp_id = N'0004',
@VName = N'Mac Hill',
@VFather_Name = N'Banzamin',
@VVMSG = @VVMSG OUTPUT,
@Vmsg_code = @Vmsg_code OUTPUT
SELECT @VVMSG as N'@VVMSG',@Vmsg_code as N'@Vmsg_code'
Output:
@VVMSG @Vmsg_code
Data Inserted Successfully. Success
GO