|
create or replace procedure MY_Pro_SearchKeyWord is
? v_sql VARCHAR2(4000);
? v_tb_column VARCHAR2(4000);
? v_cnt NUMBER(18,0);
? cursor cur is SELECT ‘SELECT ‘‘‘||‘"‘||t1.table_name||‘"."‘||t1.Column_Name||‘"‘||‘‘‘‘||‘ as col_name,NVL(COUNT(t."‘||t1.Column_Name||‘"),0) as cnt FROM "‘||
???????? t1.table_name||‘" t WHERE t."‘||t1.column_name||‘" like ‘‘%关键字%‘‘‘ AS str
??? FROM cols t1 left join user_col_comments t2
????? on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
??? left join user_tab_comments t3?
????? on t1.Table_name=t3.Table_name?
?? WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
?????????????? WHERE t4.Object_Type=‘TABLE‘?
???????????????? AND t4.Temporary=‘Y‘?
???????????????? AND t4.Object_Name=t1.Table_Name )
???? AND (t1.Data_Type=‘CHAR‘ or t1.Data_Type=‘VARCHAR2‘ or t1.Data_Type=‘VARCHAR‘)
?? ORDER BY t1.Table_Name,t1.Column_ID;
BEGIN
? FOR i IN cur LOOP
??? v_sql := i.str; -- 获取将要执行的SQL语句；
??? EXECUTE IMMEDIATE v_sql INTO v_tb_COLUMN,v_cnt;
??? IF v_cnt > 0 THEN
????? dbms_output.put_line(‘表：‘||substr(v_tb_column,1,instr(v_tb_column,‘.‘,1)-1)||‘ 列：‘||substr(v_tb_column,1)+1)||
?????????????????????????? ‘有 ‘||to_char(v_cnt)|| ‘条记录含有字串"关键字" ‘);
??? END IF;
? END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
? dbms_output.put_line(v_sql);
? dbms_output.put_line(v_tb_column);
END;
end MY_Pro_SearchKeyWord;
?
http://topic.csdn.net/u/20101110/15/06f0d4d1-f537-4124-b2f5-aabd98b2d0df.html
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
