Oracle Database 11g Release 2 Standard Edition, Standard Edition One, and Enterprise Edition 7/13: Patch Set 11.2.0.4 for Linux and Solaris is now available on support.oracle.com. Note: it is a full installation (you do not need to download 11.2.0.1 first)
www.oracle.com
들어갈때마다 페이지 구성이 바뀌는 오라클 홈페이지에서 Oracle Database 11g Release 2 중 Linux x86-64(64비트) 혹은 Linux-x86(32비트)를 다운로드 받습니다.
(Nothing to do 라고 출력이 되면 이미 최신버전으로 다운로드가 되어있는것을 의미합니다.)
** 21.02.02 추가 **
[ 오류 내용 ]
위의 명령어를 실행할때 다음과 같은 오류가 발생할 경우가 있습니다.
YumRepo Error: All mirror URLs are not using ftp, http[s] or file. Eg. Invalid release/repo/arch combination/ removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt Error: Cannot find a valid baseurl for repo: base YumRepo Error: All mirror URLs are not using ftp, http[s] or file. Eg. Invalid release/repo/arch combination/removing mirrorlist with no valid mirrors: /var/cache/yum/i386/6/base/mirrorlist.txt Error: Cannot find a valid baseurl for repo: base
CentOS6 버전 업데이트 지원이 종료되면서 yum update 등 명령어 사용 시 발생합니다.
이때, 해결방법을 알려드리겠습니다. 32bit, 64bit 에 따라 명령어가 다르니 getconf LONG_BIT명령어를 통해 리눅스의 비트를 확인해주세여!
oracle soft nproc 2048 oracle hard nproc 65536 oracle soft nofile 1024 oracle hard nofile 65536
3) SELINUX 설정을 해제합니다.
[root@localhost ~]# vi /etc/selinux/config
다음과 같이 변경합니다.
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing – SELinux security policy is enforced.
# permissive – SELinux prints warnings instead of enforcing.
# disabled – No SELinux policy is loaded. SELINUX=disabled
>>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<< 에러가 날 경우 켜져있는 리눅스 서버를 모두 종료하고 다시 실행해주세요
[oracle@localhost ~]$ cd database
[oracle@localhost database]$ ./runInstaller
[oracle@localhost ~]$ cd /usr/oracle/app/product/11.2.0/dbhome_1/sysman/lib
[oracle@localhost lib]$ vi ins_emagent.mk
아래와 같은 구문을 찾아서
$(SYSMANBIN) emdctl:
$(MK_EMAGENT_NMECTL)
아래와 같이 수정하고 저장합니다.
$(SYSMANBIN) emdctl:
$(MK_EMAGENT_NMECTL) -lnnz11
Retry 버튼을 선택해서 재시도합니다. 잘되는군염
완료될때쯤 쉘 스크립트 실행하라는 안내창이 뜹니다.
하라는대로 따라하면됩니다. 새 창을 열어서 root 계정으로 실행해줍니다.
[root@localhost ~]# /usr/oracle/oraInventory/orainstRoot.sh
Changing permissions of /app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /app/oraInventory to dba.
The execution of the script is complete.
[root@localhost ~]# /usr/oracle/app/product/11.2.0/dbhome_1/root.sh
Running Oracle 11g root.sh script…
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /app/oracle/product/11.2.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]: [ENTER]키 누르세용
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …
Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
※ netca command not found 웅앵웅 에러 뜰 경우 다음과 같은 명령어를 실행하세염
위에서 환경변수가 적용이 안되었거나 오라클이 설치된 path가 잘못될 경우 나타납니다.
[oracle@localhost ~]$ cd /app/oracle/product/11.2.0/dbhome_1/bin
[oracle@localhost ~]$ ./netca
Listener configuration 체크 후 Next
최초 구성이므로 Add 선택 후 Next
아묻따 Next
확인 후 넥스트
오라클 기본포트 1521 확인 후 넥스트
다른 리스터 추가 할거니? 아니 > 넥스트
끝났당 ㄴㅅㅌ
Finish 선택해서 창 종료
6. 데이터베이스 생성
다음 명령어로 데이터베이스를 생성합니다.
[oracle@localhost ~]$ dbca
생성시작 Next
Create a Database 선택하고 Next
웅웅 알겠어염 확인하구 Next
ORACLE SID를 orcl로 설정하고 넥스트
(SID를 orcl 말고 다른걸로 변경하고 싶으면 .bash_profile의 ORACLE_SID 변수 값도 변경해주어야해여)
고대로 넥스트
관리자 전체 계정에 동일 패스워드를 사용한다는 옵션 체크 후 패스워드 설정
(까먹으면 귀찮아지니까 쉬운걸로 설정하세염)
패스워드를 단순하게 설정하면 뜨는 확인창임다 Yes를 선택해서 무시하고 넘어가시져
넥스트
ㄴㅅㅌ
ㄴㅅㅌ
서버 메모리 사양에 따라 설정된 값임다 그대로 설정하고 [Sizing] 탭을 선택해주세여
확인하고 [Character Sets] 탭 선택하세여
UTF-8이랑 항구거로 설정하고 [Connection Mode] 선택
기본 설정값 고대로 ㄴㅅㅌ
설정된 값 확인하고 ㄴㅅㅌ
피니쉬
마지막으로 설치 전에 확인하고 OK
아래와 같이 설치됩니다
(나만그런가 설치 열라느림)
아래와 같은 창 뜨면 설치 완료된겁니다.
ㅎㅏ,, 이제 모두 설치 완료가 됐습니다.
이제 실행하는 것만 남았군여,,,
7. 데이터베이스 및 리스너 실행
아까 위에서 .bash_profile에 alias로 sysdba로 접속하는 별칭을 주었기 때문에 아래와 같은 명령어로 접속합니다.
[oracle@localhost ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 31 13:56:58 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORA-01081: cannot start already-running ORACLE – shut it down first
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 768294912 bytes
Fixed Size 2217304 bytes
Variable Size 486541992 bytes
Database Buffers 276824064 bytes
Redo Buffers 2711552 bytes
Database mounted.
Database opened.
SQL > exit
리스너 기동 상태를 확인합니다.
[oracle@localhost ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 31-JAN-2020 14:01:07
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 30-JAN-2020 17:28:30
Uptime 0 days 20 hr. 32 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
리스너는 lsnrctl start 또는 lsnrctl stop 명령어를 이용해서 끄고 켜고를 할 수 있습니다.
8. 데이터베이스 계정 생성
이제 데이터베이스에 계정을 생성해서 SQLDeveloper에 붙어볼겁니다.
저는 제 티스토리 주소인 xxsiyoung 이름으로 계정을 만들고 권한까지 줘보겠슴다.
참고로 저는 귀찮아서 계정 아디랑 비번을 똑같이 만들어요
밑에 명령어중에 identified by 뒤에는 비밀번호를 설정하는 겁니다.
권한은 데이터베이스 사용하는데 필요한 권한은 다 줬어요
자세한 권한 설명은 구글링 하시기 바랍니다,,
[oracle@localhost ~]# ss
…
SQL> CREATE USER xxsiyoung identified by xxsiyoung;
User created.
SQL> GRANT resource, connect, dba to xxsiyoung;
Grant succeeded.
다음과 같은 명령어로 생성한 계정이 잘 만들어졌는지 확인해주세여
SQL> SELECT * FROM ALL_USERS;
9. SQLDeveloper에 연결하기 (외부접속하기)
이제 생성을 했으면 사용하기 쉽게 SQLDeveloper에 연결할겁니다.
$ORACLE_HOME/network/admin 폴더에 있는 listener.ora 파일과 tnsnames.ora 파일을 수정해주어야 합니다.
일반적인 방법의 ‘connect by start with’ 에서 보기 어려운
중복가지가 발생하는 경우의 누적환산량을 구하는 방법입니다. /* 1. BOM테이블 */
CREATE TABLE BOM (
PartNo VARCHAR2(2), /* PartNo */
PartNoM VARCHAR2(10), /* 부모PARTNO */
Qnty NUMBER(10) NOT NULL, /* 단위당소요량 */
CONSTRAINT pk_g_BOM PRIMARY KEY (PartNo, PartNoM));
/* 2. DATA */
INSERT INTO BOM VALUES (‘X’,’*’,1);
INSERT INTO BOM VALUES (‘A’,’X’,1);
INSERT INTO BOM VALUES (‘B’,’X’,2);
INSERT INTO BOM VALUES (‘C’,’B’,3);
INSERT INTO BOM VALUES (‘E’,’C’,3);
INSERT INTO BOM VALUES (‘F’,’C’,4);
INSERT INTO BOM VALUES (‘I’,’C’,5);
INSERT INTO BOM VALUES (‘D’,’B’,4);
INSERT INTO BOM VALUES (‘H’,’D’,2);
INSERT INTO BOM VALUES (‘C’,’J’,6);
INSERT INTO BOM VALUES (‘J’,’D’,4);
INSERT INTO BOM VALUES (‘K’,’D’,3);
/* 3. 설명*/
이렇게되면
BOM 이라는 테이블에 아래와와같은 자료가 들어 있습니다.
SELECT * FROM BOM;
PA PARTNOM QNTY
— ———- ———-
X * 1
A X 1
B X 2
C B 3
E C 3
F C 4
I C 5
D B 4
H D 2
C J 6
J D 4
K D 3
12 rows selected.
/* 4.원하는 결과 */ FUNCTION을 쓰지않은 onE SQL 로 다음과 같은 결과를 얻어내고 싶답니다.
—- ————- — —– ———-
1 1 A 1 1
2 1 B 2 2
3 ..2 C 3 6
4 ….3 E 3 18
5 ….3 F 4 24
6 ….3 I 5 30
7 ..2 D 4 8
8 ….3 H 2 16
9 ….3 J 4 32
10 ……4 C 6 192
11 ……..5 E 3 576
12 ……..5 F 4 768
13 ……..5 I 5 960
14 ….3 K 3 24
14 rows selected.
/* 5.결과설명 */
—- ————- — —– ———-
1 1 A 1 1 최상위 이므로 자신의 수량
2 1 B 2 2 최상위 이므로 자신의 수량
3 ..2 C 3 6 자신의수량*자신의부모B의수량 = 3*2 = 6
4 ….3 E 3 18 자신의수량*자신의부모C의수량*C의부모B의수량 = 3*3*2 = 18
5 ….3 F 4 24 자신의수량*자신의부모C의수량*C의부모B의수량 = 4*3*2 = 24
6 ….3 I 5 30 자신의수량*자신의부모C의수량*C의부모B의수량 = 5*3*2 = 30
7 ..2 D 4 8 자신의수량*자신의부모B의수량 = 4*2 = 8
8 ….3 H 2 16 자신의수량*자신의부모D의수량*D의부모B의수량 = 2*4*2 = 16
9 ….3 J 4 32 자신의수량*자신의부모D의수량*D의부모B의수량 = 4*4*2 = 32
10 ……4 C 6 192 자신의수량*자신의부모J의수량*J의부모D의수량*D의부모B의수량 = 6*4*4*2 = 192
11 ……..5 E 3 576 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모D의수량*D의부모B의수량 = 3*6*4*4*2 = 576
12 ……..5 F 4 768 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모D의수량*D의부모B의수량 = 4*6*4*4*2 = 768
13 ……..5 I 5 960 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모…3 K 3 24 자신의수량*자신의부모D의수량*D의부모B의수량 = 3*4*2 = 24
14 rows selected.
/* 6.가정*/
실제 테이블에 존재하는 record는 12건인데 순전개를 통하여 전개를 해보면 다음과 같이 14건의 자료가 나옵니다.
SELECT LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
(order siblings by 컬럼명)
LEVELNO LVNO PA QNTY
—————- —— — —–
1 1 A 1
1 1 B 2
..2 2 C 3
….3 3 E 3
….3 3 F 4
….3 3 I 5
..2 2 D 4
….3 3 H 2
….3 3 J 4
……4 4 C 6
……..5 5 E 3
……..5 5 F 4
……..5 5 I 5
….3 3 K 3
14 rows selected.
이유는 자료를 자세히 살펴보면 아시겠지만
자동차 부품의 중간부품에 쓰이는 부품이 또다른 중간부품에서도 쓰이듯이 C 로 시작되는 가지가 두군데에서 쓰이고 있습니다.
이럴경우는 C로 시작하여 역전개를 하면 중복가지가 발생합니다
SELECT *
FROM BOM
START WITH PARTNO=’C’
CONNECT BY PRIOR PARTNOM=PARTNO
PA PARTNOM QNTY
— ———- ———-
C B 3
B X 2
X * 1
C J 6
J D 4
D B 4
B X 2
X * 1
8 rows selected.
중복가지가 발생하지 않는다면 간단하게 역전개 결과를 이용해 나오는
결과값의 곱 만으로 환산수량을 구할 수 있습니다.
예를들어 최초의 자료중
INSERT INTO BOM VALUES (‘C’,’J’,6);
가 없다고 가정해 봅시다.
언급한 한 레코드를 제외하고 QUERY 를 던지면 다음과 같은 결과가 나옵니다.
SELECT * FROM BOM;
PA PARTNOM QNTY
— ———- ———-
X * 1
A X 1
B X 2
C B 3
E C 3
F C 4
I C 5
D B 4
H D 2
J D 4
K D 3
11 rows selected.
이경우 순전개를 하면 다음과 같이 됩니다.
NO LEVELNO PA QNTY
—- ——– — ———-
1 1 A 1
2 1 B 2
3 ..2 C 3
4 ….3 E 3
5 ….3 F 4
6 ….3 I 5
7 ..2 D 4
8 ….3 H 2
9 ….3 J 4
10 ….3 K 3
10 rows selected.
위와 같은 경우의 환산수량을 구하는 방법은 다음과 같이 간단합니다.
SELECT A.LEVELNO,
A.PARTNO,
A.QNTY,
(SELECT EXP(SUM(LN(B.QNTY)))
FROM BOM B
START WITH B.PARTNO = A.PARTNO
CONNECT BY PRIOR PARTNOM=PARTNO
) QTY
FROM (SELECT LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM) A ;
결과
LEVELNO PA QNTY QTY
——– — —– —–
1 A 1 1
1 B 2 2
..2 C 3 6
….3 E 3 18
….3 F 4 24
….3 I 5 30
..2 D 4 8
….3 H 2 16
….3 J 4 32
….3 K 3 24
10 rows selected
즉 특정한 부품 하나에 대해 역전개를 해 나가더라도 중복 가지가 발생할 염려가 없으므로
단순한 QUERY 만으로도 각 부품의 환산 산출량을 구하는데 아무런 지장이 없습니다.
하지만
INSERT INTO BOM VALUES (‘C’,’J’,6);
를 통하여 중복가지가 발생한다면
위와 같이 간단한 문장으로 해결할 길이 없습니다.
이럴경우는 접근방식을 근본적으로 다시 생각해봐야합니다.
/* 7.문제풀이*/
현재 하고자하는방식은 어떤방식을 써서라도 함수를 사용하지않고
하나의 SQL 에서 중복가지 문제를 해결하고 누적환산 산출량을 구하는 겁니다.
단계1. 중점적으로 생각해 볼것은 어떤식으로 중복가지를 피해서 자신만의 상위 부품들을 찾아낼것인가 입니다.
특정부품을 기준으로 생각해볼때 자신의 상위부품은 어떤 공통적인 특징이 있습니다.
첫째 레벨이 자신보다 높다.
즉 특정부품을 기준으로 볼때 레벨의 숫자가 자신보다 낮은경우만이 자신의 부모레벨이 될 후보입니다.
둘째 전개가 제대로 이루어졌다면 자신보다 항상 상단에서 전개가 이루어진다 입니다.
이렇게 두가지 조건으로는 완벽하지 않지만 일차적인 필터링을 할 수 있습니다.
이렇게 일차적인 필터링을 위해 필요한 쿼리에는 전개 결과와 함께 자신의 ROWNUM 이 포함되어야 합니다.
SQL과 결과는 다음과 같습니다.
SQL
SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
결과
NO PA LEN
———- — ———-
1 A 1
2 B 1
3 C 2
4 E 3
5 F 3
6 I 3
7 D 2
8 H 3
9 J 3
10 C 4
11 E 5
12 F 5
13 I 5
14 K 3
14 rows selected.
단계2. 두번째 단계는 위의 결과를 이용해서 자신보다 LEVEL(즉 LEN) 이 작으면서
동시에 자신보다 먼저 전개가 이루어진(즉 NO 가 자신보다 작은) 자료만을 뽑아내는 일입니다.
그러기 위해선 위의 결과를 이용해 부등호조인을 동일한 결과에 걸어줘야 할겁니다.
SQL과 결과는 다음과 같습니다.
SQL
SELECT A.NO,A.PARTNO,A.LEN,
B.NO,B.PARTNO,B.LEN
FROM (SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN,QNTY
FROM (
SELECT LEVEL LEV,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
결과
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
1 A 1
2 B 1
3 C 2 1 A 1
3 C 2 2 B 1
4 E 3 1 A 1
4 E 3 2 B 1
4 E 3 3 C 2
5 F 3 1 A 1
5 F 3 2 B 1
5 F 3 3 C 2
6 I 3 1 A 1
6 I 3 2 B 1
6 I 3 3 C 2
7 D 2 1 A 1
7 D 2 2 B 1
8 H 3 1 A 1
8 H 3 2 B 1
8 H 3 3 C 2
8 H 3 7 D 2
9 J 3 1 A 1
9 J 3 2 B 1
9 J 3 3 C 2
9 J 3 7 D 2
10 C 4 1 A 1
10 C 4 2 B 1
10 C 4 3 C 2
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 7 D 2
10 C 4 8 H 3
10 C 4 9 J 3
11 E 5 1 A 1
11 E 5 2 B 1
11 E 5 3 C 2
11 E 5 4 E 3
11 E 5 5 F 3
11 E 5 6 I 3
11 E 5 7 D 2
11 E 5 8 H 3
11 E 5 9 J 3
11 E 5 10 C 4
12 F 5 1 A 1
12 F 5 2 B 1
12 F 5 3 C 2
12 F 5 4 E 3
12 F 5 5 F 3
12 F 5 6 I 3
12 F 5 7 D 2
12 F 5 8 H 3
12 F 5 9 J 3
12 F 5 10 C 4
13 I 5 1 A 1
13 I 5 2 B 1
13 I 5 3 C 2
13 I 5 4 E 3
13 I 5 5 F 3
13 I 5 6 I 3
13 I 5 7 D 2
13 I 5 8 H 3
13 I 5 9 J 3
13 I 5 10 C 4
14 K 3 1 A 1
14 K 3 2 B 1
14 K 3 3 C 2
14 K 3 7 D 2
66 rows selected.
벌써 SQL 이 길어진듯한 느낌이지만 살펴보면 간단합니다.
단계1에서 나온 SQL 을 두번써서 서로 NON-EQUI 조인을
위에서 언급한대로 걸어준겁니다.
조건이
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
이렇게 되어 있는게 전부입니다.
OUTER 기호가 붙은 이유는 설명안해도 아시겠지만
A테이블을 기준으로 잡고 있기 때문입니다.
조인의 특성상 OUTER 가 아닌경우는 조인이 걸리는 값이 양쪽 데이타셋에 모두 존재해야하기때문에
최상위인 A,B 두개의 PART 가 빠집니다.
이를 막기위해 OUTER 조인이 이용되었습니다.
중요한건 여기까지의 결과를 분석하는겁니다.
결과가 많아진 이유는 14개의 각 순 전개된 PART 마다
자신의 직계 상위가 될 수 있는 후보들과 조인이 이루어졌기 때문입니다.
이 결과가 직계 상위 후보들이 맞는다면 단순히 A.NO 별로 GROUP BY 만 해주면 됩니다.
하지만 위의 결과를 보면 알 수 있듯이
여기서 한단계 더 필터링을 해줘야 합니다.
어떻게 우리가 원하듯 중복자료를 제외한 직계 자료만을 가져올 수 있나?
문제가 되는 C 를 놓고 봅시다.
위의 결과중 A.NO 가 10 인 9개 ROW를 자세히 살펴봅시다.
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
10 C 4 1 A 1
10 C 4 2 B 1
10 C 4 3 C 2
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 7 D 2
10 C 4 8 H 3
10 C 4 9 J 3
최초 순전개 자료도 놓고 함께 비교해 봅니다
LEVELNO LVNO PA QNTY
—————- —— — —–
1 1 A 1
1 1 B 2
..2 2 C 3
….3 3 E 3
….3 3 F 4
….3 3 I 5
..2 2 D 4
….3 3 H 2
….3 3 J 4
……4 4 C 6
……..5 5 E 3
……..5 5 F 4
……..5 5 I 5
….3 3 K 3
최초 순전개 자료의 10번째에 위치하는
LEVELNO LVNO PA QNTY
—————- —— — —–
……4 4 C 6
이 자료와 관계가 있는 결과를 살피고 있는 중입니다.
이자료의 직계 상위 PART 는 순전개 자료를 보면 한눈에 알수 있듯이
J,D,B 입니다.
J,D,B 에 해당하는 건을 위의 9개 ROW에서 집중적으로 살펴보세요.
뭔가 다른 자료와 차이가 있을 겁니다.
뭐가 다를까요?.
세 자료가 모두 B.LEN 이 같은 자료들중에서 B.NO 가 가장 큰 자료들입니다.
즉 J의 경우는 B.LEN 이 3인
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 8 H 3
10 C 4 9 J 3
이 다섯건중에 B.NO 가 9로 가장 값이 큽니다.
D 와 B도 마찬가지 입니다.
단계3. 여기까지 생각을 정리 했다면 이제 남은건 같은 A.NO 를 가진 ROW들을 대상으로
동일 B.LEN 을 가진것끼리 GROUP 을 지어 B.NO 가 최대값에 해당하는 자료만 걸러내면 됩니다.
걸러낸 결과가 우리가 원하던 특정부품을 기준으로 직계상위 부품이 되는겁니다.
단계2의 QUERY 를 약간 수정해 봅시다.
바뀌는 부분은 GROUP BY 를 위하여 SELECT 절이 수정되고 GROUP BY 가 추가되는 수준입니다.
SQL
SELECT A.NO,A.PARTNO,A.LEN,B.LEN,
MAX(LTRIM(TO_CHAR(B.NO,’0000000′))||LTRIM(TO_CHAR(B.QNTY,’000000000′))||B.PARTNO) TEM
FROM (SELECT NO,PARTNO,PART,
LEN
FROM (SELECT ROWNUM NO,PARTNO,PART,
LENGTHB(PART) -1 LEN
FROM (
SELECT LPAD(PARTNO, DECODE(LEVEL,1,1,LEVEL),’ ‘) AS PART,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,PART,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,PART,
LENGTHB(PART) -1 LEN,QNTY
FROM (
SELECT LPAD(PARTNO, DECODE(LEVEL,1,1,LEVEL),’ ‘) AS PART,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
GROUP BY A.NO,A.PARTNO,A.LEN,B.LEN;
결과
NO PA LEN LEN TEM
———- — ———- ———- ——————–
1 A 0
2 B 0
3 C 1 0 0000002000000002B
4 E 2 0 0000002000000002B
4 E 2 1 0000003000000003C
5 F 2 0 0000002000000002B
5 F 2 1 0000003000000003C
6 I 2 0 0000002000000002B
6 I 2 1 0000003000000003C
7 D 1 0 0000002000000002B
8 H 2 0 0000002000000002B
8 H 2 1 0000007000000004D
9 J 2 0 0000002000000002B
9 J 2 1 0000007000000004D
10 C 3 0 0000002000000002B
10 C 3 1 0000007000000004D
10 C 3 2 0000009000000004J
11 E 4 0 0000002000000002B
11 E 4 1 0000007000000004D
11 E 4 2 0000009000000004J
11 E 4 3 0000010000000006C
12 F 4 0 0000002000000002B
12 F 4 1 0000007000000004D
12 F 4 2 0000009000000004J
12 F 4 3 0000010000000006C
13 I 4 0 0000002000000002B
13 I 4 1 0000007000000004D
13 I 4 2 0000009000000004J
13 I 4 3 0000010000000006C
14 K 2 0 0000002000000002B
14 K 2 1 0000007000000004D
31 rows selected.
66건의 자료가 31건으로 줄어들었습니다.
각 NO 별로 살펴보세요.
마지막 TEM 컬럼은 B.NO 가 MAX 인건을 찾을때
해당 레코드의 수량과 상위 PART 를 함께 묶어서 붙여놓은겁니다.
나중에 수량을 이용하기 위해 저지른 짓입니다. 단계4.
이제 남은 일은 최초 순전개 자료와 조인을 거는 일입니다.
이때 조인조건은 NO 가 동일한 EQUI-JOIN 입니다.
순전개 SQL 및 결과
SQL
SELECT ROWNUM NO,
LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
결과
NO LEVELNO LVNO PA QNTY
—– ———– —— — ——-
1 1 1 A 1
2 1 1 B 2
3 ..2 2 C 3
4 ….3 3 E 3
5 ….3 3 F 4
6 ….3 3 I 5
7 ..2 2 D 4
8 ….3 3 H 2
9 ….3 3 J 4
10 ……4 4 C 6
11 ……..5 5 E 3
12 ……..5 5 F 4
13 ……..5 5 I 5
14 ….3 3 K 3
14 rows selected.
최종 SQL 및 결과
SQL
SELECT B.NO,
B.LEVELNO,
B.LVNO,
B.PARTNO,
B.QNTY,
B.QNTY*EXP(SUM(LN(NVL(TO_NUMBER(SUBSTRB(A.TEM,8,9)),1))))
FROM (SELECT A.NO,A.PARTNO,A.LEN,A.PARTNO,B.LEN,
MAX(LTRIM(TO_CHAR(B.NO,’0000000′))||LTRIM(TO_CHAR(B.QNTY,’000000000′))||B.PARTNO) TEM
FROM (SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN,QNTY
FROM (
SELECT LEVEL LEV,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
GROUP BY A.NO,A.PARTNO,A.LEN,A.PARTNO,B.LEN
) A,
(
SELECT ROWNUM NO,
LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
) B
WHERE B.NO=A.NO
GROUP BY B.NO,
B.LEVELNO,
B.LVNO,
B.PARTNO,
B.QNTY
결과
NO LEVELNO LVNO PA QNTY B.QNTY*EXP
—– ———- —— — —— ———-
1 1 1 A 1 1
2 1 1 B 2 2
3 ..2 2 C 3 6
4 ….3 3 E 3 18
5 ….3 3 F 4 24
6 ….3 3 I 5 30
7 ..2 2 D 4 8
8 ….3 3 H 2 16
9 ….3 3 J 4 32
10 ……4 4 C 6 192
11 ……..5 5 E 3 576
12 ……..5 5 F 4 768
13 ……..5 5 I 5 960
14 ….3 3 K 3 24
14 rows selected
–오라클의 암호화와 복호화
–오라클의 암호화는 DBMS_OBFUSCATION_TOOLKIT를 이용
–이 패키지에는 4개의 프로시저가 있다.
–프로시저는 VARCHAR2 타입 및 RAW 타입을 암호/복호화 지원. 다른 타입은 지원되지 않으므로 NUMBER 타입은 TO_CHAR를 이용해야 한다.
–DBMS_OBFUSCATION_TOOLKIT 패키지는 기본적으로는 사용할 수 없는 상태이다. 설치시에는 준비 되지 않은 패키지이기 때문이다. 아래 파일을 직접 실행해야 패키지가 생성되고, 준비가 된다.
D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmsobtk.sql
D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtobtk.plb
–SQLPLUS에서 SYS계정으로 로그인한 후 실행합니다.
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmsobtk.sql
–>패키지 생성
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtobtk.plb
–>패키지 본문 생성
–SYS: DBMS_OBFUSCATION_TOOLKIT 패키지를 사용할 수 있는 권한을 SCOTT 사용자에게 부여
GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO scott;
–SYS: DBMS_OBFUSCATION_TOOLKIT 패키지를 사용할 수 있는 권한을 모든 사용자에게 부여
GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO PUBLIC;
———————————–
–암호화 및 복호화용 패키지 작성
(사용할 곳에서 패키지 생성) 나의 경우는 scott에 했었음.
–선언
CREATE OR REPLACE PACKAGE CryptIT
IS
FUNCTION encrypt(str VARCHAR2, HASH VARCHAR2)
RETURN VARCHAR2;
FUNCTION decrypt(str VARCHAR2, HASH VARCHAR2)
RETURN VARCHAR2;
END CryptIT;
–몸체
CREATE OR REPLACE PACKAGE BODY CryptIT
IS
s VARCHAR2(2000);
FUNCTION encrypt(str VARCHAR2, HASH VARCHAR2) — 암호화
RETURN VARCHAR2
IS
p NUMBER := ((FLOOR(LENGTH(str)/8+0.9))*8);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(
input_string => RPAD(str,p)
,key_string => RPAD(HASH,8,’#’)
,encrypted_string => s
);
RETURN s;
END;
FUNCTION decrypt(str VARCHAR2, HASH VARCHAR2) — 복호화
RETURN VARCHAR2
IS
BEGIN
DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
input_string => str
,key_string => RPAD(HASH,8,’#’)
,decrypted_string => s
);
RETURN TRIM(s);
END;
END CryptIT;
CREATE TABLE abc(id NUMBER, pass VARCHAR2(20));
INSERT INTO abc (id, pass) VALUES (1, CryptIT.encrypt(‘12345′,’test’));
COMMIT;
