행위

오라클 프로시져 함수 목록

DB CAFE

thumb_up 추천메뉴 바로가기

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 = '[사용자정의함수명]';
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_프로시져_함수_목록&oldid=2525"