Oracle 插入 table2 然后从 table1 删除，如果失败则异常 [英] Oracle insert into table2 then delete from table1, exception if fail
问题描述
创建一个过程，将我的 table1 中的数据插入到我的 table2 中并在插入后将其删除，或者如果 table2 已达到其限制，那么我将创建一个新的备份表.
creating a procedure that will insert the data from my table1 into my table2 and delete it after insert, or in case the table2 has reached its limit then i will create a new backup table.
我有两个表 sii_bck_cfg_tab 和 sii_bck_tab
在 cfg_tab 上，我有一个 table_names 列表，其中包含几列 (id,name_tab, prefix_tab, created_on , keep_days, max_rows_bck)prefix_tab- 当我需要创建一个新的备份表时我得到前缀keep_days- 我想保留表数据的天数max_rows - 在我创建新表之前添加到备份表的行数.
On cfg_tab i have a list of table_names with a couple of columns (id,name_tab, prefix_tab, created_on , keep_days, max_rows_bck)
prefix_tab- where i get the prefix when i need to create a new backup table
keep_days- the number of days i want to keep the tables data
max_rows- the number of rows ill add to the backup table before i create a new one.
在我的 bck_tab 上，我有以下列 (id, tab_name, id_cfg_bck, created_on, close_dt)tab_name-创建的备份表的名称id_cfg_bck-来自 cfg_bck 的 FKclose_dt- 备份表达到其限制并关闭"的日期
on my bck_tab i have the following columns (id, tab_name, id_cfg_bck, created_on, close_dt)
tab_name-name of the created backup table
id_cfg_bck- FK from cfg_bck
close_dt- the date when the backup table reaches its limit and is "closed"
enter code here
CREATE OR REPLACE PROCEDURE LIMPAR_TAB_proc
IS
stmt VARCHAR2(1000);
stmt_ins_bck VARCHAR2(1000);
n_tab sii_bck_cfg_tab.nome_tab%type;
prefix sii_bck_cfg_tab.pref_tab_bck%type;
max_reg sii_bck_cfg_tab.max_reg_bck%type;
id_fk sii_bck_cfg_tab.id_bck_cfg_tab%type;
dt_criado sii_bck_cfg_tab.criado_em%TYPE;
id_fk2 sii_bck_tab.id_bck_cfg_tab%type;
dt_fec sii_bck_tab.dt_fecho%TYPE;
n_tab2 sii_bck_tab.nome_tab%type;
stmt_ins VARCHAR2(500);
id_seq VARCHAR2(500);
num_rows VARCHAR2(500);
stmt_up VARCHAR2(500);
stmt_del VARCHAR2(500);
CURSOR c1 IS
SELECT ID_BCK_CFG_TAB,Nome_tab, pref_tab_bck, max_reg_bck, criado_em FROM sii_bck_cfg_tab WHERE desativado_em IS NULL OR desativado_em<=SYSDATE AND n_dias_reten>0 ORDER BY criado_em;
CURSOR c2 IS
SELECT sii_bck_tab.ID_BCK_CFG_TAB , sii_bck_tab.nome_tab from sii_bck_tab,sii_bck_cfg_tab WHERE sii_bck_cfg_tab.id_bck_cfg_tab=sii_bck_tab.id_bck_cfg_tab and dt_fecho is NULL;
BEGIN
OPEN c1;
LOOP
--inserir os registos que cumprem a restrição da data e dos dias de retenção
FETCH c1 INTO id_fk,n_tab,prefix,max_reg, dt_criado;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nome Tabela c1 = ' || id_fk ||' '|| n_tab ||' '|| prefix ||' '|| max_reg);
OPEN c2;
LOOP
FETCH c2 INTO id_fk2, n_tab2;
--DBMS_OUTPUT.PUT_LINE('chave aqui c2= ' || id_fk2 || n_tab2);
--registos de tabelas que não estão fechadas
IF c2%FOUND AND id_fk=id_fk2 THEN
BEGIN
SAVEPOINT start_transaction;
stmt_ins:= 'INSERT all into ' || n_tab2 || ' SELECT * FROM ' || n_tab || ' where ' || dt_criado || '<' || SYSDATE;
--falta verificar se foi bem sucedido e apagar da original
-- EXECUTE IMMEDIATE stmt_ins;
dbms_output.put_line('value '||SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(stmt_ins);
siima_logs_bck.INSE_LOG(1,'TESTE INSERIR','INS_LOGS','2', 'testes insert log');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO start_transaction;
END;
stmt_del:= 'DELETE FROM ' || n_tab || ' WHERE (SELECT *
FROM ' || n_tab2 || ' WHERE criado_em' ||'<'|| sysdate ;
DBMS_OUTPUT.PUT_LINE(stmt_del);
siima_logs_bck.INSE_LOG(1,'TESTE ELIMINAR','INS_LOGS','2', 'testes delete log');
ELSE IF c2%NOTFOUND THEN
stmt:= 'create table ' || prefix || SII_BCK_TAB_ID_SEQ.nextval ||
' AS SELECT * FROM '|| n_tab || ' WHERE 1=0';
-- DBMS_OUTPUT.PUT_LINE(stmt);
--EXECUTE IMMEDIATE stmt;
id_seq:= prefix||SII_BCK_TAB_ID_SEQ.CURRVAL;
-- DBMS_OUTPUT.PUT_LINE(id_seq);
stmt_ins_bck:= 'insert into sii_bck_tab(nome_tab,id_bck_cfg_tab) VALUES ' || '(' || id_seq || ',' || id_fk || ')';
-- DBMS_OUTPUT.PUT_LINE(stmt_ins_bck);
--EXECUTE IMMEDIATE stmt_ins_bck;
EXIT WHEN c2%NOTFOUND;
END IF;
END IF;
--vefificar numero de registos e update
EXECUTE IMMEDIATE 'SELECT Count (*) from ' ||n_tab INTO num_rows;
-- DBMS_OUTPUT.PUT_LINE(num_rows);
IF num_rows>max_reg THEN
stmt_up:='update ' || n_tab || ' set ' || dt_fec || '=' || sysdate;
DBMS_OUTPUT.PUT_LINE(stmt_up);
--EXECUTE IMMEDIATE stmt_up;
END IF;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
end;
我目前遇到的问题是插入到动态表中，如果我已成功插入到备份表中，则删除.
what im having trouble at the moment is to insert into a dynamic table and then delete if i have successfully inserted into the backup table.
stmt_ins:= 'INSERT all into ' || n_tab2 || ' SELECT * FROM ' || n_tab || ' where ' || dt_criado || '<' || SYSDATE;
--这个 stmt 在没有 where 子句的情况下工作，但我需要它工作以便我可以按日期过滤数据.
--this stmt works without the where clause, but i need it to work so i can filter the data by date.
我想确保我已将数据正确插入到备份表中，然后从主表中删除.
i want to make sure i have inserted my data correctly into my backup table and then delete from my main table.
stmt_del:= 'DELETE FROM ' || n_tab || ' WHERE (SELECT *
FROM ' || n_tab2 || ' WHERE criado_em' ||'<'|| sysdate ;
在我的删除语句中，criado_em 列也有同样的问题.
on my delete statemente i have the same problem with the criado_em column.
这是一个将在作业中运行并每天将数据从主表传递到备份表的过程，并在主表上保留一定数量的数据(例如最近 30 天)
This is a procedure that will run in a job and pass data from main table onto backup tables daily, and keep a certain amount of data on the main table(like for example last 30 days)
感谢您的帮助
推荐答案
这个 stmt 在没有 where 子句的情况下工作
this stmt works without the where clause
那可能是因为当您连接字符串时，它们会被隐式转换，并且您的 where 条件在运行时失败.试试带有绑定变量的 USING 子句.
That could be because when you concatenate the strings they are converted implicitly and your where condition fails at run time. Try the USING clause with bind variables.
stmt_ins:= 'INSERT INTO ' || n_tab2 || ' SELECT * FROM ' || n_tab || ' where ' || dt_criado || ' < :dt ' ;
EXECUTE IMMEDIATE stmt_ins USING SYSDATE;
这篇关于Oracle 插入 table2 然后从 table1 删除，如果失败则异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
