PL /SQL) 기본

PL/SQL

- SQL을 확장한 절차적 언어(Procedural Language)

- 코드 실행 단위에서 블록 구조를 제공. 잘 정의된 구조로 코드 유지 관리가 쉽다.

--PL-SQL

--PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자

--SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,

--오라클 자체에 내장되어 있는Procedure Language

--DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항.

--PL/SQL 문은 블록 구조로 되어 있고 PL/SQL 자신이 컴파일 엔진을 가지고 있다.

--SQL DEVELOPER 에서 접속 방법

--Tool > 보기 > DBMS 출력창 > + 버튼 클릭 > 사용자 접속(개발자)

--DBMS 출력창 : 이클립스 console 창

--실습 테이블 만들기

create table pl_test(

no number , name varchar2(20) , addr varchar2(50));

<출력문>

-- 콘솔에 출력

BEGIN

DBMS_OUTPUT.PUT_LINE('HELLO WORLD');

END;

<변수 선언>

-- 변수를 선언(변수의 영역은 BEGIN ~ END)

-- 영역의 구분(선언부/실행부/예외부)

DECLARE

vno number(4);

vname varchar2(20);

BEGIN

vno := 100; -- 할당(':=' <-이게 할당의 기호) > String s; s = "홍길동"

vname := 'kglim';

DBMS_OUTPUT.PUT_LINE(vno); --화면 출력

DBMS_OUTPUT.PUT_LINE(vname || '입니다');

END;

<값을 할당 (into)>

-- 값을 할당하기 (into)

-- cf) 아래는 다중 row가 아니라 단일 row를 잡아서 하는 구문

-- 다중 row 잡히게 쿼리 작성하면 에러가 난다

DECLARE

vno number(4);

vname varchar2(20);

BEGIN

select empno ,ename

into vno , vname -- 실행결과 변수에 담기(into)

from emp

where empno=&empno; -- & <- 자바에서의 scanner역할 (입력값 받기)

-- 테스트 환경에서만 사용

DBMS_OUTPUT.PUT_LINE('변수값 : ' || vno || '/' || vname);

END;

-- 3개의 값을 받아서 insert 해보기

DECLARE

v_no number := '&NO';

v_name varchar2(20) := '&NAME';

v_addr varchar2(50) := '&ADDR';

BEGIN

insert into pl_test(no,name,addr)

values(v_no , v_name , v_addr);

commit;

END;

select * from pl_test;

--변수 제어하기(타입)

--1.1 타입 : v_empno number(10)

--1.2 타입 : v_empno emp.empno%TYPE (emp 테이블에 있는 empno 컬럼의 타입 사용)

--1.3 타입 : v_row emp%ROWTYPE (v_row 변수는 emp 테이블 모든 컬럼 타입 정보)

-- ex) v_row는 컬렉션처럼 emp의 모든 컬럼 타입 정보를 담게 된다

-- v_row.empno 와 같이 사용한다

-- 두개의 정수를 입력받아서 그 합을 출력

DECLARE

v_no1 number := '&no1';

v_no2 number := '&no2';

result number;

BEGIN

result := v_no1 + v_no2;

DBMS_OUTPUT.PUT_LINE('result : ' || result);

END;

<%ROWTYPE>

-- ROWTYPE을 가져와서 출력해보기

-- "컬럼명 + 자료타입" 을 가져오게 된다

DECLARE

v_emprow emp%ROWTYPE;

BEGIN

select *

into v_emprow -- empno , ename , ,..... deptno

from emp

where empno=7788;

DBMS_OUTPUT.PUT_LINE(v_emprow.empno || '-' || v_emprow.ename);

END;

-- 기존에 하던 시퀀스 생성문

create sequence empno_seq

increment by 1

start with 8000

maxvalue 9999

nocycle -- 9999가 마지막 값/ 만약 cycle이면 9999에서 다시 1이 된다

nocache;

<%TYPE>

-- 시퀀스를 이용한 값 할당

-- 먼저 empdml 만들어 두기

create table empdml as select * from emp where 1=2;

DECLARE

v_empno emp.empno%TYPE;

BEGIN

select empno_seq.nextval -- 시퀀스에서 생성한 번호를

into v_empno -- 변수에 담아서

from dual;

insert into empdml(empno ,ename)

values(v_empno,'홍길동');

commit;

END;

-- 조회해보기

select * from empdml;

<조건문 IF, ELSIF>

-- 조건문의 활용

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vdeptno emp.deptno%TYPE;

vname varchar2(20) := null;

BEGIN

select empno , ename , deptno

into vempno , vename , vdeptno

from emp

where empno=7788;

-- if(조건문)

IF(vdeptno = 10) THEN vname := 'ACC'; -- if(vdeptno==10) { vname = "ACC"}

ELSIF(vdeptno=20) THEN vname := 'IT'; --ELSEIF 가 아니고 ELSIF

ELSIF(vdeptno=30) THEN vname := 'SALES';

END IF;

-- cf) 조건문 매 쿼리마다 ;가 있음을 숙지

-- 조건문 끝나면 END IF; 꼭 달기

DBMS_OUTPUT.PUT_LINE('당신의 직종은 : ' || vname);

END;

--IF() THEN 실행문

--ELSIF() THEN 실행문

--ELSE 실행문 (선택사항)

--사번이 7788번인 사원의 사번 , 이름 , 급여를 변수에 담고

--변수에 담긴 급여가 2000 이상이면 '당신의 급여는 BIG' 출력하고

--그렇지 않으면(ELSE) '당신의 급여는 SMALL' 이라고 출력하세요

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vsal emp.sal%TYPE;

BEGIN

select empno , ename , sal

into vempno , vename , vsal

from emp

where empno=7788;

--제어문 if(조건문){실행문}

IF(vsal > 2000) THEN

DBMS_OUTPUT.PUT_LINE('당신의 급여는 BIG ' || vsal);

ELSE

DBMS_OUTPUT.PUT_LINE('당신의 급여는 SMALL ' || vsal);

END IF;

END;

<조건문 CASE>

-- CASE문 활용 (자바에서의 switch와 유사)

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vdeptno emp.deptno%TYPE;

v_name varchar2(20);

BEGIN

select empno, ename , deptno

into vempno, vename , vdeptno

from emp

where empno=7788;

-- v_name := CASE vdeptno

-- WHEN 10 THEN 'AA'

-- WHEN 20 THEN 'BB'

-- WHEN 30 THEN 'CC'

-- WHEN 40 THEN 'DD'

-- END;

v_name := CASE

WHEN vdeptno=10 THEN 'AA'

WHEN vdeptno in(20,30) THEN 'BB'

WHEN vdeptno=40 THEN 'CC'

ELSE 'NOT'

END;

DBMS_OUTPUT.PUT_LINE('당신의 부서명:' || v_name);

END;

<반복문 BASIC, WHILE, FOR, CONTINUE>

-- 반복문 (종류별로 정리)

-- Basic loop

/*

LOOP

문자;

EXIT WHEN (조건식)

END LOOP

*/

DECLARE

n number :=0;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('n value : ' || n);

n := n + 1;

EXIT WHEN n > 5;

END LOOP;

END;

/*

WHILE(n < 6)

LOOP

실행문;

END LOOP

*/

DECLARE

num number := 0;

BEGIN

WHILE(num < 6)

LOOP

DBMS_OUTPUT.PUT_LINE('num 값 : ' || num);

num := num +1;

END LOOP;

END;

-- for

-- java for(int i = 0 ; i < 10 ; i++) {}

BEGIN

FOR i IN 0..10 LOOP -- ..은 ~와 같은 의미(0~10)

DBMS_OUTPUT.PUT_LINE(i);

END LOOP;

END;

--위 FOR 문을 사용해서 (1~100까지 합) 구하세요

DECLARE

total number :=0;

BEGIN

FOR i IN 1..100 LOOP

total := total + i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('1~100 총합 : ' || total);

END;

-- CONTINUE 활용

-- 11g 이전 (continue (x))

-- 11g (continue 추가)

DECLARE

total number := 0;

BEGIN

FOR i IN 1..100 LOOP

DBMS_OUTPUT.PUT_LINE('변수 : ' || i);

CONTINUE WHEN i > 5; --skip

total := total + i; -- 1 , 2 , 3 , 4, 5

END LOOP;

DBMS_OUTPUT.PUT_LINE('합계 : ' || total);

END;

<예외처리 (간단한 예제와 함께)>

--활용

DECLARE

v_empno emp.empno%TYPE;

v_name emp.ename%TYPE := UPPER('&name');

v_sal emp.sal%TYPE;

v_job emp.job%TYPE;

BEGIN

select empno , job ,sal

into v_empno, v_job , v_sal

from emp

where ename = v_name;

IF v_job IN('MANAGER','ANALYST') THEN

v_sal := v_sal * 1.5;

ELSE

v_sal := v_sal * 1.2;

END IF;

update emp

set sal = v_sal

where empno=v_empno;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 갱신 되었습니다');

--예외처리

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(v_name || '는 자료가 없습니다');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(v_name || '는 동명 이인입니다');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러가 발생했습니다');

END;

/*

질의는 하나의 행만 RETURN 해야 합니다. PL/SQL 블록 내의 SELECT 문장은 다음 규칙을

적용하는 Embedded SQL 의 ANSI 범주에 속합니다. 질의의 결과는 하나의 행만을 RETURN 해

야 하고 하나의 행 이상 또는 행이 없는 것은 에러를 생성합니다. PL/SQL 은

NO_DATA_FOUND 와 TOO_MANY_ROWS 를 예외로 블록의 예외 섹션에서 추적할 수 있는 표준 예

외를 조성하여 처리 합니다.

*/

select * from emp where ename='SMITH';

rollback;

--PL-SQL 트랜잭션 및 예외 처리하기

DECLARE

v_ename emp.ename%TYPE := '&p_ename';

v_err_code NUMBER;

v_err_msg VARCHAR2(255);

BEGIN

DELETE emp WHERE ename = v_ename;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20002,'my no data found'); --사용자가 예외 만들기

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

v_err_code := SQLCODE;

v_err_msg := SQLERRM;

DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));

DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);

END;