23.10.04(수) 23일차 - PL/SQL

조건문

if문과 case문 둘 중 하나 사용하면 된다.

 

if 문

case문 

---

반복문

Loop문

 - 무한 반복이기 때문에 조건 체크가 필요하다. 

위 if문을 더 간단하게

 

while-loop문

 

while-loop문에서는 result_num 도 초기화 작업을 해야 한다!!

for loop문

in reverse 사용

GOTO문

 - 코드만 길어지고, 잘 사용하지 않는다. 

 

커서   - 중요!!!!

   - SELECT 문장을 실행하면 조건에 따른 결과가 추출된다. 

  - 추출되는 결과는 한 건이 될 수도 있고 여러건이 될 수도 있으므로 이를 결과 셋(result set) 혹은 결과집합이라고 부르기도 한다.
  - 쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 PL/SQL에서는 바로 커서(cursor)를 사용하여 이 결과집합에 접근할 수 있다. 

  - 즉 커서를 사용하면 결과집합의 각 개별 데이터에 접근이 가능하다.

 

---

function은 반드시 반환하는 데이터가 있음, procedure는 반환하는 데이터가 없음

자바에서는 function과 procedure를 합쳐서 메서드로 사용한다. 

PL/SQL에서는 function과 procedure를 구분한다.

함수

[함수 만들기]

[위 함수를 이용한 출력]

 

[특정 테이블에서 정보를 읽어와서 활용하는 함수]

SELECT empno,ename,sal,comm,TAX2(sal,comm) AS tax FROM emp;

예제>

 

select empno,ename,sal,comm, TAX3(sal,comm) AS tax FROM emp;

예제>

[특정 테이블에 종속적인 함수]

INTO 키워드를 이용하면 커서를 이용하지 않아도 된다(단, 하나의 행만 있을 경우)

여러행일 경우 반드시 커서를 이용해야 한다.

 

SELECT EMP_SALARIES(7839) FROM dual;

SELECT EMP_SALARIES(9000) FROM dual;

없는 사원번호를 입력하면 null을 반환한다

---

예제> 

SELECT GET_DEPT_NAME(10) FROM dual;

---

[실습문제]

실습 1번

실습 2번

SELECT deptno,dname,GET_EMP_COUNT(deptno) 사원수 FROM dept;

실습 3번

실습 4번

 서브쿼리와 조인 2가지 방법이 있다. 

1) 서브쿼리 

 

2) 조인 

실습 5번

1) case when then else 이용

SELECT ename,sal,GET_SAL_GRADE(empno) 급여등급 FROM emp ORDER BY sal DESC;

 

2) 조인 이용

 

실습 6번

1) 서브쿼리

 

2) 조인

INNER JOIN 사용

 

SELECT empno,ename,FIND_LOC(empno) 근무지 FROM emp;

SELECT FIND_LOC(7698) FROM dual;

---

 프로시저

여러개의 테이블을 access하면서 insert,delete 등 반복적인 작업을 할 때 사용

 

프로시저 실행

EXEC 또는 EXECUTE

예제>

60번 부서가 추가된 dept 테이블

 

exec문을 한번 더 실행하면 아래처럼 출력됨(예외처리 됨)

---

예제

사원번호가 9000인 사람의 정보가 insert 되었다

---

예제

exec 실행 출력값

 

---

 

pk가 아니면 커서로 만들어야 한다. 

if 문

declare
 grade char(1);  --고정길이여서 데이터를 꼭 지정해줘야 한다. 
begin
 grade := 'B';
 if grade = 'A' then 
  dbms_output.put_line('Excellent');
 elsif grade = 'B' then
  dbms_output.put_line('Good');
 elsif grade = 'C' then 
  dbms_output.put_line('Fair');
 elsif grade = 'D' then 
  dbms_output.put_line('Poor');   --else 생략 가능
 end if;
end;
 
case 문

declare 
 grade char(1);
begin
 grade := 'B';
 case grade
 when 'A' then 
  dbms_output.put_line('Excellent');
 when 'B' then 
  dbms_output.put_line('Good');
 when 'C' then 
  dbms_output.put_line('Fiar');
 when 'D' then 
  dbms_output.put_line('Poor');
 else
  dbms_output.put_line('Not Found');
 end case;
end;

Loop문

declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 loop 
  result_num := 2 * test_number;
  if result_num > 20 then
   exit; -- 블록 종료
  else 
   dbms_output.put_line(result_num);
  end if;
   test_number := test_number + 1;
 end loop;
  -- loop 블럭을 빠ㅣ져나오면 아래 코드를 실행함
  dbms_output.put_line('프로그램 끝');
end;


declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 
 loop
  result_num := 2 * test_number;
  exit when result_num > 20;
  dbms_output.put_line(result_num);
  test_number := test_number + 1;
 end loop;
end;

while-loop문

declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 result_num := 0;

 while result_num < 20 loop
  result_num := 2 * test_number;
  dbms_output.put_line(result_num); 
  --test_number를 증가시키지 않으면 무한루프에 빠짐
  test_number := test_number + 1;
 end loop;
end;

for loop문

declare
 test_number integer;
 result_num integer;
begin
 for test_number in 1..10 loop  --별도 증가 코드를 넣을 필요 없다.
  result_num := 2 * test_number;
  dbms_output.put_line(result_num);
 end loop;
end;


declare
 test_number integer;
 result_num integer;
begin
 for test_number in reverse 1..10 loop  
  result_num := 2 * test_number;
  dbms_output.put_line(result_num);
 end loop;
end;

커서
SELECT 문장을 실행하면 조건에 따른 결과가 추출된다. 추출되는 결과는 한 건이 될 수도 있고 
여러건이 될 수도 있으므로 이를 결과 셋(result set) 혹은 결과집합이라고 부르기도 한다.
쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 PL/SQL에서는 바로 커서(cursor)를
사용하여 이 결과집합에 접근할 수 있다. 즉 커서를 사용하면 결과집합의 각 개별 데이터에 접근이 가능하다.

declare
 cursor emp_csr is
 SELECT empno
 FROM emp
 WHERE deptno=10;
 -- empno의 타입을 읽어오라는 뜻
 emp_no emp.empno%type; --emp_no 라는 변수를 하나 만듦
begin
 -- 커서 열기 : 커서로 정의된 쿼리를 실행하는 역할
 open emp_csr;
 
 loop 
  --커서를 이용해서 행에 접근, 컬럼값을 emp_no에 할당한다.
  fetch emp_csr into emp_no;  --fetch: 할당한다
  --%notfound:커서에서만 사용 가능한 속성
  --더 이상 패치(할당)할 로우가 없음을 의미
  exit when emp_csr%notfound; --조건 체크
  dbms_output.put_line(emp_no); 
  end loop;
  close emp_csr;
end;

함수

입력받은 값으로부터 10%의 세율을 얻는 함수
create or replace function tax(p_value in number) 
 return number
is 
begin
 return p_value * 0.1;
end;

SELECT TAX(100) FROM dual;
SELECT ename,sal,TAX(sal) tax,sal-TAX(sal) "실지급 급여" FROM emp;

급여와 커미션을 합쳐서 세금 계산
create or replace function tax2(p_sal in emp.sal%type,
                                p_bonus emp.comm%type)  --in은 생략 가능
 return number
is
begin
 return (p_sal + NVL(p_bonus,0)) * 0.1; --comm에 null값이 많아서 NVL사용
end;

SELECT empno,ename,sal,comm,TAX2(sal,comm) AS tax FROM emp;

급여(커미션 포함)에 대한 세율을 다음과 같이 정의함. 급여가 월 $1,000보다 적으면
세율을 5% 적용하며, $1,000이상 $2,000이하이면 10%, $2,000을 초과하면 20%를 적용함

create or replace function tax3(p_sal emp.sal%type,
                                P_bonus emp.comm%type)
 return number   --반환 타입은 number
is
 e_sum number;
 e_tax number;
begin
 e_sum := p_sal + NVL(p_bonus,0);
 
 if e_sum<1000 then
  e_tax := e_sum * 0.05;
 elsif e_sum <= 2000 then
  e_tax := e_sum * 0.1;
 else
  e_tax := e_sum * 0.2;  
 end if;
 return e_tax;  -- 끝내기 전에 반환해줘야 한다!!!
end;

select empno,ename,sal,comm, TAX3(sal,comm) AS tax FROM emp;


사원번호를 통해서 급여를 알려주는 함수
create or replace function emp_salaries(emp_no number)
 return number
is
 nSalaries number(9); -- nSalaries에 급여를 담는다
begin
 nSalaries := 0; -- 이 함수에서는 초기화 생략 가능하다
 SELECT sal
 -- 결과행이 단일행일 경우 INTO를 이용해서 읽어온 값을 변수에 담을 수 있음
 INTO nSalaries
 FROM emp
 WHERE empno = emp_no;
 
 return nSalaries;
end;

SELECT EMP_SALARIES(7839) FROM dual;
SELECT EMP_SALARIES(9000) FROM dual;

부서번호를 전달하면 부서명을 구할 수 있는 함수
create or replace function get_dept_name(dept_no number)
 return varchar2 --부서번호여서 varchar2
is 
 sDeptName varchar2(30);
begin
 SELECT dname 
 INTO sDeptName
 FROM dept
 WHERE deptno=dept_no;
 
 return sDeptName;
end;

SELECT GET_DEPT_NAME(10) FROM dual;  --특정 테이블을 이용하는 거라 dual 써야한다.
--dept 테이블로 하면 5개가 나와버린다

[실습문제]
1) 두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오(add_num) 

create or replace function add_num(num1 integer, num2 integer)
 return integer
is
begin
 return num1 + num2;
end;

SELECT ADD_NUM(2,5) FROM dual;
SELECT ename, ADD_NUM(sal,NVL(comm,0)) "실급여" FROM emp;

2)부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오.(get_emp_count) 

create or replace function get_emp_count(dept_no emp.deptno%type)
 return integer
is
 emp_count integer;
begin
 SELECT COUNT(empno)  --count(empno)로 해야 null값이 포함되지 않는다. 
 INTO emp_count
 FROM emp 
 WHERE deptno = dept_no;
 return emp_count;
end;

SELECT deptno,dname,GET_EMP_COUNT(deptno) 사원수 FROM dept;

3)emp테이블의 입사일을 입력하면 근무연차를 구하는 함수를 정의하시오.                
   (소수점 자리 절삭, get_info_hiredate)

create or replace function get_info_hiredate(hire_date emp.hiredate%type)
 return number
is 
begin
 return TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12);
end;

SELECT ename,GET_INFO_HIREDATE(hiredate) 근무연차 FROM emp;

4) emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 
 정의하시오. (get_mgr_name)
 
<1번 방법>  <서브쿼리 이용>
create or replace function get_mgr_name(emp_no emp.empno%type)
 return varchar2
is 
 m_name varchar2(10);
begin
 SELECT ename
 INTO m_name
 FROM emp
 WHERE empno=(SELECT mgr FROM emp WHERE empno=emp_no);
 return m_name;
end;

<2번 방법> <조인>
create or replace function get_mgr_name(emp_no emp.empno%type)
 return varchar2
is 
 m_name varchar2(10);
begin
 SELECT m.ename  --관리자 이름
 INTO m_name
 FROM emp a, emp m
 WHERE a.mgr = m.empno
 AND a.empno=emp_no;
 return m_name;
end;

SELECT empno,ename,GET_MGR_NAME(empno) "관리자 이름" FROM emp;

5)emp테이블을 이용해서 사원번호를 입력하면 급여등급을 구하는 함수를 정의하시오.  
  (get_sal_grade)

<1번 방법> <case when then else>
create or replace function get_sal_grade(emp_no emp.empno%type)
 return char
is 
 sgrade char(1);
begin
 SELECT CASE WHEN sal>=4000 THEN 'A'
             WHEN sal>=3000 AND sal<4000 THEN 'B'
             WHEN sal>=2000 AND sal<3000 THEN 'C'
             WHEN sal>=1000 AND sal<2000 THEN 'D'
             ELSE 'F'
        END grade --SQL문 안에서는 END CASE X, END O
           --grade는 알리아스
 INTO sgrade
 FROM emp
 WHERE empno=emp_no;
 return sgrade;
end;

<2번 방법>   <조인>                                                    
create or replace function get_sal_grade(emp_no emp.empno%type)
 return number
is 
 sgrade number;
begin
 SELECT s.grade
 INTO sgrade
 FROM emp e, salgrade s
 WHERE e.sal BETWEEN s.losal AND s.hisal 
 AND e.empno=emp_no;
 return sgrade;
end;

SELECT ename,sal,GET_SAL_GRADE(empno) 급여등급 FROM emp ORDER BY sal DESC;

6) 사원번호를 입력하면 근무지를 구하는 함수(find_loc)             
<1> 서브쿼리
create or replace function find_loc(emp_no number)
 return varchar2
is
 dept_loc varchar2(14);
begin 
 SELECT loc
 INTO dept_loc
 FROM dept 
 WHERE deptno=(SELECT deptno FROM emp WHERE empno=emp_no);
 return dept_loc;
end;

<2> 조인
create or replace function find_loc(emp_no number)
 return varchar2
is
 dept_loc varchar2(14);
begin 
 SELECT d.loc
 INTO dept_loc
 FROM emp e INNER JOIN dept d
 ON e.deptno=d.deptno 
 WHERE e.empno=emp_no;
 return dept_loc;
end;

SELECT empno,ename,FIND_LOC(empno) 근무지 FROM emp;
SELECT FIND_LOC(7698) FROM dual;

프로시저

create or replace procedure hello_world(p_message in varchar2)
is
begin
 dbms_output.put_line(p_message);
end;

execute hello_world('Korea');
exec hello_world('Seoul');


부서테이블에 부서정보를 입력하는 프로시저를 생성 --(원래는 프로시저로 할 필요 없이 insert문으로 하면 된다)
create or replace procedure add_department(p_deptno in dept.deptno%type,
                                           p_dname in dept.dname%type,
                                           p_loc in dept.loc%type)
is
begin
 INSERT INTO dept
 VALUES (p_deptno,p_dname, p_loc);
 COMMIT; --SQL문이 정상적으로 수행되면 COMMIT
 
 exception when others then 
 dbms_output.put_line(p_dname || ' register is failed');  --   문자 연결 연산자 ||
 ROLLBACK; --SQL문이 비정상 수행되면 ROLLBACK
end;

exec add_department(60,'IT SERVICE','BUSAN');

사원테이블에 사원정보를 저장                                             
create or replace procedure register_emp(
                            e_no number,
                            e_name varchar2, 
                            e_work varchar2,
                            e_mgr number,
                            e_sal number,
                            e_comm number, 
                            e_deptno number)
is
begin
 INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
 VALUES (e_no,e_name,e_work, e_mgr,SYSDATE, e_sal,e_comm,e_deptno);
 COMMIT;
 
 exception when others then
 dbms_output.put_line(e_name || ' register is failed');
 ROLLBACK;
end;

execute register_emp(9000,'PETER','MANAGER',7902,6000,200,30);

부서번호를 통해서 부서명과 부서의 위치 구하기                                       
create or replace procedure output_department(p_dept_no in dept.deptno%type)
is
 d_dname dept.dname%type;
 d_loc dept.loc%type;
begin
 SELECT dname,loc 
 INTO d_dname, d_loc --하나의 레코드이기 때문에 INTO 사용 가능. 
 FROM dept
 WHERE deptno=p_dept_no;
 
 dbms_output.put_line(d_dname || ',' || d_loc);
end;

exec output_department(10);

입사연도를 입력해서 해당 연도에 입사한 사원의 사원번호,이름,급여를 출력                   
create or replace procedure info_hiredate(p_year in varchar2)
is
 -- %rowtype으로 데이터 타입이 지정되어 있는 사원테이블(emp)의 하나의 행이 가지고 있는 
 -- 모든 컬럼의 데이터 타입을 가져옴
 e_emp emp%rowtype;
begin
 SELECT empno,ename,sal 
 INTO e_emp.empno, e_emp.ename,e_emp.sal
 FROM emp
 WHERE TO_CHAR(hiredate,'YYYY') = p_year;
 
 dbms_output.put_line(e_emp.empno || ' ' || e_emp.ename || ' ' || e_emp.sal);
end;

하나의 행이 반환되어 에러가 발생하지 않음
exec info_hiredate('1980');
여러개의 행이 반환되어 에러발생
exec info_hiredate('1981');