PL/SQL 함수

FUNCTION

- 자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program이다.

- Stored Function or User Function or User-Defined Function 이라고 한다.

- 자신의 스키마 계정에 함수를 작정하려면 CREATE PROCEDURE 시스템 권한 필요

- Header에 리턴되는 데이터 타입을 기입 Header
끝에는 IS[AS]가 와야하고
IS[AS]와 Begin사이에 Begin~End에서 사용할 변수를 선언한다.

기본 형식 ex)

CREATE [OR REPLACE] FUNCTION function_name[parameter]

RETURN 리턴 데이터타입

IS[/AS]
	Declaration_section

// BEGIN 과 END 사이에 변수 선언
BEGIN 
	Execution_section
    Return return_variable
    
EXCEPTION // 예외 처리
	exception section
    Return return_variable
    
END;

실제 함수

CREATE OR REPLACE FUNCTION ojc2(p_empno IN NUMBER)

파라미터 타입이 out이면 내부에서 외부로 값을 보냄

RETURN VARCHAR2 // 리턴되는 데이터 타입

IS
	v_ename emp.ename%TYPE; // 변수 선언
    
BEGIN 
	// emp 테이블에서 ename컬럼을 select해서 v_ename에 넣어주는데
    // 조건은 empno = p_empno
	SELECT ename
    INTO v_ename // 변수에 값을 넣는다.
    FROM emp
    WHERE empno = p_empno(매개변수로 들어옴)
    
   	RETURN v_ename;
    
END ojc2;

 

계층형 쿼리 재귀호출

DROP FUNCTION IF EXISTS fnc_hierarchi;
 
DELIMITER $$
 
CREATE FUNCTION  fnc_hierarchi() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    /* 첫번쨰 실행시 v_parent에 @id가 들어 있어 자식 노드가 있나 확인.
      다음 실행 부터는 v_id에 @id가 들어있어 형제노드가 있는지 확인용. */
    SELECT MIN(id)
      INTO @id 
      FROM ANIMAL
     WHERE p_id = v_parent
       AND id > v_id;
 
    /* 현재 Root노드이거나 아직 남은 자식,형제노드가 있으면 
       그 노드로 이동해서 id,level 출력 후 다시 함수 호출 */
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    

    /* 형제 노드가 없으면 현재 노드의 부모노드로 이동해서 다시 함수 호출 */
    SET @level := @level - 1;
 
    SELECT id, p_id
      INTO v_id , v_parent 
        FROM ANIMAL
       WHERE id = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;