帮助基于光标的存储过程变得越来越慢！ [英] Help Cursor Based Stored Procedure Is Getting Slower and Slower!

问题描述

我充其量只是因为我希望有更好的人可以提供帮助。

我有一个存储过程来更新我用2

游标编写的视图。 （一种内环的类型）我这样写的因为我不能使用reqular transact SQL来做b $ b。


问题是这个程序运行时间越来越长。

现在最多5个小时！它正在分析大约30,000条记录。我认为

部分是因为我们每个月都会添加新记录。


程序就像这样。

第一个Cursor存储一个来自

视图的唯一帐户和duedate组合。

然后它会查找视图中具有该帐户的所有帐户

combo并加载它们进入Cursor 2，它们将它们组合在一起以获得数据

操作。帐户必须以这种方式分组，因为

帐户可以在每个

帐户到期日期组合中有不同的截止日期和多个记录，他们需要以这种方式查看作为

小团体。


这是我的程序，我希望有人可以对此有所了解。我的

老板给了我一把。 （我认为他认为女孩不能编码！）

我到目前为止我希望有人可以帮助我进一步优化它。



创建程序dbo.sp_PromiseStatus

AS


BEGIN

设定NOCOUNT


/ *全局变量* /

DECLARE @tot_pay money

DECLARE @rec_upd VARCHAR（1）

DECLARE @todays_date varchar（12）

DECLARE @mActivityDate2_temp datetime

DECLARE @tot_paydate datetime

/ *游标的变量ACT_CUR1 * /

DECLARE @ mAcct_Num1 BIGINT

DECLARE @mDueDate1 datetime

/ *变量ACT_CUR2 * /

DECLARE @ mAcct_Num2 BIGINT

DECLARE @mActivity_Date2 datetime

DECLARE @ mPromise_Amt_1钱

DECLARE @mPromise_Status varchar（3）

DECLARE @mCurrent_Due_Amt money

DECLARE @mDPD int

DECLARE @mPromise_Date datetime


SELECT @todays_date = ''''+ CAST（DATEPART（mm，getdate（））AS varchar（2））

+''/''+ CAST（DATEPART（dd，getdate（））AS varchar（2 ））

+''/''+ CAST（DATEPART（yyyy，getdate（））AS varchar（4））+''''


DECLARE ACT_CUR1 CURSOR

SELECT DISTINCT

A.ACCT_NUM，

A.DUE_DATE

from VWAPPLICABLEPROMISEACTIVITYRECORDS A br />
OPEN ACT_CUR1

FETCH NEXT from ACT_CUR1 INTO @ mAcct_Num1，@ mDueDate1


WHILE（@@ FETCH_STATUS = 0）

BEGIN

SELECT @rec_upd =''N''


DECLARE ACT_CUR2 CURSOR

SELECT

B.ACCT_NUM，

B.ACTIVITY_DATE，

B.PROMISE_AMT_1，

B.PROMISE_STATUS，

B.CURRENT_DUE_AMT，

B.DAYS_DELINQUENT_NUM，

B.PROMISE_DATE_1

来自VWAPPLICABLEPROMISEACTIVITYRECORDS B（UPDLOCK）

WHERE B.ACCT_NUM = @ mAcct_Num1

AND B.DUE_DATE = @ mDueDate1

订单B.ACCT_NUM，B.DUE_DATE，B.ACTIVITY_DATE，案例

B.Time_Obtained

当0那么0

ELSE 1

END Desc，B.Time_Obtained

OPEN ACT_CUR2

FETCH NEXT from ACT_CUR2

INTO @ mAcct_Num2， @mActivity_Date2，@ mPromise_Amt_1，@ mPromise_Status，@ mCurrent_Due_Amt，@ mDPD，@ mPromise_Date

WHILE（@@ FETCH_STATUS = 0）

BEGIN


---- CHECK ----------------------------------------- -----------------------------

--DECLARE @PrintVariable2 VARCHAR（8000）

--SELECT @PrintVariable2 = CAST（@ MACCT_NUM2 AS VARCHAR）+''

''+ CAST（@ MACTIVITY_DATE2 AS VARCHAR）+''''+ CAST（@ MPROMISE_AMT_1 AS

VARCHAR）+''''+ CAST（@MPROMISE_STATUS AS VARCHAR）+''

''+ CAST（@mCurrent_Due_Amt AS VARCHAR）+''''+ CAST （@mDPD AS VARCHAR）+''

''+ CAST（@mPromise_Date AS VARCHAR）

--PRINT @PrintVariable2

- --END

CHECK -------------------------------------------- ----------------


IF @mDPD> = 30

BEGIN


SELECT @tot_pay = SUM（CONVERT（FLOAT，C.PAY_AMT））

FROM vwAplicablePayments C

WHERE C.ACCT_NUM = @ mAcct_Num2
AND C.ACTIVITY_DATE> = @ mActivity_Date2

AND C.ACTIVITY_DATE< @mActivity_Date2 + 15

----检查--------------------------------- -------------------------------------

--DECLARE @PrintVariable3 VARCHAR（8000）

--SELECT @PrintVariable3 =''更大= 30美元收集''

--PRINT @PrintVariable3

- --END

CHECK -------------------------------------- ----------------------

END

ELSE IF @mDPD< 30

BEGIN


SELECT @tot_pay = SUM（CONVERT（FLOAT，C.PAY_AMT））

FROM vwAplicablePayments C

WHERE C.ACCT_NUM = @ mAcct_Num2

AND C.ACTIVITY_DATE> = @ mActivity_Date2

AND C.ACTIVITY_DATE @ mActivity_Date2 AND

@mPromise_Date + 5

---- CHECK ----------------------------- ---------------------------------------

--DECLARE @PrintVariable4 VARCHAR（8000）

--SELECT @PrintVariable4 =''少收集30美元''

--PRINT @PrintVariable4

- --- END CHECK --------------------------------------------- ---------------

END

------------------ ----------------------我修改了

LOGIC ----------------- --------------------------------------

IF @rec_upd = ''N''

BEGIN


IF @mDPD> = 30

BEGIN


SELECT @ mActivityDate2_temp = @ mActivity_Date2 + 15


--DECLARE @PrintVariable5 VARCHAR（8000）

--SELECT @PrintVariable5 =''GREATER = 30使用ACTVITY_DATE + 15''

--PRINT @PrintVariable5

结束

ELSE IF @mDPD< 30

BEGIN

SELECT @ mActivityDate2_temp = @mPromise_Date + 5


--DECLARE @PrintVariable6 VARCHAR（8000）
--SELECT @PrintVariable6 =''少于30使用PROMISE_DATE + 5''

--PRINT @PrintVariable6


END

如果@tot_pay> = 0.9 * @mCurrent_Due_Amt - 曾经是承诺amt

BEGIN

更新VWAPPLICABLEPROMISE活动奖励

SET PROMISE_STATUS =''PK''，

TOTAL_DOLLARS_COLL = @tot_pay

当前ACT_CUR2当前

- 此声明更新状态的时间被放入

到PK。

IF @mPromise_Status IN（''PTP''，''OP''）

BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS

SET Status_Date = @todays_date

当前ACT_CUR2当前

结束


SELECT @rec_upd =''Y''

END

IF（（@tot_pay< 0.9 * @mCurrent_Due_Amt）或@t ot_pay IS NULL）

AND（@mActivityDate2_temp> @todays_date） - 需要在这里放置1天

的快照9/01/2004

BEGIN

更新VWAPPLICABLEPROMISEACTIVITYRECORDS

SET PROMISE_STATUS =''OP''

当前ACT_CUR2当前


- 此声明更新状态的放置时间

进入OP，这是原始的活动日期。

- 记录将保留此日期，直到它进入PK，PB或

IP。 br />
如果@mPromise_Status IN（''PTP''，''OP''）

BEGIN

更新VVAPPLICABLEPROMISE活动重复

SET Status_Date = @ mActivity_Date2

当前的ACT_CUR2当前

结束


结束

ELSE IF （（@tot_pay< 0.9 * @mCurrent_Due_Amt）或@tot_pay IS

NULL）

AND（@mActivityDate2_temp< = @todays_date） - 需要放1天

月份快照9/01/2004

BEGIN

更新VWAPPLICABLEPROMISEACTIVITYRECORD S $ / $
SET PROMISE_STATUS =''PB''，

TOTAL_DOLLARS_COLL = @tot_pay为空时的情况

然后0其他@tot_pay结束

当前的ACT_CUR2当前

- 此声明更新状态的时间

到PB。

IF @mPromise_Status IN（''PTP''，''OP''）

BEGIN

更新VWAPPLICABLEPROMISEACTIVITYRECORDS

SET Status_Date = @todays_date

当前ACT_CUR2当前

结束

结束

结束

如果@rec_upd = ''Y''

BEGIN

更新VVAPPLICABLEPROMISEACTIVITYRECORDS

SET PROMISE_STATUS =''IP''，

TOTAL_DOLLARS_COLL = 0

当前ACT_CUR2当前

- 此声明更新状态

到IP的时间。

IF @mPromise_Status NOT IN（''IP''）

BEGIN

更新VVAPPLICABLEPROMISE活动重看

S ET Status_Date = @todays_date

当前ACT_CUR2当前

结束


结束

FETCH NEXT FROM ACT_CUR2 INTO @ mAcct_Num2

，@ mActivity_Date2，@ mPromise_Amt_1，@ mPromise_Status，@ mCurrent_Due_Amt，@ mDPD，@ mPromise_Date

END

CLOSE ACT_CUR2

DEALLOCATE ACT_CUR2


FETCH NEXT from ACT_CUR1 INTO @ mAcct_Num1，@ mDueDate1

END

CLOSE ACT_CUR1

DEALLOCATE ACT_CUR1


设定NOCOUNT OFF


结束

GO

I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn''t
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.

The procedure works like this.
The first Cursor stores a unique account and duedate combination from
the view.
It then finds all the accts in the view that have that account duedate
combo and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because a
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups.

Here is my procedure I hope someone can shead some light on this. My
boss is giving me heck about it. (I think he thinks Girls cant code!)
I got this far I hope someone can help me optimize it further.


CREATE PROCEDURE dbo.sp_PromiseStatus
AS

BEGIN
SET NOCOUNT ON

/* Global variables */
DECLARE @tot_pay money
DECLARE @rec_upd VARCHAR(1)
DECLARE @todays_date varchar(12)
DECLARE @mActivityDate2_temp datetime
DECLARE @tot_paydate datetime
/* variables for cursor ACT_CUR1*/
DECLARE @mAcct_Num1 BIGINT
DECLARE @mDueDate1 datetime

/* variables for ACT_CUR2 */
DECLARE @mAcct_Num2 BIGINT
DECLARE @mActivity_Date2 datetime
DECLARE @mPromise_Amt_1 money
DECLARE @mPromise_Status varchar(3)
DECLARE @mCurrent_Due_Amt money
DECLARE @mDPD int
DECLARE @mPromise_Date datetime

SELECT @todays_date =''''+CAST(DATEPART(mm,getdate()) AS varchar(2))
+''/''+CAST(DATEPART(dd,getdate()) AS varchar(2))
+''/''+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''''

DECLARE ACT_CUR1 CURSOR FOR
SELECT DISTINCT
A.ACCT_NUM,
A.DUE_DATE
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS A
OPEN ACT_CUR1
FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @rec_upd = ''N ''

DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE,
B.PROMISE_AMT_1,
B.PROMISE_STATUS,
B.CURRENT_DUE_AMT,
B.DAYS_DELINQUENT_NUM,
B.PROMISE_DATE_1
FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)
WHERE B.ACCT_NUM = @mAcct_Num1
AND B.DUE_DATE = @mDueDate1
ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASE
B.Time_Obtained
WHEN 0 THEN 0
ELSE 1
END Desc, B.Time_Obtained

OPEN ACT_CUR2
FETCH NEXT FROM ACT_CUR2
INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_Date
WHILE (@@FETCH_STATUS = 0)
BEGIN

----CHECK----------------------------------------------------------------------
--DECLARE @PrintVariable2 VARCHAR (8000)
--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''
''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+'' ''+CAST(@MPROMISE_AMT_1 AS
VARCHAR)+'' ''+CAST(@MPROMISE_STATUS AS VARCHAR)+''
''+CAST(@mCurrent_Due_Amt AS VARCHAR)+'' ''+CAST(@mDPD AS VARCHAR)+''
''+CAST(@mPromise_Date AS VARCHAR)
--PRINT @PrintVariable2
----END
CHECK------------------------------------------------------------

IF @mDPD >= 30
BEGIN

SELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @mAcct_Num2
AND C.ACTIVITY_DATE >= @mActivity_Date2
AND C.ACTIVITY_DATE < @mActivity_Date2 + 15
----CHECK----------------------------------------------------------------------
--DECLARE @PrintVariable3 VARCHAR (8000)
--SELECT @PrintVariable3 =''Greater=30 DOLLARS COLLECTED''
--PRINT @PrintVariable3
----END
CHECK------------------------------------------------------------
END
ELSE IF @mDPD < 30
BEGIN

SELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))
FROM vwAplicablePayments C
WHERE C.ACCT_NUM = @mAcct_Num2
AND C.ACTIVITY_DATE >= @mActivity_Date2
AND C.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND
@mPromise_Date + 5
----CHECK--------------------------------------------------------------------
--DECLARE @PrintVariable4 VARCHAR (8000)
--SELECT @PrintVariable4 =''Less 30 DOLLARS COLLECTED''
--PRINT @PrintVariable4
----END CHECK------------------------------------------------------------
END
----------------------------------------MY REVISED
LOGIC-------------------------------------------------------
IF @rec_upd = ''N''
BEGIN

IF @mDPD >= 30
BEGIN

SELECT @mActivityDate2_temp = @mActivity_Date2 + 15

--DECLARE @PrintVariable5 VARCHAR (8000)
--SELECT @PrintVariable5 ='' GREATER= 30 USING ACTVITY_DATE+15''
--PRINT @PrintVariable5

END
ELSE IF @mDPD < 30
BEGIN
SELECT @mActivityDate2_temp = @mPromise_Date + 5

--DECLARE @PrintVariable6 VARCHAR (8000)
--SELECT @PrintVariable6 ='' LESS 30 USING PROMISE_DATE+5''
--PRINT @PrintVariable6

END
IF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amt
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = ''PK'',
TOTAL_DOLLARS_COLL = @tot_pay
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into PK.
IF @mPromise_Status IN (''PTP'',''OP'')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END

SELECT @rec_upd = ''Y ''
END
IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)
AND( @mActivityDate2_temp > @todays_date )--need to put 1day
of month here for snapshot9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = ''OP''
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into OP which is the original Activity Date.
--The record will hold this date until it goes into PK,PB,or
IP.
IF @mPromise_Status IN (''PTP'',''OP'')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @mActivity_Date2
WHERE CURRENT OF ACT_CUR2
END

END
ELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS
NULL)
AND( @mActivityDate2_temp <= @todays_date )--need to put 1day
of month here for snapshot 9/01/2004
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = ''PB'',
TOTAL_DOLLARS_COLL = case when @tot_pay is null
then 0 else @tot_pay end
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into PB.
IF @mPromise_Status IN (''PTP'',''OP'')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END
END
END
ELSE IF @rec_upd = ''Y''
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET PROMISE_STATUS = ''IP'',
TOTAL_DOLLARS_COLL = 0
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into IP.
IF @mPromise_Status NOT IN (''IP'')
BEGIN
UPDATE VWAPPLICABLEPROMISEACTIVITYRECORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END

END
FETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2
,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_Date
END
CLOSE ACT_CUR2
DEALLOCATE ACT_CUR2

FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1
END
CLOSE ACT_CUR1
DEALLOCATE ACT_CUR1

SET NOCOUNT OFF

END
GO

推荐答案

如果可能的话，我认为你应该考虑使用更快的类型

的游标。仅限前进例如

Without getting into code, I think you should consider using faster type
of cursor if possible. FORWARD ONLY for example

如果您是初学者，请避免使用游标。对于SQL中的问题，它们很少是必要的或者很好的解决方案。如果你在编写好的基于set的SQL之前用游标搞乱了
，你将学习不好的做法。


看起来你看起来不像代码中光标

的任何内容都有意义，所以我建议你从头开始。如果你需要帮助，

尝试将问题减少到它的基本功能，然后重新发布

及更多信息（DDL，一些样本数据，所需结果）。这篇

文章解释了如何最好地寻求帮助：
http://www.aspfaq.com/etiquette.asp?id=5006

-

David Portas

SQL Server MVP

-

If you are a beginner then avoid cursors. They are rarely a necessary or
good solution to a problem in SQL. You will learn bad practices if you mess
with cursors before you can write good set-based SQL.

It doesn''t look like you are doing anything in your code for which a cursor
makes sense so I recommend you start again from scratch. If you need help,
try to pare the problem down to it''s essential features and then post again
with more information (DDL, some sample data, required results). This
article explains how best to ask for help:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

>>我是初学者，所以我希望有更好的人可以提供帮助。 <


你所做的一切都是错误的。


1）数据元素名称违反了ISO-11179标准。

VARCHAR（1）是什么意思？


2）你永远不应该在应用程序中写一个游标。这是

实用程序。


3）您使用FLOAT和MONEY作为货币金额。并且你得到了
CAST（）语法错误。


4）与3GL语言不同，SQL具有时态数据类型。你为什么要这样做？b $ b避免他们支持COBOL风格的字符串处理？你甚至将当前时间戳加载到一个字符串中

！

>> I am beginner at best so I hope someone that is better can help. <<

Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!

问题是这个程序花费的时间越来越长
运行。现在长达5个小时！它正在分析大约30,000条记录。我认为

部分是因为我们每个月都会添加新记录[原文如此]。 <<


编号你编写的基本算法是COBOL磁带文件合并。

30K行很小。

第一个Cursor [磁带文件]存储一个唯一的帐户和
duedate组合。 <<


视图名称有一个愚蠢的vw_前缀违反ISO-11179。并且

表没有记录！行不是记录！在你了解

之前，你永远不会写出好的SQL。表是一个集合，在SQL中我们处理整个集合中的
数据，而不是单个记录。

然后它查找视图中具有该帐户的所有行为
duedate combo ..<<


不，你写的查询找到（account_nbr，duedate）

..和将它们加载到Cursor 2中，将它们组合在一起以获取数据

The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.
The first Cursor [magnetic tape file] stores a unique account and duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.
It then finds all the accts in the view that have that account duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)
.. and loads them into Cursor 2 this groups them together for data

操作。帐户必须以这种方式分组，因为

帐户可以在每个

帐户到期日期组合中有不同的截止日期和多个记录，他们需要以这种方式查看as

一些奇异的群体。 <


想想短语单一组一分钟:) GROUP BY是用来构建分组表的
。


你似乎要做的是更新一个承诺表。那个

应该是一个UPDATE语句。声明的骨架将是这样的：


CREATE PROCEDURE PromiseStatus（..）

AS

UPDATE APPLablePromises

SET status_date = CURRENT_TIMESTAMP，

promise_status = CASE WHEN ..END，

total_dollars_coll

=（SELECT SUM（P.pay_amt），

FROM ApplicablePromises AS A，Applicablepayments AS P

WHERE P.acct_num = A.acct_num

ANDAppablePromises.acct_num = A.account_num

ANDAppablePromises.due_date = A.due_date

AND P.activity_date BETWEEN ...

AND GROUP BY A.acct_num，A.due_date）

WHERE ...;


将所有IF_THEN_ELSE逻辑放入CASE表达式。你把总数

加到标量子查询中。你使用临时函数来获得当前时间戳。


一次不要考虑一列。这就是你如何处理记录中的

字段。表格由行组成，您可以将行

视为工作单元。

manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromises
SET status_date = CURRENT_TIMESTAMP,
promise_status = CASE WHEN ..END,
total_dollars_coll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromises AS A, Applicablepayments AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromises.acct_num = A.account_num
AND ApplicablePromises.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.

这篇关于帮助基于光标的存储过程变得越来越慢！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！