12/3 PL/ SQL

/*

PL/SQL 

PL/SQL의 기본 구조

*/

declare

  counter integer;

begin

  counter := counter + 1;

  if counter is null then

    dbms_output.put_line('Result : counter is null');

  end if;

end;

/**/

declare

  counter integer;

  a integer;  

begin

  for a in 1..9 loop    

    counter := (2*a);    

    dbms_output.put_line('2*'||a||'='||counter);

  end loop;

end;

declare

  counter integer;

  a integer;  

  b integer;

begin

  for a,b in 1..9 loop    

    counter := (b*a);    

    dbms_output.put_line(b||'*'||a||'='||counter);

  end loop;

end;

/*예외 처리부

Exception when 예외1 then 예외처리1

  when 예외2 then 예외처리2

  ...

  when others then 예외처리 */

declare

  counter integer;

begin

  counter := 10;

  counter := counter/0;

  dbms_output.put_line(counter);

exception when others then /* others 모든 오류를 지정 */

  dbms_output.put_line('논리 오류가 발생했습니다');

end;

/* ZERO_DIVIDE 0으로 나눴을때 발생하는 오류를 지정

   대체 코드를 명시할수 있다. */

declare

  counter integer;

begin

  counter := 10;

  counter := counter/0;

  dbms_output.put_line(counter);

exception when zero_divide then /* zero_divide로 예외처리 */

  counter := counter/1; /* 대체코드 입력 */

  dbms_output.put_line(counter);

end;

/* 변수와 상수

변수 선언

emp_num1 number(9);

grade char(2);

emp_num2 interger :=1; 데이터 입력과 동시에 선언

상수 선언

nYear constant integer :=30; (O) 

nYear constant integer; (X) '값이 null이면 상수가 아니다.'

%TYPE 1개 타입 가져옴 || %ROWTYPE 2개 이상 타입 가져움 || 'PLSQL은 주석이 '-- 한줄'이다.

variale array의 약자로 고정 길이(fixed number)를 가진 배열

*/

declare

  type varray_test is varray(3) of integer; -- 자료형 선언 

  -- 자료형은 변수에 지정

  varray1 varray_test;

begin

  varray1 := varray_test(3,6,9);

  dbms_output.put_line(varray1(2));

end;

/*

중첩테이블

선언시 전체 크기를 명시할 필요가 없음

요소 참조시 순서를 지킬 필요가 없음

*/

declare

  type nested_test is table of varchar2(10); -- 자료형 선언 

  -- 변수 선언

  nested1 nested_test;

begin

  nested1 := nested_test('에이징','삐','시','딩동');

  dbms_output.put_line(nested1(2));

  dbms_output.put_line(nested1(4));

end;

/* 연관배열(Associative array) : 키와 값의 쌍으로 구성 

직접 키값을 지정해야 한다.*/

declare

  type assoc_array_num_type is table of number index by pls_integer; -- 자료형 선언 (number : 값의 자료형,pls_integer : 키의 자료형)  

  assoc1 assoc_array_num_type; -- 변수 선언

begin

  assoc1(3) := 33; --assoc_array_num_type의 키는 3,값은 33을 넣는다.

  dbms_output.put_line(assoc1(3));  

end;

declare

  type assoc_array_str_type is table of varchar2(32) index by pls_integer; -- 자료형 선언 (number : 값의 자료형,pls_integer : 키의 자료형)  

  assoc2 assoc_array_str_type; -- 변수 선언 , 키는 pls_integer형이며, 값은 varchar(32)형이다.

begin

  assoc2(2) := 'TT'; --

  dbms_output.put_line(assoc2(2));  

end;

declare

  type assoc_array_str_type2 is table of varchar(32) index by varchar2(64); -- 자료형 선언 키=varchar2(64)형 값=varchar2(32)형인 요소들로 구성

  assoc3 assoc_array_str_type2; -- 변수 선언

begin

  assoc3('K') := 'KOREA'; -- 키와 값을 문자형으로 넣음

  dbms_output.put_line(assoc3('K'));  

end;

/* 콜렉션을 데이터베이스에 저장 */

create type alphabet_type as varray(26) of varchar2(2);

declare 

  test_alph alphabet_type;

begin

  test_alph := alphabet_type('A','B','C','D');

  dbms_output.put_line(test_alph(1));

end;

/* 레코드 

서로 다른 유형의 데이터 타입으로 구성할 수 있음 */

declare 

  type record1 is record(deptno number not null := 50,

                         dname varchar2(14),

                         loc varchar2(13)); -- 변수 선언 (자료형 지정)

  rec1 record1;

begin

  --record1 레코드의 변수 rec1의 dname필드에 값 할당

  rec1.dname := '레코드부서1';

  --record1 레코드의 변수 rec1의 loc필드에 값 할당

  rec1.loc := 'Seoul';

  insert into dept values rec1;

  commit;

exception when others then

          rollback;

end;

/*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('Foor');

  end if;

end;

/* case문 */

declare

  grade char(1);

begin

  grade := 'D'; -- :=한번만 입력

  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('Fair');

             when 'D' then

    dbms_output.put_line('Foor');

  end case;

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 + 1;

  end loop;

end;

/*for ... loop*/

declare

  test_number integer;

  result_num integer;

begin

  test_number :=1;

  result_num := 0;

  

  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

  test_number :=1;

  result_num := 0;

  

  for test_number in reverse 1..10 loop -- reverse 추가 기능

    result_num :=2 * test_number;

    dbms_output.put_line(result_num);    

    end loop;

end;

/* goto문 */

declare

  test_number integer;

  result_num integer;

begin

  test_number :=1;

  result_num := 0;

  

  dbms_output.put_line('<<first>>'); -- <<first>> 위치 식별을 위한 레이블

  <<first>>

  for test_number in 1..10 loop -- reverse 추가 기능

    result_num :=2 * test_number;

    dbms_output.put_line(result_num);    

    end loop;

    

  dbms_output.put_line('<<second>>');

  

  result_num := 0;

  <<second>>

  for test_number in reverse 1..10 loop

    result_num := 2*test_number;

    dbms_output.put_line(result_num);

  end loop;

end;

/* cursor 커서명 is select 1명시적커서 2묵시적커서

select 문장을 실행하면 조건에 따른 결과를 추출. 그 추출된 결과에 접근하기 위해 커서를 사용 

1. 명시적커서 */

declare 

  cursor emp_csr is

    select empno

    from emp

    where deptno = 10;

  emp_no emp.empno%type;

begin

  -- 커서 열기 : 커서로 정의된 쿼리를 실행하는 역할

  open emp_csr;

  

  loop

    -- 커서의 결과에 접근

    fetch emp_csr into emp_no;

    -- %notfound:커서에서만 사용가능한 속성으로서

    -- 더 이상 패치(할당)할 로우가 없음을 의미하며

    -- 따라서 쿼리의 마지막 결과까지 패치한 후 루프를 빠져나오도록 지정

    exit when emp_csr%notfound;

    

    dbms_output.put_line(emp_no);

  end loop;

  close emp_csr;

end;

/*2.묵시적커서

오라클 내부에서 각각의 쿼리 결과에 접근하여 사용하기 위한 내부적 커서.

'SQL'이라는 이름으로 속성 접근 */

declare

  count1 number;

  count2 number;

begin

  select count(*)

  into count1

  from emp

  where deptno = 10;

  --%rowcount: 카운터(counter) 역할. 커서가 막 오픈되었을 때는

  -- 0, 패치될 때마다 1씩 증가가 됨.

  count2 := SQL%rowcount;

  

  dbms_output.put_line('select count is ' || count1);

  dbms_output.put_line('row count is ' || count2);

end;

/* 함수 */

create or replace function emp_salaries(emp_no number)

  -- 반환 하는 데이터의 타입 선언

  return number is

  nSalaries number(9);

begin

  nSalaries := 0;

  select sal

    into nSalaries

    from emp

    where empno = emp_no;

  return nSalaries;

end;

select emp_salaries(8000) from dual;

  

create or replace function get_dep_name(dept_no number)

  return varchar2 is

  sDepName varchar2(30);

begin

  

  

  select dname

  into sDeName

  from dept

  where deptno = dept_no;

  

  return sDepName;

end;

select get_dep_name(10) from dual;