15 PL/SQL WHILE LOOP, FOR, Implicit Cursor

ryunyryuny 2023. 10. 26. 17:05

2. WHILE LOOP 문
- 조건이 TRUE인 동안 반복 작업을 수행하는 문

DECLARE
    i number := 1;
BEGIN
    WHILE i <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
        i := i + 1;
    END LOOP;
END;
/

[문제] 1부터 10까지 출력해주세요. 단, 4,8 제외

DECLARE
    i number := 0;
BEGIN
    WHILE i < 10 LOOP
        i := i + 1;
        CONTINUE WHEN i IN (4,8);
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

[문제] 1부터 100까지 합을 출력해주세요.

DECLARE
    i number := 1;
    total number := 0;

BEGIN
    WHILE i <= 100 LOOP
        total := total + i;
        i := i + 1;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('1부터 100까지 합 : '||total);
END;
/

3. FOR 문
- 반복횟수를 알고 있을 때 사용하는 루프문

BEGIN
    FOR i IN 1..10 LOOP     -- i : 카운트용으로 선언없이 사용가능
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN i = 4;
        DBMS_OUTPUT.PUT_LINE(i);
        EXIT WHEN i = 5;
    END LOOP;
END;
/

DECLARE
    v_start number := 1;
    v_end number := 10;
BEGIN
    FOR i IN v_start..v_end LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

[문제] 1부터 10까지 출력해주세요. 단, 4,8 제외

BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN i IN (4,8);
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

[문제] 구구단 2단 출력.

1) 기본 LOOP
DECLARE
    num number := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('2 * '||num||' = '||2 * num);
        num := num + 1;
        EXIT WHEN num = 10;
    END LOOP;
END;
/

2) WHILE LOOP
DECLARE
    num number := 1;
BEGIN
    WHILE num < 10 LOOP
        DBMS_OUTPUT.PUT_LINE('2 * '||num||' = '||2 * num);
        num := num + 1;
    END LOOP;
END;
/

3) FOR LOOP
BEGIN
    FOR i IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE('2 * '||i||' = '||2 * i);
    END LOOP;
END;
/

# 중첩 반복문

[문제] 구구단 출력.

1) 기본 LOOP
DECLARE
    dan number := 2;
    j number;
BEGIN
    LOOP            -- 단 반복
        J := 1;

        LOOP            -- 지수 반복
            DBMS_OUTPUT.PUT_LINE(dan||' * '||j||' = '||dan * j);
            EXIT WHEN j= 9;
            j := j + 1;
        END LOOP;

        EXIT WHEN dan= 9;
        dan := dan + 1;
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

2) WHILE LOOP
DECLARE
    dan number := 2;
    j number;
BEGIN
    WHILE dan <= 9 LOOP            -- 단 반복
        J := 1;

        WHILE j <= 9 LOOP            -- 지수 반복
            DBMS_OUTPUT.PUT_LINE(dan||' * '||j||' = '||dan * j);
            j := j + 1;
        END LOOP;

        dan := dan + 1;
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

3) FOR LOOP
DECLARE
    dan number;
    j number;
BEGIN
    FOR dan IN 2..9 LOOP            -- 단 반복
        FOR j IN 1..9 LOOP            -- 지수 반복
            DBMS_OUTPUT.PUT_LINE(dan||' * '||j||' = '||dan * j);
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

[문제] 급여를 입력값으로 받아서 1000당 * 하나를 출력해주세요.
<화면출력>
급여는 5500 당신의 별은 *****

-- 바인드변수에서는 길이지정 안함
VAR sal number
EXECUTE :sal := 5500

DECLARE
    num number := trunc(:sal / 1000);
    star varchar2(10);
BEGIN
    FOR i IN 1..num LOOP
        star := star||'*';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('급여는 '||:sal||' 당신의 별은 '||star);
END;
/

■ PL/SQL의 SQL문
- SELECT문을 이용해서 데이터베이스에 있는 데이터를 추출할 수 있다.
- DML(INSERT,UPDATE,DELETE,MERGE)문을 이용해서 데이터베이스에 있는 데이터를 조작할 수 있다.
- COMMIT, ROLLBACK, SAVEPOINT문을 이용해서 트랜젝션을 처리할 수 있다.

# cursor : SQL문 실행 메모리 영역(parse - bind - execute - fetch 단계 수행)
- 암시적커서 : 오라클이 SQL문을 처리하기 위해서 내부적으로 생성하고 관리한다.
- 명시적커서 : 프로그래머가 명시적으로 관리한다.(여러개의 row를 fetch해야 할 때 사용)

# 암시적커서
- SELECT ... INTO (fetch) 절 : 반드시 1개 row만 fetch 해야한다.
데이터 0개 : NO_DATA_FOUND
2개이상 : TOO_MANY_ROWS
- DML, COMMIT, ROLLBACK, SAVEPOINT

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal          -- fetch 절
    FROM hr.employees
    WHERE department_id = 10;   -- 데이터 row 1개.

    DBMS_OUTPUT.PUT_LINE(v_name);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal          -- fetch 절
    FROM hr.employees
    WHERE department_id = 20;   -- 오류 : 1개 이상의 데이터row를 가지고 있다.

    DBMS_OUTPUT.PUT_LINE(v_name);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/

>> ORA-01422: exact fetch returns more than requested number of rows

var b_id number
execute :b_id := 100

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal
    FROM hr.employees
    WHERE employee_id = :b_id;

    DBMS_OUTPUT.PUT_LINE(v_name);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/

var b_avg_sal number

BEGIN
    SELECT avg(salary)
    INTO :b_avg_sal
    FROM hr.employees;

    DBMS_OUTPUT.PUT_LINE('전체 사원의 평균 값은 '||:b_avg_sal);
END;
/

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

# 유저.테이블.컬럼%type : 컬럼의 type, size를 가져다 사용한다.
# 후에 컬럼의 type과 size가 변경되더라도 프로그램을 수정할 필요가 없다.

DECLARE
    v_name hr.employees.last_name%type;
    v_sal hr.employees.salary%type;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal
    FROM hr.employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE(v_name);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/

DECLARE
    v_hire_date hr.employees.hire_date%type;
    v_sal hr.employees.salary%type;
BEGIN
    SELECT hire_date, salary
    INTO v_hire_date, v_sal
    FROM hr.employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('입사일 : '||to_char(v_hire_date,'yyyy"년" mon dd"일"'));
    DBMS_OUTPUT.PUT_LINE('급여는 : '||ltrim(to_char(v_sal,'l999,999.00')));
END;
/

DROP TABLE hr.test;
CREATE TABLE hr.test(id number, name varchar2(30), day date);
desc hr.test

BEGIN
INSERT INTO hr.test(id,name,day) VALUES(1,'홍길동',sysdate); -- 트랜젝션 실행
END;
/

SELECT * FROM hr.test;
ROLLBACK; -- 트랜젝션 종료. 위치 선정 중요!

DECLARE
    v_id hr.test.id%type := 1;
    v_name hr.test.name%type := '홍길동';
    v_day hr.test.day%type := sysdate;

BEGIN
    INSERT INTO hr.test(id,name,day) VALUES(v_id,v_name,v_day);
    COMMIT;
END;
/

SELECT * FROM hr.test;

var b_id number
var b_name varchar2(30)
var b_day varchar2(30)

execute :b_id := 3
execute :b_name := 'james'
execute :b_day := '2002-01-01'

BEGIN
INSERT INTO hr.test(id,name,day)
VALUES(:b_id,upper(:b_name),to_date(:b_day,'yyyy-mm-dd'));
END;
/

COMMIT;

SELECT * FROM hr.test;

# sql%rowcount : 암시적커서 속성, 영향을 입은 ROW 건수를 리턴. DML 작업 후 많이 사용.

BEGIN
    DELETE FROM hr.test WHERE id = 1;
    DBMS_OUTPUT.PUT_LINE('삭제된 행의 수는 '|| sql%rowcount);

    UPDATE hr.test SET day = sysdate + 100;
    DBMS_OUTPUT.PUT_LINE('수정된 행의 수는 '|| sql%rowcount);
END;
/

SELECT * FROM hr.test;
ROLLBACK;

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp
AS
SELECT * FROM hr.employees;

BEGIN
    DELETE FROM hr.emp
    WHERE department_id = 20;
    DBMS_OUTPUT.PUT_LINE('삭제된 행의 수는 '||sql%rowcount);

    UPDATE hr.emp
    SET salary = salary * 1.1
    WHERE department_id = 30;
    DBMS_OUTPUT.PUT_LINE('수정된 행의 수는 '||sql%rowcount);
END;
/
SELECT * FROM hr.emp;
ROLLBACK;

# sql%found : 영향을 입은 ROW가 있으면 TRUE, 없으면 FALSE를 리턴하는 속성.

DECLARE
    v_id hr.emp.employee_id%type := 0;

BEGIN
    UPDATE hr.emp
    SET salary = salary * 1.1
    WHERE employee_id = v_id;

    IF sql%found THEN
        DBMS_OUTPUT.PUT_LINE(v_id||'사원으로 수정된 데이터가 있습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id||'사원으로 수정된 데이터가 없습니다.');
    END IF;
END;
/

SELECT * FROM hr.emp;
ROLLBACK;

# sql%notfound : 영향을 입은 ROW가 없으면 TRUE, 있으면 FALSE 리턴하는 속성.

DECLARE
    v_id hr.emp.employee_id%type := 0;

BEGIN
    UPDATE hr.emp
    SET salary = salary * 1.1
    WHERE employee_id = v_id;

    IF sql%notfound THEN
        DBMS_OUTPUT.PUT_LINE(v_id||'사원으로 수정된 데이터가 없습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id||'사원으로 수정된 데이터가 있습니다.');
    END IF;
END;
/

저작자표시

'Oracle SQL' 카테고리의 다른 글

17 PL/SQL Nested Table, VARRAY, Explicit Cursor (0)	2023.10.30
16 PL/SQL RECORD, Array (0)	2023.10.27
14 PL/SQL Variable, CASE, LOOP (0)	2023.10.25
13-1 SAVEPOINT (0)	2023.10.24
12 Sequence, Synonym, Extract, 날짜함수, 분석함수, Rank (0)	2023.10.23

'Oracle SQL'의 다른글

현재글15 PL/SQL WHILE LOOP, FOR, Implicit Cursor

Record.

15 PL/SQL WHILE LOOP, FOR, Implicit Cursor

'Oracle SQL' 카테고리의 다른 글

티스토리툴바