본질의 중요성

PL/SQL (Procedural Language/Structed Query Language)

• 데이터베이스 응용 프로그램을 작성하는데 사용하는 오라클의 SQL 전용 언어
• 장점: 프로그램 개발의 모듈화 가능, 변수 선언 가능, 절차형 언어 사용, 에러처리, 이식성, 성능 향상 ...
• PL/SGQ 프로그램 종류
  - Subprogram: Function (Return a Value), Procedure(Do action)
  - 자동실행 program: Trigger
  - 복합 프로그램: Package

Function

• 반드시 하나의 값을 리턴해야하는 경우 사용

create or replace PROCEDURE p_tax
  (v_num in number,
   v_tax out number)
IS 
BEGIN 
  v_tax := v_num * 0.07;
END;

Procedure

• 리턴값이 여러 개이거나 0개일 경우 
• in / out / in out 으로 파라미터 타입과 전달 방식 명시

-- procedure: return 값 없음 대신 out으로 표기
create or replace PROCEDURE p_tax
  (v_num in number,
   v_tax out number)
IS 
BEGIN 
  v_tax := v_num * 0.07;
END;

Command로 Procedure, Function 실행하기

-- FUNCTION Command 실행하는 방법
VARIABLE a    NUMBER;
EXECUTE :a := TAX(100);
PRINT    a;

-- PROCEDURE Command 실행하는 방법
VARIABLE a    NUMBER;
EXECUTE p_tax(100, :a);
PRINT    a;

비교 연산자

• = / != <> / > / >= / > / >=

논리 연산자

• AND / OR / NOT 

SQL 연산자

• BETWEEN a AND b : a와 b의 사이 값. a, b 포함
• IN (a, b, c, ..., n) : a, b, c... n 중 하나와 일치하면 참
• LIKE : 문자 패턴과 부분적으로 일치하면 참 - 문자패턴 %:길이에 상관없음, _: 한글자
• IS NULL : NULL 이면 참
• IS NOT NULL : NULL 이 아니면 참

집합 연산자

집합 연산의 대상이 되는 두 테이블의 컬럼수와 대응되는 컬럼끼리의 데이터 타입이 동일해야 함

• UNION : 두 집합에 대해 중복되는 행을 제외한 합집합
• UNION ALL : 두 집합에 대해 중복되는 행을 포함한 합집합
• MINUS : 두 집합간의 차집합
• INTERSECT : 두 집합간의 교집합 (공통인 부분)

-- 두 질의의 컬럼수가 같아야함. 두집합 연산의 대상, studno, name
-- UNION: 합집합, UNION ALL: 공통인 부분도 다 보여줘라
SELECT studno , name
FROM   stud_heavy
UNION  ALL
SELECT studno , name
FROM   stud_101;

-- INTERSECT: 교집합, 공통 요소만 get
SELECT studno , name
FROM STUD_HEAVY
INTERSECT
SELECT studno , name
FROM STUD_101;

-- MINUS: 차집합
SELECT studno , name
FROM STUD_HEAVY
MINUS
SELECT studno , name
FROM STUD_101;

정렬

• ASC: 오름차순(기본), DESC: 내림차순
  
• NULL일 경우 오름차순에서는 맨 마지막에 출력, 내림차순에서는 맨 처음 출력
• 다중 컬럼으로 정렬 가능

그림으로 쉽게 이해하자!

Ref. 오라클 중심의 sql 배움터

DML(Data Manupulation Language)

• 테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어
• 종류: INSERT, UPDATE, DELETE, MERGE

INSERT

단일 행 입력

-- 컬럼명 생략
INSERT INTO student 
VALUES(1011, '홍길동', 'hong', 1, '8501011143098', '85/01/01', '041-630-3114', 170, 70, 101, 9903); 

-- 컬럼명 명시
-- 날짜형식을 '85/01/01' 이렇게 넣을 수 있음. 단 버전 별로 안될수도 있으니 TO_DATE('2006/01/01','YYYY/MM/DD') 이 방식도 알아야함!
INSERT INTO PROFESSOR(profno, name, position, hiredate, deptno)
VALUES(9920, '최윤식', '조교수', TO_DATE('2006/01/01','YYYY/MM/DD'), 102);

다중 행 입력

방법 1) SELECT 조건으로 넣어주기

INSERT INTO MMSUM30 (SUM_YYMM, ITEM_CODE, ITEM_GUBN, STCK_QTY, SawonID, RegiDate)
             (SELECT p_sum_yymm, ITEM_CODE, '0', STCK_QTY, SawonID, SYSDATE
              FROM  MMSUM30
              WHERE SUM_YYMM = TO_CHAR(ADD_MONTHS(TO_DATE(p_sum_yymm, 'YYYYMM'), -1), 'YYYYMM')
              AND   ITEM_GUBN = '1');

방법 2) INSERT ALL

: 서브쿼리의 결과 집합을 여러 테이블에 동시에 입력

-- ex1)
INSERT ALL
INTO   height_info VALUES(studno, name, height)
INTO   weight_info VALUES(studno, name, weight)
SELECT studno, name, height, weight
FROM   student
WHERE  grade >= 2;

-- ex2)
INSERT ALL 
WHEN   height > 170       THEN 
       INTO height_info VALUES(studno, name, height)
WHEN   weight >70         THEN
       INTO weight_info VALUES(studno, name, weight) 
SELECT studno, name, height, weight
FROM   student
WHERE  grade >= 2;

방법 3) INSERT FIRST

조건을 지정하여 조건에 만족하는 첫번째 테이블에 값을 우선적으로 입력하기 위한 명령문

서브쿼리의 결과 집합중에서 조건을 만족하는 첫 번째 WHEN절에서 지정한 테이블에만 입력하고 나머지 WHEN절 무시

INSERT FIRST
WHEN   height > 170    THEN
       INTO height_info VALUES(studno, name, height)
WHEN   weight > 70     THEN
       INTO weight_info VALUES(studno, name, weight)
SELECT studno, name, height, weight
FROM   student
WHERE  grade >= 2; 

PIVOTING INSERT

- OLTP(OnLine Transaction Processing) 업무에서 사용되는 데이터를 데이터웨어하우스 업무에서 사용되는 분석용 데이터로 변환하는 경우에 유용

- PIVOTING INSERT 명령문을 사용하여 SALES 테이블의 요일별 데이터를 통합하여 SALES_DATA 테이블에 하나의 행으로 입력

-- 알아두자. => 데이터를 다양하게 조작할 수 있는 테이블!
INSERT ALL
INTO   sales_data VALUES(sales_no, week_no, '1', sales_mon)
INTO   sales_data VALUES(sales_no, week_no, '2', sales_tue)
INTO   sales_data VALUES(sales_no, week_no, '3', sales_wed)
INTO   sales_data VALUES(sales_no, week_no, '4', sales_thu)
INTO   sales_data VALUES(sales_no, week_no, '5', sales_fri)
SELECT sales_no, week_no, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
FROM   sales;

UPDATE

WHERE로 조건을 꼭! 줘야함.

-- ex1)
UPDATE professor
SET    position = '부교수',
       sal = 390
WHERE  profno = 9903;

-- ex2)
UPDATE student
SET(grade, deptno) = (SELECT grade, deptno 
                      FROM   student
                      WHERE  studno = 10103)
WHERE studno = 10201;          

DELETE

-- ex1)
DELETE student
WHERE  studno = 20103;

-- ex2)
DELETE student
WHERE  deptno = (SELECT deptno 
                 FROM   department
                 WHERE  dname = '컴퓨터공학과');

Merge

구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어

CREATE TABLE professor_temp AS
SELECT *
FROM   professor
WHERE  position = '교수';

UPDATE PROFESSOR_TEMP
SET    position = '명예교수'
WHERE  position = '교수';

INSERT INTO PROFESSOR_TEMP
VALUES(9999, '김도경', 'arom21', '전임강사', 200, SYSDATE, 10, 101);

MERGE INTO professor p
USING professor_temp f
ON(p.profno = f.profno)
WHEN matched THEN
     UPDATE set p.position = f.position
WHEN not matched THEN
     INSERT VALUES(f.profno, f.name, f.userid, f.position, f.sal, f.hiredate, f.comm, f.deptno);