DB스토리

PL/SQL                         Data 와 DBMS를 조작하는 언어          

관리자

builtin package : 튜닝(성능,advisor), job(rman), Resourec관리 등

builtin package 을 사용

개발자 부분은 자동화가 가능함

anonymous 에서 DDL 문 사용할려면

execute immediate ' create~~~~~';

일반 변수 사용할려면 : <== 붙여야함

ex)

var x number;

exec :x  := 10;

print x

exec dbms_output.put_line(:x);

select * from dept where deptno=:x;

oupput argumnet

in : 프로시저 내에서 값이 바뀌어도 실행 시킨 부분에서는 적용이 안됨

out : 프로시저 내에서 값이 바뀌면 실행 시킨 부분에서 값이 적용됨

create or replace procedure p4(su in out number) as
begin
    su:=0;
end;
/
var x number;

exec :x  := 40;

exec p4(x)

print x;

select into 문 사용

declare
   sumx number(10);
begin
   select sum(sal) into sumx from emp;
   dbms_output.put_line(sumx);
end;

/
29025

PL/SQL procedure successfully completed.

S SCOTT> select sum(sal) from emp;

  SUM(SAL)
----------
     29025

sumx는 scalar값 혹은 vector 값 둘다 가능하나 row가 무조건 1개.

type, rowtype

declare
        enamex emp.ename%type;
        deptx dept%rowtype;
begin
        enamex :='Blake';
        deptx.deptno := 52;
        deptx.dname := 'sales2';
        deptx.loc := 'seoul';
        dbms_output.put_line(enamex || deptx.dname);
end;
/

create or replace procedure depts(su dept.deptno%type) as
        depts dept%rowtype;
begin
        select dname,loc into depts.dname, depts.loc
        from dept where deptno=su;
        dbms_output.put_line('dname= ' || depts.dname);
        dbms_output.put_line('loc= ' || depts.loc);
end;
/

cursor

변수= 값

rowtype=1차원 값들

table = 2차원 값들
cursor = 2차원 값들

declare
   cursor emp_cur is select * from emp where deptno=10;
begin
   for emrow in emp_cur loop
           dbms_output.put_line(emrow.ename);
   end loop;
end;
CLARK
KING
MILLER

scott 의 emp 테이블의 ename를 이용해서 user 만들기

conn / as sysdba
create or replace procedure createU
as
   cursor createU_cur is select scott.emp.ename from scott.emp;
begin
        for userrow in createU_cur loop
                execute immediate 'create user' ||  userrow.ename || 'identified by "1"';
                execute immediate 'grant create session to userrow.ename';
        end loop;
end;
/

cursor 보다 쉬운 for 문    ( 잘 쓰지 말것...ㅡㅡ;)                                                   적은 row

 begin
      for e in (select * from emp) loop
              dbms_output.put_line(e.ename);
      end loop;
   end;
   /

cursor의 확장

적은 row 일때.

declare
        cursor ec is select * from emp;
begin
        for e in ec loop
                dbms_output.put_line(e.ename);
        end loop;
end;
/

이걸 확장 시키면                                ( 일반적 방법 )

declare
        cursor ec is select * from emp;
        e emp%rowtype;
begin                                                                                               
        open ec;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename);
        end loop;
end;
/

create or replace procedure empd(dn emp.deptno%type)
as
        cursor ec is select * from emp where deptno=dn;
        e emp%rowtype;
begin
        if ec%ISOPEN = FALSE then
                open ec;
        end if;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
end;
/

ISOPEN            :  열려 있는냐

NOTFOUND      : 찾았는냐

ROWCOUNT      :  갯수 세기

close

declare
        dn emp.deptno%type;
        cursor ec is select * from emp where deptno=dn;
        e emp%rowtype;
begin
        dn := 10;
        open ec;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
        close ec;
                dn := 20;
                open ec;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
end;
/

암시적 cursor 의 속성

◈ SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
◈ SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
◈ SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
◈ SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

◈%ISOPEN
- 커서가 OPEN 되어 있으면 TRUE
- %ISOPEN 속성을 이용하여 커서가 열려있는지 알 수 있습니다.
◈%NOTFOUND
- 패치한 데이터가 행을 반환하지 않으면 TRUE
- %NOTFOUND 속성을 이용하여 루프를 종료할 시점을 찾습니다.
◈%FOUND
- 패치한 데이터가 행을 반환하면 TRUE
◈ %ROWCOUNT
- 현재까지 반환된 모든 데이터 행의 수
- %ROWCOUNT 속성을 이용하여 정확한 숫자만큼의 행을 추출합니다.

exception

PL/SQL declares predefined exceptions globally in package STANDARD. You need not declare them yourself. You can write handlers for predefined exceptions using the names in the following table:

SQLERRM                              dbms_output.put_line(SQLERRM);  

SQLCODE                              dbms_output.put_line(SQLCODE);

ex)
WHEN DUP_VAL_ON_INDEX THEN
   DBMS_OUTPUT.PUT_LINE('데이터가 존재 합니다.');
   DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
WHEN TOO_MANY_ROWS THEN
   DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러 발생');
WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생');
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

package

프로시저와 함수들의 집합

oop 에서의 overloading지원, 추가로 return 값 유무에 대한 overloading

create or replace package myPackage
 is
    procedure gugu2(su number);
    procedure print(st varchar2);
 end;
 /

create or replace package body myPackage
is
   procedure gugu2(su number)
   as
   begin
           for i in 1..9 loop
                   dbms_output.put_line(su || 'X' || i || '=' || su*i);
           end loop;
   end;
   procedure print(st varchar2)
   as
   begin
           dbms_output.put_line(st);
   end;
end;
/

exec myPackage.gugu(3)

exec myPackage.print('aaaa')

overloading

create or replace package myPackage
is
    procedure gugu;
    procedure gugu(dan number);
    procedure gugu(danStart number, danEnd number);
    procedure print(st varchar2);
        function gugu(dan number) return varchar2;
end;
/

create or replace package body myPackage
is
        function gugu(dan number) return varchar2
        is
                theString varchar2(2000) := '';
        begin
                for i in 1..9 loop
                        theString := theString ||','||dan||'X'||i||'='||dan*i;
                end loop;
                return theString;
        end;

        procedure gugu
        as
        begin
                for i in 2..9 loop
                        for j in 1..9 loop
                                dbms_output.put_line(i||'X'||j||'='||i*j);
                        end loop;
                end loop;
        end;

     procedure gugu(dan number)
     as
     begin
             for i in 1..9 loop
                     dbms_output.put_line(dan || 'X' || i || '=' || dan*i);
             end loop;
     end;

        procedure gugu(danStart number, danEnd number)
        as
        begin
                for i in danStart..danEnd loop
                        for j in 1..9 loop
                                dbms_output.put_line(i||'X'||j||'='||i*j);
                        end loop;
                end loop;
        end;

     procedure print(st varchar2)
     as
     begin
             dbms_output.put_line(st);
     end;
end;
/

empDML

create or replace package empDml
is
        function empInsert(
                        pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            Pcomm emp.comm%type,
            PDeptno emp.deptno%type) return emp.empno%type;
        function empUpdate(
                        pEmpno emp.empno%type,
            pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            Pcomm emp.comm%type,
            PDeptno emp.deptno%type) return number;
        procedure empDelete(pEmpno emp.empno%type);
end;
/
create or replace package body empDML
is
        function empInsert(
            pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            Pcomm emp.comm%type,
            PDeptno emp.deptno%type) return emp.empno%type
        as
                ret emp.empno%type;
        begin
        insert into emp values(emps.nextval, pEname,pJob,pMgr,pHiredate,Psal,Pcomm,PDeptno);
        select emps.currval into ret from dual;
        commit;
        return ret;
exception
        when others then
                if SQLCODE=-2291 then
                dbms_output.put_line('no FK...ERROR ');
                return null;
            end if;
        end;

        function empUpdate(
            pEmpno emp.empno%type,
            pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            PDeptno emp.deptno%type) return number
        as
        updateCount number(10):=0;
        begin
        update emp set
                empno=pEmpno,
                ename=pEname,
                job=pJob,
                mgr=pMgr,
                hiredate=pHiredate,
                sal=Psal,
                comm=Pcomm,
                deptno=PDeptno
        where empno=pEmpno;

        select count(*) into updateCount from emp where empno=pEmpno;
        return updateCount;
        end;

        procedure empDelete(pEmpno emp.empno%type)
        as
        begin
        delete from emp where empno= pEmpno;
        commit;
        end;
end;
/