오라클 동작
su oracle // 오라클로 사용자 전환
sqlplus /nolog // 계정 없이 접속
sqlplus scott/tiger // sqlplus 접속
conn /as sysdba // 관리자 모드 접속
startup // DB엔진 가동
conn scott/tiger // scott계정으로 접속
끄 기
conn /as sysdba;
shutdown;
<계정삭제>
drop user scott; //scott 계정 삭제
drop user scott cascade; //계정내 스키마 존제시 cascade 넣어야 함
<계정 생성과 세팅>
Create user scott identified by tiger; //scott계정을 tiger암호로 만듬
alter user scott default tablespace system; // 생략가능
Grant connect, resource to scott; //권한부여
conn scott/tiger //scott로 재접속
alter session set Nls_language='american'; //현재세션 언어변경
alter session set Nls_territory='america'; //현재세션 문화권변경
@c:\summit2
@c:\scott
<포토 번호 확인/변경>
1) 현재 포트 번호 확인
SQL> select dbms_xdb.getHttpPort() from dual; // 확인
SQL> conn /as sysdba // 계정변경
SQL> exec dbms_xdb.sethttpport(포트번호) // 변경
<JDBC 셋팅>
C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar 파일복사
C:\Program Files\Java\jdk1.6.0_11\jre\lib\ext 폴드내 붙여넣기
<SELECT>
*(모든칼럼표시)
산술표현식(+,-,*,/)
별칭(Alias):~ as " "사용
DISTINCT(중복값제거):Select 바로 뒤에 사용
리터럴문자:Select의 선택목록에 포함된 문자, 표현식, 숫자(컬럼 별칭제외)
날짜 및 문자값은 ' '로 에워싸고 리턴되는 행당 하나씩 출력
합성연산자(||):칼럼과 칼럼, 또는 칼럼과 다름값을 연결함
합성연산의 결과로 새로운 문자값을 생성한다.
NULL:값이 정해지지 않은, 알려지지 않은값 NULL과 산술표현식은 NULL이 됨.
NVL:NULL을 다른값으로 대치함
예)NVL(expr1, expr2) expr1과 2는 데이터 타입이 같아야함
<WHERE>
비교연산자: -,=,>,같지 않음(<>,!=)
논리연산자:AND, OR, NOT
SQL비교 연산자
Between ~ And ~ : ~와 ~사이의 값
예)Start_date Between '91/05/05' And '98/05/01'
IN(~,~,~) : 괄로 사이에 모든값
예)region_id IN(1,54)
Like(비슷한):%(0개 이상의 문자), _(한개의 문자) ESCAPE(옵션)
IS NULL(이것이 널이면):칼럼,수식의 널여부 검사
<ORDER BY>
Select 문장의 맨 마지막에 위치한다.
ASC:올림차순(디폴트) DESC(내림차순)
NULL의 위치 올림시 가장 마지막, 내림시 처음
<단일 행 함수>
-문자함수-
Lower(칼럼또는스트링):알파벳 문자를 소문자로 바꾼다.
Upper(칼럼또는스트링):알파벳 문자를 대문자로 바꾼다.
InitCap(칼럼또는스트링): 첫글자만 대문자로 하고 나머진 소문자
예) IntiCap('oracle dbms')=>Oracle Dbms
Concat:두개의 문자열을 합친다(2개만 된다.)
Substr(칼럼또는스트링, m,n):m 의 위치에서 n 길이만큼 문자열을 잘라낸다.
예) substr('Oracle DBMS',2,4)=>racl
Length(칼럼또는스트링):문자열의 길이를 리턴.
Instr(칼럼또는스르링,문자):문자열내에서 문자의 위치 값을 리턴
예) instr('Oracle DBMS','a')=>3
-숫자함수-
Round(칼럼또는 숫자, n):숫자값을 소수점n의 위치까지 반올림 한다.
예)Round(45.985,2)=>45.93
Trunc(칼럼또는 숫자,n):숫자값을 소수점n의 위치 아래까지 버림.
Floor(칼럼또는 숫자):소숫점 이하 버림
Ceil(칼럼또는 숫자):소숫점 이하 올림
Mod(칼럼숫자,칼럼숫자):나머지함수 예) mod(43,2) =>1
-날짜 산술연산-
Add_Months('02/01/01',2)=>02/03/01:날짜값에 n개월 뒤의 날짜를 리턴
Months_Between('02/07/01','02/01/01')=>6:두 날짜값사이에 개월수 리턴
Next_Day('02/07/01','금요일')=>02/07/05 : 날짜뒤에 첫 해당요일 날짜값
Last_Day('02/07/01')=>02/07/31 :날짜값이 속한 마지막 날짜 리턴
Round('02/07/01','year')=>03/01/01 :형식 기준으로 반올림된 날짜리턴
Trunc('02/08/01','year')=>02/01/01 :형식을 기준으로 그 이하 버림
#Round, Trunc는 날짜값으로 인식하기 위해서는 To_Day() 함수 사용
예)Select Round(To_Date('02/07/15'),'year')From dual;
-변환 함수-
To_char(날짜값,형식):주어진 형식으로 날짜 값을 문자로 변환
-' ' 로 에워싼다.
-대소문자 구별한다.
-유효한 날짜 형식이 정해져 있다.
-결과의 일, 월에는 자동적으로 공백을 붙인다.
-임의의 스트링도 추가 가능
예)Select To_Char(sysdate,'hh24'"시"mi"분"ss"초"') From dual;
예)Select last_name, To_char(start_date,'fmDdspth "of" Month
yyyy fmHH:MI:SS AM') Hiredate
From s_emp Where start_date Like '91%';
결과 Seventh of 2월 1991 08:00:00 오전
To_char(숫자, 형식):주어진 형식으로 숫자를 문자로 변환
형식 설명 예
9 숫자 출력 길이 999999=>12345
0 숫자 앞에 0를 넣음 099999=>012345
. 소숫점 자리표시 99999.99=>12345.00
, 천단위표시 99,999=>12,345
L 국가별 화폐단위 L99999=>\12345
To_Number('$123,456','$999,999') =>123456 문자를 숫자로 변환
To_Date('99/09/09') //To_Date(1966,2,5','yyyy,mm,dd') :문자를 날짜로 변환
Chr(66) =>숫자에 해당하는 ASCII 코드의 문자를 리턴
Select t3.id ,t3.name
,t1.quantity ,t2.date_shipped
,t6.address ,t7.title ,t11.name
,t7.dept_id ,t8.last_name
,t9.filename ,t4.amount_in_stock
,t8.Last_name ,t10.name
From s_item t1 ,s_ord t2
,s_product t3 ,s_inventory t4
,s_warehouse t5 ,s_customer t6
,s_emp t7 ,s_emp t8
,s_image t9 ,s_region t10
,s_dept t11
Where t1.product_id=t3.id And t1.ord_id=t2.id
And t2.customer_id=t6.id And t6.sales_rep_id=t7.id
And t7.dept_id=t11.id And t7.manager_id=t8.id(+)
And t3.image_id=t9.id(+) And t3.id=t4.product_id
And t4.warehouse_id=t5.id And t5.manager_id=t7.id
And t5.region_id=t10.id And t10.id=t11.region_id
And t2.payment_type='CASH' And t2.date_ordered like '91/09&'
/
92년 9월에 판매된 제품들의 각 제품별 총판매수량을 출력
(제품id , 총판매 수량)
Select T1.product_id ,sum(t1.quantity)
From s_item t1 ,s_ord t2
Where t1.ord_id=t2.id
And T2.date_ordered like '92/09%'
group by t1.product_id
/
92년 9월에 판매된 제품들의 각 제품별 총판매수량을 출력
(제품id , 총판매 수량)
평균판매수량이 3개이상인 레코드인것만 판매수량이 작은것 부터
Select T1.product_id ,sum(t1.quantity)
From s_item t1 ,s_ord t2
Where t1.ord_id=t2.id
And T2.date_ordered like '92/09%'
group by t1.product_id
Having avg(t1.quantity)>=3
order by sum(t1.quantity)
/
<고급 SELECT >
-Simple JOIN-
-Non Equi JOIN-
=,<=,<>, Between ..And.. 연산등 사용.
-Outer JOIN-
NUll이 필요한곳에(+)를 붙여 사용한다.
-Self JOIN-
한개의 테이블 2번 읽어야 할때사용.
문제)
1999년 8월달에 ID가 401 인 창고에서 판매된, 카드(CREDIT)로 결제된 상품의 상품ID, 상품명, 상품수량, 상품가격, 상품이미지파일명(FILENAME), 주문자 이름, 주문날짜, 결제타입, 영업사원명, 영업사원부서명, 영업사원관리자명,
창고관리자ID, 창고관리자명, 상품최대재고수량(MAX_IN_STOCK), 창고전화번호(PHONE), 창고지역명(S_REGION테이블의 NAME)
위의 내용을 출력하시오. 이때 상품이미지 파일이 없는 상품정보도 조회될 수 있도록 처리하시오.
/
Select t1.product_id "상품ID" ,t3.name "상품이름"
,t2.quantity "수량" ,t2.price "가격"
,t7.filename "파일명" ,t4.name "고객명"
,t2.date_ordered "주문날짜" ,t2.payment_type "결재방법"
,t5.last_name "영업사원명" ,t10.name "부서명"
,t11.last_name "영업사원메니저" ,t12.id "창고관리자ID"
,t12.last_name "창고관리자" ,t8.max_in_stock "최대재고량"
,t9.phone "전화번호" ,t13.name "창고지역"
From s_item t1 ,s_ord t2
,s_product t3 ,s_customer t4
,s_emp t5 ,s_image t7
,s_dept 10 ,s_emp t11
,s_emp t12 ,s_inventory t8
,s_warehouse t9 ,s_region t13
Where t1.product_id=t3.id and t3.image_id=t7.id(+)
and t1.ord_id=t2.id and t2.customer_id=t4.id
and t4.sales_rep_id=t5.id and t5.dept_id=t10.id
and t5.id=t11.manager_id and t3.id=t8.product_id
and t8.warehouse_id=t9.id and t9.region_id=t13.id
and t2.payment_type='CREDIT'
and t2.date_ordered like '99/08%'
and t9.id=401
/ 이거 오답
-Group 함수-
Distinct 중복제거
ALL 중복된 값도 모두대상
Expr NULL인 것을 뺀 나머지를 대상
* 모든 행을 대상
Count(Distinct|ALL|expr|*): 갯수
Sum(Distinct|ALL): 합계
Avg(Distinct|ALL|expr): 평균
Max(Distinct|ALL|expr): 최대값
MIn(Distinct|ALL|expr): 최소값
Select Sum(Distinct salary) From s_emp; /중복된 값을 제거하고 합계
Select Sum(comm_pct)/count(*) From s_emp;
Group By절
테이블의 행들을 원하는 그룹으로 나눈다.
Having절
그룹을 제한 하기 위해서 사용
그룹함수 퀴리 수행과정
1. 행들이 그룹핑 된다.
2. 그룹함수가 그룹에 적용됨.
3. Having조건을 만족하는 그룹들이 리턴됨.
Select title, Avg(salary) From s_emp
Group By title Having Count(*)>3;
-Subquery-
메인 퀴리 이전에 실행되어 결과가 메인 아웃퀴리에 의해 사용된다.
단일행 Subquery는 (=,>,<)등 사용 :결과값이 하나일때
복수행 Subquery는 IN (...) 사용 :결과값이 하나 이상일때
-집합 연산자-
RDB의 모든연산은 집합연산이다.
-UNION, UNION ALL:합집합(ALL은 중복포함
-INTERSECT:교집합
-MINUS:차잡합
-계층 구조 질의-
<SQL*Plus 명령어>
-Col[{Column|alias}[옵션]]
-Cle[ar] :column 형식을 지움
-For[mat] :column 표시 형식을 변경
Col last_name HEADING 'Emplotee|Name' For A15 //Last_name 칼럼의 해딩이 변경됨
<테이블 생성>
데이터 정의어
DDL(Date Definltion Language)
Creat, Drop
데이터 조작어
DCL(Date Control Language)
Grant, Revoke
DML(Date Multipulation Language)
Insert, Delecte, Update, Select
Insert Into Table [Column,Column...] Values(값, 값...);
한번에 한행만 삽입, 문자열은(' ')로 둘러쌈
UpDate{Table|SuQuery} Set Column=값[,Column=값} [Where 조건];
UpDate s_emp Set dept_id=10 Where id=2;
s_emp 테이블에 dept_id 부서가 10인것을 2로 변경하라.
Delete From Table [Where 조건];
Delete From s_emp Where start_date> TO_DATE('01,01,1996','DD,MM,YYYY');
-무결성 제약조건에 위배되는 경우 에러 발생
데이터 타입
Char(size):최대길이 2000 size 만큼 공간을 먹음
Varchar2(size): 4000 입력한 만큼만 먹음
Number(p,s):38자리까지 유효 s는 소숫점 자릿수 디폴트는 p가 38
Date: 날짜 기원전후 4214년
제약조건
-NOt Null
칼럼이 NULL값을 가질수 없음 Column레벨에만 지정
-Unque
테이블의 모든 행에서 고유값을 가져야 하는 칼럼이나 칼럼의 조합 명시
-Prmary key
테이블의 각행을 유일하게 식별 할 수 있는 칼럼이나 칼럼의 조합 명시
-Foreign key
다른 테이블의 Primary key나 Unque 칼럼을 참조하는 칼럼을 명시
-Check
모든행에 대하 반드시 참이여야 하는 조건을 명시
create table bbs( //테이블생성
no number(4) primary key
,title varchar2(40) default '없음'
,writer varchar2(50) not null //널을 넣으면(값을 입력치 않으면)에러
,pwd varchar2(10)
,ssn char(13) unique //중복방지
,content varchar2(1000)
,hitcnt number(4)
,regDt date default sysdate
,constraint bbs_no_pk primary key(no)
);
insert into bbs //입력시작
values (1,'test1','hong','1111','testtete','0','sysdate');
insert into bbs (no,writer,pwd,content,hicnt) //입력 순서 변경
values (2.'kim','2222','tester','0');
commit; //커밋 완료
create table bbs (
no number(4)
,title varchar2(10) default '없음'
,writer varchar2(10) constraint bbs_writer_nn not null
,ssn char(13) not null
,age number check in(age =>0, age=<150)
,pwd varchar2(10)
,city varchar2(10) constraint bbs_city_ck check (city in ('jinju','masan','chang'))
,constraint bbs_no_pk primary key(no)
,constraint bbs_ssn_nn unique(ssn)
);
create table t_emp (
id varchar2(10) primary key
,deptno number(2)
,constraint t_emp_deptno_fk foreign key (deptno) references dept(deptno)
);
subquery(서브쿼리) 에 테이블생성
create table t_dept1 as Select deptno from dept;
<테이블 삭제>
drop table product cascade constraints;
** 트랜잭션 **
논리적으로 한번에 수행되어야 하는 작업의 묶음
데이터 무결성 보장
1) 원자성(Atomicity):일부만 실행되어선 안된다.All or Nothing
2) 일관성(Consistecy):완료시 모순되지 않고 일관된 상태가 된다.
3) 격리성(Isolation):실행중에 연산결과는 다른섹션에서 접근하지 몬한다.
4) 영속성(Durability):완료시 그 결과는 영속적(영구적)이다.
시작
최초의 실행가능한 SQL명령이 실행되면서
종료
-Commit 나 RollBack 명령문 실행
-DDL, DCL 명령실행(Automatic Commit)
-Error, 종료 또는 system crash
제어
Explicit(명시적) 처리
-Commit
-SavePoint savepointname
-RollBack to savepoitname :세이브포인트 까지 롤빽
Implicit(절대적) 처리
-Create,Drop DDL
-Grant,Revoke DCL 명령실행하거나, 정상적종료시
-자동RollBack 는 비정상적종료(전원차단, 시스템실패등.)
<오라클 주요 객체>
<Table, View, Sequence, Index, Synonym>
1. Sequence(시퀸스:연속,순차적)
SQL.pdf
고유한(unique)한 숫자를 자동적으로 생성
일반적으로 primary key로 사용
메모리 적재 되어 효율성 증가
공유가 가능
1) 생 성
-Increment by N 생성되는 Sequence번호의 간격을 정수 N으로 정의, 생략시 1씩증가
-.Start With N 첫번째 Sequence번호 정의, 생략시 1부터 시작
-.MaxValue N 생성가능한 Sequence의 최대값 정의,디폴트는 NoMaxValue 최대값은 10의27승
-.MinValue N 생성가는한 Sequence의 최소값 정의,디폴트는 NoMinValue 최소값은 1
-.Cache N 캐쉬에 미리 Sequence를 생성해 놓은 개수. 디폴트 20 NoCache 가능
Create Sequence s_dept_id //s_dept_id 라는 이름의 시퀀시 생성
Increment by 1 // 1식 증가한다.
Start with 51 // 51에서 시작
Maxvalue 10000 // 최대값은 10000
Nocache // 캐쉬와
Nocycle; // 사이클은 사용치 않음
2)객체 확인
User_Sequences, User_Objects 라는 Data Dictionary 를 통해 확인가능.
Select object_name From user_objects Where object_type='SEQUENCE';
Select sequence_name, min_value, max_value, increment_by, last_number From user_sequences;
3) 사 용
순차적 번호를 생성할때 사용
Nextval과 Currval 이라는 의사(Pseudo) 칼럼을 사용하여 Sequenec 값을 참조한다.
-Nextval : 사용가능한 다음 시퀀스 값을 리턴한다.
-Currval : 현재 시퀀스 값을 리턴 한다.
-Currval은 Nextval을 최소 한번 사용후에 사용가능함.
Inset Into s_dept(id, name, region_id) VALUES(s_dept_id. NEXTVAL, 'Finance', 2);
Select s_dept_id.CURRVAL From dual;
4) 변 경
ALTER SEQUENCE 명령을 사용변경한다.
생성되지 않은 값들만 영향을 받는다.
Maxvalue은 현제값보다 많아야 하고, Start with는 변경이 안된다.
Alter Sequence s_dept_id
Increment by 3 //51부터 1식 증가한다.
Maxvalue 20000 //최대값은 20000이고
cache 20 //캐쉬 사용
5) 삭 제
Drop Sequence s_dept_id;
2. View
테이블이나 다른 뷰를 기초로 한 논리적인 테이블
자체의 데이터는 없지만 테이블 데이터를 보거나 변경할 수 있는 창
Select 문장을 정의한 것
옵션 Create 다음에 위치
-Or Replace 있으면 덮어써고, 없음 새로만듬(재정의)
-Force 무조건 뷰를 생성함
-With Check Option (이름을 명시하지 않으면 Sys_cn형태의 이름을 자동 지정함)
뷰에 의해 액세스 될수 있는 행만이 입력되거나 변경될 수 있도록 한다.
-With Read Only 읽기전용 뷰에서 DML이 수행될수 없게 한다.
1) 생 성
Create or replace [Force|NoForce] View ViewName [(alias[,alias]...)]
AS Subquery [With Check Option [Constraint 제약조건]] [With Read Only];
Create Or Replace View empvu45 //empv45이름으로 뷰를 생성
(id_number, empoyee, job) //별칭 id as "id_number"
AS Select id, last_name, title // id, last_name, title 대해서
From s_emp // s_emp 테이블에
Where dept_id=45; // dept_id 가 45번인
With Check Option Constraint empvu45_ck; //empvu45_ck 이름으로 체크 옵션 삽입
//With Read Only; // 읽기 전용옵션.
2) 뷰에 대한 DML
Simple 뷰에서는 DML연산이 가능하다.
뷰가 다음 사항을 포함하는 경우 행을 삭재할수 없음.
= 조인 조건 / 그룹 함수 / Group by절 / Distinct 명령 =
뷰가 다음 사항을 포함한는 경우 테이터를 수정할 수 없음.
= 위 조건 / 함수 칼럼 =
뷰가 다음 사항을 포함하는 경우 데이터를 추가 할수 없음
= 위 조건 / 뷰로 선택되지 않는 Not Null Column =
3) 뷰 객체 확인
-User_views : 뷰의 이름과 정의 내용을 확인한다.
Select View_name,text
From user_view;
-User_Objects : 뷰의 생성날짜와 상태를 확인
Select object_name, created, status
From user_objects Where object_type='VIEW';
3) 삭 제
Drop View ViewName;
3. Index
테이블의 값을 빠르게 액세스 하기 위해 만드는 DB객체이다
1) 생 성
-자동생성:PK나 UN제약조건을 정의하면 unique인덱스 자동생성
-사용자 생성:속도를 늘리기 위해 unique Or non_unique 인덱스 생성
Create Unque Index s_emp_i_id_idx On s_emp_i(last_name); //에러(last_name)가 중복됨
<사용자와 권한 관리>
Create User Identified By pw // 새로운 사용자 생성
Alter User Identified By pw // 암호 변경
필요 권한부여
-Create Session DB접속
-Create Table 생성
-Cerate Sequence
-Create View
-Create Procedure
1) 롤(Role)
Create Role manager; // 롤 생성
Grant create table, create view To manager; //롤에 권한을 부여
Grant manager To Cleak, Blake; //롤에 대한 권한을 부여
2) 객체 권한
DBA는 사용자가 객체에 대한 작업을 수행할 수 있게 한다.
소유자는 모든 객체권한을 가지면 줄수도 있다.
Grant 객체권한[, 객체권한...] On 객체 To {사용자[ ,사용자...] | Role|Public} [With Grant Option];
Grant Select // Query 할수 있도록
On s_emp // s_emp테이블에 대해
To sue,rich; // 슈와 리치에게
3)권한 회수
Revoke {권한[, 권한...]|All} On 객체
From {사용자[, 사용자...]|Role|Public}
-With Grant Option 은 다른사용자에게 허가된 권한도 취소.
4) Synonym
객채에 대한 동의어
Create [Public] synonym 동의어 For 객체;
Drop [Public] Synonym 동의어;
< PL/SQL >
-DECLARE -OPTION
변수, 상수, Cursor
user -defined exception
begin -Mandatory
-SQL 문장
PL/SQL 제어문
-Exception -Option
Error 발생시 실행
-End; -Mandatory
<JDBC>
DBMS에 맞는 드라이버 준비
드라이버는 클래스파일 형태로 제공되며 JAR 파일로 제공(classes12.jar)
1. jdbc 드라이버 로딩
2. DB커넥션 구하기
3. Statement 객체 생성
4. 쿼리 실행
5. 쿼리 실행 결과 사용
6. Statement 종료
7. DB 커넥션 종료
public class DBConnection {
public static Connection getInstance() throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String id = "scott"; String pass = "tiger";
Connection conn = DriverManager.getConnection(url,id,pass);
return conn;
}
}
public class DBConnection2 {
public static Connection getConnection() throws SQLException, NamingException, ClassNotFoundException{
Context initCtx = new InitialContext(); // Context 객체를 받아온다.
Context envCtx = (Context) initCtx.lookup("java:comp/env"); // 객체를 힙에 올린다.(전화번호부)
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB"); // 멥핑될걸 올린다.(전화번호)
// web.xml 에 맵핑
Connection conn = ds.getConnection();
return conn;
}
}
외부 DB 접속
oracexe\app\oracle\porduct\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
host/port/service_name 확인
CONN scott/tiger@xxx 2.9일 동강 10분
