악덕고용주의 개발 일기

ORACLE 서브프로그램의 이해와 프로시저 - (9)

2017. 9. 18. 15:44

[ 서브프로그램의 이해, 프로시저 ]

: PL/SQL의 서브프로그램이란?

-> <PL/SQL의 익명 블럭의 특징>

: . 이름이 없는 PL/SQL 블럭

. 저장해서 사용되지 않는다.

. 사용할 때마다 컴파일이 필요하다.

. 다른 응용프로그램에서 사용할 수 없다.

. 값을 반환할 수 없다.

. 매개변수를 사용할 수 없다.

-> <서브프로그램의 특징>

: . 이름이 있는 PL/SQL 블럭

. DB에 저장해 반복적으로 사용할 수 있다.

. 최초 실행될 때 한번만 컴파일 한다.

. 다른 응용프로그램에서 사용할 수 있다.

. 함수일 경우 값을 리턴(반환)할 수 있다.

. 매개변수를 사용할 수 있다.

** "서브 프로그램"은 내장된 함수와 프로시저가 있다.

- 프로시저

- 함수

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

- 프로시저란??

: 특정 처리를 위한 서브 프로그램의 한 유형이다.

: 단독으로 실행 가능하며, 다른 응용 프로그램에서도 호출되어 실행 가능하다.

- 프로시저 구문 형식)

create procedure 프로시저명

파라미터1(in,out,in out) 데이터타입, -- default는 in모드

-- out : 값을 반환하겠다, in : 서브프로그램 내에 값 전달 in out : 서브프로그램 내에도 전달되고, 외부 응용프로그램에도 반환하기도 함

파라미터2(in,out,in out) 데이터타입

변수 선언부;

begin

프로시저 본문 처리부;

exception

예외처리부;

end;

EX)

-- 프로시저 EX)
create or replace procedure update_emp -- 매개변수가 없는 프로시저 
is
  -- 변수 선언부...
begin
  update employees set first_name = '마이클'
  where department_id = 20 and job_id = 'MK_MAN';
end;
/
/* 프로시저를 실행시키기 위한 구문 
  exec 또는 execute 
*/
execute update_emp; -- 프로시저 실행

select * from employees where department_id = 20 and job_id = 'MK_MAN';

-- 매개변수에 모드 지정안하면 default로 in모드로 지정됨.
create or replace procedure up_sal
(emp_id employees.employee_id%type)
IS
BEGIN
  update employees set salary = 3500 where employee_id = emp_id; -- 프로시저 매개변수로 전달받은 값으로 처리
END;
/

-- 매개변수가 있는 프로시저 호출
exec up_sal(130);

create or replace procedure emp_name_sal
  (emp_id employees.employee_id%type)
IS
  emp_name employees.last_name%type;
  emp_salary employees.salary%type;
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id;
  
  DBMS_OUTPUT.put_line('사원명 : '||emp_name);
  DBMS_OUTPUT.put_line('급여 : '||emp_salary);
END;
/
-- 매개변수를 전달해 프로시저 수행
exec emp_name_sal(124);

-- out 모드 사용의 예
      --  emp_id는 in모드로 emp_name,emp_salary는 out모드로
      -- 즉, emp_id는 값을 프로시저 내로 가져올 때 사용하는 변수고
      -- out모드 변수 두개는 리턴할 때 사용하는 변수다.
create or replace procedure emp_info
  (emp_id employees.employee_id%type,
   emp_name out employees.last_name%type,
   emp_salary out employees.salary%type)
IS
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id; -- 입력받은 사원번호와 일치하는 것 검색
END;
/

-- 다른 PL/SQL내에서 프로시저 사용해 보기
declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
begin
  -- 위에서 작성해 컴파일된 프로시저를 다른 PL/SQL 블럭에서 사용하기
  emp_info(124, emp_name, salary); -- 124는 in으로 프로시저 내에 전달하고 out변수 값들은
  -- 각각 emp_name과 salary변수를 써주면 해당 변수에 데이터가 리턴되어 저장되게 된다.
  
  DBMS_OUTPUT.put_line(emp_name||' '||salary);
end;
/

create or replace procedure emp_info
  (emp_id employees.employee_id%type,
   emp_name out employees.last_name%type,
   emp_salary out employees.salary%type)
IS
BEGIN
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id; -- 입력받은 사원번호와 일치하는 것 검색
END;

-- 익명블럭 외에서 프로시저를 호출하는 예)
variable emp_n varchar2(10); -- 블럭 밖에서 변수 선언 방법
variable sal number;
-- 바인드 변수를 통해 프로시저 호출하는 방법
execute emp_info(124,:emp_n,:sal);

print emp_n sal

set serveroutput on;

declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
begin
  -- 연산자 =>를 이용해서 파라미터의 값을 지정하는 방식
    -- emp_id는 124로 지정 out타입인 emp_name은 emp_name으로 지정
  emp_info(emp_id => 124,
           emp_name => emp_name,
           emp_salary => salary); 
  dbms_output.put_line(emp_name ||' '||salary);
end;
/

[ 외부 변수 선언 및 전달과 out 타입을 통한 결과값 받기 예제 ]

저작자표시 비영리 (새창열림)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE 패키지의 이해 (0)	2017.09.20
ORACLE 서브프로그램 함수(Function) (0)	2017.09.19
ORACLE 예외처리 개념, 종류, 사용법 - (8) (0)	2017.09.18
ORACLE 커서(CURSOR) - (7) (0)	2017.09.15
ORACLE 반복문(basic loop, while, for, continue) -(6) (0)	2017.09.14

ORACLE 예외처리 개념, 종류, 사용법 - (8)

2017. 9. 18. 11:16

[ ORACLE 예외처리 개념, 종류, 사용법 - (8) ]

- 에러 의 종류 ]

1. 컴파일 에러 : PL/SQL 블럭이 파싱(Parsing)될 때 "사용자 오타" 등으로 인해 발생되는 에러.

(Compile Error)

2. 런타임 에러(=Exception)

: PL/SQL 블럭이 실행되는 동안 발생하는 에러로 일반적으로 런타임에러를 "Exception"이라 부른다.

종류)

a. 오라클 예외

: 오라클에서 제공되는 예외(Predefined ORACLE Exception과 Non-Predefined ORACLE Exception이 있다.)

ㄱ. Predefined ORACLE Exception

: 사전에 정해진 예외

종류)

- ACCESS_INTO_NULL

: 정의되지 않은 오브젝트 속성에 값을 할당하고자 했을 때 발생하는 예외.

- CASE_NOT_FOUND

: CASE문의 when절에 해당되는 조건이 없고 else절도 없을 경우 발생

- COLLECTION_IS_NULL

: 선언되지 않은 컬렉션(nested table, varray)에 존재하는 메서드

이외의 메서드를 사용했을 때 발생되는 예외.

- CURSOR_ALREADY_OPEN

: 이미 열려진 커서를 열려고 시도 했을 때 발생하는 예외

- DUP_VAL_ON_INDEX

: 유일인덱스에 중복값을 입력햇을 때 발생하는 예외.

- INVALID_CURSOR

: 잘못된 커서 조작이 샐행될 때 발생되는 예외.

- INVALID_NUMBER

: 문자를 숫자로의 변환 시 실패가 될 때 발생하는 예외.

- LOGIN_DENIED

: 잘못된 사용자명이나 암호로 로그인시도시 발생하는 예외.

- NO_DATA_FOUND

: PL/SQL Select문이 한 건도 리턴하지 못하는 경우 발생하는 예외.

- NOT_LOGGED ON

: 접속되지 않은 상태에서 데이터베이스에 대한 요청이 PL/SQL 프로그램으로

실행된 경우 발생되는 예외.

- PROGRAM_ERROR

: PL/SQL이 내부적인 문제를 가지고 있는 경우 발생되는 예외.

- ROWTYPE_MISMATCH

: 할당문에서 호스트 커서 변수와 PL/SQL 커서 변수의 데이터 형이 불일치할 때 발생되는 예외

- STORAGE_ERROR

: PL/SQL이 실행될 때 메모리가 부족하거나 메모리상에 문제가 일어났을 대 발생하는 예외.

- SUBSCRIPT_BEYOND_COUNT

: 컬렉션의 요소 갯수보다 더 큰 첨자 값으로 참조한 경우 발생

- SUBSCRIPT_OUTSIDE_LIMIT

: 컬렉션의 첨자 한계를 벗어난 참조가 일어났을 때 발생

- SYS_INVALID_ROWD

: 문자열을 ROWID로 변환할 때 무효한 문자열의 표현일 경우 발생되는 예외.

- TIMEOUT_ON_RESOURCE

: 자원에 대한 대기시간이 초과했을 때 발생하는 예외.

- TOO_MANY_ROWS

: PL/SQL select문이 두건이상의 행을 리턴햇을 때 발생되는 예외.

- VALUE_ERROR

: 산술,변환,절삭 크기 제약에 에러가 생겼을 때 발생되는 예외.

- ZERO_DIVIDE

: 0으로 나누려 했을 때 발생하는 예외.

ㄴ. Non-predefined ORACLE Exception

: 사전에 정해지지 않은 예외

b. 사용자 정의 예외

: 사용자에 의해 정의되는 예외

사용자 정의 예외 사용 예)

declare (선언부에서)

예외명 exception;

begin부나 exception부에서 raise문을 이용해서 예외를 발생시킨다.

- 예외처리부 형식)

-- 예외처리부 : 예외 발생시 어떻게 처리할 것인지에 예외처리 내용이 들어간다.

Exception

when 예외명 then

실행문...

when 예외명2 then

실행문...

when OTHERS then

실행문...

EX)

-- 미리 정의된 예외(Predefined ORACLE Exception) EX)
declare
  emp_lastname employees.last_name%type;
begin
  select last_name into emp_lastname
  from employees
  where last_name like 'B%';
  
  DBMS_OUTPUT.put_line('사원명 : ' || emp_lastname);

exception
  when no_data_found then 
    DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
  when too_many_rows then
    DBMS_OUTPUT.PUT_LINE('사원이 두명 이상입니다.');
end;
/

-- 사용자 정의 예외처리 EX)
-- 1. pragma를 이용한 컴파일러에 지정하는 방법
declare
  emp_exist exception;
  pragma exception_init(emp_exist, -2292); -- emp_exist예외는 2292번 에러 코드로 처리하겠다고 컴파일러에 알려주는 부분
begin
  delete departments 
  where department_id = &deptno; -- 사용자에게 detpno값 입력 받아 해당 내용을 지움
  
  commit; -- 반영함

exception
    when emp_exist then
      DBMS_OUTPUT.PUT_LINE('사원이 존재하기 때문에 지울 수 없습니다.');
end;
/ -- 20번 입력

-- 2. raise를 이용한 예외처리 발생 방법
declare
  emp_id employees.employee_id%type;
  emp_name employees.last_name%type;
  
  no_emp exception; -- 예외명 선언
begin
  delete employees
  where employee_id = &empid;
  
  if sql%notfound then -- "묵시적커서" 속성을 이용
    raise no_emp; -- 예외를 발생시킴
  end if;

exception
    when no_emp then -- raise로 no_emp가 발생할 경우 처리할 부분
      DBMS_OUTPUT.PUT_LINE('해당사원이 없습니다.');
end;
/

-- raise_application_error라는 프로시저를 이용하는 방법
    --  이때, 이용가능한 번호 : 20000 ~ 20999 번
declare
  emp_id employees.employee_id%type;
  emp_name employees.last_name%type;
begin
  delete employees
  where employee_id = &empid;
  
  if (sql%notfound) then 
    raise_application_error(-20111,'사원이 없습니다..'); -- 에러코드와 에러메시지 지정
  end if;
end;
/

set serveroutput on;

저작자표시 비영리 (새창열림)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE 서브프로그램 함수(Function) (0)	2017.09.19
ORACLE 서브프로그램의 이해와 프로시저 - (9) (0)	2017.09.18
ORACLE 커서(CURSOR) - (7) (0)	2017.09.15
ORACLE 반복문(basic loop, while, for, continue) -(6) (0)	2017.09.14
ORACLE 조건문(IF,CASE) -(5) (0)	2017.09.14

ORACLE 커서(CURSOR) - (7)

2017. 9. 15. 16:31

[ ORACLE 커서(CURSOR) ]

[ 커서 ]

: select문을 통해 결과값들이 나올 때 이 결과들은 메모리 공간에 저장하게 되는데

이때, 이 메모리 공간을 "커서"라고 한다.

즉,

- 커서란? : 쿼리문에 의해서 반환되는 결과값들을 저장하는 메모리 공간이다.

- Fetch란 : 커서에서 원하는 결과값을 추출하는 것.

왜사용하는가???!!

커서는 로우를 기반으로 하는 작업이 된다!!! 라는 것입니다. 저러한 로우를 하나 SELECT해서 여러가지 처리를 한후 어떠어떠한 작업을 한다~~

이것을 가능하게 하는 것이 바로!!! 커서 입니다.

물론 SQL구문만을 가지고 어거지로 어찌어찌 한다면~~ 가능할 겁니다.

하지만 커서를 이용하시면 훨씬 빠르고 간단히 저러한 처리를 가능하게 할 수 있습니다.

여기까진 이론적으로고... 그냥 근본적으로는

-> select의 결과가 하나의 행(단일 튜플)일 경우는 into절을 이용해 변수에 저장할 수 있지만, 결과가 복수행(복수 튜플)일 경우는 into절로 이를 처리할 수가 없죠

이렇게 여러 복수 행의 결과를 행단위로 처리를 하기 위해서 사용하는 것이 Cursor입니다.

1. 커서의 정의

커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.

오라클에서 수행한 모든 쿼리문은 커서 단위로 처리합니다.

PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가 암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.

SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적은 커서에 정보를 저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.

명시적인 커서는 PL/SQL 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서 프로그램 언어의 구조체 배열과 유사합니다

------------------------------------------------------------------------------------------------------
(오라클에서 CURSOR란 시스템 글로벌 영역의 공유 풀 내에 저장공간을 사용하여 사용자가 SQL 문을 실행시키면 결과값을 저장공간에 가지고 있다가 원하는 시기에 순차적으로 fetch해 처리하여 해당 결과 셋을 프로그래밍적으로

접근할수 있게 도와주는 기능이다.)

[ 커서의 종류 ]

1. 묵시적 커서(Implicit Cursor)

: 오라클에서 자동으로 선언해주는 SQL 커서.(사용자는 생성 유무를 알 수 없다.)

2. 명시적 커서(Explicit cursor)

: 사용자가 선언해서 생성한 후에 사용하는 SQL 커서, 주로 여러개의 행을 처리하고자

할 경우 사용한다.

[ 커서의 속성 ]

1. %Found : 가져올 레코드가 있는 경우 true를 반환

2. %isOpen : 커서가 오픈 상태일 경우 true를 반환

3. %NotFound : 더이상 참조할 레코드가 없을 때 true를 반환

4. %RowCount : 카운터 역할을 한다. 처음 오픈시 0, 패치발생할 때마다 1씩 증가

[ 커서의 처리 단계 ](명시적 커서 기준)

1. 명시적 커서 선언(커서 생성)

EX) Cursor 커서이름

2. 명시적 커서 오픈

EX) Open 커서이름

3. 커서에서 데이터 추출(데이터 행 가져오기)

EX) Fetch 커서이름

4. 커서 종료

EX) Close 커서이름

/*
  [  커서  ]
  : select문을 통해 결과값들이 나올 때 이 결과들은 메모리 공간에 저장하게 되는데
  
    이때, 이 메모리 공간을 "커서"라고 한다.
    
    즉, 
      - 커서란? : 쿼리문에 의해서 반환되는 결과값들을 저장하는 메모리 공간이다.
      - Fetch란 : 커서에서 원하는 결과값을 추출하는 것.
    
    왜사용하는가???!!  커서는 로우를 기반으로 하는 작업이 된다!!! 라는 것입니다.
    저러한 로우를 하나 SELECT해서 여러가지 처리를 한후 어떠어떠한 작업을 한다~~
    이것을 가능하게 하는 것이 바로!!! 커서 입니다.
    물론 SQL구문만을 가지고 어거지로 어찌어찌 한다면~~ 가능할 겁니다.
    하지만 커서를 이용하시면 훨씬 빠르고 간단히 저러한 처리를 가능하게 할 수 있습니다.
    
      
      [ 커서의 종류 ]
      1. 묵시적 커서(Implicit Cursor)
        : 오라클에서 자동으로 선언해주는 SQL 커서.(사용자는 생성 유무를 알 수 없다.)
        
      2. 명시적 커서(Explicit cursor)
        : 사용자가 선언해서 생성한 후에 사용하는 SQL 커서, 주로 여러개의 행을 처리하고자
          할 경우 사용한다.
        
      [ 커서의 속성 ]
      1. %Found : 가져올 레코드가 있는 경우 true를 반환
      2. %isOpen : 커서가 오픈 상태일 경우 true를 반환
      3. %NotFound : 더이상 참조할 레코드가 없을 때 true를 반환
      4. %RowCount : 카운터 역할을 한다. 처음 오픈시 0, 패치발생할 때마다 1씩 증가
      
      [ 커서의 처리 단계 ](명시적 커서 기준)
      1. 명시적 커서 선언(커서 생성)
        EX) Cursor 커서이름
      2. 명시적 커서 오픈
        EX) Open 커서이름
      3. 커서에서 데이터 추출(데이터 행 가져오기)
        EX) Fetch 커서이름
      4. 커서 종료
        EX) Close 커서이름

*/
--EX)
declare
  emp_id number(4);
  emp_name varchar2(10);
  emp_salary number(10);
  
  Cursor cu1 IS --  1. 명시적 커서 선언(커서 생성)
    select employee_id, last_name, salary
    from employees
    where department_id = 50;
begin
  open cu1; -- 2. 명시적 커서 오픈
  
  DBMS_OUTPUT.put_line('사번    이름    급여');
  
  loop
    Fetch cu1 into emp_id, emp_name, emp_salary; -- 3. 커서에서 데이터 추출(데이터 행 가져오기)
    exit when cu1%notFound; -- 더이상 할당할 레코드가 없을 때를 의미
  
    dbms_output.put_line(emp_id||'  '||emp_name||'  '||emp_salary);
  end loop;
  
  close cu1; -- 4. 커서 종료
end;
/

set serveroutput on;

-- 커서와 반복문(FOR문) ]
      --  사용형식)
        /*
        For 레코드 네임 IN 커서이름 loop
          명시적 커서의 OPEN, FETCH가 자동으로 수행
          실행 문장들
        End loop; 루프문을 빠져나갈 때 자동적으로 커서가 종료된다.
        */
        declare
          Cursor emp_cur IS
            select employee_id, last_name
            from employees;
        begin
        
          -- FOR문과 Cursor가 같이사용될 때는
          for emp_rec in emp_cur loop -- 명시적 커서의 OPEN,FETCH가 자동으로 수행
            DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id||' '||emp_rec.last_name);
          end loop; -- 루프문 빠져나갈 때 자동적으로 커서가 종료됨
        
        end;
        /
        
        
        declare
          emp employees%rowtype;
          
          Cursor cur1 IS
            select employee_id, last_name, salary
            from employees;
            
        begin
          for emp IN cur1 loop
            exit when cur1%notfound;
            dbms_output.put_line(emp.employee_id||' '||emp.last_name||' '||emp.salary);
          end loop;
        end;
        /
        
        
        -- 커서 사용하지 않고도 할 수 있는 방법
        declare
        begin
          For emp in (select employee_id, last_name, salary from employees) loop
             dbms_output.put_line(emp.employee_id||' '||emp.last_name||' '||emp.salary);
          end loop;
        end;
        /
        
        
        /* 묵시적 커서 사용 예 */
        declare
          cnt1 number;
          cnt2 number;
        begin
          select count(*) into cnt1
          from employees
          where department_id = 50;
          
          -- rowcount : 커서 오픈시 0값을 가지고 생성되며,
                --      Fetch할 때마다 값이 1씩 증가한다.
                --      묵시적 커서의 경우 속성 사용시에 sql%속성명 으로 접근한다.
                --      ex) sql%notfound, sql%rowcount, sql%found, sql%isopen
          cnt2 := sql%rowcount;
          
          DBMS_OUTPUT.PUT_LINE('cnt1의 값 : '||cnt1); -- 45(count(*)의 값)
          -- 위의 select문에서 한번 결과를 끄집어내서 cnt1에 넣을 때
          -- fetch가 발생해 rowcount값이 1증가 됨(묵시적 커서로 동작하게 된 것)
          DBMS_OUTPUT.PUT_LINE('cnt2의 값 : '||cnt2); -- 1
          
        end;

저작자표시 비영리 (새창열림)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE 서브프로그램의 이해와 프로시저 - (9) (0)	2017.09.18
ORACLE 예외처리 개념, 종류, 사용법 - (8) (0)	2017.09.18
ORACLE 반복문(basic loop, while, for, continue) -(6) (0)	2017.09.14
ORACLE 조건문(IF,CASE) -(5) (0)	2017.09.14
ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4) (0)	2017.09.14

PREV 1 ···10 11 12 13 14 15 16 ···46 NEXT

악덕고용주의 개발 일기

ORACLE 서브프로그램의 이해와 프로시저 - (9)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE 예외처리 개념, 종류, 사용법 - (8)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE 커서(CURSOR) - (7)

'스터디 > DB(ORACLE)' 카테고리의 다른 글

+ Recent posts

티스토리툴바