[linux] 리눅스 기본 명령어/자주 쓰는 명령어 https://itholic.github.io/linux-basic-command/

1. Introduction

사내 개발 인프라 개선 및 DevOps 프로젝트의 수행에 앞서, 기본적인 Ubuntu 사용법을 습득하는 과정에서 알게 된 명령어와 쓰임에 대해 간략하게 정리한다. 이 내용은 수시로 업데이트된다.

2. Explanation

2-1. Commands for Ubuntu

cat

cat [ OPTION ] [ FILE-NAME ]의 형식으로, concatenate 또는 catenate에서 따온 명령어이다. cat 명령은 파일 이름을 인자로 받아서 터미널 화면에 뿌려주는 역할을 한다. e.g. cat [ FILE-NAME1 ] [ FILE-NAME2 ] [ FILE-NAME3 ] e.g. cat > [ NEW-FILE ]은 새로운 파일을 생성할 때 사용한다.

pwd

pwd = print working directory 현재 디렉토리를 알려준다.

cd

cd = change directory

ll

ll = list. 목록 형식의 파일 및 디렉토리의 목록 표시. 권한, 사용자, 권한 그룹, 생성 일자 등이 표시된다.

cp

cp = copy. e.g. cp [ ORIGIN ] [ NEW-FILENAME ]

mv

mv = move. 파일 또는 디렉토리를 이동시킨다. e.g. mv [ TARGET ] [ DESTINATION ]

watch

watch 특정 프로세스에 대한 상태 등을 모니터링하기 위한 명령어이다. e.g. watch -n1 은 1초 단위로 interval한 모니터링 명령어를 보낸다. n = interval

grep

grep 입력으로 전달된 파일의 내용에서 특정 문자열을 찾고자 할 때 사용된다. 이는 단순 문자열 매칭이 아니라 정규표현식에 의한 패턴 매칭 방식을 사용한다.

mkdir

mkdir = make directory. 신규 디렉토리를 생성한다. e.g. mkdir [ DIRECTORY-NAME ]

rm

rm = remove. 파일을 삭제한다. e.g. rm [ FILE-NAME ]

rmdir

rmdir= remove directory. 디렉토리를 삭제한다. e.g. rmdir [ DIRECTORY-NAME ]

touch

touch 파일 또는 디렉토리의 업데이트 일자를 현재 시간으로 변경한다.

tail

tail 파일의 뒷부분을 보고싶은 만큼 보여준다. tail | less 파일을 라인 단위로 더 볼 수 있도록 표출한다. tail | more 파일을 더보기 형식으로 더 볼 수 있도록 표출한다.

find

find [ PATH ] [ 'FILE-NAME' ] 특정 경로에서 파일 또는 디렉토리를 검색한다. 이는 masking 검색 또한 지원한다. e.g. find [ DIRECTORY-NAME ] *.extension

etc.

alt shift { - (vertical) or + (horizontal) } 창을 분리한다.

2-2. authorities for access to resource : Ubuntu

ll등을 입력하였을 때, 표시되는 10글자의 문자는 접근 권한을 의미하는데, 보다 자세한 뜻은 다음과 같다: 먼저, 첫 번째 글자를 확인한다. -로 시작되면 파일, d로 시작되면 디렉토리를 의미한다. 이후 9자의 문자(3자씩의 3쌍, r(read)w(write)x(execute))는 각각 읽기, 쓰기, 실행 권한을 의미한다. 이 3쌍은 순서대로 사용자, 사용자그룹(관리자 권한), 그 외의 경우(권한이 없는 비사용자의 접근 권한)을 의미한다. 참고로, root 권한으로 파일 등을 생성하는 경우 해당 파일의 접근 권한은 자동으로 root 권한을 요구하도록 설정된다.

2-3. Base directories of Ubuntu

/mnt 마운팅된 외부 디바이스, 드라이브 등에 대한 정보가 들어있다. /lost+update 오류가 발생할 가능성이 존재하는 파일 등에 대한 정보가 들어있다. /root root 권한을 가진 사용자의 home 디렉토리. 참고로 사용자 계정의 home 디렉토리는 /home/{ USER-NAME }으로, root 계정 사용자의 home directory와 구분된다. /profile 사용자 정의 명령어, 즉 alias를 설정하는 경우에는 해당 내용을 이 곳에서 설정한다. /opt는 사용자 정의의 써드파티 어플리케이션, add-on 패키지들을 저장하기 위해 예약된 기본적인 디렉토리를 의미한다. /bin 은 기본적으로 설정되어 있는 명령어(aliases)가 저장되어 있다. /usr 윈도우에서의 user 폴더에 해당된다.

3. Remark

Difference between the commands: Ubuntu and CentOS

Ubuntu(Debian 계열) CentOS(RedHat 계열)간 명령어의 차이는 대체로 공유되며, yum과 apt의 차이 등은 명령어가 아닌 패키지 매니저의 차이로 이해하면 된다.

What is Kibana https://www.elastic.co/what-is/kibana

What is Centralized Log Management (CLM)? https://www.missioncloud.com/blog/what-is-centralized-log-management-clm

1. Introduction

Code quality Improvement, 즉 코드 품질 개선을 위한 Centralized Logs Management(CLM)에 대해서 알아보고, 이의 시각적인 이해도를 돕는 툴인 Kibana에 대해 알아본다.

2. Explanation

2-1. Centralized Log Management(CLM)

Almost every application that runs in a server environment generates logs automatically. These logs are a vital part of any system because they provide essential information about how a system is presently operating and also, how it operated in the past. By searching through log data, you're able to pinpoint issues, errors, and trends. However, it can be extremely time consuming and frustrating to manually look up one particular error on hundreds, or even thousands of servers, across thousands of log files.

서버 환경에서 실행되는 거의 모든 어플리케이션들은 자동으로 로그를 생성한다. 이 로그들은 시스템이 현재 어떻게 운영되고 있는지, 또한 어떻게 운영되었었는지에 대한 핵심적인 정보들을 제공하기 때문에 그 어떤 시스템에서도 필수불가결하다. 로그 데이터를 검색한다는 것은 이슈들, 에러, 트렌드에 정확하게 접근하는 것이 가능해진다는 것을 의미한다. 하지만, 수없이 많은 로그 파일들을 넘어 마찬가지로 셀 수 없는 서버들에서 하나의 에러를 직접 찾아내기란 무지막지한 시간을 쏟아야 하고, 매우 절망스러울 것이다.

Centralized Log Management(CLM) is a type of logging solution system that consolidates all of your log data and pushes it to one central, accessible, and easy-to-use interface. Centralized logging is designed to make your life easier. Not only does CLM provide multiple features that allow you to easily collect log information, but it also helps you consolidate, analyze, and view that information quickly and clearly. CLM gives you tons of capabilities including :

중앙화된 로그 관리(CLM)은 모든 로그 데이터를 하나의 중앙에 통합하고, 접근 가능한, 그리고 사용이 편리한 인터페이스인 로깅 솔루션의 일종이다. 중앙화된 로깅은 삶을 보다 쉽게 만들어주도록 설계되었다. CLM은 로그 정보들을 쉽게 모아주는 다양한 기능들을 제공하지는 않지만 통합, 분석, 그리고 정보들을 빠르고 명확하게 정보들을 볼 수 있도록 돕는다. CLM은 다음과 같은 수많은 역량의 제공을 포함하고 있다:

* Storing log data from multiple sources in a central location * Enforcing retention policies on your logs so they are available for a specific time period * Easily searching inside the logs for important information * Generating alerts based on metrics you defined on the logs * Sharing you dashboard and log information with others simply and quickly * Low costs and increased storage and backup for historical data * Setting up security alerts and granting login access to particular users without granting server root access

• 복수의 소스들로부터 하나의 중앙 장소에 로그 데이터를 저장한다
• 로그에 정체 정책을 강제하여 특정 시간대에 (접근이) 가능하다
• 로그 내 중요 정보에 대한 검색이 용이하다
• 로그에 정의한 매트릭스를 기반으로 경보를 생성한다
• 대쉬보드와 로그 정보들을 다른 이들과 쉽고 빠르게 공유한다
• 저비용이며, 역대 데이터를 백업하고 스토리지를 증가시킨다
• 서버 root 접근 권한을 부여하지 않아도 부분적인 사용자들에게 로그인 권한을 부여하며 보안 경보를 설정한다

CLM allows you to do more with your log data and manage it much more efficiently. You'll have the ability to access the data you want in seconds rather than hours, weeks, or even days by manually searching through tons of logs. Taking advantage of centrally dynamic, profitable, and secure.

중앙화된 로그 관리는 로그 데이터들과 함께 더 많은 작업을 해내도록 하며 이는 보다 효율적이다. 당신은 몇 시간, 주 또는 많은 날들을 직접 수많은 로그들을 검색하는 대신 데이터에 몇 초 안에 접근할 수 있는 능력을 갖게 될 것이다. 중앙-동적인, 이득이 되는, 그리고 보안적인 이점을 갖는 것이다.

ELK Stack is an open-source centralized logging solution based on Elasticsearch for collecting, parsing, and sotring logs. Elasticsearch, Logstash, and Kibana, when used together, form an end-to-end stack (ELK Stack) and real-time data analytics tool that provides actionable insights from almost any type of structured and unstructured data source.

ELK(ElasticSearch, Logstash, Kibana) 스택은 수집, 파싱, 그리고 로그 분류를 위한 ElasticSearch에 기반한 오픈소스 중앙화된 로깅 솔루션이다. Elasticsearch, Logstash, 그리고 Kibana는 함께 사용할 때 하나의 끝판왕 스택이며 거의 모든 유형의 정형 및 비정형 데이터소스의 실행 가능한 통찰로써의 실시간 데이터 분석 툴 양식이다.

2-2. Kibana, Elastic Search

Kibana is a free and open user interface that lets you visulaize your Elasticsearch data and navigate the Elastic Search.

Kibana는 당신의 Elasticsearch 데이터를 시각화하고 다룰 수 있도록 하는 무료 오픈소스 인터페이스이다.

It's an free and open frontend application that sits on top of the Elastic stack, providing search and data visualization capabilities for data indexed in Elastic search. Commonly known as the charting tool for the Elastic stack (Previously referred to as the ELK stack after Elasticsearch, Logstash and Kirbana.) Kirbana also acts as the user interface for monitoring, managing, and securing an Elastic Cluster - as well as the centralized hub for built-in solutions developed on the Elastic stack.

이는 Elastic 스택의 최상위에 위치한, Elasticsearch에서 인덱스된 데이터를 위한 검색 제공 및 데이터 시각화 역량을 제공하는 무료 오픈 프론트엔드 어플리케이션이다. 일반적으로 알려진 Elastic stack의 차트 생성 도구 (Elasticsearch, Logstash, Kibana)

3. Remark

Should I create a generation 1 or 2 virtual machine in Hyper-V? https://docs.microsoft.com/en-us/windows-server/virtualization/hyper-v/plan/should-i-create-a-generation-1-or-2-virtual-machine-in-hyper-v

UEFI : Unified Extensible Firmware Interface https://en.wikipedia.org/wiki/Unified_Extensible_Firmware_Interface

SSH 인증키 생성 및 서버에 등록(패스워드 입력X) https://my-t-space.tistory.com/31

SSH 인증키 생성 및 서버에 등록 & 간편하게 접속하기 https://velog.io/@solar/SSH-%EC%9D%B8%EC%A6%9D%ED%82%A4-%EC%83%9D%EC%84%B1-%EB%B0%8F-%EC%84%9C%EB%B2%84%EC%97%90-%EB%93%B1%EB%A1%9D-%EA%B0%84%ED%8E%B8%ED%95%98%EA%B2%8C-%EC%A0%91%EC%86%8D%ED%95%98%EA%B8%B0

1. Introduction

신규 서버 추가에 앞서, DevOps 프로젝트로 개발 환경 구축의 자동화를 위한 Ansible Playbook의 작성이 요구되었다. 먼저 로컬 서버에서의 테스트 환경을 구축하기 위해 복수의 VM을 생성하기로 한다. VM은 Hyper-v를 통해 생성하고, Ubuntu 20.04 CLI 버전을 사용한다. 또한 보다 원활한 원격 서버 접속을 위해 간단한 ssh 초기 설정 등을 진행할 것이다. 구체적인 진행 방법과 그 과정은 다음과 같다.

2. Explanation

2-1. Set up the Development Environment : Hyper-V

(1) Generate VM servers with Hyper-v

Hyper-V를 통한 가상 서버(VM) 구축은 다음과 같은 과정을 거쳐 진행된다:

Windows Pro 10을 사용하고 있는 경우, Hyper-V manager를 실행한다.

상단의 장비 목록에서 현재 사용 중인 디바이스를 선택하고, 오른쪽의 Actions 메뉴에서 New > Virtual Machines를 선택한다.

사용할 가상 머신의 이름을 특정한다.

Generation(세대)를 설정한다. Generation 1과 2의 차이란 인용하면 그 내용이 다음과 같다:

- Your choice to create a generation 1 or generation 2 virtual machine depends on which guest operating system you want to install and the boot method you want to user to deploy the virtual machine. We recommend that you create a generation 2 virtual machine to take advantage of features like Secure Boot unless one of the following statements is true :

- The VHD you want to boot from is not UEFI-compatible - Generation 2 doesn't support the operating system you want to run on the virtual machine. - Generation 2 doesn't support the boot method you want to user (Official Microsoft Documentation)

이상의 내용은 다음과 같다: 1세대 또는 2세대로 VM을 생성할지에 대한 선택은 VM을 배포할 유저가 어떠한 guest OS를 설치하고 실행하고 싶은지에 달려있다. 다음 중 하나 이상의 경우에 해당되지 않는 이상, Secure Boot과 같은 이점을 위해 2세대의 가상 머신을 생성하기를 권장한다:

• 부팅하고자 하는 VHD(가상하드디스크)가 UEFI를 지원하지 않는다.
• 가상 머신에서 실행하고자 하는 OS를 2세대가 지원하지 않는다.
• 사용하고자 하는 부팅 메소드를 2세대가 지원하지 않는다.

그렇다면 2세대를 사용함으로써 얻을 수 있는 이점이란 무엇인가? 이 역시 동일한 레퍼런스에 자세한 설명이 존재한다. (이하 원문)

What are the advantages of using generation 2 virtual machines? Here are some of the advantages you get when you use a generation 2 virtual machine:

* Secure boot This is a feature that verifies the boot loader is signed by a trusted authority in the UEFI database to help prevent unauthorized firmware, operating systems, or UEFI drivers from running at boot time. Secure Boot is enabled by default for generation 2 virtual machines. If you need to run a guest operating system that's not supported by Secure Boot, you can disable if after virtual machine's created.

To Secure Boot generation 2 Linux virtual machines, you need to choose the UEFI CA Secure Boot template when you create the virtual machine.

* Larger boot volume The maximum boot volume for generation 2 virtual machines is 64TB. This is the maximum disk size supported by a .VHDX. For generation 1 virtual machines, the maximum boot volume is 2TB for a .VHDX and 2040GB for a .VHD.

직역하면 내용은 이러하다. 2세대의 가상머신을 사용하여 얻을 수 있는 이점은 무엇인가?

• Secure Boot (보안 부팅) 부팅이 실행될 때에 인증되지 않은 firmware, OS, 또는 UEFI 드라이버들을 방지할 수 있도록 boot loader가 UEFI 데이터베이스 내에서 신뢰된 인증으로부터 서명되었는지를 증명하는 기능이다. 보안 부팅은 2세대 가상 머신에서 기본 값으로 설정되어 있다. 보안 부팅을 지원하지 않는 guest OS를 실행하고자 하는 경우, 가상 머신을 생성한 후에 설정을 끌 수 있다.

• Larger boot volume (더 큰 부팅 볼륨) 2세대 가상 머신의 최대 부팅 볼륨은 64TB이다. 이는 .VHDX의 최대 디스크 사이즈이다. 1세대 가상 머신의 경우, .VHDX의 최대 부팅 볼륨은 2TB이며 .VHD는 2040GB이다.

더욱 자세한 디바이스 지원에 대한 1세대와 2세대의 차이는 아래의 표와 같다.

다시 본론으로 넘어가서, 새 VM 생성의 다음 절차는 다음과 같다:

시작 메모리 용량을 설정한다. 기본값은 1024MB이다.

새로운 가상 머신은 각각 network adapter를 포함하고 있다. 어떤 가상 switch를 사용할지 선택할 수 있고 비연결 상태로 놔둘 수도 있다. 내 경우에는 Connection으로 WSL을 선택하였다.

가상 하드 디스크를 선택한다. 기본 값은 127GB이다.

os 이미지 파일을 선택한다. 내 경우, ubuntu 20.04 CLI 버전을 선택하였다.

생성이 완료되었다. 이 전 과정에서 선택한 사항들의 summary를 확인한다.

(2) Security configuration

상기한 바와 같이 2세대의 가상 머신 생성을 선택한 경우, 별도의 보안 설정을 해 줄 필요가 있다.

생성한 VM을 우클릭한 후, settings를 선택한다.

Security 설정에서 Template을 UEFI 증명 authority로 선택하고, apply한다.

(3) Ubuntu configuration

생성한 VM을 실행하면 다음과 같은 화면이 뜬다. start를 누른다.

로딩이 되면 언어를 먼저 설정해 준다. 참고로 CLI 버전의 VM은 마우스 조작이 불가능하므로, tab과 방향키 등을 통해 항목을 선택하고, enter를 눌러 확인 또는 다음 단계로 넘어갈 수 있다.

ipv4 주소를 설정해 준다. 기본값은 동적 ip로 설정되어 있으나, ssh 등으로 접속할 때에 고정 ip가 필요하다면 ipv4를 선택하고 원하는 주소값을 직접 입력한다.

Manual(직접 입력)을 선택하는 경우 ipv4 메소드 설정을 직접 입력할 수 있다. subnet은 공공 ip주소와 겹치지 않도록 사설 ip 주소 대역을 선택하고, address는 해당 사설 ip 주소 대역 내에서 원하는 주소를 작성해 준다. Ansible vm의 경우 192.168.100.30-33까지 할당하여 관리하고 있으므로 해당 대역대를 작성한다. gateway는 일단 적당히 적어주고 넘어간다.

proxy 설정이 별도로 필요하지 않다면 Done을 클릭해 다음 단계로 넘어간다.

apt-get update를 진행한다. 이 과정은 생략이 가능하다. (Continue without updating)

기본값대로, 디스크 전체를 사용하는 것으로 설정했다.

Summary를 확인하고 문제가 없다면 Done을 선택한 후 enter를 누른다.

위의 과정을 실행하는 경우 선택된 드라이브의 데이터가 포맷될텐데 괜찮냐는 경고문이 뜬다. Continue를 눌러 다음 단계로 넘어간다.

system의 로그를 사용할 프로필과 비밀번호를 설정해 준다.

ssh를 사용할 예정이라면 Install OpenSSH Server를 선택하여 open ssh 서버를 설치한다.

VM 설정이 완료되었다.

2-2. Set up the Development Environment : ssh, ubuntu

(1) Generate access-key with ssh-keygen

vm에 접속할 때에, 일반적인 ubuntu 명령어는 다음과 같다.

ssh vaccessName@address
# e.g. ssh codepark@192.168.100.32

ssh를 통한 위의 과정으로 원격 서버에 접속할 때에, 매번 비밀번호를 입력하는 것이 귀찮다면 아래와 같은 과정을 통해 로그인 절차를 단순화할 수 있다.

(2) Add public key to remote servers

ubuntu를 켜고, root 디렉토리로 이동한다. 단, 비root(사용자)의 경우 root 디렉토리는 /home/계정명이며, root 권한의 경우 /root이다. 참고로 습관적으로 root 권한으로 작업을 수행하는 것은 지양하는 것이 좋다. root 권한으로 생성한 작업들의 권한 역시 root 권한을 필요케 하기 때문.

root 디렉토리에서 다음과 같은 명령어를 입력해서 먼저 access key를 발급한다.

ssh-keygen

ssh-keygen을 입력하면 한 쌍의 키를 발급해 주는데, 하나는 .pub이 붙은 공개키 id_rsa.pub이며 하나는 .pub이 붙지 않은 비밀키 id_rsa이다. 이제 이 공개키를 원하는 원격 서버에 복사하면, 해당 원격 서버는 공개키와 접속 서버의 비밀키를 대조하여 known_hosts에 등록하게 된다.

ssh-keygen을 입력하고, 3번의 질문이 뜨는데 전부 엔터를 누르면 편하다. 이는 키를 복사할 디렉토리 선택, 비밀번호 설정 여부 등이다.

접근 키의 원격 서버로의 복사는 다음과 같은 명령어로 수행한다.

ssh-copy-id serverAccessname@Address
# e.g. ssh-copy-id codepark@192.168.100.32

키 복사가 완료되고 나면 해당 주소로 다시 접근해보라는 메세지가 표출된다. 해당 주소 다시 접근하면 비밀번호 입력 절차없이 간편하게 ssh 접근이 가능해진다.

(3) Add Aliases to remote servers

3. Concolusion

4. Remark

AWS VPC 생성 후 EC2 생성하기 https://twofootdog.tistory.com/27/

Conda를 사용하는 Depp Learning AMI https://docs.aws.amazon.com/ko_kr/dlami/latest/devguide/overview-conda.html

get started with deep learning using the aws deep learning ami https://aws.amazon.com/ko/blogs/machine-learning/get-started-with-deep-learning-using-the-aws-deep-learning-ami/

Pricing Information - AWS Deep Learning AMI (Ubuntu 18.04) https://aws.amazon.com/marketplace/pp/prodview-x5nivojpquy6y?ref=cns_srchrow/

ec2 on demand instance vCPU increase https://aws.amazon.com/ko/premiumsupport/knowledge-center/ec2-on-demand-instance-vcpu-increase/

Amazon EC2 Dedicated Hosts Pricing https://aws.amazon.com/ec2/dedicated-hosts/pricing/?nc1=h_ls

1. Introduction

대용량(일일 400GB의 사례)의 데이터를 처리하기 위한 Amazon EC2 P3 Instance (p3.2xlarge) 생성 과정에 대해 순서대로 간략히 기술한다.

2. Explanation

2-1. Choose an Amazon Machine Image(AMI) and Operating System with'em

요구사항에 해당하는 p3 instance의 실행 절차로, EC2에서 p3에 해당하는 IME를 설치할 필요가 있다. 즉, 이에 해당하는 것으로는 Amazon SageMaker, AWS Deep Learning AMI, NVIDIA AMI가 해당된다. 생성된 P3 instance를 통해 추후 SARscape를 통한 작업을 염두에 두고 있으며, 이는 multi-GPU를 서비스하지 않으므로 single GPU 기반의 p3.2xlarge를 사용할 수 있는 AMI를 선택하도록 한다.

이에 따라 AWS Deep Learning AMI를 통해 P3 Instance를 실행하는 것으로 결론. OS로 RHEL(Red Hat - CentOS), Ubuntu, Windows를 지원하고 있다. 메뉴에서 EC2를 검색하여 메뉴에 진입, Launch Instance 버튼을 클릭하여 AMI(Amazon Machine Image) 선택 창에서 원하는 AMI를 선택한다.

참고로 Ubuntu 버전의 경우 16.04 버전과 18.04 버전이 존재하며, 이 두 버전의 차이는 Chainer Framework, Pytorch(16.04)/Pytorch ensorflow(18.04)의 지원 여부 등이다. (자세한 것은 레퍼런스 참고) 다만, 현재 요구사항에서 구버전 Pytorch 관련 예정된 것이 없으므로 Ubuntu 18.04 버전을 선택한다.

또한 DLAMI(Deep LearningAMI)을 구성하기 위해 가능한 region의 리스트는 위의 이미지와 같다. 이 전 단계에서 생성한 서브넷들은 us-east-1 region에 해당하므로, 계속 진행한다.

2-2. Try to launch new Instance : p3.2xlarge

생성하고자 했던 p3.2xlarge 타입을 지원하는 것을 확인하고, 선택한다. 참고로 Amazon DLAMI에서 p3.2xlarge를 지원하고 있는 region은 us-east: Virginia, Ohio, Oregon, Canada: central, EU: Frankfurt, Ireland, London 등이며, 해당 소프트웨어 및 infrastructure가 설치된 지역에 따라 금액과 인스턴스 타입이 다르다. US West (N. California) 지역에서는 p3.2xlarge 인스턴스 타입을 지원하지 않으니(해당 리전 선택하고 인스턴스 실행 시도 시 instance type을 선택하는 메뉴에 p3.2xlarge가 존재하지 않는다) 리전 선택에 주의한다.

생성한 Landsubsidence network(VPC) 및 subnet을 선택한다. 딥러닝 AMI에서 지원하는 region인 us-east-1로 서브넷을 이미 생성하였으므로, 해당 서브넷을 선택해 준다.

추가 저장공간이 필요하다면 4. Add Storage에서 추가 설정을 잡아주고, Tags/Security Groups 설정은 일단 건너뛴다. 리뷰 화면은 위와 같다.

인스턴스 생성 이전, 키파일이 존재하지 않는다면 신규로 생성해 준다.

잘 되는 것 처럼 보인다.

2-3. Get region-validation

안 된다.

위의 실행 실패 메세지를 살펴보면, 해당 리전에 대한 access validation이 되지 않았기 때문에 validation이 완료되기 전까지 부가적인 리소스를 실행할 수 없다는 내용이다. validation이 끝나는대로 이메일로 알려줄 것이며, 일반적으로 해결까지는 몇 분 가량이 걸리나, 과정 완료까지 4시간 여까지는 봐달라 - 그래도 해결이 안 되면 aws-verification@amazon.com으로 이메일을 발송하면 된다고 써 있다.

인증이 완료되고 이제 실행만 하면 될 줄 알았다.

안 됨. (ㅋㅋ)

요청한(P3 Instance launch를 위해 필요한) vCPU 용량보다 현재 가지고 있는 vCPU 제한이 더 적어서 실행이 불가하다고 한다.

2-4. Quota Increase Request : Running dedicated P3 hosts

소량의 vCPU 증가 요청은 쉽게 가능하다. 먼저, 메뉴에서 Quota Increase Request (할당량 증가 요청)를 검색해서 Service Quota에 접근한다. 이후, AWS Services 검색창에서 Amazon Elastic Compute Cloud (EC2) 를 검색하고, 선택한다.

다시 등장한 검색창에서 P3를 검색한다.

여기서 주의할 점은, All P Spot Instance Requests와 혼동하지 않아야 한다! P를 검색해서 나온 P 타입 인스턴스 할당량이라길래 저건 줄 알았는데, 알고보니 P3를 실행하기 위한 전용 할당량이 별도로 존재한다. 명칭은 Running Dedicated P3 Hosts로, P3 Hosts를 실행하기 위한 전용 - 할당량이다. 직관적인 이름이다. 검색창에 P만 치고 이건가보다 하면서 All P Spot Instance Requests 할당량 증가 요청을 하지 않도록 한다.

알맞는 할당량 항목을 골랐다면, Request quota increase를 클릭한 후 원하는 quota value (할당량 값)을 입력한다. 참고로 p3.2xlarge 인스턴스를 실행하기 위해 필요한 vCPU 값은 8이다.

증가 요청이 완료되었다.

가장 쉽게 VPC 개념잡기 https://medium.com/harrythegreat/aws-가장쉽게-vpc-개념잡기-71eef95a7098

초보자도 할 수 있는 VPC구축 https://dev.classmethod.jp/articles/for-beginner-vpc-construction/

AWS VPC 생성 후 EC2 생성하기 https://twofootdog.tistory.com/27/

1. Introduction

Amazon VPC를 신규 생성하고, 해당 VPC에 Subnet을 생성하여 EC2 instance를 생성 및 실행하기 위한 선작업을 진행한다.

2. Explanation

2-1. Generate VPC at selected region

VPC 생성에 대한 접근 경로는 VPC > Your VPCs > Create VPC 이다.

생성 시 주의할 점은, IPv4 CIDR를 설정할 때에 공인망 대역과 겹치게 되면 외부 통신이 불가능하게 되는 애로사항이 발생할 수 있으므로, 사설망 대역으로 작성해야 한다. 사설망 대역은 10.0.0.0/8 172.16.0.0/12 192.168.0.0/16 등이다.

생성 완료 화면은 아래와 같다.

VPC 내부에 생성되는 인스턴스에 퍼블릭 DNS hostname을 할당해주기 위해 DNS hostnames 항목을 활성화 상태로 변경한다.

2-2. Checkout the result of new VPC

VPC 생성이 완료되었다면 Route tables, DHCP options sets, Network ACLs, Security Groups 해당 항목이 추가된 것을 확인한다. 해당 라우팅 테이블은 VPC에 연결되어 있으며, VPC 내에 서브넷이 생성되면 해당 서브넷과도 자동으로 연결된다. route table에 default로 등록된 값은 VPC의 CIDR로, 해당 대역의 IP가 유입되게 되면 VPC 내에서 리소스를 검색하게 된다.

Network ACLs는 subnet 앞단에서 방화벽 역할을 하는 리소스다. VPC와 함께 생성되는 ACL은 inbound/outbound 각각 2개씩 rules를 기본으로 가진다. rule number가 높을 수록 우선순위가 높은 규칙이며, * 규칙은 어떠한 규칙도 등록되지 않았을 경우 사용되는 규칙이다. 위의 경우 rule number 100이 존재하기 때문에 * 규칙은 무시되며, rule number 100 규칙은 모든 트래픽을 허용하고 있다.

Security group은 인스턴스 앞에서 방화벽 역할을 한다. ACL이 서브넷의 트래픽을 제어한다면, 보안 그룹은 인스턴스의 트래픽을 제어한다. 기본적으로 보안 그룹의 아웃바운드 규칙에는 모든 트래픽이 허용되어 있고, 인바운드 규칙에는 해당 보안 그룹과 동일한 보안그룹 ID를 가진 리소스의 트래픽만 허용되어 있다. 때문에 VPC 내의 리소스들을 동일한 보안 그룹에 등록하게 되면 추가적인 인바운드 등록 없이 트래픽 허용이 가능하다.

2-3. Generate Subnets on new VPC

Subnet을 생성한다. 이 전에 생성한 VPC를 설정하고, 해당 VPC CIDR 범위에 포함되는 블록값을 입력한다.

EC2 인스턴스를 각각 별도의 서브넷에 띄워 하나의 가용존에서 장애가 발생해도 다른 가용존에 있는 EC2 인스턴스가 정상 작동할 수 있도록 별도의 가용존을 바탕으로 복수의 Subnet을 생성한다.

기존에 작성한 글 [JDBC 기초편] (https://velog.io/@codepark_kr/JDBC-Oracle-DB-Java-%EC%B4%88%EA%B8%B0-%EC%84%B8%ED%8C%85%EB%B6%80%ED%84%B0-%EB%A9%A4%EB%B2%84-%EA%B4%80%EB%A6%AC-%EC%8B%9C%EC%8A%A4%ED%85%9C-%EC%9E%91%EC%84%B1%EA%B9%8C%EC%A7%80WIP)에 이어 이번엔 상품관리 & 재고 관리 프로그램을 만들어 보았다. 이는 2개의 DB 테이블, Trigger, Sequence를 사용하여 별도의 테이블간 재고 수량 기록 및 입출고 내역 관리가 이루어진다. 이번 글에서는 기초편에서 다루지 않았던 부분들 및 Oracle DB (특히 Trigger) 중심으로 기술할 예정이다.

Requirements

<<요구사항>> 상품재고관리프로그램을 작성하세요. 다음과 같은 데이터를 담을수 있도록 처리하세요. -------------------------------------------------------------- product_id p_name price description stock -------------------------------------------------------------- nb_ss7 삼성노트북 1570000 시리즈 7 55 nb_macbook_air 맥북에어 1200000 애플 울트라북 0 pc_ibm ibmPC 750000 windows 8 10 --------------------------------------------------------------

• 상품정보를 삭제하면, 해당 입출고 데이터도 삭제되도록 처리하세요.
• 입고된 수량보다 많은 수량을 출고하려하면, 에러메세지를 보이고, 실행중지하세요.
• 상품입출고테이블에 데이터가 삽입될때마다, 자동으로 재고테이블의 수량이 변경될 수 있도록 트리거를 작성하세요.
  
• 상품테이블 PRODUCT_STOCK* PRODUCT_ID VARCHAR2(30) PRIMARY KEY, PRODUCT_NAME VARCHAR2(30) NOT NULL, PRICE NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50), STOCK NUMBER DEFAULT 0
  
• 상품입출고 테이블 PRODUCT_IO* IO_NO NUMBER PRIMARY KEY => sequence PRODUCT_ID VARCHAR2(30) references PRODUCT_STOCK Table PRODUCT_ID IODATE DATE DEFAULT SYSDATE AMOUNT NUMBER STATUS CHAR(1) CHECK (STATUS IN ('I', 'O'))
  
• (메뉴 구성 : 메인메뉴)*
  ===== 상품재고관리프로그램 =====

1.전체상품조회 2.상품아이디검색 3.상품명검색 4.상품추가 5.상품정보변경 6.상품삭제 7.상품입/출고 메뉴 9.프로그램종료
(메뉴 구성 : 수정메뉴) ===== 상품정보변경메뉴 ===== 1.상품명변경 2.가격변경 3.설명변경 9.메인메뉴로 돌아가기
(메뉴 구성 : 입출고메뉴) ===== 상품입출고메뉴===== 1.전체입출고내역조회 2.상품입고 3.상품출고 9.메인메뉴로 돌아가기

Requirements Analysis: DB

1. 테이블과 Trigger: 상품 재고 테이블과 상품 입출고 테이블, 두 가지 테이블을 생성한다. 이는 Trigger로 연결되어 상품 재고 테이블에서 새로운 상품과 그의 수량을 추가하면 입출고 테이블에 자동 입고 등록이 되고, 입출고 테이블에서 기존 상품(재고 테이블에 존재하는)을 입/출고하게되면 입출고 테이블에는 내역 반영, 상품 재고 테이블에는 재고 수량이 합산 또는 감산된다.
2. 입고된 수량보다 많은 수량을 출고할 수 없게 하려면 1. DB 자체적으로 check Constraint를 선언하여 (amount > 0)으로 데이터 무결성을 지키는 방법이 있고, 자바 내부에서 처리하는 방법도 있다. 만약을 대비하여 check Constraint 또한 선언하기로 한다.
3. 재고 테이블과 입출고 테이블은 Foreign Key(product_id) 상속 관계로 설정되어 있고, 데이터 무결성의 원칙을 지키기 위해 on delete cascade(부모 테이블의 정보를 삭제시 자식 테이블의 정보 또한 삭제) 설정이 요구된다.

Requirements Analysis: Java

1. 부수메뉴인 입출고 메뉴의 원활한 호출 및 값 대입을 위하여 vo는 각 테이블에 맞게 별도로 생성한다.
2. 입출고 메뉴의 조회 및 입/출고 처리를 위해 해당 메뉴에서는 재고 테이블이 아닌 입출고 테이블을 호출하고 처리한다. 이 때, 입출고 구분은 대입될 재고 수량의 양/음수 구분이 아닌 status 값 대입을 통해 Oracle DB의 Conditional Trigger가 자동 반영하도록 한다.
3. 입출고 테이블의 5개 Column 중, DB선에서 채번된 sequence 값을 자동 대입하는 io_no, trigger 발동 시점의 일자가 default 값으로 대입되는 io_Date, Query문에서 자동 대입 또는 파라미터 전달등을 통해 대입될 status 값은 사용자의 개입이 불필요하다. 오직 product_id와 amount만이 사용자로부터 입력 받은 값이 반영된다. 따라서 이 두 가지 항목에 대한 파라미터 생성자를 선언하면 보다 쉽고 빠른 객체 생성 및 반환이 가능하다.

Oracle DB: Table, Sequence, Trigger

<<Table: product_stock>>

create table product_stock0(
product_id varchar2(30),
product_name varchar2(30) not null,
price number(10) not null,
description varchar2(50),
stock number default 0,
constraint PK_PRODUCT_ID primary key(product_id)
on delete cascade
);
--ADD FOREIGN KEY CONSTRAINT
alter table product_stock add
constraints FK_PRODUCT_ID foreign key(product_id)
references product_stock(product_id);

<<Table: product_io>>

create table product_io(
io_no number,
product_id varchar2(30),
iodate date default sysdate,
amount number,
status char(1),
constraints CK_IO_STATUS check(status in ('I', 'O')),
constraints FK_PRODUCT_ID foreign key(product_id) 
references product_stock00(product_id)
on delete set null
);
--ADD CHECK CONSTRAINT: AMOUNT CANNOT BE NEGATIVE
alter table product_io add 
constraints CK_AMOUNT_NOT_NEGATIVE_
check (amount >= 0);

<<Create Sequence>>

create sequence seq_product;

<<Trigger(1): add Stock to I/O Table when new Product added>>

create or replace trigger trg_product1
after
insert on product_stock
for each row
BEGIN
insert into product_io values
(seq_product.nextval, :new.product_id, default, :new.stock, 'I');
END;
/

<<Trigger(2): add Stock to Stock Table from I/O Table>>

create or replace trigger trg_product03
after
insert on product_io
for each row
BEGIN
    if :new.status = 'I' then
        update product_stock
        set stock = stock + :new.amount
        where product_id = :new.product_id;
    elsif
        :new.status  = 'O' then
        update product_stock
        set stock = stock - :new.amount
        where product_id = :new.product_id;
        end if;
        END;
/

<<Don't forget to commit!>>

commit;

요구사항 분석에 맞게 생성한 2개의 테이블, 2개의 Trigger, 그리고 Sequence이다. 이 중 Trigger의 역할이 특히 중요한데, 첫 번째 Trigger는 재고 테이블에 새로운 상품이 등록되면 이를 입출고 테이블에 등록 및 입고 처리한다. 재고 테이블의 Column 중 Stock, 즉 재고 항목이 있기 때문에, 이 역시 일종의 입고에 해당되기 때문이다.

또한 두 번째 Trigger는 입출고 테이블에 입/출고를 진행할 때에 status Column의 값에 따라 입/출 여부를 판별하여 수량을 반영한다. IF-Condition과 PSEUDO를 활용하여 새로 대입된 수량(amount)를 기존의 (PSEUDO가 붙지 않은 Column명 = 해당 Column의 기존 값) 값에 더하거나 빼는 식이다. 입출고 테이블의 입/출고는 java의 입/출 메뉴에서 진행될 것이고, Trigger를 통해 재고 테이블의 총 수량에 자동 반영될 것이다.

Properties: PreparedStatement

<<product-query.properties>>

##############################
###STOCK MANAGEMENT QUERIES###
##############################
selectAll = select * from product_stock0
getID = select product_id from product_stock0 where product_id = ?
selectID = select * from product_stock0 where product_id like ?
selectName = select * from product_stock0 where product_name like ?
insert = insert into product_stock0 values (?,?,?,?,?)
delete = delete from product_stock0 where product_id = ?
update1 = update product_stock0 set 
update3 = = ? where product_id = ?
selectAllio = select * from product_io0
addStock = insert into product_io0 values (seq_product.nextval,?,default, ?, 'I')
subtractStock = insert into product_io0 values (seq_product.nextval,?,default, ?, 'O')
getAmount = select stock from product_stock0 where product_id = ?

DAO 클래스에 대입될 Query문, 즉 preparedstatement pstmt = conn.prepareStatement(String sql);에 대입될 미완성 쿼리문을 .properties 파일에 저장해 두었다. 이 뿐만 아니라 프로그램을 구동시키기 위한 필수 정보 중 하나인 문자열 driver, url, user, password 등을 properties로 작성하여 불러오면 보안성 및 관리의 편의성 향상을 꾀할 수 있다. 이는 key-value의 형식을 취하고 있기 때문에 .getProperty(String key); 와 같이 불러올 수 있다.

또한 위의 properties 파일에서 key update1, update3은 매개인자로 대상 String을 받아와 이와 결합하기 위함이다. 단 conn.prepareStatement()의 인자로 전달할 문자열은 "결합이 완료된" 쿼리문이어야 한다. 즉 String sql = update1 + 인자로 받은 대상 문자열 + update3의 형식이어야 한다는 것. 또한 properties 파일을 작성할 때엔 쿼리문을 반드시 한 줄로 작성하여야 한다.

Java: Negative Stock Exception Handling(1) Get Existing Stock

<<Comparison: get existing Stock>>

--FROM DAO: GET EXISTING STOCK
    public int getAmount(Connection conn, String productID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = prop.getProperty("getAmount");
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, productID);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                amount = rset.getInt("stock");
                }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return amount;
    }

--QUERY FROM PROPERTIES
getAmount = select stock from product_stock0 where product_id = ?

요구 사항 중, 재고 테이블의 총 수량보다 많은 수량을 출고하려고 할 때 에러를 발생시키고, 프로그램을 종료시키라는 항목이 있었다. 이를 처리하기 위해서는 다음과 같은 방법이 존재한다:

0. Oracle DB에서 Check Constraint (amount >= 0) 선언
1. SQLIntergrityConstraintViolationException의 예외처리
2. 고의로 null을 발생시킨 후 NullPointerException 예외처리
3. 총 수량을 int 값으로 받아온 후 사용자가 입력한 출고 수량과 대조 및 조건절 처리

*0번, DB에서 check Constraint를 거는 것은 선택이 아닌 필수이다. * (DB측에서 다시 한 번 검증하여 에러를 발생시켜 데이터 무결성을 지킬 수 있기 때문에)

또한 2번, SQLIntergrityConstraintViolationException 항목이 꽤나 재미있는 방법이다. 이름은 참 길지만 정말 직관적인 네이밍 센스다. 이를 직역하면 SQL 무결성(Intergrity) 제약조건(Constraint) 위반(Violation) 예외(Exception)로, DB에서 테이블에 사전 설정한 제약 조건이 위반되어 데이터의 무결성을 해치게 되는 상황에 발생하는 예외이며 이는 SQLException의 후손이다.

입출고 관리의 대상 테이블인 입출고 테이블에서 사전 선언된 Constraint는 총 2가지로, primary Key가 부여된 io_no와 check를 부여한 amount가 있었다. 이 중, io_no는 sequence를 통해 채번된 중복 없는 고유번호가 Trigger를 통해 자동으로 대입되고 있으므로 예외 발생 가능성이 존재하는(즉 사용자로부터 입력값을 받는) Column은 오직 amount 뿐이다. 따라서 SQL무결성 제약조건 위반 Exception을 예외처리하면 해결될 것 같지만, 이는 오직 표면적인 방법일 뿐이다. Trigger를 통해 재고 테이블과 입출고 테이블이 연동되어 있으므로 전자 테이블의 제약조건이 위반되는 경우가 발생하면 이 역시 같은 예외를 발생시키기 때문이다. 다시 말해, "총 수량보다 많은 수량을 출고하려고 할 때" 이 한 가지 경우에만 한정하기 위해서는 별도의 조건절이 필요하기에 다루기 다소 까다로우며 효율이 좋지 않다.

코드를 작성하며 위에 작성한 방법 모두를 시도해 보았고, 여타 예외 상황이 발생할 가능성이 없는 3번+4번 방식으로 최종 진행하였다. 별도의 메소드를 통해 현 재고의 총 수량을 받아온 후 이를 int로 리턴받았으며, 이 반환값과 사용자가 입력한 출고 수량을 대조하여 출고 결과가 음수가 아닐 때만 출고를 진행하였다. 또한 출고 결과가 음수일 경우 출고를 진행하지 않고, null을 리턴하게 한다. 이후 이 null로 인해 발생하는 NullPointerException을 예외처리하여 별도의 메세지를 출력한 후 프로그램을 의도된 비정상 종료(System.exit(0);) 하도록 했다.

Java: Negative Stock Exception Handling(2) Compare with input-Stock, Print Error Message, Exit the Program

<<Compare Existing Stock with input-Stock>>

--FROM VIEW: DISTINGUISH STOCK IN/TAKE OFF
    private StockIO addStock(int i) {
        try {
            System.out.println("Which Product would you wanna stock/take Off?
                    \nPlease fill up the form");
            System.out.println("\"Exact\" Product ID? > ");
            String productID = sc.nextLine();
            System.out.println("Amount? > ");
            int amount = Integer.parseInt(sc.nextLine());
            existingStock = pc.getAmount(productID);
            if (i == 0) {
                if (amount <= existingStock) {
                    stockIO = new StockIO(productID, amount);
                } else return null;
            } else if (i == 1) {
                stockIO = new StockIO(productID, amount);
            }
        } catch (Exception e) {
            printInvalid("Form");
            e.printStackTrace();
        }
        return stockIO;
    }

<<Print Error Message>>

    public int addStock(Connection conn, StockIO stockIO, int i) {
        int result = 0;
        PreparedStatement pstmt = null;
        String sql = null;
        if(i == 1) 
        sql = prop.getProperty("addStock");
        else sql = prop.getProperty("subtractStock");
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, stockIO.getProductID());
            pstmt.setInt(2, stockIO.getAmount());
            result = pstmt.executeUpdate();
        } catch(NullPointerException npe) {
            System.out.println
            ("Input Amount less than Existing Amount of Stock.
            \nPlease check Again.");
            System.exit(0);
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeS(pstmt);
        }
        return result;
    }

예외처리 방식을 설정하고 코드를 작성하는 것까지는 좋았는데, 에러 메세지를 발생시킬 위치를 찾지 못해 고생을 조금 했다. 차근차근 생각해보면 쉽다. JDBC 기초편에서 작성한 바와 같이 현 JDBC 코드는:

1. run -> view -> controller -> service -> DAO (처리 요청, request)
2. DAO (처리값 도출) -> service -> controller -> view (처리값 반환, response)

-의 프로세스로 이루어 진다. 또한 코드의 처리 흐름을 살펴보았을 때, view에 별도 메소드를 생성하여 대조 결과 총 수량보다 출고 수량이 많으면 고의적으로 null을 리턴하고 있으므로 DAO에서 NullPointerException이 발생하게 된다. 왜냐? view에서 리턴받은 null이 service와 controller를 그대로 타고가서 DAO에서 처리하려 할 때 딱 걸리게 되니까. 따라서 DAO에서의 딱 한 번의 예외처리로 의도한 바 구현이 가능하다.

Archive: JDBC Stock Management System - zip

아카이브 겸 코드 전문의 압축 파일을 이 곳에 백업한다. 링크: https://www.dropbox.com/s/4tzmz1r6kkad4vl/park_stock_management_final.zip?dl=0

JDBC란?

Java와 Database를 연결하기 위해 이에 접근할 수 있게 만들어 주는 Programming API이며, JDBC Driver라는 Middleware를 통해 Java와 Database를 연결할 수 있다. 이 JDBC로는 Oracle JDBC Driver, MYSQL JDBC Driver, Sybase JDBC Driver등이 제공된다. Java와 Oracle DB를 연결할 때에는 ojdbc.jar(JAR = Java Archive File)를 통해 이에 필요한 클래스들을 통합적으로 제공받을 수 있다. 이는 Oracle 공식 사이트에서 제공된다.

JDBC - Eclipse Set Up

0. Eclipse Workspace 분리 및 재설정을 권장한다. 0-1. window - Perspective - Select java 0-2. Show View - problems, Error Log Activate 0-3. Preference - General - Workspace - Text File Encoding - Select UTF-8 0-4. Preference - General - Editors - Text Editors - Spelling - Encoding - Default(UTF-8)

1. library jar 파일 먼저 등록. 1-0. ojdbc파일 다운로드. (https://www.oracle.com/database/technologies/jdbcdriver-ucp-downloads.html) 1-1. lib 폴더 생성 후 ojdbc.jar 파일 넣은 후 alt+enter -> 1-2. library에서 해당 jar 파일 선택 후 apply 1-3. root Directiry에 Referenced Libraries 생성된 것 확인 2. vo 객체에 implements Serializable 선언 3. Driver Class 등록 (프로그램 시작시 한 번만 처리하면 된다.)

    public static void main(String[] args) 
        throws ClassNotFoundException{
        Class.forName("oracle.jdbc.OracleDriver");
        }
    }

Introduction: JDBC Process

package 구분에 관하여:

1. view: 사용자가 제공받을 메뉴, 화면 또는 그 틀을 제공 2. Controller: 명령을 내리고 제공하는 제어의 역할 수행 3. DAO(Database Access Object): DB를 왕래하며 쿼리 클래스에 데이터를 주고 받음 4. vo(Value Object): 데이터를 임시저장 5. Service: DAO를 제어하고 명령을 내림

JDBC Datatype 설정:

java의 Datatype과 DB의 Datatype은 일치하지 않는다. 때문에 상응하는 데이터 타입간 대략적으로 변환하는 과정이 필요하다. (e.g. String -> char, varchar2, int, float, double -> number, date -> date) 이 때, DB의 char(1)를 java의 char로 변환하는 경우 데이터를 담는 데에는 문제가 없으나, char 타입의 데이터를 직접적으로 사용하기 위한 메소드를 java에서 지원하지 않기 때문에 어차피 String으로 재변환하는 처리를 거쳐야 한다. 때문에 DB의 char(1) 역시 String 타입으로 변환해야 한다.

JDBC Process (DML)

1. DB Connection Object 생성 및 Auto-Commit Option deactivate 2. Connection Object로부터 Prepared Statement Object 생성 3. Prepared Statement에 미완성 Query 전달 및 값 대입 4. Query 실행, 결과값(int result = SQL%ROWCOUNT에 상응) 리턴받음 5. 자원 반납 (Close Statement)

JDBC Process (DQL)

1. DB Connection Object 생성 2. Connection Object로부터 Prepared Statement Object 생성 3. Prepared Statement에 미완성 Query 전달 4. Query 실행, Result Set(결과집합) 리턴받음 5. 복수의 결과인 경우 ResultSet -> vo -> List등으로 값 변환받음 6. 자원 반납 (Close Statement)

Remark

1. localhost의 PORT NUMBER는 127.0.0.1.이며, Oracle Port Number는 1521이다. 2. model, view, controller의 기본 구조로 패키지를 생성하는 것을 MVC Pattern(Model-View-Controller Pattern)이라고 한다.

코드 전문을 포함하고 있기 때문에 지금부터 후술될 내용이 좀 많다. 화면 오른쪽의 항목별 바로가기를 적극 활용하자.

(Oracle): Member DB Table

<<Member Management Table>>

create table member(
member_id varchar2(15),
password varchar2(15) not null,
member_name varchar2(30) not null,
gender char(1),
age number,
email varchar2(100),
phone char(11) not null,
address varchar2(300),
hobby varchar2(100),
enroll_date date default sysdate,
constraint pk_member primary key(member_id),
constraint ck_gender check(gender in ('M', 'F'))
);
commit;

별다를 것 없는 회원 관리 시스템용 테이블이다. Constraint pk/ck 선언을 올바르게 하는 것을 잊지 말자. (특히 unique 같은거 남발하면 나중에 귀찮아진다.)

(Java): vo.Member

<<Member Management - vo>> Package Declaration/import

package member.vo;
import java.io.Serializable;
import java.sql.Date;

Field Variables Declaration

public class Member implements Serializable{
    private String memberId;
    private String password;
    private String memberName;
    private String gender;
    private int age;
    private String email;
    private String phone;
    private String address;
    private String hobby;
    private Date enrollDate;

Constructor by Superclass/ by Fields Declaration

    public Member() {
        super();
    }
    public Member(String memberId, String password, String memberName, 
    String gender, int age, String email, String phone, String address, 
    String hobby, Date enrollDate) {
        super();
        this.memberId = memberId;
        this.password = password;
        this.memberName = memberName;
        this.gender = gender;
        this.age = age;
        this.email = email;
        this.phone = phone;
        this.address = address;
        this.hobby = hobby;
        this.enrollDate = enrollDate;
    }
    public Member(String memberId, String password, 
    String email, String phone, String address) {
        super();
        this.memberId = memberId;
        this.password = password;
        this.email = email;
        this.phone = phone;
        this.address = address;
    }
    public Member(String memberId) {
        super();
        this.memberId = memberId;
    }
    public Member(String memberId, String password, int identifier) {
        super();
        this.memberId = memberId;
        this.password = password;
    }
    public Member(String memberId, String email, double identifier) {
        super();
        this.memberId = memberId;
        this.email = email;
    }
    public Member(String memberId, String phone, char identifier) {
        super();
        this.memberId = memberId;
        this.phone = phone;
    }
    public Member(String memberId, String address, Date identifier) {
        super();
        this.memberId = memberId;
        this.address = address;
    }

getter/setter

    public String getMemberId() {
        return memberId;
    }
    public void setMemberId(String memberId) {
        this.memberId = memberId;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getMemberName() {
        return memberName;
    }
    public void setMemberName(String memberName) {
        this.memberName = memberName;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getHobby() {
        return hobby;
    }
    public void setHobby(String hobby) {
        this.hobby = hobby;
    }
    public Date getEnrollDate() {
        return enrollDate;
    }
    public void setEnrollDate(Date enrollDate) {
        this.enrollDate = enrollDate;
    }

Override toString()

    @Override
    public String toString() {
        return memberId + "\t" + password + "\t" + memberName + "\t"
                + gender + "\t" + age + "\t" + email + "\t" 
                + phone + "\t" + address+ "\t" + hobby + "\t" + enrollDate;
    }
}

상기했듯 DB의 대상 Table 내 각 Column의 Datatype을 고려하여 이에 대응하는 Field 변수를 선언하였고, 기본/파라미터 생성자 및 getter/setter 생성, toString() 메소드 오버라이딩까지 마친 상태이다. 이 vo 클래스에서 주목할 점은, 파라미터 생성자가 다양하게 선언되어 있으며 그 수가 많다는 점이다. 이는 view 클래스에서 각 컬럼별 Update DML 처리 후 오버로딩된 생성자를 통해 보다 쉽게 Member 객체를 리턴하기 위함이다. 예를 들어 password, email, phone, address 이 4가지 항목에 대해 각각 update 처리 후 Member 객체를 리턴해야 한다고 해보자. 이 4가지 경우 모두 파라미터 생성자를 통해 반환한다면 빠르고 편리하겠지만 생성자 오버로딩의 원칙상 파라미터명이 다르더라도 같은 갯수, 같은 타입의 파라미터를 갖는 생성자는 오버로딩이 불가하다.

//e.g.
public Member(String memberId, String password, int identifier) {
        super();
        this.memberId = memberId;
        this.password = password;
    }
public Member(String memberId, String email, double identifier) {
        super();
        this.memberId = memberId;
        this.email = email;
    }

때문에 이 4가지 경우에 맞는 파라미터 생성자를 우선 선언하고, 제 3의 파라미터로 각기 다른 타입의 매개변수를 받았다. (int/double/char/Date) 이들은 Member 객체를 생성하고 값을 대입할 때 각 생성자간 식별자 역할만을 수행할 뿐 Member 객체 생성에는 그 어떤 영향도 미치지 않는다. (꼼수 좀 부려본건데 이렇게 해도 되는건진 모르겠다 ㅋㅋ)

(Java): common.JDBCTemplate

<<Member Management - JDBCTemplate>> Package Declaration/import

package member.common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

(static)Registering JDBC Driver

public class JDBCTemplate {
    static {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

Generate Connection Object

    public static Connection getConnection() {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
        String user = "student";
        String password = "student";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection
            (url, user, password);
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

Conditional Method for commit/rollback

    public static void cORr(int result, Connection conn) {
        if (result > 0)
            try {
                if (conn != null && !conn.isClosed())
                    conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        else
            try {
                if (conn != null && !conn.isClosed())
                    conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

Mehods for Returning Resource(Connection/PreparedStatement/Resultset)

    public static void closeC(Connection conn) {
        try {
            if (conn != null && !conn.isClosed())
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeS(Statement stmt) {
        try {
            if (stmt != null && !stmt.isClosed())
                stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeR(ResultSet rset) {
        try {
            if (rset != null && !rset.isClosed())
                rset.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBCTemplate은 JDBC에서 필수적으로 수행해야 하는 과정들을 별도의 클래스와 메소드로 따로 빼 놓은 것이다. 코딩 시간 단축 및 코드 길이 절감에 큰 효과가 있다. 여기서의 구성 요소는:

1. (static) Registering JDBC Driver: JDBC의 경우 프로그램 실행시마다 JDBC Driver에 접속해야 하는데 이를 상수로 선언해 두어 필요할 때 코드 한 줄만 작성해주면 된다. 2. Generate Connection Object: DB와 연결하기 위한 Connection Object를 미리 선언해 두었다. 이제 url, user, password 입력을 매 때마다 하지 않아도 된다. 3. Conditional Method for commit/rollback: DML 처리 후 commit/rollback은 결과값으로 받아온 int(SQL%ROWCOUNT)값에 따라 분기가 갈리게 된다. int값이 0이면 처리된 내역이 없다는 뜻이므로 rollback, 0이상이면 해당 건수만큼 처리가 완료되었다는 뜻이므로 commit. 이 메소드는 인자로 Connection과 int result를 받아 해당 분기처리에 따라 commit 또는 rollback을 수행한다. 4. Returning Resource(Connection/PreparedStatement/Resultset): Connection, PreparedStatement, ResultSet 이 3가지 객체들의 조건절 자원 반납 메소드이다. 이는 중복된 처리 및 예외 상황을 대비해서 해당 객체가 null이 아니며 자원 반납이 이루어지지 않은 경우에만 반납하도록 작성되었다. 또한 각 자원 반납에 필수적으로 적용해야 하는 SQLException의 예외처리까지 미리 작성하여 보다 짧고 효율적인 코드 작성을 가능하게 한다.

(Java): service.MemberService

<<Member Management - Service>> Package Declaration/import

package member.model.service;
import static member.common.JDBCTemplate.*;
import java.sql.Connection;
import java.util.List;
import member.common.JDBCTemplate;
import member.model.MemberDAO;
import member.vo.Member;

Field Variables Declaration

public class MemberService {
    private MemberDAO memberDAO = new MemberDAO();
    List<Member> list = null;
    Connection conn = null;
    int result = 0;
    String pwd = null;
    String ID = null;
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
    String user = "student";
    String password = "student";

Service Methods (DML): insert, update, delete

    public int insertMember(Member member) {
        conn = getConnection();
        result = memberDAO.insertMember(conn, member);
        cORr(result, conn);
        closeC(conn);
        return result;
    }
    public int deleteMember(Member member) {
        conn = getConnection();
        result = memberDAO.deleteMember(conn, member);
        cORr(result, conn);
        closeC(conn);
        return result;
    }
    public int updateMember(Member member, String whichOne) {
        conn = getConnection();
        result = memberDAO.updateMember(conn, member, whichOne);
        cORr(result, conn);
        closeC(conn);
        return result;
    }

Service Methods (DQL): select *, select by member_id, select by member_name

    public List<Member> selectMemberByName(String memberName) {
        conn = getConnection();
        list = memberDAO.selectMemberByName(conn, memberName);
        closeC(conn);
        return list;
    }
    public List<Member> selectMemberByID(String memberID) {
        conn = getConnection();
        list = memberDAO.selectMemberByID(conn, memberID);
        closeC(conn);
        return list;
    }
    public List<Member> selectAll() {
        conn = getConnection();
        list = memberDAO.selectAll(conn);
        closeC(conn);
        return list;
    }

get Matched Password/ID records

    public String getPassword(String memberID) {
        conn = getConnection();
        pwd = memberDAO.getPassword(conn, memberID);
        closeC(conn);
        return pwd;
    }
    public String getID(String memberID) {
        conn = getConnection();
        ID = memberDAO.getID(conn, memberID);
        closeC(conn);
        return ID;
    }
}

DAO(Database Access Object) 클래스에게 명령을 보내고 값을 받아오는 Service 클래스이다. DML/DQL 메소드를 한 눈에 비교해 보면 그 성질상의 차이점을 확실히 할 수 있다. 우선 각 메소드의 구성요소들을 한 번 살펴보자.

<DML Method>

//e.g.
public int insertMember(Member member) {
        conn = getConnection();
        result = memberDAO.insertMember(conn, member);
        cORr(result, conn);
        closeC(conn);
        return result;
    }

1. Member 객체를 인자로 받아 그 값을 기록하고 결과값으로 정수(Rowcount)를 리턴한다. 2. 이 결과값은 memberDAO의 insertMember();에서 DB 처리 후 반환된 값이다. 3. 결과값에 따라(result > 0) 조건절 분기하여 commit 또는 rollback이 이루어진다. 4. 사용한 Connection 객체를 자원 반납한다.

<DQL Method>

//e.g.
public List<Member> selectMemberByID(String memberID) {
        conn = getConnection();
        list = memberDAO.selectMemberByID(conn, memberID);
        closeC(conn);
        return list;
    }

1. 쿼리문의 조건절에 대입하기 위한 문자열 변수로 String 인자를 받아 List<Member>를 리턴한다. 2. 이 List는 memberDAO의 selectMemberByID();에서 DB 처리 후 반환된 값이다. 3. DQL, 즉 단순 조회/조건절 조회 구문이므로 별도의 commit/rollback이 필요하지 않다. 4. 사용한 Connection 객체를 자원 반납한다.

너무너무 중요한 부분이다. 사실 패키지와 클래스의 수가 많고 다양해지면 어느 클래스의 어느 메소드가 여기로 갔다가 저기로 갔다가 정신없고 그 흐름을 쫓기 힘든 경우가 많은데, 이렇게 놓고 보면 이해가 된다. 위의 내용을 요약해 보면 이러하다:

1. DML은 DB의 해당 테이블에 값을 대입/갱신/삭제하므로 변동사항이 발생한다. 이를 위해 commit 또는 rollback 처리가 필요하며, DQL은 단순 조회문이므로 이 과정이 필요치 않다.
2. DML은 처리된 행의 갯수를 세어 정수형으로 리턴하고, DQL은 조회된 결과 집합을 사용자가 명시한 형태에 빌어(위의 경우는 List) 리턴한다.
3. 2의 과정에서 처리 결과가 없을 경우 DML은 0, DQL은 null을 리턴한다.
4. DML/DQL구분과 상관없이 사용한 Connection 객체에 대한 자원반납이 이루어져야 한다.

또한 DML문 처리시 사용자가 입력한 ID/Password의 유효성 검사를 위해 별도의 메소드 getPassword(), getID(); 를 생성하였다. 이들의 구조는:

//e.g.
    public String getPassword(String memberID) {
        conn = getConnection();
        pwd = memberDAO.getPassword(conn, memberID);
        closeC(conn);
        return pwd;
    }

1. 사용자가 기입한 String memberID를 인자로 받는다.
2. memberDAO를 거쳐 쿼리문에 where 조건절 변수로 해당 인자를 대입한다.
3. 인자로 받은 memberID에 해당하는 password가 있다면 이를 String으로 리턴한다.

위 과정을 거쳐 리턴받은 String password는 view의 메소드에서 유효성 검사(.contentEquals())를 실시하여 비밀번호가 일치하는 경우 DML 처리를 실행하도록 활용할 수 있다.

(Java): vo.MemberDAO

<<Member Management - DAO>> Package Declaration/import

package member.model;
import static member.common.JDBCTemplate.closeR;
import static member.common.JDBCTemplate.closeS;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import member.vo.Member;

Field Variables Declaration

public class MemberDAO {
    List<Member> list = null;
    String pwd = null;
    String ID = null;
    int result = 0;

(DML) Insert new Member

    public int insertMember(Connection conn, Member member) {
        PreparedStatement pstmt = null;
        String sql = "insert into member values (?,?,?,?,?,?,?,?,?,default)";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getMemberId());
            pstmt.setString(2, member.getPassword());
            pstmt.setString(3, member.getMemberName());
            pstmt.setString(4, member.getGender());
            pstmt.setInt(5, member.getAge());
            pstmt.setString(6, member.getEmail());
            pstmt.setString(7, member.getPhone());
            pstmt.setString(8, member.getAddress());
            pstmt.setString(9, member.getHobby());
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeS(pstmt);
        }
        System.out.println("result@dao = " + result);
        return result;
    }

(DML) Update a Member password, email, phone, address

    public int updateMember(Connection conn, Member member, String whichOne) {
        PreparedStatement pstmt = null;
        String part1 = "update member set ";
        String part2 = whichOne;
        String part3 = " = ? where member_id = ?";
        String sql = part1 + part2 + part3;
        try {
            pstmt = conn.prepareStatement(sql);
            switch (whichOne) {
            case "password":
                pstmt.setString(1, member.getPassword());
                break;
            case "email":
                pstmt.setString(1, member.getEmail());
                break;
            case "phone":
                pstmt.setString(1, member.getPhone());
                break;
            case "address":
                pstmt.setString(1, member.getAddress());
                break;
            }
            pstmt.setString(2, member.getMemberId());
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeS(pstmt);
        }
        System.out.println("result@dao = " + result);
        return result;
    }

(DML) Delete a Member

    public int deleteMember(Connection conn, Member member) {
        PreparedStatement pstmt = null;
        String sql = "delete from member where member_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getMemberId());
            result = pstmt.executeUpdate();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeS(pstmt);
        }
        return result;
    }

(DQL) select * from member where member_name = ?

    public List<Member> selectMemberByName(Connection conn, String memberName) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select * from member " + "where member_name like ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + memberName + "%");
            rset = pstmt.executeQuery();
            list = new ArrayList<>();
            while (rset.next()) {
                Member member = new Member();
                member.setMemberId(rset.getString("member_id"));
                member.setPassword(rset.getString("password"));
                member.setMemberName(rset.getString("member_name"));
                member.setAge(rset.getInt("age"));
                member.setGender(rset.getString("gender"));
                member.setEmail(rset.getString("email"));
                member.setPhone(rset.getString("phone"));
                member.setAddress(rset.getString("address"));
                member.setHobby(rset.getString("hobby"));
                list.add(member);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return list;
    }

(DQL) select * from member where member_id = ?

    public List<Member> selectMemberByID(Connection conn, String memberID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select * from member " + "where member_id like ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + memberID + "%");
            rset = pstmt.executeQuery();
            list = new ArrayList<>();
            while (rset.next()) {
                Member member = new Member();
                member.setMemberId(rset.getString("member_id"));
                member.setPassword(rset.getString("password"));
                member.setMemberName(rset.getString("member_name"));
                member.setAge(rset.getInt("age"));
                member.setGender(rset.getString("gender"));
                member.setEmail(rset.getString("email"));
                member.setPhone(rset.getString("phone"));
                member.setAddress(rset.getString("address"));
                member.setHobby(rset.getString("hobby"));
                list.add(member);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return list;
    }

(DQL) select * from member

    public List<Member> selectAll(Connection conn) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select * from member";
        try {
            pstmt = conn.prepareStatement(sql);
            rset = pstmt.executeQuery();
            list = new ArrayList<>();
            while (rset.next()) {
                Member member = new Member();
                member.setMemberId(rset.getString("member_id"));
                member.setPassword(rset.getString("password"));
                member.setMemberName(rset.getString("member_name"));
                member.setAge(rset.getInt("age"));
                member.setGender(rset.getString("gender"));
                member.setEmail(rset.getString("email"));
                member.setPhone(rset.getString("phone"));
                member.setAddress(rset.getString("address"));
                member.setHobby(rset.getString("hobby"));
                list.add(member);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return list;
    }

get member Password/ID for verification

    public String getPassword(Connection conn, String memberID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select password from member where member_id like ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + memberID + "%");
            rset = pstmt.executeQuery();
            while (rset.next()) {
                pwd = rset.getString("password");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return pwd;
    }
    public String getID(Connection conn, String memberID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select member_id from member where member_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, memberID);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                ID = rset.getString("member_id");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return ID;
    }
 }

JDBC의 핵심인 DAO 클래스이다. 마찬가지로 DML/DQL 메소드 하나씩을 떼 와 그 구조를 비교하고 주요한 특징을 추려보기로 한다.

<<DML - Insert new Member>>

//e.g. DML
    public int insertMember(Connection conn, Member member) {
        PreparedStatement pstmt = null;
        String sql = "insert into member values (?,?,?,?,?,?,?,?,?,default)";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getMemberId());
            pstmt.setString(2, member.getPassword());
            pstmt.setString(3, member.getMemberName());
            pstmt.setString(4, member.getGender());
            pstmt.setInt(5, member.getAge());
            pstmt.setString(6, member.getEmail());
            pstmt.setString(7, member.getPhone());
            pstmt.setString(8, member.getAddress());
            pstmt.setString(9, member.getHobby());
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeS(pstmt);
        }
        System.out.println("result@dao = " + result);
        return result;
    }

1. 인자로 Connection객체와 Member 객체를 받아서 int result(Rowcount)를 리턴한다. 2. String으로 Query문을 작성하고 대입할 값 또는 변수를 ?로 표기한다. 3. PreparedStatement는 미완성 쿼리문에 값 전달을 위한 일종의 temp와 유사하다. 미완성 상태인 String sql문에 대입할 값을 받은 후, 실제 DB와 연결하는 객체인 Connection과의 다리를 놓는 중간 역할을 수행한다. 4. pstmt.setString();의 두 인자는 String sql에 대입될 순서(즉 ?의 번짓수), 대입될 값이다. 5. DML에서 완성된 pstmt의 값 전달 및 실행 구문은 "executeUpdate()" 즉 "업데이트 실행"이다. 6. 사용한 Preparedstatement(상위개체 - Statement)의 자원을 반납한다.

<<DQL - Conditional Select - by member_id>>

//e.g. DQL
    public List<Member> selectMemberByID(Connection conn, String memberID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select * from member " + "where member_id like ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + memberID + "%");
            rset = pstmt.executeQuery();
            list = new ArrayList<>();
            while (rset.next()) {
                Member member = new Member();
                member.setMemberId(rset.getString("member_id"));
                member.setPassword(rset.getString("password"));
                member.setMemberName(rset.getString("member_name"));
                member.setAge(rset.getInt("age"));
                member.setGender(rset.getString("gender"));
                member.setEmail(rset.getString("email"));
                member.setPhone(rset.getString("phone"));
                member.setAddress(rset.getString("address"));
                member.setHobby(rset.getString("hobby"));
                list.add(member);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return list;
    }

1. 인자로 Connection객체와 String(조건절 목적값)을 받아 결과 집합을 list에 대입해 리턴한다. 2. pattern matching 조건절 검색을 위한 wild card의 대입 또한 필요하므로 pstmt.setString();의 2번째 인자로 양 와일드카드와 메소드 인자인 String memberID를 결합하여 선언한다. 이들의 결합된 값(String)이 미완성 쿼리의 ? 위치에 대입된다. 3. DQL에서 완성된 pstmt의 값 전달 및 실행 구문은 "executeQuery()" 즉 "질의 실행"이다. 4. 질의 결과는 Result Set(결과 집합)을 통해 도출되며, rset.getString("ColumnName");을 통해 결과 집합 내 특정 컬럼의 결과값을 받을 수 있다. 위의 코드에서는 이렇게 받아낸 결과값을 이에 상응하는 Member 객체의 각 변수에 대입하였다. 5. 결과 출력의 편의성을 위해 ResultSet으로부터 재생성한 Member 객체를 list에 담아 리턴한다. 6. 사용한 ResultSet, Preparedstatement(상위개체 - Statement)의 자원을 반납한다.

또한 DML 처리 이전 사용자의 ID/Password 유효성 검사를 위해 사용자가 입력한 ID와 대응되는 실 ID/실 Password를 받아오는 메소드를 별개로 작성하였다. 이의 구조는 다음과 같다:

<<DQL - Conditional Select - by member_id>>

//e.g. get Matching password (Conditional DQL)
    public String getPassword(Connection conn, String memberID) {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String sql = "select password from member where member_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, memberID);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                pwd = rset.getString("password");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            closeR(rset);
            closeS(pstmt);
        }
        return pwd;
    }

구조는 상기한 DQL문과 유사하다. 메소드의 인자로 조건절의 목적값인 String memberID를 사용자로부터 받아와 미완성 쿼리에 대입하고, DB에 이와 대응되는 memberID가 있다면 해당 ID의 password를 String으로 리턴한다.

(Java): view.MemberMenu

<<Member Management - view>> Package Declaration/import

//e.g. DML
package member.view;
import java.util.ArrayList;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Scanner;
import member.controller.MemberController;
import member.vo.Member;

Field Variables Declaration

public class MemberMenu {
    Scanner sc = new Scanner(System.in);
    Member member = new Member();
    MemberController mc = new MemberController();
    List<Member> list = new ArrayList<>();
    List<Member> selectByNameL = new ArrayList<>();
    List<Member> selectByIDL = new ArrayList<>();
    String memberNameStr;
    String memberIDStr;
    String memberPW;
    int result = 0;
    String menuStr = "= Member Management Program 2 =\n" + "1. 회원 전체 조회\n" + "2. 회원 아이디 조회\n" + "3. 회원 이름 검색\n"
            + "4. 회원 가입\n" + "5. 회원 정보 변경\n" + "6. 회원 탈퇴\n" + "9. 프로그램 종료\n" + "--------------------------------\n"
            + "선택 > ";
    String subMenuStr = "****** 회원 정보 변경 메뉴******\r\n" + "1. 암호변경\r\n" + "2. 이메일변경\r\n" + "3. 전화번호변경\r\n"
            + "4. 주소 변경\r\n" + "9. 메인메뉴 돌아가기";

View - Switch: DQL(select all, select by member_id, select by member_name)

public void mainMenu() throws NullPointerException {
    while (true) {
        System.out.println(menuStr);
        int choice = sc.nextInt();
        try {
            switch (choice) {
            case 1: // Select All (DQL) (F)
                list = mc.selectAll();
                displayMemberList(list);
                break;
            case 2: // Select by ID (DQL) (F)
                memberIDStr = inputMemberID();
                selectByIDL = mc.selectMemberByID(memberIDStr);
                displayMemberList(selectByIDL);
                break;
            case 3: // Select by Name (DQL) (F)
                memberNameStr = inputMemberName();
                selectByNameL = mc.selectMemberByName(memberNameStr);
                displayMemberList(selectByNameL);
                break;

View - Switch: DML(insert member, update, delete)

            case 4: // Sign Up (DML) (F)
                member = insertMember();
                result = mc.insertMember(member);
                displayMsg(result, "Sign Up");
                break;
            case 5: // Update Information(DML) (F)
                System.out.println(subMenuStr);
                int choice2 = sc.nextInt();
                try {
                    switch (choice2) {
                    case 1:
                        member = updateMember("password");
                        result = mc.updateMember(member, "password");
                        displayMsg(result, "Update");
                        break;
                    case 2:
                        member = updateMember("email");
                        result = mc.updateMember(member, "email");
                        displayMsg(result, "Update");
                        break;
                    case 3:
                        member = updateMember("phone");
                        result = mc.updateMember(member, "phone");
                        displayMsg(result, "Update");
                        break;
                    case 4:
                        member = updateMember("address");
                        result = mc.updateMember(member, "address");
                        displayMsg(result, "Update");
                        break;
                    case 9:
                        System.out.println(menuStr);
                        break;
                    }
                } catch (NullPointerException npe) {
                    printInvalid("ID or Password");
                    break;
                }
                break;
                case 6: // Delete (DML) (F)
                    member = deleteMember();
                    int result = mc.deleteMember(member);
                    displayMsg(result, "Delete");
                    break;
                case 9:
                    System.out.println("Do you really wanna Quit? (Y/N)");
                    char yn = sc.next().toUpperCase().charAt(0);
                    if (yn == 'Y')
                        return;
                    break;
                default:
                    printInvalid("ID");
                }
            } catch (InputMismatchException imme) {
                printInvalid("Number");
            } catch (NullPointerException npe) {
                printInvalid("Password");
            }
        }
    }

Methods for DML

    private Member deleteMember() {
        System.out.println("Please Insert a ID for delete");
        System.out.print("ID? > ");
        String inputID = sc.next();
        String realID = mc.getID(inputID);
        String realPW = mc.getPassword(inputID);
        System.out.println("Existing password? > ");
        String inputPW = sc.next();
        if (inputID.contentEquals(realID) && inputPW.contentEquals(realPW)) {
            System.out.println(new Member(inputID).toString());
            return new Member(inputID);
        } else
            return null;
    }
    private Member updateMember(String whichOne) {
        System.out.println("Please Insert ID for update");
        System.out.print("ID? > ");
        String inputID = sc.next();
        String realID = mc.getID(inputID);
        String realPW = mc.getPassword(inputID);
        String newP = null;
        if (inputID.contentEquals(realID)) {
            selectByIDL = mc.selectMemberByID(inputID);
            displayMemberList(selectByIDL);
            System.out.println("Existing password? > ");
            String existingPassword = sc.next();
            if (existingPassword.contentEquals(realPW)) {
                System.out.println("New " + whichOne + "? > ");
                // Identifier for Overloaded Constructors:
                // int/double/char/Date
                switch (whichOne) {
                case "password":
                    newP = sc.next();
                    member = new Member(inputID, newP, 1);
                    break;
                case "email":
                    newP = sc.next();
                    member = new Member(inputID, newP, 0.1);
                    break;
                case "phone":
                    newP = sc.next();
                    member = new Member(inputID, newP, 'i');
                    break;
                case "address":
                    sc.nextLine(); // to empty buffer
                    newP = sc.nextLine();
                    member = new Member(inputID, newP, null);
                    break;
                }
                System.out.println(menuStr);
            }
            return member;
        } else {
            printInvalid("number");
            System.out.println(menuStr);
            return null;
        }
    }

Input Data Section

    private String inputMemberName() {
        System.out.print("Name? > ");
        return sc.next();
    }
    private String inputMemberID() {
        System.out.print("ID? > ");
        return sc.next();
    }
    // Constructor from Superclass + setter
    private Member insertMember() {
        System.out.println("system: Please fill up the form");
        System.out.print("ID: ");
        member.setMemberId(sc.next());
        System.out.println("Password: ");
        member.setPassword(sc.next());
        System.out.println("Name: ");
        member.setMemberName(sc.next());
        System.out.println("Age: ");
        member.setAge(sc.nextInt());
        System.out.println("Gender(M/F): ");
        member.setGender(String.valueOf(sc.next().toUpperCase().charAt(0)));
        System.out.println("Email: ");
        member.setEmail(sc.next());
        System.out.println("Contact Number\n(numbers only): ");
        member.setPhone(sc.next());
        System.out.println("Address: ");
        sc.nextLine(); // to empty Buffer
        member.setAddress(sc.nextLine());
        System.out.println("Hobby: \n(without Puctation Marks/Space Characters) ");
        member.setHobby(sc.next());
        return member;
    }

Show Result List/system Messages Section

    private void displayMemberList(List<Member> list) {
        System.out.println("---------------------------------------");
        if (list == null || list.isEmpty()) {
            printInvalid("Data");
        } else {
            for (Member member : list) {
                System.out.println(member);
            }
            System.out.println("---------------------------------------");
        }
    }
    private void displayMsg(int result, String msg) {
        if (result > 0) 
            System.out.println("Process Success: successfully " + msg + "d");
        else
            System.out.println("Process Failed: " + msg + " failed");
    }
    private void printInvalid(String which) {
        System.out.println("system: Invalid " + which + "\nPlease check again\n");
    }
}

String으로 선언해 둔 메뉴를 출력하고, 사용자로부터 선택 번호를 받아 그에 해당하는 기능을 수행하고 결과를 콘솔창에 출력하는 구조로 view를 작성하였다. 선택 번호(int choice/choice2)에 따른 동작을 수행하기 위해 switch문을 기반으로 코드를 작성하였으며, 상기했던 대로 DML 작업을 위해 ID/Password를 사용자로부터 받고 그에 대응되는 값을 찾아 유효성 검사를 진행하였다. view 메뉴를 작성하며 특히 신경을 썼던 부분은 password, email, phone, address Column의 정보를 각각 수정하기 위해 단 하나의 메소드를 사용하도록 코드를 작성한 점이다.

private Member updateMember(String whichOne) {
        System.out.println("Please Insert ID for update");
        System.out.print("ID? > ");
        String inputID = sc.next();
        String realID = mc.getID(inputID);
        String realPW = mc.getPassword(inputID);
        String newP = null;
        if (inputID.contentEquals(realID)) {
            selectByIDL = mc.selectMemberByID(inputID);
            displayMemberList(selectByIDL);
            System.out.println("Existing password? > ");
            String existingPassword = sc.next();
            if (existingPassword.contentEquals(realPW)) {
                System.out.println("New " + whichOne + "? > ");
                // Identifier for Overloaded Constructors:
                // int/double/char/Date
                switch (whichOne) {
                case "password":
                    newP = sc.next();
                    member = new Member(inputID, newP, 1);
                    break;
                case "email":
                    newP = sc.next();
                    member = new Member(inputID, newP, 0.1);
                    break;
                case "phone":
                    newP = sc.next();
                    member = new Member(inputID, newP, 'i');
                    break;
                case "address":
                    sc.nextLine(); // to empty buffer
                    newP = sc.nextLine();
                    member = new Member(inputID, newP, null);
                    break;
                }
                System.out.println(menuStr);
            }
            return member;
        } else {
            printInvalid("number");
            System.out.println(menuStr);
            return null;
        }
    }

본 메소드 하나로 4가지 Column에 대한 update(DML)작업을 각각 수행하기 위해, 인자로 String whichOne을 받아 각 항목에 대입하였다. **DML/DQL 작업 모두 공통적으로 view -> Controller -> Service -> DAO(값 도출) -> Service(리턴된 값 전달) -> Controller (리턴된 값 전달) -> view (리턴된 값 받아서 후처리 후 최종 결과 출력)의 순서로 진행된다.** 이 때, view에서 update 메소드의 인자로 받은 String whichOne을 값 도출 DAO 메소드까지 전달하여 미완성 쿼리문의 조건절 목적값으로 활용하고 있다. **4가지 Column의 정보를 각각 수정한다고 한들, 목적 컬럼만이 다를 뿐이지 쿼리문의 구조 자체는 같기 때문에 하나의 메소드 + String 메소드 인자 + 제 3의 인자를 식별자로 받은 생성자 오버로딩을 통해 4가지 결과 도출이 가능하다.**

***

### (Java): run.Run
>**<<Member Management - run\>>**
**Package Declaration/import**
```java
package run;
import member.view.MemberMenu;

Main Method

public class Run {
    public static void main(String[] args) {
        new MemberMenu().mainMenu();
        System.out.println("==============FINISHED================");
    }
}

실행 클래스. view 클래스인 MemberMenu의 mainMenu 메소드를 호출하고, mainMenu의 swtich 문에서 사용자가 정상적으로 종료를 선택하여 프로그램이 종료되는 경우 종료 메세지를 출력한다.

Procedure는 특정 동작을 수행하는 서브 프로그램이다. 사용자는 이를 호출하기 전에 반드시 procedure를 선언하고 정의해야 한다. 사용자는 선언과 정의를 동시에 할 수 있으며, 또는 먼저 선언한 후 같은 block 또는 서브 프로그램 내에서 이를 정의할 수 있다. (A procedure is a subprogram that performs a specific action. You must declare and define a procedure before invoking it. You can either declare and define it at the same time, or you can declare it first and then define it later in the same block or subprogram. -Oracle Docs)

보충하여 이는 일련의 작업 절차를 저장, 호출하여 재사용 하는 것이 가능하다. Procedure에는 리턴값이 존재하지 않고, 매개변수 in/out을 통해 호출부로 값 전달이 가능하다. 일반 SQL문에서는 이용할 수 없기에 PL/SQL 구문에서 사용해야 하고, 다른 Procedure (e.g. Anonymous Block) 또는 다른 Client Program에 의해 호출되는 구조이다.

Syntax: Creat and Invoke a Procedure

<<Create a Procedure>> creat or replace procedure PROC-NAME(Val1, Val2 ...add'l) IS <FIELD-VALS ON HERE> BEGIN <EXECUTE-STATEMENT ON HERE> end; / <<Invoke a Procedure>> BEGIN PROC-NAME END; /

--CERATE A PROCEDURE
create or replace procedure proc_delete_emp(
p_emp_id emp_test1.emp_id%type
)
IS
BEGIN
    delete from emp_test1
    where p_emp_id = emp_id;
    dbms_output.put_line(p_emp_id || 'successfully deleted.');
END;
/
--INVOKE A PROCEDURE
BEGIN
    proc_delete_emp(&number)
    dbms_output.put_line(SQL%ROWCOUNT || 
    ' rows successfully deleted.')
END;
/

create procedure의 경우 역시 create or replace 구문을 지원하므로, 이를 가급적 활용하는 것이 권장된다. 상기했듯 Procedure는 일반 SQL문에서 사용이 불가능하므로, 이를 호출할 때 역시 PL/SQL 구문 내에서 호출하여야하며 이 때 DECLARE절은 생략이 가능하다.

Syntax: Upsert(Update + Insert) with Procedure

<<Upsert (Update + Insert) with Procedure>> create or replace procedure PROC_NAME( Val1 in Tab.Col%type, Val2 in Tabl.Col%type(add'l) ) IS Val-On-Field DataType; BEGIN select Col into Val-On-Field from Tab where Condition if Val-On-Field Num then insert into Tab values(Val1, Val2); else update Tab set Col2 = Val2 where Col1 = Val1 end if; commit; (add'l) END: /

--CREATE UPSERT-PROCEDURE
create or replace procedure proc_upsert_job(
p_job_code in job.job_code%type,
p_job_name in job.job_name7type
)
IS
    cnt number;
BEGIN
    select count(*)
    into cnt
    from job
    where job_code = p_job_code;
    if cnt = 0 then
    insert into job values(p_job_code, p_job_name);
  else
  update job set job_name = p_job_name
  where job_code = p_job_code;
end if;
commit;
END;
/
--INVOKE THE UPSERT-PROCEDURE
execute proc_upsert_job('J8', 'Intern');
select * from job;

Upsert란 Update와 Insert의 합성어로, 특정 값등이 존재하지 않는다면 Insert(즉 CRUD의 Create, 생성), 해당 값이 이미 존재한다면 Update(갱신)함을 의미한다. 해당 Procedure를 생성할 때에 가장 중요한 것은 변수에 의한 분기 처리이다. 어떤 값을 기준으로 생성과 갱신을 나눌 것인가? 어떠한 개체를 생성하고 갱신할 것인가?

위의 예제 코드에서 전자에 해당하는 분기는 if cnt = 0 then 및 where job_code = p_job_code 에서 나뉘었으며, 후자는 insert into job values(p_job_code, p_job_name)에서 나뉘었다. job 테이블 안에서 job_code와 job_name은 분류상 대응되는 값이다. 다시 말해, 부정한 데이터가 존재하지 않는다는 전제 하에 job_code과 job_name은 직급이라는 같은 기준에 의해 분할되어 있기 때문에 job_code 또는 job_name 중 열의 한 값이 존재하지 않는다면 반대 열의 상응 값도 존재하지 않는 것이 된다. if 조건절에서 cnt는 바로 이 점을 이용하여 해당 직급이 존재하는지 그 여부를 수를 세어 확인한다. 즉, 해당 프로시져의 첫 번째 파라미터로 받은 직급 코드가 존재하지 않는다면, 이와 더불어 2번째 파라미터로 받은 직급명 데이터를 함께 생성하는 것이다.

이러한 분기 구조는 매우 유용하다. 다양한 활용이 가능하니 눈여겨 봐두자.

Introduction: DML Trigger

일종의 방아쇠. 특정 이벤트가 실행되면 저장된 코드가 실행된다. 예를 들어 Trigger 내부에 선언한 특정 테이블에 insert, update, delete와 같은 DML 코드가 실행되게 되면 또 다른 테이블에 전자 테이블의 변동 사항을 저장하는 등의 처리가 가능하여 일종의 Bridge 역할을 수행한다. 또한 Trigger 내부에서는 Transaction 처리가 불가능하다. 때문에 One-DML-Statement의 앞 또는 뒤에서 실행할 수 있으며 before/after 설정이 가능하다. for each Option을 통해 Statement Level Trigger, Row Level Trigger로 나뉜다.

Syntax: Basic DML Trigger(Row Level)

<<DML Trigger - Basic>> create or replace trigger Trigger-Name before/after insert/update/delete on Tab for each row BEGIN Execute-Statement END; /

create or replace trigger trg_member_insert
before
insert on member
for each row
BEGIN
insert into member_insert values 
(:new.member_name, :new.member_password)
END;
/
--TESTCODE
insert into member values ('park', '1234');

해당하는 문법은 실행문에 별도로 trigger와 연결하는 문법 또는 구문을 설정해 주지 않아도 자동으로 Trigger가 작동하게 된다. Trigger를 생성할 때에 해당 Table에 Trigger를 달아두었기 때문에 해당 Table에 새로운 값을 insert into하게 되면 자동으로 Trigger가 실행되어 새로운 테이블(여기서는 member_insert)에 값이 대입되는 것이다.

이는 해당 구문 내부에서 Transaction 처리가 불가능하므로, One DML Statement와 함께 trigger 내부의 DML 또한 commit/rollback된다. One DML Statement를 여러 번 실행하게 되면 기록은 총 합계가 아닌 log의 형태로 기록되므로, 실행 단위대로 새로운 row가 추가되는 형태로 반영된다.

Syntax: Stock Management with Trigger + Conditional Expression

<<Stock Management with Trigger + Conditional Expression>> create or replace trigger Trigger-Name before/after insert on Tab2 for each row BEGIN if :new.StatusCol = Val1 then update Tab1 set StockCol = StockCol+ :new.AmountCol where ProductCodeCol = :new.ProductCodeCol; else update Tab2 set StockCol = StockCol - :new.AmountCol where ProductCodeCol = :new.ProductCodeCol; end if; END; /

create table product(
pcode number primary key, 
pname varchar2(30),
brand varchar2(30),
price number,
stock number default 0
);

--IN & OUT TABLE - FOR STOCK MANAGEMENT
create table product_io(
iocode number primary key,
pcode number,
iodate date default sysdate,
amount number,
status char(1) check (status in ('I', 'O')),
constraints fk_product_io foreign key (pcode) references product(pcode)
);

create sequence seq_product;
create sequence seq_product_io;
insert into product(pcode, pname, brand, price)
values (seq_product.nextval, '갤럭시20', '삼성', 1200000);
insert into product(pcode, pname, brand, price)
values (seq_product.nextval, '아이폰11', '애플', 1500000);
insert into product(pcode, pname, brand, price)
values (seq_product.nextval, 'LG 벨벳', 'LG', 1000000);
commit;

--trigger, insert -> stock update
create or replace trigger trg_product_stock
after
insert on product_io
for each row
BEGIN
    if :new.status = 'I' then
        update product 
        set stock = stock + :new.amount
        where pcode = :new.pcode;
    else
         update product 
         set stock = stock - :new.amount
        where pcode = :new.pcode;
    end if;
END;
/

insert into product_io values (seq_product_io.nextval, 1, default, 10, 'I');
insert into product_io values (seq_product_io.nextval, 2, default, 5, 'O');
commit;
select * from product;
--Product Stock - Constraint
alter table product add constraints ck_product_stock check (stock >= 0);

위의 코드는 Trigger와 Conditional Expression을 활용하여 재고 관리 DB를 자동화한 예시이다. 이는 1. 상품 관리 테이블과 2. Identifier로 활용할 Sequence를 생성하여 이에 대입하고 3. 상품의 입출고 내역을 기록할 별도의 테이블을 생성한 후 4. 1의 상품 관리 테이블에 변동사항이 발생하면 Trigger가 발동하여 3의 테이블에 그 내역을 자동으로 저장하는 구조이다.

해당 Trigger를 작성할 때에 가장 주의해야 하는 부분은 if Statement를 통한 분기처리와 그 조건부의 설정이다. 상기한 코드에서는 식별자 'I' 및 'O'를 받아 입/출고의 경우로 분기하며, Pseudo :new를 활용하여 [기존수량] [+/-] [신규 입고/출고 수량]의 수식으로 새로운 테이블에 저장하고 있다.

Remark: Virtual Record - PSEUDO in DML Trigger

<<Virtual Record - PSEUDO in DML Trigger>> PSEUDO: :old. PSEUDO: :new. w.Insert Statement: :old. = null, :new. = newly inserted w.Update Statement: :old. = row before the update :new. = row after the update w.Delete Statement: :old. = row before the delete, :new. null

상기했듯 Trigger는 해당 Table에 대한 변경사항을 감지하고 사용자가 설정한 동작을 수행한다. 이 때, 기존 내역/변동 내역에서 추출할 값을 특정하기 위해 Virtual Record(의사레코드), 즉 Pseudo :old와 :new를 활용하고 있다. 이 의사레코드는 어떠한 DML문과 조합하느냐에 따라 가리키는 바가 다르다:

1. insert문/ :old = null, :new = 새로 입력된 행 2. update문/ :old = 변경 이전 행, :new = 변경 이후 행 3. delete문/ :old = 삭제 이전 행 :new = null

Remark: SQL%ROWCOUNT

<<Count - SQL%ROWCOUNT>>

--e.g.
--CREATE A TABLE FOR TEST
create table employee as select * from employee;
--COUNT THE ROW-COUNT WITH CONDITIONAL UPDATE STATEMENT
BEGIN
    update employee set salary = salary * 1.05 
        where salary < 2000000;
    dbms_output.put_line('Updated'|| SQL%ROWCOUNT||' salaries');
END;
/

얼마나 많은 행이 DML구문에 의해 영향을 받았는지 찾기 위해서는 SQL%ROWCOUNT의 값을 확인하면 된다고 Oracle Docs는 설명하고 있다. (To find out how many rows are affected by DML statements, you can check the value of SQL%ROWCOUNT -Oracle Docs) 변수를 별도로 선언하여 이 SQL%ROWCOUNT를 대입하여 사용하여도 되지만, PL/SQL블럭 내의 출력 구문에 바로 대입해도 해당 구문으로 인해 영향을 받은 행의 수를 결과로 확인할 수 있다.

PL/SQL에서 제공하는 반복문 문법 중 while loop Statement와 for loop Statement의 예제를 중심으로 해당 문법 및 그 활용을 정리해본다.

Syntax: While Loop

<<Syntax of the while loop>> DECLARE Val DataType := n; BEGIN while Val Operator Num loop Execute-Statement; Increment/Decrement; end loop; end; /

DECLARE
    n number := 5;
BEGIN
    while n <= 10 loop
    dbms_output.put(n || ' ');
    n := n+1;
    end loop;
    dbms_output.new_line;
END;
/

if-Statement문과 같이, loop Statement 역시 end loop;으로 반복문의 종류를 선언해 주어야 한다. line feed를 자체적으로 포함한 출력문인 dbms_output.put_line('CONTENTS'); 외에 dbms_output.put('CONTENTS')도 실행 구문으로 선언할 수 있으나, 반드시 BEGIN절의 후위에 dbms_output.new_line;을 작성하여 buffer에 잔류하는 후자 코드를 출력해 주어야 한다.

Syntax: Multiplication Table with loop & if

<<Syntax of the Multiplication Table with loop & if>>

DECLARE
    Multiplicand number;
    Multiplier number;
BEGIN
    Multiplicand := &number;
    Multiplier := 1;
    if Multiplicand between 2 and 9 then
    while Multiplier <= 9 loop
        dbms_output.put_line(Multiplicand || 
        ' * ' || Multiplier || ' = ' || 
        Multiplicand * Multiplier);
        Multiplier := Multiplier + 1;
        end loop;
    else 
    dbms_output.put_line('system: ' ||
    Multiplicand || ' is invalid number.');
    end if;
END;
/

사용자에게 정수 2에서 9사이의 Multiplicand(피승수)를 Prompt Popup으로 입력받아 Multiplication Table(구구단)을 출력하는 코드이다. Multiplier(승수)는 1부터 9까지 1씩 증가한다. 복잡한 구조는 아니지만, 아직 문법에 익숙하지 않다면 1. Increment/Decrement의 필수 작성, 2. end if;와 end loop;를 통한 Conditional Expression/Loop Statement의 종료를 선언하는 부분을 보다 유의해야 하겠다. 또한, increment/Decrement를 빠트리고 작성하는 경우 Complie 시에는 문제를 일으키지 않지만 Runtime시에 [ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes] 에러가 발생한다.

Syntax: Continue & Exit Statement

<<Continue & Exit Statement>> Syntax of countinue Statement: BEGIN Increment/Decrement continue when Condition; Syntax of exit Statement: BEGIN exit when Condition; Increment/Decrement

DECLARE
n number;
BEGIN
n := &number;
if n between 1 and 3 then
while n < 30 loop
n := n * n;
--e.g.EXIT 
exit when n > 15;
--e.g. CONTINUE
continue when mod(n, 2) = 0;
...

여기서 주의할 점, continue Statement는 증감식 후위에 작성하여야 하며 exit Statement는 증감식의 전위에 작성하여야 한다. 전자의 경우 증감식 전위에 작성하게 되면 증감식이 종료된 후 다시 해당 구문으로 회귀하므로 해당 구문이 종료될 수 없게 되며, 후자의 경우 주어진 증감식이 이미 끝난 후에 탈출 구문이 실행되므로 작성 의미가 없게 된다.

Syntax: for in, for in reverse loop Statement

<<for in loop Statement>> Syntax of the for in/for in reverse loop: BEGIN for Num in FirstVal..LastVal loop --OR for Num in reverse FirstVal..LastVal loop Execute-Statement end loop; END; /

BEGIN
    for m in 5..10 loop
    --OR
    for m in reverse 5..10 loop
    dbms_output.put('result: '|| m);
    end loop;
    dbms_output.new_line;
END;
/

Increment/Decrement를 DECLARE Clause에 선언하지 않아도 되며, 자동 Increment/Decrement(reverse)로 선언한 FirstVal부터 LastVal까지 무조건 1씩 증가 또는 1씩 감소한다. 문법상 FirstVal과 LastVal 사이에 point가 2개인 것을 유의하여 작성하여야 한다.

Syntax: Generate Random Numbers

<<Generate Random Numbers>> Syntax of the random.value: DECLARE ValName DataType; BEGIN ValName := dbms_random.value(MinVal, MaxVal); --If wanna get Integer-Val, --Must use to trunc to random.Val Execute-Statement(add'l) END; /

DECLARE
    rnd number;
BEGIN
    rnd := trunc(dbms_random.value(1, 101));
    dbms_output.put_line(rnd);
END;
/

또한 Sequence와 더불어 응용한 다음의 예제도 있다:

*[요구사항] 새로운 테이블을 생성하고 1부터 100까지의 난수를 발생시킨 후, Sequence를 통해 채번한 정수와 난수를 테이블에 삽입하라. *

<<Insert Random Numbers and Sequence Number to Table>>

--CREATE A TABLE FIRST:
create table tbl_rnd(
pk_num number constraint PK_NUM primary key,
rnd number);
--CREATE A SEQUENCE:
create sequence num_seq;
--INSERT THEM TO TABLE
DECLARE
    rndno number;
    sum_no number := 0;
BEGIN
    for no in 1..50 loop
    rndno := trunc(dbms_random.value(1, 101));
    insert into tbl_rnd values(num_seq.nextval, rndno);
    sum_no := sum_no + rndno;
    dbms_output.put_line(sum_no);
    end loop;
    commit;
END;
/
--TESTCODE
select * from tbl_rnd;
select sum(rnd) from tbl_rnd;

주: sum은 예약어이므로 변수명으로 사용하면 에러가 발생한다.

Remark: Codying Dynamic SQL Statement

<<Execute DDL Statement in PL/SQL Statement>> Syntax of the Dynamic SQL DDL: execute immediate 'EXECUTE-STATEMENT-ON-HERE'

execute immediate 'truncate table tbl_rnd';

PL/SQL 구문 자체적으로는 내부에서 DDL을 실행하는 것이 불가능하며, 이러한 경우에는 Dynamic Coding(동적 코딩) 문법을 별도로 작성하여 실행할 수 있다. 문법이 다소 독특한데, 명령어 execute immediate의 후위의 Quotation Marks(' ') 내부에 실행시킬 명령문을 작성한 후 세미콜론을 붙여주면 된다.

LOG: 3:08 AM 6/24/2020

NCS <SQL 응용> 시험을 마치고, 복기 및 추가 내용 정리가 필요하다고 생각되어 그 내용을 이 곳에 정리한다. 기출된 문제들과 직접 작성했던 답안들을 가감없이 옮겨낸 후, 보충이 필요한 부분은 추가로 주석을 달겠다.

SQL 응용 - 서술형(신)

1. 데이터베이스의 관리자 계정과 사용자 계정의 역할에 대하여 설명하시오. (40점)

기존 작성 답안: 관리자 계정: 즉 Super User. sys, system 두 가지의 계정을 기본적으로 갖고 있으며, 데이터베이스 생성 권한은 sys만 가지고 있으며 system은 가지고 있지 않다. 그 외 CRUD에 필요한 모든 권한을 다 가지고 있다. 또한 이러한 권한을 사용자 계정에 부여할 수 있다. DDL인 grant를 통해 권한을 부여하고, revoke를 통해 회수할 수 있다.

사용자 계정: 신규 생성하게 되면 권한이 없는 상태로, 관리자 계정으로부터 권한을 부여받아야 해당 작업을 할 수 있다. (데이터베이스 보안성 향상) 일반적으로 권한의 묶음인 resources Role을 부여하는 경우가 많다.

관리자 계정(Administrator) 계정이란 Database 생성과 관리를 담당하며, DB에 대한 모든 권한과 책임을 지는 계정이다. 또한 사용자(user) 계정은 General User Account라고 부르며, Database에 대한 Query(질의), 갱신, 보고서 작성 등의 작업을 수행할 수 있는 계정이다. 이는 보안상 업무에 필요한 최소한의 권한만 가지는 것을 원칙으로 한다.

2. 다음은 오라클 DB의 집합 연산자(set operator)의 종류와 특징을 설명하시오. (25점)

기존 작성 답안: SET OPERATOR란? union의 상위 개념인 집합 연산자. join이 Column과 Column간의 결합을 도출하는 것이라면 set operator는 Row와 Row간의 결합을 말한다.

• UNION/UNION ALL 합집합. 예를 들어 두 테이블의 정보를 조회할 때 A테이블과 B테이블의 데이터간 대응/미대응 여부와 관계없이 두 테이블의 모든 데이터를 조회한다. 때문에 결합될 두 쿼리문의 선언 순서는 중요하지 않다. (어차피 합집합의 결과, 즉 모든 데이터를 리턴할 것이므로)

이 때 union과 union all의 결정적인 차이점이라면

1. 중복 데이터의 허용 여부
2. 정렬 여부이다.

union의 경우 합집합한 모든 데이터 중 중복 데이터는 허용하지 않으며, 이를 기본적으로 오름차순 정렬한 값을 리턴하나, union all은 데이터의 중복 여부와 상관없이 "모든" 데이터를 리턴하며, 별도의 정렬 처리가 자동적으로 이뤄지지 않는다.

문법의 기본 양식은 다음과 같다:

e.g. --UNION select dept_code from employee union select dept_id from department;

--UNION ALL select dept_id from department union all select dept_code from employee;

이와 같이 선언하였을 때 department 테이블과 employee 테이블의 dept id/dept_code의 Column이 동일하게 각 24행으로 구성되어 있으며 그 값들이 완전하게 같다고 할 때, union의 조회 결과는 24행이 출력되며 자동으로 오름차순 정렬되고, union all의 조회 결과는 정렬없이 48행이 출력될 것이다.

• INTERSECT 교집합. 결합한 두 테이블의 "대응"데이터만이 결과로 도출된다. 이 역시 각 쿼리의 선언 순서는 중요하지 않다.

• MINUS 차집합. 먼저 선언된 테이블의 모든 데이터에서 후위에 선언된 테이블의 모든 데이터를 뺀다. 따라서 위의 union, union all, intersect와 다르게 각 쿼리의 선언 순서가 매우 중요하며, 선언 순서가 달라지면 도출되는 결과도 완전히 달라지게 된다.

3. 오라클 PL/SQL 블록 문법 작성 구조를 기술하시오. (10점)

기존 작성 답안: DECLARE(부차) 사용할 변수와 데이터 타입, 그 사이즈등을 선언한다. 또한 변수 선언 후에는 반드시 세미콜론을 붙여줘야 한다.

e.g. num_1 number; num_2 number; name varchar2(20);

BEGIN(필수) declare에서 선언한 변수에 값을 대입하거나, 연산문을 입력하거나, 출력문을 입력하는 등의 작업을 한다.

e.g. num_1 := 20; num_2 := 30;

EXCEPTION(부차) 예외처리할 예외 및 예외 발생시에 실행시킬 실행 구문을 작성한다.

END;(필수) /(익명 블럭의 끝을 알림)

4. 다음의 요구사항에 대한 PL/SQL 구문을 작성하시오. (25점)

(요구사항)

• EMP 테이블에서 ENAME 이 'ALLEN' 인 직원의 사번(EMPNO)과 이름(ENAME) 을 조회하는 구문을 실행한다.
• 조회된 사번은 ENO 변수에 이름은 ENM 변수에 기록한다.
• ENO 변수의 자료형은 EMP 테이블의 EMPNO 컬럼의 자료형을 참조한다.
• ENM 변수의 자료형은 EMP 테이블의 ENAME 컬럼의 자료형을 참조한다.
• 아래의 형식으로 출력 처리한다.

사번 이름 ------------------------------------- 1001 ALLEN

기존 작성 답안:

DECLARE
    emp_row emp%rowtype;
BEGIN
    select *
    into emp_row
    from emp
    where ename = 'ALLEN';
    dbms_output.put_line('사번 이름');
    dbms_output.put_line('----------------------------------');
    dbms_output.put_line(emp_row.empno​ || emp_row.ename);
END;
/

--DECLARE
    --eno emp.empno%type;
--enm emp.ename%type;
--BEGIN
    --select empno, ename
    --into eno, enm
    --from emp
    --where ename = 'ALLEN';
    --dbms_output.put_line('사번 이름');
        --dbms_output.put_line('--------------------------------');
            --dbms_output.put_line(eno || ' ' || enm );
--END;
--/

모범답안:

DECLARE
ENO EMP.EMPNO%TYPE;
ENM EMP.ENAME%TYPE;
BEGIN
SELECT EMPNO, ENAME
INTO ENO, ENM
FROM EMP
WHERE ENAME = ‘ALLEN’;
DBMS_OUTPUT.PUT_LINE(‘사번 이름’);
DBMS_OUTPUT.PUT_LINE(‘-----------------------‘);
DBMS_OUTPUT.PUT_LINE(ENO || ‘ ‘ || ENM);
END;
/

SQL 응용 - 문제해결 시나리오

1. 오라클 PL/SQL 구문을 익명블록으로 작성하고 바로 실행하였으나 결과가 출력되지 않았다, 문제점과 해결방법을 [원인](10점)에 기술하고, 아래의 해결조건을 적용한 PL/SQL 구문을 [조치내용](30점)에 기술하시오. (40점)

(해결조건)

• PL/SQL 익명블록으로 작성
• 특정 사원이 커미션을 받는지 안 받는지를 구분해서 출력하는 구문으로 작성 출력 예: 사번 7788 은 SCOTT 사원이고 커미션을 받지 않습니다. 출력 예: 사번 7654 은 MARTIN 사원이고 커미션을 1400 받습니다.
• SCOTT 계정의 EMP 테이블을 이용 사번 EMP.EMPNO, 이름 EMP.ENAME, 커미션 EMP.COMM
• 해당 컬럼을 참조하는 변수들을 선언하고, 변수에 조회결과를 저장한 다을 출력되게 작성
• PL/SQL 조건문을 사용

기존 작성 답안: [원인] 알맞게 익명 블록을 작성하였음에도 결과가 출력되지 않았다면 set serveroutput on; 을 빠트렸을 가능성이 크다.

이는 콘솔창 출력을 활성화 시키는 구문으로, 세션마다 실행하여야 한다.

따라서 익명 블럭을 작성하기 전에 set serveroutput on; 를 작성한 후 익명블럭을 재실행하면 제대로 출력된다.

[조치내용]

set serveroutput on;

DECLARE
    eno emp.empno%type;
    enm emp.ename%type;
    ecomm emp.comm%type;

BEGIN
    select empno, ename, comm
    into eno, enm, ecomm
    from emp
    where empno = '&사번';
    if ecomm is not null then
    dbms_output.put_line('사번 '|| eno || '는 ' || enm || '사원이고 
    커미션을 ' || ecomm ||' 받습니다.');
    else 
    dbms_output.put_line('커미션을 '|| ecomm ||'받지 않습니다.');
    end if;
END;
/

2. 아래의 내용에 따라 사용자 정의 롤을 만들어 사용자에게 롤로 권한을 부여하는 명령구문을 [원인](20점)에 기술하고, 아래의 공지사항을 저장할 NOTICE 테이블의 스키마를 참조하여 최근에 등록된 공지글 5개를 조회하는 TOP-N 분석 구문을 RANK() 함수와 ROWNUM을 각각 사용하여 2개의 SELECT 구문을 [조치내용](40점)에 작성하시오. (60점)

[원인]

• 생성할 롤 이름 : MYROLE
• 롤에 부여할 권한 : CREATE SESSION, CREATE TABLE, CREATE VIEW
• MYROLE 권한을 부여받을 사용자 : MYMY

[조치내용]

1. RANK() 함수를 사용한 TOP-N 분석 구문 작성
2. ROWNUM 사용한 TOP-N 분석 구문 작성

• 최근 등록된 공지글 5개 조회
• 모든 컬럼 조회함
• NOTICE 테이블 스키마

기존 작성 답안: [원인]

create role MYROLE;
grant create session to MYROLE;
grant create table to MYROLE;
grant create view to MYROLE;
grant MYROLE to MYMY;

[조치내용]

--1
select N.*,
rank() over(order by noticedate desc) RANK
from notice N
where rownum <= 5
order by noticedate desc;

--2
select *
from(
select *
from NOTICE
order by noticedate desc) N
where rownum <= 5;

RANK() OVER() 사용한 구문, 모범답안:

SELECT *
FROM (SELECT NOTICENO, NOTICETITLE, NOTICEWRITER, NOTICEDATE, 
              NOTICECONTENT, ORIGINAL_FILEPATH, RENAME_FILEPATH, 
              RANK() OVER (ORDER BY NOTICEDATE DESC) RANK
       FROM NOTICE)
WHERE RANK >= 1 AND RANK <= 5;
--OR
select *
from(
    select E.*,
            rank() over(order by hire_date DESC) rank
    from employee E
    ) E
where rank between 1 and 5;

RANK() OVER() 사용한 구문의 경우에는 표시할 행을 잘라낼 때에 rownum을 사용하지 않고, Inline View + Inline View Alias를 사용하여야 한다. rownum은 각 행을 해당 테이블에 대입할 때에(즉 insert시에) 부여된 고유의 번호로, where 조건절에 rownum을 사용하여 표시할 행을 자르는 경우 그 정렬 순위가 의도한 결과와 다르게 도출되게 된다!

https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

Oracle Docs에서는 Data Dictionary를 "Oracle Database에서 가장 중요한 파트 중 하나인 Data Dictionary는, Database에 관한 정보를 읽기 전용인 Table들의 집합으로 제공하고 있다."고 소개하고 있다. (One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database.) 즉 이는 자원을 효율적으로 관리하기 위하여 DB Object에 대한 메타 정보를 저장 및 관리하는 테이블이다. 사용자가 이 Data Dictionary를 생성/수정/삭제할 일은 없으며, 사용자가 객체를 제어할 때 자동으로 갱신된다.

Table 생성시에 작성한 Nullablity, default Values, Comments, Constraints등의 정보는 일반적인 방법으로는 조회할 수 없고, 이 Data Dictionary의 Table 조회를 통해 가능하다. 사용자가 원하는 정보에 접근하기 위해서 위의 이미지와 같이 아주 다양한 종류의 Table들이 Oracle 내부적으로 존재하고 있으며, 해당 정보에 대응되는 Table을 골라서 평소 Table 정보를 조회할 때와 같이 select Col from TABLE_NAME(여기서는 Data Dictionary에 해당하는 Table) where Condition(add'l)의 양식으로 조회하면 된다.

Types of Data Dictionary

NAMING RULE: 아래의 접두사의 후위에 위치하는 단어들은 항상 복수형을 취한다.

1. user_...: 시용자 소유의 객체를 조회한다.
2. all_...: 사용자 소유의 객체 포함, 사용 권한을 부여받은 객체를 조회한다.
3. dba_...: 관리자만 접근 가능하며 모든 Database Object를 조회할 수 있다.

이중 가장 사용빈도가 높은 Data Dictionary Table들을 다음과 같이 정리한다:

LOOK UP(1): Tables & Privileges

--LOOK UP ALL TABS FROM CURR-USER
select *
from user_tables;

--LOOK UP ALL TABS THAT CURR-USER CAN USE 
select * 
from all_tables;

--LOOK UP ALL TABS THAT SUPER USER CAN USE
select *
from dba_tables

--LOOK UP THE ROLE THAT THE USER HAS
select *
from dba_role_privs
where grantee = 'USERNAME'; --(add'l)

--LOOK UP THE PRIVILEGE THAT THE USER HAS
select *
from dba_sys_privs
where grantee = 'USERNAME';

LOOK UP(2): default Val, Datatypes, Constraints, Comments Info in Col.

순서대로 Table 내 각 Column들에 설정한 Constraint(제약조건) 조회, Table 생성 시의 Datatypes, Table 및 Column에 설정한 Comments, 각 Columns에 할당된 default값 존재 여부 및 Default Values 와 Nullability 조회 코드이다.

--LOOK UP THE CONSTRAINTS INFORMATION IN TABLE
select  UC.table_name, UCC.column_name, constraint_name, 
        UC.constraint_type, UC.search_condition
from user_constraints UC join user_cons_columns UCC
using (constraint_name)
where UCC.table_name = 'TABLE_NAME'

--LOOK UP THE DATATYPES IN TABLE
select *
from user_tab_cols
where table_name = 'TABLE_NAME'

--LOOK UP THE TABLE/COL COMMENTS
select *
from user_col_comments UCC join user_tab_comments UTC
using(table_name)
where table_name = 'TABLE_NAME'

--LOOK UP THE DEFAULT DATA/NULLABLITY
select *
from user_tabl_cols
where table_name = 'TABLE_NAME'

Introduction: PL/SQL

Oracle's Procedural Language Extension to SQL, 즉 기본 SQL의 절차지향 확장 언어. Oracle에 내장된 절차적 언어로써 기본 SQL의 단점을 보완한 것. 조건문, 반복문, 변수 선언, 변수 사용등이 가능하다.

Types of PL/SQL, Maximum Size of Datatypes in PL/SQL

• Anonymous Block: 익명 블럭. 일회용으로 간단한 블럭 구문 수행
• Procedure: 지정된 절차를 수행하는 서브 프로그램으로 저장된 형태, 언제든지 다른 Procedure, Client Program(sql developer, sqlplus, java app)등에 의해 호출 사용
• Function: procedure의 한 형태이지만, 항상 하나의 결과값을 리턴하며 일반 sql문에서도 사용이 가능하다.

Types of Variables

1. 일반 변수: 기존 SQL 타입과 동일하며, 크기가 확장됨 (아래의 표 참조)
2. 참조 변수: 기존의 Column 혹은 Table의 row타입등을 그대로 가져와 사용할 수 있다.

PL/SQL: Anonymous Block

Syntax of the print to Console: set serveroutput on; --activate the serveroutput dbms_output.put_line('CONTENTS ON HERE');

Syntax of the Anonymous Block: declare(add'l) ArgName1 DataType(Size-add'l) ArgName2 Tab.Col%type; ... begin(required) select Cols into ArgName, from Tab where Condition(add'l) Args := '&PROMPT-POPUP-MESSAGE ON HERE' exception(add'l) when EXCEPTION_NAME then EXECUTE-STATEMENT; end; /

--e.g. GET ARGS-VAL FROM TAB.COL
set serveroutput on;
declare
    Arg_no employee.emp_no%type;
        Arg_name employee.emp_name%type;
begin
    select emp_no, emp_name
        into Arg_no, Arg_name
        from employee
        where emp_id = '&사번';
       dbms_output.put_line('주민번호:'||Arg_no);
    dbms_output.put_line('사원명:'||Arg_name);
--exception
end;
/

Remark:

1. (declare) ArgName Tab.Col%type 선언으로 특정 Table.Column의 데이터 타입을 참조할 수 있다.
2. 출력문의 변수를 nvl()처리 할 수 있다. (e.g.dbms_ouput.put_line(nvl(emp_row.phone, 'null'));)
3. Arg := '&PROMPT-POPUP-MSG', 즉 prompt pop-up으로 사용자로부터 Argument를 받아 처리할 수 있다.
4. 대입연산자로 =가 아닌 :=를 사용하며, 기호 =는 동등비교를 위한 Equal, 즉 Single-Row Operator이니 혼용하지 않도록 주의하자.
5. (declare) Arg Tab%rowtype; (begin) dbms_output.put_line('CONTENT'|| Arg.Col); 의 형식으로 해당 Table의 row를 통째로 불러온 후, 출력시에 ArgumentName.ColumnName의 문법으로 Column을 호출할 수 있다.

PL/SQL: Anonymous Block + Conditional Expression

Syntax: if Condition1 then Execute-Statement1 elsif Condition2 then Execute-Statement2 else Execute-Statement3 end if;

declare
    emp_row employee%rowtype;
begin 
    select *
        into emp_row 
        from employee
        where emp_id = '&사번';
        if emp_row.job_code = 'J1'
        then dbms_output.put_line(emp_row.emp_name||'은 사장님입니다.');
        elsif emp_row.job_code = 'J2'
             dbms_output.put_line(emp_row.emp_name||'은 임원입니다.');
        else
         dbms_output.put_list(emp_row.emp_name||'은 평사원입니다.');
    end if;
END;
/

Anonymous Block(익명블럭)에 조건절을 대입하여 이상과 같은 판별문을 작성할 수 있다. 코드 작성시에 유의할 점은, else if에 해당하는 명령어가 elsif 이라는 것. 오타가 아니다. 진짜 elsif다. (Oracle 개발자가 졸다가 쳐버린 오타로 명령어를 만들어버린 걸까???) 또한 하나의 Anonymous Block 안에서도 semicolon을 찍는 경우와 찍지 않는 경우를 잘 구분해서 써야한다는 것. 대체적으로 변수의 "선언"후, "실행문"의 작성 후, 블럭의 끝을 알리는 위치인 END의 뒤에 ;을 찍어주면 된다.

Database Objects란? Oracle 내부적으로 Database를 효율적으로 관리하기 위해 지원하는 여러가지 개체를 의미한다. 주요하게 쓰이는 Database Objects로는 Table, View, package Specification, Database Trigger, Function, Synonym, Sequence, Procedure등이 존재한다. 또한 Data Dictionary 테이블인 all_objects를 통해 더 다양한 종류의 Database Objects에 대해 조회할 수 있다.

Syntax and Outline of Stored View & Join View

실제 테이블에 근거하여 원하는 데이터만 선택적으로 볼 수 있는 가상의 테이블. 이는 호출하여 다회 사용이 가능하다. 또한 Stored View는 참조형 타입으로, 데이터를 보여줄 뿐 실제 데이터를 가지고 있지 않다. 하지만 view는 실제 데이터를 참조하고 있기 때문에 실데이터가 변경되면 이는 view에서도 실시간으로 반영된다. view는 다음과 같은 코드로 생성이 가능하며, 이를 위해서는 관리자로부터 view 생성 권한을 받아야 한다. 또한 유저가 가진 view는 Data Dictionary의 user_views 테이블을 통해 조회가 가능하다.

--GRANT PRIVILIGE FOR CREATING VIEW TO ACCOUNT
grant create view to ACCOUNT-NAME;

--CREATE VIEW
create view example_view 
as
select Cols
from employee;

--CREATE OR REPLACE VIEW
create or replace view example_view
as
select emp_id EMP_ID_ALIAS
from employee;

--ADDING DATA TO VIEW
update example_view 
set employee = 'emp_name'
where dept_code = 'D1';

또한 view의 특징으로는 다음과 같은 것들이 있다:

1. 실제 Column뿐만 아니라 산술 연산 처리 등을 거친 가상 열의 생성이 가능하다.
2. join을 통한 join view로 보다 확장된 활용도 가능하다.
3. view를 통해 DML처리 또한 제한적으로 가능하다.
4. or replace 옵션을 통해 해당 view가 존재하지 않는다면 새로 생성, 그렇지 않은 경우 갱신이 가능하다.

2번 항목의 경우, join을 통해 생성판 Query를 바탕으로 다음의 예시와 같이 view를 생성할 수 있다:

create or replace view example_view
as
select emp_no, dept_title
from employee E join department D
on E.dept_code = D.dept_id
where job_code = 'J3'; --add'l Condition

3번 항목에서 제한적으로 가능한 DML 처리란, update문을 통해 view에 데이터를 저장할 수 있음을 의미한다. 이는 실제 테이블에 반영되나(commit 필요) insert into view values(Vals)문을 통한 값 대입시에 Virtual Column의 값 대입은 허용되지 않는다. (SQL Error: ORA-01733: virtual column not allowed here 발생) 또한 not null Column이 존재하며 default값이 존재하지 않는 경우 view에 insert into로 값대입은 불가능하다.

위와 같은 특징으로 인해 타 계정에게 view의 접근 권한을 부여한다는 것은, 대상 Table의 한정된 일부 데이터만 조회 가능한 권한을 부여하는 것과 같으며 이는 곧 보안성 향상으로 이어진다. 또한 복잡한 Query문을 하나의 view로 생성하여 일부 데이터를 손쉽게 별도로 수집한 후 조회도 가능하다.

Syntax and Outline of Sequence

중복되지 않는 채번 용도의 Database Object. 정수값을 순차적으로 발행한다. Syntax: create sequence SEQUENCE-NAME start with Num --Start Val increment by Num --Increment Val nomaxvalue (add'l) --Upper Limit nominvalue (add'l) --Lower Limit nocycle (add'l) --Cycle cache Num; --Caching unit

--CREATE SEQUENCE
create sequence example_sequence
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
cache 20;

--INSERT VALS INTO TABLE
insert into example_table values (seq_num.nextval, '1');

--LOOK UP THE SEQUENCES INFO. FROM D.D.
select *
from user_sequences;

--LOOK UP THE CURRENT-VAL/NEXT-VAL
select example_seq.nextval,
example_seq.currval
from dual;

--MODIFY THE EXISTING SEQUENCE 
alter sequence example_sequence
increment by Num
nomaxvalue 
nominvalue
nocycle
nocache;

Remark:

1. cache_size는 caching의 단위를 설정하는 것으로, sequence가 메모리상 번호를 미리 가지고 있다가 부여하는 것을 말한다.

2. last_number는 다음 번수에 부여할 번호이다.

3. sequence 수정 시에 start with값은 변경이 불가하므로 sequence를 삭제 후 재생성하여야 한다.

4. 반드시 "연속된 번호"를 얻어야 하는 경우에는 sequence 객체의 cache 기능을 이용하지 말 것. 서버가 재시작된다거나 하는 경우에는 caching된 번호가 유실될 수 있다. 따라서 이러한 경우에는 no cache 선언을 해 주는 것이 권장된다. 또는 수동으로 다음 번호를 얻어올 수도 있다.(select max(no) +1 from table_name;) 단, sequence를 활용하는 경우는 대체적으로 primary key로 사용하기 위함이다. 즉, 각 행을 구문하기 위하여 고유의 식별자로 중복되지 않는 번호를 받기 위함인 것이다. 따라서 caching으로 인하여 sequence를 통해 발급받은 번호가 연속되지 않는다고 하더라도 이 값들은 서로 중복되지 않으므로 primary key로 사용하는데에는 지장이 없다.

Syntax and Outline of Index

Introduction - Index: 색인. sql 명령문의 처리 속도 향상을 위해서 Column에 대해 생성하는 Oracle Object로 Key, Val의 한 쌍 형식을 차용하고 있다. Key에는 Column값이 대입되며, value 행에는 저장된 주소값이 대입되어 있다.

Index의 사용은 해당 Database에서 다룰 데이터가 많으면 많을수록 선택이 아닌 필수에 가까워진다. 검색 속도가 빨라지고, 시스템 부하를 줄이므로 전체적인 성능 향상이 가능해지기 때문. 단, Index는 참조의 형태를 취하고 있지 않기 때문에 별도의 물리적 공간이 필요하고, 이로 인한 index 생성 혹은 재생성에 별도의 시간이 소요된다. 따라서 데이터의 변경 작업 빈도수가 높다면 index로 인한 성능 저하가 가능하나, 약 20만건 이상의 데이터가 존재하는 경우 index를 사용하지 않고 Database를 조회하는 경우 검색 속도로 인한 Time Out이 발생할 수 있다.

Generate Index: 선택도가 좋은, 즉 중복이 적고 고유값을 많이 가지는 Column을 기준으로 index를 설정하는 것이 좋다.(e.g.주민번호) 때문에 Oracle 내부적으로 unique, primary key Constraint가 설정된 Column에 대해 사용자가 명시하지 않아도 자동으로 index가 생성된다. 이 때 자동으로 생성된 index의 이름은 Constraint의 이름으로 자동 배정된다.

Remark: Additional Options of Index

• where Condition절에 자주 사용되는 Column을 index로 설정
• join 조건 Column으로 자주 사용되는 Column을 index로 설정
• 한 번 입력된 데이터가 자주 변경되지 않는 경우라면 index로 설정
• 약 20만건 이상의 데이터가 존재하는 경우 상기한 방식의 index 설정은 선택 아닌 필수

index and cost where Condition문을 통한 자료 조회시에 where절에 커서를 두고 F10을 누르면 Explain plan, 즉 해당 자료 조회를 위해 oracle이 채택한 검색 방식과 그 결과의 확인이 가능하다. 여기에서 표시되는 options에 full(full scan)이라는 정보가 뜨면 이는 모든 데이터를 수동으로 검색하여 비효율적인 방식으로 결과를 도출했다는 의미이다. 그러나 index가 존재하는 Column을 검색하는 경우 options에서 full(full scan)이 아닌 index rowid, unique scan이라는 정보를 확인할 수 있다. 이는 해당 Column에 부여된 Constraint가 unique이기 때문에 자동으로 부여된 index를 통해 index rowid(각 row에 부여된 고유한 주소값)으로 접근하여 조회했다는 의미이다. 또한 options의 왼 편에 위치한 object_name을 살펴보면 해당 index의 명칭 또한 함께 확인할 수 있다. 참고로, where절의 Comparison Condition에서 동등비교, 즉 equal(=)과 like + WildCard(e.g.% , _)\중 선택하여 사용할 수 있는 경우라면 동등비교 equal을 사용하여 조회하는 것이 cost면에서 더욱 효율적이다.

단, 다음의 경우에는 index가 존재하더라도 사용되지 않는다:

1. index가 부여된 Column에 변형이 가해지는 경우. 즉 Virtual Column등의 경우 해당 가상 Column의 기반이 되는 Column이 index를 부여받은 경우라도 index를 통한 검색은 실행되지 않는다. (즉 Database 조회가 full scan으로 진행된다.)
2. null간의 비교시
3. not statements 비교시
4. 실제 DataType과 비교할 Literal이 다른 자료형인 경우. 자동 형변환이 진행되었더라도 입력한 값의 데이터 타입이 검색할 Column의 실제 데이터 타입과 다른 경우 index를 통한 검색이 이루어지지 않는다.
5. 그 외 비용기반 Optimizer(최저 비용으로 가장 빠르고 효율적인 처리 경로를 생성하는 DBMS 내부의 핵심 엔진, 즉 최적화도구)에 의해 index를 통하지 않는 선택인 경우

Syntax: create index INDEX_NAME on TAB(COL);

--CREATE INDEX
create index IDX_NAME on employee(emp_name);

--LOOK UP THE INDEX BY DATA DICTIONARY
select *
from user_indexes UI join user_ind_columns UIC
using(index_name)
where UI.table_name = 'EMPLOYEE';

Data Control Language, 즉 database 제어에 대한 privilige를 부여하거나(grant) 회수(revoke)에 관련된 언어. 또한 Transaction을 제어하는 TCL명령어(commit, rollback, savepoint)도 넓은 범위에서 여기에 포함된다.

Grant/Revoke Priviliges

Syntax for creating user/granting Priviliges: --Creating User create user ACCOUNT-NAME identified by USERPASSWORD; default tablespace to user/system

--grant privilige for connect: grant connect to ACCOUNT-NAME;

--grant resource-role to user grant resource to ACCOUNT-NAME; ...

--CREATE USER ACCOUNT
create user TestAcc identified by 1111;
default tablespace to user;

--UNLOCK THE ACCOUNT
alter user park account unlock;

--GRANT PRIVILEGE FOR CONNECT/RESOURCE ROLE
grant connect to TestAcc;
grant resource to TestAcc;

--GRANT PRIVILIGE FOR ACCESS TO TABLE
grant select on codepark.tbl_test to park;

--GRANT PRIVILIGE FOR CRUD TO ACCOUNT
grant insert, select, update, delete on employee from park;

--REVOKE THE PRIVILIGES FROM ACCOUNT
revoke insert, select, update, delete on employee from park;

가장 기초적인 Privilige(권한)을 부여하는 코드. 신규 계정 생성 단계에서 해당 계정에 일일히 새로운 권한을 부여하는 방법보다는, resource Role을 부여하는 방법이 널리 쓰인다. 여기서 role이란 privilige의 묶음이라 보면 되며, 개체 생성의 권한 역시 포함되어 있다. 해당 계정에 권한을 부여한 후 cmd(window+R)에 command를 입력하는 방식으로도 확인이 가능한데, 먼저 sqlplus -> conn USER-NAME/IDENTIFIED-Val의 입력으로 손쉬운 connect와 disconn USER-NAME/IDENTIFIED-Val을 통한 disconnect가 가능하고 이후 Oracle DB에서 가능한 모든 작업을 sqlplus에 입력할 수 있다. 단, sqlplus CMD에서 데이터를 추가한 경우 별도로 commit을 진행해야 해당 데이터가 DB서버에 반영된다.

Create new Role by System Account

resource Role과 같이 기존의 role을 Account에 부여하는 방법도 있지만, 사용자가 직접 privilige의 집합으로써 Role을 생성하고 계정에 부여하여 관리할 수 있다. 이러한 경우는 우선 관리자로부터 create role 권한을 부여받아 진행되며 그 과정은 다음과 같다:

1. grant create role to ACCOUNT-NAME;
2. create role ROLENAME;
3. grant select, insert, update, delete on TABLE to ROLENAME;
4. grant ROLENAME to ACCOUNT-NAME;

더불어 권한 회수는 다음과 같이 진행된다:

revoke ROLENAME from ACCOUNT-NAME;

데이터 정의어. CRUD 중 CUD에 해당하는 명령어인 Create(생성), Alter(수정), Drop(삭제)를 통해 Database Object를 정의한다. 자동 commit을 지원하므로 TCL(Transaction Control Language)이 필요하지 않다. 또한 데이터의 생성(insert)/수정(update)/삭제(delete), (즉 DML의 영역)와 Database Object(개체)의 생성(create)/수정(alter)/삭제(drop), (즉 DDL의 영역)는 엄연히 다르므로 용어를 혼동하지 않도록 한다.

Database Object:

table, user, view, sequence, index, synonym, procedure, trigger, function 등을 말하는 것이고, 여기서 procedure, trigger, function등은 PLSQL(Procedural Language for SQL) Syntax에 속하는 것이다. (Oracle 단독)

CREATE: Create Table/Column Comments

Syntax of adding the comment to Tab/Col: comment on table TabName is 'COMMENT ON HERE'; comment on column TabName.ColName is 'COMMENT ON HERE';

comment on table member is 'Employee-table';
comment on column member.emp_name is 'Employee name on here';

각 Table 또는 Column에 comment(Remark)를 작성한다. 이는 별도의 수정이 불가하고, 해당 Table 또는 Column에 comment를 새로 선언하게 되면 기존의 데이터를 덮어씌우게 된다. 따라서 Comment를 삭제하고자 한다면 ''에 '';로 값을 입력하지 않은 comment를 덮어씌워 (null)로 처리할 수 있다.

또한 작성한 주석은 Data Dictionary를 통해 다음과 같이 조회가 가능하다: Syntax of the Looking up the comments:

select *
from user_tab_comments UTC 
join user_col_comments UCC
using (table_name)
where table_name = 'TABLE_NAME'

CREATE: Introduction of Constraints

Constraint(제약조건)이란 데이터의 Intergrity(무결성)을 지키기 위해, 즉 부정한 데이터가 존재하지 않도록 방지하기 위해 입력 데이터를 제한하기 위한 조건을 의미한다. 데이터의 무결성이란 각 데이터가 정확하고, 부정한 데이터가 존재하지 않는 것을 말한다. 예를 들어 변경된 데이터를 제대로 반영하지 않는다거나, 일치하지 않는 데이터가 존재하거나, 필수 정보가 누락되는 등의 경우를 부정한 데이터라 일컫는다. 이 때, Constraint가 에러를 발생시켜서 부정한 데이터의 입력을 막는 역할을 수행한다.

Constraint의 종류로는 총 5가지가 존재한다:

1. not null : 데이터에 null을 허용하지 않는 것. 즉 필수 데이터를 설정한다.
2. unique(UQ): 중복된 값을 비허용 한다.
3. primary key(PK): 기본키, 주키, 식별키(Identifier)라고도 불린다. not null+unique의 기능, 즉 null 비허용 및 고유 데이터만 허용한다. Table당 단 하나의 Column에만 사용이 가능하다.
4. foreign key(FK): 외래키. RDBMS의 핵심. 두 테이블간의 관계를 정의하는 제약 조건. 자식 테이블의 Column은 부모 테이블의 Column값만 가져다 쓸 수 있다. 이 때의 부모-자식 상속관계란 하나의 테이블이 또 다른 테이블을 참조하고 있음을 의미한다. 여기서 참조 테이블이 자식, 피참조 테이블이 부모이다.
5. check(CK): 데이터의 범위, 조건등을 설정한다. (ex. gender)

마찬가지로 작성된 Constraint는 Data Dictionary의 Table을 통해 조회가 가능하다.

select *
from user_constraints

--OR
select *
from user_cons_columns

단, user_constraint의 경우는 ColumnName을 조회할 수 없으므로 통상적으로 저 두 Table을 join으로 병합하여 사용한다. 아래와 같은 코드를 사용하면 조회가 간편하다:

select  UC.table_name, UCC.column_name, constraint_name, 
        UC.constraint_type, UC.search_condtition
from user_constrinats UC join user_cons_columns UCC
using(constraint_name)
where UCC.table = 'TABLE_NAME'

일반적으로 Constraint를 선언할 때 constraint_name을 지정하여 주는데, not null constraint의 경우 별도로 constraint_name을 지정하지 않는 것이 보편적이다.

CREATE: Create Constraint: not null

Syntax of the creating Constraint - not null: create table TabName( Col DataType(Size-add'l) not null);

create table employee(
emp_name varchar2(15) not null
);

Column Level, 즉 Column 단위로만 지정이 가능하다. not null Constraint 설정 후 해당 Column에 null값을 대입하려 하면 ORA-01400: cannot insert NULL into () 에러를 발생시켜 필수 데이터의 누락을 방지한다.

CREATE: Create Constraint: unique

Syntax of the creating Constraint - unique --on Column Level create table TabName( Col DataType(Size-add'l) constraint ConstraintName unique); --on Table Level create table TabName( constraints ConstraintName unique);

create table tbl_const_test(
emp_id char(10) constraint UQ_email unique);

Column 입력값에 대해서 중복을 허용하지 않는다. unique Constraint를 생성한 후 중복된 값을 대입하려 하면 ORA-00001: unique constraint (Object) violated, 즉 unique 전제조건 위반이라는 에러가 발생한다. 또한 DBMS마다 unique 제약조건 Column의 null 허용 여부가 다르다. Oracle 및 mysql은 null 삽입이 가능하고, mssql은 불가능하다.

CREATE: Create Constraint: primary key

Syntax of the creating Constraint - primary key (Unique Identifier): create table TabName( Col DataType(Size-add'l) primary key); or create table TabName( Col DataType(Size-add'l), constraints ConstraintName primary key(Col));

--COLUMN LEVEL
create table tbl_alter(
num number primary key
);

--TABLE LEVEL
create table shop_nickname(
member_id varchar2(20),
constraints FK_NMEMBER_ID primary key(memebr_id)
);

--CREATE COMPOSITE-CONSTRAINT 
create table tbl_composite_pk(
product_code varchar2(50),
user_id varchar2(50),
order_date date default sysdate,
num number,
cconstraints PK_ORDER primary key
(product_code, user_id, order_date));

테이블마다 단 하나씩만 존재할 수 있는 Unique Identifier(고유 식별자)로 상술했듯 한 Table당 한 Column에만 부여가 가능하다. 여러 Column을 묶어서 Primary Key 설정도 가능하나(Composite-Primary Key Constraint), 사용된 Column은 그 어떤 것도 null일 수 없다. 단, 여러 Column을 선언하는 만큼 Column Level에 작성할 수 없다. 이렇게 여러 Columns를 묶어 Composite-PK 설정한 경우 선언한 모든 Column의 값을 하나의 그룹으로 묶어 판별하기 때문에 실제 데이터가 전부 동일해야 중복되는 것으로 본다.

CREATE: Create Constraint: foreign key

Syntax of the Creating Constraint - foreign key: create table TabChild( member_id DataType(Size-add'l), constraints ConstraintName foreign key(Col1) references TabParent(Col2));

create table shop_buy(
member_id varchar2(20),
    constraints FK_MEMBER_ID foreign key(member_id)
    references shop_member(member_id));

Reference Intergrity를 위한 제약조건. 참조된 Parent Table의 Column Data만 사용할 수 있도록 제한하며, null을 허용한다. 참조하고 있는 부모 테이블의 Column은 primary key 또는 unique 제약 조건이 걸려있어야 한다. 또한 foreign key는 Child Table의 Column에 지정하는 것이다. 또한 foreign key를 통해서 참조한 자식 테이블에 새로운 데이터를 추가할 때, 상속받은 부모 테이블의 unique/primary key 조건에 해당하지 않는 데이터가 포함되어 있을 경우 Error report - ORA-00001: unique constraint() violated 에러가 발생되며, 해당 데이터는 추가되지 않는다.

또한 이렇게 피참조-참조의 부모-자식 관계로 엮인 테이블간에서 부모 테이블의 데이터를 삭제하는 시도를 하면 ORA-02292: integrity constraint () violated - child record found 에러가 발생한다. 따라서 피참조-참조 상속 관계에서 참조 테이블인 자식 테이블에서의 데이터 삭제는 문제를 발생시키지 않으나, 피참조 대상인 부모 테이블의 데이터를 삭제하게 되면 Data intergrity가 깨지게 되므로 에러를 발생시키는 것이다. 개발 과정에서 해당 에러를 생각보다 자주 만나게 되므로, Foreign Key는 신중하게, 개발 후순 단계에서 사용해 주는 것이 좋다.

상기한 문제에 관해 부모 테이블의 데이터를 삭제할 때 자식 데이터를 어떻게 처리할 지에 대한 옵션이 존재한다. 이는 총 3가지로, 테이블 및 컬럼 생성 시점에서 설정할 수 있다:

1. on delete restricted (default) 기본값으로 설정되어 있으며, 상속관계가 존재하는 부모 테이블의 데이터를 삭제할 수 없게 한다.
2. on delete set null 자식 행은 유지하되, 해당 데이터 섹션은 (null)로 표시되게 된다. 즉, 데이터는 삭제되나 삭제여부를 확인할 수 있게 한다.
3. on delete cascade 상속 관계에서 부모 테이블의 데이터 삭제시 자식 테이블의 레코드도 함께 삭제된다. on delete set null과 다르게 해당 행 전체가 삭제된 것을 확인할 수 있다.

Identify Foreign Key Relationship

또한 부모-자식 테이블의 피참조-참조 관계를 식별/비식별 관계로 정의할 수도 있다:

1. 1 : n Relationship: 비식별 관계. 자식 테이블이 부모 테이블로부터 Foreign Key로 가져온 값을 비식별자(다시 말 해 primary key/unique가 아닌 경우)로 사용하고 있는 경우를 일컫는다.
2. 1 : 1 Relationship: 식별관계. 자식 테이블이 참조하고 있는 Foreign Key가 곧 부모 테이블의 Primary Key로, 이를 자식 테이블의 식별자(primary/unique)로 사용하고 있는 경우이다.

CREATE: Create Constraint: check

Syntax of Creating Constraint - check: create table TabName( Col DataType(Size-add'l), constraints ConstraintName check(Col in (Val1, Val2 ...)));

create table tbl_const_test(
gender char(1),
constraints CK_GENDER check(gender in ('F', 'M')));

해당 Column이 가질 수 있는 값을 특정 범위 내로 한정하여 선언한다. check Constraint를 선언하여 테이블을 생성한 이후에 해당 Column에 값을 대입할 때는 반드시 check(Col in (Val1, Val2 ...))에 상응하는 값만이 대입이 가능하고, 이에 해당하지 않는 경우 ORA-02290: check constraint () violated 에러를 발생시킨다.

UPDATE: alter Statement - add

Syntax of adding Column to Table with alter: alter table TabName add ColName DataType(SAize-add'l) Constraint; **Syntax of adding Constraint to Table with alter: alter table TabName add constraint ConstraintName ConstraintType;

--ADD NEW COLUMN TO TABLE
alter table tbl_alter add name;

--ADD NEW COLUMN WITH CONSTRAINT TO TABLE
alter table tbl_alter add constraint UQ_TBL_ALTER_ID unique(id);

Database Object(데이터베이스 개체)수정. Column/Constraint를 대상으로 추가가 가능하며, Column의 DataType, default Value, Constraint의 추가 및 rename, drop이 가능하다. alter를 통한 add시에 추가된 Column은 무조건 가장 마지막 순서의 위치로 생성되며, 이 순서는 변경할 수 없다. 또한 not null Constraint의 경우는 alter가 아닌 modify에서 진행된다.

UPDATE: alter Statement - modify

Syntax of the modifying nullability with alter: alter table TabName modify ColName not null Syntax of the modifying datatype with alter

--MODIFYING NULLABILITY WITH ALTER
alter table tbl_alter modify id not null;

--MODIFYING DATATYPE OF COLUMN WITH ALTER
alter table tbl_alter modify id varchar2(10);

not null Constraint와 Datatype은 alter-add가 아닌 alter-modify로 수정할 수 있다. 단 데이터타입/사이즈를 변경할 때 이미 존재하는 데이터와 상충하여 데이터 손실이 예상되는 경우는 에러를 발생시킨다. 또한 Constraint를 수정하고자 하는 경우에는 수정이 아닌 '덮어씌우기'형식을 사용하고 있으므로 modify가 아닌 add를 통해 수정해 주어야 한다.

UPDATE: alter Statement - rename

Syntax of renaming the Column with alter: alter table TabName rename column ColName to NewColName; Syntax of renaming the Constraint Name with alter: alter table TabName rename constraint ConstName to NewConstName;

--RENAMING COLUMN
alter table tbl_alter rename column pwd to password;

--RENAMING CONSTRAINT
alter table tbl_alter constraint SYS_C007260 to PK_NO;

Column명과 Constrain의 설정된 이름을 변경한다.

DELETE: alter Statement - drop

Syntax of dropping Column with alter: alter table TabName drop column ColName; Syntax of dropping Constraint with alter: alter table TabName drop constraint ConstName;

--DROPPING COLUMN
alter table tbl_alter drop column id;

--DROPPING CONSTRAINT
alter table tbl_alter drop constraint PK_NO;

Column과 Constraint를 삭제한다.

Introduction: Data Manipulation Language

Data Manipulation Language(DML), 즉 데이터 조작어. 즉 데이터를 Create, Read, Update, Delete(CRUD)하는 언어이다. DML에서 CRUD에 대응되는 명령어는 다음과 같다:

1. CREATE: insert
2. READ: select
3. UPDATE: update
4. DELETE: delete

Syntax: Create Table, Create Table with Subquery +Modifying default Vals

Syntax of the create Table without Constraints/Reference: create table TableName( ColumnName1 DataType(Size-add'l) default defaultValues(add'l) null/not null(add'l) ...);

create table tbl_dml_test(
column_a varchar2(10) not null,
column_b date default sysdate not null,
column_c number);

가장 기본이 되는 Table 생성 구조로, 각 Column명, 해당 Colum에 대입될 값의 Datatype과 그 크기, default값 부여, null값 허용 여부 설정의 순서로 선언한다. 또한 Table 생성 시에 기존의 Table내 데이터들과 Subquery를 활용할 수도 있으며, 그 문법은 다음과 같다: Syntax of the create Table with Subqeury: create table TableName( as select Cols from Table where Conditions);

create table emp_test1(
as
select * from employee);
--OR
create table emp_test2(
as 
select emp_name from employee);

해당 문법에서 as 이후에 작성된 코드들은 일종의 Subquery로, Parentheses 없이 as의 후위에 작성하여 기존 테이블의 데이터를 다른 테이블에 추가하여 생성할 수 있다. 단, Subquery를 사용하여 테이블을 생성하면 default 값 설정이 삭제되므로, 다음과 같은 별도의 수정 조치가 필요하다: Syntax of the modifying default Vals/Tab: alter table TabName modify Col default (defaultVal);

alter table emp_test1 modify quit_yn default 'N';
alter table emp_test2 modify hire_Date default sysdate;

CREATE: insert Statement

Syntax of the insert into(1): insert into TableName values (Col1Value Col2Value Col3Value ...); insert into TableName (Col1, Col2 ...) values (Col1Value, Col2Value ...)

--SYNTAX(1)
insert into tbl_dml_test values ('test', default, 100);
--SYNTAX(2)
insert into tbl_dml_test(col1, col2) values('test', default);

새로운 행을 추가하는 것. 문법은 두 가지로, 해당 Table에 존재하는 모든 Columns에 값을 대입하는 경우와, 특정 Column을 명시적으로 선언하여 해당 Columns에만 값을 대입하는 방법이 있다. 전자의 경우, 반드시 값 대입시에 선언한 모든 Columns에 해당하는 값을 선언하여야 하며, 모든 값을 선언하지 않는 경우 not enough values 에러가 발생한다. 후자의 경우 null을 허용하거나, default값이 정해진 Column의 경우는 값대입 생략이 가능하다. 단, 생략시엔 해당 ColumnName과 ColumnValue 둘 다 제외하고 선언하여야 한다.

UPDATE: update Statement

Syntax of the update: update TableName set ColName = ColValue where Condition(add'l)

update emp_test1
set email = 'codepark.kr@gmail.com'
where emp_id = '302'

Column의 갯수는 달라지지 않고, 각 행에 도입된 값만 바꾸는 명령어. where절의 조건문은 생략이 가능하지만, 조건문을 생략하고 update하는 경우 명시한 모든 테이블의 모든 행에 set한 데이터가 일괄적으로 반영된다. 상상만 해도 무섭다. 잘못하면 진짜 jot되고 시말서 쓴다. DML의 특성상 commit/rollback은 사용자가 반드시 명시해줘야 한다. 올바른 시점에서의 commit을 반드시 생활화하자!

또한 update문에도 다음과 같이 Subquery를 사용할 수 있다: Syntax of the update statement with Subquery: update Table1 set Col1 = (select Col1 from Tab2 where Condition1), Col2 = (select Col2 from Table3 where Condition2) where Condition;

update emp_test1
set job_code = 
(select job_code from job where job_name = '과장'),
dept_code = (select dept_id 
         from department 
         where dept_title = '해외영업3부')
where emp_name = '임시환'

DELETE: delete Statement

Syntax of the delete statement: delete from TabName where condition

delete from emp_test1
where emp_name = 'codePark';

마찬가지로 where+Condition으로 조건문을 선언하여 삭제를 원하는 Col.Val만 따로 걸러주지 않는다면 선언한 TabName의 모든 Col이 삭제된다! 반드시 where절의 조건문을 사용하여 삭제를 원하는 데이터를 특정하여야 하고, commit을 생활화하여야 한다.

Using truncate(DDL) STMT instead of delete(DML) STMT

Syntax of the truncate Statement: truncate table TabName;

truncate table emp_test1;

delete와 유사한 역할을 하는 것으로 truncate가 존재하나, 이는 DDL이기 때문에 자동으로 commit된다. DML은 사용시에 Before Image라는 것을 생성하고, rollback을 실행 시에 이 Before Image를 이용하기 때문에 이전 작업 내용의 복구가 가능하다. 하지만 DDL은 Before Image 생성 없이 실행되므로 처리 속도가 DML에 비해 상대적으로 빠르지만 복구가 불가능하다.

즉 truncate와 delete의 차이는: delete는 DML, truncate는 DDl이므로 rollback을 통한 복구 가능 여부이다. 또한 delete(삭제)는 Table 자체를 삭제하는 것이고 truncate(버림)는 Table에 존재하는 모든 데이터를 삭제하는 것이므로 이 두 절차 이후 select * from TabName을 하게 되면 전자는 Table 자체가 조회되지 않고, 후자의 경우 해당 Table과 ColumnName들은 그대로 조회가 가능하다. (단 Column 내의 값들은 전부 비어있는 상태이다.)

Remark: Using DDL and DML at same time

DML을 사용한 코드 작업 후 DDL을 사용한 코드 작업을 하게 되면 DDL에 의해 자동 commit이 이루어지므로 rollback을 하더라도 DDL 작업 이전의 DML 작업 내용으로 돌릴 수 없게 된다! 따라서 DDL을 통한 작업 이전에 반드시 DML로 작업한 변경 사항들에 문제가 없고, 추가로 변경할 사항이 없는지 확인한 후에 DDL 작업을 시작하는 것이 권장된다.

행과 행간의 관계를 정의하는 SQL 표준 함수.

1. Ranking Functions
2. Reporting Functions
3. Windowing Functions

Syntax and Difference between rank() and dense_rank()

Syntax of the rank(): select rank() over(order by Criterion asc/desc) Alias(add'l) Syntax of the dense_rank(): select dense_rank() over(order by Criterion asc/desc) Alias(add'l)

select emp_name, salary
rank() over(order by salary desc) RankAlias,
dense_rank() over(order by salary desc) DenseRankAlias
from employee;

rank(), dense_rank() 두 함수 모두 후위에 붙는 over()에 Criterion, 즉 판별 조건을 인자로 받는다. 이 자리에는 order by Clause가 들어갈 수 있으며, order by절에 의해 리턴된 결과의 순서에 맞게 Rank가 정해진다. 예를 들어 rank() over(oder by salary asc) RankAlias를 선언한다고 해 보자. 리턴된 결과는 월급을 적게 받는 사람들의 순위를 나타낼 것이다. asc를 선언하여 오름차순으로 정렬하는 것을 순위매김의 기준으로 삼았기 때문에. 반대로 dense_rank() over(order by salary desc)로 작성했다고 해 보자. 이 번에는 월급을 많이 받는 사람들의 순위대로 1, 2...와 같이 매겨지게 된다.

또한 rank()와 dense_rank()에는 다음과 같은 결과상의 차이가 존재한다: rank()는 판별시에 같은 판별값을 갖는 대상의 순위를 같은 것으로 판별하고, (즉 같은 값을 가진 대상에게 공동의 순위를 부여하고) 공동 인원 만큼의 순위를 지나쳐 다음 순위로 넘어간다. 그에 반해 dense_rank()는 같은 값을 가진 대상들에게 공동의 순위를 부여한다는 점은 같으나, 순위를 지나치지 않고 바로 그 차순을 부여한다. (주: dense는 밀집된, 빽빽한이라는 뜻이다. 참으로 직관적인 네이밍 센스가 아닌가 싶다!)

추가로 rank(), dense_rank() 자체적으로 그룹간 데이터 비교도 가능하다: Syntax of the rank() with partition by: rank() over(partition by Col order by Col asc/desc(add'l)) Alias(add'l) Syntax of the dense_rank() with partition by: dense_rank() over(partition by Col order by Col asc/desc(add'l)) Alias(add'l)

rank() over(partition by dept_code order by salary asc) R_PARTITION,
dense_rank() over(partition by dept_id order by salary desc) D_PARTITION 

상기한 Criterion의 경우와 마찬가지로 rank()/dense_rank()의 후위에 위치하는 over()내에 인자로 선언해 주면 되며, partition by Col을 먼저 선언한 후 order by Col asc/desc에 해당하는 부분을 선언한다. 질리도록 많이 보았을 ORA-00979: not a GROUP BY expression, 즉 group by의 한계로 인해 발생하는 에러를 가볍게 넘길 수 있어 아주 좋은 방법이다.

Syntax: sum/avg Function

Syntax of the sum Function: select sum(Col) over(partition by Col) Alias(add'l) select sum(Col) over(partition by Col order by Col asc/desc) Alias(add'l)

sum(salary) over(partition by dept_code)
sum(salary) over(partition by dept_code order by salary desc)

sum()의 인자로는 합계를 낼 Column을 선언하고, over()의 인자로는 rank(), dense_rank()와 같이 partition by Col, order by Col을 받는다. 이 때, over(partition Col) 까지만 선언하게 되면 해당 Col 단위로 합산한(sum) "결과"를 행마다 리턴하며 이는 중복될 수 있고(Col단위 합계), over(partition Col1 order by Col2)까지 선언하면 Col1의 합계를 Col2를 기준으로 정렬하여 리턴하므로 Col1단위 Col2순 누계를 출력하게 된다.

Syntax of the avg Function: select avg(Col) over(partition by Col) Alias(add'l) select avg(Col1) over(partition by Col1 order by Col2) Alias(add'l)

trunc(avg(salary)) over(partition by dept_code) 
trunc(avg(salary)) over(partition by dept_code order by salary)

sum() over()와 동일한 방식으로 작동한다. avg() over(partition by Col)을 선언하면 Col단위의 "평균값 산출 결과"를 중복하여 매 행마다 리턴하고, avg() over(partition by Col1 order by Col2)까지 선언하면 Col1단위의 "평균값 산출 내역"을 Col2 기준으로 asc(오름차순)/desc(내림차순)하여 정렬한 값을 리턴한다.

Introduction: Top-N Analysis - rowid, rownum

• rowid: 특정 Record에 접근하기 위한 논리적인 주소값
• rownum: 각 행에 대한 Serial Number. Oracle 내부적으로 insert한 순서대로 1부터 1씩 증가하여 부여된 것. where 조건절이나 Inline View를 통해 필터링하면 새로운 넘버를 부여받게 되고, order by를 통한 정렬 등으로는 변화하지 않는다. 즉, rownum은 Result Session의 index와 같다.

Syntax of the checking rowid/rownum: select rowud, rownum from Table

Syntax: Top-N Analysis

select Col from(select Col from Table order by asc/desc) where rownum Operator Condition;

--who takes the highest salary 1-5
select rownum, Subquery.*
from(
select emp_name, salary
from employee
order by salary desc) Subquery
where rownum <= 5;

기본적으로 부여된 rownum은 from/where절을 마치고 얻을 수 있다. 단, 순차적으로 처리할 수 있는 경우에만 같은 level의 where절을 사용할 수 있다.(ex. 몇 번의 rownum까지 표시하기 위해서) 따라서 순차적으로 접근하는 조건절을 사용하지 않는(쉽게 말해 6~10위를 구하라거나) 경우 Inline View로 한 번 더 감싸고, Main Query(Inline View의 바깥)에서 Alias로 접근하는 방식으로 취할 수 있다.

Syntax: Between N to N Analysis

select * from ( select rownum RowNumAlias, Col1, Col1Alias, Col2 Col2Alias ... from( select Col1 Col2 from Table) order by Col asc/desc) where RowNumAlias between Num and Num order by RowNumAlias asc/desc;

select *
from(
select rownum "RowNumAlias", SubQueryAlias.*
from(
select emp_name, salary
from employee
order by salary desc) SubQueryAlias
) SubqueryAlias
where RowNumAlias between 6 and 10;

위의 Top-N Analysis를 보충한 것. 상기했듯 1번부터 순차적으로 rownum에 접근하는 방식이 아닌 경우 하나의 Subquery만으로는 원하는 사잇값을 도출할 수 없다. (rownum은 from/where절을 마친 후에 생성되기 때문에 where절에서 조건문을 대입해봐야 rownum 생성 이전 시점이다.) 이러한 경우는 해당 코드를 한 번 더 Inline View로 감싸는 방법으로 개선할 수 있다. Inner-Inline View에서 모든 처리과정이 끝난 결과를 Outer-Inline View에서 받아 값을 도출하기 때문이다.

Inline View w.With As

with InlineViewAlias as (select Col ColAlias ... from Table TableAlias) select Col1, Col2, ColAlias from InlineViewAlias where ColAlias Condition/ComparisonOperators;

with emp_gender_Age
as
(select E.*,
    trunc((sysdate-todate(decode(substr(emp_no, 8, 1)
         , '1', 19, '2', 19, 20)
         ||substr(emp_no, 1, 6), 'yyyymmdd'))/365) Age
from employee E)
select emp_id, emp_name, Age
from emp_gender_Age
where Age between 30 and 49;

with InlineViewAlias as (Inline View)의 형식으로 Inline View Alias 재사용 또한 가능하다.

Main Query(Parent Query)안에 포함되어 있는 SQL문을 Subquery(= Nested Query)라고 하며, Subquery는 Main Query에 종속되어 있는 구조이다. 이때, Subquery가 Main Query보다 먼저 실행되고, 그 결과를 받아 main Query가 실행되어 Result Session을 산출한다.

Guidelines for Using Subqueries

1. Subquery는 Parentheses()로 묶어서 사용한다.
2. Comparison Condition의 오른편에 Subquery를 배치해야 한다.
3. 통상적인 경우 Subquery내 order by는 지원되지 않는다.
4. Single-Row Subqueries는 Single-Row Operator와 사용하고, Multiple-Row Subqueries는 Multiple-Row Operators와 사용해야 한다. (혼용시 에러 발생)

Types of Subqueries

1. Single-Column with Single-Row Subquery
2. Single-Column with Multiple-Row Subquery
3. Multiple-Column Subquery
4. Correlated Subquery
5. SCALAR Subquery
6. Inline View

Syntax: Single-Col. Single-Row Subquery

where Condition1 SingleRowOperator (Subquery)

where dept_no = (select dept_no
         from employee
              where dept_no = '200');

Subquery가 리턴한 결과가 1행, 1열의 값이며 단행 함수, 일반 컬럼등인 경우. 단행의 Subquery이므로 아래의 Single-Row Operator(단행 연산자)와 함께 사용해야 한다. (Multiple-Row Operator와 혼용시 에러발생)

• Single-Row Operator:

Syntax: Single-Col. Multiple-Row Subquery

where Condition1 Multi-RowOperator (Subquery)

where emp_no in (select emp_no
         from employee
             where emp_no = manager_id);

Subquery가 리턴한 결과가 1열, 여러 행의 값인 경우이다. 이 때, Subquery를 통해 리턴되는 결과값이 다중행이므로 조건문에 대입되는 비교연산자는 Multiple Rows Operator(다행연산자)이어야 한다.

• Multiple-Row Operator:

Syntax: Multiple-Cols. Subquery

where Condition1 Multi-RowOperator (Subquery)

where emp_no, emp_name in (select emp_no, emp_name
         from employee
             where emp_no = manager_id);

Subquery가 리턴한 결과가 다중 열의 값인 경우. 즉 리턴되는 값은 다중 행일 수 밖에 없으므로 다행 연산자를 비교 연산자로 대입하게 된다.

Syntax: Correlated Subquery (Single Row, Single Col)

from Table1 Table1Alias where Col1 Operator (select Col2 from Table where Col = Table1Alias.Col)

select emp_name
from employee E
where emp_id = (select manager_id
            from employee
            where E.emp_id = manager_id)

상호연관(Correlated) Subquery. Main Query의 값을 Subquery에 전달하여 실행하고 그 결과를 다시 Main Query로 리턴하여 처리한다.

Syntax: Correlated Subquery with exists Condition

from Table1 Table1Alias where exists (select Cols from Table where Condition);

select *
from employee E
where exists (select emp_name
            from employee
            where E.emp_id = manager_id);

exists Condition: 일종의 조건문. 나열된 대입값 중 단 한 행이라도 조건문에 일치하는 값이 있다면 참, 없다면 거짓을 리턴한다. 이 때, 조건문 검사 단계에서 단 한 건이라도 일치하는 건이 있다면 바로 true를 리턴하므로 이 후의 조건문들은 검사하지 않는다. 참이 리턴되면 해당 조건문은 true가 되어 Correlated Subquery의 select Clause에 작성된 모든 Column들이 출력되며, 거짓이 리턴되면 해당 조건문 자체가 false 처리되어 단 한 행도 출력되지 않는다.

또한 Subquery내의 Select절에 어떠한 Column을 선언해도 전부 같은 결과가 도출되므로 통상적으로 수 1을 입력하여 자리만 채워준다. 직접적으로 출력되는 Column은 Main Query의 select절에서 결정되며, 여기서 선언된 Subquery는 값을 필터링하는 조건문으로써의 역할만을 수행한다.

Syntax: Correlated Subquery with not exists Condition

from Table1 Table1Alias where not exists (select Cols from Table where Condition);

--GET MINIMUM VALUE
select * 
from employee E
where not exists(select 1 
         from employee 
         where E.salary > salary);

--GET MAXIMUM VALUE
select * 
from employee E
where not exists(select 1 
         from employee 
         where E.salary < salary);

• not exists: 조건문에 해당하는 모든 값을 제한 값을 리턴한다.

이러한 원리를 이용하여 Aggregate Function인 max와 min을 group by절 선언없이 추려낼 수도 있다. 위에서 최솟값을 구하는 코드의 진행 순서를 보면 다음과 같다:

1. Main Query employee Table의 salary Column의 값이
2. Subquery employee Table의 salary Column의 값보다 크다면
3. 해당하는 값을 전부 제외한다. (not exists)
4. 남은 값의 모든 정보를 출력한다. (Main Query - select *)

여기서 Subquery 조건문의 부등호만 바꿔주면 손쉽게 최댓값도 구할 수 있다. 아주 유용한 코드이니 잘 눈여겨 봐두자.

Syntax: SCALAR Subquery

select Col, (select Col from Table where Condition-With-MainTableAlias.Col)

select salary, (select trunc(avg(sal))
        from empoloyee
            where dept_cpde = 'D5')
from employee;

Correlated Subquery 중에 결과값이 단 하나, 즉 Single-Row, Single-Column이면서 통상적으로 select절에 선언된 경우를 의미한다. 결과로 도출된 Result Session은 Main Query에서 통합적으로 추가 조건문을 선언하여 필터링 후 정렬 또한 가능하다.

Introduction/Syntax: Inline View

• view란?: 실제 테이블의 데이터를 바탕으로 특정 목적을 가지고 가공해낸 가상의 테이블을 말한다. 복잡한 처리를 간결하게 하고 싶을 때 사용하면 좋다. 크게는 2가지, 1. Inline View(1회용의 view)와 2. Stored View(Database 객체, 호출하여 반복적으로 사용되는 view)가 존재하나 여기서는 Inline View만 다뤄보도록 한다.

select Col1 Col1Alias from (Subquery Includes-Col-Alias) SubqueryAlias where SubqueryAlias ComparisonOperator Condition(add'l)

select *
from
(select E.*, decode(substr(emp_no, 8, 1), '2', '여', '4', '여')) gender
from employee E)
where gender = '여';

Subquery내에서 선언한 각 Column의 Alias가 각각 새로운 Column명으로 명명되었으므로, Main Query에서 Subquery내의 Column을 조회하려면 그들의 Alias를 선언해야 조회된다. 단, Subquery에 선언된 모든 Alias(즉 새롭게 명명된 Subquery내의 Column명들) 전부를 선언하지는 않아도 된다. (즉 Subquery의 조회 내용 전부를 Main Query에서 조회하지 않아도 된다.)

Set Operator는 Union의 상위 개념으로, 집합 연산의 결과를 열(Column)과 열(Column)간의 결합으로 표현하여 가상 테이블을 생성하는 것을 말한다.

Precondition of Set Operator

1. 결합할 각 Table의 Column 수는 동일해야 한다.
2. 대응하는 Column의 자료형간 상호 호환이 가능하여야 한다. (ex.char-varchar2)
3. order by Clause는 마지막 결과 집합에 단 한번만 대입이 가능하다.

Types of Union

1. union: 합집합. 중복된 데이터는 제거하고 첫번째 Column을 기준으로 오름차순 정렬한다.
2. union all: 합집합. 단, 중복과 정렬에 무관하게 모든 데이터를 리턴한다.
3. intersect: 교집합의 값을 리턴한다.
4. minus: 차집합. 먼저 선언된 쿼리의 모든 값에서 이후에 선언된 쿼리의 산출값을 제한다.

Syntax: union/union all (Union Set)

Syntax of the union: Query1 union Query2 union(add'l) Query3(add'l) ...

select emp_name, dept_code
from employee
union
select emp_id, emp_no
from employee

Syntax of the union all: Query1 union all Query2 union all(add'l) Query3 ...

select phone, email
from employee
union all
select job_code, dept_code
from employee

각 데이터의 대응/미대응 여부 및 쿼리의 선언 순서와 무관하게 같은 Result Set(결과 집합)을 리턴한다. (합집합의 결과를 리턴할 것이므로) 또한, 모든 구성 쿼리가 전제조건을 거스르지만 않는다면 갯수 제한 없이 여러개의 쿼리를 선언하고 결합할 수 있다.

Syntax: Intersect (Intersection Set)

Syntax of the intersect: Query1 intersect Query2 intersect(add'l) Query3(add'l)

select emp_name, dept_code
from employee
intersect
select emp_id, emp_name
from employee

선언된 쿼리간 대응 데이터만을 리턴한다. (교집합) 따라서 각 쿼리의 선언 순서와 관계 없이 같은 결과 집합을 리턴한다.

Syntax: Minus (Relative Complement, Set Difference)

Syntax of the minus: Query1 minus Query2 minus(add'l) Query3(add'l)

select emp_name, dept_code
from employee
minus
select emp_id, emp_name
from employee

먼저 선언된 쿼리의 "모든" 데이터에서 후위에 선언된 쿼리의 "모든" 데이터를 제한다. (차집합) 상기한 union/union all/intersect와 다르게 각 쿼리의 선언 순서가 매우 중요하며, 쿼리의 선언 순서가 달라지게 되면 도출될 결과 집합 또한 달라지게 된다.

Remark: Add Condition with Set Operator

Set Operator + Parentheses로 묶인 하나의 가상테이블에 공통의 조건문을 대입하거나 정렬을 적용하려는 경우에는 다음과 같은 문법으로 작성할 수 있다: select Col1, Col2 ... (add'l) from( Query1 union/union all/intersect/minus Query2 union/union all/intersect/minus(add'l) Query3(add'l) ... order by asc/desc(add'l) ) where Conditions group by Cols having Conditions(add'l) order by (add'l)

Remark: Add Conditions with diff.Cols

또한, Set Operator+Parentheses로 연합한 가상 테이블에 공통의 조건절을 대입할 때 각 Query의 컬럼명이 다른 경우 첫 번째 Query의 컬럼명을 전제로 조건문을 선언해야 정상적인 조건 대입 및 조회가 가능하다.