PL/SQL - DAY 3

[문제5]

입력값(bind 변수)으로 숫자를 받아서 짝수면 짝수라고 출력, 홀수면 홀수라고 출력하는 프로그램을 작성해주세요.

<풀이>

variable b_cnt number
execute :b_cnt := 5

BEGIN
    IF mod(:b_cnt,2) = 0 THEN
        dbms_output.put_line('짝수');
    ELSE
        dbms_output.put_line('홀수');
    END IF;
    
END;
/

[문제6]

빵이 10개가 있습니다. 하나씩 먹고 다 먹었으면 '빵을 다 먹었습니다.' 라는 문구를 출력하고 종료하는 익명블록 프로그램을 작성해 주세요. 단, WHILE문을 이용해 주세요.

<풀이>

DECLARE
    v_cnt number := 10;
BEGIN
    WHILE v_cnt > 0 LOOP 
        v_cnt := v_cnt -1;
        dbms_output.put_line('빵을 1개 먹었습니다. 남은 빵의 갯수는 '||v_cnt||'개 입니다.');
    END LOOP;
    dbms_output.put_line('빵을 다 먹었습니다.');
END;
/

[문제7]

1부터 10까지 홀수만 출력하고 짝수면 CONTINUE문을 이용해서 다음 루프를 수행하는 프로그램을 작성해주세요. 단 FOR문을 이용해주세요.

<풀이>

DECLARE
    start_num number := 1;
    end_num number := 10;
BEGIN
    FOR i IN start_num..end_num LOOP
        IF mod(i,2) =0 THEN
            CONTINUE;
        END IF;
            dbms_output.put_line(i);      
    END LOOP;
END;
/

<다른풀이>

DECLARE
    start_num number := 1;
    end_num number := 10;
BEGIN
    FOR i IN start_num..end_num LOOP
            CONTINUE WHEN mod(i,2) = 0;
            dbms_output.put_line(i);
    END LOOP;
END;
/

[문제8]

구구단 중에 2단을 기본 LOOP, WHILE, FOR 문으로 프로그램을 생성해주세요.

<기본 LOOP 풀이>

DECLARE
    v_cnt number := 2;
    v_cnt_2 number := 1;
BEGIN
    LOOP
        dbms_output.put_line(v_cnt|| ' * ' || v_cnt_2 || ' = ' || v_cnt*v_cnt_2);
        v_cnt_2 := v_cnt_2 + 1;
        EXIT WHEN v_cnt_2 = 10;
    END LOOP;

END;
/

<WHILE 풀이>

DECLARE
    v_cnt number := 2;
    v_cnt_2 number := 1;
BEGIN
    WHILE v_cnt_2 <10 LOOP
        dbms_output.put_line(v_cnt|| ' * ' || v_cnt_2 || ' = ' || v_cnt*v_cnt_2);
        v_cnt_2 := v_cnt_2 + 1;
    END LOOP;

END;
/

<FOR 풀이>

DECLARE
    v_cnt number := 2;
BEGIN
    FOR i in 1..9 LOOP
        dbms_output.put_line(v_cnt|| ' * ' || i || ' = ' || v_cnt*i);
    END LOOP;

END;
/

[문제9]

구구단 2단~ 9단까지를 기본 LOOP, WHILE, FOR 문으로 프로그램을 생성해주세요.

<기본 LOOP 풀이>

DECLARE
    v_cnt number := 2;
    v_cnt_2 number;
BEGIN
    LOOP
        v_cnt_2 := 1;
        LOOP
            dbms_output.put_line(v_cnt|| ' * ' || v_cnt_2 || ' = ' || v_cnt*v_cnt_2);
            v_cnt_2 := v_cnt_2 + 1;
            EXIT WHEN v_cnt_2 = 10;
        END LOOP;
        dbms_output.put_line('');
        v_cnt := v_cnt + 1;
        EXIT WHEN v_cnt = 10;
    END LOOP;
END;
/

<WHILE 풀이>

DECLARE
    v_cnt number := 2;
    v_cnt_2 number;
BEGIN
    WHILE v_cnt <10 LOOP
        v_cnt_2 := 1;
        WHILE v_cnt_2 < 10 LOOP
            dbms_output.put_line(v_cnt|| ' * ' || v_cnt_2 || ' = ' || v_cnt*v_cnt_2);
            v_cnt_2 := v_cnt_2 + 1;
        END LOOP;
        dbms_output.put_line('');
        v_cnt := v_cnt + 1;
    END LOOP;
END;
/

<FOR 풀이>

BEGIN
    FOR i in 2..9 LOOP
        FOR j in 1..9 LOOP
            dbms_output.put_line(i|| ' * ' || j || ' = ' || i*j);
        END LOOP;
        dbms_output.put_line('');
    END LOOP;

END;
/

GOTO문

• 코드내의 특정 레이블로 이동하는 문
• 가독성 저하, 유지보수 어려움, 구조적 프로그래밍(루프,조건문,함수)을 위반시킨다.

<기본 문법>

DECLARE
    i number := 1;
BEGIN 
    <<location>>
        dbms_output.put_line('2 * ' || i || ' = ' || 2*i);
        i := i+1;
        IF i <=9 THEN
            GOTO location;
        END IF;
END;
/

<구구단 활용>

DECLARE
    dan number := 2;
    j number;
BEGIN
        <<dan_loc>>
            v_cnt_2 := 1;
            <<j_loc>>
            dbms_output.put_line(v_cnt|| ' * ' || v_cnt_2 || ' = ' || v_cnt*v_cnt_2);
            v_cnt_2 := v_cnt_2 + 1;
            IF v_cnt_2 <= 9 THEN
                GOTO j_loc;
            ELSE
                dbms_output.put_line('');
                v_cnt := v_cnt+1;
                IF v_cnt <= 9 THEN
                    GOTO dan_loc;
                END IF;
            END IF;
END;
/

PL/SQL의 SQL문

• SELECT문을 사용하여 데이터베이스에서 있는 행을 검색한다.
• DML문을 이용해서 데이터베이스에 있는 데이터(행) 조작한다.
• COMMIT, ROLLBACK, SAVEPOINT문을 사용하여 transaction을 제어할 수 있다.

SQL CURSOR(커서)

• CURSOR는 ORACLE SERVER에서 할당한 전용 메모리 영역에 대한 포인터 입니다.
• CURSOR는 SQL문 실행 메모리 영역(parse, bind, execute,fetch)
• implicit cursor(암시적 커서) and explicit cursor(명시적 커서)
  • 암시적 커서 : 오라클 서버가 SQL문 처리하기 위해 내부적으로 생성하고 관리한다.
  • 명시적 커서 : 1) 프로그래머가 명시적 생성하고 관리해야한다.
                      2)여러건에 행을 fetch해서 조작할 때 사용.

1) parse - syntax error(문법오류), semantic error(유효값 오류), 실행계획을 만듬
2) bind - 변수처리
3) execute - 데이터베이스에서 데이터를 가져와 cursor에 active set을 생성
4) fetch - active set을 user process에 전달

implicit cursor(암시적 커서)

• SELECT ...INTO(FETCH절)...
  • 반드시 1개 행(ROW)만 FETCH 해야한다.
  • 0개 : NO_DATA_FOUND 오류
  • 2개 이상 : TOO_MANY_ROWS 오류, 해결방법 - 명시적커서를 이용해야한다.
• DML(INSERT, UPDATE, DELETE, MERGE)

DECLARE
    v_id number;
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT employee_id, last_name,salary
    INTO v_id, v_name, v_sal 
    FROM hr.employees 
    WHERE department_id = 20;
    dbms_output.put_line(v_id|| ' ' || v_name || ' ' || v_sal);
END;
/

%type : 컬럼의 데이터 타입과 사이즈를 가지고 온다.

DECLARE
    v_id    hr.employees.employee_id%type; --%type 해당 컬럼의 타입과 사이즈를 그대로 이어받음
    v_lname hr.employees.last_name%type;
    v_fname v_lname%type;
    v_sal hr.employees.salary%type;
BEGIN
    SELECt employee_id, last_name,first_name, salary
    INTO v_id, v_lname,v_fname, v_sal 
    FROM hr.employees 
    WHERE department_id = 10;
    dbms_output.put_line(v_id|| ' ' || v_lname || ' '|| v_fname ||' ' || v_sal);
END;
/

• bind 변수는 실행계획을 균등하게 생각하고 잡게 되는데 employee_id 같은 경우에는 pk 값이기 때문에 전체중에 하나씩 찾게 됨으로 bind변수를 사용해 실행계획을 sharing 해주는게 좋다.(소프트 파싱) 하지만 department_id 같은 경우 부서별로 수 불균등하기 때문에 bind변수로 실행계획을 공유하면 더 좋지 않다.즉 다른 실행계획을 가지게 한다(하드파싱)

---

• bind 변수를 활용한 프로그램 select문

  bind 변수를 활용하여 프로그램이 종료되어도 프로그램 밖에서 바인드 변수에 저장된 값을 사용 할 수 있다.

variable b_avg_sal number;

BEGIN
    SELECT avg(salary)
    INTO :b_avg_sal
    FROM hr.employees;
    dbms_output.put_line('전체 사원의 평균 급여 ' || :b_avg_sal);
END;
/

print :b_avg_sal;

SELECT * FROM hr.employees WHERE salary  > :b_avg_sal;

[문제10]

사원번호를 입력값으로 받아서 입사일, 금여 정보를 출력하는 프로그램을 작성해주세요.
ex) 입사일 : 2003년 10월 17일
급여 : 24,000

<풀이>

variable b_emp number    
execute :b_emp := 100

DECLARE
    v_hire_day varchar2(30);
    v_sal varchar2(30);
BEGIN
    SELECT to_char(hire_date,'yyyy"년" mm"월" dd"일"'),to_char(salary,'l999G999')
    INTO v_hire_day, v_sal
    FROM hr.employees
    WHERE employee_id = :b_emp;
    
    dbms_output.put_line('입사일 : '||v_hire_day );
    dbms_output.put_line('급여 : '||v_sal );
END;
/

[문제11]

년도를 입력값으로 받아서 그 년도 해당하는 사원들의 급여의 총액을 출력해주세요.

variable b_day varchar2(10)
execute :b_day := '2002'

DECLARE
    v_sum_sal number;
    v_start varchar2(20) := :b_day||'-01-01';
    v_end varchar2(20) := :b_day||'-12-31';
BEGIN
    SELECT sum(salary)
    INTO  v_sum_sal
    FROM hr.employees
    WHERE hire_date between to_date(v_start,'yyyy-mm-dd') and to_date(v_end,'yyyy-mm-dd');
    dbms_output.put_line(:b_day ||'년도 입사자의 총 급여액은 '||to_char(v_sum_sal,'999G999')||'원 입니다.');
END;
/

암시적 커서에 대한 SQL 커서 속성

SQL%ROWCOUNT

가장 최근의 DML문에 의해 영향을 받은 행의 수를 리턴해주는 값

BEGIN
INSERT INTO hr.test(id,name,day)
SELECT employee_id, last_name, hire_date
FROM hr.employees;
dbms_output.put_line(sql%rowcount || ' row created');
END;
/