方法:oracle 的动态SQL, 一般的PL/SQL程序设计中,在DML和事务控制的语句中可以直接使用SQL,但是DDL语句及系统控制语句却不能在PL/SQL中直接使用,要想实现在PL/SQL中使用DDL语句及系统控制语句,可以通过使用动态SQL来实现。
如何把下数的业务用PL/SQL实现?
1.业务操作流水表ODS_PRODUCT_SEQUENCE_DM_434
操作日期:每月1日上午即可
操作方式:按标号顺序在一个ORACLE SQL*PLUS中顺序执行
(1)备份
conn boss/******8@jyfx36;
select count(*) from jlcrm.ods_product_sequence_dm_434
where start_datetime>=to_date('20090201','yyyymmdd')
and start_datetime<to_date('20090301','yyyymmdd');
conn boss/******@sp87;
select count(*) from yxf_oper_200902
where start_datetime>=to_date('20090201','yyyymmdd')
and start_datetime<to_date('20090301','yyyymmdd');
如上述两个执行结果相等,备份成功,否则,重新查找原因。
解决办法:
CREATE OR REPLACE PROCEDURE "SCOTT"."BACKDATA" (
vdate in varchar2
) as
str_sql1 varchar2(500);
str_sql2 varchar2(150);
str_sql3 varchar(350);
str_sql4 varchar2(450);
str_sql5 varchar(450);
cnt int;
tablename varchar(20):='yxf_oper_'||vdate;
begin
select count(*) into cnt from cat where table_name=upper(trim(tablename));
if cnt > 0 then
str_sql2:='drop table ' ||tablename ;
execute immediate str_sql2 ;
end if;
str_sql1:= 'create table ' || tablename || ' as select * from yxf_oper_mode' ;
execute immediate str_sql1 ;
str_sql3:= 'insert into yxf_oper_'|| vdate || '(select * from yxf_oper_'|| vdate || 'tmp where start_datetime>=to_date('||''''|| vdate || '01' ||''''||',' || '''yyyymmdd''' || ')'||' and start_datetime<=to_date('||''''||vdate ||'31'||'''' ||',' || '''yyyymmdd'''||'))' ;
-- execute immediate str_sql3 ;
--execute immediate 'commit';
str_sql4:='create index inde_yxf_oper_' || vdate|| ' on yxf_oper_' || vdate || ' (phone_no)' ;
-- execute immediate str_sql4 ;
str_sql5:='create index inde_yxf_oper_1'||vdate||' on yxf_oper_'||vdate||' (id_no)';
-- execute immediate str_sql5 ;
DBMS_OUTPUT.PUT_LINE(str_sql1);
-- DBMS_OUTPUT.PUT_LINE(str_sql2);
DBMS_OUTPUT.PUT_LINE(str_sql3);
DBMS_OUTPUT.PUT_LINE(str_sql4);
DBMS_OUTPUT.PUT_LINE(str_sql5);
exception
when others then
DBMS_OUTPUT.PUT_LINE('error');
end ;