[SQL] 오라클 함수 생성·수정·삭제하기

인증 페이지에서 고객명을 뽑아내야하는데,

 

인증 페이지에는VERIFY_AUDIO , USER_VERIFY 테이블만 사용했다.

해당 테이블엔 CUSTOMNAME이라는 고객명 데이터가 있는 컬럼이 없으므로

 

USER_TRAIN이라는 테이블의 CUSTOMNAME이라는 컬럼에서 고객명을 가져오는 함수를 생성했다.

기존함수가 있으므로 그것을 바꿔서 사용하면 되었는데,

수정은 아래와 같이 create or replace function 을 사용하면 된다.

CREATE [OR REPLACE] FUNCTION 함수명 (매개변수)
RETURN 데이터타입
IS
        주요 변수 선언(지역 번수)
BEGIN
        실행문;
        RETURN 값;
END;

 

 

1.기존함수 조회

1-1) 현재 사용자가 소유한 모든 함수를 확인

-- 현재 사용자가 소유한 모든 함수를 검색
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';

1-2) 함수 내용 조회 (함수이름)

-- 프로시저 내용조회
SELECT TEXT
FROM USER_SOURCE
WHERE NAME LIKE '함수이름';

 

2. 함수 만들기 + 수정하기

--함수 만들기 + 수정하기
CREATE OR REPLACE FUNCTION FN_GET_CUSTOM_NAME (v_speakerkey IN VARCHAR2)
    RETURN VARCHAR2
IS 
    V_NAME VARCHAR2(20);
BEGIN 
        SELECT  max(CUSTOMNAME)
        INTO V_NAME
        FROM USER_TRAIN
        WHERE SPEAKERKEY = v_speakerkey;
        RETURN V_NAME;
END;

--커밋
commit;

CREATE OR REPLACE FUNCTION

- FUNTION 생성

- OR REPLACE : 해당 문법은 기존에 존재하는 같은 이름의 Function이 있다면, 덮어 씌워버린다는 뜻 (지워버리고 작성한 쿼리로 새로 만든다)

- FN_GET_CUSTOM_NAME : 해당이름의 함수를 만들며,
해당 함수의 매개변수는 v_speakerkey 이고, 데이터타입은 VARCHAR2

RETURN 과 IS
- RETURN : 해당 함수의 결과 값의 Datatype을 정의해준다.
- IS :  리턴도 하고, 쿼리문 내에서도 사용할 변수의 이름과 데이터타입을 정의한다. 

BEGIN 과 END
- BEGIN과 END 사이에 해당 함수에 대한 내용을 적는다

SELECT INTO
한테이블에서 새로운테이블로 정보를 복사할 때 사용한다.
USER_TRAIN 라는 테이블에서 CUSTOMNAME 가져와  V_NAME이라는 변수에 INSERT함

MAX()
선택된 컬럼의 가장 큰 값을 가져온다.
CUSTOMNAME이 여러개 나올 수 있으니 max 표현식을 통해 하나만 출력토록 만든다.

 

3. 함수 실행하기

--함수실행
SELECT FN_GET_CUSTOM_NAME('I4CHON1234') FROM DUAL;

테스트시 잘 나오는 것을 확인했다.

 

*  사용자정의함수는 되도록 사용하지 않는 것이 좋다고 한다.

다음 작업 인수자가 해당 함수의 필요유무나 해당 함수에 대한 정보가 없으면 수정하기 힘들기 때문에...

 

4. 함수 삭제하기

--함수삭제하기
DROP FUNCTION 함수명;

 

 

함수생성방법 참고링크:

https://sup-report.tistory.com/102

[Oracle] SQL로 간단한 Function 만들기