PL/SQL 기초 사용 예

 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:

   

Exception	ORA Error	SQLCODE	Raise When ...
ACCESS_INTO_NULL	06530	-6530	A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND	06592	-6592	None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	06531	-6531	A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	06511	-6511	A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	00001	-1	A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR	01001	-1001	A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED	01017	-1017	A program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUND	01403	+100	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON	01012	-1012	A program issues a database call without being connected to Oracle.
PROGRAM_ERROR	06501	-6501	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	06504	-6504	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL	30625	-30625	A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
STORAGE_ERROR	06500	-6500	PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT	06533	-6533	A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	06532	-6532	A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID	01410	-1410	The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	00051	-51	A time out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS	01422	-1422	A SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE	01476	-1476	A program attempts to divide a number by zero.

   

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;

/