오라클 PL/SQL 프로시저, 함수 정리 + 커서 외

• 프로시저 : 결과값 반환X / IN 입력파라미터, OUT 출력값(리턴용)
• 함수 : 리턴 반환해야됨
• 패키지 : 하나 이상의 프로시저, 함수,변수, 에외 등의 묶음
• 지정 이벤트 발생시 자동으로 실행되는 PL/SQL 블록

저장프로시저 ?

• 매개변수 O, 재사용 가능, 트랜잭션 제어, 수행
  문법

CREATE OR REPLACE PROCEDURE 프로시저명
(
파라미터 데이터타입 			--매겨변수 

)
IS [AS]  		--변수선언  //없어도 명시는 해야됨
BEGIN 
		-- 처리 로직
        
    EXCEPTION WHEN OTHERS THEN
    	-- 예외처리
        
END

실행

EXECUTE 프로시저명(파라미터)
EXEC 프로시저명(파라미터)

파라미터부분에 테이블과 같은 데이터타입을 지정해주는 법: 테이블명.컬럼명%TYPE

%ROWTYPE - 한개이상의 값일때 씀.

• 로우타입변수 선언하면테이블에있는 ROW 대입가능

CREATE OR REPLACE PROCEDURE PROC
(
파라미터 	테이블명.컬럼명%TYPE	--파라미터-테이블컬럼과 같은데이터타입
)
IS 
레코드변수1    테이블명%ROWTYPE; 	--로우타입선언
변수2    데이터타입;
BEGIN 
SELECT * INTO 레코드변수1
FROM 테이블
WHERE 테이블컬럼 = 파라미터;
변수2 := 레코드변수1.테이블컬럼1 ||''|| 레코드변수1.테이블컬럼2 ||''|| 
		레코드변수1.테이블컬럼3 ;
DBMS_OUTPUT.PUT_LINE(변수2);

EXCEPTION WHEN OTHERS THEN 		--예외처리
		--- ;
END;

||''|| 로 이어줌
여러개의 레코드를 넣을때 INTO절을 이용한다.

커서

• 결과 집합의 각 개별 데이터에접근이 가능함
  쉽게 배열과 같은 거임

%NOTFOUND

• 커서에서만 사용가능 속성
• 패치할 로우가 없음을 의미함

명시적 커서 : 사용자가 직접쿼리결과에 접근하고 커서를 사용함

1. 커서 선언 CURSOR 커서명 IS SELECT문;
2. 커서 열기 BEGIN내에 OPEN 커서명;
3. 패치(FECTH) 쿼리결과에 접근, 루프 돌며 개별 값에 접근
   FETCH 커서명 INTO 변수명
4. 커서 닫기 메모리상 존재쿼리결과 소멸

CREATE OR REPLACE PROCEDURE PROC2
IS 
CUSRSOR PROC_CSR IS			--커서 선언
SELECT * FROM TBL;
로우타입변수		TBL%ROWTYPE;	--루프돌며 담을 변수 선언
BEGIN
OPEN PROC_CSR;
LOOP 
	FETCH PROC_CSR(커서명) INTO 로우변수.컬럼1, 로우변수.컬럼2, 로우변수.컬럼3;
    
    EXIT WHEN PROC_CSR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(로우변수.컬럼1.....);
    
END LOOP;		--루프닫기
CLOSE PROC_CSR 		--커서닫기

예외~~
.
.
END;

묵시적 커서 : 오라클내부에서 각쿼리결과에 접근해 사용하기위한 내부적 커서

• 모든 쿼리 실행될때마다 오픈됨, 내부에서 접근사용하기때문에 선언,오픈 필요X
• 가장 최근에 실행된 sql 문장에 대한 커서를 내부적으로 가지고있음

FOR LOOP CURSOR

FOR LOOP문을 사용시 커서의 OPEN, FETCH, CLOSE가 자동으로 발생됨
할당할 로우타입변수를 따로 기술할 필요가 없다함 ~~!
-->커서 선언 후 레코드단위로읽을수 있기때문에 따로 로우타입 변수 선언없이 바로 작성할수있다고한다.

FOR 변수명(레코드정보 담김) IN 커서명 LOOP
	실행문;
    
END LOOP;

예)

CREATE OR REPLACE PROCEDURE PROC3
IS
CURSOR CSR3 IS SELECT NO, CODE, NAME,PRICE FROM TBL_PRO		--커서 선언
BEGIN
FOR PRO_ROW IN CSR3 LOOP 		-- 로우변수없이 바로 FOR LOOP
DBMS_OUTPUT.PUT_LINE(PRO_ROW.NO ||''|| PRO_ROW.CODE ||''|| PRO_ROW.NAME ||''|| PRO_ROW.PRICE);
END LOOP; 		-- LOOP문 닫기
EXCEPTION WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR();
END;

여기서 RAISE_APPLICATION_ERROR는
RAISE_APPLICATION_ERROR( [에러코드], [에러메시지] )
에러코드 : -20000 ~ -20999 사이의 코드
사용자가 사용자정의함수만들때 임의로 설정할 수 있는 오류코드이다.

저장프로시저 SELECT 결과값을 JAVA에서 읽으려면 SYS_REFCURSOR타입을 사용해야됨 < DBMS_OUTPUT.PUT_LINE이 없기때문

예)

CREATE OR REPLACE PROCEDURE PROC4
(
CSR4 	OUT 	SYS_REFCURSOR		-- 출력하기위해 OUT매개변수
)
IS
BEGIN
OPEN CSR4	FOR
SELECT NO, CODE,NAME,PRICE 
FROM TBL_PRO;
EXCEPTION WHEN OTHERS THEN
	--예외처리
END;

• OPEN 커서명 FOR 사용하면 SELECT 결과값이 OUT으로 넘어가서 JAVA에서 사용할수있음

---

함수(FUNCTION)

• 사용자정의함수
• 특정기능 수행 후 결과값 리턴

CREATE OR REPACE FUNCTION 함수명
(
파라미터1 데이터타입,
파라미터2 데이터타입
)
RETURN 데이터타입  	--RETURN의 데이터타입
IS
변수 선언
BEGIN
 	--로직
    
    EXCEPTION WHEN OTHERS THEN
    --예외처리
    
END;

예)
주민번호 입력시 성별을리턴함수

CREATE OR REPLACE FUNCTION GET_GENDER
(
F_SSN	VARCHAR2		-- VARCHAR2(10) 이렇게 쓰면안됨
)
RETURN 		VARCHAR2
IS
GENDER 		VARCHAR2(10);
BEGIN 
SELECT CASE WHEN SUBSTR(F_SSN, 7, 1) IN ('1','3') THEN '남'
			ELSE '여' END
INTO GENDER 
FROM DUAL;
RETURN GENDER;
EXCEPTION~ 		--예외

END;

함수실행
SELECT GET_GENDER('283212112111') FROM DUAL;

• extract 함수 : 날짜유형 데이터로부터 날짜 정보를 분리해 새로운 컬럼의 형태로 추출해줌
  extract( YEAR, MONTH, DAY, HOUR, MINUTE, SECOND FROM (SYSDATE 또는 ,,,) )
  등으로 활용
• SUBSTR : 문자열에서 부분문자열 받아옴
  SUBSTR(문자열, 시작위치, 문자길이)-시작위치에서 지정길이까지 / SUBSTR(문자열, 시작위치)-시작위치에서 끝까지

CASE WHEN 조건1 THEN 참이면 값 1
		WHEN 조건2 THEN 참일때 값2
        ELSE 그외 값3
        END

END 뒤에 AS 써서 그리드의 가독성 올림

주민번호 입력해 나이 구하는 함수 - https://bbinya.tistory.com/22#%E-%-C%--%--%EC%A-%--%EC%-E%A-%--%ED%--%--%EB%A-%-C%EC%-B%-C%EC%A-%--%--%EC%-B%A-%ED%--%--%EC%-B%-C%ED%--%A-%EA%B-%B- 님꺼 참조

CREATE OR REPLACE FUNCTION GET_AGE
(
IN_SSN VARCHAR2
)
RETURN NUMBER
IS
AGE NUMBER(3);

BEGIN
SELECT EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(IN_SSN, 1,2) +  
		CASE WHEN SUBSTR(IN_SSN, 7,1) IN ('1','2') 
		THEN 1990 ELSE 2000 END) +1  
INTO AGE 		-- INTO절에 결과를  선언한변수에넣음
FROM DUAL;
RETURN AGE 		--리턴함

EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR');
    RUTURN NULL;
END;

함수실행
SELECT GET_AGE('9403112222222') FROM DUAL;

---

WHERE 1=1 ?

항상 참인 조건이다.
동적쿼리에서 WHERE절 첫번째 조건으로 쓰이며 뒤에 조건을 더 추가해주기 편리하다. 일반 WHERE절은 첫번째 조건 구분이 필요함 AND를 계속 쓰기위해선 첫번째 조건에 AND가 붙을 수 없기 때문임.
동적쿼리에서는 맨앞에 1=1 선언해주면 뒤에 AND를 몇개든지 사용할수 있다~!
SELECT * FROM TBL_BOARD WHERE 1=1 AND ~~
이처럼 조회쿼리에서는 유용하나 수정,삭제는 사용을 지양하자.

<UPDATE ID="UPDATE"> 
	UPDATE MEMBER 
    SET USE_YN = 'N' 
	WHERE 1=1
	<IF TEST="ID != NULL">
    	AND ID = #{ID}
    </IF>
<UPDATE>

만약 ID가 NULL일때 AND 구문은 수행되지않아서

UPDATE MEMBER
SET USE_YN = 'N'
WHERE 1=1

WHERE문이 참이 되기 때문에 모든 USE_YN컬럼이 업데이트 되어버린다.
그래서 NULL체크를 주의해야한다.

---

EXISTS ?

IN과 비슷하지만 적용 범위가 다르다함,,
IN은 ()사이에 특정값이나 서브쿼리가 들어갈수있음
EXISTS의 ()안에는 서브쿼리만 가능
서브쿼리의 결과가 하나라도 있으면 T, 없으면 F
SELECT절에 CASE WHEN EXISTS 으로 조건으로 같이 사용 가능하지만 복잡해져서 별로다
EXISTS의 서브쿼리를 메인쿼리의 JOIN으로 변경하게 되면 중복데이터가 나올수 있다. => 메인과 서브쿼리 데이터가 1:1일때 가능함

SELECT 1 ?

불필요한 컬럼일때 의미없는 1 작성
값의 존재유무가 중요할때 간단히 사용하기위해 SELECT 1 FROM 테이블
특히 WHERE EXISTS절에서 TRUE인 조건이 WHERE의 조건이 된다
즉 조건이 T라면 결과를 출력함

ANY, ALL

ANY 서브쿼리의 값이 어떤값이 라도 만족시 조건 성립 >= ANY 이상, =ANY은 IN
ALL 값보다 큰