我想把所有以FA开头的表的表名放到表B中,如何来写这个SQL呢? 求大侠帮忙表B
create table B
(
A CHAR(50)
)表AS_TAB_COL中放着所有我要查找的表的表明,表明可以通过如下语句查找:
SELECT DISTINCT TAB_ID FROM AS_TAB_COL WHERE TAB_ID LIKE 'FA%' ORDER BY TAB_ID我写了一个存储,如下:
create or replace procedure YSC_gdzk
as
TABLEX VARCHAR2(20);
ASQL VARCHAR2(1000);
NUMBER_A INT;CURSOR TAB_X IS SELECT DISTINCT TAB_ID FROM AS_TAB_COL WHERE TAB_ID LIKE 'FA%' ORDER BY TAB_ID;
BEGIN
OPEN TAB_X;
FETCH TAB_X INTO TABLEX;
LOOP
EXIT WHEN TAB_X%NOTFOUND;
BEGIN
ASQL:='SELECT COUNT(*) INTO NUMBER_A FROM '||TABLEX;
IF NUMBER_A >0 THEN
ASQL:='INSERT INTO B(A) VALUES(TABLEX)';
EXECUTE IMMEDIATE ASQL;
END IF;
END;
END LOOP;
CLOSE TAB_X;
commit;
end YSC_gdzk;编译没有错误,执行也不提示错误,但是一直执行,不会结束,不知道是不是出现死循环了。
求大侠帮我改一下这个存储过程 或者 也可以根据我的条件帮我重新写一个存储过程,谢谢了!
create table B
(
A CHAR(50)
)表AS_TAB_COL中放着所有我要查找的表的表明,表明可以通过如下语句查找:
SELECT DISTINCT TAB_ID FROM AS_TAB_COL WHERE TAB_ID LIKE 'FA%' ORDER BY TAB_ID我写了一个存储,如下:
create or replace procedure YSC_gdzk
as
TABLEX VARCHAR2(20);
ASQL VARCHAR2(1000);
NUMBER_A INT;CURSOR TAB_X IS SELECT DISTINCT TAB_ID FROM AS_TAB_COL WHERE TAB_ID LIKE 'FA%' ORDER BY TAB_ID;
BEGIN
OPEN TAB_X;
FETCH TAB_X INTO TABLEX;
LOOP
EXIT WHEN TAB_X%NOTFOUND;
BEGIN
ASQL:='SELECT COUNT(*) INTO NUMBER_A FROM '||TABLEX;
IF NUMBER_A >0 THEN
ASQL:='INSERT INTO B(A) VALUES(TABLEX)';
EXECUTE IMMEDIATE ASQL;
END IF;
END;
END LOOP;
CLOSE TAB_X;
commit;
end YSC_gdzk;编译没有错误,执行也不提示错误,但是一直执行,不会结束,不知道是不是出现死循环了。
求大侠帮我改一下这个存储过程 或者 也可以根据我的条件帮我重新写一个存储过程,谢谢了!
create table b(tab varchar2(64),cnt number);create or replace procedure test
as
TABLEX VARCHAR2(20);
ASQL VARCHAR2(1000);
NUMBER_A INT;
begin
for x in (select table_name from user_tables where rownum <=10) loop
asql := 'insert into b select '''||x.table_name||''',count(*) from '||x.table_name||' where rownum <=1';
execute immediate asql;
END LOOP;
execute immediate 'delete from b where cnt =0';commit;
end ;
/T@ORA>select * from b;TAB CNT
---------------------------------------------------------------- ----------
PF_RESULT_56A4_704 1
PF_RESULT_69_704 1
TT_03_SYNC_OBJS 1
TT_03_AGENT_STATUS 1
RESULT 1
EMP 1
ANIMAL 1
TT_03_62938_L 1
O 19 rows selected.把上面的查询sql改成lz自己的就可以了
2 as
3 TABLEX VARCHAR2(20);
4 ASQL VARCHAR2(1000);
5 cnt INT;
6 begin
7 for x in (select table_name from user_tables where rownum <=10) loop
8 asql := 'select count(*) from '||x.table_name||' where rownum <=1';
9 execute immediate asql into cnt;
10 if(cnt > 0) then
11 insert into b values(x.table_name);
12 else
13 dbms_output.put_line(x.table_name||' count 0');
14 end if;
15 END LOOP;
16
17 commit;
18 end ;
19 /Procedure created.Elapsed: 00:00:00.09
T@ORA>truncate table b;Table truncated.Elapsed: 00:00:00.15
T@ORA>exec test;
N count 0PL/SQL procedure successfully completed.Elapsed: 00:00:00.07
T@ORA>select * from n;no rows selectedElapsed: 00:00:00.01
T@ORA>select * from b;TB
----------------------------------------------------------------------------------------------------
PF_RESULT_56A4_704
PF_RESULT_69_704
TT_03_SYNC_OBJS
TT_03_AGENT_STATUS
RESULT
B
EMP
ANIMAL
TT_03_62938_L