ORACLE 공부 12일차 2020/09/01

2020-09-01
저장프로시저(Stored Procedure)
    - 비지니스 로직을 처리하는 모듈을 작성하여 컴파일하고 해당 기능이 핑요한곳에서 함수처럼 호출 할 수 있는 단위 
    - 컴파일된 프로시져는 캐시메모리에 저장되어 처리 속도가 빠르고, 네트워크의 트래픽 감소 효과
    - 사용자에게 내부 모듈을 감추기 때문에 보안확보
    - 프로시저는 반환값이 없다.
    (사용형식) 
    CREATE [OR REPLACE] PROCEDURE 프로시저명[(
        변수명 [모드] 타입명 [ := DEFAULT 값], -- 타입의 크기를 쓰면 오류, VARCHAR2(50) 괄호 쓰면 오류 
        변수명 [모드] 타입명 [ := DEFAULT 값],
                                :
        변수명 [모드] 타입명 [ := DEFAULT 값])]
    IS | AS
        선언문;
    BEGIN
        처리문;
        [EXCEPTION
            예외처리문;
     END;
    -  '변수명' : 매개변수명으로 보통 P_로 시작
    - 모드 : IN -> 입력용, OUT -> 출력용, INOUT -> 입/출력 공용
                default는 IN이다, 생략하면 IN으로 간주
    - 타입명 : 매개변수의 타입으로 크기 설정 안함 
    

---

    (실행문 사용형식)
    EXEC | EXECUTE 프로시저명[(매개변수|값, .....)];
    - PL/SQL에서 프로시져 실행시 'EXEC|EXECUTE'는 생략
 예) 회원테이블에서 'd001'회원의 회원번호를 입력 받아 회원명, 주소, 직업을 출력하는 프로시져 작성 

 CREATE OR REPLACE PROCEDURE PROC_MEM01( 
    P_MEM_ID IN MEMBER.MEM_ID%TYPE) 
 IS  
    V_NAME MEMBER.MEM_NAME%TYPE; 
    V_ADDR VARCHAR2(100); 
    V_JOB MEMBER_JOB; 
 BEGIN  
    SELECT MEM_NAME.MEM_ADD1||'  '||MEM_ADD2, 
             MEM_JOB INTO V_NAME, V_ADDR, V_JOB 
    FROM MEMBER 
 WHERE MEM_ID = P_MEM_ID; 
 DBMS_OUTPUT.PUT_LINE(V_NAME || ', '|| V_ADDR|| ', ' ||V_JOB ); 
 END;

---

2020-09-01-02)
**OUT 매개변수
    - 실행이 BLOCK에 기술 
예) 회원번호를 입력 받아 이름과 직업을 출력하는 프로시져 작성
    (단, 이름과 직업은 OUT 매개변수 사용)

    

CREATE OR REPLACE PROCEDURE PROC_MEM03( 
    P_MEM_ID IN MEMBER.MEM_ID%TYPE, 
    P_NAME OUT MEMBER.MEM_NAME%TYPE, 
    P_JOB OUT MEMBER.MEM_JOB%TYPE) 
   IS 
   BEGIN 
SELECT  MEM_NAME, MEM_JOB            --넘겨줄거 
        INTO  P_NAME, P_JOB                --넘겨주는거 INTO 
FROM MEMBER 
 WHERE MEM_ID = P_MEM_ID; 
END; 

DECLARE  
    V_NAME MEMBER.MEM_NAME%TYPE; 
    V_JOB MEMBER.MEM_JOB%TYPE; 
BEGIN 
    --EXEC PROD_MEM02('g001', :V_NAME,:V_JOB); 
    --SQL에 코딩단위 LINE 
    DBMS_OUTPUT.PUT_LINE('회원명 : '||V_NAME ); 
    DBMS_OUTPUT.PUT_LINE('직업 : '||V_JOB); 
    END; 

    
    
2020-09-01-03)
사용자 정의 함수 (USER DEFINED FUNCTION)
    - 반환(하나) 값이 있음 
    - 반환되는위치는 함수 호출문의 위치
    - 사용형식은 PROCEDURE와 유사
    (사용형식)
    CREATE [OR REPLACE] FUNCTION 함수명 [(
    매개면수 IN|OUT|INOUT 타입명 [ := default 값],
                            :
    매개면수 IN|OUT|INOUT 타입명 [ := default 값],
    
    RETURN 타입명   --변수명 아니고 타입명
    
    IS| AS 
    BEGIN 
        RETURN 변수 | 값 | 수식 ;  -- 타입명하고 일치해야한다.
    END;
    
 예) 회원번호를 입력받아 나이를 반환하는 함수를 작성하시오 

 

CREATE OR REPLACE FUNCTION FN_MEM01( 
    P_MEM_ID MEMBER.MEM_ID%TYPE) 
    RETURN NUMBER   --타입명만 쓴다 크기지정하지 않고  
 IS  
    V_AGE NUMBER :=0; --초기화 반드시 해야한다 않하면 오류 
BEGIN 
    SELECT EXTRACT(YEAR FROM SYSDATE)- EXTRACT(YEAR FROM MEM_BIR)  
            INTO V_AGE 
        FROM MEMBER 
      WHERE MEM_ID = P_MEM_ID;  
    EXCEPTION WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE('오류발생 : ' || SQLERRM); 
        RETURN NULL;     --EXCEPTION 쓸때 써야된다 FUNCTION에서 
RETURN V_AGE;     
END; 
     
--(실행) 
SELECT MEM_ID AS 회원명, 
        FM_MEM_01(MEM_ID) AS 나이, 
        MEM_JOB AS 직업 , 
        MEM_MILEAGE AS 마일리지 
    FROM MEMBER 
 WHERE    FM_MEM_01(MEM_ID) >=50;