기록

주제선정

논리적 모델링 Logical Modeling

알아볼 수 있게 대략적인 설계

테이블 생성 시 PK가 반드시 필요하다.

회원 가입은 회원의 ID, 게시판은 글번호를 설정하는 것이 보편적이다.

없는 테이블도 있지만, 데이터를 제어하기 위해선 PK가 있는 것이 편리하다.

물리적 모델링 Physical Modeling

테이블의 물리 이름 지정,

컬럼별 물리 이름 지정,

(이름 지정시 프로그램의 예약어로 지정된 것을 사용하면 나중에 배포시 에러

포워드 엔지니어링

배포

리버스 엔지니어링

포워드와 반대. 데이터를 가져와 모델링한 형태로 보여준다.

아주 간단히 보고 넘어갔다.

sys 계정 소유의 dual 테이블을 검색해본다.

varchar 타입의 컬럼 하나만 존재하며, 데이터 또한 의미가 없다.

의미없는 데이터가 존재하고 단일 행, 단일 컬럼이 존재하는 이 dual 테이블은 다음과 같은 용도로 사용한다.

select 10+20 from dept;

-> dept의 로우 데이터는 4개이다.

따라서 10+20 연산 결과가 4번 출력된다.

그러나 dual 테이블을 사용해 SQL문을 작성한다면 결과는 1번 출력된다.

단일 로우 데이터를 가진 테이블이기 때문이다.

select 10+20 from dual;

DUAL

1) dual은 sys 계정 소유의 테이블로, 'dual'은 공개 동의어로 설정되어 있다.

때문에 어느 계정에서든 사용 가능하다.

scott 계정에서 검색을 할 때에는 
select*from sys.dual; 로 해야 하지만, 공용 동의어 설정으로 sys.dual;이 아닌 dual;로 호출이 가능하다.

2) dual 테이블을 구성하는 데이터는 varchar2(1) 데이터 1개이다.
때문에 연산 결과를 한번만 출력한다.

함수

오라클에서 함수라고 부르는 이 기능은 자바의 메소드 같은 것이다.
함수는 함수(); <- 괄호가 있는 형식이다. (예외로 날짜 함수인 sysdate)

자주 쓰이는 숫자 함수

1) abs()

절대값을 구하는 함수라, 주어진 데이터가 음수여도 양수로 반환한다.

2) floor()

소수점 아래를 버려서 실수를 정수로 반환한다.

3) round(대상숫자, 지정자리수)

지정한 자리수에서 반올림을 해서 반환한다.

그리고 해당 자리수까지 소수점을 표시한다.

지정 자리수 자리에 양수 2를 넣는다면 소수점 두번째에서 반올림 후 nn.xx 까지 반환하여 출력한다.

만약 음수 2를 넣는다면 소수점 '.' 를 기준으로 -1은 4, -2는 3에서 반올림을 한다.

4) trunc(대상숫자, 지정자리수)

지정 자리수 이하의 소수점 숫자들을 버린다.

지정 자리수 자리에 양수 2를 넣는다면 소수점 두번째에서 반올림 후 nn.xx 까지 반환하여 출력한다.

만약 음수 2를 넣는다면 소수점 '.' 를 기준으로 -1은 4, -2는 3에서 반올림을 한다.

5) mod(대상 숫자, 나눌 숫자)

나누기 연산 실행, 몫이 아닌 나머지를 반환하는 함수이다.

자주 쓰이는 숫자 함수

1) upper('문자열'), lower('문자열)

괄호안의 모든 문자열을 대문자 또는 소문자로 변환한다.

2) initcap('문자열')

띄어쓰기를 기준으로 단어의 첫글자는 대문자, 나머지는 소문자로 변환한다.

3) substr('문자열', 시작위치 , 잘라낼개수)

문장 'Drive my car'에서 D가 1부터 시작한다.

잘라낼개수는 생략해도 된다.

만약 잘라낼개수를 5라고 입력한다면, 시작 위치에서부터 5글자가 반환된다.

4) instr('문자열', '찾을글자', 시작위치, 몇번째 발견)

문자열에서 특정 문자가 나타나는 위치를 알려준다.

시작위치와 몇번째발견은 생략할 수 있고, 그렇게 한다면 기본값이 1로 설정된다.

'strawberry fields forever' 라는 문장에서 1번째부터 시작하여 2번째에 위치한 'r'의 위치를 찾아보는 함수를 사용했고,

그 결과 instr의 값은 8이이다.

1번째 문자가 s고 거기서부터 시작하면 두 번째로 사용된 r의 위치값은 8이기 때문이다.

5) length(), lengthb()

공백을 포함한 문자열의 길이를 숫자로 반환한다.

단, length의 경우 한글 영문 둘 다 한글자에 1씩 증가하지만,

lengthb는 한글은 한글자에 3byte씩 증가한다.

'drive my car'는 공백 포함 12글자이다.

length, lengthb 모두 값이 12이다.

'깊은 산 속 옹달샘' 은 한글은 7글자, 공백이 3글자이다.

그래서 length는 10이 나왔지만 lengthb는 7*3 = 21byte, 공백 3글자로

총 24라는 값이 나왔다.

6) lpad, rpad(문자열, 원하는글자길이, 채워넣을문자)

대상 문자열에 오른쪽이나 왼쪽에 사용자가 지정한 문자를 채워넣어 지정한 길이로 반환한다.

drive my car라는 12글자 짜리 문장에 #을 왼쪽이나 오른쪽에 채워넣어

총 20글자의 문자열로 반환하라는 함수를 사용하면

아래와 같이 #이 8번 들어가서 20글자짜리 데이터를 담은 컬럼이 나온다.

7)ltrim(), rtrim(), trim()

문자열의 앞 뒤 공백을 삭제한다.

단, 문자열 중간의 공백은 삭제하지 않는다.

특정 테이블이 변경되면,

연쇄적으로 다른 테이블이 자동 변경되도록 설정하는 프로시저의 일종.

형식 :

create trigger 트리거이름
timing [before or after] event [DML 명령문 (insert or update or delete)] on 테이블 이름
[for each row]
[when 조건문]
BEGIN
	실행문
END

before or after

목표 테이블을 대상으로 DML 명령문이 실행되기 직전이나 이후에

트리거의 BEGIN ~ END 사이의 문장을 실행

body

BEGIN ~ END 구간에 기술, 이벤트 발생시 실행될 로직이 포함되는 부분.

for each row

문장 레벨 트리거 정의 시 생략 가능하다.

그러나 행 레벨 트리거를 정의할 때에는 반드시 기술해야 한다.

문장 레벨 트리거

트리거가 설정된 테이블에 대해 DML 명령문을 실행하면, 단 한번 트리거를 발생시킨다.

행 레벨 트리거

DML 명령문으로 여러 행이 변경된다면, 각 행이 변경될때마다 트리거가 발생한다.

5개의 행이 변경된다면 트리거도 5번 발생한다.

사원테이블에 새로운 데이터가 들어오면 '신입사원이 입사했습니다.'라는 메시지가 출력되는

문장 레벨 트리거

1) 실습용으로 데이터가 없는 emp01 테이블을 생성한 후, 트리거 작성

create or replace trigger trg_01
after insert on emp01   -- 이벤트 발생
BEGIN
    dbms_output.put_line('신입사원이 입사했습니다.');
END;
/

2) 트리거 목록 확인

select*from user_triggers;

3) 이벤트 발생시키기

: emp 01 테이블에 데이터 입력를 입력하면 이벤트가 발생한다.

사원테이블에 새로운 데이터가 들어오면(신입 사원이 들어오면)

급여 테이블에 새로운 데이터가 자동으로 생성되는 (신입 사원의 급여 정보)

행 레벨 트리거 작성

(단, 신입사원의 급여는 일괄 100)

1) 급여 정보를 저장할 테이블 sal01을 새로 생성하고, 일련번호가 자동 생성되도록 시퀀스도 생성했다.

2) 트리거 생성

create or replace trigger trg_02
after insert on emp01
for each row
BEGIN
    insert into sal01 values(
    sal01_salno_seq.nextval, 100, :new.empno);
END;
/

3) 이벤트 발생 :

emp01 테이블에 새로운 데이터를 삽입한다. (신입사원 입사)

그리고 emp01을 조회후, sal01에도 데이터가 잘 들어갔는지 확인해본다.

4) 일반적인 delete 문으로 emp01의 사원 정보를 삭제하려고 하면 지워지지 않는다.

왜냐면 sal01.empno는 emp01.empno를 참조하는 외래 키 조건을 붙여서 생성했기 때문이다.

그러나 트리거문으로는 삭제가 가능하다.

↓ 무결성 제약 조건을 위배했기 때문에 삭제할 수 없다는 오류 발생.

5) emp 01의 사원 정보가 삭제되면 sal01의 해당 사원 로우 데이터도 같이 삭제되는 트리거문을 작성해본다.

create or replace trigger trg_03
after delete on emp01
for each row
BEGIN
    delete from sal01 where empno = :old.empno;
END;
/

6) emp01의 사원 정보를 삭제해본다.

emp01과 sal01을 조회해보면 데이터가 삭제되어 있다.

패키지

저장 프로시저와 저장 함수의 묶음.

지금까지 PL / SQL 을 공부하면서 콘솔창에 메시지를 출력할 때는 다음과 같은 코드를 사용했다.

DBMS_OUTPUT.PUT_LINE ('Hello World!');

여기서 PUT_LINE 은 오라클이 제공해주는 프로시저로, DMBS_OUTPUT 패키지 소속이다.

이것을 사용할 때는 DBMS_OUTPUT.PUT_LINE 으로 사용을 해 왔다.

하나의 패키지는 헤드와 바디로 구성되어 있다.

헤드에는 저장할 프로시저나 함수를 선언하고

바디에는 구체적인 내용이 들어간다.

--HEAD

create or replace package 패키지이름
is
PROCEDURE 프로시저1
PROCEDURE 프로시저2
...
end;
/

--BODY

create or replace package body 패키지이름
is
PROCEDURE 프로시저1
is
PROCEDURE 프로시저2
...
end;
end;
/

앞서 저장 프로시저와 저장 함수를 공부하면서 만들었던 것들을 묶어

패키지를 만들어 보는 실습을 진행하였다.

1) 패키지 헤드 생성

2) 패키지 바디 생성

3) exam_pack 소속의 저장 프로시저 cursor_sample02를 실행해본다.

4) exam_pack 소속의 매개변수가 있는 저장 함수 cal_bonus를 실행해본다.

5) exam_pack 소속의 매개변수가 있는 저장 함수 cal_bonus를 select 문으로 실행해본다.

지금까지 PL / SQL을 학습하면서 하나의 데이터를 처리했지만,

커서는 두 개 이상의 데이터를 처리할 수 있다.

형식:

DECLARE							-- 커서 선언
	CURSOR 커서이름IS 실행문;	 -- 보통 select SQL문이 실행문에 들어간다.

BEGIN 							-- 커서 열기
	OPEN 커서이름;
    	FETCH 커서이름 INTO 바인드변수;
   	CLOSE 커서이름; 			--커서닫기
END;
/

OPEN

FETCH

이것만 봐선 무슨 소린지 모르겠다..

예제를 통해서 살펴보아야 겠다.

부서 테이블의 모든 데이터를 출력하기 위한 PL/SQL문 작성

1) 저장 프로시저를 생성한다.

set SERVEROUTPUT ON

create or replace procedure cursor_sample01

is
    vdept dept%rowtype;
    
    cursor c1 is
    select * from dept;    
    
BEGIN
    dbms_output.put_line('부서번호 / 부서명 / 지역명');
    dbms_output.put_line('------------------------');    
    
    OPEN c1;
    LOOP
        FETCH c1 into vdept.deptno, vdept.dname, vdept.loc; -- 인출
            exit when c1%notfound; -- 커서가 가져올 데이터가 없을 때 TRUE
            dbms_output.put_line(vdept.deptno || ' / ' || vdept.dname || ' / ' || vdept.loc);
    END LOOP;
    CLOSE c1;
    
END;
/

2) 프로시저 실행.

매개변수가 없으므로 프로시저 명만 입력해도 잘 실행된다.

execute cursor_sample01;

3) 출력한다.

여전히 잘 모르겠지만 일반 loop문 대신 for loop 문으로 같은 내용을 출력해보는 실습을 했다.

for loop문의 장점은 OPEN ~ FETCH ~ CLOSE 없이 보다 간결하게 SQL문을 작성할 수 있다는 점인데,

각 반복문마다 커서를 열고, 각 행을 인출 fetch 하고, close로 커서를 닫는 작업을 자동으로 처리해준다.

set SERVEROUTPUT ON

create or replace procedure cursor_sample02
is
dept dept%rowtype;
cursor c1
is 
select*from dept;

BEGIN
    dbms_output.put_line('부서번호 / 부서명 / 지역명');
    dbms_output.put_line('------------------------');    
    FOR vdept IN c1 LOOP 
    	EXIT WHEN c1%notfound;
        dbms_output.put_line(vdept.deptno || ' / ' || vdept.dname || ' / ' || vdept.loc);
    END LOOP;
END;
/

저장 프로시저와 유사한 용도로 사용하지만 

다른 점은 함수는 Return 구문을 사용해서 실행 결과를 되돌려 받을 수 있다는 것이다.

결과를 되돌려받기 위해 자료형과 값을 기술해야 한다.

생성 :

create or replace function 저장함수이름
(매개변수 [in/out/inout] 데이터타입,
...)
is
RETURN 데이터타입;
BEGIN
실행문1;
실행문2;
RETURN 돌려줄값
END;
/

실행 : 

execute :variable_name := 함수이름(매개변수);

예제로 사원 테이블에서 특정 사원의 급여를 200% 인상하는 저장 함수를 작성해 보았다.

1) 저장 함수 생성

create or replace function cal_bonus(
    vempno in emp.empno%type)
RETURN number
is
    vsal number(7,2);
BEGIN
    select sal into vsal from emp 
    where empno = vempno;
    RETURN vsal * 2;
END;
/

2) 돌려받을 값을 저장할 바인드 변수를 생성한다.

바인드 변수 생성문은 실행해도 콘솔창에 별다른 변화가 없다.

variable var_sal number;

3) 함수 실행

괄호 안에 넣는 사원 번호를 매개로 하여 해당 사원의 급여를 200% 인상한 값을 가져온다.

execute :var_sal := cal_bonus(7788);

5) 값을 출력한다.

사원번호가 7788인 사원의 200% 인상된 급여는 6000이다.

print var_sal;

6) cal_bonus 저장 함수를 select SQL문에 포함하여 실행해 보았다.

select ename, sal, cal_bonus(7788) as "200%인상" from emp where empno=7788;

IN 

매개변수로 값을 받는다 --> 기본값! 생략 가능하다.

OUT

매개변수로 값을 돌려준다.

INOUT

두가지 목적에 모두 사용된다.

바인드 변수

프로시저를 실행했을 때 결과를 저장할 변수

사원번호를 매개변수로 전달 받아 그 사원의 이름, 급여, 직책을 구하는 프로시저를 생성해본다.

1) 프로시저 생성

create or replace procedure sal_empno
    (vempno in emp.empno%type,
    vename out emp.ename%type,
    vsal out emp.sal%type,
    vjob out emp.job%type)
is
BEGIN
    select ename, sal, job into vename, vsal, vjob from emp
    where empno = vempno;
END;
/

2) 바인드 변수를 생성한다.

사원번호를 입력하면 (in)

사원명, 급여, 직급(out) 3개 정보를 출력해야 하니 바인드 변수도 3개를 생성한다.

variable var_ename varchar2(12);
variable var_sal number;
variable var_job varchar2(10);

3) 프로시저를 실행한다.

이 때, 바인드 변수를 넣는 형식이 정해져 있다.

바인드 변수는 :바인드변수명 형식으로 적어야 한다.

execute sal_empno(7788, :var_ename, :var_sal, :var_job);

4) 매개변수로 가져와 바인드 변수에 저장한 값을 출력해본다.

print var_ename;
print var_sal;
print var_job;

자꾸 오류 메시지만 떠서 대체 뭐가 잘못 됐나 알 수가 없었는데,

처음 프로시저 생성시 where 조건절에 내가 입력할 매개변수가 사원번호인데, 사원명이라고 입력을 해놓아서 계속 오류가 났었고 이걸 찾느라 40분이 걸렸다... 정말 별 거 아닌건데 ^_ㅠ

결과

사용자가 임의로 만든 PL/SQL문을 저장해놓고 필요한 경우 호출하여 사용하면 보다 효율적으로 작업을 처리할 수 있다.

오라클은 사용자가 만든 이 임의의 PL/SQL문을 데이터베이스에 저장해두고 필요할때 꺼내 쓸 수 있는 기능을 제공하는데,

이것이 저장 프로시저이다.

create (or replace) procedure 프로시저이름
(매개변수 [in or out] 
is
	지역변수..?
begin
	실행문1
	실행문2
end;
/

() 괄호는 꼭 쓰지 않아도 된다. 필요한 경우에만 사용하는데,

이 괄호 안의 매개변수 값에 따라 다른 동작을 수행한다.

매개변수는 [in], [out], [inout] 둘 중 하나를 택하도록 되어 있다. 기본값이 [in] 이므로 [out]과 [inout]일때만 기재한다.

실습용으로 emp의 데이터가 모두 들어있는 복사본 테이블 emp01을 생성했다.

이 emp01의 데이터를 모두 삭제하는 프로시저를 만들어 볼 예정이다.

프로시저 생성 성공

create or replace procedure del_all
is
BEGIN
    delete from emp01;
END;
/

프로시저가 잘 생성되었는지 확인해본다.

select*from user_sources;

user_procedures; 가 아니다!

execute 명령어로 실행한다.

execute 또는 exec로 실행 가능하다.

execute del_all;
exec del_all;

emp01을 조회해보면 모든 데이터가 삭제되어 있다.

프로시저 삭제

drop procedure del_all;

지금까지 간단한 프로시저를 생성-삭제해보았는데,

이번에는 매개변수를 가진 프로시저를 생성하여

특정 사원의 이름을 입력하면 그 사원의 데이터를 삭제하는 프로시저를 만들어본다.

create or replace procedure del_ename
(vename in emp01.ename%type) -- in 생략가능
is
BEGIN
    delete from emp01 where ename = vename;
END;
/

위의 실행 명령문 그대로 실행하면 오류가 나타난다.

매개변수가 있기 때문에 괄호 안에 해당 사원의 이름을 입력해주어야만 제대로 실행이 된다.

위 에러 화면과는 다르게 완료되었다는 메시지가 나온다.

execute 프로시저이름(매개변수);

execute del_ename('JAMES');

emp01 테이블 목록을 출력해보면 JAMES 사원이 사라져 사원은 13명 뿐이다.

1. 조건 없이 반복 작업을 하기 위한 BASIC LOOP
2. COUNT를 기본으로 작업의 반복 제어를 하는 FOR LOOP
3. 조건을 전제로 작업의 반복 제어를 하기 위한 WHILE LOOP
4. 종료를 위한 EXIT

1. BASIC LOOP

형식

loop
	반복 실행될 문자;
    	조건식 exit;
end loop;

Q. 1 ~ 5 까지 출력하는 예제

set serveroutput on

DECLARE
    n number := 1; -- 변수의 초기값을 1로 설정

BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(n);
        n := n + 1;
        IF n > 5 THEN exit;
        END IF;
    END LOOP;
END;
/

만약 If ~ then ~ end if 조건문을 설정하지 않으면 무한 루프가 돌아갈 것 같지만,

number 자료형의 용량 한계로 인해 185184까지만 출력된다.

Q. 1+2+3+...+10 의 값을 출력하는 예제

set serveroutput on

DECLARE
    n number := 1;  --루프를 돌릴 변수
    s number := 0;  --합이 누적될 변수

BEGIN
    LOOP
        s := s + n;
        n := n + 1;
        
        IF n > 10 THEN exit; 
    END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~10까지의 합:' || S);
END;
/

1. FOR LOOP

반드시 1씩 증가하는 형식이고 2 증가 3 증가 등은 어렵기 때문에 잘 사용되지 않는다.

형식

FOR 루프를돌릴변수명
IN 작은값..큰값
LOOP
반복실행될문장;
END LOOP;

Q. 1 ~ 5까지 출력

set serveroutput on

BEGIN
    FOR n IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(n);
    END LOOP;
END;
/

이 for 형식은 변수를 지정하는 자리가 명시된 형식이다.

그래서 declare 선언부가 필요하지 않다.

만약 1과 5의 자리를 바꿔 쓴다면 오류가 나지는 않지만 값은 출력되지 않는다.

Q. 5 ~ 1까지 출력

set serveroutput on

BEGIN
    FOR n IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(n);
    END LOOP;
END;
/

Q. for loop 문을 통하여 DEPT의 모든 정보를 출력해본다.

set serveroutput on

DECLARE
    vdept dept%rowtype;
    
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역'); -- title값
    
    FOR cnt IN 1..4 LOOP
        select * into vdept from dept where deptno = 10 * cnt;
        DBMS_OUTPUT.PUT_LINE(vdept.deptno || ' / ' || vdept.dname || ' / ' || vdept.loc);
    END LOOP;  
END;
/

3. WHILE LOOP

형식

WHILE 조건식
LOOP
반복실행할문장;
END LOOP;

Q. WHILE LOOP 문으로 1~5 출력

set serveroutput on

DECLARE
    n number := 1;

BEGIN
    WHILE n <= 5 LOOP
        dbms_output.put_line(n);
        n := n+1;
    END LOOP;
END;
/

특별히 어려운 점은 없었다.

PL / SQL

Oracle's Prodecural Language extension to SQL

변수 정의, 조건 처리, 반복 처리

절차적 언어로, SQL의 단점을 보완해 좀 더 효율적으로 SQL문을 사용할 수 있게 해준다.

구조

1. 선언부

DECLARE로 시작

모든 변수나 상수를 선언하는 부분.

2. 실행부

BEGIN으로 시작

내용을 기술.

3. 예외처리부

EXCEPTION으로 시작

(잘 쓰지 않는다.)

'Hello World'를 SQL Developer 콘솔창에 출력해보는 실습을 해 보았다.

제일 먼저 환경 변수를 설정해주어야 한다.

기본 값이 off 인데 이것을 바꿔주지 않으면 아무리 명령문을 실행해도 콘솔창에 나타나지 않는다.

set serveroutput on

이어서 다음과 같이 작성하고 실행한다.

BEGIN
	dbms_output.put_line('Hello World!');
END;
/

1. 변수 선언

declare로 시작하는 선언부에서 선언해야 한다.

1) 스칼라(SCALAR) 형식

2) 레퍼런스 형식

1) 스칼라 형식

변수 선언시 '=' 이 아닌 ' := ' 로 선언해 주어야 한다.

set severoutput on

DECLARE
	vempno number(4);
	vename varchar2(10);

BEGIN
	vempno := 7788;
	vename := 'SCOTT';
	dbms_output.put_line('사번 / 이름');
	dbms_output.put_line(vempno || ' / ' || vename);
END;
/

자바에서는 system.out.println(); 을 쓸때 괄호 안에서 문자와 변수를 섞어쓸 때

vempno + " / " + vename 같이 입력을 했지만

이 쪽에서는 || 를 이용해서 연결을 해주어야 한다.

vempno + " / " + vename (x)

vempno || ' / ' || vename  (O)

2) 레퍼런스 형식

여기서 %의 의미는 

emp테이블의 empno 타입과 같은 자료형 형식을 똑같이 한다는 뜻이다.

vempno emp.empno%type;
vename emp.ename%type;


%type : 컬럼 하나를 참조하는 레퍼런스 변수 선언
%rowtype : 로우 전체를 참조하는 레퍼런스 변수 선언

2. SELECT문

BEGIN으로 시작되는 선언부 안에 들어가며, into 절이 필요하다.

select 절의 컬럼은 into절의 변수와 1:1 대응을 하기에 개수와 데이터의 형, 길이가 일치해야 한다.

Q.  SCOTT 사원의 사번과 이름 검색하기 (레퍼런스 형식으로 변수 선언)

set SERVEROUTPUT ON

DECLARE
    vempno emp.empno%type;
    vename emp.ename%type;

BEGIN
    SELECT empno, ename INTO vempno, vename
    FROM emp
    WHERE ename = 'SCOTT';
    
    dbms_output.put_line('사번 / 이름');
    dbms_output.put_line(vempno || ' / ' || vename);    
END;
/

같은 결과가 나오도록 출력했지만,

스칼라 변수 선언과 레퍼런스 변수는 다른 형식이다.

변수 선언시 타입과 저장 공간을 직접 지정하는 것과, 이미 존재하는 테이블의 형식을 그대로 따오는 것.

3. 선택문

1) IF ~ THEN ~ END IF

TRUE : THEN 이하의 문장 실행

FLASE or NULL : END IF 다음 문장 실행

사원 테이블에서 SCOTT 사원의 연봉을 구하는 PL/SQL문을 작성해보는 실습

set SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vsal number(7,2);
    
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename='SCOTT';
    IF vemp.comm is null THEN vemp.comm := 0;
    END IF;
    
    vsal := vemp.sal * 12 + vemp.comm; 
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 연봉');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vsal);
    
END;
/

scott 사원의 일부 컬럼만 뽑아 쓰는 것보다는 rowtype으로 전체 데이터를 참조하는 것이 더 간단하므로 %rowtype

2) IF - THEN - ELSE - END IF

SET SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vsal number(7,2);
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename = 'SCOTT';
    
    IF vemp.comm is null THEN 
    vsal := vemp.sal*12;
    ELSE
    vsal := vemp.sal+12+vemp.comm;
    END IF;
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 연봉');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vsal);
    
END;
/

위의 IF ~ THEN ~ END IF; 문과 다른 점은

1)은 comm 컬럼의 null 값을 0으로 치환하여 계산했다는 것이고,

2)는 comm 컬럼이 null 인 경우 계산 방식과 null 이 아닌 경우의 계산 방식을 각각 지정하여 처리했다는 점이다.

3) IF ~ THEN ~ ELS IF ~ ELSE ~ END IF

SCOTT 사원의 부서번호를 이용해서 부서명을 구하는 PL/SQL문 작성해보기

SET SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vdname varchar2(14);
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename = 'SCOTT';
    
    IF vemp.deptno = 10 THEN vdname = 'ACCOUNTING';
    ELSIF vemp.deptno = 20 THEN vdname = 'RESEARCH';
    ELSIF vemp.deptno = 30 THEN vdname = 'SALES';
    ELSE vemp.deptno = 40 THEN vdname = 'OPERATIONS';
    END IF;
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 부서');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vdname);
    
END;
/