===================================================================
[예]

SELECT
        LEVEL AS LEV
      , MENU.*
FROM
        TBL_MENU  MENU
WHERE               MENU_TYPE_CD = '1'
START WITH          MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID
ORDER SIBLINGS BY   MENU_SEQ

===================================================================
[풀이]

UP_MENU_ID 가 'TOP'인 것부터 순환고리를 시작하며
동일 레벨일경우(즉, UP_MENU_ID 가 'TOP'으로 하는 여러 ROW)는 MENU_SEQ가 먼저인것 부터 계층구조
그리고 마지막으로 MENU_TYPE_CD가 '1'인것만 최종 추출

===================================================================


◈ LEVEL 예약어 : depth

◈ CONNECT BY의 실행순서
    (1) START WITH 절
    (2) CONNECT BY 절
    (3) WHERE 절

◈
    START WITH : 시작위치 설정 (서브쿼리를 사용가능)
    CONNECT BY : 순환고리의 조건 (서브쿼리를 사용불가)
    ORDER SIBLINGS BY : 같은 레벨중 순환고리 순서를 정할때 사용

◈ PRIOR 의 위치
    (1) CONNECT BY PRIOR 자식컬럼 =       부모컬럼  ==> 부모에서 자식으로 트리 구성
    (2) CONNECT BY       자식컬럼 = PRIOR 부모컬럼  ==> 자식에서 부모으로 트리 구성

◈ 데이터가 많아질 경우....

    - START WITH MENU_ID = 'TOP'
          MENU_ID 컬럼에 index가 생성되어 있지 않는다면 속도를 보장할 수 없습니다.

    - CONNECT BY PRIOR MENU_ID = UP_MENU_ID
          역시 PRIOR 쪽의 컬럼값이 상수가 되기 때문에 UP_MENU_ID컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.

    - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로 표현하기가 어렵 습니다.

◈ 
    (1) '상품' 메뉴는 모두 출력 안함.
    SELECT
            LEVEL AS LEV
          , MENU.*
    FROM   
            TBL_MENU  MENU
    WHERE               MENU.MENU_NAME  <> '상품'
    START WITH          MENU.MENU_ID = 'TOP'
    CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID
    ORDER SIBLINGS BY   MENU_SEQ
   
    (2) '상품'메뉴 밑으로 모든 메뉴는 출력안함.
    SELECT
            LEVEL AS LEV
          , MENU.*
    FROM   
            TBL_MENU  MENU
    START WITH          MENU.MENU_ID    = 'TOP'
    CONNECT BY PRIOR    MENU.MENU_ID    = MENU.UP_MENU_ID
    AND                 MENU.MENU_NAME  <> '상품'
    ORDER SIBLINGS BY   MENU_SEQ
   
    ** 참고) 메뉴중 2LEVEL까지만 결과 출력
    (WHERE조건으로도 LEVEL <= 2가능함. 그러나 권하지는 않는다.
     왜? WHERE 조건은 모든 나온결과에 대해서 FILTER하지만 CONNECT BY절의 조건으로 넣으면 순환자체를 안한다. 즉 성능에 좋다)
    SELECT
            LEVEL AS LEV
          , MENU.*
    FROM   
            TBL_MENU  MENU
    START WITH          MENU.MENU_ID    = 'TOP'
    CONNECT BY PRIOR    MENU.MENU_ID    = MENU.UP_MENU_ID
    AND                 LEVEL  <= 2
    ORDER SIBLINGS BY   MENU_SEQ    
  
   
◈
    (1) 들여쓰기로 결과출력
    SELECT
            LPAD(’ ’, 4*(LEVEL-1)) || MENU.MENU_NAME
          , MENU.*
    FROM
            TBL_MENU  MENU
    WHERE               MENU_TYPE_CD = '1'
    START WITH          MENU.MENU_ID = 'TOP'
    CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID
    ORDER SIBLINGS BY   MENU_SEQ
   
    (2) 엑셀과 같이 셀 단위로 들여쓰기
    SELECT
             DECODE(LEV, '1', MENU_NAME, '') AS LEV1
           , DECODE(LEV, '2', MENU_NAME, '') AS LEV2
           , DECODE(LEV, '3', MENU_NAME, '') AS LEV3
           , DECODE(LEV, '4', MENU_NAME, '') AS LEV4
           , DECODE(LEV, '5', MENU_NAME, '') AS LEV5
    FROM
           (
             SELECT
                     LEVEL AS LEV
                   , MENU.*
             FROM    TBL_MENU  MENU
             WHERE               MENU_TYPE_CD = '1'
             START WITH          MENU.MENU_ID = 'TOP'
             CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID        
             ORDER SIBLINGS BY   MENU_SEQ
           )

저작자표시 비영리 변경금지

'Database > Oracle' 카테고리의 다른 글

오라클 백그라운드 프로세서  (0) 2010.09.28
oracle 에서 파티션(partition) 사용하기  (0) 2010.08.28
오라클 클라이언트 가 토드에서 안될때. ㅋ  (0) 2010.06.29
update 방법들  (0) 2010.06.16
Oracle optimizer goal, FIRST_ROWS 그리고 ALL_ROWS  (0) 2010.05.14
http://syaku.tistory.com/132
저작자표시 비영리 변경금지

'Database > Oracle' 카테고리의 다른 글

oracle 에서 파티션(partition) 사용하기  (0) 2010.08.28
계층형 쿼리 (CONNECT BY)  (0) 2010.07.14
update 방법들  (0) 2010.06.16
Oracle optimizer goal, FIRST_ROWS 그리고 ALL_ROWS  (0) 2010.05.14
◈ DB Link (오라클 원격DB 연결)  (0) 2010.05.13

Basic Update Statements
The Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify.

Update all records		 UPDATE <table_name>
SET <column_name> = <value>
CREATE TABLE test AS
SELECT object_name, object_type
FROM all_objs;

SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'OOPS';

SELECT DISTINCT object_name
FROM test;

ROLLBACK;

Update a specific record		 UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>
SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'LOAD'
WHERE object_name = 'DUAL';

COMMIT;

SELECT DISTINCT object_name
FROM test

Update based on a single queried value		 UPDATE <table_name>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name
  WHERE <column_name> <condition> <value>)
WHERE <column_name> <condition> <value>;
CREATE TABLE test AS
SELECT table_name, CAST('' AS VARCHAR2(30)) AS lower_name
FROM user_tables;

desc test

SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

-- this is not a good thing ...
UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name LIKE '%A%');
-- look at the number of rows updated

SELECT * FROM test;

-- neither is this
UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name NOT LIKE '%A%');

SELECT * FROM test;

UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name LIKE '%A%')
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;

Update based on a query returning multiple values 		UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
   SELECT (<column_name>, <column_name>)
   FROM <table_name>
   WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;
CREATE TABLE test AS
SELECT t. table_name, t. tablespace_name,  s.extent_management
FROM user_tables t, user_tablespaces s
WHERE t.tablespace_name = s. tablespace_name
AND 1=2;

desc test

SELECT * FROM test;

-- does not work
UPDATE test
SET (table_name, tablespace_name) = (
  SELECT table_name, tablespace_name
  FROM user_tables);

-- works
INSERT INTO test
(table_name, tablespace_name)
SELECT table_name, tablespace_name
FROM user_tables;

COMMIT;

SELECT *
FROM test
WHERE table_name LIKE '%A%';

-- does not work
UPDATE test t
SET tablespace_name, extent_management = (
  SELECT tablespace_name, extent_management
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name
  AND t.table_name LIKE '%A%');

-- works but look at the number of rows updated
UPDATE test t
SET (tablespace_name, extent_management) = (
  SELECT DISTINCT u.tablespace_name, u.extent_management
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name
  AND t.table_name LIKE '%A%');

ROLLBACK;

-- works properly
UPDATE test t
SET (tablespace_name, extent_management) = (
  SELECT DISTINCT (u.tablespace_name, u.extent_management)
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name)
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;

Update the results of a SELECT statement		 UPDATE (<SELECT Statement>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;
SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

UPDATE (
  SELECT *
  FROM test
  WHERE table_name NOT LIKE '%A%')
SET extent_management = 'Unknown'
WHERE table_name NOT LIKE '%A%';

SELECT * FROM test;
 
Correlated Update

Single column		 UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name> <alias>
  WHERE <alias.table_name> = <alias.table_name>);
conn hr/hr

CREATE TABLE empnew AS
SELECT * FROM employees;

UPDATE empnew
SET salary = salary * 1.1;

UPDATE employees t1
SET salary = (
  SELECT salary
  FROM empnew t2
  WHERE t1.employee_id = t2.employee_id);

drop table empnew;

Multi-column		 UPDATE <table_name> <alias>
SET (<column_name_list>) = (
  SELECT <column_name_list>
  FROM <table_name> <alias>
  WHERE <alias.table_name> <condition> <alias.table_name>);
CREATE TABLE t1 AS
SELECT table_name, tablespace_name
FROM user_tables
WHERE rownum < 11;

CREATE TABLE t2 AS
SELECT table_name,
TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAME
FROM user_tables
WHERE rownum < 11;

SELECT * FROM t1;

SELECT * FROM t2;

UPDATE t1 t1_alias
SET (table_name, tablespace_name) = (
  SELECT table_name, tablespace_name
  FROM t2 t2_alias
  WHERE t1_alias.table_name = t2_alias.table_name);

SELECT * FROM t1;
 
Nested Table Update
  See Nested Tables page
 
Update With Returning Clause

Returning Clause demo		 UPDATE (<SELECT Statement>)
SET ....
WHERE ....
RETURNING <values_list>
INTO <variables_list>;
conn hr/hr

var bnd1 NUMBER
var bnd2 VARCHAR2(30)
var bnd3 NUMBER

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000,
department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

print bnd1
print bnd2
print bnd3

rollback;
conn hr/hr

variable bnd1 NUMBER

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;

print bnd1

rollback;
 
Update Object Table

Update a table object		 UPDATE <table_name> <alias>
SET VALUE (<alias>) = (
  <SELECT statement>)
WHERE <column_name> <condition> <value>;
CREATE TYPE people_typ AS OBJECT (
last_name     VARCHAR2(25),
department_id NUMBER(4),
salary        NUMBER(8,2));
/

CREATE TABLE people_demo1 OF people_typ;

desc people_demo1

CREATE TABLE people_demo2 OF people_typ;

desc people_demo2

INSERT INTO people_demo1
VALUES (people_typ('Morgan', 10, 100000));

INSERT INTO people_demo2
VALUES (people_typ('Morgan', 10, 150000));

UPDATE people_demo1 p
SET VALUE(p) = (
  SELECT VALUE(q) FROM people_demo2 q
  WHERE p.department_id = q.department_id)
WHERE p.department_id = 10;

SELECT * FROM people_demo1;
 
Record Update

Update based on a record

Note: This construct updates every column so use with care. May cause increased redo, undo, and foreign key locking issues.

 UPDATE <table_name>
SET ROW = <record_name>
WHERE <column_name> <condition> <value>;
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

SELECT DISTINCT tablespace_name
FROM t;

DECLARE
 trec  t%ROWTYPE;
BEGIN
  trec.table_name := 'DUAL';
  trec.tablespace_name := 'NEW_TBSP';

  UPDATE t
  SET ROW = trec
  WHERE table_name = 'DUAL';

  COMMIT;
END;
/

SELECT DISTINCT tablespace_name
FROM t;
 
Update Partitioned Table

Update only records in a single partition		 UPDATE <table_name> PARTITION (<partition_name>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;
conn sh/sh

UPDATE sales PARTITION (sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;

http://psoug.org/reference/update.html
저작자표시 비영리 변경금지

'Database > Oracle' 카테고리의 다른 글

계층형 쿼리 (CONNECT BY)  (0) 2010.07.14
오라클 클라이언트 가 토드에서 안될때. ㅋ  (0) 2010.06.29
Oracle optimizer goal, FIRST_ROWS 그리고 ALL_ROWS  (0) 2010.05.14
◈ DB Link (오라클 원격DB 연결)  (0) 2010.05.13
Architecture  (0) 2010.05.04

■ ALL_ROWS  - throughput 최대화를 위한 optimizing을 한다.
  - full table scan을 선호하는 경향이 있다.
  - batch 프로그램 또는 report 출력 프로그램에 사용되는게 좋다.

■ FIRST_ROWS
  - response time 최소화를 위한 optimizing을 한다.
  - index scan을 선호하는 경향이 있다.
  - index scan을 선호하는 경향이 있기에 작은 테이블로부터 데이터를 찾을 때도 index scan을 해서
    full table scan을 하는 것보다 cost가 더 걸리는 단점이 있다.
  - user interaction 즉, 화면계에 사용하면 좋다.

■ FIRST_ROWS_N
  - Oracle 9i부터 도입된 파라미터.
  - FIRST_ROWS의 단점을 보안했다.
    단점이란, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000 처럼
    FIRST ROWS의 범위를 지정하도록 함으로써 index scan를 해야하는 지, full table scan을 해야하는 지에
    대한 선택을 더 현명하게 하도록 했다는 것이다.

저작자표시 비영리 변경금지

'Database > Oracle' 카테고리의 다른 글

오라클 클라이언트 가 토드에서 안될때. ㅋ  (0) 2010.06.29
update 방법들  (0) 2010.06.16
◈ DB Link (오라클 원격DB 연결)  (0) 2010.05.13
Architecture  (0) 2010.05.04
도메인 인덱스(Domain Index)  (0) 2010.04.28

+ Recent posts

티스토리툴바