Admin 1. 08-1 장 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;
/