[SIST] Oracle_days15
1. PL/SQL
1) SQL 확장 + PL == [P]rocedural [L]anguage extensions to SQL을 의미
2) 이는 비절차적인 SQL 언어를 '절차적'으로 사용할 수 있도록 한다.
(1) 변수 선언
(2) 제어문
(3) 예외 처리 등등
3) PL/SQL은 블록 구조로 된 언어이며, 3가지의 블럭으로 구조되어있다.
[ 선언 기능 블럭 ]
[ 실행 기능 블럭 ]
[ 예외 처리 블럭 ]
4) PL/SQL 선언 형식 ***
【형식】
[ DECLARE ] -- 선언블럭(declarations) : 변수 선언, 상수 선언
BEGIN -- 실행블럭(statements) : INSERT, DELETE, UPDATE 등
[ EXCEPTION ] -- 예외 처리 블럭(handlers)
END;
5) PL/SQL의 블록 작성요령
(1) 블럭 내에 SQL문을 여러 번 사용할 수 있다. ***
(2) 블럭 내에는 CREATEST, LEAST, DECODE, 그룹함수를 사용할 수 없다. ***
(3) 식별자는 최대 30문자로 작성
(4) 식별자는 테이블 또는 컬럼명과 같을 수 없음
(5) 식별자는 알파벳으로 시작해야 함
(6) 문자와 날짜 타입은 단일 인용부호로 표시함
(7) 주석은 단일 라인인 경우 2개의 대시(--), 여러 라인 경우 /* ... */로 표기
6) PL/SQL의 6가지 종류
(1) 익명 프로시저(anonymous procedure)
- DECLARE ...로 시작되며, 사용자가 반복적으로 실행하려는 SQL문을 필요할 때마다 작성하여 실행하는 방법, 데이터베이스에 그 정보가 저장되지 않음
(2) 저장 프로시저(stored procedure)
- CREATE PROCEDURE name ...문에 의해서 생성된 후, 데이터베이스 내에 그 정보가 저장됨. stored procedure는 로직을 처리만 하고 끝남
(3) 저장 함수(stored function)
- stored procedure와 같으며, stored procedure는 로직을 처리만 하고 끝나지만, stored function은 그 처리 결과를 사용자에게 반환함.
(4) 패키지(package)
- 자주 사용되는 여러 procedure, function들을 하나의 package묶어서 관리에 편리토록 함 -> ex) dbms_random 패키지
(5) 트리거(trigger)
- 어떤 작업전, 또는 작업 후 trigger에 정의한 로직을 실행시키는 PL/SQL 블럭임.
(6) 객체 타입(object type)
- 객체에 데이터를 입력, 수정, 삭제, 조회하기 위해서는 반드시 PL/SQL 언어를 사용해야 함
익명 프로시저와 저장 프로시저에 대해서만 배웠기 때문에 2가지에 대해서 정리를 할 것이며, EXCEPTION에 대해서는 아직 배우지 않아 주석처리를 하여 쿼리를 실행하였다.
2. 익명 프로시저(anonymous procedure)
Oracle SQL Developer 프로그램을 실행시킨 후 상단에 보기 탭에 DBMS출력을 누르면 에디터가 나오게 되는데 + 모양을 눌러 접속할 계정을 선택해주면 사용가능하다.
SCOTT 계정에서 아래와 같은 익명프로시저를 작성하고 실행시켜보면 위 그림과 같은 결과가 나온다.
DECLARE
vname VARCHAR2(10);
vsal NUMBER(7, 2);
BEGIN
SELECT ename, sal
INTO vname, vsal -- SELECT한 결과를 DECLATE에 선언된 변수에 담겠다.
FROM emp
WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(vname);
DBMS_OUTPUT.PUT_LINE(vsal);
-- EXCEPTION
-- WHEN THEN
END;
DECLARE에서 선언한 변수를 이용하여
BEGIN에서 SELECT한 데이터를 선언한 변수에 담아서 출력하는 것!
[참고] DBMS_OUTPUT 패키지
- 이 패키지는 PL/SQL 내에서 처리된 어떤 결과를 사용자의 화면에 출력할 때 사용한다.
- DBMS_OUTPUT 패키지의 서브프로그램(함수)은 다음과 같다.
> PUT() 또는 PUT_LINE() : 정의된 문자 값을 화면에 출력하는 프로세서
> NEW_LINE() : GET_LINE에 의해 읽힌 행의 다음 라인을 읽을 때 사용
> GET_LINE() 또는 GET_LINES() : 현재 라인의 문자값을 읽는 프로세서
> ENABLE() : 화면에 문자값을 출력하는 모드로 설정하며 문자값을 지정할 수 있는 버퍼크기를 정의함
> DISABLE() : 화면에 문자값을 출력하는 모드로 해제함
예시1) 홍길동의 이름과 나이를 변수에 저장해서 DBMS로 출력하기
DECLARE
vname VARCHAR2(20);
vage NUMBER(3);
BEGIN
vname := '홍길동';
vage := 20;
DBMS_OUTPUT.PUT_LINE(vname || ', ' || vage);
-- EXCEPTION
END;
여기서 vname := '홍길동'; 을 vname = '홍길동'; 이렇게 쿼리를 작성하면 에러가 발생하니 주의하자!
예시2) 30번 부서의 지역명(loc)를 가져와서 10번 부서의 loc로 수정
- 익명 프로시저 생성 + 실행
DECLARE
-- vloc VARCHAR2(13);
vloc dept.loc%TYPE; -- 타입형 변수(dept 테이블의 loc와 자료형을 똑같이 주겠다.)
BEGIN
SELECT loc INTO vloc
FROM dept
WHERE deptno = 30;
UPDATE dept
SET loc = vloc
WHERE deptno = 10;
-- COMMIT;
-- EXCEPTION
END;
예시3) 기본값 선언해보기
DECLARE
vname VARCHAR2(20);
vage NUMBER(3) := 20; -- 여기서 선언하면 기본값
BEGIN
vname := '익순이';
vage := 28; -- BEGIN에서 값을 넣어주면 기본값이 아닌 이 값으로 출력
DBMS_OUTPUT.PUT_LINE(vname || ', ' || vage);
--EXCEPTION
END;
3-1. %TYPE형 변수
[형식]
변수명 table명.column명%TYPE;
예시)
tempno emp.empno%TYPE;
- tempno 변수의 자료형을 emp 테이블의 empno 컬럼의 자료형을 사용하겠다.
예제)
DECLARE
tempno emp.empno%TYPE;
tdeptno emp.deptno%TYPE;
tename emp.ename%TYPE;
tjob emp.job%TYPE;
tmgr emp.mgr%TYPE;
thiredate emp.hiredate%TYPE;
tpay emp.sal%TYPE;
vmax_pay NUMBER;
BEGIN
-- 1번 수행 *** 서브쿼리를 따로 빼와서 변수에 저장
SELECT MAX(sal+NVL(comm,0)) max_pay
INTO vmax_pay
FROM emp
WHERE deptno = 10;
-- 2번 수행
SELECT empno, deptno, ename, job, mgr, hiredate, sal + NVL(comm, 0) pay
INTO tempno, tdeptno, tename, tjob, tmgr, thiredate, tpay
FROM emp
WHERE deptno = 10 AND sal + NVL(comm, 0) = vmax_pay;
DBMS_OUTPUT.PUT_LINE(tempno || ', '|| tdeptno || ', '|| tename || ', '|| tjob || ', '|| tmgr || ', '|| thiredate || ', '|| tpay);
--EXCEPTION
END;
<결과>
- 수행할 때마다 서브쿼리가 있으면 성능이 떨어지기 때문에 변수를 하나 선언하여 서브쿼리를 따로 담을 수 있도록 한 예제이다.
3-2. %ROWTYPE형 변수
[형식]
변수명 table명%ROWTYPE;
예시)
vemprow emp%ROWTYPE;
- emp 테이블의 한 행(레코드) 전체를 저장할 변수(vemprow) 선언
예제)
DECLARE
-- emp 테이블의 한 행(레코드) 전체를 저장할 변수 선언
vemprow emp%ROWTYPE;
vpay NUMBER;
vmax_pay NUMBER;
BEGIN
-- 1번 수행 *** 서브쿼리를 따로 빼와서 변수에 저장
SELECT MAX(sal+NVL(comm,0)) max_pay INTO vmax_pay
FROM emp
WHERE deptno = 10;
-- 2번 수행
SELECT empno, deptno, ename, job, mgr, hiredate, sal + NVL(comm, 0) pay
INTO vemprow.empno, vemprow.deptno, vemprow.ename, vemprow.job, vemprow.mgr, vemprow.hiredate, vpay
FROM emp
WHERE deptno = 10 AND sal + NVL(comm, 0) = vmax_pay;
DBMS_OUTPUT.PUT_LINE(vemprow.empno || ', '|| vemprow.deptno || ', '|| vemprow.ename || ', '|| vemprow.job
|| ', '|| vemprow.mgr || ', '|| vemprow.hiredate || ', '|| vpay);
-- EXCEPTION
END;
<결과>
<주의>
- PL/SQL 문 안에서 SELECT한 처리 결과가 여러 개의 행을 반환할 경우에는 아래와 같이 에러가 발생한다.
- 에러 : ORA-01422: exact fetch returns more than requested number of rows
- 에러 발생한 쿼리 :
DECLARE
vename emp.ename%TYPE;
vjob emp.job%TYPE;
BEGIN
SELECT ename, job -- 12명 사원 == 12 row (행)
INTO vename, vjob
FROM emp;
--WHERE empno = 7369;
--EXCEPTION
DBMS_OUTPUT.PUT_LINE(vename || ',' || vjob);
END;
- 해결방법 : 커서를 사용하면 가능하다. 후반부에 배웠기 때문에 하단 내용을 확인하면 해결할 수 있을 것!
3-3. RECORD
- 사용자가 정의하는 새로운 구조의 자료형 => '사용자 정의 구조체'
예시)
DECLARE
-- 사용자가 정의하는 새로운 구조의 자료형 => '사용자 정의 구조체'
TYPE EmpDeptType IS RECORD -- 구조체 이름 정의
(
vdeptno dept.deptno%TYPE,
vdname dept.dname%TYPE, -- 구분자는 ,(콤마)!!
vempno emp.empno%TYPE,
vename emp.ename%TYPE,
vpay NUMBER
);
vrow EmpDeptType; -- RECORD형 변수 선언
BEGIN
SELECT d.deptno, dname, empno, ename, sal + NVL(comm, 0) pay
INTO vrow.vdeptno, vrow.vdname, vrow.vempno, vrow.vename, vrow.vpay
FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(vrow.vdeptno || ', ' || vrow.vdname || ', ' || vrow.vempno || ', ' || vrow.vename || ', ' || vrow.vpay);
-- EXCEPTION
END;
<결과>
4. CURSOR
1) CURSOR 란? PL/SQL 블럭 내에서 실행되는 SELECT문을 의미
2) 여러 개의 레코드를 처리하기위해서 커서(CURSOR)를 사용해야된다.
3) 커서의 2가지 종류
ㄱ. implicit cursor 묵시적(암시적, 자동) 커서
예시)
DECLARE
-- vrow 선언X 왜? RECORD형 변수를 선언했기 때문에
BEGIN
FOR vrow IN (SELECT empno, ename, job FROM emp)
LOOP
DBMS_OUTPUT.PUT_LINE(vrow.empno || ', ' || vrow.ename || ', ' || vrow.job);
END LOOP;
--EXCEPTION
END;
ㄴ. explicit cursor 명시적 커서
(1) 커서 선언
(2) 커서 OPEN
(3) LOOP
-- 커서로부터 SELECT한 것을 가져오는 코딩(FETCH)
EXIT WHEN 커서가 읽을 것이 없을 때 까지의 조건 (%NOTFOUND 참이될 때 까지)
END LOOP;
(4) 커서 CLOSE
4) CURSOR 형식 및 CURSOR 속성
[커서선언형식]
CURSOR 커서명 IS (서브쿼리);
OPEN [커서명];
FOR
FETCH [커서명] INTO [변수명];
EXIT WHEN [조건절];
END LOOP;
CLOSE [커서명];
[커서의 속성]
> %ROWCOUNT 실행된 커서문장에서 읽힌 행의 수
> %FOUND 실행된 커서문장에서 검색된 행이 발견되었는지 알 수 있는 속성
> %NOTFOUND 실행된 커서문장에서 검색된 행이 발견되지 않았음을 알 수 있는 속성
> %ISOPEN 선언된 커서가 현재 OPEN되어 있는지를 반환
5) 예시
방법1)
DECLARE
vename emp.ename%TYPE;
vsal emp.sal%TYPE;
vhiredate emp.hiredate%TYPE;
-- 1) 커서 선언 : CURSOR 커서명 IS (서브쿼리);
CURSOR emp30_cursor IS(
SELECT ename, sal, hiredate
FROM emp
WHERE deptno = 30
);
BEGIN
--2) OPEN : OPEN 커서명;
OPEN emp30_cursor;
--3) LOOP ~ FETCH 작업(반복적으로 가져오는 작업)
LOOP
FETCH emp30_cursor INTO vename, vsal, vhiredate;
DBMS_OUTPUT.PUT_LINE(vename || ', ' || vsal || ', ' || vhiredate );
EXIT WHEN emp30_cursor%NOTFOUND;
END LOOP;
--4) CLOSE : CLOSE 커서명;
CLOSE emp30_cursor;
-- EXCEPTION
END;
방법2)
DECLARE
CURSOR emp_cursor IS( SELECT empno, ename, job FROM emp );
BEGIN
FOR vrow IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE(vrow.ename || ', ' || vrow.ename || ', ' || vrow.job );
END LOOP;
-- EXCEPTION
END;
방법3)
DECLARE
vename emp.ename%TYPE;
vsal emp.sal%TYPE;
vhiredate emp.hiredate%TYPE;
-- 1) 커서 선언 : CURSOR 커서명 IS (서브쿼리);
CURSOR emp30_cursor IS(
SELECT ename, sal, hiredate
FROM emp
WHERE deptno = 30
);
BEGIN
--2) OPEN : OPEN 커서명;
OPEN emp30_cursor;
--3) LOOP ~ FETCH 작업(반복적으로 가져오는 작업)
LOOP
FETCH emp30_cursor INTO vename, vsal, vhiredate;
DBMS_OUTPUT.PUT_LINE(vename || ', ' || vsal || ', ' || vhiredate );
EXIT WHEN emp30_cursor%NOTFOUND OR emp30_cursor%ROWCOUNT >= 3;
END LOOP;
--4) CLOSE : CLOSE 커서명;
CLOSE emp30_cursor;
-- EXCEPTION
END;
<결과>
5. PL/SQL의 제어문
1) IF THEN ELSE 문
(1) IF문 형식
IF( 조건식 ) THEN
-- 코딩
IF 조건식 THEN
-- 코딩
END IF;
-> IF 조건식을 쓸 때 괄호를 써도 되고, 안써도 된다.
(2) IF ELSE문 형식
IF( 조건식 ) THEN
-- 코딩
ELSE
-- 코딩
END IF;
(3) ELSEIF문 형식
IF( 조건식 ) THEN
-- 코딩
ELSIF( 조건식 ) THEN
-- 코딩
ELSIF( 조건식 ) THEN
-- 코딩
ELSIF( 조건식 ) THEN
-- 코딩
ELSE
-- 코딩
END IF;
예시1) 변수를 하나 선언해서 정수를 입력받아서 짝수/홀수 출력
DECLARE
vnum NUMBER := 0;
vresult VARCHAR2(20);
BEGIN
vnum := :bindNumber; -- 변수를 입력받아서 대입하겠다.
IF(mod(vnum, 2) = 0) THEN
vresult := '짝수';
ELSE
vresult := '홀수';
END IF;
DBMS_OUTPUT.PUT_LINE(vresult);
-- EXCEPTION
END;
예시2) 국어 점수를 입력받아서 수우미양가라고 출력하는 익명 프로시저 작성
ELSEIF문 사용)
DECLARE
kor NUMBER(3) := 0;
grade VARCHAR2(3) := '가';
BEGIN
kor := :bindNumber;
IF kor >= 90 THEN
grade := '수';
ELSIF kor >= 80 THEN
grade := '우';
ELSIF kor >= 70 THEN
grade := '미';
ELSIF kor >= 60 THEN
grade := '양';
ELSE
grade := '가';
END IF;
DBMS_OUTPUT.PUT_LINE(grade);
-- EXCEPTION
END;
CASE문 사용)
DECLARE
kor NUMBER(3) := 0;
grade VARCHAR2(3) := '가';
BEGIN
kor := :bindKor;
kor := TRUNC(kor / 10);
CASE kor
WHEN 10 THEN grade := '수';
WHEN 9 THEN grade := '수';
WHEN 8 THEN grade := '우';
WHEN 7 THEN grade := '미';
WHEN 6 THEN grade := '양';
ELSE grade := '가';
END CASE;
DBMS_OUTPUT.PUT_LINE(grade);
-- EXCEPTION
END;
2) FOR...LOOP 문(제한적 반복)
【형식】
FOR counter변수 IN [REVERSE] 시작값 .. 끝값
LOOP
실행문; -- 반복처리할 코딩
END LOOP;
예시1) 1 ~ 10까지 합을 출력
DECLARE
vi NUMBER;
vsum NUMBER := 0;
BEGIN
FOR vi IN 1..10
LOOP
vsum := vsum + vi;
-- DBMS_OUTPUT.PUT_LINE(vi || '+' );
IF(vi = 10) THEN
DBMS_OUTPUT.PUT(vi);
ELSE
DBMS_OUTPUT.PUT(vi || '+' );
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('=' || vsum );
-- EXCEPTION
END;
<결과>
3) WHILE...LOOP 문(제한적 반복)
[형식]
WHILE 조건
LOOP
실행문; -- 조건이 참일동안 실행되는 문
END LOOP;
예시1) 1 ~ 10까지 합을 출력
DECLARE
vi NUMBER := 1;
vsum NUMBER := 0;
BEGIN
vi := 1;
WHILE (vi <= 10)
LOOP
DBMS_OUTPUT.PUT(vi || '+');
vsum := vsum + vi;
vi := vi + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('=' || vsum);
-- EXCEPTION
END;
4) LOOP END LOOP 문(단순 반복)
[형식]
LOOP
[EXIT WHEN 빠져나가는조건;]
실행문;
EXIT WHEN 빠져나가는조건;
END LOOP;
> EXIT WHEN 빠져나가는조건;은 어디에 있어도 상관없으나 없으면 무한루프에 빠지게 되니 주의하자!
예시1) 1 ~ 10까지 합을 출력
DECLARE
vi NUMBER := 1;
vsum NUMBER := 0;
BEGIN
vi := 1;
LOOP
EXIT WHEN vi = 11;
DBMS_OUTPUT.PUT(vi || '+');
vsum := vsum + vi;
vi := vi + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('=' || vsum);
-- EXCEPTION
END;
예시) 구구단
1) FOR LOOP문 2개 사용
DECLARE
vdan NUMBER(1);
vi NUMBER(1);
BEGIN
FOR vdan IN 2..9
LOOP
FOR vi IN 1..9
LOOP
DBMS_OUTPUT.PUT(vdan || '*' || vi || '=' || RPAD(vdan * vi, 4, ' ') );
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
-- EXCEPTION
END;
2) WHILE LOOP문 2개 사용
DECLARE
vdan NUMBER(2) := 2;
vi NUMBER(2) := 1;
BEGIN
WHILE (vdan <= 9)
LOOP
vi := 1;
WHILE (vi <= 9)
LOOP
DBMS_OUTPUT.PUT(vdan || '*' || vi || '=' || RPAD(vdan * vi, 4, ' ') );
vi := vi + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
vdan := vdan + 1;
END LOOP;
END;
3) LOOP END LOOP 문 사용
DECLARE
vdan NUMBER;
vnum NUMBER;
BEGIN
vdan := 2;
LOOP
vnum := 1;
LOOP
DBMS_OUTPUT.PUT_LINE(vdan || '*' || vnum || '=' ||vdan*vnum);
vnum := vnum +1;
EXIT WHEN (vnum > 9);
END LOOP;
vdan := vdan +1;
EXIT WHEN (vdan > 9);
END LOOP;
--EXCEPTION
END;
6. 저장 프로시저(stored procedure)
1) PL/SQL 6가지 중에 가장 대표적인 구조
2) 개발자가 자주 실행해야 하는 업무를 이 문법에 의해 미리 작성하고 DB 내에 저장해 두었다가 필요할 때 마다 호출해서 사용할 수 있다. (성능 때문에..)
3) 저장 프로시저 선언 형식
CREATE OR REPLACE PROCEDURE 프로시저명
(
-- 파라미터를 여러개 준다면 ,(콤마) 구분 / 자료형은 크기 설정 안한다.
파라미터 선언 MODE (IN/OUT/INOUT) 자료형, -- 기본은 IN(입력용)
파라미터 선언 MODE (IN/OUT/INOUT) 자료형, -- 기본은 IN(입력용)
파라미터 선언 MODE (IN/OUT/INOUT) 자료형 -- 기본은 IN(입력용)
)
IS -- DECLARE 대신에 사용함
-- 변수,
-- 변수,
-- 변수,
BEGIN
-- 실행 쿼리
EXCEPTION
-- 예외처리
END;
4) 저장 프로시저 사용하는 방법
ㄱ. EXECUTE문 실행
ㄴ. 또 다른 저장 프로시저 안에서 호출해서 실행
ㄷ. 익명 프로시저에서 호출할 수 있음
5) 예시
(1) 프로시저 생성
CREATE OR REPLACE PROCEDURE up_delDept
(
-- 파라미터 MODE IN 자료형,
pdeptno IN NUMBER -- 삭제하고자 하는 부서번호를 입력받을 파라미터(인자)
)
IS
-- 변수
BEGIN
-- 실행
DELETE FROM dept
WHERE deptno = pdeptno;
-- COMMIT; -- 커밋 또는 롤백해줘야 작업이 완료됨. 현재는 다시 돌릴 거라 하지 않음
-- EXCEPTION
END up_delDept;
--> rocedure UP_DELDEPT이(가) 컴파일되었습니다.
(2) 익명 프로시저에서 저장 프로시저 호출
DECLARE
BEGIN
up_deldept(40); -- 40이 pdeptno임!
--EXCEPTION
END;
--> PL/SQL 프로시저가 성공적으로 완료되었습니다.
(3) EXECUTE에서 사용
EXECUTE up_deldept(40);
ROLLBACK;
