• 데이터베이스 프로그래밍
• 다른 PLSQL
  • PL/SQL이란?
  • PL/SQL의 장점
    • Declarative Section(선언부)
    • Executable Section(실행부)
    • Exception Handling Section(예외처리)
  • PL/SQL 프로그램의 작성 요령
  • PL/SQL 블럭의 유형
  • Anonymous Block (익명 블록)
  • Procedure (프로시저)
  • Function (함수)
  • 프로시저(Procedure)란?
  • 프로시저 문법
  • 프로시저 작성 예제
  • 프로시저 실행 예제

데이터베이스 프로그래밍

업무 시스템 구축의 흐름

계획 수립 -\> 데이터 모델링(프로세스 모델링) -\> 물리DB(AP 설계) -\> DB구축(AP 개발) -\> DB 튜닝(AP 테스트) -\> DB 전환(AP 설치)

데이터 모델링의 순서(설계 과정) : 엔티티타입 정의 -\> 엔티티타입간 관계정의 -\> 식별자정의(실질식별자 정의, 보조식별자) -\> 세부사항 정의(속성상세 정의, 정규화, 도메인 정의, 속성 규칙 정의) -\> 통합화(엔티티타입 통합, 안정성/확장성분석) -\> 데이터모델 검증(엔티티타입 검증, 관계 검증, 속성 검증)

관계형 데이터베이스 모델링의 3요소

1. 엔티티 : 구별할 수 있는 객체(사람, 자동차, 계약, 주문 등)
2. 선정 방법 -\> 명사를 구분하고 엔티티타입의 특성이거나 속성의 값은 제거하고 개념이 광범위한 것 제거한다.
3. Key엔티티(고객, 상품, 자재, 부서, 사원) - 자신의 부모를 안 가짐. 데이터 발생의 주체나 목적어. 태초부터 창조된 실체
4. Main엔티티(신용카드, 계약, 단가, 카드) - 부모로부터 태어난 실체지만 하위에 자손을 거느린 실체, 많은 자손.
5. Action엔티티(카드 사용내역, 계약 변경, 공사 내역 - 말 그대로 액션) - 실제 발생하는 업무. 반드시 부모를 가짐. 자주 변경.
6. 수퍼 타입과 서브 타입으로 통합할 수도 있고, 세분화할 수도 있다. 수퍼타입(엔티티)에 직원번호, 직원명이 오면 서브타입(엔티티)에는 부서명, 근무시간, 계약기간등이 온다.
7. 관계 : 2개 이상의 개체를 연결한 집단화로 이루어진다(결혼 - 남자와 여자, 계좌 - 은행 상품과 고객, 주문 - 고객과 고객의 물품 등) 1. A.

선정 방법 -\> 동사를 구분하고 아예 고객에게 질문해서 모델을 검토한다.(하나/여러, ~만/항상, 속할 수 있다/소속되어 있다) + 직접 모델링을 해본다. 그려본다.

하나의 주문목록에는 / 한 개의 목록을 / 항상 / 포함하고 / 한 목록은 / 여러 개의 주문목록에 의해 / 포함될 수 있다.

1. 순환관계(재귀 관계)

1.

• 순환관계(Recursive Relationship) : 하나의 엔티티가 다른 엔티티가 아닌 자기 자신과 관계를 맺는 관계(하나의 순환 엔티티는 각 엔티티의 모든 속성을 포함해야 한다, 순환 모델은 필수 관계로 취급될 수 없고(무한 loop 발생), 반드시 선택 관계이다.)

1. B. Arc관계(상호배타적 관계 - Arc, Mutually Exclusive) : 어떤 엔티티가 두 개 이상의 다른 엔티티와 관계를 가지는 것을 배타적(Exclusive) 관계 혹은 아크 관계라고 한다. 이러한 아크 관계는 동일한 의미의 관계가 서로 다른 하나 이상의 엔티티와 배타적으로 관계를 갖고 있을 때 이를 하나로 통합함으로써 발생하게 된다. (아크 내에 있는 관계는 보통 동일하고 아크는 반드시 하나의 엔

티티에만 속해야 한다.

티티에만 속해야 한다.

1. BOM(Bill of Materials) 관계
   • BOM관계의 모델을 네트워크 구조라 한다. M:M 순환 구조는 BOM 모델이라 알려지게 되었다. 새로운 관계 엔티티를 추가하여 두 개의 일대 다 관계로 구성된 모델로 구체화 한다.
2. 속성 : 속성은 개체 또는 관계의 기초적인 성질을 나타냄(사람 - 이름, 생년월일, 성별, 나이 등)
3. 선정 방법 -\> 개발 이전까지 찾아야 하므로 완벽하게 안 찾아도 됨.
4. 속성 정의 유의사항 1. 의미가 명확해야 한다. 2. 최대한 복합명사를 사용한다. 3. 표준 단어를 쓴다. 4. 임의로 만들어서 사용은 하지 않는다.
5. 일단 최소 단위까지 분할한 후에 필요에 따라 통합한다.(원자값) - 일자같은 것들도. 1. 매출일자 -\> 매출년도, 매출월, 매출일 2. 주소 -\> 지역주소, 상세주소 3. 전화번호 -\> 지역번호, 국번, 개별번호
6. 속성 Notation 1. $ -\> 비상속 2. # -\> 실질 식별자(PK) 3. * -\> Not null 4. (#) -\> 보조식별자 5. o -\> 선택 속성
7. 속성의 3종류 1. 기본속성 -\> 업무상 수집된 기본속성 2. 설계속성 -\> 업무에 필요한 정보를 주기위해 시스템에서 고안한 속성(코드, 일련번호) 3. 유도속성 -\> 다른 속성에 의해 계산되거나 영향을 받아 생성된 속성(금액총합, 이자)
8. 속성 111 원칙 -\> 한 속성값은 한 시점에 한 개의 값만을 가질 수 있다.
9. 속성에 의한 엔티티 타입 생성 -\> 속성이 시간에 따라 여러 개의 값을 가질 수 있다. 새로운 엔티티타입을 생성한다. (주소가 변경될 때 마다 주소가 바뀌는 것 처럼)

이상적인 데이터 모델은 데이터 규칙이 데이터에 존재할 때이다. 누가 봐도 보기 편할 때. (규칙을 정확하게 표현하는 ERD)

식별자(UID - Unique IDentifier) : 엔티티 타입내에서 엔티티들을 구분할 수 있는 구분자이다.

• 모든 ENTITY는 반드시 UID를 가져야 한다.
• UID를 가지지 못하면 ENTITY가 아니다.
• 명칭이나 내역은 주식별자로 선정하기에 부적절하다.

수강에서 학번과 번호? 그 두 개가 식별자가 된다.

대부분의 키의 엔티티는 하나의 애트리뷰트(보통 인조 식별자)로 구성된다. 왜냐면 여러 개가 UID가 되면 다 참조시켜야되기 때문에?

본질 식별자 : 집합의 본질을 명확하게 설명할 수 있는 의미상의 주어(예:주민등록번호)

인조 식별자 : 인조 속성을 영입하여 공식적인 식별자로 인정하는 식별자(예:학번, 군번)

실질 식별자 : 인스턴스를 식별하기 위해 공식적으로 부여된 식별자(인조 식별자나 본질 식별자는 실질 식별자가 될 수 있다.

보조 식별자 : 원래의 식별자를 대체할 수 있는 또 다른 속성들(예:학번을 실질로 쓰고 주민등록번호를 보조 식별자로)

후보 식별자 : 각 인스턴스를 유일하게 식별할 수 있는 속성(들). - 고객명+주민등록번호

식별관계(Bar가 있음) : 부모엔티티의 식별자(UID)가 자식엔티티의 식별자(#, primary key)로 상속되는 것. - 바가 있는 관계선을 만들려면 관계선에서 1:다 identity를 써주면 된다. 상속된 UID식별자를 가장 위로 올려줘야 된다.

비식별관계 : 부모엔티티의 식별자(UID)가 자식엔티티의 일반속성(그냥 동그라미)으로 상속되는 것

그러면 Bar는 언제 해주는 걸까?

• C 엔티티가 부모의 부모 엔티티인 A의 UID 정보가 필요할 때 써준다. C 엔티티의 자식들도 A의 UID 정보가 필요할 때. 자식 엔티티 중 일부만이 조부 엔티티의 UID 정보로써 원한다면 상속을 UID Bar없이 하고 물리적 단계에서 접근 경로를 단축시킨다.

함수적 종속성(Functional Dependency : FD)의 정의

데이터들이 어떤 기준 값에 의해 항상 종속되는 현상. 속성 집합 X의 값이 Y의 값을 유일하게 결정한다면 (X -\> Y이면) 'Y는 X에 함수적으로 종속된다'고 합니다.

이게 있으면 학번과 과목번호가 성적을 결정하기 때문에 '완전 함수적 종속성'이다. 그런데 여기서 두 개의 키 중에 학번은 학과를 알 수 있다. 하나의 키에만 종속하기 때문에 '부분 함수적 종속성'이다.

학번을 통해 지도교수를 알 수 있고 지도교수를 통해 학과를 알 수 있다면 학번을 통해 학과를 알 수 있게 된다. 이게 바로 '이행 함수적 종속성'이다.

이상현상(Anomaly) 데이터의 중복성으로 인해 릴레이션을 조작할 때 발생하는 예기치 못한 비합리적 현상.

데이터의 삽입(Insert), 삭제(Delete), 갱신(Update)할 때 일어난다.

1. 삽입이상 - 어느 특정 학생이 어떤 과목을 등록할 때까지는 그 학생의 지도교수가 누구라는 사실을 삽입하지 못 한다.
2. 삭제이상 - 튜플이 하나만 있는 상황에서 과목을 삭제하면 지도교수 정보도 같이 삭제해야 된다.
3. 갱신이상 - 지도교수 속성값이 여러 번 중복되어 변경 시 문제가 발생한다. 하나만 갱신하고 나머지는 갱신하지 않아 발생하는 데이터 불일치 현상 발생이 가능하다.

정규화

이상 현상을 야기하는 속성간의 종속관계를 제거하기 위해 엔티티를 작은 여러 엔티티로 무손실 분해하는 과정이라고 할 수 있다. DB 설계의 최적화 과정이라 하기도 한다.

정규화 단계별 규칙을 사용하여 ATTRIBUTE의 위치를 적절히 하는 것

제1, 제2, 제3 정규형까지 간다. 그 뒤로는 웬만해서 하지 않는다.

기본 키는 릴레이션의 모든 속성에 대해 결정자이다.

제 1 정규형의 정의

• 릴레이션 R 에 속한 모든 도메인이 원자값(Atomic Value)만으로 되어 있는 경우
• 모든 Attribute는 반드시 하나의 값을 가져야 한다.(반복형태가 있어서는 안됨)

//하나의 주문 번호에 한 번에 많은 제품을 사서(제품코드들) 주문번호가 중복될 수 있다. 그래서 나눈다. 주문상세에서 주문번호와 제품코드가 동시에 기본키가 되도록. 그러니까 처음 테이블에는 주문번호와 제품코드의 테이블이 주문번호가 101이면 제품코드에는 M21, K32 이런 식으로 중복이 되어있다는 소리이다.

제 2 정규형의 정의

• 릴레이션 R 이 1NF이고 릴레이션의 기본키가 아닌 속성들이 기본키에 완전히 함수적으로 종속할 경우
• 모든 Attribute는 반드시 UID 전부에 종속되어야 한다.(UID 일부에만 종속되어서는 안됨 - 부분 함수적 종속성을 제거해야한다)

//제품명은 제품코드에 종속되어 있다. 제품 코드가 달라지면 제품명도 달라지니까. 즉, 기본키가 아닌 속성들이 기본키에 종속되어 있다.

제 3 정규형의 정의

• 릴레이션 R 이 2NF이고 기본키가 아닌 모든 속성들이 기본키에 대하여 이행적 함수 종속성 (Transitive FD) 의 관계를 가지지 않는 경우, 즉 기본키 외의 속성들간에 함수적 종속성을 가지지 않는 경우
• UID가 아닌 모든 Attribute 간에는 서로 종속될 수 없다.(Attribute 간 종속성 배제)

//공급자코드는 공급자명과 공급자주소를 알고 있다. 그런데 키인 주문번호가 공급자코드를 알고 있다. 그러면 이행적 종속성이 된다. 키가 공급자명까지 종속하고 있다는 것이니까.

개념적 설계와 논리적 설계를 '논리 모델링'이라고 한다.

DA#은 바커표기법을 따르고 ERWin은 I/E표기법을 따른다.

필수 속성 -\> 값이 무조건 있어야 됌.(not null)

I/E 표기법에서는 고객번호(기본 키) 아래에 필수 속성을 표시할 수 없고 그냥 이름만 써내려 간다. 그래서 사실상 DA#5가 더 구체적이고 보기 편하다.

데이터의 정합성(어쩔 수 없이 NULL값을 허용해줘야 하는 상황)을 유지하기위해서는 계속 세분화 시켜야 한다. 다만 이렇게 할 경우 관리할 테이블이 많아진다.(성능을 양보해줘야 된다. 테이블이 1개면 셀렉트 한 번으로 가능하지만 NULL값이 많이 들어감.)

1개를 프로젝션하면 2개로 만들 수 있고 2개를 조인하면 1개를 만들 수 있고.

성능과 데이터 정합성(데이터의 정합성이란 무결성과 같다. 데이터의 정확성과 일관성을 유지하는 것이다. )의 관계는 반비례 관계에 있다.

조인을 하면 카티션 곱을 먼저 해야되기 때문에 응답이 늦어질 수 있다. 그래서 보통 테이블을 1개로 가는 경우가 많다.

개체 무결성 : 기본 키에 null값이 들어갈 수 없다.(#에는 데이터가 무조건 들어가야 한다.)

참조 무결성 : 조인이 가능하도록 해야된다.(찾아가도록 외래키를 넣어야 함)

\<SQL\>

논리모델링이 주어졌을 때 외래키가 인지 아닌지 구별해줘야 한다. ex) SCHEDULE에 HOMETEAM_ID는 외래키가 아니다.

SELECT D.*, E.* FROM DEPT D, EMP E;

조인을 하는데 조건이 없다면 D가 가지고 있는 모든 칼럼과 E가 가지고 있는 모든 칼럼을 출력한다. 즉, 카티션 프로덕트가 되어버린다.

여기서 WHERE 절을 주면 카티션 프로젝트에서 SELECTION연산자(시그마 - 수평적 부분집합)를 줘서 빼주게 된다.

수직적 부분집합(파이)

자연 조인은 아래처럼 한다.

SELECT E.*, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

SELECT E.EMPNO, E.ENAME, D.DNAME, E.SAL FROM EMP E, DEPT D WHERE E.DEPTNO

= D.DEPTNO AND E.SAL \> 1200;

와

SELECT EMPNO, ENAME, DNAME, SAL FROM EMP INNER JOIN DEPT ON EMP.DEPTNO

= DEPT.DEPTNO AND SAL \> 1200;

는 같다. 밑에서 EMP.을 그대로 써줘도 되고 ON 뒤에 그냥 AND로 조건을 해줘도 되고 ON 뒤에 다시 WHERE절을 넣어줘도 된다.

VIEW는 가상 테이블을 말한다.

FROM절의 테이블 순서가 결과에 영향을 미칠까?

SELECT * FROM DEPT INNER JOIN EMP E ON DEPT.DEPTNO = E.DEPTNO; 와

SELECT * FROM EMP E INNER JOIN DEPT ON DEPT.DEPTNO = E.DEPTNO; 의 차이(WHERE 조인도 똑같다)는 결과는 같지만 열의 순서가 다르다는 것.

//그리고 INNER JOIN이나 RIGHT OUTER JOIN등을 할 때 ON을 써주고 WHERE절은 또 따로 쓴다?

NATURAL JOIN(중복 칼럼을 제거한다. 여기서는 DEPTNO)

SELECT * FROM EMP NATURAL JOIN DEPT; //신기한 건 조인하는 DEPTNO가 열의 첫 번째로 온다. 그리고 ON이 따로 필요 없다. 그냥 써주면 된다.

UNION, MINUS, INTERSECT 등을 쓸 때 ()를 잘 쳐야 한다.

()의 중요성

다음 문제를 풀기 위해서 나는 두 개의 가로()를 안으로 치면 되겠지 라고 생각했다.

'전남'에 속한 선수 중에서 포지션이 'GK'인 선수들과 '서울'에 속한 선수 중에서 몸무게가 65이상인 선수들을 합집합하시오. ( 연고지명, 선수명, 포지션, 몸무게 순으로 (선수명, 포지션)순으로 오름차순으로 출력하시오 ),

가) 합집합 대신에 조건절에 이용한 일반 SQL문으로 변경하여 결과를 비교하시오.

그래서 아래처럼 작성했다.

SELECT REGION_NAME, PLAYER_NAME, POSITION, WEIGHT FROM TEAM, PLAYERWHERE (REGION_NAME='전남' AND POSITION='GK') OR (REGION_NAME='서울' AND WEIGHT \>= 65) AND TEAM.TEAM_ID=PLAYER.TEAM_ID;

그랬더니 더 많이 나왔다. 그 이유는 A + B * C가 되었기 때문이었다. A는 더해지고 B만 C로 인해 조인이 된다. 가로를 잘 쳐줘야 한다.

SELECT REGION_NAME, PLAYER_NAME, POSITION, WEIGHT FROM TEAM, PLAYERWHERE ((REGION_NAME='전남' AND POSITION='GK') OR (REGION_NAME='서울' AND WEIGHT \>= 65)) AND TEAM.TEAM_ID=PLAYER.TEAM_ID;

위와 같이 전체적으로 가로를 쳐주면 (A + B) * C로서 전체에 대해서 조인하고 고르기 때문에 정답이 된다. 또 다른 해결방법이 있는데

select t.region_name 연고명, p.player_name 선수명, p.position 포지션,p.weight 몸무게

from team t join player p on (t.team_id=p.team_id)

where (t.region_name = '전남' and p.position = 'GK') or (t.region_name = '서울' and p.weight \>= 65) order by 2, 3;

위처럼 해주면 ON에 대한 조건이라서 오류가 뜨지 않는다. WHERE가 딱 조건이 되니까. WHERE를 안 주고 AND를 줄 수 있으나 AND를 주게 되면 위와 똑같은 오류가 뜨게 된다.

시스템 사용자 생성 및 권한 부여( DCL - Data Control Language)

conn system/system // system사용자로 연결

create user john identified by john123; //john이라는 id와 john123이라는 passwd

conn john/john123

//위 코드를 차례로 실행하면 세션 연결 권한이 없다고 뜬다. 새로 만든 사용자 john에게 연결 권한을 줘야 한다.

conn system/system

grant create session, create table, create view, CREATE TRIGGER to john; //grant 명령어로 권한을 부여한다.

conn john/john123

create table test_tb

//테이블을 만들려고 하니까 테이블 영역에 대한 권한이 없다고 뜬다.

conn system/system

column file_name FORMAT 40

SELECT tablespace_name, file_name, bytes FROM dba_data_files; //행을 선택하고

ALTER USER john QUOTA 5M ON USERS DEFAULT TABLESPACE USERS; //john이라는 사용자로 바꿔준다.

CONN john/john123

create table test_tb(t_id number(3), t_name char(10));// 테이블 생성

select owner, table_name from all_tables where owner = 'john'; //실행하면 잘 뜬다.

DROP TEST_TB;

자주 사용되는 SQL*PLUS 명령어

LIST - SQL 버퍼에 있는 모든 행을 표시

CHANGE /old/new - 현재 행의 old를 new로 변경

EDIT 파일이름 - 지정한 파일을 편집기로 불러온다.

START 파일이름 - 지정한 파일(.sql)을 실행한다.

SPOOL 파일이름 - 조회화면을 저장하기 시작한다.

SPOOL OFF - 스풀을 해제한다.

RUN - 버퍼에 있는 SQL문을 실행한다.

EXIT - SQL*PLUS 툴을 종료한다.

JOIN 수행 원리 - SQL 전문가 가이드

조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다. SQL문에서 FROM 절에 두 개 이상의 테이블이 나열될 경우 조인이 수행된다. FROM절에 세 개 이상의 테이블이 있더라도 두 개만 실행되고 실행된 조인 결과와 나머지 테이블을 조인 시키는 것이다. 조인하는 테이블의 순서는 경우에 따라 다르다.

실행계획(Execution Plan)의 분석

SQL Developer에서 SQL 문장을 선택하여 실행계획 버튼을 클릭하면 분석할 수 있다. 만약, 권한이 부족할 경우에는 system으로 로그인하여 scott에게 권한을 부여하면 된다.

grant SELECT _CATALOG_ROLE to scott

JOIN에는 3가지 JOIN이 있다. NESTED LOOP JOIN, HASH JOIN, SORT MERGE JOIN이다. 우리가 쓴 SQL문장을 실행하면 Optimizer가 세 가지 중에서 가장 COST가 적은 걸 선택해서 실행한다. 대부분은 NLJ, HASH JOIN이다.

• 자신보다 하위 레벨이 있으면 하위레벨부터
• 같은 레벨이라면 위(상)의 로우 소스부터 처리된다.

FROM절에 있는 테이블의 순서를 바꿔도 Optimizer가 알아서 처리하기 때문에 추가하지 않는 한, 똑같은 선행테이블이 온다.

1. Nested Loop Join(NLJ) - 선행테이블의 조건을 만족하는 행을 추출하여 후행테이블을 읽으면서 조인을 수행한다. 이 작업은 선행테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행한다. - 랜덤방식으로 데이터를 액세스한다. 조인이 성공하면 바로 나타나니 가능한 빨리 화면에 보여줄 때 쓴다.
2. (1)선행테이블(DEPT)에서 주어진 조건(D.LOC \>= 'DALLAS' 같은…)을 만족하는 행을 찾는다. - TABLE ACCESS(FULL) DEPT
3. (2)선행테이블에서 조인 키 값을 가지고 후행테이블에서 조인을 수행한다. (인덱스에서 추출한 레코드 식별자를 이용하여 후행테이블을 액세스) 그리고 그 결과를 추출 버퍼에다 넣는다. //추출 버퍼는 SQL문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출 버퍼에 결과가 모두 차거나 채울 게 아예 없으면 결과를 사용자에게 반환한다. - INDEX(UNIQUE SCAN) PK_DEPT
4. (3)선행테이블의 조건을 만족하는 모든 행에 대하여 (1), (2)의 작업을 반복한다. - TABLE ACCESS(BY INDEX ROWID) DEPT
5. Hash Join - 조인을 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하면서 조인을 수행한다. 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 쓸 수 있다. 그리고 해쉬 함수를 이용하기 때문에 동등하게 =로 수행하는 조인, 동등 조인의 경우에만 쓸 수 있다. 해쉬 테이블은 메모리에 생성이 된다. 즉, 선행테이블을 통해 해쉬 테이블을 만들고, 후행테이블의 모든 행을 해쉬 테이블에 통과시켜 일치하는 행을 찾는다.
6. (1)선행테이블에서 주어진 조건을 만족하는 행을 찾는다.
7. (2)조인 키를 기준으로 해서 해쉬 함수를 적용하여 해쉬 테이블을 생성. 이걸 반복 수행한다.
8. (3)마찬가지로 후행 테이블에서 주어진 조건을 만족하는 행을 찾음.
9. (4)후행테이블의 조인 키를 기준으로 해서 해쉬 함수를 적용하여 해당 버킷을 찾음(해쉬값의 존재여부). 이걸 반복수행.
10. (5)해슁된 두 테이블의 ROW를 조인한다. 그리고 그 해쉬테이블이 추출 버퍼로 간다. 이후, 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행?

select t.team_name, s.stadium_name, sc.sche_date, sc.home_score

from team t, stadium s, schedule sc

where t.stadium_id = s.stadium_id and s.stadium_id = sc.stadium_id;

두 개의 테이블을 조인할 때 그림은 아래와 같다.

1. 1TEAM 테이블이 선행 테이블이 되어 가장 먼저 FULL SCAN방식으로 처리된다(①). - - TABLE ACCESS(FULL) TEAM꺼.
2. 2STADIUM 테이블과 1:1 관계이므로 TEAM 테이블의 STADIUM_ID와 같은 로우(row)들을 FULL SCAN방식으로 찾는다(②). - TABLE ACCESS(FULL) STADIUM꺼.
3. 3해슁된 두 테이블의 ROW들을 JOIN 한다(③). ( 위 그림에서 ⑤번 ) - HASH JOIN
4. 4JOIN된 버퍼의 테이블의 STADIUM_ID와 같은 로우들을 SCHEDULE 테이블에서 FULL SCAN방식으로 찾는다(④). - TABLE ACCESS(FULL) SCHEDULE꺼.

5. 5해슁된 두 테이블의 ROW들을 JOIN 한다(⑤). ( 위 그림에서 ⑤번 ) - HASH JOIN

6. Sort Merge Join(SMJ) - 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다. 그리고 순서대로 양쪽 테이블(선행, 후행)에서 한 번 읽어서 연결하여 운반한다. (/*+ USE_MERGE (e, d) */의 사용으로 DBMS는 NLJ을 사용한 조인을 수행하지 않고 강제로 SMJ를 시킬 수 있다.) - 랜덤이 아닌 스캔 방식으로 데이터를 읽고 넓은 범위의 데이터를 처리할 때 좋다. 다만, 정렬할 데이터가 너무 많아 임시 메모리로 가게 되면 성능이 떨어진다. 그리고 조인 칼럼의 인덱스를 사용하지 않아 조인 칼럼의 인덱스가 없어도 사용할 수 있는 조인 기법이다.
7. (1)선행 테이블에서 조건에 맞는 행을 찾는다.
8. (2)DEPTNO 기준으로 정렬 작업을 수행한다.
9. (3)1,2를 반복수행한다.
10. (4)후행 테이블에서 조건에 맞는 행을 찾는다.
11. (5)EMPNO 기준으로 정렬 작업을 수행한다.
12. (6)3, 4 작업을 반복 수행한다.
13. (7)정렬된 결과를 이용하여 조인을 수행한 후 조인에 성공하면 추출 버퍼에 넣는다.

실행 계획에서 COST를 보면 큰지, 적은지 판단해 누가 더 좋은 성능을 내는지 알 수 있다. DBMS의 Optimizer는 이 쿼리에 대하여 NLJ, HJ, SMJ을 사용하여 여러 가지 실행계획을 세운 다음 각 계획별 비용이 가장 낮은 실행계획을 선택하게 된다고 할 수 있다.

\<DA#5 사용법\>

A/R 연결과 wordic에서 db도 연결 둘 다 연결

분류체계선택에서 대학뭐시기 하고 선택

용어 자동분석. 도메인을 선택해줘야됨

다음다음 누르다가 논리명 물리명 반영(도메인 포함)

데이터에 스크립트 생성있음.

\<ERWin 사용법\>

1. model 탭에 model properties를 들어간다.

2. Notation 탭에 들어가서 IE를 선택한다.

3. 아까 들어간 model properties에 들어가서 RI(Reference Integrity - 참조 무결성) Default 탭에 들어간다. NONE은 아무 액션도 안 하겠다. RESTRICT는 제한을 걸겠다는 의미. 삭제를 못하도록 하거나 안 되도록. CASCADE는 연쇄작용. 다 삭제해버림.

4. RI Default탭은 건드리지 말기.

5. ERWin은 다르다. IE표기법에서 관계선에 O가 있으면 Optional이다. 1 : 1이다라는 걸 말해주고 \< : 다를 의미한다. 이게 다 있으면 1: 다 관계다. Optional이면 null값이 들어갈 수 있다는 말.

비식별관계는 점선이다. 실선은 식별관계.

부서만 있을 수 있다. 그러나 사원은 반드시 부서가 있어야한다.

다:다 관계. 사원도 여러 부서를 옮길 수 있다.

식별관계를 풀고 키를 줘서 기본 키로 만들 수 있다.

model property 탭에 가서 Defaults를 가보면 CHAR(18)로 되어있다. 다른 건 알아서 모두 설정해줘야 한다. 더블 클릭해보면 Datatype탭이 있다.

다른 PLSQL

20171211 PL/SQL 작성

문제 3번을 SQL Developer에 넣는다.

SET VERIFY OFF

SET SERVEROUTPUT ON

ACCEPT p_ename PROMPT '조회하고자 하는 사원의 이름을 입력하시오 : '

DECLARE

TYPE emp_record_type IS RECORD(

v_empno emp.empno%TYPE, //%타입은 EMP 타입의 데이터 타입을 상속받아서 하는 것. 그래서 다 같게 된다.

v_ename emp.ename%TYPE,

v_job emp.job%TYPE,

v_mgr emp.mgr%TYPE,

v_hiredate emp.hiredate%TYPE,

v_sal emp.sal%TYPE,

v_comm emp.comm%TYPE,

v_deptno emp.deptno%TYPE);

emp_record emp_record_type;

v_ename emp.ename%TYPE := '&p_ename';

BEGIN

SELECT *

INTO emp_record //셀렉트 한다음에 INTO절에 할당한다. emp_record는 위에 DECLARE 된 것.

FROM emp

WHERE ename = UPPER(v_ename);

LTRIM(TO_CHAR(emp_record.v_sal,'$999,990.00')));

LTRIM(TO_CHAR(NVL(emp_record.v_comm,0),'$999,990')));

EXCEPTION

WHEN NO_DATA_FOUND THEN

WHEN TOO_MANY_ROWS THEN

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러 입니다.');

END;

/

SET VERIFY ON

SET SERVEROUTPUT OFF

앞은 레코드를 선언한 것이고 사원 테이블에 있는 모든 값을 할당하고 출력하는 것.

Run SQL Command Line에서 Tiger로 접속한 후, 아까 저장한 sql파일을 실행하면 그 프로시저가 실행된다(경로까지 써줘야 한다.)

PL/SQL에서 사용가능한 SQL 문장.

PL/SQL에는 INTO 절이 필수다.

3장의 문제 1번

SET VERIFY OFF

SET SERVEROUTPUT ON

ACCEPT p_name PROMPT '사원의 이름을 입력하시오 : '

DECLARE

    v\_name                emp.ename%TYPE := UPPER('&p\_name');         //대문자로 변경시킨다.

    v\_sal                emp.sal%TYPE;

    v\_hiredate        emp.hiredate%TYPE;

BEGIN

    SELECT sal,hiredate         //sal과 hiredate를 검색해서 넣어라~

            INTO v\_sal,v\_hiredate

            FROM emp

            WHERE  ename = v\_name;

    DBMS\_OUTPUT.PUT\_LINE('급  여 : ' || LTRIM(TO\_CHAR(v\_sal,'$999,999')));

    DBMS\_OUTPUT.PUT\_LINE('입사일 : ' || TO\_CHAR(v\_hiredate,'YYYY-MM-DD'));

EXCEPTION

    WHEN NO\_DATA\_FOUND THEN

            DBMS\_OUTPUT.PUT\_LINE(v\_name || '는 자료가 없습니다.');

    WHEN TOO\_MANY\_ROWS THEN

            DBMS\_OUTPUT.PUT\_LINE(v\_name || '는 자료가 여러행이 존재합니다.');

    WHEN OTHERS THEN

            DBMS\_OUTPUT.PUT\_LINE('기타 에러가 발생했습니다.');

END;

/

SET VERIFY ON

SET SERVEROUTPUT OFF

제 4장 PL/SQL의 제어문

문제 3번

반복문은 FOR LOOP

제 5장 CURSOR

명시적 커서인 프로그래밍을 해보는 것. 커서를 만들 때 선언하는 것. 다중 행. ALL FROM EMP를 하면 다 검색이 된다. 일시적으로 저장하기 위해서.

명시적으로 CURSOR를 선언하기 위해서 CURSOR 문장을 사용한다. 질의 내에서 변수를 참조할 수 있지만 CURSOR 문장 전에 선언되어야 한다.

문제 1번을 가져옴.

SET VERIFY OFF

SET SERVEROUTPUT ON

ACCEPT p_deptno PROMPT ' 부서번호를 입력하시오 : '

DECLARE

    v\_deptno        emp.deptno%TYPE := &p\_deptno;         //&는 syntax다. 할당해줌.

    v\_empno                emp.empno%TYPE;         //타입을 그대로 가져옴.

    v\_ename                emp.ename%TYPE;

    v\_sal                emp.sal%TYPE;

    v\_sal\_total        NUMBER(10,2) := 0;

    CURSOR emp\_cursor IS

            SELECT empno,ename,sal         //emp\_cursor의 데이터 셋.

                    FROM emp

                    WHERE deptno = v\_deptno

                    ORDER BY empno;

BEGIN

    OPEN emp\_cursor;         //얘를 오픈함.

    DBMS\_OUTPUT.PUT\_LINE('사번    이 름         급   여');

    DBMS\_OUTPUT.PUT\_LINE('----  ----------  ----------------');

    LOOP

            FETCH emp\_cursor INTO v\_empno,v\_ename,v\_sal;         //펫치에 의해서 emp number는 v number에... 할당됨.

            EXIT WHEN emp\_cursor%NOTFOUND;

            v\_sal\_total := v\_sal\_total + v\_sal;

            DBMS\_OUTPUT.PUT\_LINE(RPAD(v\_empno,6) ||

            RPAD(v\_ename,12) || LPAD(TO\_CHAR(v\_sal,'$99,999,990.00'),16));

    END LOOP;

    DBMS\_OUTPUT.PUT\_LINE('----------------------------------');

    DBMS\_OUTPUT.PUT\_LINE(RPAD(TO\_CHAR(v\_deptno),2) || '번 부서의 합    ' ||

            LPAD(TO\_CHAR(v\_sal\_total,'$99,999,990.00'),16));

    CLOSE emp\_cursor;

END;

/

SET VERIFY ON

SET SERVEROUTPUT OFF

8장 SUBPROGRAM & PROCEDURE

PL/SQL을 지원하는 어떤 툴이나 언어에서도 SUBPROGRAM(프로시저와 함수)을 실행할 수 있다. PL/SQL 내부에서 식의 일부로서 함수를 실행할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Procedure를 실행한다.

PL/SQL에는 프로시저와 함수라는 두 가지 유형의 SUBPROGRAM이 있다.

아래는 문제 1번인데 잘못됐다고 하네?

CREATE OR REPLACE PROCEDURE emp_input( //이제 그 파라미터 값만 넣어주면 함수로 작동.

    v\_name        IN         emp.ename %TYPE,

    v\_job        IN         emp.job %TYPE,

    v\_mgr        IN         emp.mgr %TYPE,

    v\_sal        IN         emp.sal %TYPE)

IS

    v\_comm                emp.comm%TYPE;

    v\_deptno                emp.deptno%TYPE;

    manager\_error        EXCEPTION;

BEGIN

    IF UPPER(v\_job) NOT IN ('PRESIDENT','MANAGER','ANALYST',

                            'SALESMAN','CLERK') THEN

            RAISE manager\_error;

    ELSIF UPPER(v\_job) = 'SALESMAN' THEN

            v\_comm := 0;

    ELSE

            v\_comm := NULL;         //세일즈맨이 아니면 여기다 넣음.

    END IF;

    SELECT deptno

            INTO v\_deptno

            FROM emp

            WHERE empno = v\_mgr;

    INSERT INTO emp

            VALUES (empno\_sequence.NEXTVAL,v\_name,UPPER(v\_job),         //sequence파일이 있어야 한다.

                    v\_mgr,SYSDATE,v\_sal,v\_comm,v\_deptno);

EXCEPTION

    WHEN manager\_error THEN

            DBMS\_OUTPUT.PUT\_LINE('담당 업무가 잘못 입력되었습니다.');

    WHEN NO\_DATA\_FOUND THEN

            DBMS\_OUTPUT.PUT\_LINE('입력한 MANAGER는 없습니다.');

    WHEN OTHERS THEN

            DBMS\_OUTPUT.PUT\_LINE('기타 에러입니다.');

END;

/

문제 2번

CREATE OR REPLACE PROCEDURE dname_sal_disp(

    v\_ename        IN         emp.ename%TYPE,

    v\_dname        OUT         dept.dname%TYPE,

    v\_sal        OUT         emp.sal%TYPE)

IS

    v\_deptno        emp.deptno%TYPE;

BEGIN

    SELECT sal,deptno

            INTO v\_sal,v\_deptno

            FROM emp

            WHERE ename = UPPER(v\_ename);

    SELECT dname

            INTO v\_dname

            FROM dept

            WHERE deptno = v\_deptno;

EXCEPTION

    WHEN NO\_DATA\_FOUND THEN

            DBMS\_OUTPUT.PUT\_LINE('입력한 MANAGER는 없습니다.');

    WHEN TOO\_MANY\_ROWS THEN

            DBMS\_OUTPUT.PUT\_LINE('자료가 2건 이상입니다.');

    WHEN OTHERS THEN

            DBMS\_OUTPUT.PUT\_LINE('기타 에러입니다.');

END;

/

진짜 못 해먹겠네 이렇게 금방 나간다고 맛이라도 볼줄아는 건가.

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 문장에서 사용 한다.

• 단순, 복잡한 데이터 형태의 변수를 선언 한다.

• 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.

• EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.

• 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.

PL/SQL의 블럭 구조.

PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화 된 블록 언어 이다.

PL/SQL 블록은 선언부(선택적), 실행부(필수적), 예외 처리부(선택적)로 구성되어 있고, BEGIN과 END 키워드는 반드시 기술해 주어야 한다.

PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있다.

|

| | | — | — |

Declarative Section(선언부)

• • 변수, 상수, CURSOR, USER_DEFINE Exception 선언

Executable Section(실행부)

• • SQL, 반복분, 조건문 실행
• • 실행부는 BEGIN으로 시작하고 END로 종료된다.
• • 실행문은 프로그램 내용이 들어가는 부분으로서 필수적으로 사용되어야 한다.

Exception Handling Section(예외처리)

• • 예외에 대한 처리.
• • 일반적으로 오류를 정의하고 처리하는 부분으로 선택 사항이다.

PL/SQL 프로그램의 작성 요령

• PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용 한다.

• END뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시 한다.

• PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고, SQL 프롬프트에서 바로 작성 할 수도 있다.

• SQL*PLUS 환경에서는 DECLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을 알 수 있다.

• 단일행 주석 : –

• 여러행 주석 : /* */

• PL/SQL 블록은 행에 / 가 있으면 종결 된다.

PL/SQL 블럭의 유형

|

|

|

Anonymous Block (익명 블록)

이름이 없는 블록을 의미 하며, 실행하기 위해 프로그램 안에서 선언 되고 실행시에 실행을 위해 PL/SQL 엔진으로 전달 된다.

선행 컴파일러 프로그램과 SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장 할 수 있다.

Procedure (프로시저)

특정 작업을 수행할수 있는 이름이 있는 PL/SQL 블록으로서, 매개 변수를 받을수 있고, 반복적으로 사용할수 있다.

보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터베이스에 저장하기 위해 생성 한다.

Function (함수)

보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 한다.

대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용 할 수 있고, 반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 한다.

또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환 해야 한다.

프로시저(Procedure)란?

특정 작업을 수행 하는, 이름이 있는 PL/SQL BLOCK 이다.

매개 변수를 받을 수 있고, 반복적으로 사용 할 수 있는 BLOCK 이다.

보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성 한다.

프로시저 문법

CREATE OR REPLACE procedure name

**IN** argument

**OUT** argument

**IN OUT** argument

IS

[변수의 선언]

BEGIN –\> 필수

[PL/SQL Block]

– SQL문장, PL/SQL제어 문장

[EXCEPTION] –\> 선택

– error가 발생할 때 수행하는 문장

END ; –\> 필수

• • CREATE OR REPLACE 구문을 사용하여 생성 한다.
• • IS 로 PL/SQL의 블록을 시작 한다.
• • LOCAL 변수는 IS 와 BEGIN 사이에 선언 한다.

프로시저 작성 예제

– 프로시저의 이름은 update_sal이다

– update_sal 프로시저는 사번을 입력받아 급여를 인상 한다.

– 프로시저를 끝마칠 때에는 항상 "/"를 지정 한다.

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;

 /

프로시저 실행 예제

EXECUTE 문을 이용해 프로시저를 실행 한다.

SQL\> EXECUTE update_sal(7369);

PL/SQL 처리가 정상적으로 완료되었습니다.

– 7369번 사원의 급여가 10% 인상 되었는지 확인해 보기 바란다.L