missprocedure.sql

oracle 全文检索的示例代码. 实现基于分区表的全文检索实现

			v_err	:=SQLCODE;
			v_eMsg	:=substr(sqlerrm,1,200);
			insert into proclog values('pro_SyncIndex',sysdate,v_err,v_emsg);
			commit;

END;
/

create or replace procedure pro_delPart(v_tName in  varchar2,v_timeLen in int default 0)
AS
/*
* desc: 定期删除过期数据
*
* date:2006-9-29 13:57
*
* auth: 郑继东
*/

v_pCondition     varchar2(30)    := null;
v_sql           varchar2(100)    ;
c_date          varchar2(10);
v_err 	varchar2(20);
v_emsg 	varchar2(201);
v_i     integer     :=0 ;
begin
     if v_timeLen = 0 then
        select count(*) into v_i from t_vast_time ;
        if v_i > 0 then
            select RES_SET_TIME into v_i from t_vast_time where rownum < 2;
        else
            v_i :=90;
        end if;
     else
        v_i := v_timeLen;
     end if;
     
     v_pCondition :=fun_getPartName(v_tName,SYSDATE-v_i);
     v_sql :='alter table '||v_tName||' drop partition '||v_pCondition;
    --execute immediate v_sql;

	exception
		when others then
			v_err	:=SQLCODE;
			v_eMsg	:=substr(sqlerrm,1,200);
			insert into proclog values('pro_delPart',sysdate,v_err,v_emsg);
			commit;

END;
/



create or replace procedure pro_AntoDel
AS
cursor cur is select RES_MIN_TIME,TNAME from t_vast_time;
v_i integer :=0;
v_name  varchar2(30) :=null;
v_tableName varchar2(30) :=null;
begin
    /*
    pro_delPart('t_http_content');
    pro_delPart('t_wap_content');
    pro_delPart('t_email_content');
    pro_delPart('t_telnet_content');
    pro_delPart('T_http');
    pro_delPart('T_ims');
    pro_delPart('T_telnet');
    pro_delPart('T_ftp');
    pro_delPart('T_wap');
    pro_delPart('T_email');    
    */
    for v_cur in cur loop
        v_name :=v_cur.TNAME;
        while (length(v_name) > 0 ) loop        
            begin 
                if (instr(v_name,',')) > 0 then 
                    v_tableName :=substr(v_name,0,instr(v_name,',') -1 );
                    v_name :=substr(v_name,instr(v_name,',')+1);
                else
                    v_tableName :=substr(v_name,0);
                    v_name :=null;
                end if;
                pro_delPart(v_tableName,v_cur.RES_MIN_TIME);
                
            end;
        end loop;
            
    end loop;
END;
/


CREATE OR REPLACE procedure PRO_GATHER_TABLE_INFO(V_OWN VARCHAR2,V_NAME VARCHAR2,v_date date default null)
AS 
v_err 	varchar2(20);
v_emsg 	varchar2(401);
BEGIN       
     
     dbms_stats.gather_table_stats(ownname=> V_OWN, tabname=> V_NAME, partname=> fun_getPartName(V_NAME,nvl(v_date,sysdate-2)));
exception
	when others then
	v_err	:=SQLCODE;
	v_eMsg	:=substr(sqlerrm,1,400);
	insert into proclog values('PRO_GATHER_TABLE_INFO',sysdate,v_err,v_emsg);
	commit;
END;
/






prompt
prompt Creating procedure PRO_DISPATCH
prompt ===============================
prompt
create or replace procedure pro_dispatch
as
errm		varchar2(2000);
/*
* desc: job 调度过程
*
*
* date:2006-4-14 13:23
*
*
* auth: 郑继东
*/

v_date 	date ;
v_min	int	;
v_err 	varchar2(20);
v_emsg 	varchar2(201);
p_name  varchar2(100);
begin
	v_date :=sysdate;
	select to_number(to_char(v_date,'mi')) into v_min from dual;

	if (v_min <55) or (v_min = 0) then
	    
		if to_number(to_char(v_date,'hh24')) = 3  then
		    pro_AutoAddPart();
		end if;

		if to_number(to_char(v_date,'hh24')) = 5  then		
		    /*
		    dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_HTTP_CONTENT$I', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_HTTP_CONTENT$K', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_HTTP_CONTENT$N', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_HTTP_CONTENT$R', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_WAP_CONTENT$I', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_WAP_CONTENT$K', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_WAP_CONTENT$N', partname=> NULL);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'DR$IDX_P_WAP_CONTENT$R', partname=> NULL);
            dbms_stats.gather_index_stats(ownname=> 'MISS', indname=> 'DR$IDX_P_HTTP_CONTENT$X', partname=> NULL);
	        dbms_stats.gather_index_stats(ownname=> 'MISS', indname=> 'DR$IDX_P_WAP_CONTENT$X', partname=> NULL);
            */
            
            /*
            p_name :=fun_getPartName('T_HTTP',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_HTTP', partname=> p_name);
            p_name :=fun_getPartName('T_ims',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_ims', partname=> p_name);
            p_name :=fun_getPartName('T_telnet',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_telnet', partname=> p_name);
            p_name :=fun_getPartName('T_ftp',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_ftp', partname=> p_name);
            p_name :=fun_getPartName('T_wap',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_WAP', partname=> p_name);            
            p_name :=fun_getPartName('T_email',sysdate-1);
            dbms_stats.gather_table_stats(ownname=> 'MISS', tabname=> 'T_email', partname=> p_name);
            */
                      
            
            PRO_GATHER_TABLE_INFO('miss','T_HTTP_CONTENT');
            PRO_GATHER_TABLE_INFO('miss','T_WAP_CONTENT');
            PRO_GATHER_TABLE_INFO('miss','T_TELNET_CONTENT');
            PRO_GATHER_TABLE_INFO('miss','T_EMAIL_CONTENT');            
            
            /*删除过期的数据*/
            pro_AntoDel();
            
    	end if;
	end if;

	/*更新系统统计信息*/




exception
	when others then
	v_err	:=SQLCODE;
	v_eMsg	:=substr(sqlerrm,1,200);
	insert into proclog values('pro_dispatch',sysdate,v_err,v_emsg);
	commit;
end;
/

create or replace procedure pro_AutoAddPart_test(v_date in date)
AS
/*
* desc: 定期给协议表增加分区
*
* date:2006-9-29 13:57
*
* auth: 郑继东
*/

v_pCondition     varchar2(30)    := null;

v_err 	varchar2(20);
v_emsg 	varchar2(201);
begin

        
    v_pCondition :=to_char(v_date+2,'yyyymmdd')||'00000000000';       

    pro_AddPartition(v_date,'t_http_content',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_EMAIL_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_TELNET_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_WAP_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_http',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_ims',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_telnet',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_ftp',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_wap',v_pCondition,null,'miss');
    pro_AddPartition(v_date,'T_email',v_pCondition,null,'miss');


	exception
		when others then
			v_err	:=SQLCODE;
			v_eMsg	:=substr(sqlerrm,1,200);
			insert into proclog values('pro_AutoAddPart_test',sysdate,v_err,v_emsg);
			commit;

END;
/
alter function f_count compile;

alter function fun_getPartName compile;

alter function fun_QUERY compile;

alter procedure pro_AutoAddPart_test compile;

alter procedure pro_AutoAddPart compile;

alter procedure pro_SyncIndex compile;

alter procedure pro_SyncIndexRecent compile;

alter procedure pro_delPart compile;

alter procedure pro_AntoDel compile;

alter procedure  PRO_GATHER_TABLE_INFO compile;

alter procedure pro_dispatch compile;

alter procedure pro_AutoAddPart_test compile;

exit;