1월 25일 국비교육

#1

1. 오라클 sys, system 암호 재설정

C:>sqlplus "/as sysdba"

SQL> show user

- USER is "SYS"

2. 암호 원하는 대로 설정

SQL> alter user sys identified by 암호;

SQL> alter user system identified by 암호;

3. 모든 유저 보기

SQL> select username from all_users;

4. 모든 테이블 보기

SQL> SELECT TABLE_NAME FROM USER_TABLES;

5. SQLPLUS에서 SQL문의 실행속도를 알아보기

SQL>SET TIMING ON

SQL>SELECT COUNT(ENAME) FROM EMP;

6. SQLPLUS에서 SELECT문 사용시 데이터만 출력되고 HEADER는 안 나오게 하는 방법

SQL> SET HEADING OFF

SQL> SELECT EMPNO, ENAME, MGR FROM EMP WHERE ROWNUM < 6;

7.

SQL> CONN AS SYSDBA;

#2

cf. 계정설정을 위해서는 http://localhost:8080/apex/f?p=4550:11:2172639819672742::NO::: 

들어가서 계정 설정 해주기

create tablespace 관리하고자 하는 dbf파일명

실습 1. 테이블 영역과 파일을 선언

SQL> CREATE TABLESPACE TS_TEST

  2  LOGGING

  3  DATAFILE 'C:/Test/ts_test.dbf'

  4  size 32m

  5  autoextend on

  6  next 32m maxsize 2048m

  7  extent management local;

실습 2. 사용자 생성

SQL> CREATE USER TESTMAN

   IDENTIFIED BY TESTMAN

   DEFAULT TABLESPACE TS_TEST

   TEMPORARY TABLESPACE TEMP;

실습 3. 권한 부여

grant connect, resource to testman;

실습 4. 설정한 계정으로 접속

conn testman/testman;

#3

오라클 계정 설정

ⓐ 위에서 datafile에서 경로 빼먹거나 잘못넣었을 때 대처법

1. Sysdba로 다시연결

SQL>show user;

USER은 "TESTMAN"입니다.

SQL> conn as sysdba

사용자명 입력 : sys

암호 입력 : *********

SQL> shutdown immediate;

데이터베이스가 닫혔습니다.

데이터베이스가 마운트 해제되었습니다.

ORACLE 인스턴스가 종료되었습니다.

2. 파일을 맞는 경로(C:\ORACLE\ORADATA\TEST\)에 수작업으로 옮긴 후,

3. SQL> STARTUP MOUNT;

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  805306368 bytes

Fixed Size                  1289996 bytes

Variable Size             213909748 bytes

Database Buffers          587202560 bytes

Redo Buffers                2904064 bytes

데이터베이스가 마운트되었습니다.

SQL> 데이터베이스가 변경되었습니다. ALTER DATABASE RENAME FILE 'C:\TEST\TS_TEST.DBF'TO

'C:\ORACLE\ORADATA\TEST\TS_TEST.DBF';

4. 수정하면 된다.

SQL> ALTER DATABASE OPEN;

데이터베이스가 변경되었습니다.

#4

오라클 계정 설정

1.

2. 사용자에게 주는 일반적인 권한들

ⓐ 데이터베이스에 접속할 수 있는 권리(세션 생성) 

ⓑ 사용자 스키마에 테이블을 생성할 수 있는 권리

ⓒ 다른 사용자 테이블의 행을 선택할 수 있는 권리

ⓓ 다른 사용자의 내장 프로시저를 실행할 수 있는 권리

3. Roll(역할)

ⓐ 시스템 권한만 해도 120여가지나 된다. 따라서 이 많은 권한들을 사용자마다 일일이 부여하기란 쉽지 않으므로 권한들을 미리 정의해놓은 집합을 만들어놓고, 

4. 오라클 데이터베이스에 설치와 동시에 기본적으로 생성되어 있는 롤

ⓐ CONNECT : 접속권한 가진 롤

ⓑ RESOURCE : 객체의 생성, 변경, 삭제 등 기본 시스템 권한을 가진 롤

ⓒ DBA : DB관리에 필요한 권한을 가진 롤

ⓓ SYSDBA : DB시작과 종료 및 관리 권한을 가진 롤

ⓔ SYSORDER : SYSDBA 권한 + DB생성 권한을 가진 롤

5. 실습

SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE IN('CONNECT', 'RESOURCE');

#5

1. 오라클 TABLE- 자료형

SQL> SELET SYSDATE, TO_CHAR(SYSDATE, 'D') FROM DUAL; - 주에 대한 날짜(1~7)

일 ~7 -> 토요일 (D: 1~7 수 반환)

SYSDATE  TO

-------- --

19/01/25 6

2. TABLE 생성 시 제한사항과 고려할 점

ⓐ 테이블 이름과 컬ㄹ머은 항상 알파벳 문자로 시작해야 하며 A~XZ까지의 문자, 0~9까지의 숫자, 그리고 $, # , _ 를 사용할 수 있다(공백사용 불가능)

ⓑ 테이블의 컬럼 이름은 30자를 초과할 수 없고, 예약어를 사용할 수 없습니다.

3. TABLE 생성

ⓐ CHAR : 문자형(고정길이) MAX :2000BYTE

ⓑ NCHAR : 문자형(고정길이), 유니코드 지원 MAX :2000BYTE

ⓒ VARCHAR2 : 문자형(가변길이) MAX : 4000BYTE (오라클 기준), 기본 DB - 2000BYTE

ⓓ NVARCHAR2 : 문자형(가변길이), 유니코드 지원, MAX : 4000BYTE

ⓔ DATE : 날짜형, 형식 지정자로 변환가능, MAX : 7BYTE

ⓕ NUMBER : 숫자형 

4. 대용량 데이터 저장용 자료형

ⓐ long : 문자/바이너리 저장용, max 2byte 테이블에 단 한번만 사용 가능

ⓑ clob : 문자 저장용 : MAX : 4GBYTE

ⓒ blob : 바이너리 저장용, MAX : 4GBYTE (데이터 저장/출력시 특수한 변환과정 필요)

5. 테이블 생성 시 

CREATE TABLE EMP2

AS 

SELECT * FROM EMP;

집계함수 사용시 별칭을 꼭 줄 것

#6

PL/SQL

1. 작성순서

ⓐ DECLARE(선택, 변수 선언)

ⓑ BEGIN(필수, SQL 문장, PL/SQL 문장)

ⓒ EXCEPTION(선택 -> 예외처리)

ⓓ END

2. 데이터 타입의 설명

ⓐ BOOLEAN

ⓑ BINARY_ INTEGER

ⓒ 

ⓓ 

3. 기본명령

ⓐ SET SERVERPUT ON : PL/SQL에서 화면에 출력하기

ⓑ 문자열을 화면에 출력 : DBMS_OUTPUT.PUT_LINE

ⓒ DBMS_OUTPUT.PUT_LINE('출력할 내용')

ⓓ DBMS_OUTPUT 프로시저의 활성화와 비활성화

- DBMS_OUTPUT.ENABLE; //활성화

- DBMS_OUTPUT.DISABLE; //비활성화

4.함수 VS 메소드

함수 :귀속이 없음.

메소드 :  

5. 실습 

ⓐ

SQL> SET SERVEROUTPUT ON

SQL> BEGIN

  2     DBMS_OUTPUT.DISABLE;

  3     DBMS_OUTPUT.PUT_LINE('1.HELLO WORLD.');

  4     DBMS_OUTPUT.ENABLE;

  5     DBMS_OUTPUT.PUT_LINE('2.HELLO WORLD.');

  6     DBMS_OUTPUT.PUT_LINE(3+3);

  7     END;

  8  /

ⓑ

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2     I INTEGER := 20;

  3     BEGIN

  4     DBMS_OUTPUT.PUT_LINE('현재의 I값은?'||I);

  5     END;

  6  /

현재의 I값은?20

ⓒ 

SQL> DECLARE

  2     I INT := 10;

  3     BEGIN

  4     IF I !=20 THEN

  5     DBMS_OUTPUT.PUT_LINE('현재의 값은?' || I ||'이다');

  6     END IF;

  7     END;

  8     /

ⓓ elsif

SQL> DECLARE

  2     SCORE01 INT := 85;

  3     GRADE01 VARCHAR2(2);

  4     BEGIN

  5     IF SCORE01 >= 90 THEN GRADE01 := 'A';

  6     ELSIF SCORE01 >= 80 THEN GRADE01 := 'B';

  7     ELSIF SCORE01 >= 70 THEN GRADE01 := 'C';

  8     ELSIF SCORE01 >= 60 THEN GRADE01 := 'D';

  9     ELSE                     GRADE01 := 'F';

 10     END IF;

 11     DBMS_OUTPUT.PUT_LINE('당신의 SCORE는?' || SCORE01 || '점이고' ||' '||GRADE01||'입니다.');

 12     END;

 13  /

6. FOR ~ LOOP문

변수가 초기값부터 1씩 증가하여 최종값이 될 때까지 반복처리

문법 > FOR 변수 IN 초기값 .. 최종값 LOOP

명령문1

..

명령문 N;

END;

ⓐ 

SQL> DECLARE

  2  NAME VARCHAR2(10) := '홍길동';

  3  I INT := 0;

  4  BEGIN

  5     FOR I IN 1..3 LOOP

  6     DBMS_OUTPUT.PUT_LINE(NAME);

  7     END LOOP;

  8  END;

  9  /

ⓑ FOR 예제 

SQL> DECLARE

  2  I INT := 0;

  3  BEGIN

  4     FOR I IN 1..16 LOOP

  5     DBMS_OUTPUT.PUT_LINE('현재의 I값은'|| I ||'이다');

  6     END LOOP;

  7  END;

  8  /

ⓒ 

SQL> CREATE TABLE TEST1(

  2     BUNHO NUMBER(3),

  3     IRUM VARCHAR2(10));

SQL> BEGIN

  2     FOR I IN 1..10 LOOP

  3     INSERT INTO TEST1

  4     VALUES(I, SYSDATE);

  5     END LOOP;

  6  END;

  7  /

ⓓ 홀수단만 출력하기

SQL> DECLARE

  2     TOTAL NUMBER := 0;

  3     BEGIN

  4     FOR I IN 2..9 LOOP

  5             IF MOD(I, 2) <> 0 THEN

  6             DBMS_OUTPUT.PUT_LINE(I || '단');

  7             DBMS_OUTPUT.PUT_LINE('=======');

  8             FOR J IN 2..9 LOOP

  9             TOTAL := J * I;

 10             DBMS_OUTPUT.PUT_LINE(I || '*' || J || '=' || TOTAL || '  ');

 11             END LOOP;

 12             END IF;

 13     END LOOP;

 14  END;

 15  /

ⓔ EMP 테이블에서 부서번호 10인 사원 평균급여 출력하는 익명의 블록을 작성하라. 

SQL> DECLARE

  2     AVG01 NUMBER(7) := 0;

  3     DEPTNO01 NUMBER(7) := 10;

  4     BEGIN

  5     SELECT AVG(SAL) INTO AVG01

  6     FROM EMP

  7     WHERE DEPTNO = DEPTNO01

  8     GROUP BY DEPTNO;

  9     DBMS_OUTPUT.PUT_LINE(DEPTNO01||'번 부서의 평균 급여는 ['||AVG01||']원입니다.');

 10  END;

 11  /

10번 부서의 평균 급여는 [2917]원입니다.

ⓕ 20번 부서 N명에 대한 평균급여 구하기

SQL> DECLARE

  2     V_AVG NUMBER(5) := 0;

  3     V_CNT NUMBER(5) := 0;

  4     V_DEPTNO VARCHAR2(5) := 20;

  5     BEGIN

  6     SELECT COUNT(DEPTNO), AVG(SAL) INTO V_CNT, V_AVG

  7     FROM EMP

  8     WHERE DEPTNO = V_DEPTNO

  9     GROUP BY DEPTNO;

 10     DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'번 부서에['||V_CNT||']명에 대한 평균 급여는 ['||V_AVG||']원입니다..');

 11  END;

 12  /

#7

프로시져

ⓐ 프로시저의 정의 : 특정작업을 수행할 수 있고, 

#8