create or replace procedure prepare.prc_truncate_tab

(P_TABLE_NAME in varchar2, --Òªtruncate?íµÄ?íÃû??ÓÃ??Ãû.?íÃû??

     p_ok OUT varchar2, --?éÑ?ÊÇ?ñ?É??

     p_delete_day in number DEFAULT 60 --??60ÌìÒÔÇ??òÊäÈë?ÎÊýÒÔÇ?µÄËùÓÐÊý?ÝÉ??ý )

as

    --****************************************************************

    *********************

    --author: Ô?Ã?

    --create date :2002.01.09

    --chinese name:?Ô?íÊý?Ý?øÐÐ?ÜÀí,ÊÍ?Å?ÕÏÐ?Õ?ä --Description :ÓÃÓÚ?éÒ???Êý?ÝÁÙÊ??íµÄ??Àí --update date:

    --memo:

    --****************************************************************

    **********************

    V_SQL_ins varchar2(4000);

    V_SQL_ins2 varchar2(4000);

    V_SQL_trun varchar2(4000);

    V_SQL_trun2 varchar2(4000);

    V_SQL_LOCK varchar2(4000);

    V_SQL_CRT_TAB varchar2(4000);

    V_SQL_SELECT varchar2(4000);

    V_SQL_DELETE varchar2(4000);

    V_SQL_DROP varchar2(4000);

    v_table_name varchar2(50);

    v_table_name1 varchar2(50);

    v_a number(10);

    V_delete_day number(10);

    TYPE t_cursor IS REF CURSOR;

    V_Cursor_tab t_cursor;

begin

IF p_delete_day IS NULL THEN

     V_delete_day:=60;

    ELSE

     V_delete_day:=p_delete_day;

    END IF ;

    v_table_name:=upper(trim(P_TABLE_NAME));

    v_table_name1:=SUBSTR(V_table_name,1,INSTR(V_table_name,'.'))||'T_'||SUBSTR(V_table_name,INSTR(V_table_name,'.')+1); V_SQL_LOCK:='LOCK TABLE '||v_table_name1||' IN EXCLUSIVE MODE'; V_SQL_DELETE:='DELETE FROM '||v_table_name||' WHERE GENTIME<(SYSDATE-'||V_delete_day||')';

    V_SQL_ins:='insert into '||v_table_name1||' select * from '||v_table_name;

    V_SQL_trun:='truncate table '||v_table_name;

    V_SQL_ins2:='insert into '||v_table_name||' select * from '||v_table_name1;

    V_SQL_trun2:='truncate table '||v_table_name1;

    V_SQL_SELECT:='SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME= '''||SUBSTR(V_table_name1,INSTR(V_table_name1,'.')+1)||''''; V_SQL_DROP:='drop table '||v_table_name1;

    --V_SQL_SELECT:='SELECT COUNT(*) FROM DBA_TABLES WHERE TABLE_NAME= '''||SUBSTR(V_table_name1,INSTR(V_table_name1,'.')+1)||''' AND OWNER='''||SUBSTR(V_table_name1,1,INSTR(V_table_name1,'.')-1)||'''';

    --dbms_output.put_line(V_SQL_SELECT);

OPEN v_cursor_tab FOR V_SQL_SELECT;

    fetch v_cursor_tab into v_a;

    IF V_A=0 THEN

     V_SQL_CRT_TAB:='CREATE TABLE '||v_table_name1||' TABLESPACE TEMPDATA AS SELECT * FROM '||v_table_name||' WHERE 1=2 '; -- dbms_output.put_line(V_SQL_CRT_TAB);

     EXECUTE IMMEDIATE V_SQL_CRT_TAB;

    END IF;

--dbms_output.put_line(V_SQL_LOCK);

     EXECUTE IMMEDIATE V_SQL_LOCK;

    --dbms_output.put_line(V_SQL_DELETE);

    IF p_delete_day IS NOT NULL THEN

     EXECUTE IMMEDIATE V_SQL_DELETE;

    END IF;

--dbms_output.put_line(V_SQL_trun2);

     EXECUTE IMMEDIATE V_SQL_trun2;

    --dbms_output.put_line(V_SQL_ins);

     EXECUTE IMMEDIATE V_SQL_ins; --USING OUT P_NE_ALL,OUT P_NE_PART,V_TIME_COUNT,v_gentime1,v_gentime2,v_gentime3; --,V_TABLE_PK_LIST

    --dbms_output.put_line(V_SQL_trun);

     EXECUTE IMMEDIATE V_SQL_trun;

    --dbms_output.put_line(V_SQL_ins2);

     EXECUTE IMMEDIATE V_SQL_ins2; --dbms_output.put_line(V_SQL_trun2);

     EXECUTE IMMEDIATE V_SQL_trun2; --dbms_output.put_line(V_SQL_drop);

     EXECUTE IMMEDIATE V_SQL_DROP;

     p_ok:='OK';

     COMMIT;

    exception

     when others then

     p_ok:='F';

     ROLLBACK;

    END;