P L / S Q L에서는 SQL 문을 사용하여 O racle 데이터와 그 데이터를 처리하는 흐름 제어문을 조작할 수 있으며 또한, 상수 및 변수를 선언하고 프로시저 및 함수를 정의하며 런타임 오류를 잡을 수 있습니다. PL/SQL에서는 이와 같이 S Q L의 데이터 조작능력과 절차적 언어의 데이터 처리 능력을 결합합니다.




P L / S Q L은 블록 구조로 이루어진 언어입니다. 즉, PL/SQL 프로그램을 구성하는 기본 단위(프로시저, 함수, 익명 블록)는 임의의 수의 중첩된 하위 블록을 포함할 수 있는 논리 블록입니다. 



블록(또는 하위 블록)을 사용하여 논리적으로 관련된 선언과 명령문을 그룹화하여 사용되는 위치 가까이에 선언을 넣을 수 있습니다. 이러한 선언은 해당 블록에 대해 지역적이며 그 블록이 완료될 때 소멸됩니다.


[ D E C L A R E

-- d e c l a r a t i o n s ]

B E G I N

-- s t a t e m e n t s

[ E X C E P T I O N

-- h a n d l e r s ]

E N D ;


PL/SQL 블록은 선언부, 실행부, 예외 처리부, 세 부분으로 구성.

(PL/SQL에서 예외란 경고 또는 오류 조건을 의미합니다.) 이 중 실행부는 반드시 필요합니다.


선언부가 우선하며 일단 선언되면 항목은 실행부에서 조작할 수 있습니다. 실행 도중 발생된 예외는 예외 처리부에서 처리됩니다.


하위 블록은 PL/SQL 블록이나 하위 프로그램의 실행 및 예외 처리부에서는 중첩될수 있지만 선언부에서는 중첩될 수 없습니다. 또한 선언부의 임의의 블록에서는 로컬 하위 프로그램을 정의할 수 있지만 정의된 블록에서만 해당 로컬 하위 프로그램을 호출할 수 있습니다.




변수와 상수


PL / SQL에서는 상수와 변수를 선언한 다음 SQL과 표현식 사용이 가능한 절차문에서 상수와 변수를 사용할 수 있습니다. 

상수나 변수를 먼저 선언해야만 다른 선언문을 비롯한 다른 명령문에서 참조할 수 있습니다.




 

변수 선언


변수는 CHAR, DATE 또는 NUMBER와 같은 SQL 데이터 유형이나 BOOLEAN 또는 BINARY INTEGER와 같은 PL/SQL 데이터 유형을 가질 수 있습니다. 

예를 들어, 4자리 숫자를 갖는 partno라는 변수와 TRUE나 FALSE와 같은 부울 값을 갖는 instock이라는 변수는 다음과 같이 선언합니다.


part_no NUMBER(4);

in_stock BOOLEAN;


또한 TABLE, VARRAY, RECORD 복합 데이터 유형을 사용하여 중첩 테이블, 가변 길이 배열(약어는 가변 배열)과 레코드를 선언할 수 있습니다.





변수에 값 할당


두 가지 방법으로 변수에 값을 할당할 수 있습니다. 첫 번째 방법은 콜론에 등호를 붙인 할당 연산자( := )를 사용하는 것입니다. 

다음 예와 같이 연산자의 좌변에는 변수를,우변에는 표현식을 넣습니다.


tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR(’750 dollars’, 1, 3));

valid := FALSE;



두 번째 방법은 데이터베이스 값을 선택하거나 인출하여 변수에 값을 할당하는 것입니다. 다음은 사원의 급여를 선택할 때 10 %의 보너스를 계산하는 예입니다.


SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;


bonus 변수를 다른 계산에서 사용하거나 그 값을 데이터베이스 테이블에 삽입할 수있습니다.





상수 선언


상수 선언 방법은 변수 선언과 유사하나 CONSTANT 키워드를 추가하고 즉시 해당 상수에 값을 할당해야 합니다. 이후에는 더 이상 상수 값을 할당할 수 없습니다. 

다음은 credit_limit이라는 상수를 선언하는 예입니다.


credit_limit CONSTANT REAL := 5000.00;






커서


커서라는 PL/SQL 구성을 사용하여 작업 영역의 이름을 지정하고 저장된 정보에 액세스 할 수 있으며, 암시적 커서와 명시적 커서 두 종류가 있습니다. PL/SQL은 하나의 행만 반환하는 질의를 포함하여 모든 SQL 데이터 조작문에 대해 커서를 암시적으로 선언하고 하나 이상의 행을 반환하는 질의에는 다음 예와 같이 개별적으로 행을 처리할 커서를 명시적으로 선언할 수 있습니다.


DECLARE

  CURSOR c1 IS

     SELECT empno, ename, job FROM emp WHERE deptno = 20;


복수 행 질의 처리는 파일 처리와 유사합니다. 예를 들어, COBOL 프로그램은 파일을 열고 레코드를 처리한 다음 파일을 닫습니다. 마찬가지로 PL/SQL 프로그램은 커서를 열고 질의에 의해 반환된 행을 처리한 다음 커서를 닫습니다. 파일 포인터가 열린 파일에서의 현재 위치를 표시하는 것처럼 커서는 결과 집합에서 현재 위치를 표시합니다.

OPEN, FETCH, CLOSE 문을 사용하여 커서를 제어합니다. OPEN 문은 커서와 연관된 질의를 실행하여 결과 집합을 식별하고 첫 번째 행 앞에 커서를 놓습니다. FETCH 문은 현재 행을 읽어 들인 후 다음 행으로 커서를 보냅니다. C L O S E 문은 마지막 행 처리가 끝나면 커서를 사용할 수 없도록 합니다.




커서 FOR 루프


명시적 커서가 필요한 대부분의 경우에는 OPEN, FETCH, CLOSE 문 대신 커서 FOR루프를 사용하여 코딩을 단순화할 수 있습니다. 커서 FOR 루프는 루프 인덱스를 데이터베이스에서 인출된 행을 나타내는 레코드로 암시적으로 선언한 다음 커서를 열고 결과 집합에서 행 값을 레코드 필드로 반복하여 인출한 후 모든 행이 처리되면 커서 를 닫습니다. 다음은 커서 FOR에 있는 루프가  emp_rec를 레코드로 암시적으로 선언하는 예입니다.


DECLARE

    CURSOR c1 IS

        SELECT ename, sal, hiredate, deptno FROM emp;

...

BEGIN

   FOR emp_rec IN c1 LOOP

       ...

       salary_total := salary_total + emp_rec.sal;

   END LOOP;


레코드의 각 필드를 참조하려면 구성 요소 선택자 역할을 하는 점( . ), 점 표기법을 사용합니다.




커서 변수


커서 변수는 커서와 마찬가지로 복수 행 질의의 결과 집합에서 현재 행을 가리키지만, 커서와 달리 특정 질의와 결합되지 않고 임의의 유형과 호환 가능한 질의에 대해 열릴 수 있습니다. 커서 변수는 새로운 값을 할당하고 오라클 데이터베이스에 저장된 하위 프로그램에 전달할 수 있다는 점에서 전형적인 PL/SQL 변수로 데이터 검색을 중앙 집중화할 수 있는 융통성 있고 편리한 방법을 제공합니다.

일반적으로 커서 변수를 형식 매개변수 중 하나로 선언한 내장 프로시저로 전달하여 커서 변수를 엽니다. 다음 프로시저는 선택된 질의에 대해 커서 변수 generic_cv를 엽니다.


PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade;

END IF;






속성


%TYPE

%TYPE 속성은 변수 또는 데이터베이스 열의 데이터 유형을 제공합니다. 이 속성은 데이터베이스 값을 저장할 변수를 선언할 때 특히 유용합니다. 예를 들어, books라는 테이블에 title이라는 열이 있을 경우 title 열과 동일한 데이터 유형으로 mytitle이라는 변수를 선언하려면 다음과 같이 점 표기법과 %TYPE 속성을 사용하면 됩니다.


my_title books.title%TYPE;


title의 정확한 데이터 유형을 몰라도 되고 둘째, title의 데이터베이스 정의를 (예를 들어, 더 긴 문자열로) 변경하는 경우 mytitle의 데이터 유형도 실행 시간에 그에 따라 변경됩니다.


%ROWTYPE

PL/SQL에서 레코드는 데이터를 그룹화하는데 사용되며 데이터 값을 저장할 수 있는 여러 관련 필드로 구성됩니다. 

%ROWTYPE 속성은 테이블의 한 행을 나타내는 레코드유형을 제공하며 테이블에서 선택하거나 또는 커서나 커서 변수에서 인출한 데이터행 전체를 저장할 수 있습니다.

행의 열과 레코드의 해당 필드는 동일한 이름과 데이터 유형을 가집니다. 

다음은 dept_rec 레코드를 선언하는 예입니다. dept_rec 레코드의 필드는 dept 테이블의 열과 동일한 이름 및 데이터 유형을 가집니다.


DECLARE

dept_rec dept%ROWTYPE; -- declare record variable


필드를 참조하려면 다음 예와 같이 점 표기법을 사용합니다.


my_deptno := dept_rec.deptno;


사원의 이름, 급여, 고용일, 직책을 읽어 들이는 커서를 선언하려는 경우 다음과 같이 %ROWTYPE을 사용하여 동일한 정보를 저장하는 레코드를 선언할 수 있습니다.


DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE; -- declare record variable that represents a row fetched from the emp table


다음 명령문을 실행하면


FETCH c1 INTO emp_rec;


emp_rec의 ename 필드에 emp 테이블의 ename 열 값이 지정되고 sal 필드에 sal열 값이 지정됩니다. 












예제 테이블 >

 

CREATE TABLE SCOTT.DEPT
(
 DEPTNO   NUMBER (2) NOT NULL,
 DNAME    VARCHAR2 (14),
 LOC      VARCHAR2 (13)
)

;

CREATE TABLE SCOTT.EMP
(
 EMPNO      NUMBER (4) NOT NULL,
 ENAME      VARCHAR2 (10),
 JOB        VARCHAR2 (9),
 MGR        NUMBER (4),
 HIREDATE   DATE,
 SAL        NUMBER (7,2),
 COMM       NUMBER (7,2),
 DEPTNO     NUMBER (2),
 CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO)
)

;

CREATE TABLE SCOTT.DEPT
(
 DEPTNO   NUMBER (2) NOT NULL,
 DNAME    VARCHAR2 (14),
 LOC      VARCHAR2 (13)
)

;

 

 

 


예제 데이터 >

 

 


 




 

 

'DBMS > 오라클 PLSQL' 카테고리의 다른 글

2. 기본  (0) 2015.02.13
프로시져 실행 샘플 - OUT 값이 여러행인 경우  (0) 2015.01.21
pl/sql, procedure, function  (0) 2013.01.27



CREATE OR REPLACE PROCEDURE SCOTT.SP_TEST
(
     P_PJT_CD               IN      VARCHAR2,
     PC_CURSOR            OUT  SYS_REFCURSOR 
) IS
    V_RESULT_CD         VARCHAR2(30 CHAR) := '';
    V_RESULT_MSG       VARCHAR2(500 CHAR) := '';    
    v_CNT                     NUMBER(22):=0;
    v_id                         varchar(15 char) :='';
    E_USER_ERROR_NO_DATA_FOUND    EXCEPTION;
    V_CODE                  VARCHAR(30 CHAR) := '';
    V_MSG                    VARCHAR(30 CHAR) := '';
BEGIN  
 
    dbms_output.put_line('Start!!!!!-->');
 
    begin
        select row_id
          into v_id
          from TEST
         where row_id ='9999';
--      exception
--          when no_data_found then
--                dbms_output.put_line('no data found');
--                V_CODE 
--                RAISE E_USER_ERROR_NO_DATA_FOUND;
    end;

    OPEN PC_CURSOR
    FOR   
        SELECT
                '0000'           AS RESULT_CD,
                '111'            AS RESULT_MSG
        FROM DUAL;   
    
    dbms_output.put_line('end-->'||V_RESULT_MSG);
 
--EXCEPTION
--   WHEN OTHERS THEN
--        OPEN PC_CURSOR
--        FOR   
--        SELECT
--                '333'           AS RESULT_CD,
--                '444'            AS RESULT_MSG
--        FROM DUAL;  
  
END;
 
-------------------------------------------------------------

DB툴에서 아래 내용을 실행 
------------------------------------------------
-- 프로시져 실행 
-------------------------------------------------
DECLARE 
      i_counter integer;
      v_cursor SYS_REFCURSOR;  
      v_RESULT_CD varchar2(100);  
      v_RESULT_MSG varchar2(500);
BEGIN  
  SCOTT.SP_TEST( P_PJT_CD    => '',
                            PC_CURSOR   => v_cursor);
  i_counter := 0;
  LOOP
    FETCH v_cursor
    INTO v_RESULT_CD,v_RESULT_MSG;
    EXIT WHEN v_cursor%NOTFOUND;
        i_counter := i_counter + 1;
        DBMS_OUTPUT.PUT_LINE('결과:'||v_RESULT_CD|| v_RESULT_MSG);
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('COUNT: ' || i_counter);
  CLOSE v_cursor;
END;
 


 





'DBMS > 오라클 PLSQL' 카테고리의 다른 글

2. 기본  (0) 2015.02.13
1. PL/SQL 개요  (0) 2015.02.11
pl/sql, procedure, function  (0) 2013.01.27


인터넷 검색하면서 발견..

어느 분께서 정리하신 내용인 듯 합니다.


index란?

검색을 빠른 속도로 하기 위해서 사용됩니다.
SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체임.

--index 사용해야할 경우
테이블에 행의 수가 많을때
조건절인 where문에 해당 컬럼이 많이 사용될때
검색 결과가 데이터의 2% ~ 4% 정도일때(적은 양의 컬럼을 가져올때)
join에 자주 사용되는 컬럼
NULL을 포함하는 컬럼이 많은 경우

--index 사용하지 말아야 하는 경우
테이블에 행의 수가 적을때
where 문에 해당 컬럼이 자주 사용되지 않을 때
검색 결과가 전체 데이터의 10% ~ 15% 이상 높을 때(많은 양의 컬럼을 가져올대)
테이블에 DML 작업이 많은 경우(입력,수정,삭제 등이 자주 발생할때)

--index 딕션너리
USER_INDEXES :인덱스 이름 및 고유성을 포함
USER_IND_COLUMNS : 인덱스 이름, 테이블 이름, 열 이름 포함

--index 생성법
create index index명 on table명(index걸어줄 table에 colum명);
ex)create index idx_emp_ename on emp(ename);

--index 제거
drop index index명
ex)drop index idx_emp_ename;

--index 재생성
alter index index명 rebuild;
ex)alter index idx_emp_ename rebuild;

--index 조회(index를 모두 보여준다.)
select object_name, object_type
from user_objects
where object_type ='INDEX';

효율성이 떨어지면 인덱스를 재 생성해 주어야 한다.
컬럼의 데이터가 입력,수정,삭제될 경우 해당 컬럼에 의해 생성된 인덱스에 대해서 재구성 해야 된다.

--index 종류
Unique Index(고유 인덱스) - 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스
NonUnique Index(비고유 인덱스) - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스
Single Index(단일 인덱스)
Composite Index(결합 인덱스)
 결합 인덱스 우선순위
 1순위 - 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정
 2순위 - 랜덤 액세스를 고려한 인덱스 컬럼 선정
 3순위 - 정렬 제거를 위한 인덱스 컬럼 선정
 4순위 - 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정
 
 위의 우선순위 구성 규칙의 이유는 디스크 I/O를 가장 적게 발생시키기 위해서다.
 
Function Based Index(함수 기반 인덱스)
 표현식을 기반으로 하는 인덱스
 인덱스 표현식은 테이블 열, 상수, SQL 함수 및 사용자가 정의한 함수로부터 생성


1.컬럼의 중간 부분의 검색
 create index from_loc_idx on orders(sbstr(ship_id,5,3));
 create index repair_loc_idx on orders(substr(ship_id,3,2,),ord_date);

 2.조인 연결고리 컬럼이 대응하지 않는 경우의 해결
 ...
 from item_group x,items y
 where x.class1||x.class2||x.class3 = y.group_cd....
    ▽
 ...
 from item_group x, items y
 where x.class1 = substr(y.group_cd,1,2)
 and x.class2 = substr(y.group_cd,3,2)
 and x.class3 = substr(y.group_cd,5,3)
 ...
    ▽ 
 create index group_cd_idx_ on item_group(class1||class2||class3);

 3.일자 컬럼이 분활된 경우의 해결
 where sal_yyyy >='2005' and sal_mm >='12' and sal_dd>='10' (x)
   ▽
 where sal_yyyy||sal_mm||sal_dd >='20051210' (o)
   ▽
 create index sal_date_idx on  sales(sal_yyyy||sal_mm||sal_dd);
 
 4.데이타 타입이 상이한 조인 컬럼
 create index deptno_idx on emp(to_number(deptno));

 5.조인 컬럼이 경우에 따라 달라지는 경우
 ...
 from sales s, department d
 where d.deptno = (case when sal_type = 1 then sal_dept else agent_no end)
 and d.location = 'BUSAN'
 '''
     ▽
 create index deptno_idx on sales(case when sal_type = 1 then sal_dept else agent_no end); 
 
 6.대/소문자나 공백이 혼재된 컬럼의 검색
 create index ename_upper_ix on employees(upper(ename));(대문자)
   
 create index ename_upper_ix on employees(upper(replace(ename,''));(대문자/공백)
 
 7.NULL 값을 치환하여 검색
 ...
 where :input_date between start_date and nvl(end_date,'99991231');
 ...
    ▽
 create index end_date_idx on account_history(nvl(end_date,'99991231'),start_date);
 
 8.복잡한 계산 결과의 검색
 create index order_amount_idx on order_items(item_cd,(order_price - nvl(order_discount,0)) * order_count));
    
 select/*+index_desc(x order_amount_idx)*/ *
 from ordero_items x
 where item_cd =:b1
 and rownum <= 100;
 
 9.기간, 컬럼 길이 검색
 create index item_idx on activities (expire_date - start_date); (기간)
 create index source_length_idx on print_media(text_length(source_text));(컬럼 길이)
 
 10.배타적 관계의 유일성 보장
 create unique index official_id_idx on customers
  (case when cust_type=1 then resident_id else business_id end);
 
 select * from customers
 where(case when cust_type=1 then resident_id else business_id end) =:b1;
 함수기반 인덱스는 버전에 따라 제약사항에 차이가 있으므로 관련 메뉴얼을 참조해야함.
 
ex)DATE TYPE 과 함수기반인덱스 활용 예
DATE TYPE으로 칼럼을 설계했을경우 일자만 비교하고 싶으면 TRUNC(DATE_COL)이렇게 하면됨
그런데 이런 함수를 쓰면 INDEX를 사용할 수 없다.(해결방법 Function-Based index)

create index ix_t on t(trunc(date_col)); (여기서 date_col은 date_type의 컬럼명)
쿼리문
select * from t
where trunc(date_col) = '20091001';
이렇게 하면 된다.
 
trunc(date_col)이 부분을 간편하게 이용하려면 뷰를 이용

create view v_t
as
select trunc(date_col) as date_col from t;
이러면 , 쿼리문이
select * from t
where date_col = '20091001';


[고유인덱스]-단일
 create unique index idx_dept_deptno on dept(deptno);
[비고유인덱스]-단일
 create index idx_dept_loc on dept(loc);
[결함 인덱스]
 create index ind_dept_com on dept(deptno,loc);
[함수기반 인덱스]

 create table emp as select * from emp;
 검색 조건으로 sal*12 = 3600 을 지정하는 경우, 이럴경우 sal*12를 인덱스를 탈 수 없을수 있다.
 이럴때 구성하는 인덱스가 함수기반 인덱스 입니다.
 create index idx_emp_annsal on emp(sal*12);(함수기반 인덱스 생성)
 
 select index_name, column_name
 from user_ind_columns
 where table_name='emp'; (인덱스 생성 확인)

--where 절의 사용에 따른 인덱스 활용
 점 조건 :IN,= -->해당 연산자는 하나의 점만을 의미
 선분 조건:LIKE,BETWEEN,<,> -->해당 조건을 만족하는 모든 실수를 의미

 조건에 사용된 연산자에 의해 액세스해야 하는 처리 범위 차이

 점 조건 + 점 조건 : 두 조건에 의해 처리 범위 감소
 점 조건 + 선분 조건 : 두 조건에 의해 처리 범위 감소
 선분 조건 + 선분 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소
 선분 조건 + 점 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소
 
 단일 인덱스보다는 결합 인덱스를 사용하고 점 조건을 우선적으로 인덱스 조건으로 해야 성향상을 기대할 수 있다.

--index 사용/불가 예

* 인덱스를 타지 않는 SQL
1. 인덱스 컬럼 절의 변형
2. 내부적인 데이터 변환
3. NULL 조건의 사용
4. 부정형 조건의 사용
5. LIKE 연산자 사용
6. 최적기가 판단

1 - 인덱스 컬럼 절의 변형
SQL> select ename from emp where sal * 2.1 > 950 --인덱스 사용불가
SQL> select ename from emp where sal > 950 /2.1  --인덱스 사용가능
SQL> select ename from emp where to_char(hiredate,'DDMMYY') = '250884' --인덱스 사용불가
SQL> select ename from emp where hiredate = to_date('250884','DDMMYY') --인덱스 사용가능

-> 인덱스 컬럼에 변형을 가하면은 사용할수 없습니다. 단 변형 가능하더라도 쓰고 싶다면은
    말리지는 않겠지만 create index .... on emp to_char(hiredate,'DDMMYY') 이렇게 하시면 됩니다.

2 - 내부적인 데이터 변환
SQL> select * from emp where hiredate ='14-JAN-85' --인덱스 사용불가
SQL> select * from emp hiredate = to_date('71-10-22','YY/DD/DD') --인덱스 사용가능
SQL> select * from emp where empno = '7936' --인덱스 사용불가
SQL> select * from emp where empno = to_number('7936') --인덱스 사용가능

-> 내부적인 데이터변환에서 가장 많이 실수하는 부분은 문자값 데이터타입을 갖는 컬럼에
    '값' -> 값 이렇게 하시는분이 많습니다. 딱맞는 데이터타입을 주세요 ^_^

3 - NULL 조건의 사용
SQL> select ename from emp where comm is null --인덱스 사용불가
SQL> select ename from emp where comm is not null --인덱스 사용불가
SQL> select ename from emp where ename > '' --인덱스 사용가능
SQL> select ename from emp where comm >= 0 --인덱스 사용가능

-> NULL조건으로 검색한다는 가정하에는 거의 인덱스 풀 스캔이 일어나겠죠. 적절히 사용합씨다.


4 - 부정형 조건의 사용
SQL> select ename from emp where deptno != 30 --인덱스 사용불가
SQL> select ename from emp where deptno < 30 and deptno > 30 --인덱스 사용가능

-> 논리적으로 부정형을 이용하여 인덱스를 사용하겠다는것은 말이 안되죠...
    이 쿼리문도 적절히 사용합씨다.

5 - Like 연산자 사용
SQL> select * from emp where ename like 'S%' --인덱스 사용가능
SQL> select * from emp where ename like '%S%' --인덱스 사용불가

-> %S% 부분을 꼭 쓰고싶다면은 이렇게 하세요 앞에부분을 다 넣는거죠. AS%, BS%...
    요즘 홈피를 보면 본문 찾기는 거의 없어져가고 있죠. 엔코아 경우 주제어를 검색을..


6 - 최적기가 판단

-> RBO경우 무조건 타죠 있으면은 이눔은 워낙 법을 좋아해서리..CBO는 통계값을 기준으로
    하기 때문에 DBA가 잘 해야겠죠. 그럼 우리가 판단하게 할라면은 HINT를 써서 이눔들을
    인도해야죠... 오늘도 전도를 ^____^


마지막으로 인덱스를 이용한 sort 회피 방법에 대해서 알아보겠습니다.
SQL> select empno, ename, job, comm from emp order by empno --sort 정렬
SQL> select empno, ename, job, comm from emp where empno >= 0 --sort 정렬제거

-> 인덱스는 값이 정렬되어 들어가있는 구조이기 때문에 처음부터 작은값 맨 끝값은
    최대값이 있겠죠. 잘 사용하면 최고의 튜닝이 되겠죠.

--Optimizer Hint Syntax(Hint 사용)

-----> SELECT -----> /*+ hint 절*/ ------->
DELETE
UPDATE
또는
-----> SELECT -----> --+ hint 절 ------->
DELETE
UPDATE

Hint 절에는 다음 조건들을 부여할 수 있다.

1. FULL(table_name)
: table을 full scan하길 원할 때 사용함.

2. INDEX(table_name index_name[index])
: 특정 index를 사용하도록 할 때 사용함.

3. INDEX_ASC(table_name index_name[index])

4. INDEX_DESC(table_name index_name[index])

5. AND_EQUALS(table_name index_name index_name[index])

6. ROWID(table_name)

7. CLUSTER(table_name)

8. HASH(table_name)

9. ORDERED

10. USE_MERGE(table_name)

11. USE_NL(table_name)

Hint를 사용하여 특정 index를 사용한 update의 예

(이 결과는 7.3 이상에서 제공되는 auto trace를 사용하는 방법이나
execution plan 또는 tkprof를 이용하여 확인할 수 있다.)

Update /*+ INDEX (e1 e_job) */
Emp e1
SET sal =
(SELECT --+ INDEX (e1 e_dept)
(e1.sal + AVG(e2.sal))/2
FROM emp e2
WHERE e2.deptno = e1.deptno)
WHERE job = 'CLERK'
AND deptno BETWEEN 10 AND 40;



'DBMS > 오라클기타' 카테고리의 다른 글

두 테이블간 update  (0) 2015.07.20
다중update  (0) 2015.06.08
TYPE 오브젝트  (0) 2015.01.21
오라클 행을 열로  (0) 2014.12.17
다중 update  (0) 2014.12.17



-- FN_REPORT_01

DROP TYPE T_REPORT01_TAB;


CREATE OR REPLACE TYPE T_REPORT01_OBJ AS OBJECT

(

    COL01    VARCHAR2(500 CHAR),

    COL02    VARCHAR2(50  CHAR),

    COL04    VARCHAR2(50  CHAR),

    COL05    VARCHAR2(50  CHAR),

    COL06    VARCHAR2(50  CHAR),

    COL07    VARCHAR2(50  CHAR),

    COL08    VARCHAR2(50  CHAR)

);

CREATE OR REPLACE TYPE T_REPORT01_TAB AS TABLE OF T_REPORT01_OBJ;


--SELECT * FROM TABLE(FN_REPORT_01('1-3I9PQT')) T1;


CREATE OR REPLACE FUNCTION 계정.FN_REPORT_01

(

    P_NAME IN VARCHAR2

) RETURN T_REPORT01_TAB PIPELINED

IS

 

BEGIN

    FOR ROWS_CON IN (

        SELECT A.COL01                                        AS COL01 --"모델명"

             , A.COL02                                          AS COL02--"환율"

             , TO_CHAR(A.COL03,'999,999,999,999,999')       AS COL04 --"계약가(KRW)"

             , A.COL05                                          AS COL05 --"통화"

             , TO_CHAR(A.COL03_CURR,'999,999,999,999,999')  AS COL06 --"계약가(통화)"

             , TO_CHAR(A.COL07 ,'999,999,999,999,999') ||'<br>('||

                DECODE(A.COL03,0,0,ROUND(( 100 * A.COL07 )/A.COL03,0))||'%)'       AS COL07 --"총원가"

             , TO_CHAR(A.COL08,'999,999,999,999,999') ||'<br>('||

                DECODE(A.COL03,0,0,ROUND(( 100 * A.COL08 )/A.COL03,0))||'%)'  AS COL08 --"세전이익"

         FROM (..



'DBMS > 오라클기타' 카테고리의 다른 글

다중update  (0) 2015.06.08
인덱스  (0) 2015.01.21
오라클 행을 열로  (0) 2014.12.17
다중 update  (0) 2014.12.17
순환구조  (0) 2013.06.24

 

 

 

 

 

행을 열로

http://blog.ngelmaum.org/entry/research-note-oracle-story-the-conversion-of-rows-and-columns-in-the-oracle

 


 

top n query 오라클에서

http://blog.ngelmaum.org/entry/research-note-oracle-topn-queries


DBMS_RANDOM 패키지 사용

http://blog.ngelmaum.org/entry/research-note-using-oracle-dbms-random-package



'DBMS > 오라클기타' 카테고리의 다른 글

인덱스  (0) 2015.01.21
TYPE 오브젝트  (0) 2015.01.21
다중 update  (0) 2014.12.17
순환구조  (0) 2013.06.24
25가지 SQL작성법  (0) 2013.06.09

+ Recent posts