oracle全文检索 学习

楔子

给自己做个笔记，毕竟是菜鸟。

---

创建过程

CREATE OR REPLACE PROCEDURE Find(infor varchar2,pd varchar2) is
type ref_cursor is ref cursor;
tablename_list  ref_cursor;
fieldname_list  ref_cursor;
foundtable      ref_cursor;
tablename       user_tables.table_name%type;
fieldname       user_tab_columns.column_name%type;
fieldtype       user_tab_columns.data_type%type;
num             number(10);
statement_sql   varchar2(5000);
BEGIN
  dbms_output.enable(999999999);
  open tablename_list for select user_tables.table_name from user_tables;
  loop
    fetch tablename_list into tablename;
    exit when tablename_list%notfound; 
         open fieldname_list for  select user_tab_columns.column_name,user_tab_columns.data_type from user_tab_columns where user_tab_columns.table_name= tablename ;
        loop
          fetch fieldname_list into fieldname,fieldtype;
          exit when fieldname_list%notfound;
          if (pd = 'number') or (pd = 'n') then
              if (fieldtype = 'NUMBER')  then
                 statement_sql :='select count(*) from ' ||  tablename  || ' where "' || fieldname || '" = '''||infor||'''';
                 execute immediate statement_sql into num ;
                 if num > 0 then
                    dbms_output.put_line('表名: '||tablename||'     列名: '||fieldname);
                    exit;
                 end if;           
              end if;
   elsif (pd = 'date') or (pd = 'd') then
             if (fieldtype = 'DATE') then
                statement_sql :='select count(*) from ' ||  tablename  || ' where "' || fieldname || '" = to_date('''||infor||''',''yyyy-mm-dd'')'; 
  execute immediate statement_sql into num ;
                 if num > 0 then
                    dbms_output.put_line('表名: '||tablename||'     列名: '||fieldname);
                    exit;
          end if;
      end if;
          else 
       if (fieldtype = 'CHAR') or (fieldtype = 'VARCHAR') or (fieldtype = 'VARCHAR2') or (fieldtype = 'NVCHAR')  then
   statement_sql :='select count(*) from ' ||  tablename  || ' where "' || fieldname || '" like  ''%'||infor||'%''';
                 execute immediate statement_sql into num ;
                 if num > 0 then
                    dbms_output.put_line('表名: '||tablename||'     列名: '||fieldname);
                    exit;
                 end if;    
       end if;                                
      end if; 
    end loop;
    close fieldname_list ;
  end loop;
  close tablename_list;
 END Find;

使用

查找关键信息
这里写代码片

1、运行语句 
set serveroutput on;
exec find('查找内容','g');

2、参数说明
exec find('参数1','参数2');
参数1   要查找的内容
参数2   要查找的内容的类型（n：表示数据类型    v：表示字符串）