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上，我有一个带有几列(id,name_tab, prefix_tab, created_on , keep_days, max_rows_bck)的table_names列表
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的FK
close_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 ;
在我的delete语句上，我对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屋！
