'스터디' 카테고리의 글 목록 (2 Page)

스터디

ORACLE 패키지의 이해 2017.09.20
ORACLE 서브프로그램 함수(Function) 2017.09.19
ORACLE 서브프로그램의 이해와 프로시저 - (9) 2017.09.18
ORACLE 예외처리 개념, 종류, 사용법 - (8) 2017.09.18
ORACLE 커서(CURSOR) - (7) 2017.09.15
ORACLE 반복문(basic loop, while, for, continue) -(6) 2017.09.14
ORACLE 조건문(IF,CASE) -(5) 2017.09.14
ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4) 2017.09.14
ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3) 2017.09.13
ORACLE PL/SQL 변수 선언 및 데이터 타입 - (2) 2017.09.12
ORACLE PL/SQL 개념 - (1) 2017.09.11
ORACLE 시퀀스(sequence) 2017.09.08
ORACLE 뷰(View)와 Inline 뷰 2017.09.08
ORACLE 무결성 제약조건 2017.09.07
ORACLE DML( insert, update, delete, select ) 2017.09.06
ORACLE DDL( create, drop, alter, truncate ) 2017.09.06
ORACLE 서브쿼리와 ANY,ALL,IN에 대하여... 2017.09.06 3
ORACLE JOIN(Inner join,Outer Join,Self Join) 2017.09.05
ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP 2017.09.05
ORACLE 날짜함수, 변환함수, decode(), case() 2017.09.04
ORACLE 문자열 함수 2017.09.04
ORACLE 집계함수, 숫자함수 2017.09.04
이채남저 오라클실습 자료(개인 보관용) 2017.08.31 1
JSP MVC패턴을 이용한 게시판 만들기 1(개요) 2017.08.28
ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문 2017.08.22 1
JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여... 2017.08.20
JSP JSTL(Java Standard Tag Liarary) 2017.08.18
JSP EL(Expression Language) 표현언어 2017.08.16
JSP 파일업로드 2017.08.16 2
JSP 커넥션 풀(Connection Pool) 2017.08.16

ORACLE 패키지의 이해

2017. 9. 20. 11:34

[ 패키지 ]

: 연관성이 있는 함수나 프로시저를 그룹으로 모아놓은 개념.

[ 패키지 구성 ]

: 선언부 + 몸체부

**참고로 패키지 선언부에 선언되지 않아도 패키지 몸체부에서

사용할 수 있다... BUT 권장사항이 아니다.

[ 구문 형식 ]

- 선언부 형식

create (or replace) package 패키지명

패키지 내 전역 변수 선언;

procedure 프로시저1(매개변수1,...);

procedure 프로시저2(매개변수1,...);

function 함수1(매개변수1,..2)return 타입;

...

end;

- 몸체부 형식

create (or replace) package body 패키지명

procedure 프로시저 이름

subprogram bodis : 실제 작동하게될 서브프로그램(프로시저, 함수)

end;

*패키지 실행 : 패키지는 여러 환경에서 호출되어 실행가능하다.

패키지에 대한 실행권한을 가진 사용자만 실행시킬 수 있다.

exec 패키지명.프로시저or함수명;

select 패키지명.함수명(매개변수) from ~~

*패키지 삭제 : 선언부와 몸체부를 모두 삭제할 수 있다. 또는 몸체만 삭제할 수도 있다.

drop package 패키지명; -- 둘 다 삭제할 경우

drop package body 패키지명; -- 몸체만 삭제할 경우

- 패키지 내에 전역 변수를 선언하고 해당 변수를 사용할 경우 초기화 작업이 필요한 경우가 있다.

이런 경우를 지원하기 위해

패키지.프로시저 or 함수를 수행할 때 마다 한번 무조건 실행하게 해 초기화 작업등을 할 수 있도록 지원하는 것이 있는데

이를 ONE_TIME_ONLY_PACKAGE 라고 한다.

방식은, 단순히 패키지 바디의 맨 아래 부분에 BEGIN을 기술하고 초기화 작업등을 해주면 된다.

set serveroutput on;
-- 선언부
-- emp_sum과 emp_avg 프로시저로 구성된 패키지
create or replace package emp_proc
IS
  entireVariable varchar2; -- 패키지 전역 변수
  procedure emp_sum;
  procedure emp_avg;
  function emp_cnt(emp_id employees.employee_id%type) return number;
end;
/
--몸체부
create or replace package body emp_proc 
IS
  -- 프로시저1(위에 선언한 emp_sum 구현부)
  procedure emp_sum
  IS
    cursor emp_tot_sum is
      select count(*), sum(nvl(salary,0))
      from employees;
    tot_num number;
    tot_sum number;
  BEGIN
    open emp_tot_sum;
    fetch emp_tot_sum into tot_num, tot_sum;
    dbms_output.put_line('전체인원수 : '||tot_num||', 급여합계 : '||tot_sum);
    close emp_tot_sum;
  END emp_sum; -- 각 프로시저 끝(emp_sum프로시저의 끝 부분)

-- emp_avg 프로시저
  procedure emp_avg
  IS
    cursor emp_tot_avg is
      select count(*), avg(nvl(salary,0))
      from employees;
    tot_num number;
    tot_avg number;
  BEGIN
    open emp_tot_avg;
    fetch emp_tot_avg into tot_num, tot_avg;
    dbms_output.put_line('전체인원수 : '||tot_num||', 평균급여 : '||tot_avg);
    close emp_tot_avg;
  END emp_avg; -- emp_avg 프로시저 끝
  
  function emp_cnt(emp_id employees.employee_id%type)
  return number
  IS
    cnt number;
  BEGIN
    select count(*) into cnt from employees where employee_id = emp_id;
    return cnt;
  END emp_cnt;
  
  BEGIN -- one time only package
     entireVariable := '초기값 설정';

end; -- body의 끝
/

-- 패키지 실행 방법
      --  : exec 패키지명.프로시저이름
exec emp_proc.emp_avg;
execute emp_proc.emp_sum;
select emp_proc.emp_cnt(employee_id) from employees;

저작자표시 비영리

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

ORACLE 트리거(trigger) (0)	2017.09.21
ORACLE 중간 연습... 프로시저를 활용한 데이터 조작 (0)	2017.09.20
ORACLE 서브프로그램 함수(Function) (0)	2017.09.19
ORACLE 서브프로그램의 이해와 프로시저 - (9) (0)	2017.09.18
ORACLE 예외처리 개념, 종류, 사용법 - (8) (0)	2017.09.18

ORACLE 서브프로그램 함수(Function)

2017. 9. 19. 17:55

[ 서브프로그램의 함수 사용 ]

서브프로그램

- 프로시저

- 함수

[ 함수와 프로시저의 차이? ]

- 프로시저는 in, out, in out모드를 사용해서 값을 반환하기도 하고,

반환하지 않고 종료 할 수도 있다.

- 함수는 어떤 작업을 수행한 후에, 결과를 반환한다.

[ 함수의 구문 형식 ]

create (or replace) function 함수명

파라미터1 파라미터타입,

파라미터2 파라미터타입,...

return datatype

PL/SQL 블럭;

EX)

--EX)
create or replace function dept_max_sal
  (dept_id employees.department_id%type)
return number
IS
  max_sal employees.salary%type;
begin
  select max(salary) into max_sal
  from employees
  where department_id = dept_id;
  
  -- number 타입의 max_sal을 리턴시킴
  return max_sal; 
end;
/
  
select dept_max_sal(50) from dual;

create or replace function cnt_number
  (cnt number)
return number
IS
  total_cnt number;
begin
  select count(*) into total_cnt
  from employees
  where department_id = cnt;
  
  return total_cnt;
end;
/

select distinct cnt_number(50) from employees;
-- 부서별 인원수
select distinct department_id, cnt_number(department_id) from employees;

declare

begin
   dbms_output.put_line(cnt_number(50));
end;

create or replace function avg_sal
  (dept_id employees.department_id%type)
return number
IS
  avg_salary number;
begin
  select round(avg(salary),2) into avg_salary
  from employees
  where department_id = dept_id;
  
  return avg_salary;
end;
/

select department_id, avg_sal(department_id) 부서별평균급여
from employees
group by department_id;

create or replace function emp_dept_name
  (emp_id employees.employee_id%type)
return varchar2
IS
  dept_name departments.department_name%type;
BEGIN
  select department_name into dept_name
  from departments
  where department_id = (select department_id from employees 
                         where employee_id = emp_id);
  
  return dept_name;
END;
/

select distinct employee_id, emp_dept_name(employee_id) from employees;

저작자표시 비영리

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

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

ORACLE 패키지의 이해  (0) 2017.09.20

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

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

ORACLE 커서(CURSOR) - (7)  (0) 2017.09.15

ORACLE 중간 연습... 프로시저를 활용한 데이터 조작 (0)	2017.09.20
ORACLE 패키지의 이해 (0)	2017.09.20
ORACLE 서브프로그램의 이해와 프로시저 - (9) (0)	2017.09.18
ORACLE 예외처리 개념, 종류, 사용법 - (8) (0)	2017.09.18
ORACLE 커서(CURSOR) - (7) (0)	2017.09.15

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) 데이터타입
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 타입을 통한 결과값 받기 예제 ]
-- 프로시저 정의 create procedure sumProcedure(numVal in number, resultVal out number) AS BEGIN resultVal := numVal + 10; END; / -- 외부에서 변수를 하나 선언 variable numV number; -- 함수를 수행하되 외부에서 선언해논 변수에 값이 저장될 수 있도록 같이 전달해 준다. 이때 외부 선언한 -- 변수를 사용할 때는 : 를 붙여주어야 함 exec sumProcedure(8, :numV); print numV; -- 18 출력

저작자표시 비영리

'스터디 > 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

ORACLE 반복문(basic loop, while, for, continue) -(6)

2017. 9. 14. 14:26

[    ORACLE 반복문(basic loop, while, for, continue) ]

/* [ 반복문 ] - basic loop문 - while문 - for문 */ -- 1. basic loop문(조건을 나중에 검사) : DO WHILE문과 유사 -- 형식 /* loop pl/sql문장; exit when(조건); end loop; */ -- 1~10까지 출력하기 declare num number := 1; begin loop dbms_output.put_line(num); num := num + 1; exit when (num > 10); end loop; end; / -- 2. while문(조건을 먼저 검사) /* 형식) while 조건 loop 실행문장; end loop */ declare num number := 1; begin while (num <= 10) loop DBMS_OUTPUT.PUT_LINE(num); num := num + 1; end loop; end; / declare num number := 10; begin loop DBMS_OUTPUT.PUT_LINE(num); num := num - 1; exit when num = -1; end loop; end; / declare num number := 10; begin while (num >= 0) loop DBMS_OUTPUT.PUT_LINE(num); num := num - 1; end loop; end; / -- 3. FOR문 : 반복횟수를 지정할 수 있다. /* 형식) java의 FOR EACH문과 유사! IN뒤에 나온 것이 순차적으로 i에 들어가는 개념 FOR i IN start..end loop 실행문장 end loop; */ declare begin FOR i IN 1..10 loop DBMS_OUTPUT.PUT_LINE(i); end loop; end; / -- for문 역순 -- reverse를 붙여주자. begin FOR n IN reverse 0..10 loop DBMS_OUTPUT.PUT_LINE(n); end loop; end; / /*사원테이블에서 사원id를 입력받아서 사원이름의 문자길이만큼 #을 찍는 PL/SQL문을 작성해보자.(employees테이블을 이용) */ declare emp_id employees.employee_id%type := &emp_no; -- 입력받음 emp_name employees.last_name%type; emp_name_length number(20); v_char varchar2(30); begin select last_name, length(last_name) into emp_name, emp_name_length from employees where employee_id = emp_id; for i in 1..emp_name_length loop v_char := v_char ||'#'; end loop; DBMS_OUTPUT.PUT_LINE(v_char); end; / -- continue 보조제어문(*11g부터 추가된 기능임*) ] declare tot number := 0; begin for i in 1..10 loop tot := tot + 1; DBMS_OUTPUT.put_line('tot : ' || tot); continue when i > 5; -- 해당 조건을 만족하면 다음 차수로 바로 가게 된다. -- 아래를 수행하지 않고 다음번 차수로 넘어가게 됨. tot := tot + i; DBMS_OUTPUT.PUT_LINE('tot2 :' || tot); end loop; end; /

저작자표시 비영리

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

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

ORACLE 커서(CURSOR) - (7)  (0) 2017.09.15

ORACLE 조건문(IF,CASE) -(5)  (0) 2017.09.14

ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)  (0) 2017.09.14

ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3)  (0) 2017.09.13

ORACLE 조건문(IF,CASE) -(5)

2017. 9. 14. 11:52

[    ORACLE 조건문(IF,CASE) ]

/* [ 제어문 ] : 조건문, 반복문 - 조건문 : if문, case문(switch문과 유사) - 반복문 : basic loop문, while문(반복횟수를 정하지 않을 경우) : for문(반복횟수 지정할 경우) // if문 : if ~ end if EX) if(조건) then 실행명령; elsif(조건) then 실행명령; else 실행명령; end if; // case문 EX) case 변수명 when 값1 then 실행명령; when 값2 then 실행명령; ... end; */ set serveroutput on; -- if문 Ex) declare emp_id employees.employee_id%type; emp_name employees.last_name%type; emp_dept employees.department_id%type; dept_name varchar2(20) := null; begin select employee_id, last_name, department_id into emp_id,emp_name,emp_dept from employees where employee_id = 124; if(emp_dept = 50) then --if문 시작 dept_name := 'Shipping'; end if; if(emp_dept = 60) then dept_name := 'IT'; end if; if(emp_dept = 70) then dept_name := 'Public Relation'; end if; DBMS_OUTPUT.PUT_LINE(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); end; / declare emp_id employees.employee_id%type; emp_name employees.last_name%type; emp_dept employees.department_id%type; dept_name varchar2(20) := null; begin select employee_id, last_name, department_id into emp_id,emp_name,emp_dept from employees where employee_id = 103; if(emp_dept = 50) then dept_name := 'Shipping'; elsif(emp_dept = 60) then -- elseif가 아니라 elsif임을 주의... dept_name := 'IT'; elsif(emp_dept = 70) then dept_name := 'Public Relation'; ELSE dept_name := 'Other'; end if; DBMS_OUTPUT.PUT_LINE(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); end; / declare emp_id employees.employee_id%type; emp_name employees.last_name%type; emp_comm employees.commission_pct%type := null; begin select employee_id, last_name, commission_pct into emp_id, emp_name, emp_comm from employees where employee_id = 130; if (emp_comm > 0) then dbms_output.put_line(emp_id||' 의 보너스는 '||emp_comm); else DBMS_OUTPUT.PUT_LINE(emp_id||'의 보너스는 없습니다.'); end if; end; / -- case문 EX) declare emp_id employees.employee_id%type; emp_name employees.last_name%type; emp_dept employees.department_id%type; dept_name varchar2(20) := null; begin select employee_id, last_name, department_id into emp_id, emp_name, emp_dept from employees where employee_id = &empno; --치환변수 이용해 사용자로부터 입력받음 dept_name := case emp_dept when 50 then 'Shipping' when 60 then 'IT' when 70 then 'Public Relation' when 80 then 'Sales' end; DBMS_OUTPUT.PUT_LINE(dept_name); end; /

저작자표시 비영리

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

ORACLE 커서(CURSOR) - (7)  (0) 2017.09.15

ORACLE 반복문(basic loop, while, for, continue) -(6)  (0) 2017.09.14

ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)  (0) 2017.09.14

ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3)  (0) 2017.09.13

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

ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)

2017. 9. 14. 09:52

[    ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4) ]

쿼리의 일부분 예를 들어 WHERE 절의 내용만 다른 쿼리를 실행해야 하는 경우가 종종 생길 것이다. 이러한 경우에 거의 비슷한 두번의 쿼리를 실행하는 비효율성을 해소하는 방법이 바로 바인드 변수의 사용이다.
바인드 변수는 입력 내용을 넣고 SQL로부터 출력 내용을 받아내는 방법으로, " 이 부분에 들어갈 정확한 값은 이후에 알려줄테니, 일단 내가 값을 넣었을 때 어떻게 실행할 것인지에 대해서 계획만 세워둬라 "는 명령을 오라클에 내리는 것이다.

select * into temp from row_test where no = :bind_no; 이 부분이 있을 때 :가 붙은 변수가 바인드 변수인대, 실행한 뒤에 바인드 변수에 입력할 값을 넣게 된다.
그에 따라 no = 1, no = 2 등 실행할 때마다 다양한 결과를 낼 수가 있게 된다.

/* [ Table Type 변수(=컬렉션) ] 컬렉션 : 일반 프로그래밍 언어에서 사용하는 배열 타입을 pl/SQL에서는 "컬렉션"이라 한다. - 종류 1. 연관배열(associative array or index-by table) : (KEY, VALUE) 쌍으로 저장되는 배열 - 키값을 통해 값에 접근한다. . key의 데이터 유형 - 숫자 : binary_integer, pls_integer 위 두가지 데이터 타입은 number보다 작은 저장 영역이 필요, 산술연산의 경우 number보다 빠르다. - 문자 : varchar2 . 값(value) 유형 - 일반 데이터 타입, 레코드 데이터 타입도 값이 될 수 있다. -> 레코드 타입일 경우 여러개의 값을 가질 수 있다. 2. varry(variable array) : 고정 길이를 가진 배열(일반 프로그래밍에서 사용하는 배열과 동일) : 인덱스가 "0"부터 시작한다. ex) 3번째 v[3] 3. 중첩테이블(nested table) : varry와 흡사한 구조의 배열 : (배열의 크기를 명시하지 않음)배열의 최대값이 정해지지 않고 동적으로 자동으로 최대값이 증가됨 - [ Table Type(컬렉션)의 선언 형식 ] 1. 정의 TYPE 타입명 IS TABLE OF employees.first_name%type INDEX BY BINARY_INTEGER -- binary integer로 인덱스를 사용한단 의미 2. 선언(메모리 공간이 잡히는 부분) 식별자 타입명; */ set SERVEROUTPUT ON; declare tname varchar2(20); -- 정의 TYPE t_emp_name IS TABLE OF employees.last_name%type INDEX BY BINARY_INTEGER; -- 선언 v_name t_emp_name; begin select last_name into tname from employees where employee_id = 100; -- 인덱스를 통해 접근 v_name(0) := tname; DBMS_OUTPUT.PUT_LINE(v_name(0)); end; / declare TYPE tbl_type IS TABLE OF employees.last_name%type INDEX BY BINARY_INTEGER; vtbl_type tbl_type; a binary_integer := 0; -- 변수 a값을 0으로 초기화 begin for emp_name in(select last_name from employees) loop a := a+1; vtbl_type(a) := emp_name.last_name; end loop; for i in 1..a loop DBMS_OUTPUT.PUT_LINE(vtbl_type(i)); end loop; end; / /* [ 바인드 변수(비 PL/SQL 변수) ] : 호스트 환경에서 생성되어 데이터를 저장하기 때문에 호스트 변수라고 한다. 쿼리의 일부분 예를 들어 WHERE 절의 내용만 다른 쿼리를 실행해야 하는 경우가 종종 생길 것이다. 이러한 경우에 거의 비슷한 두번의 쿼리를 실행하는 비효율성을 해소하는 방법이 바로 바인드 변수의 사용이다. 바인드 변수는 입력 내용을 넣고 SQL로부터 출력 내용을 받아내는 방법으로, " 이 부분에 들어갈 정확한 값은 이후에 알려줄테니, 일단 내가 값을 넣었을 때 어떻게 실행할 것인지에 대해서 계획만 세워둬라 "는 명령을 오라클에 내리는 것이다. select * into temp from row_test where no = :bind_no; 이 부분이 있을 때 :가 붙은 변수가 바인드 변수인대, 실행한 뒤에 바인드 변수에 입력할 값을 넣게 된다. 그에 따라 no = 1, no = 2 등 실행할 때마다 다양한 결과를 낼 수가 있게 된다. - 키워드 VARIABLE를 이용하며, SQL문이나 PL/SQL블록에서도 사용가능 - PL/SQL블록이 실행된 후에도 액세스가 가능하다. - print명령을 이용하여 출력가능 - :을 붙여 이용한다. */ set autoprint on; -- print를 하지 않아도 자동으로 출력하도록 세팅하는 부분(default는 false로 되어있음) declare temp row_test%ROWTYPE; begin select * into temp from row_test where no = :bind_no; insert into row_test3 values temp; end; select * from row_test3; -- no에 1을 입력시 그에 해당되는 결과가 2를 입력하면 그에 해당하는 결과가 나오게 된다. begin select (salary*12+nvl(commission_pct,0)) into :vsal -- 결과값을 바인드 변수에 넣는다. from employees where employee_id = 100; end; / -- print를 이용해서 PL/SQL블럭 밖에서도 출력할 수 있다. print vsal; create table row_test3 as select * from row_test; truncate table row_test3; select * from row_test3; declare type arrRecType IS TABLE OF row_test%ROWTYPE INDEX BY binary_integer; TestValue arrRecType; a binary_integer := 0; begin for temp_rec in (select * from row_test) loop a := a+1; TestValue(a) := temp_rec; end loop; for i in 1..a loop insert into row_test3 values TestValue(i); end loop; end; / select * from row_test3;

저작자표시 비영리

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

ORACLE 반복문(basic loop, while, for, continue) -(6)  (0) 2017.09.14

ORACLE 조건문(IF,CASE) -(5)  (0) 2017.09.14

ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3)  (0) 2017.09.13

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

ORACLE PL/SQL 개념 - (1)  (0) 2017.09.11

ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3)

2017. 9. 13. 09:44

[    ORACLE rowType 변수 및 복합변수 활용 예 ]

--[ rowType 변수 및 복합변수 활용의 예 ] -- : rowType 변수를 활용한 데이터의 변경 DECLARE c_rec row_test%ROWTYPE; BEGIN select * into c_rec from row_test where no = 3; -- 대입연산자는 := 를 사용해야 한다.(주의!) c_rec.name := '강길동'; -- row(행 전체)를 c_rec로 바꾸는 방법 update row_test2 set row = c_rec where no = 3; END; / select * from row_test2; -- 사용자로 부터 두개의 숫자를 입력받아서 합을 구하는 예 -- 치환연산자 & 사용) set SERVEROUTPUT ON; declare no1 number := &no1; -- 사용자로부터 입력을 받아 값을 할당 no2 number := &no2; sumV number; begin sumV := no1 + no2; DBMS_OUTPUT.put_line('첫번째 수'||no1||'두번째 수'||no2||'합'||sumV); end; / -- 복합변수 ) -- 직접 복합 타입을 지정할 수도 있다. -- record Type 변수 지정 방법 --1. type 타입명 is record(); --2. 식별자 타입명 declare type emp_rec is record (emp_id employees.employee_id%type, emp_name employees.first_name%type, emp_job employees.job_id%type ); rec1 emp_rec; -- 위에서 정의한 타입을 사용해 변수 선언 begin select employee_id, first_name, job_id into rec1 from employees where department_id = 10; DBMS_OUTPUT.put_line('사번 이름 업무ID'); DBMS_OUTPUT.PUT_LINE(rec1.emp_id||' '||rec1.emp_name||' '||rec1.emp_job); end; / declare type emp_rec2 is record ( emp_id employees.employee_id%type, emp_name employees.last_name%type, emp_email employees.email%type, emp_salary employees.salary%type ); rec2 emp_rec2; -- 사원번호는 사용자로부터 입력받도록 vemp_id employees.employee_id%type := '&사번'; begin select employee_id, last_name, NVL(email,'없음'), salary into rec2 from employees where employee_id = vemp_id; -- 사원번호가 사용자로부터 입력받은 것과 같은 정보 DBMS_OUTPUT.put_line('사번:'||rec2.emp_id); DBMS_OUTPUT.put_line('이름:'||rec2.emp_name); DBMS_OUTPUT.put_line('이메일;'||rec2.emp_email); DBMS_OUTPUT.put_line('월급:'||rec2.emp_salary); end; / -- 지금까지 공부한 PL/SQL 부분 연습하기 -- declare -- 1.일반 변수 선언법 no number(10); strValue varchar2(20); -- 2.기존 테이블의 특정 필드의 타입으로 선언하는 방법 emp_id employees.employee_id%TYPE; emp_name employees.first_name%TYPE; -- employees테이블의 first_name 필드와 같은 타입으로 선언 -- 3.특정 기존 테이블과 동일한 복합 타입으로 지정하는 방법 emp_rec employees%ROWTYPE; dep_rec departments%ROWTYPE; -- 4.사용자에게 입력을 받아 선언한 변수에 데이터를 넣는 방법 input_data row_test.no%TYPE := '&no번호'; -- 5.사용자 지정 복합 타입으로 변수를 선언하는 방법 type myType is record ( my_no row_test.no%TYPE, my_name row_test.name%type, my_hdate row_test.hdate%type ); myDefVal myType; begin select no, name, hdate into myDefVal from row_test where no = 3; myDefVal.my_name := '수정합니다.'; update row_test set row = myDefVal where no = input_data; -- 사용자가 입력한 no번호에 해당하는 필드 값을 수정하도록... end; / select * from row_test;

저작자표시 비영리

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

ORACLE 조건문(IF,CASE) -(5)  (0) 2017.09.14

ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4)  (0) 2017.09.14

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

ORACLE PL/SQL 개념 - (1)  (0) 2017.09.11

ORACLE 시퀀스(sequence)  (0) 2017.09.08

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

2017. 9. 12. 17:04

/* [ 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)

2017. 9. 11. 17:36

[    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 시퀀스(sequence)

2017. 9. 8. 13:59

--[ 시퀀스(sequence) ] -- : mysql에서 auto_increment 를 대체하는 기능으로 값들이 자동으로 순차적으로 증가하여야하는 -- 데이터 등에 주로 사용된다. -- 연속적인 번호를 만들어 주는 기능 --사용Ex) -- 구문 형식 ] /* create sequence (시퀀스 이름) increment by n -> 시퀀스 번호의 증가값을 설정한다.(default 1) start with n -> 시작값 설정(default 1) maxvalue n | nomaxvalue <- 시퀀스 최대값 설정 minvalue n | nominvalue <- 시퀀스 최소값 설정 : cycle 옵션일 경우 시작값 cycle | nocycle <- 최대값을 지나면 최소값으로 순환되는 옵션 설정 cache n | nocache <- 시퀀스의 속도를 개선하기위해 캐싱 여부 지정 */ -- [ 시퀀스 생성 ] : 제품번호를 생성하는 시퀀스 만들기 create SEQUENCE seq_serial_no INCREMENT BY 1 START WITH 100 MAXVALUE 110 MINVALUE 99 CYCLE cache 2; create table good( good_no number(3), good_name varchar2(20) ); -- 시퀀스를 이용해 삽입 : 시퀀스명.nextval : 다음 시퀀스 번호를 가져옴 -- : 시퀀스명.currval : 현재 시퀀스 번호를 가져옴 insert into good values(seq_serial_no.nextval, '제품1'); select * from good; select seq_serial_no.CURRVAL from dual; insert into good values(seq_serial_no.nextval, '제품2'); insert into good values(seq_serial_no.currval, '제품3'); -- 시퀀스 삭제 -- drop sequence 시퀀스명 drop sequence seq_serial_no; create sequence seq_serial_no2 INCREMENT BY 1 start with 100 maxvalue 105 cache 2; insert into good values(seq_serial_no2.nextval,'제품4'); select * from good;

저작자표시 비영리

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

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

ORACLE PL/SQL 개념 - (1)  (0) 2017.09.11

ORACLE 뷰(View)와 Inline 뷰  (0) 2017.09.08

ORACLE 무결성 제약조건  (0) 2017.09.07

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06

ORACLE 뷰(View)와 Inline 뷰

2017. 9. 8. 11:38

[    ORACLE 뷰(View) & 인라인 뷰 ]

-- 뷰(VIEW)란? -- : 뷰는 Table과 유사하지만 테이블과 달리 물리적인 공간이 필요하지 않은 가상의 테이블이다. -- : 테이블과 마찬가지로 select,insert,update,delete 명령이 가능하다. -- 사용 이유) -- 1. [보안관리를 위한 뷰] -- : 보안등급에 맞추어 컬럼 및 범위를 정하여 조회할 수 있도록 제공함 -- (내가 공개하고 싶은 데이터만 table에서 뽑아서 제공하기 위함) -- 연산결과만 제공하고 알고리즘을 숨기기 위해 사용 -- select list를 함수로 가공하여 update, insert를 못하도록 함 -- 테이블 명이나 컬럼 명을 숨기기 위함 -- 2. [사용편의성을 위한 뷰] -- 검색 조건을 단순화하여 사용할 수 있도록 함 -- 테이블간 복잡하게 뽑아와야할 것을 그런 결과로 뽑은 하나의 뷰로 만들어 제공하면 -- 쉽게 사용자는 조회할 수 있다.(for End User) -- 조인을 단순화 -- 사용자를 위한 컬럼명이나 테이블명 제공 -- EX) -- Employees 테이블에서 필요한 몇몇 컬럼 데이터들만 "뷰"로 뽑아와 보자. -- 뷰 생성 -- job_id가 ST_CLERK인 사람들의 정보를 이용한 뷰 create view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정 as select employee_id, first_name, job_id, hire_date, department_id from Employees where job_id = 'ST_CLERK'; -- job_id가 SH_CLERK인 사람들의 정보를 이용한 뷰 create view v_emp1(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정 as select employee_id, first_name, job_id, hire_date, department_id from Employees where job_id = 'SH_CLERK'; -- view를 이용한 조회 select dept_id from v_emp1; -- view 제거 drop view v_emp1; -- view 수정 create or replace view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정 as select employee_id, first_name, job_id, hire_date, department_id from Employees where job_id = 'SH_CLERK'; -- 조건만 수정했음 select * from v_emp; ---- [ view의 활용 ] ---- -- 1. 보안상 목적 -- 뷰를 만들 때 select문에서 함수를 써서 만들면 해당 뷰에 insert와 update를 할 수 없게 된다. -- 따라서, 별 의미없어보이지만 nvl함수를 써서 보안상 목적을 제공할 수 있다.(꼭 nvl일 필요는 없다.) create or replace view v_emp3(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정 as select nvl(employee_id,null), nvl(first_name,null), job_id, nvl(hire_date,null), department_id from Employees where job_id = 'SH_CLERK'; desc v_emp3; -- 변경 불가 update v_emp3 set first_name = 'modify' where first_name='Julia'; -- 연봉을 구하기 위한 긴 구문을 뷰를 이용해 편리하게 조회하는 예 create view v_emp_salary(emp_id, last_name, annual_sal) as select employee_id, last_name, (( salary + nvl(commission_pct,0) ) * 12) from employees; -- annual salary를 편리하게 조회할 수 잇다. select * from v_emp_salary; -- 뷰의 수정을 막는 조회용 with read only! -- 조회만 가능한 view 생성!(read only view) create view v_emp_readonly(emp_id, last_name, annual_sal) as select employee_id, last_name, (( salary + nvl(commission_pct,0) ) * 12) from employees with read only; -- with read only하면 해당 뷰를 수정할 수 없게 설정한다. desc v_emp_readonly; select * from v_emp_readonly; -- 변경 불가(조회만 가능한 read only view이기 때문) update v_emp_readonly set last_name = 'kim' where last_name = 'Grant'; create view v_sample1 as select employee_id, last_name, department_id, hire_date from employees where (salary + nvl(commission_pct,0))*12 > 40000 and department_id = 50 and job_id = 'ST_CLERK' and sysdate - 365 * 5 > hire_date; -- 입사한지 5년이 넘은 사람 조건 select * from v_sample1; create view 사원 (사번, 이름, 부서번호, 입사일) as select employee_id, first_name||' '||last_name, department_id, hire_date from employees where department_id = 50; select * from 사원; create view v_join(사번,이름,부서번호,부서명,입사일) as select emp.employee_id, emp.first_name || ' ' || emp.last_name, dept.department_id, dept.department_name, emp.hire_date from employees emp, departments dept where emp.department_id = dept.department_id (+); select * from v_join;

[ 인라인(Inline) 뷰(View) 란? ]
: select의 from절에 테이블이 바로 오는 것이 아니라, select절이 오는 방식

왜 사용할까?
-> 서브쿼리를 포함해 테이블 조인이 이루어질 경우 검색하는 필드명은 테이블.필드명처럼해서 길어지게 된다. 이게 서브쿼리를 통해
메인 쿼리로 올라갈 수록 그 길이는 점점 더 길어져 불편할 수 있는데
이때, from절에서 select문을 통해 추출한 필드명에 Alias를 줘서 간단하게 만들 수 있다.

이러한 목적 이외에도 예를들어, 전체 데이터에서 비교하는 것보다 rownum을 이용해서 10번째 튜플 정보만 가져와 그 중에서 조건을 따져 데이터를
가져온다면 비교하는 횟수가 줄어들게 할 수도 있다.
이때, rownum을 통해 가져오는 갯수를 제한하는 방식을 N-TOP 질의라고 한다.
EX)     select 사원명, 부서명 from ( select e.ename AS 사원명, d.dName AS 부서명 from emp e, dept d where e.deptno = d.deptno
where rownum <= 10
order by e.sal
);

저작자표시 비영리

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

ORACLE PL/SQL 개념 - (1)  (0) 2017.09.11

ORACLE 시퀀스(sequence)  (0) 2017.09.08

ORACLE 무결성 제약조건  (0) 2017.09.07

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06

ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06

ORACLE 무결성 제약조건

2017. 9. 7. 09:52

[    ORACLE 무결성 제약조건 ]

--무결성 제약 조건1 -- 데이터가 올바르게 저장될 수 있도록 제약 조건을 거는 것 -- 객체 무결성 -- : 기본키 필드의 데이터는 null값을 가질 수 없으며, 중복된 값이 올 수 없다. -- 참조 무결성 -- : 기본키를 참조하는 외래키(Foreign key)는 null은 허용하되, 참조하는 기본키 -- 값들로 사용되는 값들만 올 수 있다. -- ex) 기본키값이 (A,B,C,D)고 외래키가 이 기본키를 참조한다면 -- 외래키값은 A,B,C,D,null 값들 중에 하나의 값만 입력될 수 있다. -------- [ 제약 조건 ] -------- -- not null -- : NULL값이 입력되지 못하게하는 조건 -- unique : 중복된 값이 입력되지 못하게 하는 조건 -- primary key(PK,기본키) : not null + unique 조건 -- = '객체 무결성' -- foreign key(FK,외래키) : 다른 테이블의 필드(컬럼)를 참조해서 무결성을 검사하는 조건 -- = '참조 무결성' -- check : 주어진 값만 허용하는 조건 -- EX) -- not null ) create table null_test( col1 varchar2(20) not null, -- null 입력 불가 col2 varchar2(20) null, -- null 입력 가능 col3 varchar2(20) -- null 입력 가능 ); insert into null_test(col1,col2) values('aa','bb'); -- col3에 null들어감 select * from null_test; -- col1은 null이 불가하기 때문에 에러가 발생하게 됨 insert into null_test(col2,col30) values('cc','dd'); -- unique ) create table unique_test( col1 varchar2(20) unique not null, -- unique + not null = primary key(PK) col2 varchar2(20) unique, col3 varchar2(20) not null, col4 varchar2(20) not null, constraint temp_unique unique(col3,col4) -- col3과 col4를 "조합"했을 때 중복되지 않도록 ); insert into unique_test(col1,col2,col3,col4) values('aa','bb','cc','dd'); insert into unique_test(col1,col2,col3,col4) values('a2','b2','c2','d2'); select * from UNIQUE_TEST; -- ERROR 발생 : unique로 중복을 허용하지 않게 해났기 때문에... update unique_test set col1 = 'aa' where col2 = 'b2'; insert into unique_test(col1,col2,col3,col4) values('a3','','c3','d3'); -- col2에 null값이 가능함으로 들어간다.(스페이스없으면 null) select * from unique_test; -- unique는 null값은 가능함(중복 여부 비교 대상에서 제외함) insert into unique_test(col1,col2,col3,col4) values('a4','','c4','d4'); -- Primary Key(PK,기본키) = UNIQUE + NOT NULL -- 1. 테이블 생성시 기본키 생성 방법 create table primary_test( student_id number(20) primary key, -- 인라인 방식 name varchar2(20) ); create table primary_test2( student_id number(20), name varchar2(20), constraint student_id_pk primary key(student_id) -- 아웃라인 방식 -- 인라인 방식에서는 아웃라인에서처럼 기본키의 이름을 지을 수 없다. ); -- 2. 테이블 생성하고 나서 이후에 기본키를 생성하는 방법 alter table primary_test drop primary key; -- 기존 기본키 지우기 alter table primary_test add constraint student_id_pk2 primary key (student_id); -- 기본키 추가 -- Foreign Key(FK,외래키) create table foreign_key( department_id constraint dept_fk references departments(department_id) -- 인라인 방식 ); create table foreign_key( department_id number(20), -- 아웃라인 방식 constraint dept_fk2 foreign key (department_id) references departments(department_id) ); -- 테이블 생성 뒤 fk 만들기 -- ALTER TABLE 테이블명 DROP CONSTRAINT fk_table_id; alter table foreign_key drop constraint dept_fk2; -- fk 삭제 -- ALTER TABLE 테이블명 ADD CONSTRAINT fk_table_id FOREIGN KEY(id) REFERENCES 참조테이블명(id); alter table foreign_key add constraints dept_fk3 foreign key(department_id) -- fk 추가 references departments(department_id); -- 참조되고 있는 부모테이블의 데이터를 지우개되면?? -- 그 없어질 데이터를 참조하고 있는 자식 테이블 때문에 에러가 발생함 -- 따라서 지울 수가 없는대, 지울려면 2가지 방법이 있다. -- 1. 자식 테이블에서 해당 부모 테이블을 참조하는 데이터를 먼저 삭제한 뒤 부모테이블 데이터를 삭제한다. -- 2. casecade 키워드를 붙여 삭제한다. -- check ) create table check_test( gender varchar2(10) not null constraint check_sex check(gender IN('M','F')) -- M,F만 입력 가능해짐 ); create table check_test2( gender varchar2(10) not null constraint check_sex check(gender IN('남성','여성')) -- 남성,여성만 입력 가능해짐 ); create table check_test3( score number(20) constraint score_check check(score between 0 and 100), -- 0~ 100 값만 입력 가능 kor varchar2(10) );

저작자표시 비영리

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

ORACLE 시퀀스(sequence)  (0) 2017.09.08

ORACLE 뷰(View)와 Inline 뷰  (0) 2017.09.08

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06

ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  (3) 2017.09.06

ORACLE DML( insert, update, delete, select )

2017. 9. 6. 16:55

[   ORACLE DML( insert, update, delete, select ) ]

--DML : Data Manipulation Language : ( select, insert, update, delete )

-- 1. select 문 : 데이터 검색
-- 형식) select 컬럼 from 테이블 where 조건들.. group by having order by ...

-- 2. insert 문 : 데이터 삽입(튜플 삽입)
-- 형식)

- 단일행을 삽입하는 경우 : insert into 테이블(컬럼1,컬럼2,...) values(값1,값2,...)
- 복수행을 삽입하는 경우 : insert into 테이블(컬럼1,컬럼2...) (select 컬럼1,컬럼2,... from ~ where ~);

-- 3. update 문 : 데이터 수정
-- 형식) update 테이블 set 컬럼1=값1 where 조건들...

-- 4. delete 문 : 데이터 삭제(튜플 삭제)
-- 형식) delete from 테이블 where 조건

delete * from 테이블명; 과 Truncate Table 테이블명과의 차이점?

: delete를 수행시 자동으로 commit이 이루어 지지 않기 때문에 rollback을 할 수 있지만

truncate의 경우 데이터를 삭제 후 자동으로 commit까지 진행하기 때문에 rollback을 할 수 없다.

--DML : Data Manipulation Language : ( select, insert, update, delete ) -- 1. select 문 : 데이터 검색 -- 형식) select 컬럼 from 테이블 where 조건들.. group by having order by ... -- 2. insert 문 : 데이터 삽입(튜플 삽입) -- 형식) insert into 테이블(컬럼1,컬럼2,...) values(값1,값2,...) -- 3. update 문 : 데이터 수정 -- 형식) update 테이블 set 컬럼1=값1 where 조건들... -- 4. delete 문 : 데이터 삭제(튜플 삭제) -- 형식) delete from 테이블 where 조건 --ex) create table sample( deptNo number(20), deptName varchar2(15), deptLoc varchar2(15), deptManager varchar2(10) ); -- 데이터 튜플 삽입 insert into sample(deptNo,deptName,deptLoc,deptManager) values(1,'인사부','강남','홍길동'); select * from sample; -- 필드명을 생략하고 추가할 수도 잇다. 생략하면 전 필드의 데이터를 다 넣어줘야함 insert into sample values(2,'개발부','군산','홍길순'); -- deptManager 값을 빼고 튜플을 삽입 insert into sample(deptNo,deptName,deptLoc) values(3,'관리부','광주'); select * from sample; -- deptManager값이 null로 들어감을 알 수 있다. -- deptManager 값이 null인 것을 수정하자. update sample set deptManager = '홍삼순' where deptManager IS NULL; select * from sample; update sample set deptLoc = '일산' where deptName = '인사부'; select * from sample; -- 튜플 삭제 delete from sample where deptName = '관리부'; select * from sample; -- commit 과 rollback -- commit : 최종적으로 명령 처리를 반영하는 명령어 -- rollback : 직전에 수행한 명령을 수행하지 않은 상태로 돌리는 명령어 -- transaction처리와 관련있다. -- 트랜잭션이란? 완전히 다 처리하거나 아예 처리되지 않은 상태로 돌리는 것으로 -- 일부만 수행됨으로써 발생할 수 있는 심각한 오류를 방지하기 위함 -- rollback commit;

저작자표시 비영리

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

ORACLE 뷰(View)와 Inline 뷰  (0) 2017.09.08

ORACLE 무결성 제약조건  (0) 2017.09.07

ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  (3) 2017.09.06

ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05

ORACLE DDL( create, drop, alter, truncate )

2017. 9. 6. 15:58

--[ DDL(Data Definition Language) : create, alter, drop, truncate를 이용한 테이블 관리 ] -- desc 테이블명 -- 테이블 구조 보기(describe) desc Employees; desc member; create table employees2( employee_id number(10) primary key, name varchar2(20), salary number(7,2) -- 7자리를 할당하고 소수점은 2자리란 의미 ); create table employees4( employee_id number(10), --constraint employees4_pk primary key, name varchar2(20), salary number(7,2), -- 7자리를 할당하고 소수점은 2자리란 의미 --foreign key (employee_id) references employees2.employee_id constraint employees4_pk primary key(employee_id), -- 각 키 이름을 지정할 수 있는데 -- 이름을 지정할 때는 constraint 라는 키워드를 붙여준다. constraint employees4_fk FOREIGN key(employee_id) REFERENCES employees2(employee_id) -- 외래키 지정 ); -- 기존 테이블 항목을 그대로 만들고 싶을 때 create table employees3 as select * from employees; desc employees3; -- 테이블 수정 : alter ------------------------------------------------------------------------------- -- alter문) -- employees2 테이블에 manager_id 항목 추가 alter table employees2 add ( manager_id varchar2(10) ); desc employees2; -- manager_id의 varchar2(20)을 varchar2(10)으로 변경해보자. alter table employees2 modify( manager_id varchar2(20) ); -- manager_id "컬럼"을 삭제하자. alter table employees2 drop column manager_id; desc employees2; -- Primary Key 변경 방법 -- 1. 먼저 primary key를 drop한다. -- 2. alter table 테이블명 add constraint 인덱스명 primary key(기존컬럼,추가컬럼) alter table employees2 drop primary key; -- primary key 지우기 alter table employees2 add primary key(name); -- primary key 추가하기 desc employees2; -- 현재 name이 기본키로 되어 있다. name뿐만아니라 employee_id + name 두개를 복합키로 지정해 보자. alter table employees2 drop primary key; alter table employees2 add primary key(name,employee_id); ---------------------------------------------------------------- -- drop 문) -- employees2 테이블 삭제하기 drop table employees2; ---------------------------------------------------------------- -- rename문) -- 테이블 이름 변경 -- member 테이블명을 member1으로 변경 rename member to member1; desc member1; -------------------------------------------------------------------- -- truncate 문) -- 테이블 내에 있는 모든 레코드를 삭제하는 구문(drop은 테이블 자체를 지우고 -- truncate는 레코드들만 비우는 것 -- employees3 테이블의 모든 레코드를 지워라. truncate table employees3; select * from employees3; /* 문자형 데이터 타입 char : 고정길이 문자형 타입(지정한 사이즈만큼,남아도 고정적으로 용량 차지), varchar2 : 가변길이(최대사이즈는 지정하지만, 입력한게 여분이 남으면 입력한대까지만 용량차지), nchar : 고정길이 유니코드 문자형 타입, nvarchar : 가변길이 유니코드 문자형 타입, long(2GB) : 가변길이형 문자 데이터 타입(2GB까지 가능) */

저작자표시 비영리

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

ORACLE 무결성 제약조건  (0) 2017.09.07

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  (3) 2017.09.06

ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...

2017. 9. 6. 10:54

[   ORACLE 서브쿼리와 ANY,ALL,IN에 대하여... ]

메인 쿼리 안에서 메인 쿼리의 중간 결과로 사용되는 쿼리문을 "서브쿼리"라고 하는데,

서브 쿼리를 작성할 때 고려해야할 점은, 서브쿼리의 결과값이 단일 튜플이 나올 수도 있지만 다중 튜플일 경우이다.

일반적으로 salary(급여)가 (13000,6000,7000) 이 중 하나인 경우( salary = 13000 or salary = 6000, salary = 7000 )는

간단하게 salary IN (서브 쿼리) 로 "IN"을 사용해 작성하면 되지만

">"와 같은 연산자는 IN을 사용할 수가 없는 단일 비교 연산자이다. 이러한 경우에는 ANY와 ALL을 사용하게 된다.

ANY는 해당 비교 연산의 or결과로 ALL은 and 결과로 연산이 되는데 예를들자면

salary > ANY(다중 튜플을 결과로하는 서브쿼리) 는

salary > 13000 or salary > 6000 or salary > 7000 의 결과가 된다. 즉, salary > 6000을 만족하는 결과들이 될 것이고,

salary > ALL(다중 튜플을 결과로하는 서브쿼리) 는

salary > 13000 and salary > 6000 and salary > 7000의 결과임으로, salary > 13000 을 만족하는 결과가 나올 것이다.

다음 아래는 해당 내용에 대한 예제 코드와 주석문이다.

--[ 서브 쿼리 ] -- 메인 쿼리 안에 또 다른 쿼리문이 들어가는 것! -- 메인쿼리를 구성하는 소단위 쿼리다. -- DML(select,insert,delete,update)에서 모두 사용가능하다. -- 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용된다. -- 서브 쿼리 자체는 일반 쿼리와 다를 바가 없다. -- 평균 급여보다 급여가 작은 사람들의 정보를 구할 때 select employee_id, first_name, last_name, salary from employees where salary < 6462; -- 이렇게하면 가변적으로 변하는 값에 대응할 수가 없음 -- 따라서, 서브쿼리를 이용해 평균을 구한 값을 중간 결과값으로 써서 구할 수 있다. select employee_id, first_name, last_name, salary from employees where salary < (select round(avg(salary)) from Employees); -- where절에선 집계함수 -- 사용이 불가능하기 때문에 서브쿼리를 이용해 중간 결과값을 가지고 와야 한다. --COUNTRY_ID값이 US인 location_id를 가지고와서 departments 테이블에 있는 부서명을 구해보자 select department_name from departments -- 조건절에서 서브쿼리 결과가 여러 튜플이 나올 수 있을 땐 IN을 사용해야 한다. where location_id IN (select location_id from locations where COUNTRY_ID = 'US'); --EX)Employees 테이블에서 월급이 가장 적은 사용자 정보(직급포함)를 가져와라 select emp.first_name, emp.last_name, job.JOB_TITLE from Employees emp, jobs job where salary = (select min(salary) from Employees) -- 서브쿼리 and emp.job_id = job.job_id; -- join(JOB_TITLE을 구하기 위함) --평균 급여보다 많은 급여를 받는 사원 정보를 출력(단, 급여가 높은순에서 낮은순으로) select emp.first_name || emp.last_name 이름, emp.salary 급여, j.JOB_TITLE 직급 from Employees emp, Jobs j where salary > (select avg(salary) from Employees) and emp.job_id = j.job_id order by emp.salary desc; -- 급여가 높은순으로 정렬 -- [ any, all 연산자 ] 를 활용한 서브쿼리 select salary from Employees where Department_id = 20; --department_id가 20인 부서에서 받는 급여보다 많은 급여를 받는 사람 -- 위의 서브쿼리 결과는 salary가 6000, 13000이 나옴 -- 따라서, 6000보다 많이받아도되고, 13000보다 많이 받아도 되는 사람들을 출력하도록 하자. select Employee_id, department_id, salary from Employees where salary > (select salary from Employees where Department_id = 20); -- 이 경우는 오류가 날 것이다. > 연산자나 = 연산자는 하나의 결과값과 비교하는 것인데 -- 위의 경우 서브쿼리의 결과가 여러 튜플이 나오게 된다. 따라서 그것보다 크다는 비교를 할수가 없게된다. -- in을 사용하려해도 in은 그 돈과 같은 돈을 받는 사람을 구하는 것이기 때문에 그것도 불가능함 -- 따라서, 아래처럼 해 주어야 한다. -- ANY를 사용하자. (다중값이 나올 때 각 비교의 하위 고려 대상을 기준으로 된다.) -- ex) 6000,13000 두개의 값이 나왔을 때 salary < ANY(6000,13000)일 경우 13000보다 작은 값들 -- salary > ANY(6000,13000)일 경우 6000보다 큰 값들이 나오게 됨 -- 더 쉽게 말해, salary < 6000 or salary < 13000일 경우 출력되는 상황과 같음 -- salary > 6000 or salary > 13000 -- SUMMARY) 즉, ANY는 서브쿼리의 결과가 여러개 나올 때, 그 튜플 결과의 둘중 하나만이라도 만족시키는 -- 값들은 다 나올 수 있도록 하는 "OR"와 같은 개념이고... -- ALL은 여러 튜플의 결과에 따른 비교 결과를 모두 만족시킬 수 있도록 나오게하는 "AND"와 같다. select Employee_id, department_id, salary from Employees where salary < ANY(select salary from Employees where Department_id = 20); -- ALL의 경우] -- 따라서 다 만족하게 되는 결과가 나오게 됨으로 6000보다 작은 얘들만 나오게 됨 select Employee_id, department_id, salary from Employees where salary < ALL(select salary from Employees where Department_id = 20);

저작자표시 비영리

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

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06

ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06

ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE JOIN(Inner join,Outer Join,Self Join)

2017. 9. 5. 14:02

--조인(JOIN) -- : n개의 테이블간에 필요한 컬럼 정보를 하나로 출력하기 위해 사용됨 -- [ Inner JOIN ] : 완전히 일치하는 데이터만 나오는 방식(일반적인 조인 방식) select e.First_name, e.Last_name, e.Email 이메일, d.DEPARTMENT_NAME 부서명 from Employees e, Departments d where e.DEPARTMENT_ID = d.DEPARTMENT_ID; -- 두 정보가 완전히 일치하는 정보들만 출력됨 -- 즉, 이 경우는 값이 다른 값들은 출력되지 않음 -- 단순히 부서명 컬럼이 하나 더 필요해서 조인을 하는 경우는 이렇게하면 출력되지 않는 값들이 생겨 -- 원하는 데이터들을 뽑아내지 못할 수 있다. select emp.first_name || emp.last_name 이름, emp.email 이메일, emp.department_id, dep.department_name 부서명, j.job_title 직급, l.CITY 지역 from Employees emp, Departments dep, jobs j, locations l where emp.department_id = dep.department_id and emp.job_id = j.job_id and dep.location_id = l.location_id -- 여기까지 조인조건 and l.city = 'Seattle'; -- 일반조건 --[ Self JOIN ] -- 자신의 테이블에서 자신의 테이블을 조인해서 사용하는 방식 -- 왜 필요?? -- ex) Employees 테이블에서 한 튜플의 직원 정보에서 그 사람의 상급자인 manager_id값이 있다. -- 이 manager_id는 상급자의 사원번호인데 이 사원번호 정보는 동일 테이블인 Employees에 있다. -- 왜? 상급자도 직원이기 때문에 같은 테이블에 정보가 있고 따라서 같은 테이블을 참고할 일이 생김 -- 아래의 경우, 셀프조인을 하지 않으면 emp2.FIRST_NAME인 상사의 이름에 해당하는 필드는 가져올 방법이 없다. select emp1.EMPLOYEE_ID, emp1.FIRST_NAME, emp2.EMPLOYEE_ID 상사ID, emp2.FIRST_NAME 상사이름 from Employees emp1, Employees emp2 where emp1.manager_id = emp2.employee_id; ------------------------------------------------------------------------------------------ --[ Outer JOIN(외부 조인) ] -- left outer join, right outer join, full outer join이 있다. -- full outer join은 ANSI 표준 문법으로만 작성이 가능하고 ORACLE 전용 문법으로는 불가능하다. select emp.employee_id, emp.first_name, emp.department_id, dep.department_name from Employees emp, departments dep where emp.department_id = dep.department_id; -- 106명 출력 select * from Employees; -- 107명 -- 왜 106명 107명 다를까? 둘다 완전히 일치한 경우만 출력되기 때문(inner join), null값인 경우 출력안됐을 것... -- 따라서, 이런 경우 Outer Join 방식을 사용해야 함 select emp.employee_id, emp.first_name, emp.department_id, dep.department_name, loc.CITY from Employees emp, departments dep, locations loc where emp.department_id = dep.department_id (+) and dep.location_id = loc.location_id (+); -- 즉, 이러한 방식은 Oracle 전용 문법임, ANSI 표준은 from에 left outer join혹은 right outer join식으로 써야함 -- 즉, 오라클 문법에서 outer join은 (+)로 나타내며, 전부 출력되어야 하는 테이블의 반대편에 붙여주게 된다. -- ANSI 표준 방식으로 작성한 3중 left outer join select emp.employee_id, emp.first_name, emp.department_id, dep.department_name, loc.city from Employees emp left outer join departments dep on emp.department_id = dep.department_id left outer join locations loc on dep.location_id = loc.location_id;

저작자표시 비영리

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

ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  (3) 2017.09.06

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE 문자열 함수  (0) 2017.09.04

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP

2017. 9. 5. 09:09

--Group by 절 -- distinct처럼 중복되지 않도록 나오지만 각 튜플마다 집계함수와 연계되어 -- 각 튜플별로 그룹핑해 그 결과를 알고싶을 때 주로 사용된다. -- 예를들어, 여러 학생들의 성적이 들어 있는 Student테이블이 있을 때 -- A반,B반,C반... 반마다 평균 국어 성적을 구한다고 했을 때 반으로 그룹핑을 하고 avg로 평균을 구할 수 있다. select distinct department_id from EMPLOYEES; select department_id from Employees GROUP BY DEPARTMENT_ID; --부서별 급여 합계 : 그룹별(부서별) 급여 총액 구하기 select DEPARTMENT_ID, sum(SALARY) from Employees GROUP BY DEPARTMENT_ID; --부서별,직급별 사원수와 평균 급여를 구해보자. -- job_id 도 같이 출력할 경우 DEPARTMENT_ID 때문에 일일이 하나하나 다 표시될 수 없기 때문에 -- 이 job_id또한 GROUP BY를 걸어주어야 한다. select DEPARTMENT_ID, job_id, sum(SALARY), count(*), round(avg(salary),2) from Employees GROUP BY DEPARTMENT_ID, job_id; select DEPARTMENT_ID, job_id, sum(SALARY), count(*), round(avg(salary),2) from Employees GROUP BY DEPARTMENT_ID, job_id ORDER BY DEPARTMENT_ID, job_id; select DEPARTMENT_ID, job_id, to_char(sum(salary),'999,999') 총급여, to_char(avg(salary),'999,999') 평균급여 from Employees where department_id = 80 GROUP BY DEPARTMENT_ID,job_id ORDER BY DEPARTMENT_ID, job_id; --having 절 -- GROUP BY의 조건절 --현재 부서별 사원수가 10명 이상인 직원수 구하기 -- 집계 그룹과 관련된 조건(사원수 10명이상)이기 때문에 having절이 필요 -- 집계함수에 대한 조건을 사용할 때(sum,count,avg 등...) having절을 사용하고 -- 부서id가 80이고 null이 아닌...등의 조건은 where절을 사용하면 된다. SELECT department_id 부서ID, count(*) 직원수 FROM Employees where department_id is not null -- null이 아닌 부서 id만 출력할 것(<>사용하면 안됨) GROUP BY department_id HAVING count(*) > 10 ORDER BY department_id; --rollup --[ ROLLUP operator ] -- ROLLUP구문은 GROUP BY 절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 집합 결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행 한다. -- SELECT절에 ROLLUP을 사용함으로써 보통의 SELECT된 데이터와 그 데이터의 총계를 구할 수 있다. -- : 그룹별 합계 정보를 추가해서 보여주는 함수 -- Employees 테이블에는 department_id정보에 대한 부서Name 정보가 없는데 -- Employees 테이블에 대해 GROUP BY를 해 출력할 경우 부서 테이블에 있는 부서명도 같이 출력하고 싶을 때 -- rollup 함수를 이용하면 효과적으로 해당정보를 가져올 수 있다.(그룹별에서 조인을 쉽게할 수 있는 함수) select l.CITY, d.DEPARTMENT_NAME, e.JOB_ID, count(*) 사원수, sum(e.salary) 총급여 from Employees e, departments d, locations l where e.DEPARTMENT_ID = d.DEPARTMENT_ID and d.LOCATION_ID = l.LOCATION_ID group by rollup(l.CITY, d.DEPARTMENT_NAME, e.JOB_ID) -- 그룹별 나뉘어진것 결과가 나오고 그 서브 합계도 추가로 출력되게된다.(rollup) order by l.CITY, d.DEPARTMENT_NAME, e.JOB_ID; -- 따라서 rollup을 쓰면 좀 더 세밀한 결과값들을 얻어올 수 있다.

저작자표시 비영리

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

ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  (3) 2017.09.06

ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE 문자열 함수  (0) 2017.09.04

ORACLE 집계함수, 숫자함수  (0) 2017.09.04

ORACLE 날짜함수, 변환함수, decode(), case()

2017. 9. 4. 14:42

[    ORACLE 날짜함수, 변환함수, decode(), case() ]

--[ ** 날짜 함수 ** ] --sysdate : 현재 시스템 날짜를 구해옴 select sysdate AS 현재날짜 from dual; --두 날짜 사이의 개월수를 출력하는 함수 : months_between(date1,date2) select ENAME,months_between(sysdate,hiredate) as 근무개월수 from EMP; --개월수를 더하는 메서드 : add_months() select add_months(sysdate,4) from dual; -- 현재 날짜에서 4개월을 더해줌 --다가올 특정일 날짜 가져오기 : next_day select next_day(sysdate,'일요일') from dual; -- 현재날짜 기준 다가올 일요일의 날짜 --해당달의 마지막 일 수 구하기 : last_day() select LAST_DAY(SYSDATE) from dual; --형식을 지정해 날짜를 문자열로 변환시킨다. : to_char() select to_char(sysdate,'yyyy-MM-dd') from dual; --문자열을 날짜형으로 바까주는 메서드 : to_DATE() select to_DATE('2017/10/27','yyyy/mm/dd') from dual; --NULL일 경우 다른 값으로 치환하는 메서드(중요!! NULL과 산술연산시 결과는 무조건 NULL이기 때문에 적당한 처리 필요) : NVL select (NVL(sal,0) * 0.09) 세금 from EMP; -- sal값이 NULL인경우 0으로 계산되도록... --switch문과 같은 역할을 하는 sql 메서드 : decode() -- deptno가 20일경우 '리서치부서'로 30일경우 '영업부'로 40일경우 '운영부'로 출력하는 코드 select DEPTNO, decode(deptno,20,'리서치부서',30,'영업부',40,'운영부') from DEPT; --elseif문과 유사 : case() select ENAME,DEPTNO, case when DEPTNO = 20 then '리서치부' when DEPTNO = 30 then '영업부' when DEPTNO = 40 then '운영부' else ' ' end AS "부서명" from EMP;

저작자표시 비영리

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

ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05

ORACLE 문자열 함수  (0) 2017.09.04

ORACLE 집계함수, 숫자함수  (0) 2017.09.04

이채남저 오라클실습 자료(개인 보관용)  (1) 2017.08.31

ORACLE 문자열 함수

2017. 9. 4. 11:19

--[ 문자형 함수 ] --문자열과 문자열을 연결하는 함수 : CONCAT(char1,char2) or || 를 이용 select CONCAT('HELLO','_World'), CONCAT('good','_bad') from dual; select 'Hello' || 'World' from dual; -- ||연산자를 이용한 문자열 합치기 select sal || '원' from EMP; --단어의 첫 문자를 대문자로 변환 : INITCAP(char) select INITCAP('the soap') from dual; -- The Soap select INITCAP('good/bad morning') from dual; -- Good/Bad Morning --LOWER(char) / UPPER(char) select LOWER('ABC') from dual; select UPPER('abc') from dual; --LPAD() select LPAD('good',6) from dual; -- good이란 글자를 6자리로 늘림 select lpad('good',6,'@') "LPAD1" from dual; -- @@good 6자리로 확대하고 왼족에 3번째 -- 인자 문자를 채움 select lpad('good',8,'L') from dual; -- LLLLgood -- RPAD() select RPAD('good',6,'#') from dual; -- good## -- 단, 한글은 2바이트니까 주의해야한다. select RPAD('good','10','홍길동') from dual; -- good(4byte) + '홍길동'(6byte) 총 10byte필요. -- trim() : LTRIM(), RTRIM(), trim() select LTrim('goodbye','g') from dual; -- oodbye 왼쪽에 있는 g값을 지움 select lTrim('goodbye','o') from dual; -- goodbye 중간에 기여잇는 경우는 지울 수 없음 아래처럼 해야함 select lTrim('goodbye','go') from dual; -- dbye select RTrim('goodbye','go') from dual; -- goodbye 오른쪽에서 go를 찾으니까 없음으로 안지워짐 select RTrim('goodbye','e') from dual; -- 왼쪽 삭제 'leading' select trim(leading from ' good ') from dual; -- good select length(trim(leading from ' good ')) from dual; -- 5 : leading은 왼쪽기준 공백 제거함으로 뒤에 5개 길이가 나옴 -- 오른쪽 삭제 'trailing' select trim(trailing from ' good ') from dual; -- 양쪽 : 'both' select trim(both from ' good ') from dual; -- 양쪽 공백을 지움 -- 왼쪽기준에서 g문자를 지움 select trim(leading 'g' from 'good') from dual; -- ood --substr('문자열',시작인덱스,문자열개수(길이)) : 부분적인 문자열을 뽑아내는 함수(시작 인덱스가 1임!!) select substr('good morning john',8,4) from dual; -- rnin select substr('good morning john',6) from dual; -- morning john 길이를 생략하면 그 뒤로 쭉 다 출력된다. select substr('good morning john',-4) from dual; -- john 오른쪽 끝에서부터 거꾸로... select substr('good morning john',-4,2) from dual; -- jo select substr('good morning john',instr('good morning john','morning',1),length('morning')) from dual; --replace('원문자열','바꿀문자열','교체할문자열') : 문자열 교체 select replace('good morning john','good','switch') from dual; -- good문자열을 switch로 변경 : switch morning john --translate() : replace와 약간 다른데 -- translate('문자열','You','We')라하면 1:1로 대응해서 바끼게된다. Y는 W로 o는 e로 select translate('You are not alone','You','We') from dual; -- We are net alene -- ASCII() : 각 문자열의 아스키값을 구함 select ascii('a') from dual; -- 97 -- 문자열 길이 : length() select length('안녕') from dual; -- 2 -- instr('문자열','찾고자하는문자',어느인덱스부터찾을지,몇번째꺼찾을지) : 찾고자하는 문자열의 시작 위치를 반환(시작인덱스는 1부터...) select instr('good morning john','mor',1) from dual; -- 6 select instr('good morning john','n',1,2) from dual; -- 11 두번째 n시작위치를 구한다. -- 1번째 인덱스부터 찾기 시작해서 2번째 n의 위치를 가져오는 것

저작자표시 비영리

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

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE 집계함수, 숫자함수  (0) 2017.09.04

이채남저 오라클실습 자료(개인 보관용)  (1) 2017.08.31

ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문  (1) 2017.08.22

ORACLE 집계함수, 숫자함수

2017. 9. 4. 09:23

--[ 기본함수 : 집계함수, 숫자함수 ] --sum(): 합계 select sum(salary) AS '임금합계' from employees; --count() : 튜플(레코드) 수 출력 select count(*) from employees; --전체 개수(중복포함), 중복을 배제한 개수 출력 select count(all First_name), count(distinct First_name) from Employees; --avg() : 평균 select avg(salary) as '임금평균' from Employees; WHERE department_id = 80; --max() : 최대값 select max(salary) as '가장높은임금' from Employees; --고용일이 가장 최근인 직원 select max(hire_date) from Employees; --주의!! : 집계함수는 조건절에서는 아래와같이 사용할 수 없음!! --select first_name,last_name from Employees where hire_date = max(hire_date); --따라서 서브쿼리를 이용해서 해야한다.!! -- 이런식으로... select First_name,Last_name from Emplyees where hire_date = (select max(hire_date) from Employees); --min() : 최소값 select min(salary) from Employees; --[ number function ] --abs() : 절대값 -- 이런 경우는 테이블을 쓸 필요 없음으로 오라클이 제공해주는 더미(dummy)테이블인 dual을 써서하면 된다. select ABS(-23) from dual; --sign() : 양수(1) 음수(-1) 0(0) select sign(23), sign(-23), sign(0) from dual; --round() : 반올림 select round(0.123), round(2.543) from dual; --반올림할 자리수를 지정할 수도 있다. select round(0.12345678,6), round(2.345678,4) from dual; --trunc(n1,n2) : 잘라내기, n2생략시 0이 default select trunc(0.12345,2) from dual; select trunc(1234.1234,-1) from dual; -- 1230 출력 --ceil() : 무조건 올림 함수(큰 정수 값) select ceil(32.8) from dual; -- 33 --floor() : 무조건 내림 함수(작은 정수 값) select floor(32.8) from dual; --power(n1,n2) : 제곱을 구해주는 함수 select power(4,2) from dual; -- 4의 2승 --mod(n1,n2) : 나머지 구하기 : n1 % n2 select mod(777,4) from dual; --sqrt() : 제곱근 구하기 select sqrt(16) from dual; select sqrt(2) from dual;

저작자표시 비영리

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

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE 문자열 함수  (0) 2017.09.04

이채남저 오라클실습 자료(개인 보관용)  (1) 2017.08.31

ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문  (1) 2017.08.22

1. desc와 select 문  (0) 2017.08.07

이채남저 오라클실습 자료(개인 보관용)

2017. 8. 31. 13:55

JSP MVC패턴을 이용한 게시판 만들기 1(개요)

2017. 8. 28. 08:42

[    JSP MVC패턴을 이용한 게시판 만들기 1(개요) ]

전체적인 게시판 설게 구성도

클라이언트(브라우저에서의) 모든 요청은 FrontController에서 받게 되고, 요청을 받은 FrontController는 요청 분석을 해 필요한 처리르 담당하는 Command 객체를 지정하게 된다.

Command 객체들은 Service 인터페이스를 implements해 관리하며, 처리 요청을 받은 Command객체 내부에서는 DAO를 통해 DB에서 원하는 정보를 꺼내와 리턴하게 되고

꺼내온 정보를 담은 뒤 FrontController에서는 RequestDispatcher 를 이용해 forward시켜 원하는 화면(jsp 파일)으로 이동시켜 준다.

[ 테스트용 데이터베이스 구성 ]

STUDENT 테이블을 생성하고, mysql에서 auto_increment에 해당하는 sequence를 생성해 주도록 한다. 시퀀스는 삽입할 때마다 자동으로 컬럼을 증가시켜 넣어야하는 경우 주로 사용되는데

대표적으로 nextval : 다음에 삽입될 인덱스 번호, currval : 현재 마지막에 삽입된 인덱스 번호

등을 사용할 수 있다.

아래의 코드는 STUDENT 테이블을 생성하고 id를 기본키로 지정했고, 시퀀스를 이용해서 테스트용 데이터를 삽입했다

create table STUDENT(
id varchar2(50) primary key,
name varchar2(20) not null,
address varchar2(100)
);

commit;

create sequence STUDENT_seq;

insert into STUDENT(id,name,address) values(STUDENT_seq.nextval,'hyr','seoul');

commit;

select * from STUDENT;

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여...  (0) 2017.08.20

JSP JSTL(Java Standard Tag Liarary)  (0) 2017.08.18

JSP EL(Expression Language) 표현언어  (0) 2017.08.16

JSP 파일업로드  (2) 2017.08.16

JSP 커넥션 풀(Connection Pool)  (0) 2017.08.16

ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문

2017. 8. 22. 11:55

[  ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문   ]

--치환할 문자를 |로 구분하여 입력 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', 'Stay|Foolish|Steve', '') FROM DUAL --결과: Hungry, . - Jobs ; --문자를 제거만 할꺼면 3번재 파라미터는 생략가능 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', 'Stay|Foolish|Steve') FROM DUAL --결과: Hungry, . - Jobs ; SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005', 'Stay|Foolish|Steve', '###') FROM DUAL --결과: ### Hungry, ### ###. - ### Jobs 2005 ; --특수문자 제거 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', '[[:punct:]]') FROM DUAL --결과: Stay Hungry Stay Foolish Steve Jobs ; --숫자를 제외한 모든문자 제거 (숫자만) SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005', '[^[:digit:]]') FROM DUAL --결과: 2005 ; --$같은 특수문자를 치환하기 위해서는 Escape 문자(\)를 붙여 줘야함 SELECT REGEXP_REPLACE('$Stay Hungry, Stay Foolish. - Steve Jobs', '-|\$') FROM DUAL --결과: Stay Hungry, Stay Foolish. Steve Jobs

참조 : http://gent.tistory.com/82
출처: http://wjheo.tistory.com/entry/특수문자-제거-숫자만-빼고-다-제거 [IT world]

출처: http://wjheo.tistory.com/entry/특수문자-제거-숫자만-빼고-다-제거 [IT world]

저작자표시 비영리

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

ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

ORACLE 문자열 함수  (0) 2017.09.04

ORACLE 집계함수, 숫자함수  (0) 2017.09.04

이채남저 오라클실습 자료(개인 보관용)  (1) 2017.08.31

1. desc와 select 문  (0) 2017.08.07

JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여...

2017. 8. 20. 13:54

[    JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여... ]

- FrontController 패턴이란?

모든 요청을 하나의 서블릿 파일에서 받아서 처리하는 방식

- Command 패턴?

FrontController에서 받은 요청을 분석해 어떤 처리가 필요한지에 따라서 각 처리에 필요한 Command 객체를

할당해 그곳에서 처리한 뒤 필요한 데이터를 가지고 FrontContrller로 보내주고 그 데이터를 request 객체 등에

세팅해 RequestDispatcher 객체를 통해서 화면 전환시 request, response 객체를 함께 전달해주는 방식

이때, RequetDispatcher 객체의 특징!!

: 현재 페이지의 request, response 객체를 그대로 전달할 수 있다.!

- frontControllerEx.jsp
: 하나는 insert.do로 하나는 delete.do 라는 uri로 요청을 하고 있다. 단 둘 다 .do로 끝나는 패턴을 채택하고 있다.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <a href="insert.do">insert</a> <a href="<%=request.getContextPath()%>/delete.do">delete</a>

- FrontController.java
.do로 끝나는 모든 요청을 받을 수 있도록 @WebServlet을 통해 서블릿 맵핑이 되어 있다.
또한, get방식이든 post 방식이든 모두 actionDo라는 메서드를 수행하도록 되어 있고 해당 메서드에서는
요청받은 uri를 가지고 와서 어떤 작업이 필요한지 command를 분석한다.

분석하고 처리에 필요한 Service를 지정해 해당 service에서는 dao에 접근해 데이터를 처리하고 FrontController로 반환해준다.
service를 통해 가지고 온 데이터는 request 객체에 저장하고
해당 requets 객체와 response객체를 데이터를 뿌려줄 화면으로 전달하기 위해
RequestDispatcher 객체를 사용해서 전달해준다.
이 객체의 특징은 현재 페이지의 request, response 객체를 그대로 전달할 수 있다는 점이다!!
package com.java.controller; import java.io.IOException; import java.io.PrintWriter; import java.util.ArrayList; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("*.do") // (.do로 끝나는)모든 요청이 이 FrontController 서블릿으로 올 수 있도록 함 public class FrontController extends HttpServlet { private static final long serialVersionUID = 1L; public FrontController() { super(); } // get요청이든 post요청이든 다 이 메서드를 수행하도록 함 private void actionDo(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException{ System.out.println("actionDo"); // 화면으로 포워딩 시키기 위한 객체 RequestDispatcher dispatcher = null; // 요청한 uri를 가져옴 String uri = request.getRequestURI(); System.out.println("uri : " + uri); // 컨텍스트 패스를 가져옴 String conPath = request.getContextPath(); // 전체 uri에서 컨텍스트 패스를 제외한 뒷 부분을 가져옴 String command = uri.substring(conPath.length()); System.out.println("command : " + command); // ex) /insert.do if( command.equals("/memberAll.do") ){ response.setContentType("utf-8"); PrintWriter writer = response.getWriter(); writer.println("<head></head>"); // memberAll 에 해당하는 요청시 해당 요청을 담당할 커맨드 객체를 지정해 줌(서비스) Service service = new MemberCommand(); // 서비스에서 해당하는 데이터를 dao를 통해 가져온다.(실질 처리) ArrayList<StudentDTO> dtos = (ArrayList<StudentDTO>)service.execute(request, response); for(int i=0; i<dtos.size(); i++){ StudentDTO dto = dtos.get(i); String name = dto.getName(); String addr = dto.getAddress(); int age = dto.getAge(); } // 이런식으로 화면 전환하기 전에 request 등의 공유객체에 데이터를 세팅해주는대 // 지금은 테스트용이라 requestName 을 임의로 지정했지만 // 본래 service에서 가져온 데이터를 세팅해준다. request.setAttribute("requestName", "requestValue"); request.setAttribute("dtos", dtos); // 화면으로 이동시키기 위한 RequestDispatcher 객체를 생성함 // 인자로는 이동할 jsp의 uri를 넣어줌 // RequestDispatcher의 가장 큰 특징은!! // 현재 페이지의 request, response 객체를 그대로 가지고 다른 요청 페이지로 이동할 수 있다는점!! dispatcher = request.getRequestDispatcher("/dispatcherJsp.jsp"); // 이동할 때 현재 페이지의 request, response 객체를 함께 전달해줌. dispatcher.forward(request, response); }else if ( command.equals("/insert.do") ){ System.out.println("insert"); System.out.println("------------"); }else if( command.equals("/update.do") ){ System.out.println("update"); System.out.println("------------"); }else if( command.equals("/delete.do") ){ System.out.println("delete"); System.out.println("------------"); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { actionDo(request,response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { actionDo(request,response); } }

- Service 인터페이스
: 다형성 적인 측면과 코드의 관리적 측면 때문에 모든 커맨드 객체들을 포함할 수 있도록 Service 인터페이스를 만들고
모든 Command 객체들은 이 Service 인터페이스를 implements 하도록 구현한다. 이때 execute 메서드를 재구현 할 수 있도록
execute 메서드를 선언해 놓는다.
package com.java.controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public interface Service { // 모든 커맨드 객체들이 상속해 사용하게 될 execute 메서드 public Object execute(HttpServletRequest request, HttpServletResponse response); }

- 각 처리에 필요한 Command 클래스 중 하나
: Service 인터페이스를 구현하고 execute 메서드를 재정의한다.
실재로 이 커맨드 객체에서 dao를 통해 DB에 접근해서 원하는 데이터를 가지고 오는 작업을 해야하지만
현재 코드에서는 생략하도록 하였다.
package com.java.controller; import java.util.ArrayList; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; // Service 인터페이스를 implements 해서 execute 메서드를 구현한다. public class MemberCommand implements Service { @Override public Object execute(HttpServletRequest request, HttpServletResponse response) { // dao를 통한 db에 접근해 데이터들을 가지고옴... ArrayList<StudentDTO> dtos = null; // 본래 null이 아닌 dao객체를 통해 쿼리 수행을 해서 가져온 데이터를 넘긴다. return dtos; } }

- FrontController에서 request 객체에 세팅한 값들을 가지고오는 페이지이다.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> requestName : <%=request.getAttribute("requestName") %> <% /* // jsp페이지에서도 가능 RequestDispatcher dispatcher = request.getRequestDispatcher("/"); dispatcher.forward(request, response); */ %>

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP MVC패턴을 이용한 게시판 만들기 1(개요)  (0) 2017.08.28

JSP JSTL(Java Standard Tag Liarary)  (0) 2017.08.18

JSP EL(Expression Language) 표현언어  (0) 2017.08.16

JSP 파일업로드  (2) 2017.08.16

JSP 커넥션 풀(Connection Pool)  (0) 2017.08.16

JSP JSTL(Java Standard Tag Liarary)

2017. 8. 18. 08:48

[    JSP JSTL(Java Standard Tag Liarary) ]

JSTL은 Java Standard Tag Libarary의 약자로

EL가 같이 사용되어 스크립트릿과 태그를 번갈아 가며 작업할 때의 불편함을 없애기 위해 나왔다.

아래는 사용 예제이다.

하지만, 그 전에

1. http://tomcat.apache.org/taglibs/standard/ 로 들어간다.

가장 안정적인 버전은 JSTL 1.1 버전이니, 이걸 다운받도록 한다.

2. binaries를 클릭하고, 1.1.1.zip을 받는다.

다운을 받고 압축을 풀어 lib 폴더에 들어간다.

두 파일을 복사하여 STS 프로젝트의 WebContent 폴더안의 Web-INF 안의 lib 폴더에 복사한다.

이렇게 까지 하면 세팅 완료 이제 jstl을 사용할 수 있다.

jstl을 사용하려면 <%@taglib 을 등록해주어야 한다. 여기서는 대표적으로 코어(c) 태그 라이브러리만 예제를 사용해 보겠다.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>  <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> 변수 정의 : <c:set var="name" value="nameValue" /><br/> 값 출력 : <c:out value="${name}" /><br/> 변수 제거 : <c:remove var="name"/> <c:out value="${name}" /><br/> <c:if test="${2<3}"> 조건문 : <c:out value="${'조건문'}" /><br/> </c:if> <c:forEach var="temp" begin="1" end="10" step="2" varStatus="condi"> ${condi.index} : ${temp}<br/> </c:forEach> switch case 문 : <c:choose> <c:when test="${1<2}"> 1 </c:when> <c:when test="${2<3}"> 2 </c:when> </c:choose> response.sendRedirect : <c:redirect url="http://www.naver.com"></c:redirect>

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP MVC패턴을 이용한 게시판 만들기 1(개요)  (0) 2017.08.28

JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여...  (0) 2017.08.20

JSP EL(Expression Language) 표현언어  (0) 2017.08.16

JSP 파일업로드  (2) 2017.08.16

JSP 커넥션 풀(Connection Pool)  (0) 2017.08.16

JSP EL(Expression Language) 표현언어

2017. 8. 16. 09:27

[    JSP EL(Expression Language) 표현언어 ]

EL : Expression Language 표현언어로 표현식, 액션태그를 통해 출력하는 것을 좀더 편리하게 출력할 수 있도록 해준다.

예제를 보기 전에 프로젝트 전체에서 사용할 초기화 변수를 web.xml에 등록하고
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>ex4</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.htm</welcome-file> <welcome-file>index.jsp</welcome-file> <welcome-file>default.html</welcome-file> <welcome-file>default.htm</welcome-file> <welcome-file>default.jsp</welcome-file> </welcome-file-list>  <context-param> <description>전체 프로젝트에 적용되는 초기화 파라미터</description> <param-name>contextParamName</param-name> <param-value>contextParamValue</param-value> </context-param>  </web-app>

객체를 쉽게 가져올 수 있음을 테스트하기 위해 StudentDTO 빈즈를 만들어 놓고...
package ex4.com.java.study; public class StudentDTO { /* [ 자바 빈즈 ] : 그냥 단순히 DTO, VO 개념임... StudentDTO 이게 DTO이고 jsp단에서 useBean이 객체 생성 setProperty, getProperty를 통해 값을 가져올 수 있음. 단, 네이밍 규칙은 지켜줘야 함 */ private String id; private String name; private String address; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "StudentDTO [id=" + id + ", name=" + name + ", address=" + address + "]"; } }

본격적인 EL 예제)
ELPractice.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> EL 이란? : EL은 표현식, 액션태그 등을 대신해서 출력을 편리하게 해주는 표현 언어입니다. 사용 예제) 정수 : ${10}<br/> 실수 : ${10.56}<br/> 문자열 : ${"abcd"}<br/> boolean : ${true}<br/> 3항연산자 : ${(1<2) ? 2 : 1}<br/> <jsp:useBean id="studentDTO" class="ex4.com.java.study.StudentDTO"></jsp:useBean> <jsp:setProperty property="id" value="류큐규" name="studentDTO"/> getProperty액션 태그를 통해 dto 필드변수 값 가져오기 : <jsp:getProperty property="id" name="studentDTO"/><br/> EL을 통해 간단하게 가져오기 : ${studentDTO.id}<br/> **[EL의 내장 객체]** - applicationScope : application 내장 객체로 프로젝트 전체에서 공유된다. - sessionScope : session 객체로 브라우저 마다 생성되고 공유된다. - pageScope : 현재 페이지에서만 공유되는 pageContext 객체이다. - requestScope : request 내장 객체 - param : request로 전달한 데이터를 request.getParameter("name")으로 가져오는 파라미터 값을 가져오는 객체 - paramValues : 체크박스처럼 다수의 값을 전달한 값을 가져올 때 사용하는 객체 - initParam : web.xml에서 설정한 초기화 변수 값을 가져올 때 사용하는 내장객체 <% application.setAttribute("applicationName", "applicationVal"); session.setAttribute("sessionName", "sessionVal"); pageContext.setAttribute("pageName", "pageVal"); request.setAttribute("requestName", "requestVal"); // 이건 request.getParameter로 가져오는 것과 다름 // 이건 request.getAttributes로 가져와야함 // 폼이나 get방식으로 쿼리에 붙여 전달하는게 getParameter고 EL로는 param 객체를 이용함 %> <form action="ELPractice2.jsp" method="GET"> 이름 : <input type="text" name="name"> <input type="submit" value="전송" /> </form>

- ELPractice2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> application 값 : ${applicationScope.applicationName }<br/> session 값 : ${sessionScope.sessionName }<br/> page 값 : ${pageScope.pageName }<br/> request 값 : ${requestScope.requestName }<br/>  param 값 : ${param.name }  initParam 값 : ${initParam.contextParamName }

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여...  (0) 2017.08.20

JSP JSTL(Java Standard Tag Liarary)  (0) 2017.08.18

JSP 파일업로드  (2) 2017.08.16

JSP 커넥션 풀(Connection Pool)  (0) 2017.08.16

JSP DB(데이터베이스)  (0) 2017.08.14

JSP 파일업로드

2017. 8. 16. 00:07

[ JSP 파일업로드 ]

jsp에서 파일업로드를 지원하는 라이브러리는 다양한 것들이 있지만 대표적인 것이 cos.jar이다.

해당 라이브러리를 다운받는 사이트는 http://servlets.com/cos/ 에서 스크롤을 내리다보면 다운로드가 있다.

해당 파일을 다운로드해서 압축을 풀어 lib에 들어가보면 cos.jar파일이 보이는데 해당 파일을

자신의 프로젝트의 WEB-INF/lib 폴더에 넣고 동기화를 해주면 된다.

- [ 파일 업로드 폼 ] : fileUploadForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>파일 업로드 폼</title> </head> <h3>파일 업로드 폼</h3> <center>  <form action="fileUpload.jsp" method="post" enctype="Multipart/form-data"> 올린 사람 : <input type="text" name="name" /><br/> 제목 : <input type="text" name="subject" /><br/>  파일명1 : <input type="file" name="fileName1" /><br/> <input type="submit" value="전송" />   <input type="reset" value="취소" /> </form> </center>

이때, 파일을 저장할 디렉토리가 필요함으로 프로젝트 WebContent 하위에 uploadFile 폴더를 하나 만들자

이제 이 폴더에 업로드한 파일이 저장되도록 할 것인데, 프로젝트 내에 위치했다해서 워크스페이스 내에 있는 것이 아니다!!

실제 파일의 위치는 자신의 톰캣 서버 내의 프로젝트 폴더 내에 위치하고 있다는 점을 알아야한다.

만약 정확히 모르겠다면 jsp페이지에서 절대경로를 찍어보면 된다.

- fileUpload.jsp
: 파일 업로드
<%@page import="java.io.File"%> <%@page import="java.util.Enumeration"%> <%@page import="com.oreilly.servlet.multipart.DefaultFileRenamePolicy"%> <%@page import="com.oreilly.servlet.MultipartRequest"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <% // request.getRealPath("상대경로") 를 통해 파일을 저장할 절대 경로를 구해온다. // 운영체제 및 프로젝트가 위치할 환경에 따라 경로가 다르기 때문에 아래처럼 구해오는게 좋음 String uploadPath = request.getRealPath("/uploadFile"); out.println("절대경로 : " + uploadPath + "<br/>"); int maxSize = 1024 * 1024 * 10; // 한번에 올릴 수 있는 파일 용량 : 10M로 제한 String name = ""; String subject = ""; String fileName1 = ""; // 중복처리된 이름 String originalName1 = ""; // 중복 처리전 실제 원본 이름 long fileSize = 0; // 파일 사이즈 String fileType = ""; // 파일 타입 MultipartRequest multi = null; try{ // request,파일저장경로,용량,인코딩타입,중복파일명에 대한 기본 정책 multi = new MultipartRequest(request,uploadPath,maxSize,"utf-8",new DefaultFileRenamePolicy()); // form내의 input name="name" 인 녀석 value를 가져옴 name = multi.getParameter("name"); // name="subject" 인 녀석 value를 가져옴 subject = multi.getParameter("subject"); // 전송한 전체 파일이름들을 가져옴 Enumeration files = multi.getFileNames(); while(files.hasMoreElements()){ // form 태그에서 <input type="file" name="여기에 지정한 이름" />을 가져온다. String file1 = (String)files.nextElement(); // 파일 input에 지정한 이름을 가져옴 // 그에 해당하는 실재 파일 이름을 가져옴 originalName1 = multi.getOriginalFileName(file1); // 파일명이 중복될 경우 중복 정책에 의해 뒤에 1,2,3 처럼 붙어 unique하게 파일명을 생성하는데 // 이때 생성된 이름을 filesystemName이라 하여 그 이름 정보를 가져온다.(중복에 대한 처리) fileName1 = multi.getFilesystemName(file1); // 파일 타입 정보를 가져옴 fileType = multi.getContentType(file1); // input file name에 해당하는 실재 파일을 가져옴 File file = multi.getFile(file1); // 그 파일 객체의 크기를 알아냄 fileSize = file.length(); } }catch(Exception e){ e.printStackTrace(); } %>  <form action="fileCheck.jsp" method="post" name="fileCheckFormName"> <input type="hidden" value="<%=name%>" name="name" /> <input type="hidden" value="<%=subject%>" name="subject" /> <input type="hidden" value="<%=fileName1%>" name="fileName1" /> <input type="hidden" value="<%=originalName1%>" name="originalName1" /> </form>  <a href="#" onclick="javascript:document.fileCheckFormName.submit()">업로드 파일 확인하기 :<%=fileName1 %></a>

-fileCheck.jsp
: 파일 체크 페이지
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>파일체크 JSP 페이지</title> </head> fileCheck jsp페이지 <% // post방식에 대한 한글 인코딩 방식 지정 get방식은 서버의 server.xml에서 Connector태그에 URIEncoding="UTF-8" 추가 request.setCharacterEncoding("UTF-8"); // input type="name" 의 value값을 가져옴 String name = request.getParameter("name"); // input type="subject" 의 value값을 가져옴 String subject = request.getParameter("subject"); // 중복방지용으로 만들어져 넘겨진 파일명을 가져옴 String fileName1 = request.getParameter("fileName1"); // 본래의 파일명을 가져옴 String originalName1 = request.getParameter("originalName1"); %> <h3>업로드 파일 확인</h3> 올린 사람 : <%=name %><br/> 제목 : <%=subject %><br/>  파일1 다운로드 : <a id="downA" href="#"><%=originalName1%></a> <script type="text/javascript"> // 영문파일은 그냥 다운로드 클릭시 정상작동하지만 한글파일명을 쿼리문으로 날릴경우 인코딩 문제가 발생할 수 있다. 한글이 깨져 정상작동하지 않을 수 있음 // 따라서, 쿼리문자열에 한글을 보낼 때는 항상 인코딩을 해서 보내주도록 하자. document.getElementById("downA").addEventListener("click", function(event) { event.preventDefault(); // a 태그의 기본 동작을 막음 event.stopPropagation(); // 이벤트의 전파를 막음 // fileName1을 utf-8로 인코딩한다. var fName = encodeURIComponent("<%=fileName1%>"); // 인코딩된 파일이름을 쿼리문자열에 포함시켜 다운로드 페이지로 이동 window.location.href = "fileDown1.jsp?file_name="+fName; }); </script>

-fileDown1.jsp
: 파일 다운 페이지
<%@page import="java.net.URLEncoder"%> <%@page import="java.io.FileInputStream"%> <%@page import="java.io.File"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>파일 다운로드 페이지</title> </head> <% // a태그의 href로 fileDown1.jsp?file_name="<%=fileName1 을 통해 전달한 // 중복 방지 처리한 파일명 값을 가져온다. String fileName = request.getParameter("file_name"); // 업로드한 폴더의 위치와 업로드 폴더의 이름을 알아야 한다. String savePath = "uploadFile"; // WebContent/uploadFile // 위의 폴더는 상대경로이고 절대경로 기준의 진짜 경로를 구해와야한다. String sDownPath = request.getRealPath(savePath); System.out.println("다운로드 폴더 절대 경로 위치 : " + sDownPath); System.out.println("fileName1 : " + fileName); // 저장되어 있는 폴더경로/저장된 파일명 으로 풀 path를 만들어준다. // 자바에서는 \를 표시하기 위해서는 \를 한번 더 붙여주기 때문에 \\로 해준다. String sFilePath = sDownPath + "\\" + fileName; // ex)c:\\uploadPath\\image.jpg System.out.println("sFilePath : " + sFilePath); // 풀 path에 대한걸 파일 객체로 인식시킨다. File outputFile = new File(sFilePath); // 저장된 파일을 읽어와 저장할 버퍼를 임시로 만들고 버퍼의 용량은 이전에 한번에 업로드할 수 있는 파일크기로 지정한다. byte[] temp = new byte[1024*1024*10]; // 10M // 파일을 읽어와야 함으로 inputStream을 연다.(풀패스를 가지는 파일 객체를 이용해 input스트림을 형성한다.) FileInputStream in = new FileInputStream(outputFile); // 유형 확인 : 읽어올 경로의 파일의 유형 -> 페이지 생성할 때 타입을 설정해야 한다. String sMimeType = getServletContext().getMimeType(sFilePath); System.out.println("유형 : " + sMimeType); // 지정되지 않은 유형 예외처리 if ( sMimeType == null ){ // 관례적인 표현 sMimeType = "application.octec-stream"; // 일련된 8bit 스트림 형식 // 유형이 알려지지 않은 파일에 대한 읽기 형식 지정 } // 파일 다운로드 시작 // 유형을 지정해 준다. response.setContentType(sMimeType); // 응답할 페이지가 text/html;charset=utf-8을 // 파일 mime 타입으로 지정해 준다. // 업로드 파일의 제목이 깨질 수 있으므로 인코딩을 해준다. String sEncoding = new String(fileName.getBytes("euc-kr"),"8859_1"); //String B = "utf-8"; //String sEncoding = URLEncoder.encode(A,B); // 기타 내용을 헤더에 올려야 한다. // 기타 내용을 보고 브라우저에서 다운로드 시 화면에 출력시켜 준다. String AA = "Content-Disposition"; String BB = "attachment;filename="+sEncoding; response.setHeader(AA,BB); // 브라우저에 쓰기 ServletOutputStream out2 = response.getOutputStream(); int numRead = 0; // 바이트 배열 temp의 0번부터 numRead번까지 브라우저로 출력 // 파일이 위치한 곳에 연결된 inputStream에서 읽되 끝(-1) 전까지 while을 돈다. while((numRead = in.read(temp,0,temp.length)) != -1){ // temp 배열에 읽어올건데 0번째 인덱스부터 한번에 최대 temp.length 만큼 읽어온다. // 읽어올게 더이상 없으면 -1을 리턴하면서 while문을 빠져나감 // 브라우저에 출력 : 근대 header 정보를 attachment로 해놓았음으로 다운로드가 된다. out2.write(temp,0,numRead); // temp배열에 있는 데이터의 0번째부터 최대 numRead만큼 출력한다. } // 자원 해제 out2.flush(); out2.close(); in.close(); %>

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP JSTL(Java Standard Tag Liarary)  (0) 2017.08.18

JSP EL(Expression Language) 표현언어  (0) 2017.08.16

JSP 커넥션 풀(Connection Pool)  (0) 2017.08.16

JSP DB(데이터베이스)  (0) 2017.08.14

JSP 자바 빈즈(JAVA BEANS)  (0) 2017.08.14

JSP 커넥션 풀(Connection Pool)

2017. 8. 16. 00:01

커넥션 풀이란?

: jsp에서 데이터베이스와 관련된 처리를 할 때 가장 오래 걸리는 부분이 커넥션(Connection)을 생성하는 부분이다.

많은 사용자들이 요청할수록 그것은 더 심한데 그럴 경우 서버가 처음 로딩될 때 미리 일정량의 Connection 객체를

커넥션 풀에 생성해 두고 그곳에서 가져다 사용하고 반납하는 개념이다.

이때, 커넥션 풀에 생성한 커넥션들을 관리하는 객체가 DataSource라는 객체이다.

구현 방법)

먼저, 서버가 로딩될 때 생성해야 함으로 server의 context.xml 파일에서 작업을 한다.

- context.xml
이곳에 아래의 소스코드를 추가한다.
driverClassName, url, username, passowrd 등은 우리가 디비 연결시 필수적으로 필요했던 항목이고
maxActive는 미리 생성해놀 커넥션의 수이다.
maxWiat의 경우 미리 생성한 커넥션 이상의 요청이 들어왔을 때 추가로 커넥션을 생성할 때 최대 기다릴 수
있는 시간을 설정하는 것이다. 즉 timeout 을 설정
type은 커넥션을 관리하는 DataSource이다.
<Resource auth="Container" driverClassName = "oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:orcl" username="아이디" password="자기비밀번호" name="jdbc/Oracle11g" type="javax.sql.DataSource" maxActive="50" maxWait="1000" />

- db 객체를 얻어오는 페이지
<%@page import="java.sql.Connection"%> <%@page import="javax.sql.DataSource"%> <%@page import="javax.naming.Context"%> <%@page import="javax.naming.InitialContext"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <% // 컨텍스트 객체를 가져온다. Context context = new InitialContext(); DataSource datasource = null; // 컨텍스트 객체에서 DataSource 객체를 찾아온다. datasource = (DataSource)context.lookup("java:comp/env/jdbc/Oracle11g"); // env이하에는 context.xml에서 설정한 name값이 온다. Connection conn = null; // datasource 객체에서 커넥션 객체를 가져온다. conn = datasource.getConnection(); // 이후는 기존과 동일하게 사용하면 된다. %>

저작자표시 비영리

'스터디 > JSP' 카테고리의 다른 글

JSP EL(Expression Language) 표현언어  (0) 2017.08.16

JSP 파일업로드  (2) 2017.08.16

JSP DB(데이터베이스)  (0) 2017.08.14

JSP 자바 빈즈(JAVA BEANS)  (0) 2017.08.14

JSP 에러페이지에 대하여...  (0) 2017.08.11

PREV 1 2 3 NEXT

+ Recent posts

Powered by Tistory, Designed by wallel

Rss Feed and Twitter, Facebook, Youtube, Google+

ORACLE 반복문(basic loop, while, for, continue) -(6) (0)	2017.09.14
ORACLE 조건문(IF,CASE) -(5) (0)	2017.09.14
ORACLE PL/SQL rowType 변수 및 복합변수 활용 예 -(3) (0)	2017.09.13
ORACLE PL/SQL 변수 선언 및 데이터 타입 - (2) (0)	2017.09.12
ORACLE PL/SQL 개념 - (1) (0)	2017.09.11

ORACLE 조건문(IF,CASE) -(5) (0)	2017.09.14
ORACLE TABLE TYPE(컬렉션), 바인드 변수 -(4) (0)	2017.09.14
ORACLE PL/SQL 변수 선언 및 데이터 타입 - (2) (0)	2017.09.12
ORACLE PL/SQL 개념 - (1) (0)	2017.09.11
ORACLE 시퀀스(sequence) (0)	2017.09.08

ORACLE 시퀀스(sequence) (0)	2017.09.08
ORACLE 뷰(View)와 Inline 뷰 (0)	2017.09.08
ORACLE DML( insert, update, delete, select ) (0)	2017.09.06
ORACLE DDL( create, drop, alter, truncate ) (0)	2017.09.06
ORACLE 서브쿼리와 ANY,ALL,IN에 대하여... (3)	2017.09.06

ORACLE 뷰(View)와 Inline 뷰 (0)	2017.09.08
ORACLE 무결성 제약조건 (0)	2017.09.07
ORACLE DDL( create, drop, alter, truncate ) (0)	2017.09.06
ORACLE 서브쿼리와 ANY,ALL,IN에 대하여... (3)	2017.09.06
ORACLE JOIN(Inner join,Outer Join,Self Join) (0)	2017.09.05

ORACLE JOIN(Inner join,Outer Join,Self Join) (0)	2017.09.05
ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP (0)	2017.09.05
ORACLE 문자열 함수 (0)	2017.09.04
ORACLE 집계함수, 숫자함수 (0)	2017.09.04
이채남저 오라클실습 자료(개인 보관용) (1)	2017.08.31

ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP (0)	2017.09.05
ORACLE 날짜함수, 변환함수, decode(), case() (0)	2017.09.04
ORACLE 집계함수, 숫자함수 (0)	2017.09.04
이채남저 오라클실습 자료(개인 보관용) (1)	2017.08.31
ORACLE DB 특수문자 제거, 숫자만 빼고 다 제거 쿼리문 (1)	2017.08.22

JSP FrontController 패턴, Command 패턴, RequestDispatcher를 이용한 forward에 대하여... (0)	2017.08.20
JSP JSTL(Java Standard Tag Liarary) (0)	2017.08.18
JSP EL(Expression Language) 표현언어 (0)	2017.08.16
JSP 파일업로드 (2)	2017.08.16
JSP 커넥션 풀(Connection Pool) (0)	2017.08.16

JSP MVC패턴을 이용한 게시판 만들기 1(개요) (0)	2017.08.28
JSP JSTL(Java Standard Tag Liarary) (0)	2017.08.18
JSP EL(Expression Language) 표현언어 (0)	2017.08.16
JSP 파일업로드 (2)	2017.08.16
JSP 커넥션 풀(Connection Pool) (0)	2017.08.16

스터디

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

'스터디 > JSP' 카테고리의 다른 글

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

'스터디 > JSP' 카테고리의 다른 글

'스터디 > JSP' 카테고리의 다른 글

'스터디 > JSP' 카테고리의 다른 글

'스터디 > JSP' 카테고리의 다른 글

'스터디 > JSP' 카테고리의 다른 글

티스토리툴바