7.7 ~7.9

Oracle/PLSQL 2010/08/06 14:11
-- 1.출력 프로시저
exec dbms_output.put_line(3*4);
2. 출력안될때 실행해야 하는 명령
set serveroutput ON

--기본 문
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;

2010/08/06 14:11 2010/08/06 14:11

트랙백 주소 :: 이 글에는 트랙백을 보낼 수 없습니다