01. PL/SQL
PL/SQL 이란?
Procedure Language + SQL 의 약자
절차적 언어 비절차적언어➡️ SQL을 가지고 하는 반복적인 업무를 절차적 언어를 가지고 자동화하는 수업
(프로시저 : 보통 저장 프로시저를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.)
❗ if문, loop문 + SQL = PL/SQL ❗
PLSQL 이 DBA 작업을 하는데 필요한 이유?
-
PL/SQL 을 이용하면 DBA의 작업을 편하게 할 수 있다.
리눅스 쉘 스크립트와 파이썬과 같이 PL/SQL 을 이용하면 DB작업을 쉽게 수행할 수 있다. -
PL/SQL 개발자들이 만든 함수, 프로시져 등을 DBA 가 관리를 해야 하는데,
관리를 하려면 알아야 하기 때문에 -
SQL 로 하는 많은 작업들을 아주 쉽게 구현할 수 있다.
ex) 삼성 디스플레이 개발자들의 pl/sql 활용
개발자 관점에서 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;
문제 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을 같이 변경해줘야해서 불편하다.
그래서 좋은 개발자들이 있는 회사에서는 이 작업을 PL/SQL 로 변경해서 수행합니다.
SQL 을 PL/SQL 로 튜닝
문제 3. 위의 결과를 PL/SQL 로 수행하시오
1) 명령 프롬프트 창에서 먼저 수행합니다.( 프로시저를 만드는 문 ) 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;
✔️ SQL : 새로운 직업과 부서번호가 입력될 때 마다 SQL 을 변경해줘야한다.
✔️ PL/SQL : 새로운 직업과 부서번호가 입력되어도 수정할 것 없고 그냥 그대로 수행하면 된다.
DBA 관점에서 PL/SQL 사용 용도
DB 관리를 편하게 할 수 있다.
ex) 테이블 스페이스의 공간이 80% 이상 찼으면 자동으로 공간 추가되게 프로시저를 만들 수 있다. (db관리)
ex) 데이터베이스 아카이브 로그 파일이 90% 이상 공간을 차지하면 자동으로 백업받고 아카이브 로그파일을 지울 수 있게 프로시저 생성 (db백업)
ex) SLOW sql 을 빨리 찾을 수 있게 프로시저를 생성해서 관리할 수 있다.
기타 등등..
sql 시간에 배운 지식은 : 테이블 생성, 인덱스 생성, 서브쿼리를 사용한 insert ...
이게 지혜가 되면 : 데이터 이행할 때 테이블을 먼저 생성하고 데이터 이행한 후에 index 를 생성해야된다 (순서가 중요하다는 것을 경험을 통해 깨닫게되는..)
6. PL/SQL 의 정보
- PL/SQL 은 블럭구조 로 되어있다.
- SQL 보다 코드의 유지관리가 쉽다.
- 한번 작성하면 재사용이 가능하다.
PL/SQL 의 런타임 구조
-> 오라클에서 프로시저 코드 확인가능
프로시저: pl/sql 코드의 집합. 마치 테이블처럼 관리가 가능하다.
즉 pl/sql -> sql문을 반복실행 하게하는..!
✅ 정리하자면,
1. 오류 없이 컴파일에 성공한 PL/SQL 의 코드인 프로시저 가 DB에 생성된다.
2. 프로시저내의 반복문이 SQL을 자동으로 반복 호출하면서 실행이 된다.
7. PL/SQL 의 이점
✔️ SQL 은 한번에 하나씩 데이터베이스에 보내야하기 때문에 네트워크 부하가 자주 발생하는데,
✔️ PL/SQL 은 블럭단위로 묶어서 하나로 보내기 때문에 네트워크 부하를 줄일 수 있고 성능을 향상시킴
-
모듈식 프로그램 개발 : DB관리작업, DB성능 모니터링작업 등을 프로그램으로 만들수있다.
ex) 엑셈(멕스게이지 : 오라클 pl/sql로 만든 패키지 3개) -> 오라클 성능 모니터링하는 툴 개발 -
툴과의 통합 : PL/SQL 엔진은 Oracle Forms 및 Oracle Reports와 같은 Oracle 툴에 통합됩니다.이러한 툴을 사용할 경우 논리적으로 사용할 수 있는 PL/SQL 엔진이 프로시저문을 처리하고 SQL 문만 데이터베이스에 전달
-
이식성 : PL/SQL 프로그램은 운영 체제나 플랫폼에 상관없이 Oracle 서버가 실행되는 모든 환경에서 실행할 수 있습니다. 매번 새로운 환경에 맞게 커스터마이즈하지 않아도 됩니다. 이식 가능한 프로그램 패키지를 작성하고 서로 다른 환경에서 재사용 가능한 라이브러리를 만들 수 있습니다.
ex) 내가 PL/SQL 코드를 개발해서 프로시저를 만들었으면, 그 프로시저를 LG db에서 실행해서 원하는 기능을 수행하게 할 수 있다. -
예외처리 : 코드를 잘못짠건 아닌데 데이터가 틀려서 발생하는 오류들을 처리할 수 있다.
8. PL/SQL 블록구조
declare: 선언절 (필수나 다름없는 선택)
프로그램에서 사용할 데이터를 담을 변수(빈컵) 을 선언
없어도 실행은 되나 있는 것이 좋음?begin: 실행절 (필수)
프로그램의 실행문 (위에 만든 변수를 실행시키는)exception: 예외처리 (선택)
프로그램이 순조롭게 잘 수행되기 위해 입력되는 잘못된 데이터에 예외를 두는 부분end;: 종료절 (필수)
PL/SQL 문법 설명
문제 4. sql200제 344p 의 코드를 코딩하고 실행하시오
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 를 켜주는것 (defalt 는 off)] accept p_num1 prompt '첫번째 숫자를 입력하세요 ~' [변수이름] [야기시킨다] [이 문구를 통해 받은값을 '변수이름' 으로 야기시킨다] accept p_num2 prompt '두번째 숫자를 입력하세요 ~' declare v_sum number(10); --> 숫자를 담을 변수(빈컵)을 선언했다. (만들었다) [변수(빈컵)] [숫자만 담겠다] begin --> 가장 신경써서 만들 실행절 (sql문 or pl/sql문) v_sum := &p_num1 + &p_num2 ; [할당연산자] [치한변수 &] dbms_output.put_line('총합은 : '|| v_sum ); [출력하는 패키지(print)] end; --> 종료절 /
- declare랑 end 사이의
변수 이름v_ 를 쓰고, 바깥쪽 변수는 p _ 를 쓴다는 암묵적 약속- (빈컵) := 3 --> 숫자 3이 빈컵에 할당되는것 (
할당연산자)- ★실행절에 & 쓰는건 필수 문법임! (
치한변수) ★- dbms_output.put_line 오라클의 내장
패키지
- dbms_output : 패키지
- put_line : 프로시저
✅ 세미콜론 ; 자리 체크
문제 5. (점심시간 문제)
위의 코드를 수정해서 다음과 같이 실행될 수 있도록 하시오첫번째 숫자를 입력하세요 ~ 두번째 숫자를 입력하세요 ~ 세번째 숫자를 입력하세요 ~ 네번째 숫자를 입력하세요 ~ 4개의 숫자의 총합은 ? 입니다.set serveroutput on accept p_num1 prompt '첫번째 숫자를 입력하세요' 2 accept p_num2 prompt '두번째 숫자를 입력하세요' 3 accept p_num3 prompt '세번째 숫자를 입력하세요' 4 accept p_num4 prompt '네번째 숫자를 입력하세요' 1 declare v_sum number(10); begin v_sum := &p_num1 + &p_num2 + &p_num3 + &p_num4; dbms_output.put_line('4개의 숫자의 총합은 : '|| v_sum ||' 입니다'); end; /
9. PL/SQL 의 블럭의 유형
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; -> 이름 : get_data
✔️ 첫번째 숫자를 입력하세요~ 했던 그 코드는 이름 없는 익명블록이다.
10. PL/SQL 익명블록 실행하는 방법
pl/sql 코드를 sqldeveloper 에 붙여두고,
단축키 F5 를 누르거나, 상단의 재생 옆 버튼을 누른다.
(선택할 필요도 없음)
11. PL/SQL 블럭의 출력 활성화
- SQL Developer에서 출력을 활성화하려면 PL/SQL 블록을 실행하기 전에 다음 명령을 실행합니다
SET SERVEROUTPUT ON✔️계속 켜놔도 상관은 없는데, end; / 뒤에 set serveroutput off 를 해주는것도 좋다.
- 미리 정의된 Oracle 패키지와 해당 프로시저를 익명 블록에 사용합니다
(패키지.put_line)
DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || v_fname);2장. 변수 선언
12. 변수 사용
테이블에서 데이터를 불러와서 변수에 넣었다.
13. 변수이름을 지을 때 주의사항
• 문자로 시작해야 합니다.
• 문자나 숫자를 포함할 수 있습니다.
• 특수 문자($, _ , # 등)를 포함할 수 있습니다.
• 30자 이하의 문자만 포함해야 합니다.
• 예약어를 포함하면 안됩니다.
-> 오라클에서 예약해놓은 예약어가 있다 (ex. true..등)
문제 6. 오라클 예약어를 변수명으로 사용하면 오류가 나는지 테스트 하시오
변수이름을 v_num 으로 했던것을 selesct 로 바꿔보았다.
14. PL/SQL 에서 변수 처리
✅ 변수는 다음과 같이 처리됩니다.
• 선언 섹션에서 선언 및 선택적으로 초기화됨 -> declare 에서 선언되고 사용된다는 말
• 실행 섹션에서 사용되고 새 값이 할당됨 -> begin 에서 값이 주어지고 사용된다는 말
(나중에 다시 설명)
• PL/SQL 서브 프로그램 (프로시져) 에 파라미터로 전달됨
• PL/SQL 서브 프로그램의 출력 결과를 보유하는 데 사용됨
15. PL/SQL 변수 선언 및 초기화
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;deptno 에 not null 을 썼으니 null 값이 들어가지않게 초기화해줘야한다.
v_ 3개는 변수 (빈컵) 이다.
constant -> 상수변수. 1400을 고정해놓는 것.
숫자는 숫자로, 문자는 문자로 형태 맞춰줘야함.
❗❗데이터가 들어가야한다면, 꼭 not null 등의 초기화 처리를 해줘야한다!
:= 뒤에 임의의 값을 넣어준다. (어차피 begin 절에서 지정된 값으로 바뀜!)❗❗
✅ 변수를 선언하고 초기화하는 예문
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;
/
✔️ 첫 블록에서는 v_myName 변수가 선언되지만 초기화되지 않습니다. 실행 섹션에서 John 값이 변수에 할당됩니다.
✔️ v_myName 변수를 전달하여 PUT_LINE 프로시저가 호출됩니다. 'My name is:'
문자열에 변수의 값이 연결됩니다.
DECLARE
v_myName VARCHAR2(20):= 'John';
BEGIN
v_myName := 'Steven';
DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName);
END;
/
✔️ 선언절에서 v_myName 에 john 을 할당해서 변수 초기화를 했는데,
실행절에서 v_myName 에 Steven 이 할당 된 것
16. 문자열 리터럴의 구분자
🔎 변수에 문자열 값을 할당하려 하는데, 문자열에 싱글쿼테이션 마크가 있으면 어떻게 해야 변수에 문자열 값을 담을 수 있을까?
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;
/
✅ 문자열이 아포스트로피(작은 따옴표와 같음)를 포함할 경우
- 변수 := q'! 싱글 쿼테이션 마크가 포함된 문자열!'
- 변수 := q'[ 싱글 쿼테이션 마크가 포함된 문자열 ]'
17. 변수 데이터 유형의 종류
- PL/SQL 변수
스칼라 변수형: 단일값을 담는 변수. 대부분의 변수는 스칼라 변수참조 변수형LOB 변수형: 잘쓰이진 않으나, 이미지동영상을 테이블에 저장하고자 할때 사용
(하둡과 몽고디비를 이용하지 오라클에는 잘 안씀)조합 변수형: 여러개 값을 담는 변수
- 비PL/SQL 변수 : 바인드 변수 (PL/SQL 이 아닌..? 뒤에서 배울것)
18. 변수 유형
✅ 슬라이드는 다음 데이터 유형을 보여줍니다.
• TRUE는 부울 값을 나타냅니다. (true / false)
• 15-JAN-09는 DATE를 나타냅니다.
• 이미지는 BLOB를 나타냅니다.
• 콜아웃의 텍스트는 VARCHAR2 데이터 유형이나 CLOB를 나타낼 수 있습니다.
• 256120.08은 정밀도와 배율을 가진 NUMBER 데이터 유형을 나타냅니다.
• 필름 릴은 BFILE을 나타냅니다.
• 도시 이름 Atlanta는 VARCHAR2 데이터 유형을 나타냅니다.
기본 스칼라 데이터 유형
• CHAR [(maximum_length)]
• VARCHAR2 (maximum_length)
• NUMBER [(precision, scale)]
• BINARY_INTEGER
• PLS_INTEGER
• BOOLEAN
• BINARY_FLOAT
• BINARY_DOUBLE
19. 변수 선언과 초기화 할 때 주의사항
- 일관성 있는 이름 지정 규칙을 따릅니다.
p_ , v _ 등 - 변수의 의미를 알 수 있는 식별자를 사용합니다.
p_salary 등으로 의미있게 지정해라 - NOT NULL 및 CONSTANT로 지정된 변수를 초기화합니다.
declare
v_num number(10) not null; <--X
v_num number(10) not null := 0; <--O
v_num constant ; <--X
v_num constant := 120 ; <-- O
v_num varchar2(10) default 'john'; <--O✔️ 할당 연산자(:=) 또는 DEFAULT 키워드로 변수를 초기화합니다.
할당 연산자 (:=) 또는 DEFAULT 예약어가 있는 표현식으로 변수를 초기화합니다.
초기값을 할당하지 않으면 값을 할당할 때까지 새 변수에 기본적으로 NULL이 들어갑니다.
변수에 값을 할당하거나 재할당하려면 PL/SQL 할당문을 작성합니다. 그러나 모든 변수를
초기화하는 것은 좋은 프로그래밍 습관입니다.- 가독성 및 코드 유지 관리 효율을 높이기 위해 각 행마다 하나씩 식별자를 선언합니다
❗❗ 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; /
PL/SQL 가장 많이 쓰는 select ... into 절
문제 8. 위의 코드의 v_sal 변수명을 sal 이라고 수정해서 실행하면 실행 되는지 확인하시오
되는디.. ?❓
20. PL/SQL 구조의 이름 지정 규칙
아래의 표처럼 변수명을 명명하기를 지침합니다. (지침이지만 거의 강제)
(DBA가 초반에 지정해줘야한다.)
21. %TYPE 속성 ★
(❗❗이수자 평가 문제)
✅ 다음에 따라 변수를 선언하는 데 사용됩니다.
- 데이터베이스 열 정의
- 다른 선언된 변수
✅ 다음 항목이 앞에 나옵니다.
- 데이터베이스 테이블 (emp) 과 열 이름 (sal)
- 선언된 변수의 이름 (v_sal 이라면, v_sal2%type 으로 쓰면 됨)
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;
/⬇️ emp table 의 sal 의 데이터 타입을 그대로 따르겠다 ★★ 라고 수정입력하기
number(10) -> emp.sal%type
✔️ 변수의 데이터 유형을 지정할때 emp.sal%type 이라고 하게되면
가장 큰 장점이 emp 테이블의 월급의 데이터 유형의 크기가 변경이 되었을때
plsql 코드를 따로 변경해주지 않아도 됩니다.
만약에 아래와 같이 개발자가 PL/SQL 코딩을 했다면?
v_sal number(10);그런데 어느날 dba가 아래와 같이 emp 테이블의 sal을 변경했다.
이렇게 크기를 늘리면 db에 sal과 관련된 모든 변수의 데이터 유형을 number(15)로 변경해주어야한다.alter table emp modify sal number(15);✔️ 애초에 코딩할 때, emp 테이블의 sal 의 데이터타입을 쓰겠다라고 입력하면,
number(15)로 모든 코드를 변경해주지 않아도된다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; /✔️ DBA가 si프로젝트 초반에 또는 운영중에 프로시져를 만드는 단계에서도 반드시 %type 으로 변수를 선언하라고 꼭 얘기해줘야한다.
(요즘에도 %type 을 안써서 dba가 테이블 변경을 했을때 개발자들이 많은 고생을 하는경우가 많다고 합니다.)
PL/SQL 코드작성
문제 9.
아래의 코드를 수정해서 사원번호를 물어보게하고 사원번호를 입력하면, 이번에는 월급과 직업도 같이 출력되게 하시오사원번호를 입력하세요 (7788) 해당사원의 월급은 3000 해당사원의 직업은 ANALYSTset 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. (오늘의 마지막 문제)
사원번호를 물어보게하고 사원번호를 입력하면 해당 사원이 근무하는 부서위치가 출력되게 하시오
(실행문에 JOIN 사용)사원번호를 입력하세요 (7788) 근무하는 부서위치 : DALLASset 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 empno = &p_empno and e.deptno = d.deptno; dbms_output.put_line('근무하는 부서위치: ' || v_loc) ; end; /
글이 많은 도움이 되었습니다, 감사합니다.