oracle 存储过程堆栈,sql - 跨存储过程的Oracle表使用情况 - 堆栈内存溢出

以下是我为给定的@schema(仅大写)和@table(仅大写)执行影响分析(ONLY MERGE，INSERT和UPDATE)的片段。 它将返回所有过程名称，过程代码，行号和行号以及其他详细信息。 它可以很容易地用于包括函数对象而不是包。 我正在开发一个可以跨所有架构或选定架构运行的实用程序(也包括SELECT行)。 虽然这对你开始工作已经足够了。

我知道您可以使用Oracle中可用的依赖项和引用来执行类似的操作。 但对于包级别的影响，这是一个很好的补充。 我们还可以使用正则表达式进行更复杂的搜索。 但是像操作员一样简单而有效地满足了我的需求。

请注意，这不适用于可能在您的环境中工作的任何动态代码。 这只是在包中使用静态PL / SQL代码进行快速影响的恰当起点。

WITH TableDep as

-- This table returns references where the table is used within the code for UPDATE OR INSERT

(

SELECT

owner as schemaname,

name as packagename,

type as typename,

TEXT as refcodeline,

CASE WHEN upper(text) LIKE '%INSERT%' THEN 'INSERT'

WHEN upper(text) LIKE '%UPDATE%' THEN 'UPDATE'

WHEN upper(text) LIKE '%MERGE%' THEN 'MERGE'

END AS opr,

:Tablename AS Tablename,

line refline

FROM dba_source WHERE upper(owner) = upper(:OWNER)

AND type = 'PACKAGE BODY'

AND (

upper(text) LIKE ('%INSERT INTO '||:Tablename||'%')

OR

upper(text) LIKE ('%UPDATE%'||:Tablename||' %')

OR

upper(text) LIKE ('%MERGE%'||:Tablename||' %')

)

),

ProcedureDetails as

-- This code build all procedures within the package for references that is found in above query

(

SELECT

owner as schemaname,

name as packagename,

type as typename,

TEXT,

trim(REGEXP_SUBSTR(TEXT, '(PROCEDURE [[:print:]]+)\(',1,1,null,1)) as procedure_name,

line startline,

LEAD(line, 1) OVER (partition by name order by line)-1 as endline

FROM dba_source

WHERE owner = upper(:OWNER)

AND type = 'PACKAGE BODY'

AND upper(text) LIKE '%PROCEDURE%(%'

and exists (SELECt 1 FROM TableDep WHERE TableDep.packagename=name)

)

,ProcCode as

-- This code builds procedures into one cell per program for a given package. Later to find the effected procedures

(

SELECT

ProcTag.packagename ,

ProcTag.schemaname,

ProcTag.typename,

ProcTag.PROCEDURE_NAME,

ProcTag.startline,

ProcTag.endline,

TO_CLOB(rtrim(xmlagg(xmlelement(e,codeline.text).extract('//text()') order by line).GetClobVal(),',')) as Procedure_Code

FROM

ProcedureDetails ProcTag

INNER JOIN dba_source codeline ON ProcTag.packagename=codeline.name

AND ProcTag.schemaname=codeline.owner

and ProcTag.typename=codeline.type

and codeline.line between ProcTag.startline and ProcTag.endline

--WHERE PROCEDURE_NAME='PROCEDURE TRANS_KAT_INSO'

group by

ProcTag.packagename ,

ProcTag.schemaname,

ProcTag.typename,

ProcTag.PROCEDURE_NAME,

ProcTag.startline,

ProcTag.endline

)

-- extract all the reference code for the given table selected with it complete procedure code.

SELECT

ProcHeader.Packagename, ProcHeader.schemaname, ProcHeader.typename, ProcHeader.procedure_name, ProcHeader.Procedure_Code ,ProcHeader.startline,ProcHeader.endline,ProcReference.Tablename, ProcReference.opr

FROM

ProcCode ProcHeader

INNER JOIN

(

SELECT DISTINCT ProcCode.Packagename, ProcCode.schemaname, ProcCode.typename, ProcCode.procedure_name , TableDep.Tablename, TableDep.opr

FROM ProcCode

INNER JOIN TableDep ON ProcCode.packagename=TableDep.packagename

AND ProcCode.schemaname=TableDep.schemaname

and ProcCode.typename=TableDep.typename

and TableDep.refline between ProcCode.startline and ProcCode.endline

) ProcReference

ON ProcHeader.Packagename=ProcReference.Packagename

AND ProcHeader.schemaname=ProcReference.schemaname

AND ProcHeader.typename=ProcReference.typename

AND ProcHeader.procedure_name=ProcReference.procedure_name

;