这个存储过程,目的是从数据库中查找某个表相关的触发器和存储过程
但是执行的时候,只能执行出第一段找出触发器,第二段存储过程那段没有执行,没有报错
这是咋个问题?create or replace procedure relation(tname varchar2) is
beginFOR I IN (SELECT * FROM sys.dba_triggers) LOOP
IF INSTR(I.TRIGGER_BODY, LOWER(tname)) > 0 OR INSTR (I.TRIGGER_BODY, UPPER(tname)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(tname),I.TRIGGER_NAME,1,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 1 );
COMMIT;
END LOOP;FOR K IN (SELECT * FROM sys.all_source WHERE sys.all_source.type = 'PROCEDURE' AND sys.all_source.owner NOT LIKE ('%SYS%')) LOOP
IF INSTR(K.TEXT, LOWER(tname)) > 0 OR INSTR(K.TEXT,UPPER(tname)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(tname),K.NAME,3,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 3 );
COMMIT;
END LOOP;end;
但是执行的时候,只能执行出第一段找出触发器,第二段存储过程那段没有执行,没有报错
这是咋个问题?create or replace procedure relation(tname varchar2) is
beginFOR I IN (SELECT * FROM sys.dba_triggers) LOOP
IF INSTR(I.TRIGGER_BODY, LOWER(tname)) > 0 OR INSTR (I.TRIGGER_BODY, UPPER(tname)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(tname),I.TRIGGER_NAME,1,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 1 );
COMMIT;
END LOOP;FOR K IN (SELECT * FROM sys.all_source WHERE sys.all_source.type = 'PROCEDURE' AND sys.all_source.owner NOT LIKE ('%SYS%')) LOOP
IF INSTR(K.TEXT, LOWER(tname)) > 0 OR INSTR(K.TEXT,UPPER(tname)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(tname),K.NAME,3,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 3 );
COMMIT;
END LOOP;end;
我有找过 但找不到存储过程的视图 只知道到了触发器的sys.dba_triggers
原谅我把 我刚刚接触ORACLE
求指教dbms_output.put_line('.........') 怎么用
原谅我把 我刚刚接触ORACLE
求指教dbms_output.put_line('.........') 怎么用直接加到你需要的地方就可以了,调用的时候看一下有没有输出信息,有输出就说明进了这个循环,没有输出就说明没进,可以调试一下
求指教dbms_output.put_line('.........') 怎么用直接加到你需要的地方就可以了,调用的时候看一下有没有输出信息,有输出就说明进了这个循环,没有输出就说明没进,可以调试一下FOR K IN (SELECT * FROM sys.all_source WHERE sys.all_source.type = 'PROCEDURE' AND sys.all_source.owner NOT LIKE ('%SYS%')) LOOP
IF INSTR(K.TEXT, LOWER(tname)) > 0 OR INSTR(K.TEXT,UPPER(tname)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(tname),K.NAME,3,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 3 );
COMMIT;
dbms_output.put_line('.........');
END LOOP;是这样吗 我试了 没有输出
可是为什么没有进循环啊 觉得不能啊
dbms_output.put_line('.........'||K.TEXT);
还是没有输出
这是为什么啊 愁死了
你是不是过程有问题啊,优点奇怪啊,加到第一个loop之后看看,或者你单独执行第二段逻辑看看。END LOOP;
dbms_output.put_line('.........'||K.TEXT);
FOR K IN (SELECT * FROM sys.all_source WHERE sys.all_source.type = 'PROCEDURE' AND sys.all_source.owner NOT LIKE ('%SYS%')) LOOP
还是没有输出
这是为什么啊 愁死了
你是不是过程有问题啊,优点奇怪啊,加到第一个loop之后看看,或者你单独执行第二段逻辑看看。END LOOP;
dbms_output.put_line('.........'||K.TEXT);
FOR K IN (SELECT * FROM sys.all_source WHERE sys.all_source.type = 'PROCEDURE' AND sys.all_source.owner NOT LIKE ('%SYS%')) LOOP
试过了 加到第一个循环后有输出 但是还是进不到第二个循环
单独执行第二个逻辑没问题
有表啊,除非你用了动态SQL,一般肯定会显示的
select * from DBA_DEPENDENCIES where REFERENCED_NAME='...'
有表啊,除非你用了动态SQL,一般肯定会显示的
select * from DBA_DEPENDENCIES where REFERENCED_NAME='...'
用了你说的这个表 真的可以了
不过要是加上个条件就不行了
FOR K IN (SELECT * FROM DBA_DEPENDENCIES WHERE DBA_DEPENDENCIES.type = 'PROCEDURE' AND DBA_DEPENDENCIES.referenced_owner NOT LIKE '%SYS%' ) LOOP
IF INSTR(K.REFERENCED_NAME,UPPER(xxa)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(xxa),K.NAME,3,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 3 );
COMMIT;
END LOOP;
这样就还是出不来数据了
有表啊,除非你用了动态SQL,一般肯定会显示的
select * from DBA_DEPENDENCIES where REFERENCED_NAME='...'
用了你说的这个表 真的可以了
不过要是加上个条件就不行了
FOR K IN (SELECT * FROM DBA_DEPENDENCIES WHERE DBA_DEPENDENCIES.type = 'PROCEDURE' AND DBA_DEPENDENCIES.referenced_owner NOT LIKE '%SYS%' ) LOOP
IF INSTR(K.REFERENCED_NAME,UPPER(xxa)) > 0 THEN
INSERT INTO TABLE_RELATED VALUES (UPPER(xxa),K.NAME,3,'');
END IF;
DELETE FROM TABLE_RELATED A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM TABLE_RELATED B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.RELATED_NAME = B.RELATED_NAME AND A.RELATED_TYPE = 3 );
COMMIT;
END LOOP;
这样就还是出不来数据了这是为什么呀