▣ PLSQL 수업
▣ 1. PL/SQL 왜 배워야하는지 ?
- PL/SQL 이란 ?
↓
Procedure Language + SQL
절차적 언어 비절차적 언어
SQL을 가지고 하는 반복적인 업무를 절차적 언어를 가지고 자동화 하는 수업
- PLSQL 이 DBA 작업을 하는데 필요한 이유 ?
- PL/SQL 을 이용하면 DBA 작업을 편하게 할 수 있습니다.
리눅스 쉘 스크립트와 파이썬과 같이 PLSQL 을 이용하면
DB 작업을 쉽게 수행할 수 있습니다. - PLSQL 개발자들이 만든 함수, 프로시져등을 DBA 가 관리를 해야 하는데 관리를 하려면 알아야합니다.
- SQL 로 하는 많은 작업들을 아주 쉽게 구현할 수 있습니다.
- PL/SQL 을 이용하면 DBA 작업을 편하게 할 수 있습니다.
예: 삼성 디스플레이 개발자들 PL/SQL 을 어떻게 쓰는지 ?
■ 2. 개발자 관점에서 PLSQL을 사용해야하는 이유
문제1. 부서번호, 부서번호별 토탈월급을 출력하는데 가로로 출력하시오 !
( sum 과 decode 를 이용해서 하세요 )
select sum( decode( deptno, 10, sal, null) ) as "10",
sum( decode( deptno, 20, sal, null) ) as "20",
sum( decode( deptno, 30, sal, null) ) as "30"
from emp;
10 20 30
---------- ---------- ----------
8750 10875 9400문제2. 앞에 직업도 같이 나오면 직업별 부서번호별 토탈월급이 출력되게 하시오 !
select job, sum( decode( deptno, 10, sal, null) ) as "10",
sum( decode( deptno, 20, sal, null) ) as "20",
sum( decode( deptno, 30, sal, null) ) as "30"
from emp
group by job;
JOB 10 20 30
------------------ ---------- ---------- ----------
SALESMAN 5600
CLERK 1300 1900 950
ANALYST 6000
MANAGER 2450 2975 2850
PRESIDENT 5000※ 위와 같이 SQL을 작성하게 되면 부서번호가 EMP 테이블에 추가 될때 마다 SQL 을 같이 변경해줘야합니다. 그래서 이 작업을 좋은 개발자들이 있는 회사에서는 PLSQL 변경해서 수행합니다.
문제3. 위의 결과를 PL/SQL 로 수행하시오 !
- 명령 프롬프트 창에서 먼저 수행합니다.
create or replace procedure
get_data(p_x out sys_refcursor)
as
l_query varchar2(400) :='select deptno ';
begin
for x in (select distinct job from emp order by 1)
loop
l_query := l_query ||replace(', sum(decode(job,''$X'',sal)) as $X '
,'$X',x.job );
end loop;
l_query := l_query ||' from emp group by deptno ';
open p_x for l_query;
end;
/
* 프로시져를 실행하는 부분
variable x refcursor;
exec get_data(:x);
print x;
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30 950 2850 5600
10 1300 2450 5000
20 6000 1900 2975
※ 설명: SQL : 새로운 직업과 새로운 부서번호가 입력될 때 마다 SQL을 변경해
줘야합니다.
PL/SQL : 새로운 직업과 새로운 부서번호가 입력되어도 수정할 것 없고
그냥 그대로 수행하면 됩니다.
▣ 3. DBA 관점에서 PL/SQL 사용 용도
- DB 관리를 편하게 할 수 있습니다.
예: 테이블 스페이스의 공간이 80% 이상 찼으면 자동으로 공간 추가되게
프로시져 생성 (DB관리)
데이터베이스 아카이브 로그 파일이 90% 이상 공간을 차지하면
자동으로 백업받고 아카이브 로그파일을 지울수있게 프로시져 생성(DB백업)
SLOW SQL 을 빨리 찾을 수 있게 프로시져를 생성해서 관리할 수 있습니다.
기타 등등
SQL 시간에 배운 지식 : 테이블 생성, 인덱스 생성, 서브쿼리를 사용한 insert
지혜 : 데이터 이행할 때 테이블을 먼저 생성하고 데이터 이행한 후에
인덱스를 생성해야된다. ( 순서가 중요하다)▣ 4. PL/SQL 이란 무엇인가 ? (교재 30페이지)
if 문, loop문 + SQL = PL/SQL
▣ 5. PL/SQL 의 정보
- PL/SQL 은 블럭구조로 되어있습니다.
- SQL 보다 코드의 유지관리 쉽습니다.
- 한번 작성하면 재사용이 가능합니다.
▣ 6. PL/SQL 의 런타임 구조
32페이지 그림
- 오류 없이 컴파일에 성공한 PL/SQL 의 코드인 프로시져가 DB 에 생성됩니다.
- 프로시져내의 반복문이 SQL을 자동으로 반복 호출하면서 실행이 됩니다.
▣ 7. PL/SQL 의 이점 (P33)
-
SQL은 한번에 하나씩 데이터베이스에 보내야하기 때문에
네트워크 부하가 자주 발생합니다. -
PL/SQL 을 블럭단위로 묶어서 하나로 보내기 때문에 네트워크 부하를
줄일 수 있고 성능을 향상 시킬 수 있습니다.
▣ 7. PL/SQL의 이점
- 모듈식 프로그램 개발 : db 관리작업, db 성능 모니터링 작업등을
프로그램으로 만들수 있다.예: 엑셈(멕스게이지: 오라클 PL/SQL로 만든 패키지 3개 ) ↓ 오라클 성능 모니터링하는 툴 ( 코드를 암호화 할 수 있는 장점) - 오라클의 툴과 통합 ( 오라클 DB에서 데이터를 레포팅해주는 툴과 통합)
PLSQL을 사용할 수있는 툴이 여러개 있습니다. - 이식성 : 내가 PL/SQL 코드를 개발해서 프로시져를 만들었으면
그 프로시져를 LG db에서 실행해서 원하는 기능을 수행하게
할 수있다. - 예외처리 : 코드를 잘못짠건 아닌데 데이터가 잘못되어서 발생하는
오류들을 처리할 수 있습니다.
▣ 8. PL/SQL 블록 구조
declare : 선언절 (선택)
프로그램에서 사용할 데이터를 담을 변수를 선언
begin : 실행절 (필수)
프로그램의 실행문
exception : 예외처리하는 절 : 프로그램이 순조롭게 잘 수행되기 위해
(선택) 입력되는 잘못된 데이터에 예외를 두는 부분
end; 종료절 (필수)
문제4. 책 344페이지의 코드를 코딩하고 실행하시오 !
set serveroutput on ****
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
declare
v_sum number(10);
begin
v_sum := &p_num1 + &p_num2 ;
dbms_output.put_line('총합은 : ' || v_sum );
end;
/
코드설명:
**set serveroutput on** **-- dbms_output.put_line 를 실행하기 위해서
-- serveroutput 을 on 으로 해줍니다.**
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
declare
v_sum number(10); --- 숫자를 담을 변수(빈컵) 을 선언했습니다.
begin
v_sum := &p_num1 + &p_num2 ;
dbms_output.put_line('총합은 : ' || v_sum );
end;
/
**set serveroutput off -- 끄는 부분은 맨아래에 기술합니다. 습관처
수미상관.**문제 5. (점심시간 문제) 위의 코드를 수정해서 다음과 같이 실행될 수 있도록 하시오
'첫번째 숫자를 입력하세요 ~' 2
'두번째 숫자를 입력하세요 ~' 3
'세번째 숫자를 입력하세요 ~' 4
'네번째 숫자를 입력하세요 ~' 1
4개의 숫자의 총합은 10입니다.
set serveroutput on
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
accept p_num3 prompt '세번째 숫자를 입력하세요 ~'
accept p_num4 prompt '네번째 숫자를 입력하세요 ~'
declare
v_sum number(10);
begin
v_sum := &p_num1 + &p_num2 + &p_num3 + &p_num4;
dbms_output.put_line('4개의 숫자의 총합은 ' || v_sum || ' 입니다.');
end;
/
이전:declare
v_sum number(10);
begin
v_sum := &p_num1 + &p_num2 + &p_num3 + &p_num4;
dbms_output.put_line('4개의 숫자의 총합은 ' || v_sum || ' 입니다.');
end;
신규:declare
v_sum number(10);
begin
v_sum := 2 + 3 + 4 + 1;
dbms_output.put_line('4개의 숫자의 총합은 ' || v_sum || ' 입니다.');
end;
4개의 숫자의 총합은 10 입니다.
PL/SQL 프로시저가 성공적으로 완료되었습니다.▣ 9. PL/SQL의 블럭의 유형 (P 39)
설명요약: PL/SQL의 블럭의 유형은 3가지인데 프로시져, 함수, 익명블럭입니다.
프로시져와 함수는 이름이 있어서 이름을 가지고 언제든 호출할 수 있고,
익명 블럭은 이름이 없어서 호출할 수는 없습니다.
자주 사용하는 PL/SQL 코드는 재사용하기 위해서 이름을 주고 생성하기를 권장 합니다.예제: 이름을 주고 PL/SQL 블럭을 만든다는 것은 ?
create or replace procedure get_data(p_x out sys_refcursor)
as
l_query varchar2(400) :='select deptno ';
begin
for x in (select distinct job from emp order by 1)
loop
l_query := l_query ||replace(', sum(decode(job,''$X'',sal)) as $X '
,'$X',x.job );
end loop;
l_query := l_query ||' from emp group by deptno ';
open p_x for l_query;
end;
/
variable x refcursor;
exec get_data(:x);
print x;▣ 10. PL/SQL 익명 블럭을 실행하는 방법
PL/SQL 코드를 sqldeveloper 에 붙여두고 단축키 F5 를 눌러서 실행합니다.
▣ 11. PL/SQL 블럭의 출력 활성화
set serveroutput on
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
declare
v_sum number(10);
begin
v_sum := &p_num1 + &p_num2 ;
dbms_output.put_line('총합은 : ' || v_sum );
end;
/
set serveroutput off -- 끄는 부분은 맨아래에 기술합니다.
■ 2장. PL/SQL 변수 선언
▣ 12. 변수 사용
▣ 13. 변수이름을 지을 때 주의 사항
- 문자 시작 → must
- 예약어(TRUE, FALSE, DECLARE, BEGIN)를 포함하면 안된다.
되는 것도 있으니깐 그냥 v_를 사용하는 게 좋다~!
문제6. 오라클 예약어를 변수명으로 사용하면 오류가 나는지 테스트 하시오
-- 문제4번 코드를 다 가져오세요 ~
set serveroutput on
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
declare
select number(10);
begin
select := &p_num1 + &p_num2 ;
dbms_output.put_line('총합은 : ' || select );
end;
/
-> 오류가 난다.▣ 14. PL/SQL 에서의 변수 처리
set serveroutput on
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~'
accept p_num2 prompt '두번째 숫자를 입력하세요 ~'
declare
v_sum number(10) := 0 ;
begin
v_sum := &p_num1 + &p_num2 ;
dbms_output.put_line('총합은 : ' || v_sum );
end;
/
create or replace procedure get_data( p_x out sys_refcursor)
as
l_query varchar2(400) :='select deptno ';
begin
for x in (select distinct job from emp order by 1)
loop
l_query := l_query ||replace(', sum(decode(job,''$X'',sal)) as $X '
,'$X',x.job );
end loop;
l_query := l_query ||' from emp group by deptno ';
open p_x for l_query;
end;
/
variable x refcursor;
exec get_data(:x);
print x;▣ 15. PL/SQL 에서의 변수 선언 및 초기화
- not null을 썼으면 초기화를 반드시 해줘야 한다.
- constant 상수 고정 이것도 초기화
- 설명: 변수를 선언하고 실행절에서 변수에 값을 할당하는 연습 코드
DECLARE v_myName VARCHAR2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); v_myName := 'John'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; / My name is: My name is: John PL/SQL 프로시저가 성공적으로 완료되었습니다. - 코드 설명: 선언절에서 v_myName 에 John 을 할당해서 변수 초기화를 했는데
실행절에서 v_myName 에 Steven 이 할당 되었습니다.```sql DECLARE v_myName VARCHAR2(20):= 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; / My name is: Steven PL/SQL 프로시저가 성공적으로 완료되었습니다. ```
▣ 16. 문자열 리터럴의 구분자
변수에 문자열 값을 할당하려 하는데 문자열에 싱글쿼테이션 마크가 있으면
어떻게 해야 변수에 문자열 값을 담을 수 있을까 ?
1. 변수 := q'**!** 싱글 쿼테이션 마크가 포함된 문자열 **!**'
2. 변수 := q'**[** 싱글 쿼테이셤 마크가 포함된 문자열 **]**'DECLARE
v_event VARCHAR2(15);
BEGIN
v_event := q'!Father's day!';
DBMS_OUTPUT.PUT_LINE ('3rd Sunday in June is : ' || v_event );
v_event := q'[Mother's day]';
DBMS_OUTPUT.PUT_LINE ('2nd Sunday in May is : '|| v_event );
END;
/
3rd Sunday in June is : Father's day
2nd Sunday in May is : Mother's day
PL/SQL 프로시저가 성공적으로 완료되었습니다.▣ 17. 변수 데이터 유형의 종류
1. 스칼라 변수형 : 단일값을 담는 변수
2. 참조 변수형, LOB 변수형 : 사진이나 동영상을 테이블에 저장하고자 할때 사용
하둡과 몽고디비를 이용해서 주로 이미지와 동영상
을 저장합니다.
3. 조합 변수형 : 여러개의 값을 담는 변수
4. 비 PL/SQL 변수 : 바인드 변수▣ 18. 변수 유형
▣ 19. 변수를 선언할 때와 초기화 할때 주의사항 (ocp 문제은행)
- v로 사용해라, p로 사용해라
- [ocp 문제 은행] : not null 및 constant는 무조건 초기화!
-
첫번째 주의사항
declare
v_num number(10) not null ; <-- Xv_num number(10) not null := 0 ; <-- O
v_num2 constant ; <--- X
v_num2 constant := 120 ; <--- O
v_num3 varchar2(10) default 'john' ; <--- O -
두번째 주의사항
select ... into 절 변수명 지을때 해당 테이블의 컬럼명으로 만들면 안됩니다.
문제7. 두번째 주의사항을 확인하기 위해서 SQL200제 책의 346 페이지를
코딩하고 실행하시오 ! (140번 예제)
set serveroutput on
accept p_empno prompt '사원번호를 입력하세요 ~'
declare
v_sal number(10) ;
begin
select sal into v_sal
from emp
where empno = &p_empno;
dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
end;
/문제8. 위의 코드의 v_sal 변수명을 sal 이라고 수정해서 실행하면
실행되는지 확인하시오 !
→ 실행은 되는데 하지마라 헷갈린다.
▣ 20. PL/SQL 변수명 명명할 때 지침사항
아래의 표처럼 변수명을 명명하기를 지침 합니다.
→ 이건 그냥 참고…
▣ 21. %type 속성 ( 이수자 평가문제 ★)
set serveroutput on
accept p_empno prompt '사원번호를 입력하세요 ~'
declare
v_sal emp.sal%type ;
begin
select sal into v_sal
from emp
where empno = &p_empno;
dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
end;
/※ 변수의 데이터 유형을 지정할때 emp.sal%type 이라고 하게 되면
가장 큰 장점이 emp 테이블의 월급의 데이터 유형의 크기가 변경이 되었을때
PLSQL 코드를 따로 변경해주지 않아도 됩니다.
만약에 아래와 같이 개발자가 PL/SQL 코딩을 했다면 ?
v_sal number(10);
그런데 어느날 dba 가 아래와 같이 emp 테이블의 sal을 변경했습니다.
alter table emp
modify sal number(15);
이렇게 크기를 늘리면 db 에 sal 와 관련된 모든 변수의 데이터 유형을
number(10) 에서 number(15) 로 변경해줘야 합니다.
그러면 많은 공수가 들어가니까 아예 처음부터 PL/SQL 코딩할 때
다음과 같이 코딩하는것을 권장해야합니다.
v_sal emp.sal%type;※ dba 가 si 프로젝트 초반에 또는 운영중에 프로시져를 만드는 단계에서도
반드시 %type 으로 변수를 선언하라고 꼭 애기해줘야 합니다. !
" 요즘에도 %type 을 안써서 dba 가 테이블 변경을 했을때 개발자들이
많은 고생을 하는 경우가 많다고 합니다. "
문제9. 아래의 코드를 수정해서 사원번호를 물어보게하고 사원번호를
입력하면 이번에는 월급과 직업도 같이 출력되게하시오 !
구현결과 : 사원번호를 입력하세요 ~ 7788
해당사원의 월급은 3000
해당사원의 직업은 ANALYST
set serveroutput on
accept p_empno prompt '사원번호를 입력하세요 ~'
declare
v_sal emp.sal%type ;
v_job emp.job%type;
begin
select sal , job into v_sal, v_job
from emp
where empno = &p_empno;
dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
dbms_output.put_line('해당 사원의 직업은 ' || v_job );
end;
/문제10. 우리반 테이블을 가지고 다음의 결과를 출력하는 PL/SQL 코드를
작성하시오 !
학생번호를 입력하세요 ~ 2
이름: 김희선
나이: 30
주소 : 서울시 동작구
답:
set serveroutput on
accept p_empno prompt '학생번호를 입력하세요 ~'
declare
v_ename emp17.ename%type ;
v_age emp17.age%type;
v_address emp17.address%type;
begin
select ename, age, address into v_ename, v_age, v_address
from emp17
where empno = &p_empno;
dbms_output.put_line('이름은 ' || v_ename );
dbms_output.put_line('나이는' || v_age );
dbms_output.put_line('주소는' || v_address) ;
end;
/문제11. (오늘의 마지막 문제) 사원번호를 물어보게하고 사원번호를
입력하면 해당 사원이 근무하는 부서위치가 출력되게하시오 !
사원 번호를 입력하세요 ~ 7788
근무하는 부서위치 : DALLAS
※ 힌트 : 실행문에 조인을 사용해야 합니다.
set serveroutput on
accept p_empno prompt '사원 번호를 입력하세요 ~ '
declare
v_loc dept.loc%type;
begin
select d.loc into v_loc
from emp e, dept d
where e.deptno = d.deptno and
e.empno = &p_empno;
dbms_output.put_line('근무하는 부서위치 : ' || v_loc);
end;
/
근무하는 부서위치 : DALLAS
PL/SQL 프로시저가 성공적으로 완료되었습니다.[7월 19일 점심시간 문제] 테이블을 truncate 하면 관련 제약과 인덱스는 어떻게 되는지 테스트 하세요
1. emp 와 dept 를 초기화 합니다.
2. emp 테이블에 sal 에 인덱스를 생성합니다.
3. emp 테이블에 empno 에 primary key 제약을 겁니다.
4. emp 테이블을 truncate 합니다.
5. emp 테이블와 연관된 인덱스와 제약이 db 에 존재하는지 확인합니다.
SQL> create index emp_sal_idx
2 on emp(sal);
SQL> alter table emp
2 add constraint emp_empno_pk primary key emp(empno);
add constraint emp_empno_pk primary key emp(empno)
*
2행에 오류:
ORA-00906: 누락된 좌괄호
SQL> alter table emp
2 add constraint emp_empno_pk primary key empno;
add constraint emp_empno_pk primary key empno
*
2행에 오류:
ORA-00906: 누락된 좌괄호
SQL> alter table emp
2 add constraint emp_empno_pk primary key(empno);
테이블이 변경되었습니다.
SQL> truncate table emp;
테이블이 잘렸습니다.
SQL> select index_name
2 from user_indexes
3 where table_name = 'EMP';
INDEX_NAME
--------------------------------------------------------------------
EMP_SAL_IDX
EMP_EMPNO_PK
