关于存储过程 数据库备份
给位你好,最近在工作中要使用一个存储过程用来数据库备份.由于本人是刚刚接触存储过程,所以想问一下给位下面的存储过程是否正确:
create or replace procedure trideArchiveProcedure(
expiryDate in varchar2
)as
dateschema varchar2(20);
begin
dateschema varchar2(20);
begin
select key_value_ into dateschema from APP_PARAMS where key_name_='archive_schema_name';
INSERT INTO dateschema.fnd_subscribe SELECT * FROM fnd_subscribe WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_subscribe WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_purchase SELECT * FROM fnd_purchase WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_purchase WHERE trade_date_ <= expiryDate ;
INSERT INTO dateschema.fnd_redeem_list SELECT a.* FROM fnd_redeem_list a, fnd_redeem b WHERE a.oapp_sno_ = b.app_sno_ AND b.trade_date_ <= expiryDate;
DELETE FROM fnd_redeem_list WHERE oapp_sno_ IN (SELECT app_sno_ FROM fnd_redeem WHERE trade_date_ <= expiryDate);
INSERT INTO dateschema.fnd_redeem SELECT * FROM fnd_redeem WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_redeem WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_redeem_list SELECT a.* FROM fnd_redeem_list a, fnd_convert b WHERE a.oapp_sno_ = b.app_sno_ AND b.trade_date_ <= expiryDate;
DELETE FROM fnd_redeem_list WHERE oapp_sno_ IN (SELECT app_sno_ FROM fnd_convert WHERE trade_date_ <= expiryDate);
INSERT INTO dateschema.fnd_convert SELECT * FROM fnd_convert WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_convert WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_redeem_list SELECT a.* FROM fnd_redeem_list a, fnd_escrow b WHERE a.oapp_sno_ = b.app_sno_ AND b.trade_date_ <= expiryDate;
DELETE FROM fnd_redeem_list WHERE oapp_sno_ IN (SELECT app_sno_ FROM fnd_escrow WHERE trade_date_ <= expiryDate);
INSERT INTO dateschema.fnd_escrow SELECT * FROM fnd_escrow WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_escrow WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_recalldeal SELECT * FROM fnd_recalldeal WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_recalldeal WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_erasepurchase SELECT * FROM fnd_erasepurchase WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_erasepurchase WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_periodic_subs SELECT * FROM fnd_periodic_subs WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_periodic_subs WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_periodic_change SELECT * FROM fnd_periodic_change WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_periodic_change WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_riskaudit SELECT * FROM fnd_riskaudit WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_riskaudit WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.fnd_settinginvest SELECT * FROM fnd_settinginvest WHERE trade_date_ <= expiryDate;
DELETE FROM fnd_settinginvest WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.acc_dealdetail SELECT * FROM acc_dealdetail WHERE trade_date_ <= expiryDate;
DELETE FROM acc_dealdetail WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.acc_faildetail SELECT * FROM acc_faildetail WHERE trade_date_ <= expiryDate;
DELETE FROM acc_faildetail WHERE trade_date_ <= expiryDate;
INSERT INTO dateschema.acc_divcash SELECT * FROM acc_divcash WHERE confirm_date_ < expiryDate;
DELETE FROM acc_divcash WHERE confirm_date_ < expiryDate;
INSERT INTO dateschema.acc_forceadjust SELECT * FROM acc_forceadjust WHERE confirm_date_ < expiryDate;
DELETE FROM acc_forceadjust WHERE confirm_date_ < expiryDate;
end trideArchiveProcedure;
第一个select是在现在使用的数据库的系统参数表中查找到要备份的数据库名称.查询出来以后再将这个值作为表的前缀.语法这一块能过关吗?可以这样使用查询出来的数据库名称么?