Transcript Oracle 데이터베이스 실습

Oracle Database 실습
using Oracle 10g express
Database & System Integration Laboratory
1
목차
 Oracle 10g Express 설치
 SQL 문
 CREATE TABLE
 ORACLE 데이터타입
 DESCRIBE
 ALTER TABLE
 DROP TABLE
 INSERT INTO
 DELETE FROM
 UPDATE SET
 SELECT
 PL/SQL기초
 Cursor
2
Oracle 10g Express 설치
Part: 1
3
Oracle 10g Express 설치 (1)
다운로드주소:
 http://www.oracle.com/technology/software/products/database/xe/htdoc
s/102xewinsoft.html
1. Accept를 선택합니다.
2. 다국어인터페이스지원을
위하여 OracleXEUniv.exe
를 클릭합니다.
(바로 위의 OracleXE.exe는
유럽어만 지원됩니다.)
4
Oracle 10g Express 설치 (2)
보안경고에서 Yes를
선택합니다.
사용자 아이디와 패스워드를
입력한다. 만약 없는 경우 sign
up now를 클릭하여 새 계정을
등록합니다.
혹은 기존에 등록해 놓은 계정
을 사용할 수 있습니다.
Username: [email protected]
Password: emailme
5
Oracle 10g Express 설치 (3)
다운로드 완료 후 다운로드 한 폴더에서 OracleXEUniv 아이콘을
더블 클릭하여 설치를 시작합니다.
암호 입력 창이 나타나면 Oracle
관리에 필요한 SYS및 SYSTEM
계정 암호를 자체로 결정하여
입력합니다.
※ 암호설정에서 영어대소문자, 숫
자 외의 특수문자 @, #등을 사용하
지 말아야 합니다. 이런 특수문자는
“SQL 명령줄 실행”으로 데이터베
이스를 관리할 경우 로그인 실패를
가져오게 됩니다.
6
Oracle 10g Express 설치 (4)
 Oracle 10g Express의 설치는 일반 Windows 프로그
램의 설치표준을 따르며 화면에 나타나는 제시에
따라 설치를 진행하면 됩니다.
 설치가 완료되면 Windows “시작”메뉴에 아래와
같이 Oracle 10g Express 메뉴가 설치됩니다.
7
Oracle 10g Express 의 사용자계정
Oracle 10g Express(아래부터 Oracle XE로
약칭함)은 두 개의 주요 계정을 가집니다.
SYS계정은 Oracle의 대부분 테이블을 소유하고
데이터베이스의 시작과 중지를 할 수 있는 역할을
가집니다.
SYSTEM계정은 SYS사용자의 테이블을 바로 제
어하지 않고 테이블을 바탕으로 작성된 뷰를 소유
합니다.
8
Oracle 10g Express 관리방식
 Oracle XE는 두 가지 관리방식이 있습니다. 하나
는 웹 브라우저를 이용한 관리이고 다른 하나는
명령 줄을 이용한 관리입니다.
 웹 브라우저
을 이용하여
관리
 명령줄을 이
용하여 관리
9
웹 브라우저를 이용한 관리 (1)
시작메뉴의 “Database 홈페이지로 이동”을
선택하면 아래와 같은 화면이 나타납니다.
 설치완료 후 처음 사용 시
시스템에는 SYS 와
SYSTEM계정만 활성화 되
어있으므로 사용자이름은
SYS 혹은 SYSTEM을 입력
합니다. 사용자계정은 차후
필요에 따라 추가할 수 있
습니다.
 OracleXE를 설치 시 설정한
SYS혹은 SYSTEM계정의
비밀번호를 입력합니다.
10
웹 브라우저를 이용한 관리 (2)
로그인 후 화면은 아래와 같습니다.
11
명령줄을 이용하여 관리 (1)
 콘솔 창에 connect 를 입력하고 엔터를 누르면 로
그인 제시가 뜹니다.
 SYSTEM 계정은 그냥
SYSTEM을 입력하고 암
호를 입력하면 로그인 됩
니다.
12
명령줄을 이용하여 관리 (2)
 SYS계정은 “사용자 명 입력”에서
“SYS as SYSDBA” 혹은 “SYS as
SYSOPER” 이런 형식으로 로그인하
여야 합니다.
 SYSDBA는 데이터베이스의 시작과
종료 및 기타 관리 권한을 갖고 있는
Role입니다.
 SYSOPER은 SYSDBA 의 모든 기능
외에 데이터베이스 생성 권한을 갖고
있는 Role입니다.
 웹 브라우저를 이용한 관리와 명령 줄을 이용한
관리는 사용자의 취향에 따라서 선택하시면 됩니
다. 두 방식이 완성할 수 있는 기능은 같습니다.
아래 각 설명은 명령 줄 기반을 위주로 합니다.
13
새 사용자 추가
 데이터베이스 사용 시 기존의 관리용 SYS, SYSTEM계정을 직접 사용
하지 않고 새로 계정을 추가하여 테스트를 진행하는 것이 바람직합니
다.
 새 사용자 추가는 아래와 같이 하면 됩니다.
 Create user [USERID] identified by [PASSWORD] default tablespace users;
 사용자가 추가되면 권한을 부여해주어야 합니다.
 Grant [ROLE] to [USERID]
 예: 아래 그림은 test 사용자를 추가하고 권한을 부여하는 과정입니다.
14
SQL 문
Part: 2
15
테이블 생성: CREATE TABLE (1)
 형식:
CREATE TABLE 테이블이름 (
 칼럼 명1
데이터타입1 [NOT NULL],
 칼럼 명2
데이터타입2 [NOT NULL],
 …
 칼럼 명n
데이터타입n [NOT NULL],
 primary key (칼럼 명1, …, 칼럼 명m)
);
 ※ []로 표기한 부분은 선택입력항목임을 나타냅
니다.
16
테이블 생성: CREATE TABLE (2)
 예: customers 테이블을 만듭니다.
 create table customers (
 cid
char(4) not null,
 cname varchar(13),
 city
varchar(20),
 discnt
real
 );
17
Oracle의 데이터타입
 NUMBER (p, s): 숫자 타입, 전체 p자리 중 소수점
이하 s 자리.
 FLOAT(p): 실수 형 숫자타입, REAL = FLOAT(63)
 CHAR(n): 고정길이문자열데이터
 VARCHAR2(n): 가변길이 문자열데이터
 VARCHAR(n): VARCHAR2의 약칭사용
 RAW (n): n Byte의 이진데이터
 BLOB: 가변길이 이진 데이터
 DATE: 날짜타입, BC 4712/1/1 ~ CE 4712/12/31.
18
테이블정보보기: DESCRIBE
 형식:
DESC[RIBE] 테이블이름;
19
테이블 구조 변경: ALTER TABLE
 형식:
 새로운 칼럼을 추가하는 경우
 ALTER TABLE 테이블이름
 ADD (칼럼 명 데이터타입);
 존재하는 칼럼을 변경하는 경우
 ALTER TABLE 테이블이름
 MODIFY (기존의 칼럼이름 바꿀 데이터타입/크기);
20
테이블삭제: DROP TABLE
 기존의 테이블을 삭제합니다.
 형식:
 DROP TABLE 테이블이름;
21
데이터 추가: INSERT INTO
 형식:
INSERT INTO 테이블이름
[ (칼럼1, 칼럼2, … ) ]
VALUES( 값1, 값2, … )
 값과 칼럼은 서로 매칭되어야 합니다.
22
데이터 삭제: DELETE FROM
형식:
DELETE [FROM] 테이블이름
[WHERE 조건절 ]
23
데이터업데이트: UPDATE … SET
형식:
UPDATE 테이블이름
SET 칼럼1 = ‘값1’, 칼럼2 = ‘값2’, …
[WHERE 조건절 ];
Or
24
샘플데이터
 실습진행에 필요한 샘플데이터는 아래 주소에서 다운로드 할 수 있습니다.
 Create tables: http://dbsi.inha.ac.kr/www/course/db/oraclexe/create.sql
 Insert data: http://dbsi.inha.ac.kr/www/course/db/oraclexe/insert.sql
 쿼리 실행방법: 메모장 같은 텍스트편집도구로 해당 파일을 열고 파일내용을
전부 복사한 후 아래와 같이 명령줄 화면에 붙여 넣으면 됩니다.
타이틀 바에서 마우스
오른쪽 키 클릭, 붙여
넣기를 선택
전체선택, 복사
25
SELECT 문 (1)
형식:
SELECT [DISTINCT]
 {* | [칼럼1 [AS newName1]] [,...] }
FROM
테이블 이름 [alias] [, ...]
[WHERE
조건절 ]
[GROUP BY 칼럼리스트] [HAVING condition]
[ORDER BY 칼럼리스트]
26
SELECT 문 (2)
 테이블에 존재하는 모든 칼럼 조회하기
SELECT * FROM 테이블 이름
27
SELECT 문 (3)
 테이블에 존재하는 특정 칼럼 조회하기
SELECT 칼럼1, 칼럼2 … FROM 테이블이름
조회되는 레코드의 형태 제어하기
SELECT 칼럼1 as Newname1, 칼럼2 … FROM 테이블이름
28
SELECT 문 (4)
SELECT문은 일정 수량의 내장함수를 갖고
있습니다.
형식: SELECT 함수이름(변수) …
지원하는 함수 예:
AVG: 평균값을 구합니다.
COUNT: SELECT된 ROW의 수
SUM: 합계를 구합니다.
29
SELECT 문 (5)
조건절 쿼리
SELECT … FROM 테이블이름 WHERE 조건절
조건 절에서 문자열은 ‘’로 묶어주어야 합니다.
And, Or 등으로 조건 연산을 진행할 수 있습니다.
30
SELECT 문 (6)
여러 테이블에서 조회하기
SELECT … FROM TABLE1 Alias1, TABLE2
Alias2…
예: 소재도시가 같은 Customer와 Products 의 이름
과 해당도시를 조회하시오
31
SELECT 문 (7)
결과의 집결 GROUP BY
특정한 칼럼의 데이터 들을 다른 데이터들과 비
교해 유일한 값에 따라 무리를 짓습니다.
예: ORDERS에서 CID가 같은 Order들의 Dollars
합계를 구하시오.
32
SELECT 문 (8)
결과의 정렬
ORDER BY 정렬기준으로 할 칼럼이름1, …
33
PL/SQL 기초
Part: 3
34
PL/SQL 소개
 PL/SQL (Procedural Language extension to SQL) :
Oracle환경에서 실행되는 절차적인 데이터베이스
프로그래밍 언어이며 트랜잭션처리를 가능하게
합니다.
 PL/SQL 일반구조:








DECLARE
변수/상수 선언문
BEGIN
실행 문
EXCEPTION
예외처리(생략가능)
END;
/
--PL/SQL 종료문자
35
PL/SQL: 선언부
 DECLARE: 상수 혹은 변수를 선언합니다.
변수 선언:
 변수이름 데이터타입(크기) [:=초기값]
 예:
•
•
•
•
DECLARE
TOTAL_PRICE NUMBER(10,3);
ZIP_CODE VARCHAR(10);
P_COUNT NUMBER(3) := 0;
상수 선언:
 상수이름 CONSTANT 데이터타입(크기) := 상수 값
 예:
• DECLARE
• PI CONSTANT REAL:=3.1416;
36
PL/SQL: 실행 부
 SQL 문, 조건판단(IF),반복 문(LOOP, WHILE, FOR) 등이
포함됩니다.
 예: 일반 SQL문 처리
 (주: 예에서 사용할 SQL명령문 스크립트 파일에 대한 설명
은 본 PPT파일의 제일 마지막 페이지에 있습니다.)
37
반복 문 (1)
 LOOP문
 EXIT문이 실행되기 전까지 명령
문을 반복 처리합니다.
 문법:




LOOP
명령문…
EXIT문
END LOOP
 EXIT문은 조건을 만족하면 LOOP
루프로부터 탈출하도록 합니다.
 문법:
 EXIT WHEN 조건 문
 예: 1부터 9까지 반복하면서 시스
템시간을 TEMP테이블에 저장하
시오
 ※SYSDATE함수는 시스템시간을
가져옵니다
38
반복 문(2)
 WHILE…LOOP문
 조건이 만족하면 명령문을 반
복 처리합니다.
 문법:
 WHILE 조건 LOOP
 명령문…
 END LOOP
 FOR…LOOP문
WHILE…LOOP 사용
 변수가 초기 값부터 1씩 증가하
여 최종 값이 될 때까지 명령문
을 반복 처리합니다.
 문법:
 FOR 변수 IN 초기값 .. 최종 값
LOOP
 명령문…
 END LOOP
 예: LOOP문과 같은 예를 위의
두 개 반복 문으로 실현합니다.
FOR…LOOP 사용
39
IF 조건 문
 예:
 문법:
 1.
 IF 조건 THEN
 명령문_1
 END IF;
 2.
 IF 조건 THEN
 명령문_1
 ELSE
 명령문_2
 IF total_price>10000 THEN
 score :=‘good’;
 ELSEIF total_price<8000 THEN
 score :=‘fail’;
 ELSE
 score :=‘normal’
 END IF
 END IF;
 3.
 IF 조건_1 THEN
 명령문_1
 ELSIF 조건_2 THEN
 명령문_2
 ELSIF 조건_3 THEN
 명령문_3

… ...
 ELSE
 명령문_n
 END IF;
40
PL/SQL에서의 SELECT문 (1)
 PL/SQL에서의 SELECT는 기존 SQL에서의 SELECT 와 다
릅니다.
 쿼리진행결과의 Row 개수가 한 개에 제한되어야 하고, 검색한 값
을 INTO절의 변수에 저장합니다.
 쿼리진행결과의 Row의 수가 0개(NO_DATA_FOUND)이거나 복
수 개(TOO_MANY_ROWS)일 때 오류가 발생합니다.
 ORDER BY절은 사용하지 않습니다.
 문법:
 SELECT 칼럼이름1, 칼럼이름2, …, 함수, 수식, ...
 INTO
변수1, 변수2...
 FROM 테이블이름1, 테이블이름 2, ...
 WHERE 검색조건
 GROUP BY 칼럼이름 1, 칼럼이름 2, ...
41
PL/SQL에서의 SELECT문 (2)
 예: Orders데이터의 Order 수와 Dollars 합계를 출
력하시오.
DBMS_OUTPUT.PUT_LINE 은 출력함수입니다.
 만약 출력이 없으면 “set serveroutput on”을 입력하시오.
“||” 는 문자열을 연결해줍니다.
42
Cursor
Part: 4
43
Cursor 개요
 PL/SQL의 SELECT문은 한 개 이상의 Row를 조회할 수 없
습니다.Cursor을 이용하면 한 개 이상의 Row를 조회할 수
있습니다.
 Cursor 사용일반과정
 Cursor를 선언합니다(Declare):
 Cursor에 이름을 부여하고 Select문과 연결합니다.
 Cursor를 엽니다(Open):
 Cursor과 연결된 Select문이 실행됩니다.
 Cursor에서 Row를 가져옵니다(Fetch):
 각 Row의 값이 한번에 하나씩 반환됩니다.
 Cursor를 닫습니다(Close):
 Cursor와 관련된 Oracle의 모든 자원이 개방됩니다.
44
Cursor 의 선언
 예: 변수가 없는 경우
 문법:
 DECLARE
CURSOR 커서이름
 CURSOR get_prices IS
[(
SELECT price FROM
products;
변수1 변수타입 [:=초기값],
변수2 변수타입 [:=초기값],
 예: 변수가 있는 경우
…
 DECLARE
)]
 CURSOR
IS SELECT 문
get_prices(cityname varchar)
IS SELECT price FROM
products WHERE
city=cityname;
45
Cursor 열기
문법:
OPEN 커서이름[(변수 값1, 변수 값2…)];
Cursor가 변수를 가지고 선언되면 각 변수
에 대한 값을 지정해주어야 합니다.
예:
BEGIN
OPEN get_prices(‘Dallas’);
END;
46
Cursor에서 Row가져오기
 일반적으로 반복 문을 사용하여 Cursor로부터 Row를 추출
합니다.
 Cursor은 SELECT문의 각 칼럼 값을 순서대로 해당 변수에
부여합니다.
 문법:
 FETCH 커서이름 INTO 변수1, 변수2, …
 사용자는 Cursor로부터 모든 Row가 추출되었을 때 EXIT
문을 사용하여 루프를 빠져나가야 합니다.
 EXIT [WHEN 조건 문]
 여기서 조건 문은 커서의 상태 정보를 나타내
는 %ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN과 결합됩
니다.
 예:
 EXIT WHEN get_prices%NOTFOUND
47
Cursor의 속성
%FOUND와 %NOTFOUND: Cursor가 반환
할 Row의 유무를 나타냅니다.
%ROWCOUNT: Cursor에서 추출된 Row의
현재 집계를 반환합니다.
%ISOPEN: 특정Cursor가 열려있는지 여부
를 반환합니다.
48
Cursor 닫기
사용자는 두 가지 이유에서 커서를 닫아야
합니다.
다양한 결과값 조회를 위하여 Cursor를 다시 열기
위하여
Cursor가 차지한 자원을 개방하기 위하여
문법:
CLOSE 커서 이름
예:
CLOSE get_prices;
49
Cursor 예
 매번 거래에 참여한 Customer, Product, Agent의 이
름 및 거래액수를 출력하시오.
50
Cursor 예(출력화면)
51
SQL 스크립트 파일 실행
 예에서 언급 된 스크립트 파일들은
http://dbsi.inha.ac.kr/www/course/db/oraclexe/scripts.
zip 에서 다운로드 할 수 있습니다.
 파일이름은 본 PPT 해당 예 페이지 번호와 매칭됩
니다.
 특정폴더에 압축을 푼 후 PL/SQL 콘솔에서
start 경로\파일명
 하시면 해당 SQL 스크립트는 실행 됩니다.
예: d:\에 압축을 풀었고 P50의 커서 예를 실행하려면
start d:\p50
하시면 됩니다.
52