這是一個(gè)生成PL / sql函數(shù)原型的小腳本：

DECLARE

-- Local variables here

strPrev_object VARCHAR2(30);

strReturn_type VARCHAR2(30);

strProcedure_definition VARCHAR2(32767);

BEGIN

-- This dumps out subprogram definitions. It doesn't try to build

-- package scripts; instead,it dumps the package procedures as though they were defined

-- individually,with package name preceding subprogram name.

FOR aRow IN (SELECT *

FROM USER_ARGUMENTS a

INNER JOIN (SELECT PACKAGE_NAME,OBJECT_NAME,MAX(SEQUENCE) AS MAX_SEQUENCE

FROM USER_ARGUMENTS

GROUP BY PACKAGE_NAME,OBJECT_NAME)

USING (PACKAGE_NAME,OBJECT_NAME)

WHERE PACKAGE_NAME IS NULL AND

OBJECT_NAME = ''

ORDER BY PACKAGE_NAME,SEQUENCE)

LOOP

strProcedure_definition := NULL;

IF strPrev_object IS NOT NULL AND

strPrev_object <> aRow.OBJECT_NAME

THEN

IF strReturn_type IS NULL THEN

DBMS_OUTPUT.PUT_LINE(' );');

ELSE

DBMS_OUTPUT.PUT_LINE(' ) RETURN ' || strReturn_type || ';');

END IF;

DBMS_OUTPUT.PUT_LINE('');

END IF;

IF aRow.SEQUENCE = 1 THEN

IF aRow.ARGUMENT_NAME IS NULL THEN

strProcedure_definition := 'FUNCTION ';

strReturn_type := aRow.DATA_TYPE;

ELSE

strProcedure_definition := 'PROCEDURE ';

strReturn_type := NULL;

END IF;

IF aRow.PACKAGE_NAME IS NOT NULL THEN

strProcedure_definition := strProcedure_definition || aRow.PACKAGE_NAME || '.' || aRow.OBJECT_NAME || '(';

ELSE

strProcedure_definition := strProcedure_definition || aRow.OBJECT_NAME || '(';

END IF;

DBMS_OUTPUT.PUT_LINE(strProcedure_definition);

END IF;

IF aRow.ARGUMENT_NAME IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(' ' || aRow.ARGUMENT_NAME || ' ' ||

CASE aRow.IN_OUT WHEN 'IN/OUT' THEN 'IN OUT' ELSE aRow.IN_OUT END || ' ' ||

aRow.DATA_TYPE || CASE WHEN aRow.SEQUENCE <> aRow.MAX_SEQUENCE THEN ',' ELSE '' END

);

END IF;

strPrev_object := aRow.OBJECT_NAME;

END LOOP; -- aRow

IF strReturn_type IS NULL THEN

DBMS_OUTPUT.PUT_LINE(' );');

ELSE

DBMS_OUTPUT.PUT_LINE(') RETURN ' || strReturn_type || ';');

END IF;

END;

分享和享受.

總結(jié)

以上是生活随笔為你收集整理的oracle获取下级函数,从oracle获取函数和过程签名列表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。