관리자 입장에서의 PL/SQL

▣ 관리자 입장에서 필요한 PL/SQl 내공

▶ 개발자가 만든 pl/sql 스크립트 이해할 것

▶ Built-in Package 분석 => 실행

=> package 검색(DBMS_??) => 함수, 프로시져, Argument 분석

▶ 튜닝용 패키지 설치 및 운영

- 오라클에서 제공하는 : statspack

- 고수들이 제공하는 

- 개인적으로 만든

▶ 작업 및 튜닝을 위한 업무 자동화

▶ JobScheduler를 운영 by pl/sql

   

▣ Analyze Object

SQL> begin

2 dbms_ddl.analyze_object('TABLE','HR','EMP','COMPUTE');

3 end;

4 /

PL/SQL procedure successfully completed.

S SYS> select table_name, num_rows from dba_tables

where owner='HR' and table_name='EMP';

   

TABLE_NAME                       NUM_ROWS

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

EMP                                    14

   

   

S SYS> truncate table hr.emp;

Table truncated.

SQL> begin

2 dbms_ddl.analyze_object('TABLE','HR','EMP','COMPUTE');

3 end;

4 /

PL/SQL procedure successfully completed.

S SYS> select table_name,num_rows from dba_tables where owner='HR' and table_name='EMP';

   

TABLE_NAME                       NUM_ROWS

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

EMP                                     0

   

▣ Script

S SYS> @fp

Enter value for key: DDL

OBJECT_NAME

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

OWM_DDL_PKG

WM_DDL_UTIL

LCR$_DDL_RECORD

DBMS_DDL_INTERNAL

LTDDL

DBMS_DDL

NameFromLastDDL

7 rows selected.

S SYS> spool DBMS_DDL

S SYS> desc DBMS_DDL

S SYS> spool off

S SYS> !vi DBMS_DDL.lst

PROCEDURE ALTER_COMPILE

Argument Name Type In/Out Default?

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

TYPE VARCHAR2 IN

SCHEMA VARCHAR2 IN

NAME VARCHAR2 IN

REUSE_SETTINGS BOOLEAN IN DEFAULT

PROCEDURE ALTER_TABLE_NOT_REFERENCEABLE

Argument Name Type In/Out Default?

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

TABLE_NAME VARCHAR2 IN

TABLE_SCHEMA VARCHAR2 IN DEFAULT

AFFECTED_SCHEMA VARCHAR2 IN DEFAULT

PROCEDURE ALTER_TABLE_REFERENCEABLE

Argument Name Type In/Out Default?

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

TABLE_NAME VARCHAR2 IN

TABLE_SCHEMA VARCHAR2 IN DEFAULT

AFFECTED_SCHEMA VARCHAR2 IN DEFAULT

PROCEDURE ANALYZE_OBJECT

Argument Name Type In/Out Default?

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

TYPE VARCHAR2 IN

SCHEMA VARCHAR2 IN

NAME VARCHAR2 IN

METHOD VARCHAR2 IN

ESTIMATE_ROWS NUMBER IN DEFAULT

ESTIMATE_PERCENT NUMBER IN DEFAULT

METHOD_OPT VARCHAR2 IN DEFAULT

PARTNAME VARCHAR2 IN DEFAULT

PROCEDURE CREATE_WRAPPED

Argument Name Type In/Out Default?

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

DDL VARCHAR2 IN

PROCEDURE CREATE_WRAPPED

Argument Name Type In/Out Default?

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

DDL TABLE OF VARCHAR2(256) IN

LB BINARY_INTEGER IN

UB BINARY_INTEGER IN

PROCEDURE CREATE_WRAPPED

Argument Name Type In/Out Default?

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

DDL TABLE OF VARCHAR2(32767) IN

LB BINARY_INTEGER IN

UB BINARY_INTEGER IN

FUNCTION IS_TRIGGER_FIRE_ONCE RETURNS BOOLEAN

Argument Name Type In/Out Default?

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

TRIG_OWNER VARCHAR2 IN

TRIG_NAME VARCHAR2 IN

FUNCTION IS_TRIGGER_FIRE_ONCE_INTERNAL RETURNS BINARY_INTEGER

Argument Name Type In/Out Default?

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

TRIG_OWNER VARCHAR2 IN

TRIG_NAME VARCHAR2 IN

PROCEDURE SET_TRIGGER_FIRING_PROPERTY

Argument Name Type In/Out Default?

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

TRIG_OWNER VARCHAR2 IN

TRIG_NAME VARCHAR2 IN

FIRE_ONCE BOOLEAN IN

FUNCTION WRAP RETURNS VARCHAR2

Argument Name Type In/Out Default?

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

DDL VARCHAR2 IN

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(256)

Argument Name Type In/Out Default?

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

DDL TABLE OF VARCHAR2(256) IN

LB BINARY_INTEGER IN

UB BINARY_INTEGER IN

FUNCTION WRAP RETURNS TABLE OF VARCHAR2(32767)

Argument Name Type In/Out Default?

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

DDL TABLE OF VARCHAR2(32767) IN

LB BINARY_INTEGER IN

UB BINARY_INTEGER IN

   

   

 S SYS> begin

  2     dbms_ddl.ANALYZE_OBJECT(?,'HR','EMP',?); => 들어가는 ? 는 DBA_SOURCE에서 검색한다.

   

S SYS> @fv

Enter value for key: DBA_%SOUR%

VIEW_NAME

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

DBA_SOURCE_TABLES

DBA_SOURCE

_DBA_APPLY_SOURCE_SCHEMA

_DBA_APPLY_SOURCE_OBJ

DBA_TSM_SOURCE

DBA_HIST_RESOURCE_LIMIT

DBA_RESOURCE_INCARNATIONS

7 rows selected.

S SYS> desc DBA_SOURCE

 Name                                                        Null?    Type

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

 OWNER                                                                VARCHAR2(30)

 NAME                                                                 VARCHAR2(30)

 TYPE                                                                 VARCHAR2(12)

 LINE                                                                 NUMBER

 TEXT                                                                 VARCHAR2(4000)

S SYS> select count(*) from dba_source where name like '%DBMS_DDL%';

  COUNT(*)

----------

       288

S SYS> select count(*) from dba_source where name like '%ANALYZE_OBJECT%';

  COUNT(*)

----------

         0   => 카운트가 놓게 나온 것을 다시 검색한다

검색방법

   select text from dba_source where name like '%DBMS_DDL%' and type='PACKAGE' order by line;

   

▣ 참고

▶ set serveroutput on/off 와 같은 역할을 하는 pl/sql문

S SYS> exec dbms_output.disable;

PL/SQL procedure successfully completed.

S SYS> exec dbms_output.put_line('aa');

PL/SQL procedure successfully completed.

S SYS> exec dbms_output.enable;

PL/SQL procedure successfully completed.

S SYS> exec dbms_output.put_line('aa');

aa

PL/SQL procedure successfully completed.

   

▶ 이어 붙이기 (띄어쓰기 안되게 하는 방법)

S SYS> r

  1  begin

  2     for i in 1..10 loop

  3             dbms_output.put(i);

  4     end loop;

  5     dbms_output.new_line;

  6* end;

12345678910

PL/SQL procedure successfully completed.

dml 구현에서 Exception

S HR> @empInsertTest

begin

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.FK_EMP) violated - parent key not found

ORA-06512: at "HR.EMPINSERT", line 13

ORA-06512: at line 2

empInsert 에 아래와 같이 추가

exception

        when others then

                dbms_output.put_line(SQLERRM);

                dbms_output.put_line(SQLCODE);

                return null;

S HR> @empInsert

S HR> @empInsertTest =>  에러 메시지, 에러 번호 확인 후.. 아래와 같이 수정

exception

        when others then

                if SQLCODE=-2291 then

                        dbms_output.put_line('no Fk.err');

                        return null;

                end if;

S HR> @empInsertTest

no Fk.err

PL/SQL procedure successfully completed.

   

Package 실습

▣ empCreate.sql

conn / as sysdba

create table hr.emp as select * from scott.dept

/

create table hr.dept as select * from scott.emp

/

conn hr/hr

alter table emp add constraint pk_emp primary key(empno)

/

alter table dept add constraint pk_dept primary key(deptno)

/

alter table dept add constraint fk_emp foreign key(deptno) references dept(deptno)

/

create sequence emps start with 7950

/

create sequence depts start with 60 increment by 10

/

   

▣ empTest.sql

conn hr/hr

select count(*) from emp;

select count(*) from dept;

select emps.nextval from dual;

select depts.nextval from dual;

   

▣ empDrop.sql

conn hr/hr

drop table emp;

drop table dept;

drop sequence emps;

drop sequence depts;

   

▣ empInsert.sql

create or replace 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 number

as

        ret emp.empno%type;    =>    emps.currval은 리턴값으로 리턴할 수 없기 때문에 이 구문 사용해서 리턴한다.

begin

        insert into emp values(emps.nextval,pEname,pJob,pMgr,pHiredate,pSal,pComm,pDeptno);

        select emps.currval into ret from dual;

        return ret;

end;

/

   

▣ 확인

S HR> var e number;

S HR> exec :e := empInsert('hoho','MANAGER',7934,sysdate,980,null,20);

PL/SQL procedure successfully completed.

S HR> select * from emp where empno=:e;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7951 hoho       MANAGER         7934 01-JUN-09        980                    20

   

S HR> begin

  2     :e := empInsert('hoho','MANAGER',7934,sysdate,980,null,20);

  3  end;

  4  /

S HR> sav empInsertTest

   

▣ empUpdate.sql

empUpdate

   

select 'p'||column_name||' '||'&&TABLE_NAME.'||column_name||'%type.'

from user_tab_cols where table_name='&TABLE_NAME'

/

undefine TABLE_NAME

/

   

 :%s/EMP/\&TABLE\_NAME/g

sav argList

define TABLE_NAME;

select column_name||'=p'||column_name||','

from user_tab_cols where table_name='&TABLE_NAME'

/

sav ucolList

위에서 sav 한 sql을 이용하여 empUpdate 작성 시 이용한다.  

   

create or replace 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

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;

/

   

var uc number;

begin

    :uc :=empUpdate(7369,'SMITH','CLERK',7902,to_date('1980.12.17','yyyy.mm.dd'),999,10,20);

end;

/

print uc;

   

▣ empDelete.sql

empDelete

 create or replace function empDelete (

        pEMPNO emp.empno%type)

return number

as

        deleteCount number(10) :=0;

begin

        select count(*) into deleteCount from emp where empno=pEMPNO;

        delete from emp where empno=pEMPNO;

        return deleteCount;

end;

/

   

▣ Package

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 number;

        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;

        function empDelete(pEMPNO emp.empno%type) return number;

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 number

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;

        return ret;

exception

        when others then

                if SQLCODE=-2291 then

                        dbms_output.put_line('no Fk.err');

                        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,

        pComm emp.comm%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;

        function empDelete(

        pEMPNO emp.empno%type)

return number

as

        deleteCount number(10) :=0;

begin

        select count(*) into deleteCount from emp where empno=pEMPNO;

        delete from emp where empno=pEMPNO;

        return deleteCount;

        end;

end;

/

   

▣ empTruncate

empTruncate

commit;

drop table emp;

drop table dept;

drop sequence emps;

drop sequence depts;

@@empCreate.sql            @@ : 같은 위치에 있는 sql 파일 실행   

truncate table emp;          truncate는 상황에 맞게 설정해 줌

truncate table dept;

drop package empDml;

   

▣ 파일 리스트

empCreate.sql	emp/dept 테이블 생성 및 시퀀스 생성
empDelete.sql	Row 삭제
empDeleteTest.sql	empDelete 내용 실행
empDrop.sql	생성된 테이블 및 시퀀스, 패키지 삭제
empInsert.sql	Row 추가
empInsertTest.sql	empInsert 실행
empTruncate.sql	테이블 삭제 및 시퀀스 삭제
empUpdate.sql	Row 업데이트
empUpdateTest.sql	empUpdate실행

 

Argument 명시적 선택

SQL> create or replace procedure p6(

2 a1 number default 1,

3 a2 number default 2,

4 a3 number default 3,

5 a4 number default 4,

6 a5 number)

7 as

8 begin

9 dbms_output.put_line((a1+a2+a3+a4+a5));

10 end;

11 /

   

Procedure created.

   

SQL> exec p6(null,null,null,null,5);

   

PL/SQL procedure successfully completed.

   

SQL> exec p6(1,2,3,4,5);

15

   

PL/SQL procedure successfully completed.

   

SQL> exec p6(a5=>5);

15

   

PL/SQL procedure successfully completed.

   

SQL> exec p6(a5=>5,a3=>6);

18

   

PL/SQL procedure successfully completed.

PL/SQL 객체 검색 및 Argument 보기

▣ PL/SQL 객체 검색 및 Argument 보기

뷰 검색 -> 컬럼 리스트 찾기 -> 질의 실행

PL 객체 검색 -> Argument 리스트 찾고 -> 실행

   

S SCOTT> get fp

    select distinct object_name from user_procedures where object_name like '%&KEY%'

S SCOTT> select distinct object_name from user_procedures where object_name like '%&KEY%';

Enter value for key: MYPACK

OBJECT_NAME

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

MYPACKAGE

S SCOTT> desc MYPACKAGE

PROCEDURE GUGU

 Argument Name                  Type                    In/Out Default?

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

 DAN                            NUMBER                  IN

PROCEDURE PRINT

 Argument Name                  Type                    In/Out Default?

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

 ST                             VARCHAR2                IN

   

   

▣ PL/SQL 객체 검색 및 Argument 보기 - sys 소유, built-in)

S SYS> @fp

Enter value for key: OUTPUT

OBJECT_NAME

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

DBMS_OUTPUT

DBMS_REPCAT_OUTPUT

S SYS> spool DBMS_OUTPUT    =>   내용이 많으면 확인이 불가능 하므로 spool로 출력 리스트를 저장한다.

S SYS> desc DBMS_OUTPUT

PROCEDURE DISABLE

PROCEDURE ENABLE

 Argument Name                  Type                    In/Out Default?

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

 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT

PROCEDURE GET_LINE

 Argument Name                  Type                    In/Out Default?

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

 LINE                           VARCHAR2                OUT

 STATUS                         NUMBER(38)              OUT

PROCEDURE GET_LINES

 Argument Name                  Type                    In/Out Default?

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

 LINES                          TABLE OF VARCHAR2(32767) OUT

 NUMLINES                       NUMBER(38)              IN/OUT

PROCEDURE GET_LINES

 Argument Name                  Type                    In/Out Default?

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

 LINES                          DBMSOUTPUT_LINESARRAY   OUT

 NUMLINES                       NUMBER(38)              IN/OUT

PROCEDURE NEW_LINE

PROCEDURE PUT

 Argument Name                  Type                    In/Out Default?

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

 A                              VARCHAR2                IN

PROCEDURE PUT_LINE

 Argument Name                  Type                    In/Out Default?

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

 A                              VARCHAR2                IN

S SYS> spool off

S SYS> ed DBMS_OUTPUT.lst    =>    spool로 저장된 내용 확인

S SYS> exec DBMS_OUTPUT.PUT_LINE('aaa');

aaa

PL/SQL procedure successfully completed.

   

Package - 2

▣ Package Overloading : 프로시져 명이 같아도 Argument의 수에 따라 실행되는 Procedure가 다르다.

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

        as

                theString varchar2(2000) :='';

        begin

                for i in 1..9 loop

                        theString := theString || ',' || dan || 'X' ||i|| '=' || dan*i;

                end loop;

                return theString;

        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

                        gugu(i);

                end loop;

        end;

        procedure gugu as

        begin

                gugu(2,9);

                 end;

        procedure print(st varchar2)

        as

        begin

              dbms_output.put_line(st);

        end;

end;

/

Package - 1

▣ Package 프로시저와 함수들의 집합, oop에서의 overloading지원, return값 유무에 대한 overloading

   

SQL> create or replace package myPackage

2 is

3 procedure gugu(dan number);

4 procedure print(st varchar2);

5 end;

6 /

   

Package created.

   

SQL> create or replace package body myPackage

2 is

3 procedure gugu(dan number) as

4 begin

5 for i in 1..9 loop

6 dbms_output.put_line(dan || 'x' || i || '=' || dan*i);

7 end loop;

8 end;

9 procedure print(st varchar2)

10 as

11 begin

12 dbms_output.put_line(st);

13 end;

14 end;

15 /

   

Package body created.

   

SQL> exec myPackage.gugu(3);

3x1=3

3x2=6

3x3=9

3x4=12

3x5=15

3x6=18

3x7=21

3x8=24

3x9=27

   

PL/SQL procedure successfully completed.

   

SQL> exec myPackage.print('aaa');

aaa

   

PL/SQL procedure successfully completed.

   

   

   

실제로 구현할 때는 Header와 Body가 한 파일(myPackage)에 들어간다.(마지막라인에는 실행부(exec)를 사용하여 확인할 수 있다.) 

   

create or replace package myPackage

is

       procedure gugu(dan number);

       procedure print(st varchar2);

end;

/

create or replace package body myPackage

    is

      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 print(st varchar2)

      as

      begin

              dbms_output.put_line(st);

      end;

end;

/

exec myPackage.gugu(3);

exec myPackage.print('a');

   

Exception 예

▣ Exception 의 예

S SCOTT> r

  1  create or replace procedure insertDept(

  2     deptnox dept.deptno%type,

  3     dname dept.dname%type,

  4     locxdept.loc%type

  5  ) as

  6  begin

  7     insert into dept values(deptnox,dnamex,locx);

  8* end;

S SCOTT> @insertDept

Procedure created.

S SCOTT> exec insertDept(1,'aaa','bb');

PL/SQL procedure successfully completed.

S SCOTT> select * from dept;

S SCOTT> exec insertDept(10,'bbbb','bbbb');

BEGIN insertDept(10,'bbbb','bbbb'); END;

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

ORA-06512: at "SCOTT.INSERTDEPT", line 7

ORA-06512: at line 1

create or replace procedure insertDept(

        deptnox dept.deptno%type,

        dnamex dept.dname%type,

        locx dept.loc%type

) as

begin

        insert into dept values(deptnox,dnamex,locx);

exception

        when DUP_VAL_ON_INDEX then

                for d in (select * from dept where deptno=deptnox) loop

                        dbms_output.put_line(d.deptno||','||d.dname||' is exist');

                end loop;

        WHEN OTHERS THEN

                dbms_output.put_line(SQLERRM);

                dbms_output.put_line(SQLCODE);

end;

/

   

▣ 급여가 500 이하면 작업을 롤백 시키는 기능을 추가하시오

S SCOTT> create or replace procedure raiseSal(empnox emp.empno%type,raiseSal number)

  2  as

  3  begin

  4     update emp set sal=sal+raiseSal where empno=empnox;

  5  end;

  6  /

Procedure created.

create or replace procedure raiseSal(empnox emp.empno%type,raiseSal number)

as

        low_sal_err EXCEPTION;

begin

        update emp set sal=sal+raiseSal where empno=empnox;

        for i in (select sal from emp where empno=empnox) loop

                if i.sal<500 then

                        raise low_sal_err;

                else

                        commit;

                end if;

        end loop;

exception

        when low_sal_err then

                rollback;

                dbms_output.put_line(raiseSal || ' is too low sal');

end;

/

S SCOTT> @raiseSal

Procedure created.

S SCOTT> exec raiseSal(7369,-500);

-500 is too low sal

PL/SQL procedure successfully completed.

Exception

SQL> begin

2 dbms_output.put_line(3/1);

3 dbms_output.put_line(3/0);

4 end;

5 /

3

begin

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

ORA-06512: at line 3

   

SQL> begin

2 dbms_output.put_line(3/1);

3 dbms_output.put_line(3/0);

4 exception

5 when others then

6 dbms_output.put_line('err');

7 end;

8 /

3

err

   

PL/SQL procedure successfully completed.

   

   

SQL> begin

2 dbms_output.put_line(3/1);

3 dbms_output.put_line(3/0);

4 exception

5 when ZERO_DIVIDE then

6 dbms_output.put_line('Divided by zero');

7 when others then

8 dbms_output.put_line(SQLERRM);

9 dbms_output.put_line(SQLCODE);

10 end;

11 /

3

Divided by zero

   

PL/SQL procedure successfully completed.

   

SQL>

SQL> declare

2 e emp%rowtype;

3 begin

4 dbms_output.put_line(3/1);

5 select * into e from emp where sal=3000;

6 dbms_output.put_line(e.ename || '''s sal is ' || e.sal);

7 exception

8 when TOO_MANY_ROWS then

9 dbms_output.put_line('More than One Row');

10 when others then

11 dbms_output.put_line(SQLERRM);

12 dbms_output.put_line(SQLCODE);

13 end;

14 /

3

More than One Row

   

PL/SQL procedure successfully completed.

   

While loop

▣ while loop 기초

SQL> declare

2 x number(12);

3 begin

4 x := 1;

5 while x<10 loop

6 dbms_output.put_line(x);

7 x := x+1;

8 end loop;

9 end;

10 /

1

2

3

4

5

6

7

8

9

   

PL/SQL procedure successfully completed.

   

▣ 무한루프와 해결책

S SCOTT> r      

  1  declare

  2     x number(12);

  3  begin

  4     x:=1;

  5     while x<10 loop

  6             dbms_output.put_line(x);

  7  --         x:=x+1;

  8     end loop;

  9* end;

   

 S SYS> select sid,serial# from v$session

  2  where username='SCOTT';

   

S SYS> alter system kill session '123,3859';    SID : 123, serial# : 3859

커서 활용 - 2

▣ 커서 기본 구문

SQL> declare

2 cursor ec is select * from emp;

3 e emp%rowtype;

4 begin

5 if ec%ISOPEN = FALSE then

6 open ec;

7 end if;

8 loop

9 fetch ec into e;

10 exit when ec%NOTFOUND;

11 dbms_output.put_line(e.ename);

12 end loop;

13 end;

14 /

   

▣ 부서명을 입력받아서 해당 부서의 직원명과 급여 출력

SQL> create or replace procedure empd(

2 dn emp.deptno%type

3 ) as

4 cursor ec is select * from emp where deptno = dn;

5 e emp%rowtype;

6 begin

7 if ec%ISOPEN = FALSE then

8 open ec;

9 end if;

10 loop

11 fetch ec into e;

12 exit when ec%NOTFOUND;

13 dbms_output.put_line(e.ename || '-' || e.sal);

14 end loop;

15 end;

16 /

   

Procedure created.

   

SQL> exec empd(10);

CLARK-2450

KING-5000

MILLER-1300

   

PL/SQL procedure successfully completed.

   

▣ ROWCOUNT

SQL> r

1 create or replace procedure empd(

2 dn emp.deptno%type

3 ) as

4 cursor ec is select * from emp where deptno = dn;

5 e emp%rowtype;

6 begin

7 if ec%ISOPEN = FALSE then

8 open ec;

9 end if;

10 loop

11 fetch ec into e;

12 exit when ec%NOTFOUND;

13 dbms_output.put_line(e.ename || '-' || e.sal || '-' || ec%ROWCOUNT); => 출력되는 값의 개수 출력

14 end loop;

15* end;

   

Procedure created.

   

SQL> exec empd(10);

CLARK-2450-1

KING-5000-2

MILLER-1300-3

   

PL/SQL procedure successfully completed.

   

▣ cursor 재 오픈

declare     =>     close 사용방법, 커서를 재 open 할 수 있다.

        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 || '-' || e.deptno);

        end loop;

        close ec;

        dn := 20;

                open ec;

        loop

                fetch ec into e;

                exit when ec%NOTFOUND;

                dbms_output.put_line(e.ename ||'-'|| e.sal || '-' || e.deptno);

        end loop;

end;

/