'프로시저' 태그의 글 목록

create or replace procedure changeDataProc
IS
  rec_data hanTest%rowtype;
  tempExpireData hanTest.expire_time%type;
  tempIsVal hanTest.is_valid%type;
  tempName hanTest.nfcn%type;
  tempNameCnt number;
  Cursor cu1 IS
    select * from hanTest where is_valid = 0 order by nfuid asc, create_time asc, expire_time asc;
BEGIN
   open cu1;
   -- 맨 처음 row가져오기
   fetch cu1 into rec_data;
   -- 맨 처음 튜플은 그대로 삽입
   insert into hantest2 values rec_data; -- 
   tempExpireData := rec_data.expire_time; 
   tempName := rec_data.nfcn;
   
   -- 두번째 튜플부터 반복문을 돌림
   loop 
    fetch cu1 into rec_data;
    
    if ( tempName = rec_data.nfcn ) then
      rec_data.create_time := tempExpireData;
      tempExpireData := rec_data.expire_time;
      insert into hantest2 values rec_data;
      tempName := rec_data.nfcn;
    else 
      tempName := rec_data.nfcn;
      insert into hantest2 values rec_data;
      tempExpireData := rec_data.expire_time;
    end if;
    
    exit when cu1%notfound;
   end loop;
  
   close cu1;
  
END;
/
truncate table hanTest2;
exec changeDataProc;
delete from hantest2 where nfcn='이원섭' and nfuid='이원섭' and create_time='20010119 071939';
select * from hantest2;

create or replace procedure insertOneData
IS
  recOne hantest%rowtype;
BEGIN
  for temp in (select * from hantest where is_valid=1) loop
    insert into hanTest2 values temp;
  end loop;
END;
/

execute insertOneData;
select * from hantest2 where is_valid = 1;
select count(*) from hantest2 where is_valid = 0;
select count(*) from hantest2 where is_valid=1;
select count(*) from hantest2;

저작자표시 비영리

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

ORACLE 테이블스페이스(Table Space) -(1) (0)	2017.09.21
ORACLE 트리거(trigger) (0)	2017.09.21
ORACLE 패키지의 이해 (0)	2017.09.20
ORACLE 서브프로그램 함수(Function) (0)	2017.09.19
ORACLE 서브프로그램의 이해와 프로시저 - (9) (0)	2017.09.18

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

: 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) 데이터타입

is

변수 선언부;

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

/*
  [ PL/SQL 변수 ]
  - 변수의 생성 규칙
    1. 반드시 문자로 시작해야 한다.
    2. 문자나, 숫자, 특수문자를 포함할 수 있다.
    3. 변수명은 30Byte 이하여야 한다.
    4. 예약어(키워드)를 사용하면 안된다.
    
  - 변수의 선언은 선언부(Delcare 블럭)에서 선언되어야 하고,
    값으로 초기화가 가능하다.
  - 실행부(Begin~End)에서 실행될 경우 값이 할당 된다.
  - 서브프로그램의 파라미터로 전달되기도 하며, 서브프로그램의
    출력 결과를 저장하기도 한다.
    
  - 선언의 예>
  emp_no number(6,3) : 숫자를 저장하는 변수로 총 6자리며 소수점 밑 3자리
  
  emp_name varchar2(10) : 문자를 저장하는 변수로 총 10Byte 저장가능 변수
  
  emp_date date : 날자를 저장하는 변수
  
  - 데이터 타입>
    - char : 고정길이 문자 타입, 기본최소값 1, 최대 32,767Byte를 저장 가능
    - varchar2 : 가변길이 문자 타입, 기본값은 없다. 
                 최대 32,767Byte 저장 가능.
    - number(전체자리수,소수점이하 자리수) : 숫자를 저장 
                                         전체자리수 범위는 1~38까지 가능하고,
                                         소수점 자리수 범위는 -84~127까지 가능                                     
    - binary_double : 부동 소수점 수를 저장하는 타입, 9Byte 필요
    - date : 날짜 및 시간을 나타내는 타입, 초단위로 저장,
             날짜의 범위는 4712B.C ~ 9999 A.D
    - timestamp : date 타입을 확장, 연도, 월, 일, 시, 분, 초 및 소수로
                  표시되는 초단위를 저장.
                  
    ** 참조 변수 **
    테이블명.필드명%Type
    
    empNo employees.employee_id%TYPE // 현재 employees테이블의 employee_id의 타입을 그대로 가져오겟다.
      : employees 테이블의 employee_id와 동일한 데이터 타입으로 선언한다는 의미.
      
    emp_name employees.frst_name%TYPE
      : employees 테이블의 first_name과 동일한 데이터 타입으로 선언
      
    empRow employees%ROWTYPE
      : employees 테이블의 모든 컬럼을 한꺼번에 저장하기 위한 변수로 선언
*/

-- EX)
create table employees1
as
select employee_id, salary, department_id from employees;

set serveroutput on;

DECLARE
  empNo employees.employee_id%TYPE;
  empSalary employees.salary%TYPE;
BEGIN
  select employee_id, salary into empNo, empSalary
  from employees1
  where department_id = 10;
  
  DBMS_OUTPUT.put_line(empNo||' '||empSalary);
END;
/

DECLARE
  emp_row employees1%ROWTYPE; -- employees1 테이블의 모든 컬럼 타입을 의미함
BEGIN
  select * into emp_row
  from employees1
  where employee_id = 100;
  
  DBMS_OUTPUT.put_line(emp_row.employee_id||' '||emp_row.salary||' '||emp_row.department_id);
END;
/

create table row_test(
  no number,
  name varchar2(20),
  hdate date
);

create table row_test2
as
select * from row_test;

insert into row_test values(1,'아무개',sysdate);
insert into row_test values(2,'홍길동',sysdate);
insert into row_test values(3,'고길동',sysdate);

select * from row_test;

commit;

DECLARE
  c_rec row_test%ROWTYPE;
BEGIN
  select * into c_rec
  from row_test
  where no = 3;
  
  insert into row_test2 values c_rec;
END;
/

select * from row_test2;

저작자표시 비영리

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

ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4) (0)	2017.09.14
ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3) (0)	2017.09.13
ORACLE PL/SQL 개념 - (1) (0)	2017.09.11
ORACLE 시퀀스(sequence) (0)	2017.09.08
ORACLE 뷰(View)와 Inline 뷰 (0)	2017.09.08

[ ORACLE PL/SQL 개념 - (1) ]

[ PL/SQL(Procedural Language/SQL) ]

: 오라클에서 제공하는 프로그래밍 언어

: 일반 프로그래밍 언어적인 요소를 다 가지고 있고,

데이터베이스 업무를 처리하기 위한 최적화된 언어

** [ 프로시저(PL)의 기본 구조 ] **

- 선언부(Declare) : 모든 변수나 상수를 선언하는 부분

- 실행부(Executable) : BEGIN ~ END // 실재 로직이 수행되는 부분

ex) 제어문, 반복문, 함수정의 등의 로직을 기술하는 부분...

- 예외처리부(Exception) : 실행도중에 에러 발생시 해결하기위한 명령들을 기술하는 부분

(생략가능)

** Declare, begin, exception 키워드들은 ';' 을 붙이지 않는다. **

** 나머지 문장들은 ';'으로 처리하여 영역의 긑을 표시한다.

- 익명 블록(Anonymous PL/SQL)

: 주로 일회성으로 사용할 경우 많이 사용된다.

- 저장 블록(Stored PL/SQL)

: 서버에 저장해 놓고 주기적으로 반복해서 사용할 경우 사용된다.

EX)

/*
  [ PL/SQL(Procedural Language/SQL) ]
  : 오라클에서 제공하는 프로그래밍 언어
  : 일반 프로그래밍 언어적인 요소를 다 가지고 있고, 
    데이터베이스 업무를 처리하기 위한 최적화된 언어
    
    
    
   ** [ 프로시저(PL)의 기본 구조 ] **
   
     - 선언부(Declare) : 모든 변수나 상수를 선언하는 부분
     - 실행부(Executable) : BEGIN ~ END // 실재 로직이 수행되는 부분 
          ex) 제어문, 반복문, 함수정의 등의 로직을 기술하는 부분...
     - 예외처리부(Exception) : 실행도중에 에러 발생시 해결하기위한 명령들을 기술하는 부분
       (생략가능)
       
      ** Declare, begin, exception 키워드들은 ';' 을 붙이지 않는다. **
      ** 나머지 문장들은 ';'으로 처리하여 영역의 긑을 표시한다.
      
      
     - 익명 블록(Anonymous PL/SQL)
          : 주로 일회성으로 사용할 경우 많이 사용된다.
          
     - 저장 블록(Stored PL/SQL)
          : 서버에 저장해 놓고 주기적으로 반복해서 사용할 경우 사용된다.
*/
-- EX)

set SERVEROUTPUT OFF;
set SERVEROUTPUT ON; -- dbms_output.put_line메서드를 이용해 출력을 가능케해주는 세팅

-- 익명 블럭(Anonymous PL/SQL) EX
DECLARE --선언부
  cnt integer;
BEGIN --실행부
  cnt := cnt+1; /* 할당 '=' 이 아니라 ':=' */
  -- null과 연산하면 결과는 null
  if cnt is null then
    dbms_output.put_line('결과 : cnt는 NULL입니다.'); -- 괄호 안 내용을 화면에 출력하는 메서드
  end if;
END;

/ -- 작성한 프로시저 실행

/*
  PL/SQL 문장 안에 SQL 문장이 포함될 수 있는데 이 SQL 문장은 SQL 실행자에 의해 실행된다.
  PL/SQL 문장은 프로시저 실행자가 처리->(PL/SQL엔진)이 수행한다.
  
  SQL실행자가 SQL문을 실행해 프로시저 실행자에게 넘겨준다.(돌려준다)
  그러면 그걸 PL/SQL엔진이 처리하게 된다.
*/

DECLARE
  empNo number(20);
  empName varchar2(10);
BEGIN
  select employee_id, first_name into empNo, empName -- empNo와 empName에 각 결과 필드값을 넣겠단 의미(into)
  from Employees
  where employee_id = 124;

DBMS_OUTPUT.put_line(empNo||' '||empName); -- 각 변수 값을 화면에 출력
END;

/

저작자표시 비영리

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

ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3) (0)	2017.09.13
ORACLE PL/SQL 변수 선언 및 데이터 타입 - (2) (0)	2017.09.12
ORACLE 시퀀스(sequence) (0)	2017.09.08
ORACLE 뷰(View)와 Inline 뷰 (0)	2017.09.08
ORACLE 무결성 제약조건 (0)	2017.09.07

악덕고용주의 개발 일기

프로시저

ORACLE 중간 연습... 프로시저를 활용한 데이터 조작

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

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

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

ORACLE PL/SQL 변수 선언 및 데이터 타입 - (2)

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

ORACLE PL/SQL 개념 - (1)

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

+ Recent posts

티스토리툴바