如题,sqlserver sql:
CREATE PROCEDURE [sp_TC_CheckPTP]
ASSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
BEGIN TRANDECLARE @Int_BuferDays int
DECLARE @Amt_BuferRate money
DECLARE @Dat_Today char(10)
DECLARE @Dat_BfoDate char(10)
DECLARE @Str_FieldData varchar(20)
DECLARE @Str_RiskRank varchar(2),
@Str_Bucket varchar(3)
DECLARE @Int_DefAddDays int SELECT TOP 1 @Int_BuferDays=Int_BuferDays , @Amt_BuferRate = Amt_BuferRate FROM tbl_TC_ChkPTPSet (NOLOCK)
IF @@ROWCOUNT = 0 --叁数表内无设定时,给予默认值
BEGIN
SET @Int_BuferDays=0
SET @Amt_BuferRate=1
ENDSET @Dat_Today = to_char (sysdate, 'yyyy-mm-dd hh:mi:ss:mmm')
SET @Dat_BfoDate = to_char (sysdate - @Int_BuferDays , 'yyyy-mm-dd hh:mi:ss:mmm') DECLARE @Pk_PTPHistory bigint, @Fk_Bill varchar (20), @Dat_PTP char(10), @Amt_PTP moneyDECLARE curPTP CURSOR FOR
SELECT Pk_PTPHistory, Fk_Bill, Dat_PTP, Amt_PTP FROM tbl_TC_PTPHistory (NOLOCK)
WHERE str_Paid = '0' AND Dat_PTP BETWEEN @Dat_BfoDate AND @Dat_TodayOPEN curPTP
FETCH NEXT FROM curPTP
INTO @Pk_PTPHistory, @Fk_Bill, @Dat_PTP, @Amt_PTPWHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 @Str_FieldData=Fk_Bill FROM tbl_Payment (NOLOCK) WHERE Fk_Bill=@Fk_Bill AND Dat_Payment >= @Dat_BfoDate AND Amt_Payment >= (@Amt_PTP * @Amt_BuferRate)
IF @Str_FieldData IS NOT NULL
BEGIN
UPDATE tbl_TC_PTPHistory SET Str_Paid='1' WHERE Pk_PTPHistory = @Pk_PTPHistory
IF @@ERROR <>0
ROLLBACK TRAN SELECT TOP 1 @Str_RiskRank = Str_RiskRank , @Str_Bucket = Str_Bucket FROM tbl_BillComm (NOLOCK)
WHERE Pk_Bill = @Fk_Bill SELECT TOP 1 @Int_DefAddDays = Int_DefAddDays FROM tbl_PB_FollowupRule (NOLOCK)
WHERE Str_RiskRank = @Str_RiskRank AND Str_Bucket = @Str_Bucket AND Fk_Referto='998'
IF @@ROWCOUNT = 0
BEGIN
SET @Int_DefAddDays = 0
END
UPDATE tbl_BillInfo SET Fk_Referto = '998', Dat_FollowUp = to_char(sysdate+@Int_DefAddDays, 'yyyy-mm-dd hh:mi:ss:mmm') WHERE Pk_Bill =@Fk_Bill
END FETCH NEXT FROM curPTP
INTO @Pk_PTPHistory, @Fk_Bill, @Dat_PTP, @Amt_PTP
ENDCLOSE curPTP
DEALLOCATE curPTP
DECLARE curBP CURSOR FOR
SELECT DISTINCT Fk_Bill FROM tbl_TC_PTPHistory (NOLOCK)
WHERE str_Paid = '0' AND Dat_PTP = @Dat_BfoDateOPEN curBP
FETCH NEXT FROM curBP
INTO @Fk_Bill
WHILE @@FETCH_STATUS = 0
BEGIN SELECT TOP 1 @Str_RiskRank = Str_RiskRank , @Str_Bucket = Str_Bucket FROM tbl_BillComm (NOLOCK)
WHERE Pk_Bill = @Fk_Bill SELECT TOP 1 @Int_DefAddDays = Int_DefAddDays FROM tbl_PB_FollowupRule (NOLOCK)
WHERE Str_RiskRank = @Str_RiskRank AND Str_Bucket = @Str_Bucket AND Fk_Referto='999'
IF @@ROWCOUNT = 0
BEGIN
SET @Int_DefAddDays = 0
END UPDATE tbl_BillInfo SET Fk_Referto = '999', Dat_FollowUp = to_char(sysdate+@Int_DefAddDays, 'yyyy-mm-dd hh:mi:ss:mmm') WHERE Pk_Bill =@Fk_Bill
IF @@ERROR <>0
ROLLBACK TRAN FETCH NEXT FROM curBP
INTO @Fk_Bill
ENDCLOSE curBP
DEALLOCATE curBPCOMMIT TRAN
GO
CREATE PROCEDURE [sp_TC_CheckPTP]
ASSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
BEGIN TRANDECLARE @Int_BuferDays int
DECLARE @Amt_BuferRate money
DECLARE @Dat_Today char(10)
DECLARE @Dat_BfoDate char(10)
DECLARE @Str_FieldData varchar(20)
DECLARE @Str_RiskRank varchar(2),
@Str_Bucket varchar(3)
DECLARE @Int_DefAddDays int SELECT TOP 1 @Int_BuferDays=Int_BuferDays , @Amt_BuferRate = Amt_BuferRate FROM tbl_TC_ChkPTPSet (NOLOCK)
IF @@ROWCOUNT = 0 --叁数表内无设定时,给予默认值
BEGIN
SET @Int_BuferDays=0
SET @Amt_BuferRate=1
ENDSET @Dat_Today = to_char (sysdate, 'yyyy-mm-dd hh:mi:ss:mmm')
SET @Dat_BfoDate = to_char (sysdate - @Int_BuferDays , 'yyyy-mm-dd hh:mi:ss:mmm') DECLARE @Pk_PTPHistory bigint, @Fk_Bill varchar (20), @Dat_PTP char(10), @Amt_PTP moneyDECLARE curPTP CURSOR FOR
SELECT Pk_PTPHistory, Fk_Bill, Dat_PTP, Amt_PTP FROM tbl_TC_PTPHistory (NOLOCK)
WHERE str_Paid = '0' AND Dat_PTP BETWEEN @Dat_BfoDate AND @Dat_TodayOPEN curPTP
FETCH NEXT FROM curPTP
INTO @Pk_PTPHistory, @Fk_Bill, @Dat_PTP, @Amt_PTPWHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 @Str_FieldData=Fk_Bill FROM tbl_Payment (NOLOCK) WHERE Fk_Bill=@Fk_Bill AND Dat_Payment >= @Dat_BfoDate AND Amt_Payment >= (@Amt_PTP * @Amt_BuferRate)
IF @Str_FieldData IS NOT NULL
BEGIN
UPDATE tbl_TC_PTPHistory SET Str_Paid='1' WHERE Pk_PTPHistory = @Pk_PTPHistory
IF @@ERROR <>0
ROLLBACK TRAN SELECT TOP 1 @Str_RiskRank = Str_RiskRank , @Str_Bucket = Str_Bucket FROM tbl_BillComm (NOLOCK)
WHERE Pk_Bill = @Fk_Bill SELECT TOP 1 @Int_DefAddDays = Int_DefAddDays FROM tbl_PB_FollowupRule (NOLOCK)
WHERE Str_RiskRank = @Str_RiskRank AND Str_Bucket = @Str_Bucket AND Fk_Referto='998'
IF @@ROWCOUNT = 0
BEGIN
SET @Int_DefAddDays = 0
END
UPDATE tbl_BillInfo SET Fk_Referto = '998', Dat_FollowUp = to_char(sysdate+@Int_DefAddDays, 'yyyy-mm-dd hh:mi:ss:mmm') WHERE Pk_Bill =@Fk_Bill
END FETCH NEXT FROM curPTP
INTO @Pk_PTPHistory, @Fk_Bill, @Dat_PTP, @Amt_PTP
ENDCLOSE curPTP
DEALLOCATE curPTP
DECLARE curBP CURSOR FOR
SELECT DISTINCT Fk_Bill FROM tbl_TC_PTPHistory (NOLOCK)
WHERE str_Paid = '0' AND Dat_PTP = @Dat_BfoDateOPEN curBP
FETCH NEXT FROM curBP
INTO @Fk_Bill
WHILE @@FETCH_STATUS = 0
BEGIN SELECT TOP 1 @Str_RiskRank = Str_RiskRank , @Str_Bucket = Str_Bucket FROM tbl_BillComm (NOLOCK)
WHERE Pk_Bill = @Fk_Bill SELECT TOP 1 @Int_DefAddDays = Int_DefAddDays FROM tbl_PB_FollowupRule (NOLOCK)
WHERE Str_RiskRank = @Str_RiskRank AND Str_Bucket = @Str_Bucket AND Fk_Referto='999'
IF @@ROWCOUNT = 0
BEGIN
SET @Int_DefAddDays = 0
END UPDATE tbl_BillInfo SET Fk_Referto = '999', Dat_FollowUp = to_char(sysdate+@Int_DefAddDays, 'yyyy-mm-dd hh:mi:ss:mmm') WHERE Pk_Bill =@Fk_Bill
IF @@ERROR <>0
ROLLBACK TRAN FETCH NEXT FROM curBP
INTO @Fk_Bill
ENDCLOSE curBP
DEALLOCATE curBPCOMMIT TRAN
GO
一些数据类型要相应的转换
TOP 换成oracle中的rownum
select给变量赋值是,如果没有记录会引发no_data_found异常
具体例子参看:http://hi.baidu.com/zhao_e893/blog/item/5c7097356a9d8f1190ef3937.html
AS
Int_Buf NUMBER(10,0);
Amt_Buf NUMBER(19,4);
Dat_Tod CHAR(10);
Dat_Bfo CHAR(10);
Str_Fie VARCHAR2(20);
Str_Ris VARCHAR2(2);
Str_Buc VARCHAR2(3);
Int_Def NUMBER(10,0);
Pk_PTPH NUMBER(19,0);
Fk_Bill VARCHAR2(20);
Dat_PTP CHAR(10);
Amt_PTP NUMBER(19,4);
CURSOR curPTP IS SELECT Pk_PTPH,Fk_Bill,Dat_PTP,Amt_PTP FROM tbl_TC_PTPHistory
WHERE str_Pai = '0' AND Dat_PTP BETWEEN Dat_Bfo AND Dat_Tod;
CURSOR curBP IS SELECT DISTINCT Fk_Bill FROM tbl_TC_PTPHistory
WHERE str_Pai = '0' AND Dat_PTP = Dat_Bfo;
SWV_err NUMBER(10,0) DEFAULT 0;
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin
SELECT Int_Buf,Amt_Buf INTO Int_Buf,Amt_Buf FROM tbl_TC_ChkPTPSet WHERE ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
--叁数表内无设定时,给予默认值 IF SQL%ROWCOUNT = 0 then
Int_Buf := 0;
Amt_Buf := 1;
END IF;
Dat_Today := to_char (sysdate, 'yyyy-mm-dd hh:mi:ss:mmm'); Dat_BfoDate := to_char (sysdate - Int_BuferDays , 'yyyy-mm-dd hh:mi:ss:mmm'); OPEN curPTP;
FETCH curPTP
INTO Pk_PTPH,Fk_Bill,Dat_PTP,Amt_PTP;
while curPTP%FOUND loop
begin
SELECT Fk_Bill INTO Str_Fie FROM tbl_Payment WHERE Fk_Bill = Fk_Bill AND Dat_Pay >= Dat_Bfo AND Amt_Pay >=(Amt_PTP*Amt_Buf) AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
IF Str_Fie IS NOT NULL then
SWV_err := 0;
begin
UPDATE tbl_TC_PTPHistory SET Str_Pai2 = '1' WHERE Pk_PTPH = Pk_PTPH;
EXCEPTION
WHEN OTHERS THEN
SWV_err := SQLCODE;
end;
IF SWV_err <> 0 then
ROLLBACK;
END IF;
begin
SELECT Str_Ris,Str_Buc INTO Str_Ris,Str_Buc FROM tbl_BillComm WHERE Pk_Bill = Fk_Bill AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
begin
SELECT Int_Def INTO Int_Def FROM tbl_PB_FollowupRule WHERE Str_Ris = Str_Ris AND Str_Buc = Str_Buc AND Fk_Refe = '998' AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
IF SQL%ROWCOUNT = 0 then
Int_Def := 0;
END IF;
END IF;
FETCH curPTP
INTO Pk_PTPH,Fk_Bill,Dat_PTP,Amt_PTP;
end loop;
CLOSE curPTP;
OPEN curBP;
FETCH curBP
INTO Fk_Bill;
while curBP%FOUND loop
begin
SELECT Str_Ris,Str_Buc INTO Str_Ris,Str_Buc FROM tbl_BillComm WHERE Pk_Bill = Fk_Bill AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
begin
SELECT Int_Def INTO Int_Def FROM tbl_PB_FollowupRule WHERE Str_Ris = Str_Ris AND Str_Buc = Str_Buc AND Fk_Refe = '999' AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
IF SQL%ROWCOUNT = 0 then
Int_Def := 0;
END IF;
UPDATE tbl_BillInfo SET Fk_Referto = '999', Dat_FollowUp = to_char(sysdate+Int_DefAddDays, 'yyyy-mm-dd hh:mi:ss:mmm') WHERE Pk_Bill =Fk_Bill; IF SWV_err <> 0 then
ROLLBACK;
END IF;
FETCH curBP
INTO Fk_Bill;
end loop;
CLOSE curBP; COMMIT;
END;
/