악덕고용주의 개발 일기

[ 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

--[ 시퀀스(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) & 인라인 뷰 ]

-- 뷰(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 PL/SQL 개념 - (1)

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

ORACLE 시퀀스(sequence)

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

ORACLE 뷰(View)와 Inline 뷰

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

+ Recent posts

티스토리툴바