오라클 DBMS 정리 13

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;

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;

-- 1. BIND 변수 선언
VAR       변수명 데이터형[;]
VARIABLE  
 
-- 2. 값 할당 
-- PL/SQL 에서 사용할 때
:변수명 := 값;
 
-- PROCEDURE 에서 사용할 때
EXECUTE 프로시저명(... :변수명)
EXEC 
 
-- 3. 출력
PRINT 변수명 변수명 ...

-- 선언된 BIND변수 목록보기
var
VARIABLE
 
-- 생성가능한 데이터타입 확인
var n t

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

SQL> @경로\파일명.sql

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

-- 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;