34,594
社区成员
发帖
与我相关
我的任务
分享
declare @old table()——字段省略
UPDATE a
SET
IsRestrictContract
,IsAnyDT
,ApplyBefortDelivery
,ApplyEndDateType
,ApplyEndDate
,OpenBefortDelivery
,OpenEndDateType
,OpenEndDate
,IsAssignPeriod
,AssignPeriod
,IsReusePeriod
,ReuseBefortDelivery
,ReuseEndDateType
,ReuseEndDate
,UpdateDT := SYSDATE
output
deleted.ProductId,
deleted.IsRestrictContract,
deleted.IsAnyDT,
deleted.ApplyBefortDelivery,
deleted.ApplyEndDateType,
deleted.ApplyEndDate,
deleted.OpenBefortDelivery,
deleted.OpenEndDateType,
deleted.OpenEndDate,
deleted.IsAssignPeriod,
deleted.AssignPeriod,
deleted.IsReusePeriod,
deleted.ReuseBefortDelivery,
deleted.ReuseEndDateType,
deleted.ReuseEndDate
into v_old
FROM ProductHedge a
WHERE ProductId = v_ProductId
deleted.ProductId中的deleted指的是表的名字或者给表的别名 oracle中不知道是怎么处理的
select Id,
LoginId,
TradePW,
PWErrTime,
PWExpireDate,
ReSetDT,
PWStatus,
IsPermanent
INTO @Id
FROM [Customer].[dbo].[LoginAccount] AS a
WHERE a.LoginId = @LoginId
AND a.Flag = 0
ALTER PROC [dbo].[spUpdPwdReset] @OperateId varchar(20),
@LoginId varchar(20),
@TradePW varchar(20),
@PWErrTime int,
@PWExpireDate bigint,
@ReSetDT bigint,
@PWStatus tinyint,
@IsPermanent tinyint
AS
BEGIN
SET nocount ON
DECLARE @err int
DECLARE @unique int
DECLARE @Id TABLE(
Id int,
LoginId varchar( 20 ),
TradePW varchar( 20 ),
PWErrTime int,
PWExpireDate bigint,
ReSetDT bigint,
PWStatus tinyint,
IsPermanent tinyint )
DECLARE @field int
SET @err=0
SET @unique=( SELECT TOP 1 OperateObjectId
FROM OperateObject
WHERE TableName = 'LoginAccount'
AND IsUnique = 1 )
BEGIN try
UPDATE a
SET TradePW = @TradePW,
PWErrTime = @PWErrTime,
PWExpireDate = @PWExpireDate,
ReSetDT = @ReSetDT,
PWStatus = @PWStatus,
IsPermanent = @IsPermanent,
[UpdateDT] = GETDATE( )
output deleted.Id,
deleted.LoginId,
deleted.TradePW,
deleted.PWErrTime,
deleted.PWExpireDate,
deleted.ReSetDT,
deleted.PWStatus,
deleted.IsPermanent
INTO @Id
FROM [Customer].[dbo].[LoginAccount] AS a
WHERE a.LoginId = @LoginId
AND a.Flag = 0
IF EXISTS( SELECT 1
FROM @Id )
BEGIN
--TradePW
SET @field=( SELECT TOP 1 OperateObjectId
FROM OperateObject
WHERE TableName = 'LoginAccount'
AND Fields = 'TradePW'
AND Flag = 0 )
INSERT OperateLog
(OperateDT,
OperatorId,
OperateType,
OperateObjectId,
UniqueId,
BeforeValue,
AfterValue,
Flag)
SELECT GETDATE( ),
@OperateId,
3,
@field,
a.Id,
a.TradePW,
@TradePW,
0
FROM @Id AS a
END
END try
BEGIN catch
SET @err=ERROR_NUMBER( )
END catch
SELECT @err
SET nocount OFF
END