23.02.02(group by, having, view, PL/SQL(변수선언, if, 반복문)

문제 소숫점 나오는거 dual테이블로 확인해보기

==> round(avg(sal),0) 이런식으로 select 내에서 쓰면 된다.

 

테이블 추가해보고 view with read only 한게 컬럼 추가되는 지 확인하기

==> 자동으로 추가된다.

 

레코드 삭제하는 법?? 왜 컬럼 삭제하는거만 배웠지? 

==> delete from 테이블이름 where 삭제하고 싶은 레코드 (ex. hakbun(primary key여야함) = 2023_001)

 

primary key로 설정된거 어떻게 보지?

==> db의 model에서 p는 primary key / sql 가서 어떻게 작성했는지 확인

 

 

:= 

이건 초기값 대입할 때만 넣어주는 거임

 

😀레코드

 

 

/*
   group by 절
   - 특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용함
   - 보통은 특정 컬럼을 기준으로 집계를 구하는데 많이 사용됨
   - 또한 그룹함수와 함께 사용하면 효과적으로 활용이 가능
*/

select deptno, sum(sal), count(*), round(avg(sal), 0), max(sal), min(sal)
from emp
group by deptno
order by sum(sal) desc;

/*
   having 절
   - group by 절 다음에 오는 조건절
   - group by 절의 결과에 조건을 주어서 제한할 때 사용함
   - group by 절에는 where(조건절) 가 올 수 없음
*/

select deptno, min(sal)

from emp

group by deptno

having min(sal) <= 1000;

/*
   ★★★★★★★★★★★★★★★★★★★★★★★★
   View
   - 물리적인 테이블에 근거한 논리적인 가상의 테이블을 말함
   - View는 실질적으로 데이터를 저장하고 있지 않음
   - View를 만들면 데이터베이스에 질의 시 실제 테이블에 접근하여 데이터를 불러오게 됨
   - 간단하게 필요한 내용들만 추출해서 사용할 때 많이 사용됨.
   - View는 테이블과 유사하며, 테이블처럼 사용이 가능함
   - View는 테이블에 저장하기 위한 물리적인 공간이 필요 없음
   - 테이블과 마찬가지로 insert, update, delete 명령이 가능함
   - 하지만 주로 데이터를 조회(select) 할 때 가장 많이 사용됨 
   - View를 사용하는 이유
     1) 보안 관리를 위해 사용함(중요함)
        ==> 보안 등급에 맞추어 컬럼의 범위를 정해서 조회가 가능하도록 할 수 있음
     2) 사용자의 편의성 제공함
        
     형식)
            create view 뷰이름
            as
            쿼리문;
*/

create view emp_insa

as

select empno, hiredate, deptno

from emp;

 

 

-- View 를 읽기 전용으로 만들면 데이터가 추가가 안 됨.
-- 읽기 전용으로 만드는 방법
-- ==> View 만들 때 쿼리문 맨 마지막에 with read only; 문구 추가;

@ view를 with read only라고 하고 from으로 받은 table을 수정하면 view도 같이 수정된다!

-- 주의사항) view를 만들 때 그룹함수 사용시에는 반드시 별칭을 설정해 주어야 함.

-- create or replace view
-- 같은 이름의 view가 있는 경우에는 기존의 view를 삭제하고 새로운 view를 만들라는 의미

create or replace view emp_ex1

as

select deptno, max(sal * 12 + nvl2(comm, comm, 0)) "최대연봉"

from emp

group by deptno

with read only;

 

 

/*
   트랜잭션(Transaction)
   - 데이터 처리의 한 단위를 말함.
   - 오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것을 말함
   - ALL or Nothing 방식으로 처리함
   - 명렁어 여러 개의 집합이 정상적으로 처리가 되면 종료하고,
     여러 개의 명령어 중에서 하나의 명령이라도 잘못이 되면 전체를 취소하는 것을 말함 - 중요함
     
   - 트랜잭션 사용 이유 : 데이터의 일관성을 유지하면서 데이터의 안전성을 보장하기 위해 사용함
   - 트랜잭션 사용 시 트랜잭션을 제어하기 위한 명령어
     1) commit : 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어
                 트랜잭션(insert, update, delete) 작업의 내용을 실제 DB에 반영.
                 이전에 있던 데이터에 update 현상이 발생을 함.
                 모든 사용자가 변경된 데이터의 결과를 볼 수 있음
     2) rollback : 작업 중에 문제가 발생했을 때 트랜잭션 처리 과정에서 발생한 변경 사항을
                   취소하여 이전 상태로 되돌리는 명령어.
                   트랙잭션(insert, update, delete) 작업의 내용을 취소함
                   이전에 commit 한 곳까지만 복구가 됨
*/

- 잘못해서 전체가 삭제 된 경우, commit을 안 썼다면 rollback 가능 (commit 쓰기 전까지 복원 가능)

 

/*
   savepoint 
   - 트랜잭션을 작게 분할하는 것을 말함
   - 사용자가 트랜잭션 중간 단계에서 포인트를 지정하여 
     트랜잭션 내의 특정 savepoint까지 rollback 할 수 있게 하는 것을 말함
*/

savepoint sp1;  -- 세이브 포인트 설정

savepoint sp2;

savepoint sp3

 

rollback to savepoint sp2

 

-- student 테이블의 특정 레코드를 수정하는 방법
-- 형식) update 테이블이름 set 
--              수정할 컬럼이름1 = 수정될 컬럼내용,
--              수정할 컬럼이름2 = 수정될 컬럼내용
--              where primary key 컬럼명
-- 주의사항) 만약 where조건절이 누락이 되면 테이블 전체 데이터가 수정이 이루어짐.

update emp set

ename = '호랑이', job = '개발자'

from empno = 30;

 

 

/*
   PL/SQL(Procedual Language / SQL)
   - SQL 만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해서
     오라클에서 제공하는 프로그래밍 언어를 말함
   - 일반적으로 프로그래밍 언어적인 요소들을 다 가지고 있으며,
     데이터베이스 업무를 처리하기 위한 최적화된 언어
   - 변수, 조건 처리, 반복 처리 등 다양한 기능을 사용할 수 있음
   
   - 기본 구조
     1) 선언부(declare) : 모든 변수나 상수를 선언하는 부분
     2) 실행부(executable ~ begin) 
        - 실제 로직이 실행되는 부분
        - 제어문(조건문). 반복문, 함수 정의 등의 로직을 기술하는 부분
     3) 예외처리부(exception)
        - 실행 도중 예외가 발생 시 해결하기 위한 명령들을 기술하는 부분
   - 위 기본 구조 중에서 선언부와 예외처리부는 생략이 가능하지만,
     실행부는 반드시 존재해야 함
     
   - PL/SQL 사용시 주의사항
     1) 기본 구조(declare, begin, exception) 키워드 뒤에는 세미콜론(;)을 붙이지 않는다.
     2) 블럭의 각 부분에서 실행해야 하는 문장 끝에는 반드시 세미콜론(;)을 붙인다.
     3) begin ~ end(실행부) 밑에는 반드시 "/"를 붙여 주어야 한다
*/

 

-- 화면에 출력기능을 활성화 시키기
set serveroutput on;

 

-- 선언부(declare) 영역에 변수를 선언하는 방법
-- 1) 스칼라 자료형
--    형식) 변수명 자료형(크기)
--    예) num number3(3);, name varchar2(20);

set serveroutput on;

 

declare

      e_num number(3) := 78;

      e_number number(4);

begin

      e_number := 666;

      dbms_output.put_line(e_num);

     dbms_output.put_line(e_number);

end;

/

-- 2) 레퍼런스 자료형
-- 테이블에 정의된 컬럼의 자료형과 크기를 모두 파악하고 있다면 문제가 없겠지만,
-- 대부분 그렇지 못하기 때문에 오라클에서는 레퍼런스(reference) 변수를 제공해 줌
-- 형식) 변수명 테이블명.컬럼명%type;
-- 예) num emp.empno%type; -- num이라는 변수는 emp테이블의 empno컬럼의 타입으로 선언하겠다

declare

     e_number emp.empno%type := 9999;

begin

     dbms_output.put_line(e_number);

end;

/

-- 3) rowtype 자료형
-- 테이블의 모든 컬럼을 한꺼번에 저장하기 위한 변수로 선언하는 방법
-- 형식) e_row emp%rowtype;

declare

    e_emp emp%rowtype; 

begin

   select * into e_emp

   from emp

   where empno = 9000;

   dbms_output.put_line(e_emp.empno || ' ' || e_emp.ename);

end;

/

 

조건 제어문(조건문)
    - 특정 조건식을 통해 상황에 따라 실행할 내용을 달리하는 방식의 명령어를 말함

 1. if 조건문
        1) if ~ then
            - 특정 조건을 만족하는 경우에 작업을 계속 수행
            - 형식)
                if 조건식 then
                    조건식이 참인 경우 실행 문장;
                end if;

declare

    e_num number(3) := 10;

begin

   if e_num >=10 then

         dbms_output.put_line(e_num || '은 10보다 크거나 같다');

         end if;

end;

/

 

2) if ~ then ~ else
            - 특정 조건식에 만족하는 경우와 반대의 경우에 각자 지정한 작업을 수행
            - 형식)
                if 조건식 then
                    조건식이 참인 경우 실행 문장;
                else 
                    조건식이 거짓인 경우 실행 문장;
                end if;

declare 
    e_num number(3) := 88;
begin
    if mod(e_num, 2) = 1 then
        dbms_output.put_line('안녕하세요');
    else
        dbms_output.put_line('안녕 못해요');
    end if;
end;
/

 

3) if ~ then ~ elsif
            - 여러 가지 조건에 따라 각자 지정한 작업을 수행
            - 형식)
                if 조건식1 then
                    조건식1이 참인 경우 실행 문장;
                elsif 조건식2 then
                    조건식1이 거짓이고, 조건식2가 참인 경우 실행 문장;
                elsif 조건식3 then    
                    조건식1, 2가 거짓이고, 조건식3이 참인 경우 실행 문장;
                else
                    조건식1, 2, 3 모두가 거짓인 경우 실행 문장;
                end if;

-- 키보드로 데이터를 입력 받는 방법 ==> '&문자열'
declare e_num number(3);
begin e_num := '&num1';

 

declare 
    e_num number(3) := '&점수';
begin
    if e_num >= 90 then
        dbms_output.put_line('a학점입니다');
    elsif e_num >= 80 then
        dbms_output.put_line('b학접입니다');
    elsif e_num >= 70 then
        dbms_output.put_line('c학접입니다');
    else
        dbms_output.put_line('f학접입니다');
    end if;
end;
/

 

/*
   2. case 조건문
      형식) 
            case 비교기준
                when 값1 then
                    값1 일 때 수행할 명령어;
                when 값2 then
                    값2 일 때 수행할 명령어;
                when 값3 then
                    값3 일 때 수행할 명령어;
                else
                    값1,2,3 이 아닌 다른 값일 때 수행할 명령어;
                end case;
*/

declare
    e_num number(3):= '&점수';
begin
    case trunc(e_num/10)
        when 10 then
            dbms_output.put_line('A학점입니다');
        when 9 then
            dbms_output.put_line('B학점입니다');
        when 8 then
            dbms_output.put_line('C학점입니다');
        else
            dbms_output.put_line('F학점입니다');
    end case;
end;
/

 

/*
    반복 제어문
    - 특정 작업을 반복하여 수행하고자 할 때 사용하는 문장
    - 반복 제어문 오류
      1) 기본 loop
         - 가장 기본적인 반복문
      2) while loop
         - 특정 조건의 결과를 통해서 반복을 수행하는 반복문
      3) for loop
         - 반복 횟수를 정하여 반복을 수행하는 반복문
         
    - 반복문의 반복적인 수행을 종료시키는 명령어
      1) exit
         - 수행 중인 반복을 종료시키는 명령어
      2) exit ~ when
         - 반복 종료를 위한 조건식을 지정하고 만족하면 반복을 종료시키는 명령어
      3) continue 
         - 수행 중인 반복의 현재 주기를 건너 뛰는 명령어
      4) continue ~ when
         - 특정 조건식을 지정하고 조건식을 만족하면 반복 주기를 건너 뛰는 명령어
*/

loop