view



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

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



2. 형식

CREATE OR REPLACE VIEW 뷰이름

AS

SELECT 구문;


서브쿼리로 예를 들면,

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

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

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


뷰로 예를 들면,

CREATE OR REPLACE VIEW 뷰이름

AS

서브쿼리 구문;

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

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



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

-- 원본SELECT 쿼리

SELECT uc.owner                         --소유자

, uc.constraint_name            --제약명

, uc.table_name AS table_name   --테이블명

, uc.constraint_type            --제약조건 종류(P, R, U, C)

, uc.search_condition           --CHECK 제약 조건식

, ucc.column_name               --지정된 컬럼

, (SELECT table_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_table_name --참조하는 테이블

, (SELECT column_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_conlumn_name --참조하는 컬럼

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name

AND uc.table_name='테이블명'; -- 외부로 부터 받는 인자는 지우고 추후 추가 where 절을 통해 개별지정.


--뷰생성

CREATE OR REPLACE VIEW constraint_check

AS

SELECT uc.owner                         --소유자

, uc.constraint_name            --제약명

, uc.table_name AS table_name   --테이블명

, uc.constraint_type            --제약조건 종류(P, R, U, C)

, uc.search_condition           --CHECK 제약 조건식

, ucc.column_name               --지정된 컬럼

, (SELECT table_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_table_name --참조하는 테이블

, (SELECT column_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_conlumn_name --참조하는 컬럼

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name;


--뷰 실행

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. 형식

DECLARE

-- 선언문

BEGIN

-- 실행문


[EXCEPTION

-- 예외처리문]


END;



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. 형식

CREATE OR REPLACE FUNCTION 함수이름

(

매개변수 리스트 --자료형 지정시 크기 지정 불가

)

RETURN 리턴자료형 --자료형 지정시 크기 지정 불가

IS

내부변수 리스트;

BEGIN

실행문;

RETURN 값;

END;


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

--> 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;



WRITTEN BY
빨강꼬마

,