这是一个生成PL / sql函数原型的小脚本：
@H_403_7@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 = '<your package,procedure,or function>'
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;
分享和享受.
