CREATE OR REPLACE PROCEDURE search_col1(p_look varchar2) IS
tmpVar NUMBER;
v_col varchar2(3200);
v_look varchar2(32);-已知的要查找的列内容
v_colname varchar2(32);
v_sql varchar2(3200);

/**************************t****************************************************
NAME: search_col
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2008-11-24 1. Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: search_col
Sysdate: 2008-11-24
Date and Time: 2008-11-24, 19:29:44, and 2008-11-24 19:29:44
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN

tmpVar := 0;
for x in (
select table_name,column_name,owner from dba_tab_columns where owner='TESTUSER' and data_type='NUMBER'
AND table_name not in ('ITSM_SERVICECALLS','ITSM_CODES')
)
loop
tmpVar:=search_col_inner(x.owner,x.table_name,x.column_name,p_look);
if tmpVar=1 then exit; end if;
end loop;


/*
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('no data');
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
dbms_output.put_line('errors');
*/
END search_col1;
/


CREATE OR REPLACE FUNCTION search_col_inner(p_owner varchar2,p_table varchar2,p_colname varchar2,p_look varchar2) RETURN NUMBER IS
mpVar NUMBER;
v_col varchar2(3200);
v_look varchar2(32);
v_colname varchar2(32);
v_sql varchar2(3200);
PRAGMA autonomous_transaction;
/******************************************************************************
NAME: search_col
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2008-11-24 1. Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: search_col
Sysdate: 2008-11-24
Date and Time: 2008-11-24, 19:29:44, and 2008-11-24 19:29:44
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN

--insert into search_save(owner,tablename,colname)values(p_owner,p_table,'DONE');
delete from search_save;
commit;
v_sql:='select '||p_colname||' from '||p_owner||'.'||p_table|| ' where rownum=1 and '|| p_colname||'=:cnt';
--dbms_output.put_line(v_sql);
execute immediate v_sql INTO v_col using p_look;
dbms_output.put_line(v_col);
if v_col=p_look then
insert into search_save(owner,tablename,colname)values(p_owner,p_table,p_colname);
commit;
return 1;

end if;



EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('no data');
return 0;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
dbms_output.put_line('errors');
return 0;

END search_col_inner;
/
[@more@]