一,业务场景:根据表名+某列的具体值+该列的数据类型_查询出所有有关联的表及列名
二 Oracle 存储过程
整体代码:
create or replace PROCEDURE proc_hw(tab_name IN VARCHAR,--表名称
col_value IN VARCHAR, --列值
col_type IN VARCHAR --数据类型
)
IS
v_bcount number;
out_tab_name VARCHAR2(50);
col_name VARCHAR2(50);
CURSOR cur_a IS select TABLE_NAME from user_tables WHERE TABLESPACE_NAME is not null AND TABLE_NAME!=tab_name ORDER BY TABLE_NAME ASC; -- 游标1
CURSOR cur_b(ta_name VARCHAR) IS SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name =ta_name AND data_type like '%'||col_type ||'%' AND owner='表空间的用户名' ORDER BY data_type; -- 游标2
BEGIN
FOR a_cur IN cur_a LOOP
FOR b_cur IN cur_b(a_cur.TABLE_NAME) LOOP
execute immediate 'SELECT '||''''||a_cur.TABLE_NAME||''''|| ' FROM DUAL' into out_tab_name;--动态检索sql
execute immediate 'SELECT '||''''||b_cur.column_name||''''|| ' FROM DUAL' into col_name;--动态检索sql
execute immediate 'select count(*) from '||a_cur.TABLE_NAME || ' where "'|| b_cur.column_name||'" IS NOT NULL AND "'|| b_cur.column_name||'"='''||col_value||'''' into v_bcount;--动态检索sql
IF v_bcount>0 THEN DBMS_OUTPUT.PUT_LINE(tab_name||'-----'||col_value||'-------'||v_bcount||'------'||col_name||'------'||out_tab_name);
end IF;
END LOOP;
END LOOP;
END;
注:1.代码中 ‘‘’’ 为转义单引号;
2.此sql语句在Navicat12中编译通过,Navicat11中编译不通过
三 调用
begin
proc_hw('tableName','colValue','colDataType');
END;
四 分歧
游标1 在有时候不成立,
CURSOR cur_a IS select TABLE_NAME from user_tables WHERE TABLESPACE_NAME is not null AND TABLE_NAME!=tab_name ORDER BY TABLE_NAME ASC; -- 游标1
换成下面这种写法
CURSOR cur_a IS select TABLE_NAME from dba_tables WHERE TABLESPACE_NAME='tablespace' AND TABLE_NAME!=tab_name ORDER BY TABLE_NAME ASC; -- 游标1
改为上面的sql之后若是提示 视图或表不存在,可执行下面的sql
grant select any dictionary to 用户名;