Oracle Stored Procedure 2장(지역변수 선언 및 프로세스)

CREATE OR REPLACE PROCEDURE TESTPROCEDURE (
	v1 IN NVARCHAR2 := 'no Data',
	v2 OUT NVARCHAR2,
	v3 IN OUT NVARCHAR2
)
AS
	strQuery NVARCHAR2(300);
	deptNo NVARCHAR2(2);
	v_cursor SYS_REFCURSOR;
BEGIN
	/*	
		!! 기본 상식 !!
		 : 문자열 조합은 +가 아닌 || 로 수행
		 : 변수 초기화 시 := 로 초기화
	*/
	
	/*
		지역변수 선언 및 익명 블록
		 : Oracle Procedure에서 지역변수를 선언하기 위해서는 DECLARE 및 익명 블록이 필요하다.
	*/
	DECLARE
		s1 NVARCHAR2(20) := 'aa';
		s2 NVARCHAR2(20) := 'bb';
	BEGIN
		-- DECLARE 이후 로직은 익명블록(BEGIN/END)을 통해 수행해야 한다.
		DBMS_OUTPUT.PUT_LINE('s1 : ' || s1 || ' s2 : ' || s2);
	END;
	
	/*
		DBMS_OUTPUT.PUT_LINE()
		 : 출력문. 변수와 pipe line을 통해 함께 출력 할 수 있다.
	*/
	DBMS_OUTPUT.PUT_LINE('Hello World :)');
	
	/*
		DBMS_SQL.RETURN_RESULT() // 커서리턴
		 : 결과(레코드) RETURN. PROCEDURE는 보통 RETURN을 하지 않으며 단일 데이터 RETURN이 필요한 경우 FUNCTION을 통해 반환한다.
		 : 일반적인 변수 반환은 불가능하며 cursor를 통해 RETURN한다.
		 : 동적쿼리(String Query) 실행이 가능하다.
	*/
	-- 커서리턴 예시1
	OPEN v_cursor FOR
	
	SELECT
		*
	FROM
		EMP;
	
	DBMS_SQL.RETURN_RESULT(v_cursor);


	-- 커서리턴 예시2(변수 바인딩)
	deptNo := '10';
	strQuery := '
		SELECT
			EMPNO,
			ENAME,
			JOB,
			DEPTNO
		FROM
			EMP
		WHERE
			DEPTNO = :deptNo	-- 동적쿼리에 변수를 바인드 하고자 할 때는 앞에 콜론(:)을 붙인 후 CURSOR OPEN시 USING절에 바인딩 순서대로 명시한다.
	';

	OPEN v_cursor FOR strQuery
	USING deptNo;
	DBMS_SQL.RETURN_RESULT(v_cursor);

	-- 커서리턴 예시3(변수 바인딩)
	deptNo := '20';
	strQuery := '
		SELECT
			EMPNO,
			ENAME,
			JOB,
			DEPTNO
		FROM
			EMP
		WHERE
			DEPTNO = ''' || deptNo || '''';	-- 문자열 조합으로 변수 바인딩 시에는 콜론 및 USING절이 필요 없다. 
		
	OPEN v_cursor FOR strQuery;
	DBMS_SQL.RETURN_RESULT(v_cursor);

	/*
		동적쿼리
		 - 동적쿼리를 실행함으로서 레코드를 리턴하는 것이 아닌 SELECT INTO와 같은 SQL을 실행하고자 하는 경우 
	*/
	-- 동적쿼리 예시1
	DECLARE
		EMPNO NVARCHAR2(20) := '7839';
		ENAME NVARCHAR2(20) := 'KING';
		DEPTNO NVARCHAR2(20) := '10';
		JOB NVARCHAR2(20);
	BEGIN
		strQuery := '
			SELECT
				JOB
			FROM
				EMP
			WHERE
				EMPNO = :EMPNO
		';
	
		EXECUTE IMMEDIATE strQuery	-- 실행시킬 동적 쿼리.
		INTO JOB					-- 쿼리 결과를 대입할 변수.
		USING EMPNO;				-- 바인딩 변수. 순서대로 명시해야 한다.
	
		DBMS_OUTPUT.PUT_LINE('JOB : ' || JOB);
	END;
	
	-- 동적쿼리 예시2
	DECLARE
		EMPNO NVARCHAR2(20) := '7698';
		DEPTNO NVARCHAR2(20) := '30';
		JOB NVARCHAR2(20);
		HIREDATE DATE;
	BEGIN
		strQuery := '
			SELECT
				JOB,
				HIREDATE
			FROM
				EMP
			WHERE
				EMPNO = :EMPNO
			AND
				DEPTNO = :DEPTNO
		';
	
		EXECUTE IMMEDIATE strQuery
		INTO JOB, HIREDATE
		USING EMPNO, DEPTNO;
	
		DBMS_OUTPUT.PUT_LINE('JOB : ' || JOB || ' HIREDATE : ' || TO_CHAR(HIREDATE, 'YYYY-MM-DD HH24:MI:SS'));
	END;

	EXCEPTION	-- java의 Catch 구문과 동일하다. 위 로직 수행 중 예외 발생 시 프로시저의 흐름은 해당 영역으로 진입한다.
		WHEN NO_DATA_FOUND THEN
			BEGIN
				-- SELECT INTO문 사용 시 조회 결과가 없을 경우 NO DATA FOUND EXCEPTION이 발생, 이 영역에 예외처리 구문 작성
			END;
		WHEN OTHERS THEN -- Java Catch구문에서 Exception 객체로 모든 예외를 받아 처리하는것과 동일
			BEGIN
			END;
END;