SQL [20] - PL/SQL

1. PL/SQL이란?

오라클에서 제공하는 프로그래밍 언어

일반 프로그래밍 언어적인 요소를 다 가지고 있어서 실무에서 요구되는 절차적인 데이터 처리를 다 할 수 있다.

특히 SQL과 연동되어 막강한 기능을 구현할 수 있다.

 

2. PL/SQL의 구조

선언부(Declare) / 실행부(Begin) / 예외처리부(Exception)로 구성된다.

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

Executable - 제어문, 반복문, 함수 정의 등의 로직을 기술함

Exception - 실행 도중 에러 발생시 해결하는 문장들을 기술함

 

** PL/SQL의 결과를 출력해보기 위해서는, SET SERVEROUTPUT ON; 명령어를 통하여 출력 기능을 활성화해야 합니다. 

 

-- 예시 양식 

Declare 

 vno number(4) ; 

 vname varchar2(10);1

Begin 

 select empno, ename into vno, vname

 from emp

 where empno = 7900 ; 

dbms_output.put_line(vno|| ' ' ||vname);

end;

 

-- Quiz. professor 테이블에서 교수번호가 1001 번인 교수의 교수번호와 급여를 조회 한 후 변수에 저장 후 출력하기

Declare

  v_profno professor.profno%type;

  v_pay professor.pay%type;

Begin

  select profno, pay into v_profno, v_pay

  from professor

  where profno = '1001';

dbms_output.put_line(v_profno||'번 교수의 급여는' ||v_pay||'입니다');

end;

 

-- <1> 커서사용 emp 테이블에서 부서번호가 20번인 사원의 번호, 이름, 급여를 화면에 출력하기
declare
  vempno number(4);
  vename varchar2(20);
  vsal number(7); 
  cursor c1 is select empno, ename, sal from emp where deptno = 20;
begin
 open c1;
 dbms_output.put_line('번호  이름  급여');
 loop
  fetch c1 into vempno, vename, vsal;
  exit when c1%notfound;
  dbms_output.put_line(vempno||' '||vename||' '||vsal);
  end loop;
  close c1;
end;

/*
c1 : emp 테이블에서 부서 번호가 20인 테이블의 empno, ename, sal 정보를 저장하기 위한 선언 
open c1 : 정의된 c1 커서를 실행. c1에 결과가 저장됨
해드라인 번호 이름 급여 출력하고
loop <- basic loop 기본 반복문 
fetch ~ end loop; : 기본 반복문의 시작부터 종료까지
fetch c1 : c1의 값 중 하나의 레코드 값을 조회해(fetch) / 첫번째~n번째 (exit when c1%notfound면 그만 c1커서의 fetch가 종료 예외 발생)
exit when : 조건 만족시 반복문 종료
end; pl/sql 끝남을 의미
*/

-- <2> 커서없이 반복문 이용해서 풀어보기
-- emp 테이블에서 부서번호가 20번인 사원의 번호, 이름, 급여를 화면에 출력하기
declare
begin
 dbms_output.put_line('번호  이름  급여');
 for i in (select empno, ename, sal from emp where deptno=20) loop
  dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal);
  end loop;
end;

-- <3> 커서있고 반복문을 이용해서 실행하기
-- emp 테이블에서 부서번호가 20번인 사원의 번호, 이름, 급여를 화면에 출력하기
declare
 cursor c1 is select empno, ename, sal from emp where deptno=20;
begin
 dbms_output.put_line('번호  이름  급여');
 for i in c1 loop
  dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal);
  end loop;
end;

/*
** 커서 반복문 
1) for문을 사용하거나
2) open / fetch 사용할 수 있다.
*/

-- 조건문 : if 조건문 
-- 1. emp 테이블에서 사번, 이름, 부서, 부서명을 출력하기
-- 부서번호 : 10, 부서명 : ACCOUNT 
-- 부서번호 : 20, 부서명 : RESEARCH 
-- 부서번호 : 30, 부서명 : SALES 
-- 부서번호 : 40, 부서명 : OPERATIONS

-- <조건문 사용하여 한 행 출력>
declare
  vno emp.empno%type;
  vname emp.ename%type;
  vdeptno emp.deptno%type;
  vdname varchar2(20) := null;
begin
  select empno, ename, deptno into vno, vname, vdeptno from emp
  where empno = 7900;
  if(vdeptno = 10) then vdname := 'ACCOUNT';
  elsif (vdeptno = 20) then vdname := 'RESEARCH';
  elsif (vdeptno = 30) then vdname := 'SALES';
  else
    vdname := 'OPERATIONS';
  end if; 
  dbms_output.put_line(vno||' '||vname||' '|| vdeptno||' '|| vdname);
end;

/*
  조건문 : 결과가 참 또는 거짓의 결과를 가지는 구문
  a 변수 : 100 
  a > 10 : 참, True
  a < 100 : 거짓, False
  a = 100 : 참
  a <= 100 : 참 
  if(조건문1) then 문장 => 조건문1의 결과가 참인 경우 실행되는 문장
  elsif (조건문2) then 문장 => 조건문1의 결과가 거짓이고, 조건문2의 결과가 참인 경우 실행되는 문장
  ...
  else 문장 => 위의 모든 조건문의 결과가 거짓인 경우 실행되는 문장
  end if; 
*/

/*
  ' = ' => 양변이 같냐. 등가 연산자 
  vdeptno = 10 => vdeptno가 10이니?
  
  ' := ' => 값을 입력하라. 대입 연산자
  vdeptno := 10 => vdeptno에 10을 입력해줘   
  
  where 조건문에서는
  profno = '&profno' / O
  profno := '&profno' / X
  
  입력하는 값은 
  등가 연산자 = / X
  대입 연산자 := / O
*/

-- 조건문 : if 조건문 
-- 1. emp 테이블에서 사번, 이름, 부서, 부서명을 출력하기
-- 부서번호 : 10, 부서명 : ACCOUNT 
-- 부서번호 : 20, 부서명 : RESEARCH 
-- 부서번호 : 30, 부서명 : SALES 
-- 부서번호 : 40, 부서명 : OPERATIONS

-- <조건문 사용하여 for로 여러행 출력>
declare
  vdname varchar2(20) := null;
begin
  dbms_output.put_line('사원번호 사원명 부서코드 부서명');
  for i in(select empno, rpad(ename,10,' ') ename, deptno from emp) loop
  if(i.deptno = 10) then vdname := 'ACCOUNT';
  elsif (i.deptno = 20) then vdname := 'RESEARCH';
  elsif (i.deptno = 30) then vdname := 'SALES';
  else
    vdname := 'OPERATIONS';
  end if; 
  dbms_output.put_line(i.empno||'   '||i.ename||'  '||i.deptno||'  '||vdname);
  end loop;
end; 

/*
select 문에 rpad와 알리아스를 추가하여, 결과 값을 깔끔하게 만들 수 있다.
*/

-- 위의 식에서, 조건 추가하여 출력하기 ex) 사원번호가 7369
declare
  vdname varchar2(20) := null;
begin
  dbms_output.put_line('사원번호  사원명  부서코드  부서명');
  for i in(select empno, rpad(ename,10,' ') ename, deptno from emp where empno='7369') loop
  if i.deptno = 10 then vdname := 'ACCOUNT';
  elsif i.deptno = 20 then vdname := 'RESEARCH';
  elsif i.deptno = 30 then vdname := 'SALES';
  else
    vdname := 'OPERATIONS';
  end if; 
  dbms_output.put_line(i.empno||'   '||i.ename||'  '||i.deptno||'  '||vdname);
  end loop;
end; 

-- <case 조건문>
declare
  vdname varchar2(20) := null;
begin
  dbms_output.put_line('사원번호  사원명  부서코드  부서명');
  for i in(select empno, rpad(ename,10,' ') ename, deptno from emp) loop
  vdname := case i.deptno
  when 10 then 'ACCOUNT'
  when 20 then 'RESEARCH'
  when 30 then 'SALES'
  else 'OPERATIONS'
  end;
  dbms_output.put_line(i.empno||'   '||i.ename||'  '||i.deptno||'  '||vdname);
  end loop;
end; 

/*
PLSQL을 이용해서 삭제 또는 수정한다.
*/

-- 부서코드를 입력받아서 해당 부서의 사원을 삭제한 후, 삭제된 사원의 인원수를 출력하기
declare
begin
 delete from emp where deptno='&dno';
 dbms_output.put_line('삭제 건수:'|| SQL%ROWCOUNT);
 rollback; -- 삭제취소
end;

select * from emp where deptno = 10;

/*
sql%rowcount : 커서 속성
결과 정보 저장 : 레코드 건수 저장
*/

drop table dept3;

create table dept3
as select * from dept2 where 1=2;
truncate table dept3;

select * from dept3;

-- dept2 테이블에서 부서코드(dcode)가 1000번대인 부서를 dept3 테이블에 데이터를 저장하고,
-- 저장된 레코드 건수를 출력하는 PLSQL 작성하기 
declare
begin
 insert into dept3 select * from dept2 where dcode between 1000 and 1999;
 dbms_output.put_line('추가건수:'||SQL%ROWCOUNT);
end;


-- sql%rowcount : 결과의 레코드의 건수 

-- <<저장 프로시저(Stored Procedure)>> --
-- PLSQL에서 작성된 내용(프로시저)를 저장하는 객체
-- 1. empcopy 테이블의 사원번호를 입력 받아서 삭제하는 procedure 작성하기
-- (1) emp 테이블과 같은 내용의 테이블 empcopy 테이블 생성하기 (데이터 보호 차원) 
create table empcopy as select * from emp;
select * from empcopy;

-- (2) empcopy로부터 번호를 입력받아서 그 번호를 삭제해주는 프로그램을 작성하기
-- empcopy_del : 프로시저 이름
--               empcopy_del 이름을 가진 프로시저 저장 
create or replace procedure empcopy_del(vempno empcopy.empno%type)
is
begin
  delete from empcopy where empno=vempno;
end;

show errors; -- 오류 확인

/*
empcopy_del <-프로시저 이름
(vempno empcopy.empno%type) <- 매개변수 vempno는 empcopy 테이블의 empno의 타입

y = f(x) 함수에서
y = x^2
9 = f(3) 에서 3은 매개 변수이다. 9는 리턴 값이다. 
*/ 

-- (3) empcopy_del 프로시저 실행하기 / empno가 9000번인 레코드 삭제하기
exec empcopy_del(9000);
select empno from empcopy;

-- 7782번 사원번호 삭제하기
exec empcopy_del(7782);
select empno from empcopy;

-- 학과코드를 입력 받아서, 학생들의 학번, 이름, 학과코드, 학과명을 출력하는 프로시저(stud_info)를 생성하기
create or replace procedure stud_info(vdeptno student.deptno1%type)
is
begin
  for a in(select s.studno, s.name, s.deptno1, d.dname from student s, department d
            where s.deptno1 = d.deptno and s.deptno1 = vdeptno) loop
    dbms_output.put_line(a.studno || ' ' || a.name || ' ' || a.deptno1 || ' ' || a.dname);
  end loop;
end;

show errors;

exec stud_info(102);

/*
create or replace procedure stud_info <- 이름
*/

-- 학생의 학년을 입력받아 해당 학년의 학년, 평균 몸무게, 평균 키를 출력하는 프로시저(stud_avg) 생성하기
-- 1학년 정보를 출력하기
create or replace procedure stud_avg(vgrade student.grade%type)
is
begin
  for a in(select grade, avg(weight) avg_w, avg(height) avg_h from student 
            where grade = vgrade group by grade) loop
    dbms_output.put_line('학년: '||a.grade||' 평균 몸무게:'|| a.avg_w || ' 평균 키: ' || a.avg_h);
  end loop;
end;

exec stud_avg(2);