[oracle] sys_connect_by_path()

2014. 4. 8. 13:54 from DB

SYS_CONNECT_BY_PATH( 컬럼명, '구분자')

 

오라클 계층형 쿼리함수이다.

첫번째 인자에 컬럼명이 두번째 인자는 구분되는 문자열값을 받는다.

 

ORA-30004 : SYS_CONECT_BY_PATH 함수를 사용할 때 열 값의 일부로 분리자를 사용할 수 없습니다.

 

 

함수에서 받는 컬럼명의 해당 값이 해당 구분자를 포함할때 나는 오류이다.

구분자를 바꾸거나 해당컬럼값에 입력되는 값중 구분자를 제한해야 한다.

 

 

 


 

WITH DT AS 

 

    (SELECT TO_DATE('2014-04-02', 'YYYY-MM-DD') - 7 * (LEVEL-1) AS DT1, LEVEL FROM DUAL

     CONNECT BY LEVEL <= (TO_DATE('2014-04-02', 'YYYY-MM-DD')-(TO_DATE('2014-02-05', 'YYYY-MM-DD')) /7+1

    )

    SELECT CHECK_ID

                 CHECK_CATEGORY,

                 CHECK_CATEGORY_NAME,

                 COUNT(*) OVER(PARTITION BY CHECK_CATEGORY) CHECK_CATEGORY_CNT,

                 ROW_NUMBER() OVER(PARTITION BY CHECK_CATEGORY ORDER BY CHECK_ID) CHECK_CATEGORY_RNUM,

                 CHECK_MANAGER,

                 CHECK_MANAGER_NM,

                 CHECK_CONTENT,

                 CHECK_PARAMETER_TARGET,

                 CHECK_PARAMETER_VALUE,

                 CHECK_PURPOSE_TARGET,               

                 CHECK_PURPOSE_VALUE,        

                 STATE_RESULT,        
       FROM (

                  SELECT CHECK_ID,

                               CHECK_CATEGORY,

                               CHECK_CATEGORY_NAME,

                               CHECK_MANAGER,

                               CHECK_MANAGER_NM,

                               CHECK_CONTENT,

                               CHECK_PARAMETER_TARGET,

                               CHECK_PARAMETER_VALUE,

                               CHECK_PURPOSE_TARGET,               

                               CHECK_PURPOSE_VALUE,        

                               SUBSTR(MAX(sys_connect_by path(STATE_RESULT_VALUE, ';') STATE_RESULT

                     FROM  (

                                SELECT CL.CHECK_ID,

                                             CL.CHECK_CATEGORY,

                                             pkg_common.getCodeValueById(CL.CHECK_CATEGORY) CHECK_CATEGORY_NAME,

                                             CL.CHECK_MANAGER,

                                             pkg_common.getCodeValueById(CL.CHECK_MANAGER) CHECK_MANAGER_NM,

                                             CL.CHECK_CONTENT,

                                             CL.CHECK_PARAMETER_TARGET,

                                             CL.CHECK_PARAMETER_VALUE,

                                             CL.CHECK_PURPOSE_TARGET,

                                             CL.CHECK_PURPOSE_VALUE,

                                             ROW_NUMBER() OVER(PARTITION BY CL.CHECK_ID ORDER BY CL.DT1 DESC) RNUM

                                   FROM (

                                             SELECT * FROM TB_SECU_DAY_CHECK_LIST, DT

                                              WHERE NVL(TB_ITP_SECU_DAY_CHECK_LIST.CHECK_DEL_YN, 'N') = 'N'

                                             ) CL,

                                             (

                                             SELECT STATE_DATE,

                                                          CHECK_ID,

                                                          NVL(STATE_RESULT_VALUE, ' ') STATE_RESULT_VALUE

                                                FROM TB_ITP_SECU_DAY_CHECK_STATE

                                              WHERE STATE_DATE BETWEEN TO_DATE('2014-02-05', 'YYYY-MM-DD') AND

                                                          TO_DATE('2014-04-02', 'YYYY-MM-DD')

                                              ) STATE

                                  WHERE CL.CHECK_ID = STATE.CHECK_ID(+)

                                              AND CL.DT1 = STATE.STATE_DATE(+)

                                  ) SL

                       WHERE 1 = 1

                                   START WITH RNUM   =   1

                                   CONNECT BY PRIOR RNUM = RNUM - 1

                                   AND PRIOR CHECK_ID = CHECK_ID

                                   GROUP BY CHECK_ID,

                                                    CHECK_CATEGORY,

                                                    CHECK_CATEGORY_NM,

                                                    CHECK_MANAGER,  

                                                    CHECK_MANAGER_NM,

                                                    CHECK_CONTENT,    

                                                    CHECK_PARAMETER_TARGET,

                                                    CHECK_PARAMETER_VALUE,

                                                    CHECK_PURPOSE_TARGET,               

                                                    CHECK_PURPOSE_VALUE

                                   ORDER BY CHECK_CATEGORY, CHECK_ID

                         ) WHERE 1=1 ;

 

'DB' 카테고리의 다른 글

테이블 insert 권한 줄때 잊지말고 시퀀스도 select 권한도 함께!  (0) 2014.04.03
PL/SQL  (0) 2014.03.06
SQL 언어의 종류  (0) 2014.03.06
데이터베이스 설계단계  (0) 2014.03.06
SQL 표준 개정이력  (0) 2014.03.06
Posted by 에시드 :

A계정 테이블의 select, insert 권한을 B계정에게 주었다.

 

grant insert, select on a.tb_example to b

 

 

B계정에서 select는 잘 되는데, insert가 되지 않았다.

인서트 시마다  아래와 같은 ORA-00942 오류

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다.

00942. 00000 - "table or view dose not exist"

 

 

 

해당 테이블은 pk가 시퀀스에 물려있는데, 해당계정로그인 후

select a.tb_example_seq.NEXTVAL from dual -- 해보았는데 똑같이 ORA-00942

 

시퀀스에 select 권한을 주지 않아서 발생된 문제이다.

시퀀스 오브젝트도 권한을 주어야 한다.

 

grant select on a.tb_example_seq to b

 

 

 

 

'DB' 카테고리의 다른 글

[oracle] sys_connect_by_path()  (0) 2014.04.08
PL/SQL  (0) 2014.03.06
SQL 언어의 종류  (0) 2014.03.06
데이터베이스 설계단계  (0) 2014.03.06
SQL 표준 개정이력  (0) 2014.03.06
Posted by 에시드 :

PL/SQL

2014. 3. 6. 14:57 from DB

비교문과 반복문을 사용할 수 없는 SQL언어의 단점을 사용가능하게 만든것이 PL/SQL (Procedural Language-SQL) 언어

 

SQL의 단점

1. 반복처리를 할 수 없다. (loop)

2. 비교처리를 할 수 없다. (if)

3. Error처리를 할 수 없다. (exception처리)

4. SQL문을 캡슐화 할 수 없다.

5. 변수선언을 할 수 없다.

6. 실행할 때마다 분석작업 후 실행

7. Network Traffic을 유발한다.  

 

일반적으로 3GL 언어를 컴파일 언어라고 한다. 즉 미리 실행될 로직(logic)을 작성하여 문법과 테이블의 존재 여부를 컴파일러에 의해 분석하고 그 결과를 바이너리 실행파일로 생성한 후 필요할 때마다 호출하여 실행하는 방법이다.

 

 

 

생성과 실행
 

SQL> Create Procedure SALARY_CAL

         Begin

             Select * INTO :a,m :b

             From emp

             where id = 10;

             If SQL%NotFound then

                ....

             ENDIF

         END;

SQL> Excute SALARY_CAL
 

 

 

 

PL/SQL 언어 유형

PL/SQL은 기본적으로 DECLARE ~ BEGIN ~ EXCEPTION ~ END; 의 기본 문법을 가지고 있다.

3GL 언어(C,C++,JAVA 등)들을 보면 프로그래밍을 하기 위해 main()함수절을 가지고 있는 것과 동일 한 의미이다.

이러한 기본문법으로 생성할 수 있는 PL/SQL의 종류는 다음과 같이 6가지 종류가 있다.

 

1. Anonymous Procedure

  Anonymous(무명의) PL/SQL 블록은 생성할 때 DECLARE~ 문법으로 시작된다.

  사용자가 반복적으로 실행하려는 SQL문을 필요할 때마다 작성하여 실행하는 방법이다.

  그래서, 일반적으로 다른 PL/SQL의 종류와는 달리 데이터베이스 내에 그정보가 저장되어 있지 않기 때문에

  무명인 Anonymous PL/SQL이라고 한다.

 

2. Stored Procedure

  CREATE PROCEDURE name ~ 문법에 의해 생성되면 생성 후 데이터베이스 내에 그 정보가 저장된다.

  이러한 PL/SQL을 NAMED PL/SQL 이라고 한다.

  그중에서도 STORED PROCEDURE는 개발자가 실행하려는 logic을 처리만 하고 PL/SQL 블록이 끝나는 처리흐름을 가졌다.

 

3. Stored Function

  Stored Function은 2번에서 소개한 Stored Procedure와 동일한 개념, 동일한 기능을 가지고 있다.

  한가지 차이점은 Stored Procedure는 로직을 처리만 하고 끝내지만, Stored Function은 그 처리 결과를 사용자에게 돌려준다.

 

4. Package

  PL/SQL 언어를 통해 시스템을 개발하다 보면 수 많은 Stored Procedure, Stored Function들을 생성하게 된다.

  하지만 그수가 너무 많아지면 개발자가 관리하기가 불편하고 어떤 Procedure, Function이 존재하는지 기억하기도 어려워진다.

  이러한 개발환경에서 보다 효과적으로 PL/SQL 블록들을 관리하기 위해 사용되는 것이 패키지다.

  보통 업무단위별로 패키지를 만들어 관리한다. 관리하기 용이하게 하기 위해 사용되어진다.

 

5. Trigger

  트리거는 PL/SQL종류 중 가장 다양한 기능을 가지고 있다. 트리거 생성시 설정한 테이블에 대해 누군가가

  UPDATE, INSERT, DELETE문을 실행하면 그작업을 실행시킨후 또는 실행시키기 전에 TRIGGER에 정의한 로직을 실행시키는

  PL/SQL 블럭이다.  데이터베이스의 감시, 보안, 연속적인 오퍼레이션의 자동처리, HISTORY등등 응용할 데가 많다.

 

6. Object-Type

  오라클 데이터베이스는 버젼 8부터 객체(Object)  옵션을 제공하고 있다.

  또한 객체옵션이 제공되는 데이터베이스를 객체관계형 데이터베이스라고 한다. 이러한 객체에 대해 데이터를 입력, 수정, 삭제

  조회하기 위해서는 반드시 PL/SQL 언어를 사용해야 한다.

 

'DB' 카테고리의 다른 글

[oracle] sys_connect_by_path()  (0) 2014.04.08
테이블 insert 권한 줄때 잊지말고 시퀀스도 select 권한도 함께!  (0) 2014.04.03
SQL 언어의 종류  (0) 2014.03.06
데이터베이스 설계단계  (0) 2014.03.06
SQL 표준 개정이력  (0) 2014.03.06
Posted by 에시드 :

SQL 언어의 종류

2014. 3. 6. 14:53 from DB

 

 구분

형식 

비고 

 DQL

 SELECT column

 FROM table명

 WHERE 조건절

 검색시 사용

 DML

 UPDATE table명

 INSERT INTO table명

 DELETE table명

 변경시 사용

 DDL

 CREATE table명

 DROP table명

 ALTER table명

 Object의 생성과 변경시

 TCL

 COMMIT;

 ROLLBACK;

 SAVEPONIT;

 Transaction 종료 및 취소

 DCL

 GRANT ...;

 REVOKE ...;

 권한 부여 및 취소

 

'DB' 카테고리의 다른 글

테이블 insert 권한 줄때 잊지말고 시퀀스도 select 권한도 함께!  (0) 2014.04.03
PL/SQL  (0) 2014.03.06
데이터베이스 설계단계  (0) 2014.03.06
SQL 표준 개정이력  (0) 2014.03.06
오라클 데이터베이스의 역사  (0) 2014.03.06
Posted by 에시드 :

데이터베이스 설계단계

2014. 3. 6. 14:32 from DB

클라이언트의 요구에 의해 정보시스템이 구축되기까지의 단계를 알아보자

 

시스템 구축단계에 대한 이해를 통해 데이터베이스가 어떻게 분석되고 설계되는지를 이해해 보고 또한 분석/설계된 내용을 데이터베이스에 어떻게 구현하는지 자세히 알아보자.

일반적으로, 새로운 데이터베이스를 구축하기 위해서 크게 3가지 단계를 통해 분석,설계를 하게 된다.

 

 

첫번째 단계. 개념적 데이터모델링

- 클라이언트의 요구사항을 듣고 분석하는 단계

 

실세계(Real World)의 내용을 그대로 이해하고 분석하며(정규화 과정), 분석결과를 ERD(Entity Relationship Diagram)

로 표현하는 과정

실세계에서 관리해야 할 대상(업무,사람,목표,장소,개념) 엔티티(Entity)와 엔티티의 속성(Attribute) 정의

 

* 예를 들면, 사람이라는 엔티티는 이름,주민번호,주소,연락처와 같은 속성들로 구분된다. 그리고, 여러개의 속성들 중에 대표적인 속성을 식별자(identifier)라고 한다. 여러명의 사원이 존재할 때 각각의 사원들을 구분하기 위해서는 각 사원들에게 사원번호라는 유일한 번호를 부여하거나 또는 주민번호와 같은 유일한 값으로 구분한다. 이렇게, 중복되지 않는 유일한 값만이 식별자(ID)가 될 수 있다. 또한 여러명의 사원정보를 하나의 튜플(Tuple)이라고 표현한다.

회사에 근무하는 한명의 사원정보가 튜플이 될 수 있으며, 많은 사원정보들도 튜플이 될 수 있다.

마지막으로, 어떤 엔티티가 가진 속성과 다른 엔티티가 가진 속성들 간에 데이터의 관계를 릴레이션쉽(RelationShip)이라고 한다.

 

 

두번째 단계. 논리적 데이터베이스 설계단계

- 개념적 데이터모델링 단계에서 분석된 ERD가 실제적인 데이터베이스 환경에 구현될 수 있는 형태로 만들어지는 단계

개념적 모델링단계에서 분석되었던 엔티티는 논리적 데이터베이스설계단계에서는 테이블이라는 구조로 표현되고, 애트리뷰트는 컬럼(Column)으로, 식별자는  Primary_key로 튜플은 행(Row)으로, 관계는 Foreign_key라는 구조로 설계된다.

 

 

세번째 단계. 물리적 데이터베이스 설계단계

- 논리적 데이터베이스 설계단계의 결과를 데이터베이스에 직접생성하고 효과적인 데이터베이스의 운영과 성능향상을 위해 하드웨어적인 사용계획을 세우는 마지막 단계.

 

 

 

 

 

 

'DB' 카테고리의 다른 글

PL/SQL  (0) 2014.03.06
SQL 언어의 종류  (0) 2014.03.06
SQL 표준 개정이력  (0) 2014.03.06
오라클 데이터베이스의 역사  (0) 2014.03.06
관계형 데이터베이스의 특징  (0) 2014.03.06
Posted by 에시드 :