나웃기면백원

Oracle

** 윈도우 서버 HOST명은 제어판 -> 시스템 -> 컴퓨터 이름의 전체 컴퓨터 이름이다

SQL*PLUS 

** 사용자의 생성 및 변경, 권한 부여는 SYS와 SYSTEM 사용자만이 할 수 있다

사용자

** 사용자가 사용할 테이블스페이스를 만든 다음 사용자 생성시 해당 테이블스페이스를 지정한다

SYS/CHANGE_ON_INSTALL AS SYSDBA  /* 오라클 슈퍼 사용자 ID */

SYSTEM/MANAGER                   /* 오라클 데이타베이스를 유지보수 관리할때 사용하는 사용자 ID */

SCOTT/TIGER                      /* SAMPLE 사용자 ID */

HR/HR                            /* SAMPLE 사용자 ID */

CREATE USER 사용자명 IDENTIFIED BY 패스워드; /* 사용자 생성과 패스워드 설정 */

CREATE USER 사용자명 IDENTIFIED BY 패스워드 /* 사용자 생성시 테이블스페이스 지정 */

DEFAULT TABLESPACE 테이블스페이스명 

TEMPORARY TABLESPACE 테이블스페이스명;

ALTER USER 사용자명 DEFAULT TABLESPACE 테이블스페이스명; /* 사용자 디폴트 테이블스페이스 변경 */

ALTER USER 사용자명 IDENTIFIED BY 패스워드; /* 사용자 패스워드 변경 */    

ALTER USER 사용자명 PASSWORD EXPIRRE; /* 사용자 패스워드 무효 */

ALTER USER 사용자명 ACCOUNT LOCK; /* 사용자 접속 금지 */

ALTER USER 사용자명 ACCOUNT UNLOCK; /* 사용자 접속 허용 */

DROP USER 사용자명 /* 사용자 삭제 */

DROP USER 사용자명 INCLUDING CONTENTS; /* 사용자와 관련된 모든 정보 삭제 */

권한

** 사용자를 만든후 해당 사용자에게 권한을 부여 해야 한다

** 오브젝트 권한 ALL(ALTER, DELETE, INDEX, INSERT, REFERENCE, SELECT, UPDATE)을 사용하여

  테이블에 한번에 모든 권한을 부여할 수 있거나 원하는 권한만 부여할 수 있다

  뷰는 DELETE, INSERT, SELECT, UPDATE, 시퀀스는 ALTER, SELECT, 프로시저는 EXECUTE 권한을 줄 수 있다

** 120여개나 되는 권한을 일일이 부여하는 단점을 해결하기 위해 ROLE을 사용한다

GRANT 권한1, 권한2, ... TO 사용자명             /* 사용자에 권한 부여 */

GRANT CREATE SESSION TO 사용자명;               /* 데이타베이스에 접속할 수 있는 권한 */

GRANT CREATE TABLE TO 사용자명;                 /* 테이블을 만들수 있는 권한 */

GRANT SELECT ON 테이블명 TO 사용자명;           /* 테이블의 SELECT 권한 */

GRANT ALL ON 테이블명 TO 사용자명;              /* 테이블에서 처리할 수 있는 모든 권한 */

GRANT EXECUTE 프로시저명 TO 사용자명;           /* 프로시저를 실행할 수 있는 권한 */

GRANT SELECT ON A사용자명.테이블명 to B사용자명 /* A사용자 테이블의 SELECT 권한을 B사용자에게 부여 */

REVOKE 권한1, 권한2, ... FROM 사용자명;         /* 사용자의 권한 취소 */

REVOKE CREATE SESSION FROM 사용자명;            /* 데이타베이스에 접속할 수 있는 권한 취소 */

REVOKE CREATE VIEW FROM 사용자명;               /* 뷰를 만들수 있는 권한 취소 */

CONNECT            /* 데이타베이스에 접속할 수 있는 권한을 가진 롤 */

RESOURCE           /* 테이블, 인덱스, 뷰등을 생성, 변경, 삭제할 수 있는 권한을 가진 롤 */

DBA                /* 데이타베이스를 관리하기 위해 필요한 권한을 가진 롤 */

RXP_FULL_DATABASE  /* EXPORT 툴을 사용할 수 있는 권한을 가진 롤 */

IMP_FULL_DATABASE  /* IMPORT 툴을 사용할 수 있는 권한을 가진 롤 */

SYSDBA             /* 데이타베이스 시작과 종료및 모든 관리를 하기 위한 필요한 권한을 가진 롤 */

SYSOPER            /* SYSDBA 권한을 포함 데이타베이스를 생성할 수 있는 권한을 가진 롤 */

GRANT 롤1, 롤2, ... TO 사용자명;                 /* 사용자에게 롤 권한 부여 */

REVOKE 롤1, 롤2, ... FROM 사용자명;              /* 사용자의 롤 권한 취소 */

로그온

CONNECT 또는 CONNECT 사용자ID/패스워드

자료 사전

** 특정 테이블에 관련된 자료사전이 어떤 것인지 알고 싶을때는 DICTIONARY 테이블의

  COMMENTS 칼럼에 특정 테이블과 관련된 문자열을 찾아보면 된다

DBA_xxxx        /* DB 전체의 관련된 정보로 SYS, SYSTEM 사용자만 볼 수 있다 */

ALL_xxxx /* 자신이나 다른 사용자가 생성한 오브젝트중에 자신이 볼 수 있는 정보 */

USER_xxxx       /* 자신이 생성한 오브젝트 정보 */

V$_xxxx         /* DB의 성능 분석, 통계 정보를 제공하면 X$의 뷰 */

X$_xxxx         /* DB의 성능 분석, 통계 정보 */

SELECT * FROM DICTIONATY; /* 모든 상태 정보 */

SELECT * FROM ALL_USERS; /* 사용자 ID 리스트 */

SELECT * FROM USER_USERS; /* 사용자 정보 */

SELECT * FROM USER_CONSTRAINTS; /* 제약 정보 */ 

SELECT * FROM USER_TABLESPACES /* 테이블스페이스 정보 */

SELECT * FROM USER_TABLES; /* 테이블 정보 */

SELECT * FROM DBA_ROLES;                         /* 전체 롤, SYS, SYSTEM에서 사용 */

SELECT * FROM USER_ROLE_PRIVS;                   /* 현재 사용자에게 부여된 롤 확인 */

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE=롤명;    /* 해당 롤이 어떤 권한을 가지고 있는지 확인 기/

SELECT * FROM USER_TAB_PRIVS_RECD;               /* 오브젝트에서 부여 받은 권한 */

TableSpace(테이블스페이스)

- SYSTEM 테이블스페이스

** 사용자 생성시 별도의 테이블스페이스를 지정하지 않으면 시스템으로 지정된다

  시스템이 사용자 데이타 포함을 허용하더라도 사용하지 말아야 한다

데이타베이스와 함께 생성되고 모든 데이타베이스에서 필요하다

데이타 딕셔너리 정보를 포함한다

시스템 실행 취소 세그먼트를 포함한다

- UNDO 테이블스페이스

SQL문을 실행한 후 트랜잭션을 ROLLBACK 하기 위해 변경 이전의 데이타를 저장한다

- TEMP 테이블스페이스

SQL문을 실행할 때 내부 분류작업(GROUP BY, ORDER BY, ...)을 위해 사용한다

USERS 테이블스페이스

사용자가 테스트할 수 있는 저장공간이다

- CREATE TABLESPACE 테이블스페이스명 /* 자동 확장 테이블스페이스를 만들기 */

DATAFILE 디렉토리와파일명 SIZE 크기

AUTOEXTEND ON NEXT 크기 MAXSIZE 크기;  /* 자동으로 NEXT 만큼 확장 MAXSIZE는 지정하지 않아도 된다 */

ALTER TABLESPACE RENAME 테이블스페이스명 TO 새테이블스페이스명;  /* 테이블스페이스명 변경 */

ALTER TABLESPACE 테이블스페이스명 RENAME         /* 물리적인 데이타 파일 변경 */

DATAFILE 'c:\oracle\oradata\jerry01.dbf' TO 'c:\oracle\oradata\aaa\jerry01.dbf';

ALTER USER 사용자명 TEMPORARY TABLESPACE 템프테이블스페이스명;   /* 사용자의 기본 템프데이블스페이스 변경 */

DROP TABLESPACE 테이블스페이스명 INCLUDING CONTENTS;

** 테이블스페이스를 삭제하더라도 관련 데이타 파일은 삭제되지 않기 때문에

  탐색기에서 직접 삭제해야 한다

테이블 스페이스 및 사용자 생성

** 테이블 스페이스는 저장 영역 및 임시 테이블 스페이스 이렇게 두게가 필요하지만

1. CREATE TABLESPACE ts_name 

DATAFILE '/home/oracle/data/ts_name.dbf' SIZE 100M 

AUTOEXTEND ON NEXT 10M

2. CREATE TEMPORARY TABLESPACE ts_name_temp TEMPFILE '/home/oracle/data/ts_name_temp.dbf' SIZE 10M 

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K               

** 임시 테이블 스페이스는 기본으로 정해져 있는 TEMP TABLESPACE 영역을 사용해도 된다

3. CREATE USER 사용자명 IDENTIFIED BY 패스워드

DEFAULT TABLESPACE ts_name          

TEMPORARY TABLESPACE ts_name_temp   

4. GRANT CONNECT, RESOURCE TO 사용자명

Table(테이블)

** 제약 조건에는 PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULL이 있다

CREATE TABLE 테이블명 (칼럼 데이타형 제약, ...); /* 테이블 만들기 */

CREATE TABLE 테이블명 (칼럼1 데이타형, ...              /* 기본 키 설정 */

                      CONSTRAINT 이름 RPIMARY KEY (칼럼1)); 

ALTER TABLE 테이블명 DROP PRIMARY KEY;                  /* 기본 키 삭제 */ 

ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;            /* 제약 조건 삭제 */

ALTER TABLE 테이블명 MODIFY (칼럼 데이타형, ...);       /* 칼럼의 타입 변경 */

ALTER TABLE 테이블명 ADD (칼럼 데이타형, ...);          /* 칼럼 추가 */

ALTER TABLE 테이블명 DROP COLUMN 칼럼;                  /* 칼럼 삭제 */

ALTER TABLE 테이블명 RENAME COLUMN 칼럼명 TO 새칼럼명   /* 칼럼명 변경 */

ALTER TABLE 테이블명 MOVE TABLESPACE 테이블스페이스명;  /* 테이블의 테이블스페이스 변경 */

DROP TABLE 테이블명;                                    /* 테이블 삭제 */

RENAME 테이블명 TO 새테이블명;                          /* 테이블명 변경 */

TRUNCATE TABLE 테이블명;                                /* 테이블내의경 데이타 삭제 */

SELECT * FROM 테이블명; /* 현재 사용자인 경우 사용자명은 생략 */

SELECT * FROM 사용자명.테이블명; /* 다른 사용자인 경우 해당 사용자명을 지정 */

SELECT * FROM DBA_CONSTRAINTS                           /* 테이블별 CONSTRAINT(제약, PRIMARY KEY, FOREIGN KEY..) */

WHERE OWNER=사용자명 AND TABLE_NAME=테이블명;

SELECT USER FROM DUAL;                                  /* 현재 사용자명 확인 */

SELECT SYSDATE FROM DUAL;                               /* 오늘 날짜 확인 */

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;      

** 오라클에서는 테이블없이 SELECT문의 결과를 보고자 할때 DUAL을 사용한다

DESC 테이블명; /* 테이블 구조 */

Sequence(시퀀스)

** MSSQL, MySql의 자동 증가와 같은 역할을 한다

  SEQUENCE가 한번 증가되면 데이타가 삭제 되어도 다시 사용할 수 없다

CREATE SEQUENCE 시퀀스명 INCREMENT BY n   /* 증가값 */

                        START WITH n   /* 시작값 */

MAXVALUE n | NOMAXVALUE   /* 최대값/최대값 지정하지 않음 */

MINVALUE n | NOMINVALUE   /* 최소값/최소값 지정하지 않음 */

CYCLE | NOCYCLE           /* CYCLE은 최대값까지 증가가 완료되면 다시 START로 돌아간다

                             NOCYCLE은 증가가 완료되면 에러를 발생시킨다 */

CACHE n | NOCACHE         /* 시퀀스를 메모리에서 관리하며 기본값은 20 */

CREATE TABLE Temp (id number, name varchar(20));

CREATE SEQUENCE sqe (INCREMENT BY 1, START WITH 1, NOCYCLE, NOCACHE);

INSERT INTO Temp (id, name) VALUES (seq.NEXTVAL, '홍길동');

Synonym(시노늄)

** 이름이 긴 다른 사용자의 객체를(사용자명.테이블명) 요약 해서 사용한다

  PUBLIC 키워드는 데이타베이스내 모든 사용자들이 시노늄을 사용할 수 있게 한다

CREATE PUBLIC SYNONYM 시노늄명 FOR 사용자명.테이블명;   

CREATE PUBLIC SYNONYM Emp FOR hr.employee;

SELECT * FROM Emp;

View(뷰)

** REPLACE 명령을 사용하면 만들거나 수정할때 마다 다시 컴파일을 한다

CREATE OR REPLACE VIEW 뷰명 AS SELECT 칼럼1, 칼럼2, ... FROM 테이블명 WHERE 조건;

DROP VIEW 뷰명;

Procedure(프로시저)

CREATE OR REPLACE PROCEDURE 프로시저명    /* 프로시저 생성 */

   (파라미터1 데이타타입, 파라미터2 OUT 데이타타입, 파라미터3 IN OUT 데이타타입)

IS 

   변수 선언;

BEGIN

   프로그램;

END;

DROP PROCEDURE 프로시저명;                /* 프로시저 삭제 */

MSSQL

** SQL문은 대소문자를 구분하지 않지만 데이타는 대문자와 소문자를 구분한다

  주석을 다는 방법은 /* 주석 */ 이거나 한줄만 주석 처리를 할 경우는 문장 맨앞에 --를 붙인다

** 각각의 데이타베이스의 SYSOBJECTS 테이블에 해당 데이타베이스의 모든 정보가 보관되어 있다

  SYSOBJECTS의 TYPE 칼럼으로 'U'=사용자 테이블, 'P'=저장 프로시저, 'K'=프라이머리 키, 'F'=포린 키,

  'V'=뷰, 'C'=체크 제약등 오브젝트 이름과 정보를 알 수 있다

데이타 검색

USE 데이타베이스명 /* USE 문을 사용한 데이타베이스 선택 */

SELECT * FROM 데이블명 /* 모든 칼럼 불러오기 */

SELECT TOP n * FROM 테이블명 /* 상위 n개의 데이타만 가져오기 */

SELECT 칼럼1, 칼럼2, 칼럼3 FROM 테이블명 /* 특정 칼럼 가져오기 */

SELECT 칼럼1 별명1, 칼럼2 AS 별명2 FROM 테이블명 /* 칼럼에 별명 붙이기 */

SELECT 칼럼3 '별  명3' FROM 테이블명                    /* 칼럼 별명에 스페이스가 들어갈 경우는 작은따옴표 사용 */

SELECT DISTINCT 칼럼 FROM 테이블명                      /* 중복되지 않는 데이타만 가져오기 */

** 데이타는 오름차순으로 재배열된다

  DISTINCT를 사용하면 재배열이 될때까지 데이타가 리턴되지 않으므로 수행 속도에 영향을 미친다 */

SELECT * FROM 테이블명 WHERE 조건절                     /* 조건에 해당하는 데이타 가져오기 */

** 조건식에 사용하는 비교는 칼럼=값, 칼럼!=값, 칼럼>값, 칼럼>=값, 칼럼<값, 칼럼<=값이 있다

  문자열은 ''(작은따옴표)를 사용한다

  날짜 비교를 할때는 'yy-mm-dd' 형식의 문자열로 한다(날짜='1992-02-02', 날짜>'1992-02-02')

SELECT * FROM 테이블명 WHERE 칼럼 BETWEEN x AND y       /* 칼럼이 x>=와 y<=사이의 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 IN (a, b...)          /* 칼럼이 a이거나 b인 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 LIKE '패턴'           /* 칼럼이 패턴과 같은 데이타 가져오기 */

** 패턴에 사용되는 기호는 %, _가 있다

  'k%'(k로 시작되는), '%k%'(중간에 k가 있는), '%k'(k로 끝나는)

  'p_'(p로 시작하는 2자리), 'p___'(p로 시작하는 4자리), '__p'(3자리 데이타중 p로 끝나는)

SELECT * FROM 테이블명 WHERE 칼럼 IS NULL               /* 칼럼이 NULL인 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 NOT BETWEEN x AND y   /* 칼럼이 x와 y 사이가 아닌 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 NOT IN (a, b...)      /* 칼럼이 a나 b가 아닌 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 NOT LIKE '패턴'       /* 칼럼이 패턴과 같지 않은 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 IS NOT NULL           /* 칼럼이 NULL이 아닌 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼>=x AND 칼럼<=y        

SELECT * FROM 테이블명 WHERE 칼럼=a or 칼럼=b

SELECT * FROM 데이블명 WHERE 칼럼1>=x AND (칼럼2=a OR 칼럼2=b)

** 복수 조건을 연결하는 연산자는 AND와 OR가 있다

  AND와 OR의 우선순위는 AND가 OR보다 높은데 우선 순위를 바꾸고 싶다면 ()을 사용한다

SELECT * FROM 테이블명 ORDER BY 칼럼                    /* 칼럼을 오름차순으로 재배열하기 */

SELECT * FROM 테이블명 ORDER BY 칼럼 ASC                

SELECT * FROM 테이블명 ORDER BY 칼럼 DESC               /* 칼럼을 내림차순으로 재배열하기 */

SELECT * FROM 테이블명 ORDER BY 칼럼1 ASC, 칼럼2 DESC   /* 복수 칼럼 재배열하기 */

SELECT * FROM 테이블명 ORDER BY 1 ASC, DESC 3           /* 칼럼 순서로 재배열하기 */ 

       ** 기본적으로 SELECT 문에서는 출력순서가 보증되지 않기 때문에

  데이타의 등록 상태나 서버의 부하 상태에 따라 출력되는 순서가 달라질 수 있다

  따라서 출력하는 경우 되도록이면 ORDER BY를 지정한다

** 칼럼 번호는 전체 칼럼에서의 번호가 아니라 SELECT문에서 선택한 칼럼의 번호이고 1부터 시작한다

연산자

** 1순위는 수치 앞에 기술되는 + - 같은 단항 연산자

  2순위는 사칙 연산의 산술 연산자인  * / + -

  3순위는 = > 비교 연산자

  4순위는 AND OR 같은 논리 연산자

  ()을 붙이면 우선 순위를 바꿀수 있다

1. SELECT 문의 연산 

  SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 

2. ORDER BY 구의 연산 

  SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 칼럼3+칼럼4 DESC

  SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 3 DESC

3. WHERE 구의 연산 

  SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 WHERE 칼럼2>=(칼럼3+칼럼4)

4. NULL 연산 

  SELECT 칼럼1, 칼럼2, ISNULL(칼럼3, 0) + ISNULL(칼럼4, 0) AS '별명' FROM 테이블명

** 수치형 데이타와 NULL값과의 연산 결과는 항상 NULL이다

  만약 NULL 값을 원치 않으면 ISNULL(칼럼, 기준값) 함수를 사용해서 기준값을 변환시킨다 

5. 날짜 연산

  SELECT GETDATE()                                    /* 서버의 현재 날짜를 구한다 */

  SELECT 날짜칼럼, 날짜칼럼-7 FROM 테이블명

  SELECT 날짜칼럼, 날짜칼럼+30 FROM 테이블명

  SELECT 날짜칼럼, DATEDIFF(day, 날짜칼럼, GETDATE()) FROM 테이블명

** 날짜의 가산과 감산은 + -로 할 수 있다

  날짜와 날짜 사이의 계산은 DATEDIFF(돌려주는값, 시작날짜, 끝날짜) 함수를 사용한다

6. 문자 연산

  SELECT 칼럼1 + 칼럼2 FROM 테이블명

  SELECT 칼럼 + '문자열'  FROM 테이블명

  SELECT 칼럼1 + '문자열' + 칼럼2 FROM 테이블명

** 기본 연결은 문자와 문자이고 문자와 숫자의 연결은 CONVERT 함수를 사용해야 한다

함수

1. 수치 함수

  ROUND(수치값, 반올림위치)            /* 반올림 및 자르기 */

  ABS(수치 데이타)                     /* 절대값 */

  SIGN(수치 데이타)                    /* 부호 */

  SQRT(수치값)                         /* 제곱근 */

  POWER(수치값, n)                     /* n승 */

2. 문자열 함수

  SUBSTRING(문자열, 위치, 문자수)      /* 특정 위치 문자열 추출 */

  LEFT(문자열, 문자수)                 /* 왼쪽부터 지정한 갯수 문자열 추출 */

  RIGHT(문자열, 문자수)                /* 오른쪽부터 지정한 갯수 문자열 추출 */

  LEN(문자열)                          /* 문자열의 문자수 */

  LTRIM(문자열)                        /* 문자열 왼쪽의 스페이스를 삭제 */

  RTRIM(문자열)                        /* 문자열 오른쪽의 스페이스를 삭제 */

  UPPER(문자열)                        /* 대문자로 변환 */

  LOWER(문자열)                        /* 소문자로 변환 */

** 위치나 문자수는 한글일 경우 2바이트가 한글자로 취급된다

  SELECT LEN('월abcd화수목')은 8을 돌려준다 

3. 날짜 함수

  GETDATE()                                /* 현재 날짜와 시각 */

  DATEADD(돌려주는값, 기간, 날짜)          /* 날짜 및 시간의 더하기와 빼기 */ 

  DATEDIFF(돌려주는값, 시작날짜, 끝날짜)   /* 두 날짜와 시간의 차이 */

  DATEPART(돌려주는값, 날짜)               /* 날짜와 시간의 특정값을 돌려준다 */

** 돌려주는값(약어)

  Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk, 

  Hour-hh, Minute-mi, Second-ss, Milisecond-ms

  SELECT DATEADD(dd, 7, 날짜칼럼)  

4. 형변환 함수

  CONVERT(데이타 타입, 칼럼)                 /* 칼럼을 원하는 데이타 타입으로 변환 */

  CONVERT(데이타 타입, 칼럼, 날짜형 스타일)  /* 원하는 날짜 스타일로 변환 */

  CAST(칼럼 AS 데이타 타입)                  /* 칼럼을 원하는 데이타 타입으로 변환 */ 

** 스타일

  1->mm/dd/yy, 2->yy.mm.dd, 3->dd/mm/yy, 4->dd.mm.yy, 5->dd-mm-yy,

  8->hh:mm:ss, 10->mm-dd-yy, 11->yy/mm/dd, 12->yymmdd

  SELECT CONVERT(varchar(10), 날짜칼럼, 2)

그룹화

함수

SELECT COUNT(*) FROM 테이블명               /* 전체 데이타의 갯수 가져오기 */

SEELECT COUNT(칼럼) FROM 테이블명           /* NULL은 제외한 칼럼의 데이타 갯수 가져오기 */

SELECT SUM(칼럼) FROM 테이블명              /* 칼럼의 합계 구하기 */

SELECT MAX(칼럼) FROM 테이블명              /* 칼럼의 최대값 구하기 */

SELECT MIN(칼럼) FROM 테이블명              /* 칼럼의 최소값 구하기 */

SELECT AVG(칼럼) FROM 테이블명              /* 칼럼의 평균값 구하기 */

GROUP BY문

SELECT 칼럼 FROM 테이블명 GROUP BY 칼럼   

SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1

SELECT 칼럼1, COUNT(*) FROM 테이블명 GROUP BY 칼럼1

SELECT 칼럼1, 칼럼2, MAX(칼럼3) FROM 테이블명 GROUP BY 칼럼1, 칼럼2

** GROUP BY를 지정한 경우 SELECT 다음에는 반드시 GROUP BY에서 지정한 칼럼 또는

  그룹 함수만이 올 수 있다

조건

SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1 HAVING SUM(칼럼2) < a

SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 ORDER BY 칼럼1 COMPUTE SUM(칼럼2)

** HAVING:        그룹 함수를 사용할 경우의 조건을 지정한다

  HAVING의 위치: GROUP BY의 뒤 ORDER BY의 앞에 지정한다

  COMPUTE:       각 그룹의 소계를 요약해서 보여준다

                 ORDER BY가 항상 선행해서 나와야 한다

조건절의 서브 쿼리

** SELECT 또는 INSERTY, UPDATE, DELETE 같은 문의 조건절에서 SELECT문을 또 사용하는 것이다

  SELECT문 안에 또 다른 SELECT문이 포함되어 있다고 중첩 SELECT문(NESTED SELECT)이라고 한다

** 데이타베이스에는 여러명이 엑세스하고 있기 때문에 쿼리를 여러개 나누어서 사용하면 데이타의 값이

  달라질수 있기때문에 트랜잭션 처리를 하지 않는다면 복수의 쿼리를 하나의 쿼리로 만들어 사용해야 한다

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼2 = (SELECT 칼럼2 FROM 테이블명 WHERE 조건)

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼1 FROM 테이블명 WHERE 조건)

** 서브 쿼리에서는 다른 테이블을 포함할 수 있다

  두개의 테이블에서 읽어오는 서브쿼리의 경우 서브 쿼리쪽에 데이타가 적은 테이블을 주 쿼리쪽에 데이타가

  많은 테이블을 지정해야 처리 속도가 빨라진다

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼2-1 FROM 테이블명2 WHERE 조건)

** FROM구에서 서브 쿼리를 사용할 수 있다

  사용시 반드시 별칭을 붙여야 하고 처리 속도가 빨라진다

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1 AND 조건2

SEELCT 칼럼1, 칼럼2 FROM (SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1) 별칭 WHERE 조건2

데이타 편집

추가

** NULL 값을 허용하지도 않고 디폴트 값도 지정되어 있지 않은 칼럼에 값을 지정하지 않은채

  INSERT를 수행하면 에러가 발생한다

** 수치값은 그대로 문자값은 ''(작은따옴표)로 마무리 한다

** SELECT INTO는 칼럼과 데이타는 복사하지만 칼럼에 설정된 프라이머리, 포린 키등등의 제약 조건은

  복사되지 않기 때문에 복사가 끝난후 새로 설정해 주어야 한다

INSERT INTO 테이블명 VALUES (값1, 값2, ...)  /* 모든 필드에 데이타를 넣을 때 */

INSERT INTO 테이블명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...)   /* 특정 칼럼에만 데이타를 넣을 때 */

INSERT INTO 테이블명 SELECT * FROM 테이블명2                      /* 이미 존재하는 테이블에 데이타 추가 */

INSERT INTO 테이블명(칼럼1, 칼럼2, ...) SELECT 칼럼1, 칼럼2, ...) FROM 테이블명2

SELECT * INTO 테이블명 FROM 테이블명2                             /* 새로 만든 테이블에 데이타 추가 */

SELECT 칼럼1, 칼럼2, ... 테이블명 FROM 테이블명2

갱신

UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2          /* 전체 데이타 갱신 */      

UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 WHERE 조건               /* 조건에 해당되는 데이타 갱신 */

삭제

DELETE FROM 테이블명                                              /* 전체 데이타 삭제 */

DELETE FROM 테이블명 WHERE 조건  /* 조건에 해당되는 데이타 삭제 */

오브젝트

** 데이타베이스는 아래 오브젝트들을 각각의 유저별로 관리를 하는데 Schema(스키마)는 각 유저별 소유 리스트이다

1. Table(테이블)

** CREATE일때 프라이머리 키를 설정하지 않는다면 (칼럼 int IDENTITY(1, 1) NOT NULL) 자동 칼럼을 만든다

  데이타들의 입력 순서와 중복된 데이타를 구별하기 위해서 반드시 필요하다

** 테이블 정보 SP_HELP 테이블명, 제약 조건은 SP_HELPCONSTRAINT 테이블명 을 사용한다

CREATE TABLE 데이타베이스이름.소유자이름.테이블이름 (칼럼 데이타형 제약, ...) /* 테이블 만들기 */

DROP TABLE 테이블명                                                           /* 테이블 삭제 */

ALTER TABLE 테이블명 ADD 칼럼 데이타형 제약, ...                              /* 칼럼 추가 */

ALTER TABLE 테이블명 DROP COLUMN 칼럼                                         /* 칼럼 삭제 */ 

** DROP COLUMN으로 다음 칼럼은 삭제를 할 수 없다

  - 복제된 칼럼 

  - 인덱스로 사용하는 칼럼

  - PRIMARY KEY, FOREGIN KEY, UNIQUE, CHECK등의 제약 조건이 지정된 칼럼

  - DEFAULT 키워드로 정의된 기본값과 연결되거나 기본 개체에 바인딩된 칼럼

  - 규칙에 바인딩된 칼럼

CREATE TABLE 테이블명 (칼럼 데이타형 DEFAULT 디폴트값, ...)                   /* 디폴트 지정 */

CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 UNIQUE, ...)             /* 유니크 설정 */

** UNIQUE란 지정한 칼럼에 같은 값이 들어가는것을 금지하는 제약으로 기본 키와 비슷하지만

  NULL 값을 하용하는것이 다르다

CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 NOT NULL, ...)           /* NOT NULL 설정 */

CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 PRIMARY KEY, ...)        /* 기본 키 설정 */

** 기본 키는 유니크와 NOT NULL이 조합된 제약으로 색인이 자동적으로 지정되고 데이타를 

  유일하게 만들어 준다

**  기본 키는 한 테이블에 한개의 칼럼만 가능하다

CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 FOREIGN KEY REFERENCES 부모테이블이름(부모칼럼), ...)        

CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 CHECK(조건), ...)        /* CHECK 설정 */

** CHECK는 조건을 임의로 정의할 수 있는 제약으로 설정되면 조건을 충족시키는 데이타만

  등록할 수 있고 SELECT의 WHERE구와 같은 조건을 지정한다

** CONSTRAINT와 제약 이름을 쓰지 않으면 데이타베이스가 알아서 이름을 붙이지만

  복잡한 이름이 되기 때문에 되도록이면 사용자가 지정하도록 한다

** CONSTRAINT는 칼럼과 데이타형을 모두 정의한 뒤에 맨 마지막에 설정할 수 있다

CREATE TABLE 테이블명 (칼럼1 데이타형,

      칼럼2 데이타형, ...

      CONSTRAINT 이름 PRIMARY KEY(칼럼1)

                      CONSTRAINT 이름 CHECK(칼럼2 < a) ...)        

ALTER TABLE 테이블명 ADD CONSTRAINT 이름 제약문                                /* 제약 추가 */

ALTER TABLE 테이블명 DROP CONSTRAINT 제약명                                    /* 제약 삭제 */

ALTER TABLE 테이블명 NOCHECK CONSTRAINT 제약명                                 /* 제약 효력 정지 */

ALTER TABLE 테이블명 CHECK CONSTRAINT 제약명                                   /* 제약 효력 유효 */

** 제약명은 테이블을 만들때 사용자가 지정한 파일 이름을 말한다

2. View(뷰)

** 자주 사용하는 SELECT문이 있을때 사용한다

  테이블에 존재하는 칼럼들중 특정 칼럼을 보이고 싶지 않을때 사용한다

  테이블간의 결합등으로 인해 복잡해진 SELECT문을 간단히 다루고 싶을때 사용한다

** 뷰를 만들때 COMPUTE, COMPUTE BY, SELECT INTO, ORDER BY는 사용할 수 없고

  #, ##으로 시작되는 임시 테이블도 뷰의 대상으로 사용할 수 없다

** 뷰의 내용을 보고 싶으면 SP_HELPTEXT 뷰명 을 사용한다

CREATE VIEW 뷰명 AS SELECT문                          /* 뷰 만들기 */

CREATE VIEW 뷰명 (별칭1, 별칭2, ...) AS SELECT문  /* 칼럼의 별칭 붙이기 */

CREATE VIEW 뷰명 AS (SELECT 칼럼1 AS 별칭1, 칼럼2 AS 별칭2, ...)

ALTER VIEW 뷰명 AS SELECT문                                       /* 뷰 수정 */

DROP VIEW 뷰명                                                    /* 뷰 삭제 */

CREATE VIEW 뷰명 WITH ENCRYPTION AS SELECT문                      /* 뷰 암호 */

** 한번 암호화된 뷰는 소스 코드를 볼 수 없으므로 뷰를 암호화하기전에

  뷰의 내용을 스크립트 파일로 저장하여 보관한다

INSERT INTO 뷰명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...)

UPDATE 뷰명 SET 칼럼=값 WHERE 조건

** 원래 테이블에 있는 반드시 값을 입력해야 하는 칼럼이 포함되어 있지 않거나

  원래 칼럼을 사용하지 않고 변형된 칼럼을 사용하는 뷰는 데이타를 추가하거나 

  갱신할 수 없다

** WHERE 조건을 지정한 뷰는 뷰를 만들었을때 WITH CHECK OPTION을 지정하지 않았다면

  조건에 맞지 않는 데이타를 추가할 수 있지만 뷰에서는 보이지 않는다

  또한 뷰를 통해서 가져온 조건을 만족하는 값도 뷰의 조건에 만족하지 않는 값으로도 

  갱신할 수 있다

CREATE VIEW 뷰명 AS SELECT문 WITH CHECK OPTION

** 뷰의 조건에 맞지 않는 INSERT나 UPDATE를 막을려면 WITH CHECK OPTION을 설정한다

3. Stored Procedure(저장 프로시저)

** 데이타베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다

  반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다

  어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다

  데이타베이스 로직을 수정시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다

** 저장 프로시저의 소스 코드를 보고 싶으면 SP_HELPTEXT 프로시저명 을 사용한다

CREATE PROC 프로시저명 AS SQL문 /* 저장 프로시저 */

CREATE PROC 프로시저명 변수선언 AS SQL문 /* 인수를 가지는 저장 프로시저 */

CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL문 /* 저장 프로시저 보안 설정 */

CREATE PROC 프로시저명                          /* RETURN 값을 가지는 저장 프로시저 */

   인수1 데이타형, ... 인수2 데이타형 OUTPUT

AS 

   SQL문

   RETURN 리턴값

DROP PROCEDURE 프로시저명1, 프로시저명2, ...    /* 저장 프로시저 삭제 */

명령어

BEGIN ... END /* 문장의 블록 지정 */

DECLARE @변수명 데이타형 /* 변수 선언 */

SET @변수명=값 /* 변수에 값 지정 */

PRINT @변수명 /* 한개의 변수 출력 */

SELECT @변수1, @변수2 /* 여러개의 변수 출력 */

IF 조건 /* 조건 수행 */

   수행1 

ELSE 

   수행2                

WHILE 조건1 /* 반복 수행 */

   BEGIN 

       IF 조건2

   BREAK    - WHILE 루프를 빠져 나간다 

   CONTINUE - 수행을 처리하지 않고 조건1로 되돌아간다 

수행

   END                

EXEC 저장프로시저 /* SQL문을 실행 */

EXEC @(변수로 지정된 SQL문) 

GO /* BATCH를 구분 지정 */

에제

1. 기본 저장 프로시저

CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2

2. 인수를 가지는 저장 프로시저

CREATE PROC pUpdateSalary 

   @mul float=2, @mul2 int 

AS 

   UPDATE Employee SET salary=salary* @Mul* @mul2

EXEC pUpdateSalary 0.5, 2       /* 모든 변수에 값을 대입 */ 

EXEC pUpdateSalary @mul2=2      /* 원하는 변수에만 값을 대입 */

3. 리턴값을 가지는 저장 프로시저

CREATE PROC pToday 

   @Today varchar(4) OUTPUT

AS 

   SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))

   RETURN @Today

DECLARE @answer varchar(4)

EXEC pToday @answer OUTPUT

SELECT @answer AS 오늘날짜

4. 변수 선언과 대입, 출력

** @는 사용자 변수이고 @@는 시스템에서 사용하는 변수이다

DECLARE @EmpNum int

SET @EmpNum=10

SELECT @EmpNum

4. Trigger(트리거)

** 한 테이블의 데이타가 편집(INSERT/UPDATE/DELETE)된 경우에 자동으로 다른 테이블의

  데이타를 삽입, 수정, 삭제한다

** 트리거 내용을 보고 싶으면 SP_HELPTRIGGER 트리거명 을 사용한다

CREATE TRIGGER 트리거명 ON 테이블명 FOR INSERT AS SQL문         /* INSERT 작업이 수행될때 */

CREATE TRIGGER 트리거명 ON 테이블명 AFTER UPDATE AS SQL문       /* UPDATE 작업이 수행되고 난 후 */

CREATE TRIGGER 트리거명 ON 테이블명 INSTEAD OF DELETE AS SQL문  

DROP TRIGGER 트리거명

5. Cursor(커서)

** SELECT로 가져온 결과들을 하나씩 읽어들여 처리하고 싶을때 사용한다

** 커서의 사용방법은 OPEN, FETCH, CLOSE, DEALLOCATE등 4단계를 거친다

** FETCH에는 NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n / @nvar}, RELATIVE {n / @nvar}가 있다

SET NOCOUNT ON  /* SQL문의 영향을 받은 행수를 나타내는 메시지를 숨긴다 */

DECLARE cStatus SCROLL CURSOR  /* 앞뒤로 움직이는 커서 선언 */

FOR

   SELECT ID, Year, City FROM aPlane

FOR READ ONLY

OPEN cStatus  /* 커서를 연다 */

DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)

FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */

WHILE @@FETCH_STATUS=0                    /* 커서가 가르키는 결과의 끝까지 */

BEGIN

   IF      @Year <= 5              SET @Status='A'

   ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'

   ELSE                            SET @Status='C'

   INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)

   FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */

END

CLOSE cStaus                              /* 커서를 닫는다 */

DEALLOCATE cStatus                        /* 커서를 해제한다 */

보안과 사용자 권한

** 보안의 설정 방법은 크게 WINDOWS 보안과 SQL 보안으로 나뉘어 진다

** 사용자에게 역할을 부여하는데는 서버롤 데이타베이스롤이 있다

1. SA(System Administrator)

** 가장 상위의 권한으로 SQL 서버에 관한 전체 권한을 가지고 모든 오브젝트를 만들거나 

  수정, 삭제할 수 있다

2. DBO(Database Owner)

** 해당 데이타베이스에 관한 모든 권한을 가지며 SA로 로그인해서 데이타베이스에서 테이블을

  만들어도 사용자는 DBO로 매핑된다

** 테이블이름의 구조는 서버이름.데이타베이스이름.DBO.테이블이름이다

3. DBOO(Database Object Owner)

** 테이블, 인덱스, 뷰, 트리거, 함수, 스토어드 프로시저등의 오브젝트를 만드는 권한을 가지며

  SA나 DBO가 권한을 부여한다

4. USER(일반 사용자)

** DBO나 DBOO가 해당 오브젝트에 대한 사용 권한을 부여한다

MySql

** COMMAND LINE에서의 실행을 기준으로 한다

시작하기, 끝내기   

MYSQL -u 사용자명 -p /* 관리자 계정은 디폴트로 ROOT */

MYSQL -u 사용자명 -p 데이타베이스명 /* 시작할때 데이타베이스를 선택한다 */

MYSQL -u 사용자명 데이타베이스명 /* 암호가 지정되어 있지 않을 경우 */

USE 데이타베이스명 /* 데이타베이스 선택 */

SELECT VERSION(), CURRENT_DATE;                                 /* 버전과 오늘 날짜 */

SELECT USER();                                                  /* 사용자 */

STATUS /* 상태 */ 

QUIT                                                            /* 끝내기 */

패스워드 설정 및 변경

USE MYSQL /* MYSQL 데이타베이스를 선택 */

UPDATE USER SET PASSSWORD=PASSWORD('패스워드') WHERE USER='사용자명';

FLUSH PRIVILEGES; /* 변경 패스워드를 적용 */

데이타베이스 생성 및 선택

SHOW DATABASES; /* 데이타베이스 리스트 */

CREATE DATABASE 데이타베이스명; /* 새 데이타베이스 만들기 */

ALTER DATABASE 데이타베이스명 DEFAULT CHARACTER SET 문자셋;     /* 문자셋 지정. 4.1버전 이상 */

DROP DATABASE 데이타베이스명; /* 데이타베이스 삭제 */

USE 데이타베이스명; /* 데이타베이스 선택 */

사용자 생성 및 권한

** ROOT 관리자 계정으로 접속한다

1. 

USE MYSQL /* MYSQL 데이타베이스 선택 */

5.0 버전 이전

INSERT INTO USER (HOST, USER, PASSWORD) VALUES('localhost', '사용자명', PASSWORD('패스워드'));

5.0 버전 이후

INSERT INTO USER /* USER 테이블에 사용자 등록 */

(HOST, USER, PASSWORD, ...

VALUES('localhost', '사용자명', PASSWORD('패스워드'), 

       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 

'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 

'', '', '', '', 0, 0, 0, 0);

** USER 테이블의 HOST, USER, PASSWORD 이외의 나머지 칼럼 갯수만큼 처리

  위의 조건은 관리자에 해당하므로 권한에 제약을 두고 싶다면 각 칼럼별 'Y', 'N'를 

  구분해 주어야 한다

FLUSH PRIVILEGES; /* 사용자 등록 적용 */

2. 

GRANT ALL ON *.*                                        /* 모든 데이타베이스에 접근 허용 */

TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드'  

GRANT ALL ON 데이타베이스명.*                           /* 패스워드없이 지정한 데이타베이스에 접근 허용 */

TO 사용자명@LOCALHOST   

GRANT ALL ON 데이타베이스명.*                           /* 지정한 데이타베이스에 접근 허용 */

TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드'  

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON 테이타베이스명.*   /* LOCALHOST의 데이타베이스에 접근 허용 */

TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드'; 

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON 테이타베이스명.*   /* LOCALHOST가 아닌 다른 곳에서의 접속 허용 */

TO 사용자명@'%' IDENTIFIED BY '패스워드';       

SELECT HOST, USER, PASSWORD FROM USER;                          /* 사용자 리스트 */

DELETE FROM USER WHERE USER='사용자명' AND HOST= '호스트명';    /* 사용자 삭제 */

SHOW GRANTS FOR 사용자명@localhost;                             /* 사용자의 권한 상태 */

SHOW GRANTS FOR CURRENT_USER;                                   /* 현재 사용자의 권한 상태 */

테이블

SHOW TABLES; /* 테이블 리스트 */

CREATE TABLE 테이블명 (칼럼 데이타형 NOT NULL AUTO_INCREMENT);  /* 자동 증가값 만들기 */

CREATE TABLE 테이블명 (칼럼 데이타형 제약, ...                  /* 기본 키 설정 */

                      PRIMARY KEY (칼럼));   

CREATE TABLE 테이블명 (칼럼 데이타형 제약, ...                  /* 외래 키 설정 */

      FOREIGN KEY(칼럼) REFERENCES 부모테이블명 (부모칼럼));

ALTER TABLE 테이블명 RENAME 새테이블명;                         /* 테이블명 변경 */

ALTER TABLE 테이블명 MODIFY 칼럼 데이타형 제약;                 /* 칼럼의 형식 변경 */

ALTER TABLE 테이블명 CHANGE 칼럼 새칼럼 데이타형;               /* 칼럼명과 형식 변경 */

ALTER TABLE 테이블명 ADD 칼럼 데이타형 제약;                    /* 칼럼 추가 */

ALTER TABLE 테이블명 ADD INDEX (칼럼); /* 인덱스 칼럼 만들기 */

ALTER TABLE 테이블명 DROP COLUMN 칼럼;                          /* 칼럼 삭제 */

LOAD DATA INFILE 'TXT파일' INTO TABLE EXPORT FIELDS TERMINATED BY ' ';  /* TXT파일을 DB에 읽어오기 */

SELECT * FROM EXPORT INTO OUTFILE 'txt파일' FILEDS TERMINATED BY ' ';   /* DB를 TXT파일로 내보내기 */

** FIELDS TERMINATED BY는 '\t', ',', ';'등 원하는 구분자를 지정하면 된다

DROP TABLE 테이블명;                                            /* 테이블 삭제 */

DESC 테이블명; /* 테이블 구조 */

인덱스

** 적절한 인덱스 사용은 SELECT문의 성능을 크게 향상 시킨다

  최대 16개까지 지정할 수 있다

  최대 인덱스는 256바이트까지 가능하다

  CAHR, VARCHART에 대해서 앞 문자열로 인덱스 할 수 있다

  인덱스는 테이블에서 MUL 로 표시된다. 

CREATE TABLE 테이블명 (칼럼 데이타형 제약, ...                  /* 인덱스 만들기 */

      INDEX 인덱스명(칼럼));

** INDEX (칼럼), KEY 인덱스명 (칼럼)으로 해도 된다

SHOW KEYS FROM 테이블명; /* 인덱스 리스트 */

Create Or Replace Procedure p_logiN(

inUserid In member.id%type,

inPwd In member.puw%type)

IS

 v_pwd member.pwd%type;

Begin

 Select pwd Into v_pwd

 From member

 Where id=inUserid;

 ifv_pwd<>inPwd then

DBMS_Out.put_line('패스위드 오류');

 else

DBMS_Out.put_line('정상적으로 로그인 됨');

 end if

Exception

 When No_Date_Found Then

DBMS_Out.put_line('유저아이디'||inUserid||'는 존재하지 않습니다.');

  DBMS_Out.put_line(v_last_name||' '||v_sal);

SQL

SQL문

SELECT

DML(데이터 조작어)

INSERT, UPDATE, DELETE

DDL(데이터 정의어) IMPLICIT COMMIT

CREATE, ALTER, DROP, RENAME, TRUNCATE

TCL(트랜잭션 제어)

COMMIT, ROLLBACK, SAVEPOINT

DCL(데이터 제어어)IMPLICIT COMMIT

GRANT, REVOKE

[1] Writing Basic SQL Statements 

1. SELECT 기본 문장( 선택, 프로잭션, 조인)

   SELECT [DISTINCT] { *, column [alias], ... }

   FROM table ;

2. SELECT 예제

   SELECT * FROM dept ;

   SELECT deptno, loc FROM dept ;

   SELECT ename, sal, 12 * (sal + 100) FROM emp ;

   * Null 값과 연산을 하면 Null이 나온다.

3. Column Alias 예제

   SELECT ename AS nme, sal salary

   FROM emp ;

   SELECT ename "Name", sal*12 "Annual Salary"

   FROM emp ;

   * 대소문자를 구분하고 공백있는 컬럼 Alias를 만들고 싶을땐 " "로 막는다.

   * AS는 안 써도 된다.

   * WHERE, GROUP BY절에는 안된다.  ORDER BY 절에는 사용 가능.

4. Concatenation 연산자 (|| : pipeline 2개)

   SELECT ename||job "Employees" FROM emp ;

   --> ename 데이터와 job 데이터가 붙어서 출력된다.

5. 문자열을 데이터로 출력할때

   SELECT ename||' '||'is a'||' '||job "Employee Details"

   FROM emp ;

   * 문자열은 ' '로 막고, 컬럼 Alias는 " "로 막는다.

6. DISTINCT keyword : 중복된 Row를 하나로 만들어 준다. 자동 SORTING[ASC] 

   SELECT DISTINCT deptno

   FROM emp ;

   * DISTINCT 대신 UNIQUE를 써도 된다.

7. SQL*Plus Log On 방법

   * UserName, PassWord, HostString에 일일이 입력해도 되지만,

     UserName에 username/password@HostString이라고 입력하면 된다.

   * UNIX상에서 command로 들어 갈 때는 sqlplus username/password 만 입력하면된다.

8. 테이블 구조보는 SQL Command (DESC)

   SQL> DESC dept : Column Name, Null?, Data Type  display 

9. SQL*Plus Editing Commands( 다음 행까지 계속하려면 -(하이픈)으로 연결한다.)

   ① A[PPEND] text      : 현재 line의 마지막 문장 뒤에 text를 붙인다. 

   ② C[HANGE]/old/new  : 현재 line의 old text를 new text로 바꾼다.

   ③ C[HANGE]/text/     : 현재 line을 text를 삭제한다. 

   ④ CL[EAR] BUFF[ER] : buffer의 내용을 모두 지운다.

   ⑤ DEL                : 현재 line을 지운다.

   ⑥ DEL n              : n번째 line을 지운다.

   ⑦ DEL m n           : m ~ n번째 line을 지운다.

   ⑧ I[NPUT]            : 현재 line 다음에 line이 제한없이 추가된다.

   ⑨ I[NPUT] text       : 현재 line 다음에 line이 추가되면서 text가 들어간다.

   ⑩ L[IST]             : buffer전체를 보여준다.

   ⑪ L[IST] n           : n번째 line을 보여준다.

   ⑫ R[UN] or /        : SQL, PL/SQL문장을 실행하라!

   ⑬ n                  : n번째 line을 display하면서 Editing 상태로 해준다.

   ⑭ n text             : n번째 line이 text로 바뀐다.

   ⑮ 0 text             : 1번째 line이 추가되면서 text가 1번째 line으로 들어간다.

* Bald로 표시된 명령어는 line번호를 먼저 수행한 후 실행해야 한다.

10. SQL*Plus File Commands

   ① SAV[E] filename [REP[LACE]|APP[END]] : buffer의 내용을 filename.sql로 저장한다.

   ② GET filename    : filename.sql을 buffer로 불러온다.

   ③ START filename  : filename.sql을 실행하라.

   ④ @filename      : START filename과 같다.

   ⑤ ED[IT]          : buffer의 내용을 edit program으로 실행한다.

   ⑥ ED[IT] filename : filename.sql을 edit program으로 실행한다.

   ⑦ SPO[OL] filename : retrieve data를 filename.lst로 저장한다.

   ⑧ SPOOL OFF     : SPOOL을 끝내라.

   ⑨ SPOOL OUT     : retrieve data를 system printer로 출력하라.

   ⑩ EXIT            : SQL*Plus를 종료한다.

  * SPOOL 사용법

    SQL> spool filename

    SQL> select ...

    SQL> spool off

11. Special Tip

     * 잠시 host상태로 나가고 싶을 때.

        SQL> ! ( $)

        -- host 상에서 다시 SQL로 들어가려면 exit(lo)

        -- unix 상에서 env를 치면 오라클 환경을 볼 수 있다.

     * SQL> define -editor

        --> Editor가 vi인지..다른 edit프로그램인지를 보여준다.

     * line size 바꾸기

       SQL> SET PAGESIZE 20 -- 한 page를 20line으로 보여준다.

       -- log off하면 사라진다.

     * NLS값 보기

       SQL> select * from V$NLS_PARAMETERS

     * NLS값 바꾸기

       SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'

       -- SESSION : 현 session동안만 YYYY-MM-DD 포맷으로 사용한다는 뜻.

            sqlplus가 종료되면 원상태로 복구된다.

[2]Restricting and Sorting Data 

1. 비교연산자

   =   : Equal to

   >   : Grater than

   >=  : Greater than or equal to

   <   : Less than

   <=  : Less than or equal to

   <>  : Not equal to

   예) SELECT ename, sal, comm

       FROM emp

       WHERE sal <= comm ;

2. 비교연산자 2

   BETWEEN A AND B ,   IN(list),   LIKE,   IS NULL

3. BETWEEN 연산자( NOT BETWEEN )

   SELECT ename, sal

     FROM emp

    WHERE sal BETWEEN 1000 AND 1500 ; --> sal >= 1000 and sal <= 1500

4. IN 연산자( NOT IN )

   SELECT emp, ename, sal, mgr

     FROM emp

    WHERE mgr IN (7902, 7566, 7788) ; --> mgr = 7902 or mgr = 7566 or mgr = 7788

5. LIKE 연산자( NOT LIKE )

   예1) SELECT ename

          FROM emp

         WHERE ename LIKE 'S%' ;  --> ename이 S로 시작하는 모든 데이터를 찾는다.   

   예2) SELECT ename

          FROM emp

         WHERE ename LIKE '_A%' ;  --> 두 번째 글자가 A인 모든 데이터를 찾는다.

   예3) SELECT ename

          FROM emp

         WHERE ename LIKE '%A/_%B' ESCAPE '/' ;

        --> '/'는 Escape문자로 정의되었기 때문에 '_'도 문자로 인식한다.

            즉, ename이 A_로 포함하는 모든 데이터를 찾는다.

        * ESCAPE는 모든 문자가 가능하다.

6. IS NULL 연산자

   SELECT ename, mgr

     FROM emp

    WHERE mgr IS NULL ;   --> mgr이 null인 데이터를 찾는다.

7. Logical 연산자

   AND : 두 개의 조건이 모두 만족해야 OK

   OR  : 한 개의 조건만 만족하면 OK

   NOT

8. AND 연산자

   SELECT empno, ename, job, sal

     FROM emp

    WHERE sal >= 1100

      AND job = 'CLERK' ;

9. OR 연산자

   SELECT empno, ename, job, sal

     FROM emp

    WHERE (sal >= 1100 OR job = 'CLERK') ;

10. NOT 연산자

   SELECT ename, job

     FROM emp

    WHERE job NOT IN ('CLERK', 'MANAGER') ;

   --> NOT (job = 'CLERK OR job = 'MANAGER')

11. 연산자 우선순위( 산술 > 문자 > 비교 > 논리 )

   1 : 모든 비교 연산자

   2 : NOT

   3 : AND  --> False > Null > True

   4 : OR   --> True > Null > False

   예) A AND B에서...

       - A가 False이고 B가 Null이면... False 이다.

       - A가 True이고 B가 Null이면.... Null이다.

12. SORT (ORDER BY)

    * ASC는 default값이다.(작은 값부터..)

    * 값이 Null일 때 가장 큰 값이 된다. (ASC일 때...맨 뒤에 붙는다.)

    * column alias도 sorting이 된다.

    예1) SELECT ename, job, deptno, hiredate  "Date"

           FROM emp

         ORDER BY hiredate["Date" or  4 ] ;

    예2) SELECT ename, job, deptno, hiredate

           FROM emp

         ORDER BY hiredate DESC ;

    예3) SELECT empno, ename, sal*12 annsal

           FROM emp

         ORDER BY empno, annsal ; * select 절에 없는 열을 기준으로 정렬 가능.

    예4) SELECT ename, deptno, sal

           FROM emp

         ORDER BY deptno, sal DESC 

[3] Single-Row Functions

 1. Character Functions

    ①LOWER( column|expression )

  LOWER('String') --> string : 소문자로 변환

    ②UPPER( column|expression )

  UPPER('String') --> STRING : 대문자로 변환

    ③INITCAP( column|expression ) 

  INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환

    ④CONCAT( column1|expression1 ,column2|expression2 )

  CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.

    ⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)

  SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.

    ⑥LENGTH( column|expression )

  LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.

    ⑦INSTR( column|expression, )

  INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.

    ⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이

  LPAD('String',10,'*') --> ****String

       : 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)

    ⑨ RPAD('String',10,'*') --> String****

       : 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)

    ⑩ LTRIM('  String') --> 'String' : 문자열의 왼쪽 공백을 버린다.

    ⑪ RTRIM('String  ') --> 'String' : 문자령의 오른쪽 공백을 버린다.

    * TRIM(leading/tailing/both,  trim_character FROM trim_source )

 TRIM( 'S' FROM 'SSMITH') --> MITH

2. Number Functions

   ① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.

   ② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.

   ③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.

   * ROUND예제(WHOLE NUMBER:정수)

     SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL

     ==> 45.92     46     50

   * TRUNC예제

     SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL

     ==> 45.92     45(n이 생략된면 일의 자리까지 남기고 버린다.)  40

   * SYSTEM 날짜를 갖고 오는 방법.

     SELECT sysdate FROM dual

3. Date 계산( 날짜를 숫자로 저장)

   date + number : date에 number만큼 후의 날자를 보여준다.

   date - number : date에 number만큼 전의 날자를 보여준다.

   date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )

   date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.   

4. Date Functions

   MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194

     ; 두날짜 사이의 달수를 보여준다.

   ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94

     ; 날짜에 6개월을 더한 날자를 보여준다.

   NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'

     ; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.

       ('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)

   LAST_DAY('01-SEP-95') --> '30-SEP-95'

     ; 해당월의 마지막날자를 보여준다.

   ROUND('25-JUL-95','MONTH')--> 01-AUG-95  ROUND('25-JUL-95','YEAR')--> 01-JAN-96

   TRUNC('25-JUL-95','MONTH') --> 01-JUL-95  TRUNC('25-JUL-95','YEAR')  --> 01-JAN-95

5. Conversion Functions

   nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호

   TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.

     - Date Format Elements

       YYYY  --> 1999 (년출력) ,  YEAR  --> nineteen ninety-nine (년출력)

       MM    --> 12 (월출력) ,  MONTH --> DECEMBER (월출력),  MON --> DEC

       D     --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)

       DD    --> 07 (달의 일출력)

       DDD    --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.

       DAY   --> MONDAY (요일출력)  DY-->MON

       CC    --> 20 (몇 세기인지를 보여준다.)

       WW    --> 그 해의 몇 번째 주인가를 리턴한다.

       W     --> 그 달의 몇 번째 주인가를 리턴한다.

     * Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.

      HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)

     * 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER

     *숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)

        ddspth : 14-> fothteenth

     * /  .  , : 구두점은  결과에  그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.

    TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.

     - Number Format Elements

       9 : 999,999 --> 1,234   $: 부동 달러 기호 $99999 -> $1234

       0 : 099999  --> 001234  99.999EEEE -> 1.234E+03   B: 0값을 공백으로

       L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)

    TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.

    TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환      한다.

6. NVL Funcion : 값이 null일 때 설정값을 보여준다.

   NVL(number_column, 0)          : null일 때 0을 보여준다.

   NVL(date_column, '01-JAN-95')  : null일 때 '01-JAN-95'를 보여준다.

   NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.

   * column Type과 표현식의 type이 반드시 일치해야 한다.

7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.

   *DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])  

             F1 (F2 (F3 (col,arg1),arg2),arg3)

[4] Displaying Data from Multiple Tables  (JOIN)

1. EquiJoin : column1과 column2는 Primary Key와 Foreign Key관계인 경우

    SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc

    FROM emp, dept

    WHERE emp.deptno = dept.deptno

2. Non-EquiJoin : Join하는 Table 사이에 서로 대응하는 Key가 없는 경우

                   where절의 Join조건에 '='을 제외한 비교연산자를 사용한다.

    SELECT e.ename, e.sal, s.grade

    FROM emp e, salgrade s

    WHERE e.sal BETWEEN s.losal AND s.hisal

3. Outer Join : 서로 = 되지 않는 row 까지도 모두 보여준다.

                정보가 없는쪽 컬럼 뒤에 (+)를 붙인다.( =, and 만 사용가능)

    SELECT e.ename, d.deptno, d.dname

    FROM emp e, dept d

    WHERE e.deptno (+) = d.deptno

    ORDER BY e.deptno

4. Self Join : 같은 Table을 그것이 마치 2개의 Table인 것처럼 Join해서 사용한다.

    SELECT worker.ename, manager.ename

    FROM emp worker, emp manager

    WHERE worker.mgr = manager.empno

5. SET OPERATORS

UNION: 중복된 row는 제외하고 보여준다.    UNION ALL : 중복된 row까지 모두 보여준다.  INTERSECT :A,B의 중복된 row만 보여준다.MINUS : A,B의 중복된 row를 제외한 A row를 보여준다.

[5] Aggregating Data Using Group Functions(그룹함수를 사용한 데이터집계)

1. Group Function : 행집합에 적용하여 그룹당 하나의 결과를 생성한다.

   AVG([DISTINCT|ALL] n)           : 평균값  

   COUNT({*|[DISTINCT|ALL] expr})  : row수

   MAX([DISTINCT|ALL] expr)        : 최대값

   MIN([DISTINCT|ALL] expr)        : 최소값

   SUM([DISTINCT|ALL] n)           : 합

   STDDEV([DISTINCT|ALL] x)        : 표준편차

   VARIANCE([DISTINCT|ALL] x)      : 분산

   * count(*)를 제외한 모든 Group Function은 Null을 배제하고 수행한다.

     Null을 포함하고 싶다면 NVL함수를 사용한다.

   * DISTINCT나 ALL을 쓰지 않으면 Default가 ALL이다.

   * AVG,SUM, STDDEV, VARIANCE는 반드시 숫자형이다.

2. 어떤 컬럼에 해당하는 데이터별 그룹함수를 사용할 때

   SELECT [deptno,] COUNT(ename)

   FROM emp  --> 이문장은 성립되지 않는다. GROUP BY가 없다.

   <추가>

   GROUP BY deptno

   *일반칼럼과 그룹함수를 같이 쓰면  group by절에 일반칼럼 명시(열 별칭 사용못함)

   *GROUP BY 열을 SELECT 절에 포함시키지 않아도 된다   

3. 그룹함수는 WHERE절에 올수가 없다.

   SELECT deptno, AVG(sal)

   FROM emp

   WHERE AVG(sal) > 2000

   GROUP BY deptno

   --> 이문장은 성립되지 않는다. WHERE절에 그룹함수가 올 수 없다.

   SELECT deptno, AVG(sal)

   FROM emp

   GROUP BY deptno

   HAVING AVG(sal) > 2000

4. HAVING : 그룹함수를 조건절에 적용시키기 위해서 사용한다.

   SELECT job, SUM(sal)

   FROM emp

   WHERE job NOT LIKE 'SALES%'

   GROUP BY job

   HAVING SUM(sal) > 5000

   ORDER BY SUM(sal) 

   *절 평가 순서 : ① WHERE  -> ② GROUP BY절    -> ③ HAVING절

   *그룹함수는 두번까지 중첩될수 있습니다.  MAX( AVG(SAL))

[6] Subqueries( WHERE 절, HAVING 절, FROM 절 )

1. Subquery 규칙

   - 반드시 ()로 묶어야 한다.

   - 반드시 비교연산자 오른쪽에 위치해야 한다.

   - ORDER BY 절에는 사용할 수 없다.

   - 서브쿼리 결과가 한개일때는 Single-Row 비교연산자를 사용해야 한다.

     ( =, >, >=, <, <=, <> )

   - 서브쿼리 결과가 여러개일때는 Multi-Row 연산자를 사용해햐 한다.

2. Subquery 사용예1

   * 반드시 비교연산자 오른쪽에 써야한다.

   SELECT ename FROM emp

   WHERE sal > (SELECT sal FROM emp

                              WHERE empno = 7566)

3. Subquery 사용예2

   SELECT ename, job

   FROM emp

   WHERE job = (SELECT job

                FROM emp

                WHERE empno = 7369)

   AND

         sal > (SELECT sal

                FROM emp

                WHERE empno = 7876)

4. Subquery 사용예3

   SELECT  job, AVG(sal)

   FROM emp

   GROUP BY job

   HAVING AVG(sal) > (SELECT MIN(AVG(sal))

                      FROM emp

                      GROUP BY job)

5. Subquery 사용예4 (Subquery 결과가 여러개가 나올때 비교연산자 사용법)

   SELECT empno, ename

   FROM emp

   WHERE sal IN (SELECT MIN(sal)   ( =ANY 와 같음 )

                 FROM emp

                 GROUP BY deptno)

6. ANY(동의어:SOME)연산자를 사용한 Subquery : 조건중에 한개만 만족하면 OK

   SELECT empno, ename, job

   FROM emp

   WHERE sal < ANY (SELECT sal   --> OR

                    FROM emp

                    WHERE job = 'CLERK')

   AND job <> 'CLERK'

7. ALL연산자를 사용한 Subquery : 모든 조건을 만족해야 OK

   SELECT empno, ename, job

   FROM emp

   WHERE sal > ALL (SELECT avg(sal)  --> AND

               FROM emp

               GROUP BY deptno 

[7] Multiple-Column Subqueries

1. Multiple-Column Subquery (Pairwise Subquery)

   : 서브쿼리의 결과가 두개 이상의 컬럼형식으로 나올 때 비교하는 컬럼도 서브쿼리 컬럼

     갯수와 형식이 같아야한다.

   SELECT ename, deptno, sal, comm

   FROM emp

   WHERE (sal, NVL(comm,-1)) IN

                             (SELECT sal, NVL(comm,-1)

                              FROM emp

                              WHERE deptno = 30)

2. NonPairwise Subquery

   SELECT ename, deptno, sal, comm

   FROM emp

   WHERE sal IN (SELECT sal FROM emp

                 WHERE deptno = 30)

   AND

         NVL(comm,-1) IN (SELECT NVL(comm,-1) FROM emp

                          WHERE deptno = 30)

3. Subquery 안에 Null값이 있을 때...

   * 매니저가 아닌 사원을 보여주기?

   SELECT e.ename

   FROM emp e

   WHERE e.empno NOT IN

                 (SELECT m.mgr FROM emp m)                 

   IN은 Null value가 나와도 한개의 조건만 만족하면 OK이지만,

   NOT IN은 !=ALL과 같아서 모든조건이 TRUE여만 TRUE인 것이다.   

   * NOT IN 을 !=ANY 로 바꿔주는게 정답에 가깝다.

4. FROM절에 사용되는 Subquery

   SELECT a.ename, a.sal, a.deptno, b.salavg

   FROM emp a,

        (SELECT deptno, avg(sal) salavg

         FROM emp

         GROUP BY deptno) b

   WHERE a.deptno = b.deptno

     AND a.sal > b.salavg

5. EXISTS : EXISTS 뒤에 나오는 서브쿼리의 결과가 한건이 row라도 있다면 O.K

   SELECT dname, deptno

   FROM dept

   WHERE EXISTS (SELECT * FROM emp

                 WHERE dept.deptno = emp.deptno)

  : 행의 존재유무만 확인

[8] Producing Readable Output with SQL*Plus

1. Substitution Variable (치환변수)

   & : 변수가 한 번 사용되고 메모리에서 사라진다.

   && : 한 번 입력한 변수는 CLEAR하기 전까지 계속 메모리에 남아있다.

   DEFINE variable=value : CHAR 데이터 유형의 사용자 변수를 생성하여 값을 변수에 할당한다.

   DEFINE : 현재 DEFINE 된 모든 것들을 보여준다.

   DEFINE 변수명 : 지정한 변수명의 DEFINE 사항을 보여준다.

   UNDEFINE 변수명 : 지정한 변수명의 DEFINE 상태를 CLEAR한다.

   ACCEPT : 변수를 입력하라고 물어보는 Prompt의 Text를 변경할수 있다. USER가

   입력하는 값을 HIDE시킬수가 있다. DataType의 Format을 변경할수가 있다.

   * SET VERIFY 는 SQL*PLUS 가 치환변수를 값으로 바꾸기 전후의 명령 텍스트 표시를 토글한다.

2. & 치환변수 사용예

   SELECT empno, ename, sal, deptno

   FROM emp

   WHERE empno = &employee_num

3. && 치환변수 사용 예 --> 한번만 물어보게 된다.   

   SELECT empno, ename, job, &&column_name

   FROM emp

   ORDER BY &column_name

4. 변수 값이 character or date value일때 꼭 single quotation mark로 묶어 줘야 한다.

   SELECT ename, deptno, sal*12

   FROM emp

   WHERE job = '&job_title'

5. 종합적인 예제(실행중에 열이름, 표현식, 텍스트를 지정)

   --> 어떤 절에나 사용해도 된다.

   SELECT empno, ename, job, &column_name

   FROM emp

   WHERE &condition

   ORDER BY &order_column

6. ACCEPT 사용예 (file로 만들어서 START시켜야 한다.)

   *ACCEPT  variable [datatype] [FORMAT format] [PROMPT text] [HIDE] 

   ACCEPT dept PROMPT 'Provide the department name: '

   SELECT *

   FROM dept

   WHERE dname = UPPER('&dept')

   /    

   Provide the department name: Sales

   처리된 결과값...

7. 파일로 만들어서 치환변수를 여러개 처리할때 예제

   test.sql

   SELECT &1 FROM &2  

   SQL> @test empno emp

   ==> &1에 empno, &2에 emp가 들어간다.(define)

   SELECT &2 FROM &4   

   SQL> @test e empno b emp

   ==> &1에 e, &2에 empno,&3에 b, &4에 emp가 들어간다.(define)

8. SET command 변수

   ① ARRAY[SIZE] {20 | n} : 데이터 fatch size

   ② COLSEP {_ | text} : column seperator

   ③ FEED[BACK] {6 | n | OFF | ON} : n 개이상의 레코드 반환시 레코드 수를 표시

   ④ HEA[DING] {OFF | ON} : column heading 출력

   ⑤ LIN[ESIZE] {80 | n} : 가로 80 으로 출력

   ⑥ PAGES[IZE] {50 | n} : 세로 50 으로 출력

   ⑦ LONG {80 | n} : long date type의 데이터를 출력할때 80byte까지만 보여주겠다.

   ⑧ PAU[SE] {OFF | ON | text} : text에 문자열을 넣으면 pause될 때마다 메시지를   보여준다.

   ⑨ TERM[OUT] {OFF | ON} : 결과를 화면에 보여주겠는가?

   ⑩ VERIFY {OFF | ON} : 치환변수의 old값과 new값을 보여주면서 결과가 처리된다.

   * SHOW {SET command 명} : SET command명의 현재 상태를 보여준다.  SQL> SHOW ECHO)

   * SHOW ALL : 모든 SET command의 현재 상태를 보여준다.

   * SET 상태를 바꿔 주려면...   SQL> SET PAUSE ON  <-- 이런식으로 하면 된다.  

   * DEFINE command나 SET command는 자신의 환경파일(login.sql)에 일괄적으로 처리 할수 있다.

   * login.sql은 oracle superuser용 환경파일이다.

   * SQL Plus command가 다음 줄로 이어질 때는 (-)으로 연결한다.

   9. REPORT 출력 예

   ① SET PAGESIZE 37 --> 세로 37로 출력

   ② SET LINESIZE 60 --> 가로 60으로 출력

   ③ SET FEEDBACK OFF

   ④ TTITLE 'Employee|Report' --> Top Title을 Employee 다음 줄에 Report를 쓰겠다.

   ⑤ BTITLE 'Confidential'    --> Bottom Title을 Confidential로 쓰겠다.

   ⑥ COLUMN job HEADING 'Job|Category' FORMAT A15 : | 은 text를 두줄로 찍는다.  

      --> job column Heading을 Job 다음줄에 Category로쓰고 15byte의 자리수로

           만들겠다.

    형식 :  COL[UMN]  [{column | alias } [option]]   

     COL[UMN] [column],  COL[UMN] column  CLE[AR],  CLE[AR] COL[UMN] 

    옵션 :  CLE[AR], FOR[MAT] format, HEA[DING] text, JUS[TIFY] {align}, NUL[L] text,

            NOPRI[NT]:열을 숨김니다 <> PRI[NT],

            TRU[NCATED] : 첫 행 끝에 표시되는 문자열을 잘라버린다. 

            WRA[PPED]  : 문자열의 끝을 다음 행으로 줄바꿈합니다.

  ⑦ REM ** Insert SELECT statement  --> 주석문

   *BREAKE 명령?

[9] Multipulating Data (DML)

*트랜젝션 :  논리 작업 단위를 형성하는 DML 문 모음으로 구성된다., DDL문(한개),DCL문(한개)

1. INSERT 예제1 

   : 모든 컬럼에 INSERT할 때는 컬럼명을 쓰지 않아도 되지만 테이블 CREATE시 

     만들어진 순서대로 입력해야한다.

   INSERT INTO dept

   VALUES (50,'AAA','BBB')

   * NULL 값을 갖는 행 삽입시 열목록에서 열을 생략(암시적)하는 방법과

     NULL 키워드를 지정(명시적)하는 방법이 있다.

2. INSERT 예제2

   INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

   VALUES ( 7196, 'GREEN', 'SALESMAN', 7782, SYSDATE, 2000, NULL, 10 )

3. sql 파일로 만들어서 Argument를 받으면서 INSERT 실행하기.

   * test.sql 파일

   ACCEPT department_id PROMPT 'Please enter the department number: '

   ACCEPT department_name PROMPT 'Please enter the department name: '

   ACCEPT location PROMPT 'Please enter the location: '

   INSERT INTO dept ( deptno, dname, loc )

   VALUES (&department_id, '&department_name', '&location' )

   SQL> @test = START test

   Please enter the department number: 90

   Please enter the department name: PAYROLL

   Please enter the location: HOUSTON

   1 row created

4. 다른 테이블의 row를 Copy하기(VALUES 절을 사용하지 않는다.)

   INSERT INTO managers (id, name, salary, hiredate)

          SELECT empno, ename, sal, hiredate

          FROM emp

          WHERE job = 'MANAGER'

5. UPDATE 예제1

   UPDATE emp

   SET deptno = 20,

       sal = 2500,

       comm = null

   WHERE empno = 7782

6. UPDATE 예제2 (Multiple-Column Subquery를 사용한 예)

   UPDATE emp

   SET (job, deptno) = (SELECT job, deptno

                        FROM emp

                        WHERE empno = 7499)

   WHERE empno = 7689

7. UPDATE 예제3 (다른 테이블에 있는 데이터를 SELECT해서 UPDATE하기)

   UPDATE employee

   SET deptno = (SELECT deptno FROM emp WHERE empno = 7788)

   WHERE job = (SELECT job FROM emp WHERE empno = 7788)

8. DELETE 예제1 (조건에 맞는 데이터 지우기)

   DELETE FROM dapartment

   WHERE dname = 'DEVELOPMENT'

9. DELETE 예제2 (한 테이블의 전체 데이터 삭제)

   DELETE FROM department

10. DELETE 규칙

   * FROM은 옵션이므로 사용하지 않아도 된다.(예: DELETE department)

   * Primary Key, Forien Key 관계가 설정되어 있는 데이터를 DELETE할때는 문제가 발생 할 수 있다.

11. COMMIT

   * 변경된 데이터를 Fix시킨다.

   * 이전상태의 데이터는 모두 잃게 된다.

   * 모든 User가 결과를 볼수 있다.

   * LOCK이 풀린다.

   * 모든 SavePoint들이 clear된다.

   * 자동 커밋 : DDL, DCL, 정상 종료시

12. ROLLBACK

   * 변경된 데이터를 undo 시킨다.

   * Transaction 전단계의 데이터로 돌아간다.

   * Lock이 풀린다.

   * 자동 롤백 : 비정상 종료, 시스템 장애

13. SAVEPOINT 예제

   * SAVEPOINT : Transaction이 일어난 곳에 Marking을 할 수가 있다.

   * 오라클은 자동적으로 눈에 안보이는 savepoint를 찍어 놓는다.

   SQL> UPDATE.....

   SQL> SAVEPOINT update_done ;

   Savepoint created.

   SQL> INSERT.....

   SQL> ROLLBACK TO update_done ;

   Rollback complete.

14. TABLE LOCK 예제 (DBA가 임으로 TABLE에 LOCK을 걸 수가 있다.)

[10] Creating and Managing Tables (DDL)

1. Database Objects

   TABLE, VIEW, SEQUENCE, INDEX, SYNONYM

2. Object Naming Rule

   - 반드시 첫글자는 문자이어야 한다.

   - 길이는 1 ~ 30 글자 이다.

   - A-Z, a-z, 0-9, _, $, # 만을 사용할수 있다.

   - 동일한 Object명이 존재해서는 안된다.(다른 user가 소유한 table과는 중복가능)

   - 오라클 서버의 예약어는 사용할 수 없다.

3. CREATING TABLES( create 권한, 저장영역이 필요 )

   CREATE [GLOBAL TEMPORARY]  TABLE  [schema.]table

      ( column  datatype [DEFAULT expr] [,...] );

  *GLOBAL TEMPORARY : 임시 table로 지정, 정의는 모든 세션에서 볼 수 있지만,

                          데이터는 데이터를 삽입하는 세션에서만 볼 수 있다.

  * DEFAULT 값: 다른 열의 이름이나, 의사열은 잘못된 값이다.

  * CREATE TABLE dept

      ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13),

        dymd DATE DEFAULT SYSDATE )

4. Data Dictionary

   USER_ : 사용자가 소유하는 객체에 대한 정보

   ALL_   : 사용자가 ACCESS 할 수 있는 모든 TABLE 에 대한 정보

   DBA_  : DBA 롤을 할당 받은 사용자만 ACCESS 할 수 있다.

   V$_    : 동적 성능(Performance) 뷰, DB SERVER 성능 및 잠금에 관한 정보.

   * DICT 테이블 안에 모든 data dictionary 정보가 있다.

   >> 유저가 소유한 개별 객체 유형을 봄.

       SELECT * FROM USER_TABLES

   >> 유저가 소유한 TABLE, VIEW, 동의어, 시퀀스를 봄.

       SELECT DISTINCT object_type

       FROM USER_OBJECTS ( USER가 ACCESS 할 수 있는 ALL_OBJECTS )

   >> User가 소유한 TABLE을 설명.

      SELECT * FROM USER_CATALOG 

       --> SELECT * FROM cat 이라고 써도 된다.

   >> 모든 테이블 이름보기         SELECT * FROM TAB

   >> 컬럼 정보 보기       USER_TAB_COLUMNS

5. Data Types

   VARCHAR2(size) : Variable-length character data (1 ~ 4000 bytes)

   CHAR(size) : Fixed-length character data (1 ~ 2000 bytes)

   NUMBER(p,s) : p - precision, s - scale

   DATE : B.C.4712-01-01 ~ A.D.9999.12.31

   LONG : Variable-length character data (2GB)

         - 한 table 당 하나  - constraints 사용 못함          

         - order by, group by에서 사용못함  - 서브쿼리에서 사용할 때 not copy  

   LONG RAW : Raw binary data of variable length up to 2 gigabytes

   RAW(size)  : 2000 byte

   CLOB : Single-byte character data up to 4 gigabytes

   BLOB : Binary data up to 4 gigabytes

   BFILE : binary data stored in an external file; up to 4 gigabytes

6. Subquery를 사용해서 테이블 복사하기(새로운 table 의 datatype 정의는 필요없다)

   CREATE TABLE dept30

   AS

     SELECT empno, ename, sal*12 ANNSAL, hiredate

     FROM emp

     WHERE deptno = 30

   * computed field일때 반드시 컬럼 alias를 줘야 한다. (ANNSAL)

   * computed 컬럼으로 만들어진 컬럼 타입 NUMBER일 때 기존의 자릿수는 사라지고 

     최대자리수로 잡힌다.

   * column, type, data 모든것이 복사된다.(NOT NULL constraints 만 상속된다.)

   * key constraint는 정의되지 않는다.

   * WHERE조건을 false가 되게 만들면 데이터는 복사되지 않는다.

7. 테이블에 컬럼 추가 하기

   ALTER TABLE dept30

   ADD ( job VARCHAR2(9) )

   -> 열을 추가할 때 모든 행은 초기에 null 값을 가진다.( not null 제약조건 X )

8. 테이블에 있는 기존 컬럼 수정하기

   ALTER TABLE dept30

   MODIFY ( ename VARCHAR2(15) )

   * 제약사항.

     - 숫자타입에서 자릿수를 늘리는건 가능하다.

     - 숫자타입에서 자릿수를 줄일때는 모든 data가 null이거나 데이터가 한건도 없을  때만 가능하다.

     - char <--> varchar2 로 바꿀때는 모든 data가 null이거나 데이터가 한건도 없을 때만 가능하다.

     - default값도 바꿀 수 있다.(기존 default값은 유지된다.)

     - column name 은 modify 가 안된다.

=> 열을 추가(ADD), 수정(MODIFY)할 수 있지만 table에서 삭제할 수 없다.

<oracle 8i 가능>

 1.>   ALTER TABLE table SET UNUSED (column);

   OR

     ALTER TABLE table SET UNUSED COLUMN column;

 2.>ALTER TABLE table  DROP UNUSED COLUMNS;

9. DROP TABLE

   DROP TABLE dept30

10. OBJECT 이름 바꾸기

   RENAME dept TO department

11. TRUNCATE TABLE

   TRUNCATE TABLE department

   * ROLLBACK을 절대로 할 수 없다.

   * 로그없이 테이블의 전체 데이터가 삭제되고 COMMIT이 된다.

   * 저장공간을 해제( DELETE 문은 해제할 수 없다.)

12. 테이블에 주석문 추가하기

   COMMENT ON TABLE emp

   IS 'Employee Information'

13. 컬럼에 주석문 추가하기

   COMMENT ON TABLE emp COLUMN emp.ename

   IS 'Employee Name'

14. 주석문 삭제하기   

   COMMENT ON TABLE emp IS ' '

15. 주석문을 볼수있는 data dictionary

   * Column

     ALL_COL_COMMENTS

     USER_COL_COMMENTS   

   * Table

     ALL_TAB_COMMENTS

     USER_TAB_COMMENTS

[11] Including Constraints

1. 오라클의 Constraint Type

   NOT NULL : null value를 허용하지 않음.

   UNIQUE : 유일한 값을 가지면서 null도 허용한다.

   PRIMARY KEY : NOT NULL, UNIQUE, TABLE당 1개 설정, 참조 당함

   FOREIGN KEY : Primary에 연결되어 있어서 Primary에 있는 값들만 갖고 있다.(PK,UK 참조)

   CHECK : 정해진 값 이외의 것이 들어오면 Error

2. Constraint 규칙

   - Constraint을 생성할 때 이름을 주지 않으면 오라클서버는 SYS_Cn 이라고 이름을 자동 생성한다.

   - Column level 과 Table level로 Constraint를 정의한다.

   - constraint를 볼 수 있는 Data Dictionary는 

     USER_CONSTRAINTS,USER_CONS_COLUMNS 이다.

3. Constraint 정의

   * Column Level

     column [CONSTRAINT constraint_name] constraint_type,  

   * Table Level

     column,...

        [CONSTRAINT constraint_name] constraint_type (column, ...),

        (column, ...),

   * not null은 반드시 column level로 정의를 내려야 한다.

4. NOT NULL Constraint : column level

5. UNIQUE Constraint : index 자동 생성

   CREATE TABLE dept (

      deptno     NUMBER(2),

      dname      VARCHAR2(14),

      loc        VARCHAR2(13),

      CONSTRAINT dept_dname_uk UNIQUE (dname) )

6. PRIMARY KEY Constraint

   CREATE TABLE dept (

      deptno     NUMBER(2),

      dname      VARCHAR2(14),

      loc        VARCHAR2(13),

      CONSTRAINT dept_dname_uk UNIQUE (dname) ,

      CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno) )

7. FOREIGN KEY Constraint

   CREATE TABLE emp (

      empno      NUMBER(4),

      ename      VARCHAR2(10) [CONSTRAINT epm_ename_nn] NOT NULL,

      job        VARCHAR2(9),

      mgr        NUMBER(4),

      hiredate   DATE,

      sal        NUMBER(7,2),

      comm       NUMBER(7,2),

      deptno     NUMBER(7,2) NOT NULL,

      CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

                 REFERENCES dept (deptno) )

      --> dept.deptno를 emp.deptno가 Foreign key로 사용하겠다는 뜻.

   * column level로 정의할 때는...(FOREIGN KEY   키워드는 생략)

     deptno NUMBER(7,2) NOT NULL

            CONSTRAINT emp_deptno_fk REFERENCES dept (deptno),

   * ON DELETE CASCADE

     Foreign key로 연결된 parent data가 삭제될 때 child data도 모두 삭제 되게 만든다.

8. CHECK Constraint

   ..., deptno   NUMBER(2),

                 CONSTRAINT emp_deptno_ck

                     CHECK (deptno BETWEEN 10 AND 99), ...

        --> deptno의 값은 10에서 99 사이에 있는 값들만 입력이 가능하다는 뜻.

   * CURRVAL, NEXTVAL, LEVEL, ROWNUM등 Pseudocolumn엔 허용되지 않는다.

   * SYSDATE, UID, USER, USERENV 함수들을 호출할수 없다.

* 제약조건을 추가(ADD), 삭제(DROP)할 수 있지만 수정할 수는 없다.

* 제약조건을 설정(ENABLE) 또는 해제(DISABLE)합니다.

* MODIFT 절을 사용하여 NOT NULL 제약조건을 추가합니다.

9. ADD Constraint

*ALTER TABLE table_name 

        ADD [CONSTRAINT constraint] type (column),

   ALTER TABLE emp

   ADD CONSTRAINT emp_mgr_fk

       FOREIGN KEY(mgr) REFERENCES emp(empno)

   * Constraint 수정은 할 수 없다.

   * not null constraint일 경우 ADD로 하지 않고 MODIFY로 한다.

     (기존 행에 null data 값이 없어야 한다.)

10. DROP Constraint

*ALTER TABLE table_name 

         DROP (PRIMARY KEY| UNIQUE (column) | 

         CONSTRAINT  constraint) [CASCADE];

   ALTER TABLE emp

   DROP CONSTRAINT emp_mgr_fk

   * Primary를 삭제할 때 Foreign Key관계(종속된 제약조건)의 Constraint까지 DROP 하고 싶으면...

     ALTER TABLE dept

     DROP PRIMARY KEY CASCADE

* CASCADE CONSTRAINTS 절은 DROP COLUMN 절과 함께 사용됩니다.

 ALTER TABLE test1 DROP  (pk) CASCADE CONSTRAINTS;

11. DISABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)

    : ENABLE CONSTRAINT 하기 전까지 실행을 멈춘다.

    ALTER TABLE emp

    DISABLE CONSTRAINT emp_empno_pk CASCADE

12. ENABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)

    ALTER TABLE emp

    ENABLE CONSTRAINT emp_empno_pk

    * ENABLE 할 때는... 모든 data를 체크하기 때문에 시간이 오래 걸린다.

*

13. USER_CONSTRAINTS Data Dictionary

    SELECT constraint_name, constraint_type, search_condition

    FROM user_constraints

    WHERE table_name = 'EMP'

    --------------------------------------------------------

    CONSTRAINT_NAME             C  SEARCH_CONDITION

    ------------------------- -  --------------------------

    SYS_C00674                    C  EMPNO IS NOT NULL

    SYS_C00675                    C  DEPTNO IS NOT NULL

    EMP_EMPNO_PK                P     

    --------------------------------------------------------

14. USER_CONS_COLUMNS Data Dictionary

    SELECT constraint_name, column_name

    FROM user_cons_columns

    WHERE table_name = 'EMP'

    --------------------------------------------------------

    CONSTRAINT_NAME                   COLUMN_NAME

    EMP_DEPTNO_FK                     DEPTNO

    EMP_EMPNO_PK                      EMPNO

    EMP_MGR_FK                         MGR

    SYS_C00674                         EMPNO

    SYS_C00675                         DEPTNO

    --------------------------------------------------------

[12] Creating Views

* Oracle8부터는 View에도 Data를 갖고 있을 수 있다.

* View를 사용하는 이유?

  - 데이터베이스 access를 제한한다. 

  - 복잡한 쿼리를 쉽게 만든다.

  - 데이터의 독립을 허용하기 위해 

  - 동일한 데이터로부터 다양한 결과를 얻기 위해

* view를 만들 때 ORDER BY절을 사용할 수 없다.

* Simple View에는 DML 문장을 수행 할 수 있지만, 함수 또는 데이터 그룹을 포함하지 못한다.

  (복합뷰<Complex VIEW> 는 함수 또는 데이터 그룹을 포함할 수 있다.)

* GROUP함수, GROUP BY절, DISTINCT keyword등을 사용한 view에는 행을 delete 할 수 없다.

* 뷰를 사용한 데이터 엑세스

  - USER_VIEWS에서 뷰 정의를 검색한다.(select 문의 텍스트는 LONG 열에 저장된다.)

  - 뷰의 기본 테이블에 대한 액세스 권한을 확인한다.

  - 데이터를 기본 테이블에서 검색 또는 갱신한다.

1. CREATE VIEW 문장

   CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

       [ (alias[, alias]...) ]

   AS subquery

   [WITH CHECK OPTION [CONSTRAINT constraint]]

   [WITH READ ONLY]

   OR REPLACE : 만들려고 하는 view가 이미 존재할 때 recreate한다.

   FORCE : base table이 존재하지 않아도 view를 강제로 만든다.

   NOFORCE : base table이 반드시 존재해야만 view를 만들 수 있다.

   WITH CHECK OPTION : view를 만들 때 where조건에 있는 value를 

                           View를 통해서 DML 문장으로 수정하는 것을 막는다.

   WITH READ ONLY : view를 통해서 DML문장을 수행하는 것을 막는다.

2. Creating View 예제1

   CREATE VIEW empvu10

   AS SELECT empno, ename, job

   FROM emp

   WHERE deptno = 10 ;

   --> 이렇게 만들어진 view의 column이름은 empno, ename, job이 된다.

3. Creating View 예제2

   CREATE VIEW salvu30

   AS SELECT empno employee_number, ename name, sal salary

   FROM emp

   WHERE deptno = 30 ;

   --> column alias를 준 employee_number, name, salary가 view의 column이름이  된다.

4. VIEW 수정하기 (OR REPLACE)

   CREATE OR REPLACE VIEW empvu10

       (employee_number, employee_name, job_title)

   AS SELECT empno, ename, job

   FROM emp

   WHERE deptno = 10 ;

5. Complex VIEW

   CREATE VIEW detp_sum_vu

       (naem, minsal, maxsa, avgsal)

   AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

   FROM emp e, dept d

   WHERE e.deptno = d.deptno

   GROUP BY d.dname ;

6. WITH CHECK OPTION 사용예

   CREATE OR REPLACE VIEW empvu20

   AS SELECT *

   FROM emp

   WHERE deptno = 20

   WITH CHECK OPTION CONSTRAINT empvu20_ck ;(deptno 가 20만 작업 가능)

   ( constraint name을 주지 않으면 SYS_Cn으로 만들어 진다. )

   * 예를들어 다음과 같은 update문장을 수행하면 error가 난다.

   UPDATE empvu20

   SET deptno = 10

   WHERE empno = 7788 ;

   --> ORA-01402: view WITH CHECK OPTION where-clause violation

7. WITH READ ONLY 사용예

   CREATE OR REPLACE VIEW empvu10

       (employee_number, employee_name, job_title)

   AS SELECT empno, ename, job

   FROM emp

   WHERE deptno = 10

   WITH READ ONLY ;

   * 예를들어 다음과 같은 DELETE문장을 수행하면 error가 난다.

   DELETE FROM empvu10

   WHERE employee_number = 7782 ;

   --> ORA-01752: Cannot delete from view without exactly one key-preserved table

8. View 없애기

   DROP VIEW empvu10 ;

9. 만들어진 View를 볼때

   SELECT * FROM salvu30 ;

 * 인라인 view : FROM 절에 사용,  객체가 아님.

10. View를 통한 DML 작업 수행에 관한 규칙

 *뷰가 다음을 포함한 경우 행을 제거할 수 없슴.

  - 그룹함수   - GROUP BY절   - DISTINCT  키워드   - 의사열 ROWNUM 키워드

 *뷰가 다음을 포함한 경우 데이터를 수정할 수 없슴.

  - 위에서 언급된 모든 조건(제거할 수 없슴)

  - ROWNUM 의사 열 : 하위 질의에서 반환되는 가 행에 1에서 시작하는 순차값을 할당.

 *뷰가 다음과 같은 경우 데이터를 추가할 수 없슴.

  - 뷰가 위에서 언급한 포함하는 경우.(제거, 수정)

  - 기본 테이블에서 뷰에 의해 선택되지 않은 열에 NOT NULL 제약 조건이 있는 경우.

11. "TOP-N" 분석 수행

   SELECT [column_list], ROWNUM

   FROM (SELECT [column_list] FROM table

          ORDER BY TOP-N_column)

  WHERE  ROWNUM <= N ( where 절은 < 또는 <= )

[13] Other Database Objects (Sequence, Index, Synonym)

1. Sequence?

   - unique한 숫자를 자동으로 부여한다.

   - 공유 가능하다.

   - primary key 성격을 갖는다.

   - cache 설정을 할 수 있기 때문에 빠른 속도를 낼 수 있다.

2. CREATE SEQUENCE 문장

   CREATE SEQUENCE sequence

      [INCREMENT BY n] : default 1

      [START WITH n]    : default 1

      [{MAXVALUE n | NOMAXVALUE}]

      [{MINVALUE n | NOMINVALEU}]

      [{CYCLE | NOCYCLE}]

      [{CACHE n | NOCACHE{] ; : default 20개의 cache

   INCREMENT BY n : n만큼 증가 한다.

   START WITH n : 처음 시작하는 숫자는 n이다.

   MAXVALUE n : MAX값은 n이다.

   NOMAXVALUE : MAX값은 무한대다. (10^27  OR  -1) --> default

   MINVALUE n : MIN값은 n이다.

   NOMINVALUE : MIN값은 무한대다. (-(10^26)  OR 1 ) --> default

   CYCLE | NOCYCLE : MAX나 MIN값으로 갔을때 다시 처음부터 새로 

                        시작할 건지를 설정한다.  default는 NOCYCLE

   CACHE n | NOCACHE : n만큼의 CACHE를 줄 것인지를 설정한다. 

                           --> default는 cache 20으로 잡는다.

3. CREATE SEQUENCE 예제

   CREATE SEQUENCE dept_deptno

       INCREMENT BY 1

       START WITH 91

       MAXVALUE 100

       NOCACHE

       NOCYCLE ;

4. USER_SEQUENCES data dictionary

   SELECT sequence_name, min_value, max_value, increment_by, last_number

   FROM USER_SEQUENCES ;

   * last_number는 다음에 sequence 가능 한 숫자를 보여준다.

5. NEXTVAL 과 CURRVAL

   NEXTVAL : Sequence가 1개 증가된다.

   CURRVAL : 현재 sequence 값을 보여준다.

   * 사용가능

     - SELECT문장에서 SELECT list

     - INSERT문장에서 SELECT list, values절 list

     - UPDATE문장에서 SET절

   * 사용불가

     - View의 SELECT list

     - SELECT문장에서 DISTINCT keyword

     - SELECT문장에서 GROUP BY, HAVING, ORDER BY절

     - SELECT, DELETE, UPDATE문장 안에 있는 subquery

     - CREATE TABLE, ALTER TABLE문장에서 DEFAULT expression

   * CACHE가 20으로 정의 했을 때...

     ① INSERT INTO dept

        VALUES (dept_deptno.NEXTVAL, ...)       --> 1

     ② SELECT last_number FROM user_squences   --> 21

     ③ SELECT dept_deptno.CURRVAL FROM dual    --> 1

     ④ SELECT dept_deptno.NEXTVAL FROM dual    --> 2

6. SEQUENCE 사용예

   INSERT INTO dept( deptno, dname, loc )

   VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO')

   --> 증가된 sequence값이 deptno에 저장된다.

   * 현재의 sequence값을 보려면...

     SELECT dept_deptno.CURRVAL FROM dual ;

   * SELECT에서 sequence값 증가시키기

     SELECT dept_deptno.NEXTVAL FROM dual ;

7. SEQUENCE 수정하기

   ALTER SEQUENCE dept_deptno

         INCREMENT BY 1

         MAXVALUE 999999

         NOCACHE

         NOCYCLE ;

   * sequence에 대한 ALTER privilege를 갖고 있어야 한다.

   * maxvalue가 current value보다 적을 땐 반드시 drop하고 re-create해야 한다.

   * START WITH 문을 변경할 수 없습니다.( 삭제 후 다시 생성 )

8. SEQUENCE 제거하기   

   DROP SEQUENCE dept_deptno ;

9. INDEX ?

   - Retrieve를 빨리 하기 위해서

   - DISK I/O를 줄일수 있다.

   - 테이블과는 독립적이다.

    * PRIMARY KEY or UNIQUE constraint 생성시 오라클 서버에서 자동으로 생성되는것과, CREATE        INDEX를 사용해서 만들어지는 index가 있다.

   * INDEX는 많이 만든다고 좋은게 아니다.

   * INDEX는 이렇때 만드는게 좋다.

     - WHERE절이나 JOIN조건에 자주나오는 column

     - column의 범위가 클때

     - null value를 많이 갖고 있는 column

     - 전체 row에서 2~4% 정도의 row를 찾을때

10. CREATE INDEX

   CREATE INDEX index

   ON table (column[, column]...) ;    

   CREATE INDEX emp_ename_idx

   ON emp (ename) ;

11. USER_INDEXES and USER_IND_COLUMNS data dictionary

   SELECT ic.index_name, ic.column_name, ic.column_position, ix.uniqueness

   FROM user_indexes ix, user_ind_columns ic

   WHERE ic.index_name = ix.index_name

     AND ic.table_name = 'EMP' ;

12. INDEX 제거하기

   DROP INDEX emp_ename_idx ;

13. SYNONYM ?

   user가 synonym을 사용하면 스키마를 별도로 명시할 필요가 없어진다.

14. CREATE SYNONYM

   CREATE [PUBLIC] SYNONYM synonym

   FOR object ;

   * PUBLIC은 모든 user에게 synonym권한을 주는 것이고, DBA권한이 있는 사람만 

     사용이 가능하다.

   * PUBLIC을 안쓰면 만든 사람만 사용한다.

   CREATE SYNONYM d_sum

   FOR dept_sum_vu ;

   --> dept_sum_vu라는 view테이블을 d_sum으로 사용 하겠다.

15. PUBLIC SYNONYM

   CREATE PUBLIC SYNONYM dept

   FOR alice.dept

   --> alice가 만든 dept테이블을 dept로 모든 유저에게 명시 하겠다.

16. SYNONYM 제거

    DROP SYNONYM d_sum ;

    * public으로 정의된 SYNONYM은 DBA만 제거가 가능하다.  

[14] Controlling User Access (GRANT, REVOKE) 

* 시스템 권한(System Privilege): 데이터 베이스를 액세스할 수 있다.

* 객체 권한(Object Privilege)  : 데이터 베이스 객체 내용을 조작할 수 있다.

1. System Privilege? --> DBA권한

   - Create new users ( CREATE USER )

   - Remove users     ( DROP USER )

   - Remove tables    ( DROP ANY TABLE )

   - Backup tables     ( BACKUP ANY TABLE )

2. Creating User

   CREATE USER scott

   IDENTIFIED BY tiger ;

3. system privilege에서의 GRANT

   GRANT privilege [, privilege...]

   TO user [, user...] 

   [WITH ADMIN OPTION] ;

   * WITH ADMIN OPTION : dba가 권한을 주는 user에게도 admin 권한을 줄 수 있다.

   * user system privilege(DBA 가 USER에게 할당 할 수 있는 권한)

     CREATE SESSION    : 테이터베이스에 connect하는 권한

     CREATE TABLE      : 테이블 만드는 권한

     CREATE SEQUENCE  : sequence 만드는 권한

     CREATE VIEW        : view 만드는 권한

     CREATE PROCEDURE : stored prcedure, function 만드는 권한

     GRANT create table, create sequence, create view

     TO scott

     --> scott에게 table, sequence, view만드는 권한을 준다.

4. ROLE : 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹 

           Grant를 줄 role user를 만든후 그 role user에 grant를 주고, 

           role user의 권한을 각각의 user에게 넘겨준다.

   ① SQL> CREATE ROLE manager ;

      Role created.

   ② SQL> GRANT create table, create view TO manager ;

      Grant succeeded.

   ③ SQL> GRANT mananger TO brake, clock ;

      Grant succeeded.

5. User Password 변경하기

   ALTER USER user IDENITIFIED BY password ;

   예) ALTER USER scott IDENTIFIED BY lion ;

6. Object Privileges?

   * 객체마다 다르다.

   * 소유자는 객체에 대한 모든 권한을 갖는다.

   * 소유자는 자신의 객체에 대한 특정 권한을 부여할 수 있다.

7. Object Privilege에서의 GRANT

   GRANT object_priv [(columns)| ALL]

   ON object

   TO {user|role|PUBLIC}

   [WITH GRANT OPTION] ;

   * sue, rich 라는 user에게 emp 테이블을 select 권한을 준다.

     GRANT select

     ON emp

     TO sue, rich ;

   * scott, manager라는 user에게 dept 테이블의 dname,loc 컬럼을 update할 수 있게  권한을 준다.

     GRANT update (dname, loc)

     ON dept

     TO scott, manager ;

   * WITH GRANT OPTION

     GRANT select, insert

     ON dept

     TO scott

     WITH GRANT OPTION ;

     --> scott에게 dept 테이블의 select, insert권한을 주면서 scott가 다른 유저에게도 

         이 권한을 줄 수 있게 한다.

   * PUBLIC : 모든 유저에게 권한을 부여한다.

     GRANT select 

     ON alice.dept 

     TO PUBLIC ;

     --> 모든유저에게 alice가 만든 dept 테이블의 select권한을 준다.

   * 모든 유저에게 모든 object권한을 주기

     GRANT ALL

     ON emp

     TO PUBLIC ;

8. 권한 없애기 (REVOKE)

   REVOKE {privilege [, privilege...] | ALL}

   ON object

   FROM {user[, user...]|role|PUBLIC}

   [CASCADE CONSTRAINTS]

   REVOKE select, insert

   ON dept

   FROM scott ; --> scott에게서 dept테이블의 select, insert권한을 없앤다.   

   * CASCADE CONSTRAINTS : 이 옵션을 않쓰면 revoke할 때 forien key

     관계의 table을  revoke할 수 없다.

9. Privilege Grant를 볼 수 있는 Data Dictionary

   ROLE_SYS_PRIVS        : System privilege 권한에 대한 정보

   ROLE_TAB_PRIVS        : table(object) privilege 권한에 대한 정보

   USER_ROLE_PRIVS      : role정보

   USER_TAB_PRIVS_MADE : 내가 다른 사람에게 준 TABLE 권한에 대한 정보

   USER_TAB_PRIVS_RECD : 내가 다른 사람에게 받은 TABLE 권한에 대한 정보

   USER_COL_PRIVS_MADE : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보

   USER_COL_PRIVS_RECD : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보 

    *****************************************************************

 TABLE <--> PROCEDURE

 VIEW : DML 수행 

 SEQUENCE :  ALTER, SELECT 

1  PL/SQL의 개요       

1.1  PLSQL 이란?  [ 2002/01/20 ]         

1.2  PL/SQL Block 구조   [ 2002/05/09 ]         

1.3  PL/SQL 블럭의 유형   [ 2002/05/09 ]         

2  프로시저(PROCEDURE)와 함수(FUNCTION)       

2.1  프로시저(PROCEDURE)  [ 2002/01/20 ]         

2.2  함수(FUNCTION)  [ 2002/01/20 ]         

3  PL/SQL 데이터 타입       

3.1  스칼라 데이터 타입   [ 2002/01/20 ]         

3.2  복합 데이터 타입       

3.3  %ROWTYPE  [ 2002/01/20 ]         

3.4  PL/SQL 테이블   [ 2002/01/20 ]         

3.5  PLSQL 레코드   [ 2002/01/20 ]          

3.6  PL/SQL Table of Record  [ 2002/01/20 ]         

4  PL/SQL내의  SQL문       

4.1  INSERT  [ 2002/01/20 ]         

4.2  UPDATE  [ 2002/01/20 ]         

4.3  DELETE  [ 2002/01/20 ]         

5  PL/SQL 제어문       

5.1  반복제어       

5.1.1  FOR LOOP  [ 2002/01/20 ]         

5.1.2  LOOP문, WHILE문   [ 2002/01/20 ]         

5.2  조건제어(IF)  [ 2002/01/20 ]         

6  SQL 커서       

6.1  암시적 커서(Implicit Cursor)  [ 2002/01/20 ]         

6.2  명시적 커서(Explicit Cursor)       

6.2.1  Explicit Cursor  [ 2002/01/20 ]         

6.2.2  FOR문에서 커서 사용(Cursor FOR Loops)  [ 2002/01/20 ]         

6.2.3  명시적 커서의 속성(Explicit Cursor Attributes)  [ 2002/01/20 ]         

6.2.4  파라미터가 있는 커서(Cursors with Parameters)  [ 2002/01/20 ]         

6.2.5  The WHERE CURRENT OF Clause  [ 2002/01/20 ]          

7  예외절 처리      

7.1  예외(Exception)  [ 2002/01/20 ]         

7.2  미리 정의된 예외(Predefined Exceptions)  [ 2002/01/20 ]         

7.3  미리 정의되지 않은 예외(Non-Predefined Exception)  [ 2002/01/20 ]         

7.4  사용자 정의 예외(User-Defined Exceptions)  [ 2002/01/20 ]         

7.5  SQLCODE, SQLERRM  [ 2002/01/20 ]          

8  Package(패키지)  [ 2002/01/20 ]          

9  Trigger(트리거)  [ 2002/01/20 ]

PL/SQL 이란  ? 

 - PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자 입니다. 

 - SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며, 

   오라클 자체에 내장되어 있는  Procedure Language 입니다 

 - DECLARE 문을 이용하여 정의되며, 선언문의 사용은 선택 사항입니다.    

 - PL/SQL 문은 블록 구조로 되어 있고  PL/SQL 자신이 컴파일 엔진을 가지고 있습니다. 

PL/SQL의 장점   

 - PL/SQL 문은  BLOCK 구조로 다수의  SQL 문을 한번에 ORACLE DB 로 보내서 처리하므로   

   수행속도를 향상 시킬수 있습니다.   

 - PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다. 

 - 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있습니다.   

 - Variable, Constant, Cursor, Exception 을 정의하고, SQL 문장과  Procedural 문장에서   

   사용합니다.    

 - 단순, 복잡한 데이터형태의 변수를 선언합니다.   

 - 테이블의 데이터 구조와  DataBase 의 컬럼럼에 준하여 동적으로 변수를 선언 할 수 있습니다.   

 - Exception 처리 루틴을 이용하여 Oracle Server Error 를 처리합니다.   

 - 사용자 정의 에러를 선언하고  Exception 처리 루틴으로 처리 가능 합니다. 

PL/SQL Block Structure 

 - PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어 입니다. 

 - PL/SQL 블록은 선언부(선택적), 실행부(필수적),예외 처리부(선택적)로 구성되어 있고,    

   BEGIN 과  END 키워드는 반드시 기술해 주어야 합니다. 

 - PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있습니다.   

  ◈ Declarative Section(선언부)   

   -   변수, 상수, CURSOR, USER_DEFINE Exception 선언 

  ◈ Executable Section(실행부)   

   -   SQL, 반복분, 조건문실행   

   -   실행부는  BEGIN 으로 시작하고  END 로 끝납니다.   

   -  실행문은 프로그램 내용이 들어가는 부분으로서 필수적으로 사용되어야 합니다.   

  ◈ Exception Handling Section(예외처리)   

    - 예외에 대한 처리.   

    - 일반적으로 오류를 정의하고 처리하는 부분으로 선택 사항입니다. 

● DECLARE   

    - Optional 

    - Variables, cursors, user-defined exceptions 

● BEGIN 

    - Mandatory 

    - SQL Statements 

    - PL/SQL Statements 

● EXCEPTION   

    - Actions to perform when errors occur 

● END; 

    - Mandatory 

PL/SQL 프로그램의 작성 요령 

  - PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용합니다. . 

  - END 뒤에  ;을 사용하여 하나의 블록이 끝났다는 것을 명시 합니다.   

  - PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고,   

    SQL프롬프트에서 바로 작성할 수도 있습니다. 

  - SLQ*PLUS 환경에서는  DELCLARE 나  BEGIN 이라는 키워드로  PL/SQL블럭이 시작하는 것을   

    알 수 있습니다. 

 - 단일행 주석  : -- 

 - 여러행 주석  : /* */ 

 - PL/SQL 블록은 행에  / 가 있으면 종결됩니다. 

프로시져란.. 

- 특정 작업을 수행할수 있고, 이름이 있는  PL/SQL 블록으로서. 매개 변수를 받을  수 있고..   

  반복적으로 사용할 수 있는거죠..   보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 

   PL/SQL 블록을 데이터 베이스에 저장하기 위해 생성합니다.   

 ⊙ CREATE OR REPLACE 구문을 사용하여 생성합니다.   

 ⊙ IS 로  PL/SQL 의 블록을 시작합니다.   

 ⊙ LOCAL 변수는  IS 와  BEGIN 사이에 선언합니다.   

[Syntax] 

CREATE OR REPLACE procedure name   

   IN argument   

   OUT argument   

   IN OUT argument   

IS   

   [변수의 선언] 

BEGIN    --> 필수   

   [PL/SQL Block]   

   -- SQL 문장, PL/SQL 제어 문장   

   [EXCEPTION]   --> 선택 

  -- error 가 발생할 때 수행하는 문장 

END;    --> 필수   

프로시저 작성 예제   

SQL>CREATE OR REPLACE PROCEDURE update_sal   

        /* IN   Parameter */   

        (v_empno           IN     NUMBER)             

       IS   

       BEGIN   

         UPDATE emp   

         SET sal = sal   * 1.1   

         WHERE empno = v_empno;   

         COMMIT;   

       END update_sal;   

       /   

 프로시져가 생성되었습니다.   

설명..   

프로시저의 이름은  update_sal 이고..   

프로시저  update_sal 은 사번(v_empno)를 입력받아서 급여를  update 시켜주는  sql문입니다.   

프로시저를 끝마칠때에는 항상  "/"를 지정 합니다.   

프로시저의 실행   

EXECUTE 문을 이용해 프로시저를 실행합니다.   

SQL> execute update_sal(7369);  

PL/SQL 처리가 정상적으로 완료되었습니다. 

7369번 사원의 급여가  10% 인상됐습니다.   

SELECT 문을 실행시켜보면 데이터가 수정된 것을 확인할 수 있습니다.   

Parameter 란 

 ⊙ 실행 환경과  program 사이에 값을 주고 받는 역할을 합니다. 

 ⊙ 블록 안에서의 변수와 똑같이 일시적으로 값을 저장하는 역할을 합니다.   

 ⊙ Parameter 의 타입 

    - IN :   실행환경에서  program 으로 값을 전달 

    - OUT : program 에서 실행환경으로 값을 전달 

    - INOUT : 실행환경에서  program으로 값을 전달하고,   

                  다시  program에서 실행환경으로 변경된 값을 전달 

Block Type(PL/SQL 블럭의 유형) 

[ Anonymous ]    

[ Procedure ]   

[ Function ] 

 ◈ Anonymous Block(익명 블록) 

    이름이 없는 블록을 의미합니다. 

    실행하기 위해 프로그램 안에서 선언되고 실행 시에 실행을 위해  PL/SQL 엔진으로 전달됩니다. 

    선행 컴파일러 프로그램과  SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장할 수 있습니다. 

 ◈ Procedure(프로시저) 

    특정 작업을 수행할 수 있는 이름이 있는  PL/SQL 블록으로서. 

    매개 변수를 받을 수 있고.. 반복적으로 사용할 수 있는 거죠.. 

    보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는  PL/SQL블록을 

    데이터 베이스에 저장하기 위해 생성합니다. 

 ◈ Function(함수)  

    보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용합니다. 

    대부분 구성이 프로시저와 유사하지만  IN 파라미터만 사용 할 수 있고, 

    반드시 반환될 값의 데이터 타입을  RETURN문에 선언해야 합니다. 

    또한  PL/SQL블록 내에서  RETURN문을 통해서 반드시 값을 반환해야 합니다. 

◈ 함수(Function) 

 - 보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 합니다. 

 - 대부분 구성이 프로시저와 유사 하지만  IN 파라미터만 사용 할 수 있습니다. 

 - 반드시 반환될 값의 데이터 타입을  RETURN문에 선언해야 합니다. 

 - 또한  PL/SQL블록 내에서  RETURN 문을 통해서 반드시 값을 반환해야 합니다.   

   [Syntax] 

     CREATE OR REPLACE FUNCTION function name   

       [(argument...)]   

      RETURN   datatype   

         -- Datatype은 반환되는 값의  datatype입니다.   

     IS   

        [변수 선언 부분] 

     BEGIN 

       [PL/SQL Block]   

        -- PL/SQL 블록에는 적어도 한 개의  RETURN 문이 있어야 합니다.   

        -- PL/SQL Block은 함수가 수행할 내용을 정의한 몸체부분입니다. 

     END; 

SQL> CREATE OR REPLACE FUNCTION FC_update_sal   (v_empno          IN     NUMBER) 

          -- 리턴되는 변수의 데이터타입을 꼭 정의해야 합니다 

          RETURN NUMBER   . 

        IS 

           v_sal   emp.sal%type; 

        BEGIN 

           UPDATE emp         SET sal   = sal   * 1.1     WHERE empno   = v_empno; 

            COMMIT; 

            SELECT sal  INTO v_sal     FROM emp      WHERE empno = v_empno; 

        -- 리턴문이 꼭 존재해야 합니다 

          RETURN v_sal;   

       END;   

함수가 생성되었습니다.    

설명..   

이 함수에는  v_sal이라는 %type 변수가 사용되고 있습니다.   

스칼라 데이터 타입을 참고하세요. 

프로지저와 마찬가지로 세미콜론(;)으로 블록을 종료한 뒤  "/"를 붙여 코드를 끝마칩니다.   

함수의 실행   

먼저 함수의 반환값을 저장할 변수를 선언합니다.   

SQL> VAR salary NUMBER;   

EXECUTE 문을 이용해 함수를 실행합니다.   

SQL>EXECUTE :salary := FC_update_sal(7900);   

PL/SQL 처리가 정상적으로 완료되었습니다.   

오라클  SQL에서 선언된 변수의 출력은  PRINT문을 사용합니다.   

PRINT 문으로 함수의 반환값을 저장한  salary 의 값을 확인하면 됩니다.   

SQL>PRINT salary; 

    SALARY 

---------- 

      1045   

결과가 이렇게 나옵니다.   

변수 선언 방법 

[Syntax] 

⊙ Identifier의 이름은  sql 의  object명과 동일한 규칙을 따릅니다.   

⊙ Identifier를 상수로 지정하고 싶은 경우는  CONSTANT 라는  KEYWORD를 명시하고 

    반드시 초기화를 할당합니다.   

⊙ NOT NULL이 정의되어 있으면 초기값을 반드시 지정하고,   

    정의되어 있지 않을 때는 생략 가능합니다.   

⊙ 초기값은 할당 연산자(:=)를 사용하여 정의 합니다.   

⊙ 초기값을 정의하지 않으면  Identifier 는 NULL값을 가지게 됩니다. 

⊙ 일반적으로 한줄에 한 개의  Identifier 를 정의 합니다. 

 ※ 스칼라 데이터 타입은 단수 데이터형으로 한가지의 데이터 값만 가집니다.   

BINARY_INTEGER  -2147483647 에서  2147483647 사이의 정수 

NUMBER[(P, S)]  고정 및 부동 소숫점 수에 대한 기본 유형 

CHAR[(최대길이)] 

고정 길이 문자에 대한 기본형은  32767바이트까지 입니다.   

지정하지 않는다면 디폴트 길이는  1로 설정됩니다. 

LONG 

고정 길이 문자에 대한 기본형은  32760바이트까지 입니다. 

LONG 데이터베이스 열의 최대 폭은  2147483647 바이트입니다. 

LONG RAW 

이진 데이터와 바이트 문자열에 대한 기본형은  32760Byte 까지 입니다.   

LONG RAW 데이터는  PL/SQL 에 의해 해석되지 않습니다. 

VARCHAR2(최대길이) 

3 변수 길이 문자 데이터에 데한 기본형은  32767Byte까지 입니다.   

VARCHAR2 변수와 상수에 대한 디폴트 크기는 없습니다. 

DATE   

날짜와 시간에 대한 기본형.   

DATE 값은 지정 이후의 초 단위로 날에 대한 시간을 포함합니다.   

날짜의 범위는  BC 4712년  1월 1일부터  AD 9999 년  12월  31일사이 입니다. 

BOOLEAN 

논리연산에 사용되는 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저장 하는 데이터 유형 

선언 예제   

 v_price CONTANT NUMBER(4,2) := 12.34 ;      -- 상수 숫자 선언(변할 수 없다) 

v_name VARCHAR22(20) ;   

v_Bir_Type    CHAR(1) ;   

v_flag     BOOLEAN   NOT NULL := TRUE ;       -- NOT NULL 값  TRUE 로 초기화   

v_birthday DATE; 

%TYPE 데이터형   

⊙ %TYPE 데이터형은 기술한 데이터베이스 테이블의 컬럼 데이터 타입을 모를 경우 사용할 수 있고,   

⊙ 또. 코딩이후 데이터베이스 컬럼의 데이터 타입이 변경될 경우 다시 수정할 필요가 없습니다.   

⊙ 이미 선언된 다른 변수나 데이터베이스 컬럼의 데이터 타입을 이용하여 선언합니다.   

⊙   데이터 베이스 테이블과 컬럼 그리고 이미 선언한 변수명이 %TYPE앞에 올 수 있습니다.   

%TYPE 속성을 이용하여 얻을 수 있는 장점 

   - 기술한  DB column definition 을 정확히 알지 못하는 경우에 사용할 수 있습니다. 

   - 기술한  DB column definition 이 변경 되어도 다시  PL/SQL을 고칠 필요가 없습니다. 

예제   

  v_empno   emp.empno%TYPE := 7900 ;   

  v_ename emp.ename%TYPE;    

예제 프로시져..   

SQL>CREATE OR REPLACE PROCEDURE Emp_Info     ( p_empno IN emp.empno%TYPE ) 

        IS 

        -- %TYPE 데이터형 변수 선언   

        v_empno emp.empno%TYPE;   

        v_ename emp.ename%TYPE; 

        v_sal    emp.sal%TYPE; 

        BEGIN 

        DBMS_OUTPUT.ENABLE; 

        -- %TYPE 데이터형 변수 사용   

        SELECT empno, ename, sal 

        INTO v_empno, v_ename, v_sal    

        FROM emp 

        WHERE empno = p_empno ; 

        -- 결과값 출력   

        DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || v_empno );   

        DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || v_ename ); 

        DBMS_OUTPUT.PUT_LINE( '사원급여  : ' || v_sal ); 

        END; 

       / 

프로시져가 생성되었습니다.   

SQL>SET SERVEROUTPUT ON;      --   DBMS_OUTPUT 결과값을 화면에 출력 하기위해 

실행 결과   

SQL> EXECUTE Emp_Info(7369);   

사원번호  : 7369 

사원이름  : SMITH 

사원급여  : 880 

PL/SQL 처리가 정상적으로 완료되었습니다. 

복합데이터타입 

하나 이상의 데이터값을 갖는 데이터 타입으로 배열과 비슷한 역할을 하고 재사용이 가능합니다.   

%ROWTYPE 데이터 형과, PL/SQL 테이블과 레코드가 복합 데이터 타입에 속합니다.   

%ROWTYPE   

⊙   테이블이나 뷰 내부의 컬럼 데이터형, 크기, 속석등을 그대로 사용할 수 있습니다.   

⊙   %ROWTYPE 앞에 오는 것은 데이터 베이스 테이블 이름입니다.   

⊙   지정된 테이블의 구조와 동일한 구조를 갖는 변수를 선언할 수 있습니다.   

⊙   데이터베이스 컬럼들의 수나  DATATYPE 을 알지 못할 때 편리 합니다.   

⊙   테이블의 데이터 컬럼의  DATATYPE 이 변경될 경우 프로그램을 재 수정할 필요가 없습니다.    

%ROWTYPE 예제 프로시져..   

SQL>CREATE OR REPLACE PROCEDURE RowType_Test       ( p_empno IN emp.empno%TYPE ) 

    IS 

          v_emp    emp%ROWTYPE ; 

   BEGIN 

        DBMS_OUTPUT.ENABLE; 

        -- %ROWTYPE 변수 사용   

        SELECT empno, ename, hiredate 

        INTO v_emp.empno, v_emp.ename, v_emp.hiredate 

        FROM emp     WHERE empno = p_empno; 

       DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || v_emp.empno ); 

       DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || v_emp.ename ); 

       DBMS_OUTPUT.PUT_LINE( '입 사 일  : ' || v_emp.hiredate ); 

   END; 

        /   

   프로시져가 생성되었습니다.   

실행 결과   

  SQL> SET SERVEROUTPUT ON ;   -- DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용 

  SQL> EXECUTE RowType_Test(7900); 

  사원번호  : 7900 

  사원이름  : JAMES 

  입 사 일  : 81/12/03 

PL/SQL 처리가 정상적으로 완료되었습니다. 

PL/SQL 테이블 

PL/SQL 에서의 테이블은 오라클  SQL에서의 테이블과는 다릅니다. PL/SQL에서의 테이블은   

일종의 일차원 배열이라고 생각하시면 이해하기 쉬울겁니다.   

⊙ 테이블은 크기에 제한이 없으면 그  ROW의 수는 데이터가 들어옴에 따라 자동 증가 합니다.   

⊙ BINARY_INTEGER 타입의인덱스 번호로 순서가 정해집니다.   

⊙ 하나의 테이블에 한 개의 컬럼 데이터를 저장 합니다.     

예제   

TYPE prdname_table IS TABLE OF VARCHAR2(30)   

INDEX BY BINARY_INTEGER;   

-- prdname_table 테이블타입으로  prdname_tab 변수를 선언해서 사용   

prdname_tab     prdname_table ; 

PL/SQL 테이블 예제 프로시져..   

SQL>CREATE OR REPLACE PROCEDURE Table_Test 

    (v_deptno IN emp.deptno%TYPE) 

    IS 

         TYPE empno_table IS TABLE OF emp.empno%TYPE 

         INDEX BY BINARY_INTEGER; 

         TYPE ename_table IS TABLE OF emp.ename%TYPE 

         INDEX BY BINARY_INTEGER; 

         TYPE sal_table IS TABLE OF emp.sal%TYPE 

         INDEX BY BINARY_INTEGER; 

         -- 테이블타입으로 변수를 선언해서 사용   

         empno_tab   empno_table ; 

         ename_tab   ename_table ; 

         sal_tab     sal_table; 

         i BINARY_INTEGER := 0; 

   BEGIN 

         DBMS_OUTPUT.ENABLE; 

         FOR emp_list IN(SELECT empno, ename, sal FROM emp WHERE deptno = v_deptno) LOOP 

          /* emp_list 는 자동선언되는  BINARY_INTEGER 형 변수로  1씩 증가합니다.   

              emp_list 대신 다른 문자열 사용가능  */ 

                i := i + 1; 

               -- 테이블 변수에 검색된 결과를 넣습니다 

                empno_tab(i) := emp_list.empno ;       

                ename_tab(i) := emp_list.ename ; 

                sal_tab(i)    := emp_list.sal ; 

          END LOOP; 

          -- 1 부터  i까지  FOR 문을 실행   

          FOR cnt IN 1..i LOOP 

             -- TABLE 변수에 넣은 값을 뿌려줌   

             DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || empno_tab(cnt) ); 

             DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || ename_tab(cnt) ); 

             DBMS_OUTPUT.PUT_LINE( '사원급여  : ' || sal_tab(cnt) ); 

          END LOOP; 

  END;   

프로시져가 생성되었습니다.   

실행 결과   

  SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

  SQL> EXECUTE Table_Test(10); 

 사원번호  : 7782   

 사원이름  : CLARK 

 사원급여  : 2450 

 사원번호  : 7839 

 사원이름  : KING 

 사원급여  : 5000 

 사원번호  : 7934 

 사원이름  : MILLER 

 사원급여  : 1300   

PL/SQL 처리가 정상적으로 완료되었습니다.   

 emp 테이블에 있는 데이터의 입력한 부서에 해당하는 사원번호, 사원이름, 사원급여를   

 뿌려주는 프로시져 입니다   

PL/SQL 레코드 

여러 개의 데이터 타입을 갖는 변수들의 집합입니다.   

⊙ 스칼라, RECORD, 또는  PL/SQL TABLE datatype 중 하나 이상의 요소로 구성됩니다.   

⊙ 논리적 단위로서 필드 집합을 처리할 수 있도록 해 줍니다.   

⊙ PL/SQL 테이블과 다르게 개별 필드의 이름을 부여할 수 있고, 선언시 초기화가 가능합니다. 

예제    TYPE record_test IS RECORD   

    ( record_empno    NUMBER,   

      record_ename    VARCHAR2(30),   

      record_sal         NUMBER);   

      prd_record      record_test; 

PL/SQL RECORD 예제 프로시져..   

  SQL> CREATE OR REPLACE PROCEDURE Record_Test    ( p_empno IN emp.empno%TYPE ) 

     IS 

         TYPE emp_record IS RECORD 

         (v_empno     NUMBER,      v_ename     VARCHAR2(30),     v_hiredate   DATE ); 

         emp_rec    emp_record ; 

   BEGIN 

         DBMS_OUTPUT.ENABLE; 

         SELECT empno, ename, hiredate 

         INTO emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate 

         FROM emp     WHERE empno = p_empno; 

        DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || emp_rec.v_empno ); 

        DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || emp_rec.v_ename ); 

        DBMS_OUTPUT.PUT_LINE( '입 사 일  : ' || emp_rec.v_hiredate ); 

   END; 

 /   

프로시져가 생성되었습니다.   

실행 결과   

  SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

  SQL> EXECUTE Record_Test(7369); 

 사원번호  : 7369 

 사원이름  : SMITH 

 입 사 일  : 80/12/17 

  PL/SQL 처리가 정상적으로 완료되었습니다.      %ROWTYPE예제와 비교해 보세요

PL/SQL Table of Record 

⊙ PL/SQL TABLE 변수 선언과 비슷하며 데이터타입을 %ROWTYPE 으로 선언하면 됩니다.   

⊙ PL/SQL TABLE 과  RECORD 의 복합 기능을 합니다.   

DECLARE 

TYPE dept_table_type IS TABLE OF dept%ROWTYPE   

INDEX BY BINARY_INTEGER;   

-- Each element of dept_table   is a record   

dept_table     dept_table_type ; 

PL/SQL TABLE OF RECORD 예제 프로시져..   

SQL> CREATE OR REPLACE PROCEDURE Table_Test 

   IS 

          i BINARY_INTEGER := 0;  

     -- PL/SQL Table of Record 의 선언 

    TYPE dept_table_type IS TABLE OF dept%ROWTYPE 

     INDEX BY BINARY_INTEGER; 

     dept_table dept_table_type; 

BEGIN 

    FOR dept_list IN (SELECT * FROM dept) LOOP 

         i:= i+1; 

        dept_table(i).deptno := dept_list.deptno ;       

        dept_table(i).dname := dept_list.dname ; 

        dept_table(i).loc    := dept_list.loc ; 

    END LOOP;  

    FOR cnt IN 1..i LOOP 

        DBMS_OUTPUT.PUT_LINE( '부서번호  : ' || dept_table(cnt).deptno ||   

                                          '부서명  : ' ||  dept_table(cnt).dname ||   

                                          '위치  : ' || dept_table(cnt).loc );  

    END LOOP;  

 END; 

/ 

실행결과  

  SQL>set serveroutput on; 

  SQL>exec Table_test; 

 부서번호  : 10부서명  : ACCOUNTING위치  : NEW_YORK 

 부서번호  : 20부서명  : RESEARCH 위치  : DALLAS 

 부서번호  : 30부서명  : 인사과위치  : CHICAGO 

 부서번호  : 40부서명  : OPERATIONS 위치  : BOS%TON 

  PL/SQL 처리가 정상적으로 완료되었습니다. 

Insert문 

PL/SQL에서의  INSERT 문은  SQL과 비슷합니다. 

사원 등록 예제 프로시져..   

SQL> CREATE OR REPLACE PROCEDURE Insert_Test 

        ( v_empno   IN emp.empno%TYPE, 

          v_ename   IN emp.ename%TYPE, 

          v_deptno IN emp.deptno%TYPE ) 

        IS 

      BEGIN 

          DBMS_OUTPUT.ENABLE;   

          INSERT INTO emp(empno, ename, hiredate, deptno) 

          VALUES(v_empno, v_ename, sysdate, v_deptno); 

          DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || v_empno ); 

          DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || v_ename ); 

          DBMS_OUTPUT.PUT_LINE( '사원부서  : ' || v_deptno ); 

          DBMS_OUTPUT.PUT_LINE( '데이터 입력 성공  ' ); 

       END ; 

      /             

프로시져가 생성되었습니다.    

실행 결과   

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL>   EXECUTE Insert_Test(1000, 'brave', 20); 

사원번호  : 1000 

사원이름  : brave 

사원부서  : 20 

데이터 입력 성공 

PL/SQL 처리가 정상적으로 완료되었습니다. 

UPDATE 

상품 수정 예제 프로시저..   

※ 특정 사원의 급여를 일정%센트 인상/인하하는 프로시져 

SQL>CREATE OR REPLACE PROCEDURE Update_Test 

       ( v_empno IN     emp.empno%TYPE,        -- 급여를 수정한 사원의 사번 

         v_rate       IN     NUMBER )                      -- 급여의 인상/인하율 

         IS 

         v_emp   emp%ROWTYPE ; 

         BEGIN 

         DBMS_OUTPUT.ENABLE; 

         UPDATE emp    SET sal = sal+(sal * (v_rate/100))    -- 급여를 계산 

         WHERE empno = v_empno ; 

         DBMS_OUTPUT.PUT_LINE( '데이터 수정 성공  ' ); 

         -- 수정된 데이터 확인하기 위해 검색   

         SELECT empno, ename, sal     INTO v_emp.empno, v_emp.ename, v_emp.sal 

         FROM emp     WHERE empno = v_empno ; 

         DBMS_OUTPUT.PUT_LINE( ' **** 수 정 확 인  **** '); 

         DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || v_emp.empno ); 

         DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || v_emp.ename ); 

         DBMS_OUTPUT.PUT_LINE( '사원급여  : ' || v_emp.sal ); 

         END ; 

         /   

프로시저가 생성되었습니다.  

프로시저 실행   

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE Update_Test(7900, -10); 

데이터 수정 성공 

**** 수 정 확 인  **** 

사원번호  : 7900 

사원이름  : JAMES 

사원급여  : 855 

PL/SQL 처리가 정상적으로 완료되었습니다. 

7900번 사원의 급여를  10% 인하했습니다. 

DELETE 

사원 삭제 예제 프로시저..   

SQL> CREATE OR REPLACE PROCEDURE Delete_Test   ( p_empno IN   emp.empno%TYPE ) 

        IS 

        -- 삭제 데이터를 확인하기 레코드 선언   

        TYPE del_record IS   RECORD 

        ( v_empno       emp.empno%TYPE, 

          v_ename       emp.ename%TYPE, 

          v_hiredate     emp.hiredate%TYPE) ; 

          v_emp   del_record ; 

        BEGIN 

        DBMS_OUTPUT.ENABLE; 

         -- 삭제된 데이터 확인용 쿼리   

         SELECT empno, ename, hiredate 

         INTO v_emp.v_empno, v_emp.v_ename, v_emp.v_hiredate 

         FROM emp     WHERE empno = p_empno ; 

        DBMS_OUTPUT.PUT_LINE( '사원번호  : ' || v_emp.v_empno ); 

        DBMS_OUTPUT.PUT_LINE( '사원이름  : ' || v_emp.v_ename ); 

        DBMS_OUTPUT.PUT_LINE( '입 사 일  : ' || v_emp.v_hiredate ); 

        -- 삭제 쿼리   

        DELETE      FROM emp    WHERE empno = p_empno ; 

        DBMS_OUTPUT.PUT_LINE( '데이터 삭제 성공  ' ); 

       END; 

 /   

프로시저가 생성되었습니다.   

프로시저 실행  (결과화면) 

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE Delete_Test(7900); 

사원번호  : 7900 

사원이름  : JAMES 

입 사 일  : 81/12/03 

데이터 삭제 성공  

PL/SQL 처리가 정상적으로 완료되었습니다. 

※ 7900사원을 삭제했습니다. 

FOR LOOP 문 

  -   index 는 자동 선언되는  binary_integer 형 변수이고. 1 씩 증가합니다. 

  -   reverse 옵션이 사용될 경우  index 는  upper_bound 에서  lower_bound 로  1씩 감소합니다. 

  -   IN 다음에는  coursor 나  select 문이 올 수 있습니다. 

FOR문 예제 

DECLARE 

  -- 사원 이름을 출력하기 위한  PL/SQL 테이블 선언   

   TYPE ename_table IS TABLE OF emp.ename%TYPE 

   INDEX BY BINARY_INTEGER; 

  -- 사원 급여를 출력하기 위한  PL/SQL 테이블 선언   

   TYPE sal_table IS TABLE OF emp.sal%TYPE 

   INDEX BY BINARY_INTEGER; 

   ename_tab     ename_table; 

   sal_tab       sal_table; 

   i BINARY_INTEGER := 0; 

BEGIN 

  DBMS_OUTPUT.ENABLE; 

  FOR emp_list IN   (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP 

      i := i +1 ; 

      ename_tab(i) := emp_list.ename;      -- 테이블에 사원 이름을 저장   

      sal_tab(i)       := emp_list.sal;           -- 테이블에 사원 급여를 저장   

  END LOOP; 

  FOR cnt IN    1..i    LOOP          --   화면에 출력   

     DBMS_OUTPUT.PUT_LINE('사원이름  : ' || ename_tab(cnt));   

     DBMS_OUTPUT.PUT_LINE('사원급여  : ' || sal_tab(cnt)); 

  END LOOP; 

END; 

/   

사원이름  : CLARK 

사원급여  : 2450 

사원이름  : KING 

사원급여  : 5000 

사원이름  : MILLER 

사원급여  : 1300 

PL/SQL 처리가 정상적으로 완료되었습니다.   

Loop 문  while 문 

EXIT 문이 사용되었을 경우, 무조건 

LOOP 문을 빠져나갑니다,   

EXITH WHEN 이 사용될 경우   WHEN 

절에  LOOP를 빠져 나가는 조건을 제어할 

수 있습니다. 

LOOP 문 예제 

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL>   DECLARE 

             v_cnt number(3) := 100; 

          BEGIN 

              DBMS_OUTPUT.ENABLE ; 

              LOOP 

                INSERT INTO emp(empno, ename , hiredate) 

                VALUES(v_cnt, 'test'||to_char(v_cnt),   sysdate); 

                v_cnt := v_cnt+1; 

                EXIT WHEN v_cnt > 110; 

            END LOOP; 

            DBMS_OUTPUT.PUT_LINE(v_cnt-100 || '개의 데이터가 입력되었습니다'); 

          END;             

         / 

  11 개의 데이터가 입력되었습니다   

PL/SQL 처리가 정상적으로 완료되었습니다. 

WHILE LOOP 문 

WHILE LOOP문은  FOR 문과 비슷하며 조건이  TRUE일 경우만 반복되는  LOOP문 입니다.   

예제   

WHILE cnt < 10 LOOP   

   INSERT INTO emp(empno, ename , hiredate) 

   VALUES(emp_seq.nextval, 'test',   sysdate);   

   cnt := cnt + 1 ;   

END LOOP ;   

cnt가  10이면 반복 While Loop를 탈출 

EXIT WHEN 조건  => 조건이 만족할 때 반복  loop를 탈출합니다. .   

조건제어  

IF문 예제 프로시저..   

SQL>CREATE OR REPLACE PROCEDURE Dept_Search    (p_empno IN emp.empno%TYPE ) 

IS 

   v_deptno emp.deptno%type ; 

BEGIN 

   DBMS_OUTPUT.ENABLE; 

   SELECT deptno INTO v_deptno FROM emp WHERE empno = p_empno ; 

   IF v_deptno <=   7000 THEN 

      DBMS_OUTPUT.PUT_LINE( ' ACCOUNTING 부서 사원입니다. ' ); 

   ELSIF v_deptno < 7900 THEN 

      DBMS_OUTPUT.PUT_LINE( ' RESEARCH 부서 사원입니다. ' ); 

   ELSE 

      DBMS_OUTPUT.PUT_LINE( ' 부서가 없네요... ' ); 

   END IF ; 

END ; 

/ 

프로시저가 생성되었습니다.   

프로시저 실행 

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE Dept_Search(7900); 

부서가 없네요... 

PL/SQL 처리가 정상적으로 완료되었습니다. 

SQL> EXECUTE Dept_Search(7369); 

RESEARCH 부서 사원입니다. 

PL/SQL 처리가 정상적으로 완료되었습니다. 

암시적인 커서는 오라클이나  PL/SQL실행 메커니즘에 의해 처리되는  SQL문장이 처리되는 곳에 대한   

  익명의 에드레스입니다. 오라클 데이터 베이스에서 실행되는 모든  SQL문장은 암시적인 커서이며   

  그것들과 함께 모든 암시적인 커서 속성이 사용될 수 있습니다.   

 -암시적 커서의 속성   

◈ SQL%ROWCOUNT : 해당  SQL 문에 영향을 받는 행의 수   

◈ SQL%FOUND : 해당  SQL 영향을 받는 행의 수가  1개 이상일 경우  TRUE   

◈ SQL%NOTFOUND : 해당  SQL 문에 영향을 받는 행의 수가 없을 경우  TRUE   

◈ SQL%ISOPEN : 항상  FALSE, 암시적 커서가 열려 있는지의 여부 검색   

(암시적 커서는  SQL 문이 실행되는 순간 자동으로 열림과 닫힘 실행) 

암시적 커서 예제   

CREATE OR REPLACE PROCEDURE Implicit_Cursor (p_empno emp.empno%TYPE) 

is 

   v_sal   emp.sal%TYPE; 

   v_update_row NUMBER; 

BEGIN 

   SELECT sal INTO v_sal FROM emp  WHERE empno = p_empno ; 

  -- 검색된 데이터가 있을경우 

   IF   SQL%FOUND THEN       

       DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다  : '||v_sal); 

   END IF; 

   UPDATE emp SET sal = sal*1.1  WHERE empno = p_empno; 

  -- 수정한 데이터의 카운트를 변수에 저장 

   v_update_row := SQL%ROWCOUNT; 

   DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원 수  : '|| v_update_row); 

END; 

프로시저가 생성되었습니다.      

실행결과 

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE Implicit_Cursor(7369); 

검색한 데이터가 존재합니다  : 880 

급여가 인상된 사원 수  : 1 

PL/SQL 처리가 정상적으로 완료되었습니다 

▣ 커서란 무엇인가?  명시적 커서(Explicit Cursor) 

◈커서는  Private SQL의 작업영역 입니다. 

◈오라클 서버에 의해 실행되는 모든  SQL문은 연관된 각각의 커서를 소유하고 있습니다. 

◈ 커서의 종류 

   - 암시적 커서  : 모든  DML과  PL/SQL SELECT 문에 대해 선언됩니다. 

   - 명시적 커서  : 프로그래머에 의해 선언되며 이름이 있는 커서입니다.   

▣ Explicit Cursor 의 흐름도? 

▣ 문법(Syntax)   

▣ 커서 열기(OPEN)   

◈커서의 열기는 OPEN문을 사용합니다.   

◈커서안의 검색이 실행되며 아무런 데이터행을 추출하지 못해도 에러가 발생하지 않습니다. 

    OPEN    cursor_name; 

▣ 커서 패치(FETCH) 

◈커서의  FETCH는 현재 데이터 행을 OUTPUT변수에 반환합니다.   

◈커서의  SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 합니다. 

◈커서 컬럼의 변수의 타입과 OUTPUT 변수의 데이터 타입도 동일해야 합니다. 

◈커서는 한 라인씩 데이터를 패치 합니다. 

    FETCH    cursor_name INTO variable1, variable2 ; 

▣ 커서 닫기(CLOSE)   

◈사용을 마친 커서는 반드시 닫아 주어야 합니다. 

◈필요하다면 커서를 다시 열 수 있습니다. 

◈커서를 닫은 상태에서  FETCH 를 할 수 없습니다. 

    CLOSE    cursor_name;  

Explicit Cursor 예제 

특정 부서의 평균급여와 사원수를 출력.. 

SQL>CREATE OR REPLACE PROCEDURE ExpCursor_Test   

        (v_deptno    dept.deptno%TYPE) 

        IS 

        CURSOR dept_avg IS 

        SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary 

        FROM emp a, dept b 

        WHERE a.deptno = b.deptno 

            AND b.deptno = v_deptno 

        GROUP BY b.dname ; 

        -- 커서를 패치하기 위한 변수 선언 

         v_dname    dept.dname%TYPE; 

         emp_cnt     NUMBER; 

         sal_avg      NUMBER; 

      BEGIN 

        -- 커서의 오픈 

        OPEN dept_avg; 

        -- 커서의 패치  

        FETCH dept_avg INTO v_dname, emp_cnt, sal_avg;     

           DBMS_OUTPUT.PUT_LINE('부서명  : ' || v_dname); 

           DBMS_OUTPUT.PUT_LINE('사원수  : ' || emp_cnt); 

           DBMS_OUTPUT.PUT_LINE('평균급여  : ' || sal_avg); 

       -- 커서의  CLOSE 

       CLOSE dept_avg;       

      EXCEPTION 

       WHEN OTHERS THEN 

         DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  '); 

     END; 

     / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE ExpCursor_Test(30); 

부서명  : SALES 

사원수  : 6 

평균급여  : 1550.833 

 PL/SQL 처리가 정상적으로 완료되었습니다. 

FOR문에서 커서 사용(Cursor FOR Loops) 

◈ FOR 문을 사용하면 커서의 OPEN, FETCH, CLOSE 가 자동 발생하므로 따로 기술할 필요가 없습니다 

◈ 레코드 이름도 자동 선언되므로 따로 선언할 필요가 없습니다. 

FOR문에서 커서 사용 예제 

부서별 사원수와 급여 합계를 구하는 프로시저입니다.   

SQL> CREATE OR REPLACE PROCEDURE ForCursor_Test 

        IS 

          CURSOR dept_sum IS 

          SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary 

          FROM emp a, dept b 

         WHERE a.deptno = b.deptno 

          GROUP BY b.dname; 

     BEGIN 

       -- Cursor 를  FOR문에서 실행시킨다 

       FOR emp_list IN dept_sum LOOP 

          DBMS_OUTPUT.PUT_LINE('부서명  : ' || emp_list.dname); 

          DBMS_OUTPUT.PUT_LINE('사원수  : ' || emp_list.cnt); 

          DBMS_OUTPUT.PUT_LINE('급여합계  : ' || emp_list.salary); 

       END LOOP; 

   EXCEPTION 

       WHEN OTHERS THEN 

          DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  '); 

   END; 

   / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용)  

SQL> EXECUTE ForCursor_Test; 

부서명  : ACCOUNTING 

사원수  : 3 

급여합계  : 8750 

부서명  : RESEARCH 

사원수  : 6 

급여합계  : 10875 

부서명  : SALES 

사원수  : 6 

급여합계  : 9305 

PL/SQL 처리가 정상적으로 완료되었습니다. 

▣ 명시적 커서의 속성(Explicit Cursor Attributes) 

◈%ISOPEN   

      - 커서가 OPEN되어 있으면  TRUE 

      -  %ISOPEN속성을 이용하여 커서가 열려있는지 알 수 있습니다. 

◈%NOTFOUND   

      - 패치한 데이터가 행을 반환하지 않으면  TRUE 

      -  %NOTFOUND속성을 이용하여 루프를 종료할 시점을 찾습니다. 

◈%FOUND   

      - 패치한 데이터가 행을 반환하면  TRUE 

◈ %ROWCOUNT 

     - 현재까지 반환된 모든 데이터 행의 수 

      - %ROWCOUNT 속성을 이용하여 정확한 숫자만큼의 행을 추출합니다. 

커서의 속성 예제 

  SQL>CREATE OR REPLACE PROCEDURE AttrCursor_Test         

 IS 

        v_empno      emp.empno%TYPE; 

         v_ename      emp.ename%TYPE; 

         v_sal           emp.sal%TYPE; 

      CURSOR emp_list IS 

         SELECT empno, ename, sal 

         FROM emp;     

    BEGIN 

      DBMS_OUTPUT.ENABLE; 

      OPEN emp_list;    

      LOOP     

        FETCH emp_list INTO v_empno, v_ename, v_sal; 

           -- 데이터를 찾지 못하면 빠져 나갑니다 

           EXIT WHEN emp_list%NOTFOUND;         

      END LOOP;     

      DBMS_OUTPUT.PUT_LINE('전체데이터 수  ' || emp_list%ROWCOUNT); 

      CLOSE emp_list; 

     EXCEPTION 

       WHEN OTHERS THEN 

         DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); 

    END;   

   / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE AttrCursor_Test; 

전체데이터 수  15 

PL/SQL 처리가 정상적으로 완료되었습니다. 

파라미터가 있는 커서(Cursors with Parameters) 

◈ 커서가 열리고 질의가 실행되면 매개 변수 값을 커서에 전달한다.   

◈ 다른  active set을 원할때 마다  explicit커서를 따로 선언해야 한다   

▣ 문법(Syntax)   

파라미터가 있는 커서 예제 

SQL> CREATE OR REPLACE PROCEDURE ParamCursor_Test 

        (param_deptno    emp.deptno%TYPE)   

        IS 

         v_ename      emp.ename%TYPE; 

       -- Parameter가 있는 커서의 선언 

        CURSOR emp_list(v_deptno emp.deptno%TYPE) IS 

        SELECT ename        FROM emp       WHERE deptno = v_deptno; 

       BEGIN 

        DBMS_OUTPUT.ENABLE; 

        DBMS_OUTPUT.PUT_LINE(' ****** 입력한 부서에 해당하는 사람들  ****** ');                

       -- Parameter변수의 값을 전달(OPEN될 때 값을 전달한다) 

        FOR emplst IN emp_list(param_deptno) LOOP      

          DBMS_OUTPUT.PUT_LINE('이름  : ' || emplst.ename); 

        END LOOP;     

        EXCEPTION      

          WHEN OTHERS THEN 

             DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);          

        END;   

        / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용) 

SQL> EXECUTE ParamCursor_Test(10); 

****** 입력한 부서에 해당하는 사람들  ****** 

이름  : CLARK 

이름  : KING 

이름  : MILLER 

PL/SQL 처리가 정상적으로 완료되었습니다. 

◆ WHERE CURRENT OF 

   - ROWID 를 이용하지 않고도 현재 참조하는 행을 갱신하고 삭제할 수 있게 합니다. 

   - 추가적으로  FETCH문에 의해 가장 최근에 처리된 행을 참조하기 위해서   

      "WHERE CURRENT OF 커서이름  "     절로  DELETE나  UPDATE 문 작성이 가능합니다..   

   - 이 절을 사용할 때 참조하는 커서가 있어야 하며,   

      FOR UPDATE 절이 커서 선언  query문장 안에 있어야 합니다.   

      그렇지 않으면  error 가 발생합니다..  

WHERE CURRENT OF 예제 

SQL> SET SERVEROUTPUT ON ;   -- DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용 

SQL>CREATE OR REPLACE PROCEDURE where_current   

         IS   

        CURSOR emp_list IS   

              SELECT empno   

              FROM emp   

             WHERE empno = 7934   

              FOR UPDATE;   

      BEGIN   

        --DBMS_OUTPUT.PUT_LINE 명령을 사용하기 위해서   

         DBMS_OUTPUT.ENABLE;      

         FOR emplst IN emp_list LOOP   

              --emp_list커서에 해당하는 사람의 직업을   SALESMAN 으로 업데이트 시킵니다. 

               UPDATE emp   

               SET job = 'SALESMAN'   

             WHERE CURRENT OF emp_list;   

            DBMS_OUTPUT.PUT_LINE('수정 성공'); 

         END LOOP;      

         EXCEPTION   

           WHEN OTHERS THEN   

                -- 에러 발생시 에러 메시지 출력 

                 DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);   

END;    

 --먼저 데이터를 확인해 보세용 

SQL> SELECT job FROM emp WHERE empno = 7934; 

JOB 

--------- 

CLERK 

 --PLSQL을 실행시키고.. 

SQL> EXECUTE where_current; 

수정 성공   --DBMS_OUTPUT.PUT_LINE 명령으로 출력한거.. 

PL/SQL 처리가 정상적으로 완료되었습니다.   

-- 다시 데이터를 확인하면 변경된 것을 볼 수 있습니다.   

SQL>   SELECT job FROM emp WHERE empno = 7934; 

JOB 

--------- 

SALESMAN 

▣ 예외(Exception)란? 

◈ 오라클  PL/SQL의 오류를 예외라고 부릅니다.   

◈ 오류는  PL/SQL을 컴파일 할때 문법적인 오류로 발생하는 컴파일 타임 오류와,   

    프로그램을 실행할때 발생하는 실행타임 오류로 구분할 수 있습니다. 

▣ PL/SQL오류의 종류 

 예  외     설  명     처  리   

 미리 정의된 오라클 서버 오류 

(Predefined Oracle Server)  

  PL/SQL 에서 자주 

발생하는    약 20개의 오류   

  선언할 필요도 없고, 발생시에 

예외 절로 자동 트랩(Trap)된다.   

   미리 정의되지 않은 오라클   

  서버 오류    (Non-Predefined 

Oracle    Server)   

  미리 정의된 오라클 서버 

오류를 제외한 모든 오류   

  선언부에서 선언해야 하고 발생시 

자동 트랩된다.   

  사용자 정의 오류    (User-

Defined)    

  개발자가 정한 조건에 

만족하지 않을경우 발생하는 

오류   

   선언부에서 선언하고 

실행부에서  RAISE 문을 사용하여 

발생시켜야   한다   

▣ Execption 문법(Syntax)   

◈ WHEN OTHERS 절은 맨 마지막에 옵니다.   

◈ 예외 처리절은  EXCEPTION 부터 시작합니다.   

◈ 허용합니다.   

◈ 예외가 발생하면 여러 개의 예외 처리부 중에 하나의 예외 처리부에 트랩(Trap)됩니다.   

미리 정의된 예외(Predefined Exceptions) 

◈ 오라클  PL/SQL은 자주 일어나는 몇가지 예외를 미리 정의해 놓았으며,   이러한 예외는 개발자가 

따로 선언할 필요가 없습니다.  

▣ 미리 정의된 예외의 종류? 

◈ NO_DATA_FOUND : SELECT 문이 아무런 데이터 행을 반환하지 못할때   

◈ TOO_MANY_ROWS : 하나만 리턴해야하는  SELECT문이 하나 이상의 행을 반환할 때   

◈ INVALID_CURSOR : 잘못된 커서 연산   

◈ ZERO_DIVIDE : 0 으로 나눌때   

◈ DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터가  INSERT 될때   

   이 외에도 몇 개가 더 있습니다. 

미리 정의된 예외 예제 

SQL> CREATE OR REPLACE PROCEDURE PreException_test 

         (v_deptno   IN emp.empno%TYPE)    

   IS 

       v_emp    emp%ROWTYPE; 

   BEGIN 

      DBMS_OUTPUT.ENABLE; 

      SELECT empno, ename, deptno 

      INTO v_emp.empno, v_emp.ename, v_emp.deptno 

      FROM emp 

      WHERE deptno = v_deptno ; 

      DBMS_OUTPUT.PUT_LINE('사번  : ' || v_emp.empno); 

      DBMS_OUTPUT.PUT_LINE('이름  : ' || v_emp.ename); 

      DBMS_OUTPUT.PUT_LINE('부서번호  : ' || v_emp.deptno); 

   EXCEPTION 

      WHEN    DUP_VAL_ON_INDEX    THEN 

          DBMS_OUTPUT.PUT_LINE('데이터가 존재 합니다.'); 

          DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생'); 

      WHEN    TOO_MANY_ROWS    THEN    

        DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러 발생');

      WHEN    NO_DATA_FOUND    THEN    

        DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생'); 

      WHEN    OTHERS    THEN    

        DBMS_OUTPUT.PUT_LINE('기타 에러 발생'); 

  END; 

  /   

프로시저가 생성되었습니다. 

프로시저 실행 

SQL> SET SERVEROUTPUT ON ;   -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE PreException_Test(20); 

TOO_MANY_ROWS 에러 발생 

PL/SQL 처리가 정상적으로 완료되었습니다. 

※ TOO_MANY_ROWS 에러를 타는 이유? 

 - SELECT 문의 결과가  1개 이상의 행을 리턴하기 때문이다.. 

 - TOO_MANY_ROWS 를 피하기 위해서는  FOR문이나  LOOP 문으로  SELECT문을 처리해야 합니다. 

아래와 같이 바꾸면 에러가 발생하지 않습니다. 

     FOR   emp_list   IN 

         (SELECT empno, ename, deptno 

         FROM emp 

         WHERE deptno = v_deptno)    LOOP 

         DBMS_OUTPUT.PUT_LINE('사번  : ' || emp_list.empno); 

         DBMS_OUTPUT.PUT_LINE('이름  : ' || emp_list.ename); 

         DBMS_OUTPUT.PUT_LINE('부서번호  : ' || emp_list.deptno); 

       END LOOP; 

미리 정의되지 않은 예외(Non-Predefiend Exceptions) 

◈ STEP 1 : 예외의 이름을 선언(선언절)   

◈ STEP 2 : PRAGMA EXCEPTION_INIT 문장으로 예외의 이름과 오라클 서버 오류 번호를 결합(선언절)   

◈ STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)    

미리 정의되지 않은 예외 예제 

SQL> CREATE OR REPLACE PROCEDURE NonPreException_Test   

       IS 

          not_null_test EXCEPTION;     -- STEP 1 

          /* not_null_test 는 선언된 예외 이름   

              -1400 Error 처리번호는 표준 Oracle7 Server Error 번호  */ 

          PRAGMA EXCEPTION_INIT(not_null_test, -1400);        -- STEP 2 

        BEGIN 

          DBMS_OUTPUT.ENABLE; 

        -- empno를 입력하지 않아서 NOT NULL 에러 발생 

        INSERT INTO emp(ename, deptno) 

        VALUES('tiger', 30); 

        EXCEPTION 

        WHEN not_null_test THEN     -- STEP 3 

            DBMS_OUTPUT.PUT_LINE('not null 에러 발생  '); 

       END; 

        / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ;     -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE NonPreException_Test; 

not null 에러 발생 

PL/SQL 처리가 정상적으로 완료되었습니다. 

사용자 정의 예외(User-Defined Exceptions) 

◈ 오라클 저장함수  RAISE_APPLICATION_ERROR 를 사용하여 오류코드  -20000부터   

   -20999의 범위 내에서 사용자 정의 예외를 만들수 있습니다.   

◈ STEP 1 : 예외의 이름을 선언(선언절)   

◈ STEP 2 : RAISE 문을 사용하여 직접적으로 예외를 발생시킨다(실행절)   

◈ STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)    

사용자 정의 예외 예제  Procedure   

입력한 부서의 사원이  5명보다 적으면 사용자 정의 예외가 발생하는 예제 입니다. 

SQL>CREATE OR REPLACE PROCEDURE User_Exception   

        (v_deptno IN emp.deptno%type )   

      IS 

       -- 예외의 이름을 선언 

       user_define_error EXCEPTION;      -- STEP 1 

       cnt      NUMBER; 

     BEGIN 

       DBMS_OUTPUT.ENABLE;    

       SELECT COUNT(empno)   

       INTO cnt 

       FROM emp 

       WHERE deptno = v_deptno; 

       IF cnt < 5 THEN 

         -- RAISE 문을 사용하여 직접적으로 예외를 발생시킨다 

          RAISE user_define_error;          -- STEP 2 

       END IF; 

      EXCEPTION 

        -- 예외가 발생할 경우 해당 예외를 참조한다. 

       WHEN user_define_error THEN       -- STEP 3

           RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..'); 

    END;   

  / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE user_exception(10); 

BEGIN user_exception(10); END; 

 * 

1 행에 오류: 

ORA-20001: 부서에 사원이 몇명 안되네요.. 

ORA-06512: "SCOTT.USER_EXCEPTION", 줄  17에서 

ORA-06512: 줄  1에서 

 10 부서의 사원이  5보다 적기 때문에 사용자 정의 예외가 발생했습니다. 

SQL> EXECUTE user_exception(20); 

PL/SQL 처리가 정상적으로 완료되었습니다.  

20부서로 실행을 하면 에러가 발생하지 않는 것 을 알 수 있습니다.. 

SQLCODE, SQLERRM 

◈WHEN OTHERS 문으로 트랩(Trap)되는 오류들의 실제 오류 코드와 설명을 볼때 사용한다.   

◈ SQLCODE : 실행된 프로그램이 성공적으로 종료하였을때는 오류번호  0을 포함하며,   

                       그렇지 못할 경우에는 해당 오류코드 번호를 포함한다.   

◈ SQLERRM : SQLCODE 에 포함된 오라클 오류 번호에 해당하는 메시지를 가진다.   

SQLCODE Value  Description 

0       오류 없이 성공적으로 종료   

1       사용자 정의 예외 번호   

+100        NO_DATA_FOUND 예외 번호   

음수       위에 것을 제외한 오라클 서버 에러 번호   

SQLCODE, SQLERRM 예제 프로시저 

SQL> CREATE OR REPLACE PROCEDURE Errcode_Exception   

        (v_deptno IN emp.deptno%type )   

        IS 

         v_emp    emp%ROWTYPE ;   

        BEGIN   

          DBMS_OUTPUT.ENABLE; 

          -- ERROR 발생  for 문을 돌려야 됨 

         SELECT *   

         INTO v_emp 

         FROM emp 

         WHERE deptno = v_deptno; 

        DBMS_OUTPUT.PUT_LINE('사번  : ' || v_emp.empno);      

        DBMS_OUTPUT.PUT_LINE('이름  : ' || v_emp.ename);      

     EXCEPTION 

      WHEN OTHERS THEN 

          DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE)); 

          DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); 

  END;    

  / 

프로시져가 생성되었습니다.   

실행 결과   

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

SQL> EXECUTE Errcode_Exception(30); 

RR CODE : -1422 

ERR MESSAGE : ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다 

PL/SQL 처리가 정상적으로 완료되었습니다. 

위와 같이  SQLCODE, SQLERRM 을 사용하면 에러 코드와 에러 메시지를 볼 수 있습니다. 

package? 

  ◆ 패키지(package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는  PL/SQL   

      프로지져와 함수들의 집합 입니다 

  ◆ 패키지는 선언부와 본문 두 부분으로 나누어 집니다.   

패키지 선언부 

- 선언절은 패키지에 포함될  PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언 합니다.   

- 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용됩니다.   

- 즉 선언부에서 선언한 변수는  PUBLIC 변수로 사용 됩니다.   

패키지 본문 

- 패키지 본문은 패키지에서 선언된 부분의 실행을 정의 합니다.   

- 즉 실제 프로시져나 함수의 내용에 해당하는 부분이 옵니다. 

아주 간단한 패키지 예제입니다.   

4 개의 프로시저가 존재하고 있습니다.   

프로시저명  프로시저 기능  보기 

all_emp_info    모든 사원의   사원 정보  (사번, 성명, 입사일)  프로시저보기 

all_sal_info    모든 사원의   급여 정보  (평균급여, 최고급여, 최소급여)  프로시저보기

dept_emp_info    특정 부서의   사원 정보  (사번, 성명, 입사일)  프로시저보기 

dept_sql_info    특정 부서의   급여 정보  (평균급여, 최고급여, 최소급여)  프로시저보기 

위  4개의 프로시저를 가지고 패키지를 생성하겠습니다.   

선언부를 먼저 생성 합니다.   

package 예제  (선언부) 

SQL>CREATE OR REPLACE PACKAGE emp_info AS 

            PROCEDURE all_emp_info;                                            -- 모든 사원의   사원 정보 

            PROCEDURE all_sal_info;                                             -- 모든 사원의   급여 정보 

            PROCEDURE dept_emp_info (v_deptno IN   NUMBER) ;     -- 특정 부서의   사원 정보 

            PROCEDURE dept_sal_info (v_deptno IN   NUMBER) ;        -- 특정 부서의   급여 정보        

        END emp_info; 

Package created. 

선언부를 생성 하고 나서 본문 부분을 생성 합니다.   

package 예제  (본문) 

  SQL>CREATE OR REPLACE PACKAGE BODY emp_info AS 

                -- 모든 사원의   사원 정보   

                PROCEDURE all_emp_info 

                 IS         

                        CURSOR emp_cursor IS 

                        SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate 

                        FROM emp 

                        ORDER BY hiredate; 

                 BEGIN 

                       FOR   aa   IN emp_cursor LOOP 

                               DBMS_OUTPUT.PUT_LINE('사번  : ' || aa.empno); 

                                DBMS_OUTPUT.PUT_LINE('성명  : ' || aa.ename); 

                                DBMS_OUTPUT.PUT_LINE('입사일  : ' || aa.hiredate);         

                        END LOOP;         

                 EXCEPTION 

                        WHEN OTHERS THEN 

                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  '); 

                         END all_emp_info; 

                 -- 모든 사원의   급여 정보   

                PROCEDURE all_sal_info 

                IS                 

                        CURSOR emp_cursor IS 

                        SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal 

                        FROM emp; 

                                BEGIN         

                        FOR   aa   IN emp_cursor LOOP                 

                                DBMS_OUTPUT.PUT_LINE('전체급여평균  : ' || aa.avg_sal); 

                                DBMS_OUTPUT.PUT_LINE('최대급여금액  : ' || aa.max_sal); 

                                DBMS_OUTPUT.PUT_LINE('최소급여금액  : ' || aa.min_sal);                         

                        END LOOP; 

                EXCEPTION 

                        WHEN OTHERS THEN 

                                 DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  '); 

                END all_sal_info;  

                --특정 부서의   사원 정보 

                PROCEDURE dept_emp_info (v_deptno IN   NUMBER) 

                 IS 

                        CURSOR emp_cursor IS 

                        SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate 

                        FROM emp 

                        WHERE deptno = v_deptno 

                        ORDER BY hiredate;         

                 BEGIN 

                         FOR   aa   IN emp_cursor LOOP        

                                DBMS_OUTPUT.PUT_LINE('사번  : ' || aa.empno); 

                                DBMS_OUTPUT.PUT_LINE('성명  : ' || aa.ename); 

                                DBMS_OUTPUT.PUT_LINE('입사일  : ' || aa.hiredate);         

                        END LOOP;         

                 EXCEPTION 

                        WHEN OTHERS THEN 

                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  ');  

                 END dept_emp_info; 

                 --특정 부서의   급여 정보 

                PROCEDURE dept_sal_info (v_deptno IN   NUMBER) 

                IS                 

                        CURSOR emp_cursor IS 

                        SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal 

                        FROM emp  

                        WHERE deptno = v_deptno;                 

                BEGIN 

                        FOR   aa   IN emp_cursor LOOP                  

                                DBMS_OUTPUT.PUT_LINE('전체급여평균  : ' || aa.avg_sal); 

                                DBMS_OUTPUT.PUT_LINE('최대급여금액  : ' || aa.max_sal); 

                                DBMS_OUTPUT.PUT_LINE('최소급여금액  : ' || aa.min_sal);                         

                        END LOOP; 

                EXCEPTION 

                        WHEN OTHERS THEN 

                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생  '); 

                END dept_sal_info;           

        END emp_info; 

         / 

  Package body created. 

패키지의 실행 

패키지의 실행은 패키지 명 다음에  .을 찍고 프로시저냐 함수 명을 써 줍니다. 

먼저  set serveroutput on 을 실행한후.. 

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

다음 명령들을 실행해 보세요.. 

SQL> exec emp_info.all_emp_info; 

SQL> exec emp_info.all_sal_info; 

SQL> exec emp_info.dept_emp_info(10); 

SQL> exec emp_info.dept_sal_info(10);  

트리거란? 

◆ INSERT, UPDATE, DELETE 문이  TABLE 에 대해 행해질 때 묵시적으로 수행되는  PROCEDURE 

입니다. 

◆ Trigger 는  TABLE 과는 별도로  DATABASE 에 저장됩니다. 

◆ Trigger 는  VIEW에 대해서가 아니라  TABLE 에 관해서만 정의될 수 있습니다.   

- BEFORE : INSERT, UPDATE, DELETE 문이 실행되기 전에 트리거가 실행됩니다.   

- AFTER : INSERT, UPDATE, DELETE 문이 실행된 후 트리거가 실행됩니다.   

- trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있습니다.   

- FOR EACH ROW : 이 옵션이 있으면 행 트리거가 됩니다.   

-- 행 트리거  : 컬럼의 각각의 행의 데이터 행 변화가 생길 때마다 실행되며,   그 데이터 행의 실제 값을 

제어할 수 있습니다.   

-- 문장 트리거  : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할 수 없습니다. 

간단한 행 트리거 예제 

SQL>CREATE OR REPLACE TRIGGER triger_test 

BEFORE 

UPDATE ON dept 

FOR EACH ROW 

BEGIN 

DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값  : ' || : old.dname); 

DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값  : ' || : new.dname); 

END; 

/ 

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

-- UPDATE 문을 실행시키면.. 

PLSQL.pdf

SQL>UPDATE dept 

SET dname = '총무부' 

WHERE deptno = 30 

-- 트리거가 자동 실행되어 결과가 출력됩니당.   

변경 전 컬럼 값  : 인사과 

변경 후 컬럼 값  : 총무부 

1 행이 갱신되었습니다. 

간단한 행 트리거 예제 2 (PLSQL BLOCK 이 있는 트리거) 

SQL>CREATE OR REPLACE trigger sum_trigger 

  BEFORE 

  INSERT OR UPDATE ON emp 

  FOR EACH ROW 

DECLARE 

   -- 변수를 선언할 때는  DECLARE 문을 사용해야 합니다   

   avg_sal NUMBER; 

BEGIN 

   SELECT ROUND(AVG(sal),3)   INTO avg_sal   FROM emp; 

   DBMS_OUTPUT.PUT_LINE('급여 평균  : ' || avg_sal); 

END;   

/ 

트리거가 생성되었습니다. 

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용) 

-- INSERT문을 실행합니다.. 

  SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL) 

  VALUES(1000, 'LION', 'SALES', SYSDATE, 5000); 

-- INSERT문을 실행되기 전까지의 급여 평균이 출력됩니다.   

 급여 평균  : 2073.214 

  1 개의 행이 만들어졌습니다. 

출처    (http://www.oracleclub.com) 

오라클 동작

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분