从一个表中提取数据...操纵该数据，然后发送到另一个表

Attempting to have the output of this code fill a database from the results of an IF LOOP pulling data from a separate database. Here's what I have so far... I know it's off.

尝试使此代码的输出从IF LOOP的结果填充数据库,从单独的数据库中提取数据。这就是我到目前为止......我知道它已经关闭了。

SET SERVEROUTPUT ON
SET VERIFY      OFF

DECLARE
    v_idno          donorbackup.idno%TYPE :=&input_idno;
    v_yrgoal        donorbackup.yrgoal%TYPE;
    v_newgoal       donorbackup.yrgoal%TYPE;
BEGIN
    SELECT yrgoal INTO v_yrgoal
    FROM   donorbackup
    WHERE  idno = v_idno;

    IF v_yrgoal > 500 THEN
        v_newgoal :=  v_yrgoal * 2 ;
    ELSE
        v_newgoal := v_yrgoal * 1.5;
    END IF;

    UPDATE amttopay <-- Would I even be updating this table like this?
    SET    amt = v_newgoal
    WHERE  idno = v_idno;
END;
/

SET SERVEROUTPUT OFF
SET VERIFY ON

I'm trying to take the output from the IF and breaking it into 3 separate results. Not even sure how to begin.

我试图从IF获取输出并将其分成3个单独的结果。甚至不确定如何开始。

It should look like this at the end:

它应该在最后看起来像这样:

REC_NO    IDNO     YRGOAL
001       11111    333.333
002       11111    333.333
003       11111    333.333

That output would be the result of a user entering '11111' at the prompt, it then matches with data from another table and (in this case) finds the yrgoal is equal to 500, so it doubles it. Next it would insert that information 3x with the same IDNO, yrgoal divided by 3. and I'm assuming the rec_no would just be sequenced as to count the outputs.

该输出将是用户在提示符处输入“11111”的结果,然后它与来自另一个表的数据匹配并且(在这种情况下)发现yrgoal等于500,因此它将其加倍。接下来它将使用相同的IDNO插入该信息3x,yrgoal除以3.并且我假设rec_no将被排序为计数输出。

Here is my updated code, been trying to figure this out for 2 days now.

这是我的更新代码,现在试图解决这个问题2天。

SET SERVEROUTPUT ON
SET VERIFY      OFF
ACCEPT in_idno PROMPT 'Enter desired IDNO: ';

DECLARE
    v_idno          donorbackup.idno%TYPE :='&in_idno';
    v_yrgoal        donorbackup.yrgoal%TYPE;
    v_newgoal       donorbackup.yrgoal%TYPE;
BEGIN
    SELECT idno INTO v_idno
    FROM   donorbackup
    WHERE  idno = v_idno;

    IF v_yrgoal > 500 THEN
        v_newgoal :=  v_yrgoal * 2 ;
        INSERT INTO amttopay
        VALUES ('&in_idno', v_newgoal);
    ELSE
        v_newgoal := v_yrgoal * 1.5 ;
        INSERT INTO amttopay
        VALUES ('&in_idno', v_newgoal);
    END IF;
END;
/

SET SERVEROUTPUT OFF
SET VERIFY ON

2 个解决方案

#1

So basically you're calculating an annual value (v_newgoal) then creating a periodic value for each period. In this case #periods = 3. Thus each row has value of v_newgoal/3. You almost have a workable solution. You just need to adjust the periodic value then insert n rows. But I would bring the insert outside of the if statement, so:

所以基本上你计算年度值(v_newgoal)然后为每个时期创建一个周期值。在这种情况下,#periods = 3.因此每行的值为v_newgoal / 3。你几乎有一个可行的解决方案。您只需调整周期值然后插入n行。但是我会把插件带到if语句之外,所以:

IF v_yrgoal > 500 THEN
   v_newgoal := (v_yrgoal * 2) / 3 ;
ELSE
   v_newgoal := (v_yrgoal * 1.5) / 3 ;
END IF ; 

FOR i in 1 .. 3 LOOP
    INSERT INTO amttopay
    VALUES('&in_idno', v_newgoal);
END LOOP;

This is not the best implementation, but it seems to match your stated requirement.

这不是最好的实现,但它似乎符合您的规定要求。

BTW: Check out the FOR construct.

BTW:查看FOR构造。

#2

You can do this in plain SQL:

您可以在纯SQL中执行此操作:

insert into amttopay
     ( idno, seq, goal )
select idno
     , amttopay_seq.nextval
     , yrgoal * case when yrgoal > 500 then 2 else 1.5 end / 3 as goal
from   donorbackup
       cross join (select dummy from dual connect by rownum <= 3) rowtrebler
where  idno = &donor_id;

The only fancy part is the rowtrebler inline view that's just there to generate three dummy rows, so that when I cross join it to donorbackup I get the result three times instead of once.

唯一奇特的部分是rowtrebler内联视图,它就是在那里产生三个虚拟行,所以当我将它连接到donorbackup时,我得到结果三次而不是一次。

I suppose you could avoid hardcoding the 3 in two places to make it a bit more robust in case you ever needed to change it, though maybe this is overkill:

我想你可以避免在两个地方对3进行硬编码以使其更加强大,以防你需要更改它,尽管这可能是过度杀伤:

insert into amttopay
     ( idno, seq, goal )
select idno
     , amttopay_seq.nextval
     , yrgoal * case when yrgoal > 500 then 2 else 1.5 end / split.numrows as goal
from   donorbackup d
       cross join (select count(*) over() as numrows from dual connect by rownum <= 5) split
where  idno = &donor_id;

#1

IF v_yrgoal > 500 THEN
   v_newgoal := (v_yrgoal * 2) / 3 ;
ELSE
   v_newgoal := (v_yrgoal * 1.5) / 3 ;
END IF ; 

FOR i in 1 .. 3 LOOP
    INSERT INTO amttopay
    VALUES('&in_idno', v_newgoal);
END LOOP;