Bohemian life

       declare

 type emp_rec_type is record

(no emp.empno%type

  , name emp.ename%type

, salary emp.sal%type);

 v_emp_rec emp_rec_type;

begin

 select empno, ename, sal into v_emp_rec

 from emp

 where empno = 7788;

 p('EMPNO : ' || v_emp_rec.no);

 p('ENAME : ' || v_emp_rec.name);

 p('SALARY: ' || v_emp_rec.salary);

end;

/

Composite Data Types : Hold multiple values

  * Using PL/SQL Collections and Records ==> http://goo.gl/VMgrA

  * PL/SQL record

- can have DIFFERENT data types

- related but dissimilar data as a logical unit

- access and modify data EASILY

- fields

- PL/SQL record structure: # 6-8

  * PL/SQL collection

- SAME data type (can be of the composite type)

1) Associative array (INDEX BY table)

2) Nested table

3) VARRAY

  * 선언부에서 TYPE 이용해서 정의한 후 선언부에서 선언

  * Collection Types ==> http://goo.gl/oPpoE

   확인문제) 다음과 같은 t1 테이블이 주어져 있다. t1 테이블의 현재 행의 수가

   m 이라고 할 때 숫자 n을 입력 받아 m+1 부터 m+n 까지의 값을 차례로

   t1 테이블에 추가하는 프로시저를 작성하세요.

<Table t1>

NULL값 2건 입력        NO    n := 3        NO

==> ---------- ==> ----------

   (null)

   (null)

        1

        2

3

NULL값 2건 입력    n := 2        NO

    ==>           ==> ----------

   (null)

   (null)

      1

      2

      3

   (null)

   (null)

      4

      5

drop table t1 purge;

create table t1 (no number);

create or replace procedure append_num (n number, opt char default null)

is

 v_row_num number; -- t1 테이블의 현재 행의 수

begin

 /*

  * 테이블 리셋: 테이블 삭제 및 생성

  * 두번째 파라미터가 'r' 또는 'R' 일 경우 테이블 리셋

  */

 if UPPER(opt) = 'R' then

   begin

     execute immediate 'drop table t1 purge';

     p('t1 테이블을 삭제하였사옵니다, 주인님!');

   exception

     when others then

       p('t1 테이블이 없어 삭제할 수 없사옵니다, 주인님!');

   end;

   execute immediate 'create table t1 (no number)';

   p('t1 테이블을 생성하였사옵니다, 주인님!');

 end if;

-- t1 테이블의 현재 행 수 구하기

 select nvl(max(no), 0) into v_row_num from t1;

--  select count(no) into v_row_num from t1;

-- for loop를 돌면서 테이블에 값 삽입

 for i in v_row_num+1..v_row_num+n loop

   insert into t1 values (i);

 end loop;

 p('입력 작업이 성공적으로 끝났사옵니다, 주인님!');

end;

/

exec append_num(0, 'r')

select * from t1;

insert into t1 values (null);

insert into t1 values (null);

select * from t1;

exec append_num(3)

select * from t1;

insert into t1 values (null);

insert into t1 values (null);

select * from t1;

exec append_num(2)

select * from t1;

-----------------------------

  * null 값 입력 없는 단순한 예제의 해법

drop table t1 purge;

create table t1 (no number);

create or replace procedure append_num (n number)

is

 v_cnt number;

begin

 select count(*) into v_cnt

 from t1;

 for i in v_cnt+1..v_cnt+n loop 

   insert into t1 values (i);

 end loop;

end;

/

exec append_num(2)

select * from t1;

       NO

----------

1

2

exec append_num(3)

select * from t1;

       NO

----------

1

2

3

4

5

참고 : http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref976

  * NULL Statement:

You can use the NULL statement to indicate that you are aware of a possibility, 

but no action is necessary.

EXCEPTION

 WHEN ZERO_DIVIDE THEN

   ROLLBACK;

 WHEN OTHERS THEN

   NULL;

END;

  * 문제) 아래 블럭은 에러가 발생한다. 해결 방법은?

DECLARE

 done  BOOLEAN;

BEGIN

 FOR i IN 1..50 LOOP

   IF done THEN

     GOTO end_loop;

   END IF;

 <<end_loop>> -- not allowed unless an executable statement follows

 END LOOP; -- raises an error without the previous NULL

END;

/

↓ ↓ ↓ 

DECLARE

 done  BOOLEAN;

BEGIN

 FOR i IN 1..50 LOOP

   IF done THEN

     GOTO end_loop;

   END IF;

 <<end_loop>> -- not allowed unless an executable statement follows

 NULL; -- add NULL statement to make label legal and fix the error

 END LOOP; -- raises an error without the previous NULL

END;

/

  * 구구단

begin

 for i in 2..9 loop

   for j in 1..9 loop

     p(i || ' * ' || j || ' = ' || i*j);

   end loop;

 p(' ');

 p('------------');

 p(' ');

 end loop;

end;

/

  * Label

declare

 v_sum number := 0;

 i     number := 1;

 j     number;

begin

--  i := 1;

 <<Outside>>

 loop

   i := i + 1;

   j := 0;

   exit when i > 9;

   <<Inside>>

   loop

     j := j + 1;

     v_sum := v_sum + i * j;

     p(to_char(i)||' * '||to_char(j)

       ||' = '||to_char(i*j)||', sum = '||to_char(v_sum));

     exit outside

when v_sum > 1000; --> label is case-insensitive

     exit when j >= 9;

   end loop inside; --> label is for clarity

 end loop; --outside; --> label is optional

end;

/

       begin

 for i in 1..10 loop

   insert into players 

     values (i, 'Player '||to_char(i)||' entered.');

     end loop;

 p(to_char(i)||' data inserted'); --> 에러가 발생하면서 Tx 롤백 되었습니다.

end;

/

↓ ↓ ↓ 

declare

 c number := 0;

begin

 for i in 1..10 loop

   insert into players 

     values (i, 'Player '||to_char(i)||' entered.');

   c := c + 1;

     end loop;

p(to_char(c)||' data inserted');

end;

/

-----------------------------------------

select * from players;

       declare

 i number := 20;

 c number := 0;

begin

 while (i > 10) loop

   insert into players 

     values (i, 'Player '||to_char(i)||' entered.');

   i := i - 1;

   c := c + 1;

     end loop;

 p(to_char(c)||' data inserted');

end;

/

-----------------------------------------

select * from players;

begin

 begin

   execute immediate 'drop table players';

 exception

   when others then

     null;

 end;

 execute immediate 'create table players (no number

                                        , name varchar2(30))';

end;

/

-----------------------------------------

desc players

  * EXIT Statement: 루프를 빠져나오고 루프 문 바로 다음 문장으로 제어권을 넘김

    1) EXIT WHEN condition

declare

 i number := 1; -- counter 변수

begin

 loop

   insert into players

     values (i, 'Player '||to_char(i)||' entered.');

   i := i + 1; -- counter 변수 갱신

   exit when i > 10; -- 탈출 조건

 end loop;

 p(to_char(i-1)||' data inserted');

end;

/

-----------------------------------------

select * from players;

    2) IF condition THEN exit; END IF;

declare

 i number := 1;

begin

 loop

   insert into players 

     values (i, 'Player '||to_char(i)||' entered.');

   i := i + 1;

   if i > 10 then

     p('loop is terminated!');

     exit;

   end if;

 end loop;

p(to_char(i-1)||' data inserted');

end;

/

-----------------------------------------

select * from players;

  * Basic - 적어도 한 번은 루프 실행

 - 반드시 탈출조건 필요 (EXIT [WHEN condition])

 - 카운터 변수 선언 및 갱신 필요

  * While - 루프 실행 전 반드시 (루프 진입) 조건 평가

=> 루프가 전혀 실행되지 않을 수 있음

 - 카운터 변수 선언 및 갱신 필요

  * For   - 지정 횟수 만큼 루프 실행

=> lower_bound..upper_bound - literals, variables, expressions

   - must be round to integers (11/3, 8/5, etc.)

   - inclusive

 - 카운터 변수 선언 불필요, 내부적으로 선언됨

   cf.) Cursor For Loop

  * SYNTAX

[WHILE condition |FOR variable IN lower_bound..upper_bound]

LOOP 

   statements;

   [EXIT [WHEN ... ]];

END LOOP;

- NULL에 대한 연산은 is null, is not null을 제외하고는 결과가 모두 NULL임을 상기

declare

 x number := 5;

 y number;

begin

 if x != y then

   p('x is NOT null value!.');

 else

   p('x is null value!.');

 end if;

end; --> 엉터리

/

↓ ↓ ↓ 

declare

 x number := 5;

 y number;

begin

 if x is not null then

   p('x is NOT null value!.');

 else

   p('x is null value!.');

 end if;

end; --> 제대로

/

  - 액션을 취함

  - END CASE 로 종료

  - THEN 이후에 PL/SQL 문

  - SQL의 CASE expression과 동일

    단, 변수에 저장된 값을 사용하거나 CASE expression의 결과를 변수에 저장할 수 있다는 점이 다릅니다.

  - 값을 반환

  - END 로 종료

  - THEN 이후에 리터럴, 변수, 상수, 함수, 수식

  - If 조건이 TRUE일 경우에만 THEN 절 이후의 문장들을 실행

  - FALSE, NULL 이면 다음 elsif 절의 조건을 평가하거나 else 이후의 문장들을 실행

declare

 age number;

begin

 if age < 30 then

   p('Hi ~~'); ---> 1

   p('You''re young.'); ---> 2

 elsif age < 60 then

   p('You''re still young.'); ---> 3

 elsif (age < 100) is null then

   p('Your age is null');

 else

   p('Your age is over 60.');

 end if;

end;

/

* age 변수의 값이 30 미만이라면

- 1, 2의 문장만을 수행하고 if 문을 빠져나옴. (3번 문장 실행 안함)

* age 변수가 선언시 값이 주어지지 않았으므로 NULL 값으로 초기화 됨

- 앞선 두 개의 조건절 (age < 30, age < 60)은 

 (null < 30, null < 60)이 되므로 조건절을 평가한 결과는 NULL -> 통과

- (age < 100) is null 은 (null < 100) is null => null is null 이므로

 조건절 평가 결과는 TRUE

  * Selection - If statement

     - Case expression 1) Simple

                2) Searched

     - Case statement

  * Iteration - Basic Loop

     - While Loop

     - For Loop

  * Sequence  - Goto

     - Null

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/controlstructures.htm#CIHBCCFH

  - SQL%FOUND

  - SQL%NOTFOUND

  - SQL%ROWCOUNT

  - SQL%ISOPEN

VARIABLE rows_deleted VARCHAR2(30)

select count(deptno) from e1

where deptno = 10;

   ------------------------------

DECLARE

 v_deptno e1.deptno%TYPE := 10;

BEGIN

 DELETE FROM  e1

 WHERE deptno = v_deptno;

 :rows_deleted := (SQL%ROWCOUNT ||' rows deleted.');

END;

/

   ------------------------------

PRINT rows_deleted

  문제) 다음 e1 테이블에서 부서번호가 20인 사원들의 급여를 10% 인상하고

        인상된 사원 수를 바인드 변수에 저장하는 anonymous block을 작성하세요.

그리고 바인드 변수의 결과를 출력하세요.

drop table e1 purge;

create table e1 as select * from emp;

var rows_updated varchar2(30)

begin

 update e1

 set sal = sal * 1.1

 where deptno = 20;

 :rows_updated := sql%rowcount || ' rows updated.';

end;

/

print rows_updated