오라클 프로시져 함수 목록
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 저장프로시저 명세[편집]
1.1 (1) 저장프로시저 정보[편집]
SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME,
TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED,
TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME ASC;
1.2 (2) 저장프로시저 파라미터 정보[편집]
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.SEQUENCE, b.ARGUMENT_NAME, b.DATA_TYPE
, (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH) WHEN 'DATE' THEN ' ' ELSE
TO_CHAR(b.DATA_LENGTH) END) AS DATA_LENGTH
, b.DATA_TYPE ||
(CASE b.DATA_TYPE
WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')'
WHEN 'DATE' THEN ' '
ELSE '(' || b.DATA_LENGTH || ')'
END) DATA_TYPE_DESC
, b.IN_OUT
FROM SYS.USER_OBJECTS a, SYS.USER_ARGUMENTS b
WHERE a.OBJECT_TYPE = 'PROCEDURE' AND a.OBJECT_ID = b.OBJECT_ID
ORDER BY a.OBJECT_NAME ASC, b.SEQUENCE ASC;
1.3 (3) 저장프로시저 내용[편집]
SELECT TEXT FROM SYS.USER_SOURCE WHERE TYPE = 'PROCEDURE' AND NAME = '[저장프로시저명]';
5. 사용자정의함수 명세
(1) 사용자정의함수 정보 보기
SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME,
TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED,
TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY OBJECT_NAME ASC;
2 사용자정의함수 명세[편집]
2.1 (1) 사용자정의함수 파라미터[편집]
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.SEQUENCE, b.ARGUMENT_NAME, b.DATA_TYPE
, (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH)
WHEN 'DATE' THEN ' '
ELSE TO_CHAR(b.DATA_LENGTH) END
) AS DATA_LENGTH
, b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')'
WHEN 'DATE' THEN ' '
ELSE '(' || b.DATA_LENGTH || ')'
END ) DATA_TYPE_DESC
, b.IN_OUT
FROM SYS.USER_OBJECTS a
, SYS.USER_ARGUMENTS b
WHERE a.OBJECT_TYPE = 'FUNCTION'
AND a.OBJECT_ID = b.OBJECT_ID
ORDER BY a.OBJECT_NAME ASC, b.SEQUENCE ASC;
2.2 (3) 사용자정의함수 내용[편집]
SELECT TEXT
FROM SYS.USER_SOURCE
WHERE TYPE = 'FUNCTION'
AND NAME = '[사용자정의함수명]';