오라클 DBMS 정리 13

09 Nov 2018

Reading time ~4 minutes

오라클 DBMS 정리 13 - PL/SQL FUNCTION, PROCEDURE

FUNCTION

RETURN 값은 RETURN 반환형과 데이터형이 같아야 한다
매개변수는 ‘변수명 데이터형’만 명시
- 크기는 명시 안함!
함수는 쿼리문을 가질 수 있으나 간접실행만 되기 때문에 대부분 쿼리보단 연산이 들어감
출력설정을 안함(SET serveroutput ON), 디버깅이 필요하면 PL/SQL형태로 변경해서 디버깅

CREATE OR REPLACE FUNCTION 함수명 (매개변수, parameter, ... )
RETURN 반환형
IS
    -- 변수
BEGIN
    -- 코드
    RETURN 값;
END;
/

함수 삭제

DROP FUNCTION 함수명;

-- 입력된 주민번호에서 나이를 구하는 함수
-- 880101-1234567
-- 나이 : 현재년도 - 태어난해 + 1 (한국기준)
-- 8번째 자리수가 1,2면 19--  (한국인)
-- 8번째 자리수가 3,4면 20--  (한국인)
-- 8번째 자리수가 5,6면 19--  (외국인)
-- 8번째 자리수가 7,8면 20--  (외국인)
-- 8번째 자리수가 0,9 18--    (한국인)  (생략..)
-- 무효하면 0살을 반환

CREATE OR REPLACE FUNCTION get_age_from_ssn(ssn CHAR)
RETURN NUMBER
IS
    age NUMBER := 0;
    year_of_birth VARCHAR(4);
    yy CHAR(2);

    -- 판단기준으로 사용하는 flag 변수
    flag CHAR(1);
BEGIN             
    
    IF CHECK_SSN(ssn) = 'success' THEN
        
        -- 19일지 20일지 더 할지, 18일지 판단
        flag := SUBSTR(ssn, 8, 1);
        yy := SUBSTR(ssn,1,2);
                
        IF flag IN (1,2,5,6) THEN
            year_of_birth := '19'||yy;
        ELSIF flag IN (3,4,7,8) THEN
            year_of_birth := '20'||yy;
        ELSIF flag IN (0, 9) THEN
            year_of_birth := '18'||yy;
        END IF;                            

        age := TO_CHAR(SYSDATE, 'yyyy') 
                 - year_of_birth + 1;
    ELSE
        age:= 0;      
    END IF;

    RETURN age;

END;
/

SELECT get_age_from_ssn('901101-1234567') age
FROM   dual;

13-01

매개변수 없는 함수

CREATE OR REPLACE FUNCTION 함수명[()]
RETURN 반환형
...

-- 이전 호출 시
함수명(매개변수)

-- 매개변수 없는 함수 호출 시
-- ()는 생략가능 
함수명[()]

-- ITEM_00001' 이런식으로 연번이 생성되게 하는 
-- 매개변수 없는 함수를 만들기 위한 사전작업
-- 사전작업

-- 시퀀스 생성
CREATE SEQUENCE seq_item
 INCREMENT BY 1
 START WITH 1
 MAXVALUE 9999999
 CACHE 10
 NOCYCLE;

-- 테이블생성
CREATE TABLE test_func(
  num CHAR(10),
  name VARCHAR2(30),
  input_date DATE DEFAULT SYSDATE,
  CONSTRAINT pk_test_func PRIMARY KEY(num)
);

-- 매개변수 없는 함수 : 시퀀스를 사용하여 10자리의
-- 1씩 증가하는 고정길이형 문자열을 반환하는 함수
-- 고정길이 형식 : 'ITEM_00001'

CREATE OR REPLACE FUNCTION item_num
RETURN CHAR
IS
    temp_num CHAR(10);
    seq_num NUMBER;
    
BEGIN
    
    SELECT seq_item.nextval
    INTO   seq_num
    FROM   dual;      
                  
    -- oracle 11g부턴 함수에서 
    -- 시퀀스 사용 시 쿼리없이 바로 사용도 가능하다
    -- seq_num := seq_item.nextval;
    
    temp_num := 'ITEM_'||LPAD(seq_num,5,0);  
    -- temp_num := temp_num||문자열 하게되면
    -- temp_num이 CHAR(10)인 뒤에 +@ 더 붙게 되어 오버플로우 발생
    
    RETURN temp_num;
    
END;
/

-- 함수를 호출하여 지정된 형식의 번호를 얻기
INSERT INTO test_func(num, name)
VALUES(item_num, 'young');

INSERT INTO test_func(num, name)
VALUES(item_num, 'sol');

INSERT INTO test_func(num, name)
VALUES(item_num, 'kim');

INSERT INTO test_func(num, name)
VALUES(item_num, 'Lee');

-- 함수 호출 시 ()는 없어도 잘 호출됨, 있어도 됨
INSERT INTO test_func(num, name)
VALUES(item_num(), 'Park');

SELECT * FROM test_func;

13-02

BIND Variable (외부 변수)

sqlplus에서 제공하는 변수
- 1회용(sqlplus 종료시 사라짐)
- 같은 이름의 변수 선언하면 예전 변수를 덮어씀
PL/SQL이나 PROCEDURE에서 사용한 값을 sqlplus에서 저장하고 출력하기 위해 만드는 변수
- 프로시저의 결과를 출력하기위해 바인드 변수를 사용

-- 1. BIND 변수 선언
VAR       변수명 데이터형[;]
VARIABLE  

-- 2. 값 할당 
-- PL/SQL 에서 사용할 때
:변수명 := 값;

-- PROCEDURE 에서 사용할 때
EXECUTE 프로시저명(... :변수명)
EXEC 

-- 3. 출력
PRINT 변수명 변수명 ...

-- 선언된 BIND변수 목록보기
var
VARIABLE

-- 생성가능한 데이터타입 확인
var n t

13-03

DATE타입은 바인드변수로 만들 수 없다

PROCEDURE

프로시저(직접실행)는 자주 사용될 쿼리를 저장해 둔 것
- 함수(간접실행)는 자주사용될 코드(계산식 등 연산)를 저장해 둔 것
자주 사용될 쿼리문을 미리 만들어두고 사용하기 위한 것
- 개발자가 주로 만들고 사용, 잘 사용하면 언어코드의 간략화가 가능해짐
직접 실행
- 컴파일된 프로시저를 실행기를 사용하여 실행하는 것
  - 실행기 : EXEC[EXCUTE]
함수와 다르게 반환형이 존재하지 않는다.
- out parameter를 사용하여 결과값을 외부로 내보낼 수 있다.
user_procedures 딕셔너리에서 확인 가능
java에서 CallableStatement를 사용하여 프로시저를 호출할 수 있음
작성법
- 매개변수는 in parameter와 out parameter 2개가 존재
- in parameter
  - 프로시저 밖에 존재하는 값을 프로시저 안으로 전달하기 위해 존재
  - in parameter의 ‘IN’은 생략가능
  - 값 할당이 불가능
  - 사용만 가능
- out parameter
  - 프로시저 안에 값을 밖으로 전달하기 위해 존재
    - 바인드 변수에 값 저장
    - 언어에선 바인드 변수가 없음 -> 다른방식 사용
  - out parameter의 ‘OUT’은 생략 불가
  - 프로시저 내부에서 값 할당이 가능
  - 내보낼 값이 없다면 생략가능

CREATE [OR REPLACE] PROCEDURE 프로시저명 (
    -- in parameter
    변수명 [IN] 데이터형,
    ...,
    -- out parameter
    변수명 OUT 데이터형
)
IS
    변수선언
BEGIN
    코드작성
END;
/

컴파일

SQL> @경로\파일명.sql

실행
1. 바인드 변수의 선언
2. 실행 : 직접실행
3. 바인드 변수의 값 사용

VAR 바인드변수명 데이터형(크기); -- 1
...

EXEC 프로시저명(매개변수명, .. :바인드변수명, ...); -- 2

PRINT 바인드변수명 바인드 변수명 .. ; -- 3

프로시저 삭제

DROP PROCEDURE 프로시저명

-- 1. 소스작성
-- 두 수를 입력받아 더하기 연산한 결과를 out parameter에 설정하는 프로시저

CREATE OR REPLACE PROCEDURE proc_plus (num1 NUMBER,
num2 IN NUMBER, num3 OUT NUMBER)
IS
    -- parameter명과 같은 변수는 만들 수 없다
    -- 변수명을 정할 때에는 프로시저명과 다르게 설정한다.
BEGIN
    -- out parameter에는 값할당이 되지만 in parameter엔 값할당 안됨
    -- num1 := 100;  error!
    num3 := num1 + num2;
END;
/
-- 2. 컴파일
-- 3. 바인드 변수 선언 : VAR val NUMBER
-- 4. 실행 : EXEC proc_plus(11,7,:val)
-- 5. 바인드변수 출력: PRINT val

13-04

-- cp_emp2 테이블에 사원변호, 사원명, 입사일, 연봉을 추가하고
-- 추가된 행의 수를 외부로 반환하는 Procedure

CREATE OR REPLACE PROCEDURE proc_insert_cpemp2(
    empno IN cp_emp2.empno%TYPE,
    ename IN cp_emp2.ename%TYPE,
    sal IN cp_emp2.sal%TYPE,
    cnt OUT NUMBER,
    msg OUT VARCHAR2
)                      
IS
BEGIN

    INSERT INTO cp_emp2(empno, ename, hiredate, sal)
    VALUES(empno, ename, SYSDATE, sal);

    -- SQL%는 묵시적커서
    cnt := SQL%ROWCOUNT;       
    msg := '추가성공';    
    COMMIT;
    
    EXCEPTION
    WHEN OTHERS THEN
        cnt := 0;
        msg := '추가실패';
        
END;
/

-- 1. 바인드변수 선언
VAR cnt NUMBER;
VAR insert_msg VARCHAR2(12);

-- 2. 실행
EXEC proc_insert_cpemp2(1111, '오복동', 4000, :cnt, :insert_msg);

-- 3. 바인드 변수 출력
PRINT cnt;
PRINT insert_msg;

13-05

숙제풀이1

숙제풀이2

숙제풀이3

오라클DBMS