Oracle PL (1)

PL 예제

1번

  • EVEN_ODD( ID:NUMBER(4) GUBUN:VARCHAR2(10) ) 테이블을 작성하고 START숫자와 END숫자를 입력 받아 그 사이의 숫자를 ID에 저장하고
    ID의 숫자가 짝수이면 GUBUN에 “짝수”를 홀수이면 GUBUN에 “홀수”라고 입력하는 SCRIPT를 WHILE문으로 작성하여라.
CREATE TABLE EVEN_ODD(
    ID NUMBER(4),
    GUBUN VARCHAR2(10)
);

ACCEPT start PROMPT 'START'
ACCEPT end PROMPT 'END'

DECLARE
    v_start NUMBER := &start;
    v_end NUMBER := &end;
BEGIN
  IF v_start > v_end THEN
        dbms_output.put_line('start가 end보다 큽니다');
    ELSE
        WHILE v_start <= v_end Loop
            IF MOD(v_start,2) = 0 THEN
                INSERT INTO even_odd(id,gubun)
                VALUES(v_start,'짝수');
            ELSE
                INSERT INTO even_odd(id,gubun)
                VALUES(v_start,'홀수');
            END IF;
            v_start := v_start +1;
        END Loop;
        dbms_output.put_line(&start || '로부터 ' || &end || '까지 ' || TO_CHAR(&end - &start + 1)||'개의 자료가 입력 되었습니다.');
    END IF;
END;
/

2번

  • 예제2)사원번호를 입력 받으면 다음과 같이 출력되는 PROCEDURE를 작성하라 Purchasing 부서명의 사원입니다
CREATE or REPLACE PROCEDURE pro_test(p_empno IN NUMBER)
IS
    p_dept departments.department_name%TYPE;
    
BEGIN
    SELECT d.department_name INTO p_dept  -- ,dname 해도 된다.
    FROM employees e, departments d
    WHERE e.department_id = d.department_id AND e.employee_id = p_empno;

    dbms_output.put_line(p_dept || ' 부서명의 사원 입니다');
END;
/

호출 

EXEC pro_test(132);

3번

  • 예제3)사원번호를 입력받고, 소속부서의 최고, 최저연봉 차액을 파라미터로 출력하는 PROCEDURE를 작성하라.
CREATE or REPLACE PROCEDURE pro_sal(p_empno IN NUMBER, subSal OUT NUMBER)
IS
    higtSal NUMBER;
    lowSal NUMBER;
    
    -- deptno employees.department_id%TYPE;
BEGIN
    SELECT MAX(salary),MIN(salary)INTO higtSal,lowSal
    FROM employees
    WHERE department_id =(SELECT department_id
          FROM employees
          WHERE employee_id = p_empno);
    subSal := higtSal-lowSal;
END;
/

호출 

VAR subSal NUMBER;
EXEC pro_sal(120,:subSal);
PRINT subSal;

4번

  • 예제4) 두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.(함수명 add_num)
CREATE FUNCTION sum_test(n1 IN NUMBER, n2 IN NUMBER)RETURN NUMBER
IS
    result NUMBER;
BEGIN
    result := n1 + n2;
    return result;
END;
/

5번

  • 예제5) 부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오. (함수명 get_emp_count)
CREATE or REPLACE FUNCTION get_emp_count(deptno_no IN NUMBER)RETURN NUMBER
IS
    count_emp NUMBER;
BEGIN
    SELECT COUNT(empno)INTO count_emp
    FROM emp
    WHERE deptno = deptno_no
    GROUP BY deptno;
    
    return count_emp;
END;
/

6번

  • 예제6) emp테이블을 이용해서 입사일을 제공하면 근무연차를 구하는 함수를 정의하시오.(소수점 자리 절삭, 함수명 get_info_hiredate)
CREATE OR REPLACE FUNCTION get_info_hiredate(hire_Date emp.hiredate%TYPE)RETURN NUMBER
IS
   v_year NUMBER;
BEGIN
   v_year := TRUNC(MONTHS_BETWEEN(sysdate,hire_date)/12);
   RETURN v_year;
    
END;
/

7번

  • 예제7) emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 정의하시오.(함수명 get_mgr_name)
CREATE or REPLACE FUNCTION get_mgr_name(epm_no IN NUMBER)RETURN VARCHAR2
IS
    m_name VARCHAR2(10);
BEGIN
    SELECT b.ename INTO m_name
    FROM emp a, emp b
    WHERE a.mgr = b.empno AND a.empno = epm_no;
    
    return m_name;
END;
/

8번

  • 예제8) emp테이블을 이용해서 사원번호를 입력하면 급여 등급을 구하는 함수를 정의하시오.
  • (4000~5000 A, 3000~4000미만 B, 2000~3000미만 C, 1000~200미만 D, 1000미만 F,
  • 함수명 get_sal_grade)
CREATE or REPLACE FUNCTION get_sal_grade(emp_no IN NUMBER)RETURN VARCHAR2
IS
    grade VARCHAR2(10);
BEGIN
    SELECT
        CASE WHEN sal >= 4000 AND sal <= 5000 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 INTO grade
    FROM emp
    WHERE empno = emp_no;
    
    return grade;
    
END;
/

PL/SQL (Procedural Language extension to SQL) 

SQL을 확장한 순차적 언어 -> Procedure, Function, Trigger
                             in out     in out
                             insert     select
                             delete
                             update

script 구조

  1. 선언부 : 변수, 상수, 초기화
  2. 실행부 : 실제로 처리할 Query 부분이나 제어문 활용해서 처리하는 부분
  3. 예외처리부: 각종 오류 처리

script

기본적인 틀

DECLARE
  선언부
BEGIN
  실행부
END;
/

※ Oracle 기본 툴에서 콘솔에 출력시 사용 명령어 

SET SERVEROUTPUT ON   ★ 콘솔에 출력을 위해서는 ON 해주어야 한다.

BEGIN
  dbms_output.put_line('Hello Pl');
END;
/

기본적인 문법은 JAVA와 비슷하지만 몇몇 부분이 다르기 때문에 사용하면서 주의해야한다. 예를 들면 := 는 == 와 같고 변수 선언시 자료형은 변수 명 뒤에 기재한다.

DECLARE -- 선언부
    message VARCHAR2(10);   ★ String message와 동일

BEGIN -- 실행부
    message := 'hello pl';   ★ message = "hello pl" 콜론을 붙여주면 에러가 사라진다.
    dbms_output.put_line('message = ' || message);
    
            ★ 예외처리를 하는 위치
END;
/

IF 제어문 

DECLARE
    counter INTEGER;
BEGIN
    counter := 1;
    counter := counter + 1;
    
    IF counter IS NULL THEN           ★  IF counter = 0 THEN 으로 사용 할 수 있다.
        dbms_output.put_line('result: counter is null');
    ELSE 
        dbms_output.put_line('result: counter is not null');
    END IF;
END;
/

FOR 반복문 

DECLARE
    counter INTEGER;
    i INTEGER;
BEGIN
    
    FOR i IN 1..9 LOOP       ★ 안에 1..9 는 범위
        dbms_output.put_line('i =' || i);   
        counter := i*2;
        dbms_output.put_line('2*' || i || '=' ||counter);
    END LOOP;
END;
/

LOOP 

DECLARE
    v_count NUMBER := 0;
    v_total NUMBER := 0;
BEGIN
    LOOP
        EXIT WHEN v_count = 10;     ★ 값이 TRUE일때 탈출하게 된다. 또한 := 을 사용하지 않고 = 만 사용한다.
        
        v_count := v_count + 1;
        v_total := v_total + v_count;
    END LOOP;
    
    dbms_output.put_line('total:' || v_total);
END;
/

WHILE 

DECLARE
    v_count NUMBER :=0;
    
BEGIN
    WHILE v_count < 10
    LOOP
        v_count := v_count +1;
        dbms_output.put_line('v_count = ' || v_count);
    END LOOP;
END;
/

JAVA의 WHILE문과 동일

GOTO

조건에 맞는 출력문으로 이동하여 실행한다.

DECLARE
    v_name VARCHAR2(10) := 'Lee';
    v_case NUMBER := 0;
BEGIN
    CASE 
        WHEN MOD(v_case, 2) = 0 THEN
            GOTO test1;
        WHEN MOD(v_case, 2) = 1 THEN
            GOTO test2;
        ELSE
            GOTO err;
    END CASE;
    
<<test1>>
    dbms_output.put_line(v_name || ' is woman');
    GOTO sub_end;
<<test2>>
    dbms_output.put_line(v_name || ' is men');
    GOTO sub_end;
<<err>>
    dbms_output.put_line('ERROR');
    
<<sub_end>>
    dbms_output.put_line('Exit');
END;
/

예외처리

만약 0으로 정수를 나누려 할때 예외가 발생한다. 이때 EXCEPTION WHEN OTHERS THEN 을 이용하여 처리해준다.

DECLARE
    counter INTEGER;
BEGIN
    counter := 1;
    counter := counter / 0;
    
EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('예외가 발생하였습니다.');
END;
/

varray

배열과 같은 의미의 자료형태이다.

DECLARE
    TYPE varray_test IS VARRAY(3)OF INTEGER;      ★  int varray_test = new int[3]; 와 같으나 오라클은 타입을 설정 varray_test 는 생성 변수
    vArr varray_test;                             ★ 실제 배열 변수는 vArr (값을 넣을땐 vArr 변수를 이용해야한다.)
BEGIN
    vArr := varray_test(11,22,33);
   
    dbms_output.put_line('vArr(1)=' || vArr(1));
END;
/

오라클은 0 번째가 없기 때문에 11이 출력되는것을 알 수 있다.

ACCEPT (WINDOW창에 값 입력) 

ACCEPT p_deptno PROMPT '부서번호를 입력하시오(급여의 합)'

DECLARE 
    v_salTotal NUMBER;
BEGIN
    SELECT SUM(salary) INTO v_salTotal        ★ 합계의 값이 v_salTotal로 넘긴 후 출력 하도록 해야한다.
    FROM employees
    WHERE department_id = &p_deptno;          ★ 외부에서 값을 입력받은 변수를 기재할때는 & 을 붙여야 한다.
    
    dbms_output.put_line(&p_deptno|| '번 부서 급여 합 = ' || TO_CHAR(v_salTotal,'$999,999,999'));
END;
/

만약 문자열로 입력을 받을 경우

ACCEPT p_empname PROMPT '사원명 입력(급여)'

DECLARE
    v_name employees.last_name%TYPE := '&p_empname'; ★ 문자열은 ''안에 넣어야한다.

변수를 만들어 주고 ‘‘안에 넣어주고 이용을 해야한다.