16.PL/SQL

PL/SQL

ORACLE에서 SQL언어를 확장해서 사용하는 프로그래밍 언어

PL/SQL ON

>set serveroutput on

변수 선언

DECLARE
	V_NO NUMBER(4);
	V_NAME VARCHAR2(20);

DECLARE
	V_EMPNO	EMP.EMPNO%TYPE;
	V_SAL		EMP.SAL%TYPE;

코드 블럭

BEGIN
	SELECT EMPNO, ENAME
	INTO V_NO, V_NAME
	FROM EMP
	WHERE EMPNO = 7369;
	DBMS_OUTPUT.LINE(V_NO || '' || V_NAME);
END;
/

1. PL/SQL 예제

&ID는 id 값을 입력 받을 수 있음

DECLARE
	V_EMPNO	EMP.EMPNO%TYPE;
    V_SAL	EMP.SAL%TYPE;
BEGIN
	SELECT EMPNO, SAL
	INTO V_EMPNO, V_SAL
	FROM EMP
	WHERE EMPNO = &ID;
	DBMS_OUTPUT.PUT_LINE(V_EMPNO || ' ' || V_SAL);
END;
/

2. 커서

특정 SQL 문장을 처리한 결과를 담고있는 영역을 가리키는 일종의 포인터
커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있음

2.1 묵시적 커서

오라클 내부에서 자동으로 생성되어 사용하는 커서
PL/SQL 블럭에서 DML 문장이 실행될 때마다 자동으로 생성

2.1.1 묵시적 커서 예제

SQL%FOUND : 결과집합의 패치 ROW수가 1개 이상 TRUE, 아니면 FALSE
SQL%NOTFOUND : 결과집합의 패치 ROW수가 0이면 TURE, 아니면 FALSE
SQL%ROWCOUNT : 영향받은 결과 집합 ROW수, 없으면 0
SQL%ISOPEN : 묵시적 커서는 항상 FALSE(묵시적 커서는 실행문 종료 후 바로 닫힘)

2.2 명시적 커서

DECLARE
	V_DEPARTMENT_ID		EMPLOYEES.DEPARTMENT_ID%TYPE := 80;
BEGIN
	UPDATE EMPLOYEES
	SET FIRST_NAME = FIRST_NAME
	WHERE DEPARTMENT_ID = V_DEPARTMENT_ID;
	DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
	COMMIT;
END;
/

DECLARE
	V_DEPTNO	EMP.DEPTNO%TYPE := 20;
BEGIN
	UPDATE EMP
	SET ENAME = 'MR.' || ENAME
	WHERE DEPTNO = V_DEPTNO;
	DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
	COMMIT;
END;
/

3. 반복문

DECLARE
	CURSOR CUR_EMP
	IS
	SELECT (FIRST_NAME || ' '|| LAST_NAME) AS "FULL_NAME"
	FROM EMPLOYEES
	WHERE DEPARTMENT_ID IN (100, 110);
	EMP_FULL_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
	OPEN CUR_EMP;
	LOOP
		FETCH CUR_EMP INTO EMP_FULL_NAME;
		EXIT WHEN CUR_EMP%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(EMP_FULL_NAME);
	END LOOP;
	CLOSE CUR_EMP;
END;
/

4. 함수

CREATE OR REPLACE FUNCTION FN_GET_SALARY
(IN_EMPNO EMP.EMPNO%TYPE)
RETURN EMP.SAL%TYPE
IS
	OUT_SAL EMP.SAL%TYPE :=0;
BEGIN
	SELECT SAL
	INTO OUT_SAL
	FROM EMP
	WHERE EMPNO = IN_EMPNO;
	RETURN OUT_SAL;
END;
/

함수 삭제

DROP FUNCTION FN_GET_SALARY

5. 저장 프로시저(Stored-Procedure)

함수는 반드시 리턴이 있어야 함
저장 프로시저는 리턴이 없어도 됨

CREATE OR REPLACE PROCEDURE SP_GET_SALARY(
    IN_EMPLOYEE_ID	IN	EMPLOYEES.EMPLOYEE_ID%TYPE,
    OUT_SALARY		OUT	EMPLOYEES.SALARY%TYPE
)
IS
BEGIN
	SELECT SALARY
	INTO OUT_SALARY
	FROM EMPLOYEES
	WHERE EMPLOYEE_ID = IN_EMPLOYEE_ID;
END;

DECLARE OUT_SALARY EMPLOYEES.SALARY%TYPE;
BEGIN
	SP_GET_SALARY('101', OUT_SALARY);
	DBMS_OUTPUT.PUT_LINE(OUT_SALARY);
END;

작성한 프로시저 확인 법

SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'PROCEDURE'

6. REF CURSOR

패키지가 필요하기 때문에 패키지 생성이 필요
패키지 해더 작성

CREATE OR REPLACE PACKAGE CURSOR_PKG
AS
	TYPE T_CURSOR IS REF CURSOR;
	PROCEDURE SP_JOB_HISTORY(
        IN_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
        OUT_CURSOR		IN OUT T_CURSOR
    );
END CURSOR_PKG;
/

패키지 바디 생성

CREATE OR REPLACE PACKAGE BODY CURSOR_PKG
AS
PROCEDURE SP_JOB_HISTORY
(
    IN_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    OUT_CURSOR		IN OUT T_CURSOR
)
IS
BEGIN
	OPEN OUT_CURSOR FOR
	SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
	JO.JOB_TITLE AS TITLE,
	JH.START_DATE AS SDATE,
	JH.END_DATE AS EDATE
	FROM EMPLOYEES E JOIN JOB_HISTORY JH
		ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID
		JOIN JOBS JO
        ON JH.JOB_ID = JO.JOB_ID
    WHERE E.EMPLOYEE_ID = IN_EMPLOYEE_ID;
END SP_JOB_HISTORY;
END CURSOR_PKG;
/

DECLARE
	TYPE T_CURSOR IS REF CURSOR;
	RET_CUR T_CURSOR;
	RET_NAME	VARCHAR2(50);
	RET_TITLE	VARCHAR2(50);
	RET_SDATE	DATE;
	RET_EDATE	DATE;
BEGIN
	CURSOR_PKG.SP_JOB_HISTORY('200', RET_CUR);
	LOOP
		FETCH RET_CUR INTO RET_NAME, RET_TITLE, RET_SDATE, RET_EDATE;
		EXIT WHEN RET_CUR%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(RET_NAME || ',' || RET_TITLE || ',' || RET_SDATE || ',' || RET_EDATE);
    END LOOP;
    CLOSE RET_CUR;
END;
/