missprocedure.sql

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

/***************************************************************************  
*	创建存储过程、函数
*                                                                             
*                                                                             
*	auth:jidongzheng                                                          
*	2005-1-31                                                                 
*	                                                                          
*
****************************************************************************/ 

create or replace function fun_getPartName(t_name in varchar2,v_date in date) return varchar2
as 
begin 
      return  'P'||upper(T_name)||upper(to_char(v_date,'yyyymmdd'));
end;
/

prompt
prompt Creating function FUN_QUERY
prompt ===========================
prompt
create or replace function fun_QUERY(v_idx in varchar2,v_query  in varchar2 ) return integer
AS
A NUMBER;
BEGIN
A:=CTX_QUERY.count_hits(v_idx,v_query,FALSE);
return a;
END;
/

prompt
prompt Creating function F_COUNT
prompt =========================
prompt
create or replace function f_count(v_idx        in varchar2,
                                   v_query      in varchar2,
                                   v_begin_time in number default 0,
                                   v_end_time   in number default 0)
  return integer AS
  A     NUMBER;
  V_CNT INTEGER;
  V_SUM INTEGER;  
  V_TABLE_NAME VARCHAR2(30) :=NULL;
  
  v_p_name varchar2(30) :=null ;
BEGIN
  A := CTX_QUERY.count_hits(v_idx, v_query, FALSE);
  IF  v_begin_time = 0 AND V_END_TIME = 0 then
      RETURN A;
  END IF;

  V_TABLE_NAME := case WHEN instr(upper(v_idx), 'HTTP', 1, 1) > 0 then 'T_HTTP_CONTENT' WHEN instr(upper(v_idx), 'WAP', 1, 1) > 0 then 'T_WAP_CONTENT' WHEN instr(upper(v_idx), 'TELNET', 1, 1) > 0 then 'T_TELNET_CONTENT' WHEN instr(upper(v_idx), 'EMAIL', 1, 1) > 0 then 'T_EMAIL_CONTENT' ELSE NULL END ;
  
  
  if v_begin_time > 0 then   
    v_p_name :='PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(to_char(to_date(substr(V_BEGIN_TIME,1,8),'yyyymmdd')+2,'yyyymmdd'), 1, 8);
  end if ;
  if v_end_time > 0 then   
    v_p_name :='PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(to_char(to_date(substr(V_end_TIME,1,8),'yyyymmdd')+2,'yyyymmdd'), 1, 8);
  end if ;
  if v_begin_time > 0 AND V_END_TIME = 0 then
    select SUM(num_rows)
      INTO V_CNT
      from user_tab_partitions
     where table_name = V_TABLE_NAME
     AND PARTITION_NAME >= 'PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(V_BEGIN_TIME, 1, 8);
  end if;
  if v_begin_time = 0 AND V_END_TIME > 0 then
    select SUM(num_rows)
      INTO V_CNT
      from user_tab_partitions
     where table_name = V_TABLE_NAME
     AND PARTITION_NAME <= 'PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(V_END_TIME, 1, 8);
  end if;

  if v_begin_time > 0 AND V_END_TIME > 0 then
    select SUM(num_rows)
      INTO V_CNT
      from user_tab_partitions
     where  table_name = V_TABLE_NAME AND PARTITION_NAME BETWEEN 'PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(V_BEGIN_TIME, 1, 8) 
     AND 'PT' || SUBSTR(UPPER(V_IDX), 6) || SUBSTR(V_END_TIME, 1, 8);
  end if;  
  
  select SUM(num_rows)
      INTO V_SUM
      from user_tab_partitions
     where table_name = V_TABLE_NAME;
  return nvl(round(a*(v_cnt/v_sum)),0);
  
END;
/







/*******************************************************************************************************************************************************/
create or replace procedure pro_AddPartition(v_date	in date,                      --创建分区时间
                                            v_tName in varchar2,                  --表名
                                            v_condition in varchar2,             --分区的条件
                                            v_pName in varchar2 default null,     ---分区名称
                                            v_tbsName in varchar2 default 'miss') ---分区所使用的表空间s
AS
/*
* desc: 定期给协议表增加分区
*
* date:2006-9-29 13:57
*
* auth: 郑继东
*/

v_partition_name	varchar2(30);
v_partition_value	date;
v_tbs_name			varchar2(20) := 'mipas';
c_date				varchar2(20) :='';
v_1					integer;
v_sql       varchar2(300) := '';

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

	PRAGMA	AUTONOMOUS_TRANSACTION;
begin

    if v_pName is null then         
        v_partition_name :=fun_getPartName(v_tName,v_date);
    else
        v_partition_name :=v_pName;
    end if;        

    if v_tbsName != 'miss' then
        v_tbs_name :=v_tbsName;
    else
        v_tbs_name :='miss';
    end if;
    
  	select count(*) INTO V_1 from user_tab_partitions where  TABLE_NAME=upper(v_tName) and partition_name=upper(v_partition_name);

	IF V_1 = 0 THEN
      v_sql:= 'alter table  '||v_tName||' add partition '||v_partition_name
					||' values less than ('||v_condition||') '||' tablespace '||v_tbs_name;

      execute immediate v_sql;
      COMMIT;
	END IF;

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

END;
/




create or replace procedure pro_AutoAddPart
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(sysdate+2,'yyyymmdd')||'00000000000';    
    
    pro_AddPartition(sysdate,'t_http_content',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_EMAIL_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_TELNET_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_WAP_CONTENT',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_http',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_ims',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_telnet',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_ftp',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'T_wap',v_pCondition,null,'miss');
    pro_AddPartition(sysdate,'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',sysdate,v_err,v_emsg);
			commit;

END;
/

create or replace procedure pro_SyncIndex(t_name in varchar2,i_name in varchar2)
AS
/*
* desc: 定期更新索引
*
* date:2006-10-20 11:54
*
* auth: 郑继东
*/

v_sql     varchar2(100)    := null;
v_err 	varchar2(20);
v_emsg 	varchar2(201);
cursor c is select PARTITION_NAME from user_tab_partitions where TABLE_NAME = upper(t_name);
begin

    for v_c in c loop
        --v_sql :='exec CTX_DDL.sync_index('''||i_name||''',''20M'','''|| v_c.PARTITION_NAME ||''')';
        --dbms_output.put_line(v_sql);
        ctx_ddl.sync_index(i_name,'500m',null,8);        
        commit;
    end loop;

	exception
		when others then
			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_SyncIndexRecent(t_name in varchar2,i_name in varchar2)
AS
/*
* desc: 定期更新索引
*
* date:2006-10-20 11:54
*
* auth: 郑继东
*/
p_name  varchar2(100)      ;
v_sql     varchar2(100)    := null;
v_err 	varchar2(20);
v_emsg 	varchar2(201);

v_date 	date ;
v_min	int	;
begin                
        v_date :=sysdate;
        select to_number(to_char(v_date,'mi')) into v_min from dual;
        
        ctx_ddl.sync_index(i_name,'500m',null,8);
        commit;

        
       if mod(to_number(to_char(v_date,'dd')),5) = 0 and v_min between 0 and 15 then				
				ctx_ddl.optimize_index(i_name,'FULL',parallel_degree => 8);
		
       end if;


	exception
		when others then