begin
        execute immediate 'create table xce8(x date)';
exception
        when others then
                if sqlcode=-955 then
                        dbms_output.put_line('----------');
                        dbms_output.put_line(sqlerrm);
                        dbms_output.put_line('----------');
                end if;
end;
/

--user-defined exceptions : 논리적 error에 대한 처리
--ex) sal < 0 논리적 오류

--에러 지정 방법
declare
        min exception;
 begin
    for c in (select * from emp where deptno=30) loop
        if c.sal < 0 then
                RAISE min;
        else
            dbms_output.put_line(c.sal);
        end if;
    end loop;
exception
        when min then
                dbms_output.put_line('min error');
end;

--RAISE_APPLICATION_ERROR Procedure : error번호까지 만들어서 error발생 -> 프로그램단에서 처리하기가 용이.
-- error번호 기존 error번호와 겹칠 수 있음 그래서 특정 대역을 사용 –20000 ~ –20999
declare
        min exception;
begin
    for c in (select * from emp where deptno=30) loop
        if c.sal < 0 then
                raise min;
        else
            dbms_output.put_line(c.sal);
        end if;
    end loop;
exception
        when min then
                dbms_output.put_line('min');
                RAISE_APPLICATION_ERROR(-20111,'min error 2');
end;
/

-- 둘다 파라미터가 있음
/*
1.procedures : 리턴값이 없는 function, 재사용 가능
 subprograme : function, trigger

*/
-- functions : 리턴값이 있음

--객체생성
create procedure ce8
as
begin
        dbms_output.put_line('------------');
end;
/
--실행방법 exec ce8

--객체 확인
select object_name, object_type from user_objects where object_name='CE8'

--파마리터값 받아서 평균 출력(user defined function)
create or replace function avgsal(deptnox number)
     return number
  as
     sumx number(10);
 begin
     select avg(sal) into sumx from emp where deptno=deptnox;
  return sumx;
end;
 /

--실행 select ename from emp where sal < avgsal(10);

--EM(Enterprise Manager=(GUI관리툴))
--http://192.168.186.3:1158/em/
-- Performance(튜닝) Administration(셋팅) Maintenance(유지보수)

--구구단
create or replace procedure gugu(dan number)
as
begin
        for i in 2..9 loop
                dbms_output.put_line(dan || 'x' || i || ' = ' || dan*i);
        end loop;
end;

--실행방법 ( exec gugu(8) )

create or replace procedure tp2(su number, su2 out number) as
begin
        su2 := su+1;
end;
/

declare
        x number := 9;
begin
        tp2(1,x);
        dbms_output.put_line(x);
end;
/

--x 결과값은 2

--out의 용도 : 프로시저 내부에서 바꾼 값을 invoke해준 블록에서 적용이 되게 함
create or replace procedure tp3(su in out number) as
begin
        su := su+1;
end;

--in방식에서만 숫자 가능, out방식은 변수만 가능

--procedure 생성(파라미터를 in으로 받아서 out으로 보내는 것으로 이해)
CREATE OR REPLACE PROCEDURE getdl(pdeptno number, pdname out dept.dname%type, ploc out dept.loc%type) as
begin
        select dname, loc into pdname, ploc from dept where deptno = pdeptno;
end;

--생성된 프로시저 실행하기
declare
        dname dept.dname%type;
        loc dept.loc%type;
begin
        getdl(10, dname,loc);
        dbms_output.put_line(dname || ',' || loc);
end;

--기본 문
DECLARE
        event VARCHAR2(15); -- 변수 선언
BEGIN
 --변수 정의
        event := q'!Father's day!';
 --출력
        DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :'||event);
        event := q'[Mother's day]';
        DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :'||event);
END;

--대입
:=
x NUMBER DEFAULT 2;
--비교
=

--%type : 테이블의 타입정의를 가지고 옴
declare
        v_id txx.id%type := &id;
        v_data txx.data%type := '&data';
begin
        insert into txx values(v_id,v_data);
end;

var x number
EXEC :x := 30
SELECT * FROM dept WHERE deptno=:x;

print x

--암시적 커서 예
begin
        for c in(select * from emp) loop
         dbms_output.put_line(c.ename);
        end loop;
end;

--명시적 커서 예
declare
  cursor c is select ename from emp;
  e emp.ename%type;
begin
  open c;
  loop
        fetch c into e;
        exit when c%notfound;
         dbms_output.put_line(e);
  end loop;
  dbms_output.put_line(c%rowcount);
end;

--IF문
declare
        v_dept varchar(10);
begin
        for c in(select * from emp) loop
          if c.deptno = 10 then
                v_dept := 'acc';
          elsif c.deptno = 20 then
                v_dept := 'res';
          elsif c.deptno = 30 then
                v_dept := 'sal';
          else
                v_dept :='n/a';
          end if;
                dbms_output.put_line(c.ename || ',' || v_dept);
        end loop;
end;

--case 문
declare
        v_dept varchar(10);
begin
        for c in(select * from emp) loop
        dbms_output.put_line(c.ename || ',' ||
        case c.deptno
                when 10 then 'acc'
                when 20 then 'res'
                when 30 then 'sal'
          end
        );
        end loop;
end;

--for문
begin
   for dan in 2..9 loop
        exit when dan >= 2;
        for i in 1..9 loop
                dbms_output.put_line(dan ||'X'||i||'='||dan*i);
        end loop;
   end loop;
end;

-- scalar types( 값 <> vector)
-- records 변수인데 값이 여러개 C언어의 struct
-- collections record를 묶어 놓은 c언어에서의 struct array

--rocord
DECLARE
 --일부 컬럼도 가능
 type dept_type is record(DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(14));
 dept_row dept_type;
 --dept_row dept%rowtype; -- 전체 컬럼만 가능
begin
 select * into dept_row from dept where deptno=10;
 insert into dept values(dept_row.deptno+1, dept_row.dname, dept_row.loc);
end;

--레코드를 이용한 출력 방법
-- 변수 r은 암시적으로  emp%rowtype이 됨.
begin
        for r in (select * from emp) loop
                if r.deptno=10 and r.sal+nvl(r.comm,0) > 2000 then
                        dbms_output.put_line(r.ename || ',' || r.sal);
                end if;
        end loop;
end;

declare
 type t is record(deptno number(2),dname varchar2(14),loc varchar2(13));
 type tt is table of t index by pls_integer;
 r t;
 rs tt;
 i number(2) :=0;
begin
 select * into r from dept where deptno=10;
 dbms_output.put_line(r.dname);
 for r in(select * from dept) loop
  rs(i) :=r;
  -- dbms_output.put_line(r.dname||i);
  i:=i+1;
 end loop;
 for j in 0..i-1 loop
  dbms_output.put_line(rs(j).dname);
 end loop;
end;
/

--Explicit cursors (명시적 커서)

--명시적
begin
 FOR i IN (SELECT * FROM emp) loop
  dbms_output.put_line(i.ename);
        end loop;
end;
/

--명시적 커서
declare
        cursor c is select ename from emp; --선언
        ename emp.ename%type;
begin
        open c; --open
        loop
                fetch c into ename; -- fetch
                exit when c%notfound; --속성이용 나오기
                dbms_output.put_line(ename);
        end loop;
 CLOSE c; --close
end;
/

-- 타입 지정과 명시적 커서 사용
--
declare
        type t is record(ENAME emp.ename%type, SAL emp.sal%type);
        deptnox emp.deptno%type :=10;
        cursor c is select ENAME, SAL from emp
                     where deptno=deptnox;
        r t;
begin
        deptnox:=20;
        open c;
        loop
                fetch c into r;
                exit when c%notfound;
                dbms_output.put_line(r.ename || ',' || r.sal);
        end loop;
        close c;
end;

--- 커서 카운트
declare
        deptnox emp.deptno%type;
        rcount number(2);
        cursor c is select * from emp
                     where deptno=deptnox;
begin
        deptnox:=20;
        for r in c loop
                dbms_output.put_line(r.ename||','||r.sal);
                rcount := c%rowcount;
        end loop;
        dbms_output.put_line(rcount);
end;

----------------중요 코드(함수형식으로 콜) 파라미터
declare
           cursor c(deptnox number) is select ename,sal from emp
                        where deptno=deptnox;
           ename emp.ename%type;
        rcount number(2);
begin
      for r in c(10) loop
              dbms_output.put_line(r.ename||','||r.sal);
                rcount := c%rowcount;
      end loop;
        dbms_output.put_line(rcount);
 end;
/

--select update 동시

declare
 r emp%rowtype;
 cursor e is select * from emp where ename='BLAKE' for update of sal nowait;
begin
 open e;

 fetch e into r;
 dbms_output.put_line(r.ename || ',' || r.sal);
 update emp set sal=sal+1 where current of e;

 close e;
end;

--error handling
begin
        dbms_output.put_line(1/(1-1));
exception
        when ZERO_DIVIDE then
                 dbms_output.put_line('su /0 <--err');
end;

declare
        ename emp.ename%type;
        cursor c is select ename from emp where deptno=40;
begin
        open c;
        loop
                fetch c into ename;
                exit when c%notfound;
                dbms_output.put_line(ename);
        end loop;
end;

--2개의 에러 처리
declare
        r emp%rowtype;
begin
        select * into r from emp where deptno=30;
        dbms_output.put_line(r.ename);
exception
        when NO_DATA_FOUND then
                dbms_output.put_line(' no data');
        when TOO_MANY_ROWS then
                dbms_output.put_line('many row');
end;