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;
'Oracle > 기본문법' 카테고리의 다른 글
WRITTEN BY
