데이터베이스(2)

프로시저 : 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어
(프로시저 구성도)
DECLARE // 프로시저 명칭 
BEGIN // 프로시저 시작
  CONTROL // 조건문, 반복문 삽입되어 순차적 처리
  SQL  // DML 삽입, 삭제 등
  EXCEPTION // 예외가 발생하면 처리할 방법
  TRANSACTION // 수행된 데이터 작업들을 데이터베이스에 적용할까 취소할까 결정하는 부분
END // 프로시저 종료
(프로시저 생성)
CREATE PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
  프로시저 BODY;
END 
(프로시저 파라미터)
IN - 입력데이터
OUT - 출력데이터
INPUT - 입출력데이터
(프로시저 실행)
EXCUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;
(프로시저 제거)
DROP PROCEDURE 프로시저명;

---

트리거 : 데이터베이스 시스템에서 데이터의 이벤트(입력, 삭제, 갱신 등)가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
- 트리거 구문에는 DCL 사용 불가
(트리거 구성도)
DECLARE 
EVENT
BEGIN
  CONTROL
  SQL
END 
(트리거 생성)
CREATE TRIGGER 트리거명 [동작시기옵션] [동작옵션] ON 테이블 명
REFRENCING AS 테이블 명
FOR EACH ROW // 각 튜플마다 트리거 적용
[WHEN 조건식] // 트리거를 적용할 튜플들의 조건식 작성
BEGIN
  트리거 BODY;
END;
(트리거 삭제)
DROP TRIGGER 트리거명

---

사용자 정의 함수
SELECT를 통한 조회만 할 수 있음. DML문의 호출에 의해 실행 
RETRUN으로 값을 반환하므로 출력파라미터 존재하지 않음
(사용자 정의 함수 구성도)
DECLARE
BEGIN
  CONTROL
  SQL
  EXCEPTION
RETURN
END
(사용자 정의 함수 생성방법)
CREATE FUNCTION 사용자 정의 함수 명 (파라미터)
[지역변수 선언]
BEGIN
  사용자 정의 함수 BODY;
  RETURN 반환할 값;
END;
(사용자 정의 함수 사용 ex)
SELECT 사용자 정의 함수 명 FRON 테이블명;
INSERT INTO 테이블 명(속성명---) VALUES 사용자 정의 함수 명;
DELETE FROM 테이블 명 WHERE 속성명 = 사용자 정의 함수 명;
(사용자 정의 함수 삭제)
DROP FUNCTION 사용자 정의 함수 명;

---

커서 : 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터
열기(OPEN), 패치(FETCH), 닫기(CLOSE)
묵시적 커서 : 자동 생성되어 수행
명시적 커서 : 사용자가 직접 정의해서 사용

---

(문제)
01 테이블의 특정 행을 입력할 수 있는 DML 구문은?
02 트랜잭션 제어 명령어 중 ‘트랜잭션 진행 중 특정 지점으로 되돌리기 위해 설정하는 명령어’는 무엇인가?
03 다음 보기 중 DDL 구문을 선택하시오
INSERT CREATE SELECT ALTER DROP DELETE UPDATE
[4~11]
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
TABLE
VIEW
INDEX
SEQUENCE
SYNONYM
DESCRIBE
CASE
DECODE
SYSDAYE
SELECT
FROM
WHERE
USER_TABLES
DISTINCT
GROUP BY
HAVING
NVL
NOT NULL
SUBSTR
ORDER BY
04 중복된 행을 자동으로 제거해 주는 명령어(=키워드)는 무엇인가?
05 테이블 또는 뷰의 구조를 조회할 때 사용하는 명령어는 무엇인가?
06 데이터 검색 시 결과를 정렬할 때 사용하는 명령어는 무엇인가?
07 물리적인 테이블을 기반으로 생성되며, 데이터 딕셔너리에 SELECT 구문 형태로 저장되는 객체는 무엇인가?
08 그룹에 제하는 조건문을 작성할 수 있는 절은 무엇인가?
09 테이블에 NULL 값이 들어가지 못하게 막아주는 제약조건은 무엇인가?
10 NOT NULL과 UNIQUE의 성격을 모두 가지고 있는 제약조건은 무엇인가?
11 자신의 테이블이나 다른 테이블의 특정 칼럼을 참조하는 제약조건은 무엇인가?
12 사원 테이블로부터 부서번호가 50인 사원들의 사원번호, 이름, 급여를 조회하는 '사원뷰50' 뷰(VIEW)를 생성하는 DDL 구문을 작성하시오.
13 사원 테이블을 사용하여 사원의 이름과 커미션율 칼럼을 출력하되 커미션을 받지 않는 사원을 제외한 정보만 출력하는 쿼리 구문을 작성하시오. (커미션을 받지 않는 사원의 커미션 칼럼의 값은 NULL이다)
14 사원2 테이블을 삭제하는 쿼리구문을 작성하시오.
15 TITLE 테이블에 아래와 같이 데이터를 삽입하는 SQL(DML) 문장을 작성하시오. (테이블에는 아래 4개의 칼럼만 있다.)
테이블명
TITLE
칼럼명
TITLE_ID
TITLE
RATING
RELEASE DATE
데이터값
1
ORACLE
R
05-JUN-17
16 사원 테이블로부터 이름이 ‘박’으로 시작되는 사원들의 회원번호, 이름, 부서번호를 출력하는 구문을 작성하시오.
17 다음 빈칸에 알맞은 키워드를 넣어 쿼리 구문을 완성하시오.
SELECT 부서번호, AVG(급여) as 평균급여
FROM 사원
GROUP BY 부서번호
( ) AVG(급여) > 6000;
18 조건에 맞추어 SQL문을 작성하시오. 2020.2차 기출
① 학생 테이블에서 3, 4학년인 학번, 이름을 검색
② IN 함수를 사용하여 작성할 것
③ 조건에 맞지 않을 경우, 오답으로 처리됨
19 student 테이블의 name 속성에 idx_name 이름으로 인덱스로 생성하는 명령어를 작성하시오. 2020.2차 기출
20 사원 테이블에서 부서번호가 30, 60인 사원의 부서번호와 급여를 부서번호 기준으로 내림차순, 급여 기준으로 오름차순 정렬하여 조회하시오.
(정답)
1. INSERT 
2. SAVEPOINT  
3. CREATE DROP ALTER 
4. DISTINCT 
5. DESCRIBE 
6. ORDER BY 
7. VIEW 
8. HAVING 
9. NOT NULL 
10. PRIMARY KEY 
11. FOREIGN KEY 
12. CREATE VIEW 사원뷰 AS ( SELECT 사원번호, 이름, 급여 FROM 사원 WHERE 부서번호 = 50); 
13. SELECT 이름, 커미션 FROM 사원 WHERE 커미션 IS NOT NULL;  
14. DROP TABLE 사원2; 
15. INSERT INTO TITLE VALUES ( 1, 'ORACLE', 'R', '05-JUN-17'); 
16. SELECT 회원번호, 이름 FROM 사 원 WHERE 이름 LIKE '박%'; 
17. HAVING  
18. SELECT 학번, 이름 FROM 학생 WHERE 학년 IN (3, 4); O
19. CREATE INDEX idx_name ON student(name); 
20. SELECT 부서번호, 급여 FROM 사원 WHERE 부서번호 IN (30, 60) ORDER BY 부서번호 DESC, 급여;