view

1. 뷰는 테이블에서 사용자가 얻기를 원하는 데이터들만을 정확하고 편하게 가져오기 위하여

사전에 원하는 컬럼들만을 모아서 만들어 놓은 가상의 테이블

2. 형식

CREATE OR REPLACE VIEW 뷰이름

AS

SELECT 구문;

서브쿼리로 예를 들면,

SELECT * FROM (서브쿼리 구문);

서브쿼리 구문이 복잡하더라도 결국은 단순한 쿼리문으로 표현할 수 있다.

서브쿼리는 객체로서 등록하지는 못한다.

뷰로 예를 들면,

CREATE OR REPLACE VIEW 뷰이름

AS

서브쿼리 구문;

서브쿼리는 복잡하더라도 결국은 단순한 뷰 구문(SELECT * FROM 뷰이름)으로 표현할 수 있다.

뷰는 객체로서 등록하지는 못한다.

-- 제약조건 확인용 뷰 생성

-- 원본SELECT 쿼리

--뷰 실행

select * from constraint_check

where table_name = 테이블명;

--뷰의 구조 확인

DESC 뷰이름;

--뷰의 소스 확인

SELECT view_name, text

from user_views

where view_name = '뷰 이름';

문제) insa 테이블에서 아래의 결과를 얻는 뷰 작성.

이름, 부서, 출신도, 기본급, 수당, 기본급+수당(총급여), 세금, 실수령액 출력

세금 계산 -> 총급여가 250만원 이상이면 2% 200만원이상이면 1%, 나머지는 0%

실수령액 계산 -> 총급여 - 세금

인라인 뷰

1. 쿼리문에서 from절에 서브쿼리를 사용하여 생성한 임시 뷰이다.

인라인 뷰는 객체로서 저장되지 않는다.

2. 형식

SELECT 컬럼리스트

FROM (서브쿼리);

UPDATE (서브쿼리)

SET 컬럼 = 신규자료, ...

WHERE 조건식;

DELETE (서브쿼리)

WHERE 조건식;

--employees 테이블에서 입사일(hire_date)가 빠른 순으로 5순위까지만 출력

SELECT *

FROM (SELECT first_name, last_name, RANK() OVER(order by hire_date asc) as rank

FROM EMPLOYEES)

where rank <= 5;

--employees 테이블에서 jobs.job_title이 'Shipping Clerk'인 직원의 employees.salary를 10% 인상.

update employees

set salary = salary * 1.1

where job_id = (select job_id

from jobs

where job_title = 'Shipping Clerk');

update (select first_name, last_name, job_title, salary

from employees e, jobs j

where e.job_id = j.job_id) e

set esalary = e.salary * 1.1

where e.job_title = 'Shipping Clerk';

--employees 테이블에서 jobs.job_title이 'Shipping Clerk'인 직원 삭제.

delete (select first_name, last_name, job_title, salary

from employees e, jobs j

where e.job_id = j.job_id) e

where e.job_title = 'Shipping Clerk'

문제) employees 테이블에서 employees.salary를 각 부서 이름별(department_name)로 다른 인상율 적용합니다. 인라인뷰 이용.

Finance -> 10%

Executive -> 15%

Accounting -> 20%

나머지 부서 -> 5%

update(select e.first_name, e.last_name, d.department_name, e.salary

from employees e, departments d) t

set t.salary = case when t.department_name='Finance' then t.salary * 1.1

   when t.department_name='Executive' then t.salary * 1.15

   when t.department_name='Accounting' then t.salary * 1.2

   else t.salary * 1.05 end

PL/SQL (Procedural Language extension to SQL)

1. PL/SQL 은 프로그래밍 언어의 특성을 가지는 SQL의 확장.

2. PL/SQL은 블록 구조로 되어 있으며 선언부, 실행부, 예외처리부로 구성됨

3. 형식

4. PL/SQL 은 변수, 연산자(대입문), 제어문(조건문, 반복문) 등을 지원한다.

--메세지 출력

SET SERVEROUTPUT ON;

DECLARE

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello, Oracle World!');

END;

--변수 선언 및 초기값 대입

SET SERVEROUTPUT ON;

DECLARE

str VARCHAR2(100) := 'Hello, Oracle World!';

BEGIN

DBMS_OUTPUT.PUT_LINE(str);

END;

--산술연산

SET SERVEROUTPUT ON;

DECLARE

n1 NUMBER := 10;

n2 NUMBER := 20;

BEGIN

DBMS_OUTPUT.PUT_LINE('n1 = ' || n1);

DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);

DBMS_OUTPUT.PUT_LINE('n1 + n2 = ' || (n1 + n2));

DBMS_OUTPUT.PUT_LINE('n1 * n2 = ' || (n1 * n2));

END;

--IF문

형식

IF(조건식) THEN 실행문;

elsif 조건식 then 실행문;3

else 실행문

END IF;

형식2

IF(조건식) THEN

실행문;

ELSE

실행문;

END IF;

형식3

IF(조건식) THEN

실행문;

ELSIF(조건식2) THEN

실행문;

[ELSIF(조건식n THEN)

실행문;]

ELSE

실행문;

END IF;

SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

if(grade >= 90 and grade <= 100)  then

DBMS_OUTPUT.PUT_LINE('A등급');

else

DBMS_OUTPUT.PUT_LINE('B등급');

end if;

END;

SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

if(grade >= 90 and grade <= 100)  then

DBMS_OUTPUT.PUT_LINE('A등급');

elsif (grade >= 80 and grade <= 90) then

DBMS_OUTPUT.PUT_LINE('B등급');

elsif (grade >= 70 and grade <= 80) then

DBMS_OUTPUT.PUT_LINE('C등급');

elsif (grade >= 60 and grade <= 70) then

DBMS_OUTPUT.PUT_LINE('D등급');

else

DBMS_OUTPUT.PUT_LINE('F등급');

end if;

END;

-- CASE ~ END CASE 구문

CASE 변수

WHEN 값1 THEN 실행문;

WHEN 값2 THEN 실행문;

...

ELSE 실행문;

END CASE;

문제) 점수를 분석해서 A등급에서 F등급까지 출력하는 PL/SQL 작성. CASE ~ END CASE 구문 이용.

SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

CASE trunc(grade/10)

WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('A등급');

WHEN 9 THEN DBMS_OUTPUT.PUT_LINE('A등급');

WHEN 8 THEN DBMS_OUTPUT.PUT_LINE('B등급');

WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('C등급');

WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('D등급');

ELSE DBMS_OUTPUT.PUT_LINE('F등급');

END CASE;

END;

-- WHILE ~ END LOOP 구문

형식1

WHILE 조건식 LOOP -- 조건이 참이면 반복실행

실행문;

END LOOP;

형식2 (무한실행)

LOOP

EXIT WHEN 조건식; -- 조건이 참이면 블럭탈출

실행문;

END LOOP;

-- 1부터 10까지 출력.

SET SERVEROUTPUT ON;

DECLARE

n NUMBER; 

BEGIN

n := 0;

while n < 10 loop

n := n + 1;

DBMS_OUTPUT.PUT_LINE(n);

end loop;

END;

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

n := 0;

loop

exit when n = 11;

n := n + 1;

DBMS_OUTOUT.PUT_LINE(n);

end loop;

END;

-- FOR ~ END LOOP 문

FOR 변수 IN 시작수..끝수 LOOP

실행문;

END LOOP;

-- 1부터 10까지 출력.

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

FOR n IN 1..10 LOOP -- 이 FOR문에서 n 즉 변수는 별도의 선언이 없어도 자동선언되므로, DECLARE 에서 별도로 선언하지 않아도 됨.

DBMS_OUTPUT.PUT_LINE(n);

END LOOP;

END;

문제) 구구단 출력을 WHILE, LOOP, FOR 반복문으로 출력. 5단으로 한정.

while 문사용 ) --

SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

m NUMBER := 1;

BEGIN

WHILE(m <= 9) LOOP

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

m := m + 1;

END LOOP;

END;

LOOP 문 사용 )--

SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

m NUMBER := 1;

BEGIN

LOOP

EXIT WHEN m = 9;

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

m := m+1;

END LOOP;

END;

FOR 문 사용 )--

SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

BEGIN

for m in 1..9 loop

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

end loop;

END;

PL/SQL 외부 입력 처리

1. ACCEPT 변수 PROMPT '메시지'

2. 외부 변수에서 받은 데이터를 내부 변수에 전달할 때는 &외부변수명 형태로 연결.

ACCEPT n1 PROMPT '숫자1?';

ACCEPT n2 PROMPT '숫자2?';

SET SERVEROUTPUT ON;

DECLARE

num1 NUMBER := &n1;

num2 NUMBER := &n2;

BEGIN

DBMS_OUTPUT.PUT_LINE('n1 = ' || n1);

DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);

DBMS_OUTPUT.PUT_LINE('n1 + n2 = ' || (n1 + n2));

END;

문제) 구구단 출력을 사용자가 원하는 단수로 출력. ACCEPT, FOR문 사용.

실행 예)

구구단 단수? 5

5 * 1 = 5

....

5 * 9 = 45

ACCEPT n1 PROMPT '구구단 단수?';

SET SERVEROUTPUT ON;

DECLARE

n NUMBER := &n1;

BEGIN

for m in 1..9 loop

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

end loop;

END;

FUNCTION (함수)

1. 내장함수, 사용자 정의 함수. 외부 데이터 입력시 단일 결과 리턴하는 PL/SQL 구문의 집합체.

2. 형식

--주민번호 입력시 성별 출력하는 함수 작성.

--> fnGender('주민번호')

--사용자 정의 함수 작성

CREATE OR REPLACE FUNCTION fnGender

(

vssn VARCHAR2 --'123456-1234567', 1 또는 2만으로 구분한다고 가정

)

RETURN NVARCHAR2 --'남자', '여자'

IS

gender NVARCHAR2(2);

BEGIN

IF (SUBSTR(vssn, 8, 1) = '1') THEN

gender := '남자';

ELSE

gender := '여자';

END IF;

RETURN gender;

END;

--사용자 정의 함수 실행

SELECT fnGender('123456-1234567') AS fnGender FROM dual; --'남자'

SELECT fnGender('123456-2345678') AS fnGender FROM dual; --'여자'

SELECT num, name, ssn, fnGender(ssn) AS fnGender

  FROM insa;

SELECT fnGender(ssn), COUNT(*)

  FROM insa

  GROUP BY fnGender(ssn);

--주민번호(VARCHAR2) 입력시 생년월일(DATE, YYYY-MM-DD) 리턴하는 함수 작성

--> fnBirthDay('주민번호')

CREATE OR REPLACE FUNCTION fnBirthDay

(

vssn VARCHAR2

)

RETURN date

IS

birthday DATE;

BEGIN

birthday := TO_DATE(SUBSTR(vssn, 1, 6)); --YY/MM/DD

RETURN birthday;

END;

SELECT fnBirthDay('800101-1234567') AS fnBirthDay FROM dual;

SELECT TO_CHAR(fnBirthDay('800101-1234567'), 'YYYY-MM-DD') AS fnBirthDay FROM dual;

SELECT num, name, ssn, fnBirthDay(ssn) AS fnBirthDay FROM insa;

문제) 날짜 데이터를 입력 받아서 년도, 월, 일을 따로 출력하는 함수 작성.

반환 자료형은 NUMBER.

fnYear(날짜 데이터), fnMonth(날짜데이터), fnDay(날짜데이터)

--사용 예

SELECT num, name, ibsadate 

FROM insa

WHERE fnMonth(ibsadate) = 5;

--날짜 데이터에서 '년' 얻는 함수

CREATE OR REPLACE FUNCTION fnYear

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'YYYY'));

END;

SELECT fnYear(SYSDATE) FROM dual;

--날짜 데이터에서 '월' 얻는 함수

CREATE OR REPLACE FUNCTION fnMonth

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'MM'));

END;

SELECT fnMonth(SYSDATE) FROM dual;

--날짜 데이터에서 '일' 얻는 함수

CREATE OR REPLACE FUNCTION fnDay

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'DD'));

END;

SELECT fnDay(SYSDATE) FROM dual;