1. PL/SQL을 왜 배워야 하는가!
✏️ PL/SQL란 procedure language(절차적 언어) + SQL(비절차적 언어)
SQL을 가지고 하는 반복적인 업무를 절차적 언어를 가지고 자동화하는 수업
if문, loop문 + SQL = PL/SQL
PLSQL이 DBA작업을 하는데 필요한 이유?
- DBA작업을 편하게 할 수 있다. → 리눅스 쉘 스크립트와 파이썬과 같이 PLSQL을 이용하면 DB작업을 쉽게 수행할 수 있다.
- PLSQL 개발자들이 만든 함수, 프로시져등을 DBA가 관리를 해야한다. (그러려면 잘 알아야함)
- SQL로 하는 많은 작업들을 아주 쉽게 구현할 수 있다.
ex) 삼성 디스플레이 개발자들이 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;
🤔 개발자 관점에서 PLSQL을 사용해야하는 이유
문제1. 부서번호, 부서번호별 토탈월급을 출력하는데 가로로 출력
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;
문제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;❗위와같이 SQL을 작성하게되면 부서번호가 EMP테이블에 추가 될 때 마다 SQL을 같이 변경해주어야 한다. -> 번거롭다 !
문제3. 위 결과를 PL/SQL로 수행하시오!
- 명령 프롬프트창
✅SQL은, 새로운 직업과 새로운 부서번호가 입력될 때 마다 SQL을 변경해야한다. 반면에PLSQL은, 새로운 직업과 새로운 부서번호가 입력되어도 수정할 것 없고 그냥 그대로 수행하면 된다!
🤔 DBA관점에서 PL/SQL 사용 용도
- DB관리를 편하게 할 수 있다.
✔️ 테이블 스페이스의 공간이 80% 이상 찼으면 자동으로 공간 추가되게 프로시저 생성
(DB관리)
✔️ 데이터베이스 아카이브 로그 파일이 90% 이상 공간을 차지하면 자동으로 백업받고 아카이브 로그파일을 지울 수 있게 프로시저 생성(DB백업)
✔️ SLOW SQL을 빨리 찾을 수 있게 프로시저 생성, 관리할 수 있다.
PL/SQL 정보
✅ 우리가 배운 SQL을 BEGIN문에 넣을 것이다!
PL/SQL 런타임 구조
- 오류 없이 컴파일에 성공한 PL/SQL의 코드인 프로시저가 DB에 생성됩니다.
- 프로시저내의 반복문이 SQL을 자동으로 반복 호출하면서 실행이 됩니다.
PL/SQL코드가 실행될 때 프로시저로 만들어서 실행. 프로시저는 PL/SQL코드의 집합이다. 하나의 객체로 만드는 것!
뷰처럼 프로시저도 관리가 되고있다.
PL/SQL의 이점!
✅ SQL은 한번에 하나씩 데이터베이스에 보내야하기 때문에 네트워크 부하가 자주 발생한다.
✅ PLSQL은 블럭단위로 묶어서 하나로 보내기 때문에, 네트워크 부하를 줄일 수 있고, 성능을 향상시킬 수 있습니다.
✅ 모듈식 프로그램 개발 : DB관리작업, DB성능 모니터링 작업 등을 프로그램으로 만들 수 있다. ex)
엑셈(멕스게이지: 오라클 PL/SQL)로 만든 패키지 3개
멕스게이지? 오라클 성능 모니터링하는 툴 ! (코드를 암호화 할 수 있는 장점)
✅ 오라클의 툴과 통합 (오라클 DB에서 데이터를 레포팅해주는 툴, 통합)PLSQL사용할 수 있는 툴들이 여러곳이 있다.
✅ 이식성 : 내가 PL/SQL코드를 개발해서 프로시저에 만들었으면 내가 그 프로시저를 LG bd에서 실행해서 원하는 기능을 수행하게 할 수 있다.
✅ 예외처리 : 코드를 잘못짠건 아닌데 데이터가 틀려서 발생하는 오류들을 처리할 수 있다.
PL/SQL 블록구조
✏️ 구조
DECLARE : 선언절 (프로그램에서 사용할 데이터를 담을 변수를 선언)
BEGIN : 실행절 (프로그램의 실행문을 작성)
EXCEPTION : 예외처리 하는 절 (프로그램이 순조롭게 잘 수행되기 위해 입력되는 잘못된 데이터에 예외를 두는 부분)
END; : 종료절
✅BEGIN, END;는 필수, 나머지는 선택이지만 DECLARE 는 필수만큼 많이 쓰인다. 없어도 실행은 됨 !
문제 4. 책 (SQL200제 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; /
✅ 코드 설명// dbms_output.put_line를 작동되게 하는 코드. serveroutput을 on해준다! set serveroutput on // p_num1, p_num2은 변수(데이터를 담는 빈컵) 입력이 되면 // accept절을 이용해서 데이터가 변수로 들어간다. accept p_num1 prompt '첫번째 숫자를 입력하세요' accept p_num2 prompt '두번째 숫자를 입력하세요' // declare절에서 숫자만 담을 수 있는 변수를 선언한다. (문자x,날짜x) // declare~end 사이에서는 v_ 로 시작하는 변수를 만들고, // 아니면 p_로 시작하는것이 개발자들의 약속 declare v_sum number(10); // begin절에서는 위에서 선언해준 변수를 실행한다. 여기서 실행문을 잘 써야한다. // := 는 할당연산자이다. 빈컵이 있고 할당연산자가 있고 예를들어 숫자 3이있으면 // 숫자 3이 변수(빈컵)에 할당된다. // &는 치한변수이다. accept절에 있는 변수를 사용하려면 치한변수를 붙여야한다. // &p_num1가 2이고 &p_num2가 3이라면 v_sum에는 5가 들어간다. begin v_sum := &p_num1 + &p_num2 ; dbms_output.put_line('총합은: ' || v_sum ); // dbms_output.put_line -> 출력하는 패키지. print를 하는 것 ! // dbms_output -> 패키지, put_line -> 프로시저 end; / -> /가 있어야 끝난다는 것💡
세미콜론이 어디에 있는지 잘 보자. 필수로 붙어야 하는 자리는변수선언하는 곳(declare), begin, end 뒤!
문제 5. 위 코드를 수정해서 다음과 같이 실행될 수 있도록 하세요
첫번째 숫자를 입력하세요~
두번째 숫자를 입력하세요~
세번째 숫자를 입력하세요~
네번째 숫자를 입력하세요~
4개의 숫자의 총합은 ? 입니다.
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; /
PL/SQL의 블럭 유형
(p.39)
✏️ PL/SQL의 블럭의 유형은 3가지이다. 프로시저와 함수는 이름이 있어서 이름을 가지고 언제든 호출할 수 있고, 익명블럭은 이름이 없어서 호출할 수 없다. 자주 사용하는 PL/SQL코드는 재사용하기 위해서 이름을 주고 생성하기를 권장한다.
예제. 이름을 주고 PL/SQL 블럭을 만든다는 것은?
💡아래에서 이름은 get_data!!
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;
예제. 이름이 없는 PL/SQL 블럭
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; /
PL/SQL 익명블록 실행
아래 그림처럼 혹은 전체선택 + ctrl + enter
PL/SQL 블럭의 출력 활성화
✅ 1. set serveroutput on 해서 off 되어있는것을 켜준다.
✅ 2. 패키지 안에(괄호안에) 실행시킬 내용을 넣어준다.
💡참고! 코드 아래(/아래) set serveroutput off를 써서 꺼주는 코드 기술하자. 필수는 아니지만 습관처럼 다 쓴다.
2. PL/SQL 변수 선언
변수 사용
✅ 위 그림은 테이블에 있는 데이터를 불러와서 변수에 담는다.
first_name이 v_fname 이라는 변수에 들어가고, department_id의 데이터가 v_deptno라는 변수에 할당된다.
변수 이름을 지을 때 주의사항
❓ 예약어란 true, false, declare, begin 등등 이런애들..! 사용하면 에러난다.
문제 6. 오라클 예약어를 변수명으로 사용하면 오류가 나는지 테스트 해보기
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('4개 숫자의 총합은: ' || v_sum || '입니다.');
end;
/1. 아래처럼 초기화할 수 있다. 선택적!
declare
v_sum number(10) := 0; 2. 선언섹션에서는 0인데 begin에서는 더해진 값이 변수에 들어가니까 새 값이 할당된다는 것
3. 파라미터 확인
4. x에 출력결과 담아서 print
PL/SQL 변수 선언 및 초기화
not null을 썼으면 초기화를 꼭 해주어야 한다. 뒤에 뭐가오던 상관은 없음 상수(c_comm)는 1400을 고정시켜준 것이다. 변하면 안되는 것!
아래는 변수를 선언하고, 실행절에서 변수에 값을 할당하는 연습코드
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; /
✅ 첫번째는 할당된것이 없어서 아무것도 나오지 않고, 두번째는 john이 할당되었기 때문에 이름이 출력된다.
선언절에서
v_myName에 Jhon을 할당해서 변수 초기화를 했는데, 실행절에서v_myName에 Steven이 할당되었다.DECLARE v_myName VARCHAR2(20):= 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; /
문자열 리터럴의 구분자
변수에 문자열 값을 할당하려 하는데, 문자열에 싱글 쿼테이션 마크가 있으면 어떻게 해야 변수에 문자열 값을 담을 수 있을까?
1. 변수 := q'!싱글 쿼테이션 마크가 포함된 문자열!'
2. 변수 := q'[싱글 쿼테이션 마크가 포함된 문자열]'
변수 데이터 유형의 종류
일단 이렇게 있다라는것만 알고있기! 뒤에서 나옴
스칼라 변수형: 단일값을 담는 변수참조 변수형, LOB 변수형: 사진이나 동영상을 테이블에 저장하고자 할 때 사용하지만 오라클에 저장하기보다는 하둡, 몽고디비를 이용해서 주로 저장한다.조합 변수형: 여러개의 값을 담는 변수비PL/SQL변수: 바인드 변수
변수 유형
✔️ true, false를 저장하는 변수
✔️ 사진, 영상을 저장하는 lob데이터 유형
✔️date(날짜)
✔️ varchar2(문자)
✔️ long (긴 텍스트) -> 어쩌다 한번씩 쓰고 varchar2 많이 사용
✔️ number(숫자)
변수를 선언할 때와 초기화할 때 주의사항
1. v_ , p_ 로 일관성있게 변수이름 정하기
2. 숫자 담을거면 v_sal, v_age 이런식으로. 갑자기 v_k 이런거 X
(OCP 문제은행 문제)
3. not null을 사용했으면 무조건 초기화 해주기
✅ 첫번째 주의사항
declare
v_num number(10) not null; <-- X
v_num number(10) not null; := 0; <-- O
v_num2 constant; <-- X
v_num2 constant; := 120; <-- O
v_num3 varchar2(10) default 'jhon' <-- O✅ 두번째 주의사항
문제 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; // 사원번호가 7788인 사람의 데이터 dbms_output.put_ilnile ('해당 사원의 월급은' || v_sal) end; /
위 코드의 v_sal 변수명을sal이라고 수정해서 실행하면 실행이 되는지 확인
set serveroutput on accept p_empno prompt '사원번호를 입력해주세요' declare sal number(10); begin select sal into sal from emp where empno = &p_empno; dbms_output.put_ilnile ('해당 사원의 월급은' || sal); end; /
PL/SQL 변수명 명명할 때 지침사항
위 표처럼 변수명을 만들기를 지침합니다.
일단 참고만 하기 ↓
%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; /declare v_sal number(10); // 이부분을 NUMBER가 아닌 %TYPE으로 변경가능✅ 위처럼 쓰면 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코딩할 때 위와같이emp.sal%type를 사용해 코딩하는것을 권장해야한다.
✅ dba가 si 프로젝트 초반에 또는 운영중에 프로시저를 만드는 단계에서도 반드시%type으로 변수를 선언하라고 꼭 얘기해야한다.
v_balance가 중요한 변수여서 하위에 만들어지는 변수들이 데이터타입을 v_balance로 따른다고 치면, 나중에 v_balance의 데이터타입이 바뀌었을 때 다른변수들도 자동으로 변경이 된다.
문제 9. 아래의 코드를 수정해서 사원번호를 물어보게하고, 사원번호를 입력하면 월급, 직업도 같이 출력되게 하기
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 job 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; v_empno emp.empno%type; begin select d.loc into v_loc from emp e join dept d on e.deptno = d.deptno where empno = &p_empno; dbms_output.put_line('근무하는 부서위치: ' || v_loc ); end; /
유익한 글 잘 봤습니다, 감사합니다.