insert into table1 select * from another table2，then delete the row from table2

问题描述：

我试图插入行到table1中select * from table2，最后使用oracle sql开发者中的函数删除table2中的行。我没有得到如何去做。insert into table1 select * from another table2，then delete the row from table2

follwing是我的函数：

create or replace FUNCTION DELETEROW(GODOWN_DIST_CODE IN 
TEST_TABLE.GODOWN_DIST_CODE%TYPE) return varchar2 
AS 
returned_value varchar2(50); 
BEGIN 
begin 
INSERT INTO TEST_TABLE_BACKUP SELECT * FROM TEST_TABLE WHERE 
GODOWN_DIST_CODE=GODOWN_DIST_CODE; 
DELETE TEST_TABLE WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

INSERT INTO NEW_TABLE_BACKUP SELECT * FROM NEW_TABLE WHERE GODOWN_DIST_CODE 
= GODOWN_DIST_CODE; 
DELETE FROM NEW_TABLE WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

exception 
WHEN no_data_found THEN 
     returned_value := '#'; 
end; 
RETURN returned_value;  
END;

显示你曾试图迄今未工作或提供错误的结果观众。 – XING

你不能在'PROCEDURE'里面使用'RETURN'。如果你想返回一些东西，你需要创建'FUNCTION'。 'FUNCTION'和'PROCEDURE'是两个不同的东西。 –

@XING，我已经创建了上面的函数，当我执行函数时，所有的行都被插入到TEST_TABLE_BACKUP和NEW_TABLE_BACKUP中，但是我传递了一些值作为参数，并将其用作where条件，甚至所有记录都被删除。 –

答

根据我们的讨论中，我提出这个解决方案为您服务。即使我没有发现任何需要有FUNCTION上午显示使用FUNCTION exmaple。此外，我已将您的删除逻辑写入Procedure，并且取决于删除语句的成功，将Return值置于变量var中并作为OUT paramater发送，您可以使用该值。

功能：

CREATE OR REPLACE FUNCTION confirm_ckh 
    RETURN VARCHAR 
AS 
BEGIN 
    RETURN ('#'); 
END;

步骤：

CREATE OR REPLACE PROCEDURE DELETEROW (
    GODOWN_DIST_CODE IN TEST_TABLE.GODOWN_DIST_CODE%TYPE, 
    var OUT varchar2) <-- Out parameter to get the return value 
AS 

BEGIN 
    BEGIN 
     INSERT INTO TEST_TABLE_BACKUP 
     SELECT * 
      FROM TEST_TABLE 
      WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

     IF SQL%ROWCOUNT > 0 
     THEN 
     DBMS_OUTPUT.put_line ('Insert Success TEST_TABLE_BACKUP'); 
     var:=confirm_ckh; 
     END IF; 

     DELETE TEST_TABLE 
     WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

     IF SQL%ROWCOUNT > 0 
     THEN 
     DBMS_OUTPUT.put_line ('Deleted records from TEST_TABLE '); 
     var:=confirm_ckh; 
     END IF; 


     INSERT INTO NEW_TABLE_BACKUP 
     SELECT * 
      FROM NEW_TABLE 
      WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

     IF SQL%ROWCOUNT > 0 
     THEN 
     DBMS_OUTPUT.put_line ('Insert Success for NEW_TABLE_BACKUP'); 
     var:=confirm_ckh; 
     END IF; 

     DELETE FROM NEW_TABLE 
      WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE; 

     IF SQL%ROWCOUNT > 0 
     THEN 
     DBMS_OUTPUT.put_line ('Deleted records from NEW_TABLE '); 
     var:=confirm_ckh; 
     END IF; 
    EXCEPTION 
     WHEN NO_DATA_FOUND 
     THEN 
     null; 
    END; 
END;

答

试试这个： -

CREATE OR REPLACE PROCEDURE TABLE2_TO_TABLE1 
    IS 
    BEGIN 
    EXECUTE IMMEDIATE 'INSERT INTO TABLE1(
       Field1, 
       Field2, 
       Field3 
      ) 
      SELECT Field1, 
        Field2, 
        Field3 
        FROM TABLE2'; 
    EXECUTE IMMEDIATE 'DELETE TABLE2 where rownum != 0'; 
END TABLE2_TO_TABLE1;

执行过程

BEGIN 
    TABLE2_TO_TABLE1; 
END; 
/

insert into table1 select * from another table2，then delete the row from table2

相关推荐