day38-DB: sql

1. 프로시저

-- PLSQL(프로그래밍 SQL)
/*
오라클에서 제공되는 프로그래밍 기능이다.
일반적인 프로그래밍과는 차이가 있지만, 오라클 내부에서 아주 적절한 방식을 적용해준다.
쿼리문의 집합으로 어떠한 동작을 일괄처리 하기 위한 용도로 사용됩니다.

절차형 SQL(PLSQL)은 코드 부분만 선택하여 f5로 컴파일 하여 실행합니다.
*/
-- 오라클에서는 출력 구문으로 나올 수 있는 것을 선언해줘야 함. (출력을 허용함)
set serveroutput on;

DECLARE  -- 실행시키고 싶은 부분만 선택 후 f5로 실행
    vi_num NUMBER; -- 변수 선언(변수명, 타입)
BEGIN
    vi_num := 100; -- 대입(:= - 대입 연산자)
    DBMS_OUTPUT.PUT_LINE(vi_num); -- 출력(sysout)
END;

/*
-- 연산자
일반 SQL문의 모든 연산자의 사용이 가능하고 특별하게 **는 제곱을 의미합니다 
*/
DECLARE
 a number := 2**2*3**2; --4 * 9
BEGIN
 DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

-- DML문
-- DDL문은 사용이 불가능하고, 일반적인 SQL문의 select절을 사용하는데
-- 특이한 점은 select절 아래에 into 절을 사용해 변수에 할당합니다.
DECLARE 
 --v_emp_name VARCHAR2(50); -- 사원명 변수(문자열 변수는 길이제약이 필요)
 --v_dep_name VARCHAR2(50); -- 부서명 변수
 --해당 테이블과 같은 타입의 컬럼 변수를 선언하려면 테이블명, 컬럼명%type을 사용합니다. (오류)
 v_emp_name employees.first_name%TYPE;
 v_dep_name departments.department_name%TYPE;
BEGIN
 SELECT a.first_name, b.department_name
 INTO v_emp_name, v_dep_name -- 대입을 의미
 FROM employees a
 LEFT OUTER JOIN departments b
 ON a.department_id = b.department_id
 WHERE employee_id = 100;
 
 DBMS_OUTPUT.PUT_LINE(v_emp_name || ' - ' || v_dep_name);
end;

-- select문과 insert문 dml문을 같이 사용할 수 있습니다.
create table emp_sal( -- 드래그 하고 f9
    emp_years varchar2(50),
    emp_salary number(10)
);

-- 년도별 사원의 급여합을 구해서 새로운 테이블 insert
declare
    emp_sum employees.salary%type;
    emp_years emp_sal.emp_years%type := 2008;
begin
    -- select
    select sum(salary)
    into emp_sum
    from employees
    where to_char(hire_date, 'yyyy') = emp_years;
    
    -- insert
    insert into emp_sal values(emp_years, emp_sum);

    -- commit
    commit;
    dbms_output.put_line(emp_sum);
end;

select * from emp_sal; -- 이거만 드래그해서 선택

 

1. 1) 프로시저 예제

-- 1. 구구단 중 3단을 출력하는 익명 블록을 만들어보자.
DECLARE
     
BEGIN
 DBMS_OUTPUT.PUT_LINE('3 * 1 = ' || 3*1);
 DBMS_OUTPUT.PUT_LINE('3 * 2 = ' || 3*2);
 DBMS_OUTPUT.PUT_LINE('3 * 3 = ' || 3*3);
 DBMS_OUTPUT.PUT_LINE('3 * 4 = ' || 3*4);
 DBMS_OUTPUT.PUT_LINE('3 * 5 = ' || 3*5);
 DBMS_OUTPUT.PUT_LINE('3 * 6 = ' || 3*6);
 DBMS_OUTPUT.PUT_LINE('3 * 7 = ' || 3*7);
 DBMS_OUTPUT.PUT_LINE('3 * 8 = ' || 3*8);
 DBMS_OUTPUT.PUT_LINE('3 * 9 = ' || 3*9);
END;

-- 2. 사원 테이블에서 201번 사원의 이름과 이메일 주소를 출력하는 익명 블록을 만들어보자.
declare
   -- v_emp_name VARCHAR2(50); -- 사원명 변수(문자열 변수는 길이제약이 필요)
   -- v_emp_email VARCHAR2(50);
    
   -- 해당 테이블과 같은 타입의 컬럼 변수를 선언하려면 테이블명, 컬럼명%type을 사용합니다. 
 v_emp_name employees.first_name%TYPE;
 v_emp_email employees.email%TYPE;
begin
 select first_name, email
 into v_emp_name, v_emp_email
 from employees
 where employee_id = 201;
 
DBMS_OUTPUT.PUT_LINE(v_emp_name || ' - ' || v_emp_email);

end;

-- 3. 사원 테이블에서 사원번호가 제일 큰 사원을 찾아낸 뒤
-- 이 번호 +1번으로 아래의 사원을 emps에 
-- employee_id, last_name, email, hire_date, job_id를 신규 입력하는 익명 블록을 만들어 보자.
-- hint: select 절 이후에 insert문의 사용이 가능합니다.
-- <사원명> : steven
-- <이메일> : stevenjobs
-- <입사일자> : 오늘 날짜(sysdate)
-- <job_id> : CEO
create table emps as (select * from employees where 1 = 2);

declare
    vn_max_empno employees.employee_id%type; -- 최대값 저장
    vs_email     employees.email%type; -- 이메일 저장
    
begin
    -- select
    select max(employee_id)
    into vn_max_empno
    from employees;
    
    -- insert
    insert into emps (employee_id, last_name, email, hire_date, job_id) 
    values(vn_max_empno + 1, 'steven', 'stevenjobs', sysdate, 'CEO');

    -- commit
    commit;
end;

select * from emps;

 

2. PLSQL 제어문

--PLSQL 제어문
--랜덤 수 출력
declare
    v_num number := round(dbms_random.value(0, 10) ); -- 0~10미만, round 반올림
begin
    dbms_output.put_line(v_num);
end;

-- if 문장
-- if문 (if(조건) then else end if);
declare
 num1 number := 5;
 num2 number := trunc(dbms_random.value(1, 11)); -- 랜덤수 저장
begin
    dbms_output.put_line('랜덤수' || num2);
    
 if(num1 >= num2) then 
    dbms_output.put_line(num1 || '이 큰수 입니다.'); 
 else
    dbms_output.put_line(num2 || '이 큰수 입니다.'); 
 end if;
end;

-- elseif문

declare
 ran_num number := trunc(dbms_random.value(1,101) );
begin

    if ran_num >= 90 then
        dbms_output.put_line('a학점 입니다');
    elsif ran_num >= 80 then
        dbms_output.put_line('b학점 입니다');
    elsif ran_num >= 70 then
        dbms_output.put_line('c학점 입니다');
    else
        dbms_output.put_line('d학점 입니다');
    end if; 
    
end;

------------------실습-------------------------
--첫번째 값은 rownum을 이용하면 됩니다.
--10~120 사이의 10단위 랜덤한 번호를 이용해서 department_id의 첫번째 행만 select합니다.
--뽑은 사람의 salary가 9000이상이면 높음, 5000이상이면 중간, 나머지는 낮음으로 출력

declare
 sal employees.salary%type;
 ran number := round(dbms_random.value(10, 120), -1); -- -1은 정수 첫번째 자리에서 반올림이 올라감
 
begin

    select salary
    into sal
    from employees
    where department_id = ran and rownum = 1;
    
    dbms_output.put_line('급여:' || sal);
    
    if sal >= 9000 then
    dbms_output.put_line('높음');
    elsif sal >= 5000 then
    dbms_output.put_line('중간');
    else
    dbms_output.put_line('낮음');
    end if;
    
end;

-- case when then
declare
 sal employees.salary%type;
 ran number := round(dbms_random.value(10, 120), -1); -- -1은 정수 첫번째 자리에서 반올림이 올라감
 
begin

    select salary
    into sal
    from employees
    where department_id = ran and rownum = 1;
    
    dbms_output.put_line('급여:' || sal);
    
    case when sal >= 9000 then
    dbms_output.put_line('높음');
    when sal >= 5000 then
    dbms_output.put_line('중간');
    else
    dbms_output.put_line('낮음');
    end case;
    
end;
 
-- 반복문 while, for
declare 
 a number := 3;
 b number := 1;
begin

    while b <= 9
    loop 
        dbms_output.put_line(a || ' x ' || b || ' = ' || a*b);
        b := b + 1;
    end loop;
    
end;

-- 탈출문 exit when 조건
-- conitnue when 조건
declare 
 a number := 3;
 b number := 1;
begin

    while b <= 9
    loop 
        dbms_output.put_line(a || ' x ' || b || ' = ' || a*b);
        exit when b = 5; -- 탈출
        
        b := b + 1;
    end loop;
    
end;

--실습
--1. 모든 구구단을 출력하는 익명블록을 만드세요
DECLARE
   
BEGIN   
    FOR i IN 2..9
    LOOP
        DBMS_OUTPUT.PUT_LINE ('구구단:' || i || '단');
        FOR j in 1..9
        LOOP
              DBMS_OUTPUT.PUT_LINE (i || ' x ' || j || ' = ' || i*j);      
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE ('-------------------');
    END LOOP;
END;

--2. INFO테이블(시퀀스사용)에 insert를 300번 실행하는 익명블록을 처리하세요.
DECLARE
    I NUMBER := 1;
BEGIN
    WHILE I <= 300
    LOOP
        INSERT INTO INFO(ID, TITLE, CONTENT, REGDATE) VALUES(SEQ_INFO.NEXTVAL, 'TEST'|| I, 'TEST'|| I, SYSDATE);  
        INSERT INTO INFO(ID, TITLE, CONTENT, REGDATE) VALUES(SEQ_INFO.NEXTVAL, 'ADMIN'|| I, 'ADMIN'|| I, SYSDATE); 
        
        I := I + 1;
    END LOOP;
    COMMIT;
END;

 

3. 저장 프로시저

/*
저장 프로시저 - 하나의 함수처럼 실행하기 위한 쿼리의 집합.
만드는 과정과, 실행하는 구문이 나누어 작성합니다.
*/

--프로시저 생성
/*
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC --매개변수
IS --변수의 선언 영역
BEGIN --실행 영역
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');    
END;
*/
--프로시저 실행
EXECUTE NEW_JOB_PROC;

--프로시저 매개변수 IN
/*
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
    (P_JOB_ID IN JOBS.JOB_ID%TYPE,
     P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
     P_MIN_SAL IN JOBS.MIN_SALARY%TYPE,
     P_MAX_SAL IN JOBS.MAX_SALARY%TYPE
    )
IS
BEGIN
    INSERT INTO JOBS 
    VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);
    
END;
*/
EXECUTE NEW_JOB_PROC('SM_MAN1', 'sample test', 1000, 5000);

--프로시저 응용
EXECUTE NEW_JOB_PROC('SM_MAN1', 'sample test222', 1000, 5000);

/*
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
    (P_JOB_ID IN JOBS.JOB_ID%TYPE,
     P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
     P_MIN_SAL IN JOBS.MIN_SALARY%TYPE,
     P_MAX_SAL IN JOBS.MAX_SALARY%TYPE
    )
IS
    V_COUNT NUMBER := 0; --지역변수
BEGIN

    SELECT COUNT(*)
    INTO V_COUNT
    FROM JOBS
    WHERE JOB_ID = P_JOB_ID; -- V_COUNT의 데이터개수 할당
    -- V_COUNT가 없다면 INSERT 있다면 UPDATE
    IF V_COUNT = 0 THEN
        INSERT INTO JOBS 
        VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);    
    ELSE 
        UPDATE JOBS 
        SET JOB_TITLE = P_JOB_TITLE,
            MIN_SALARY = P_MIN_SAL,
            MAX_SALARY = P_MAX_SAL
        WHERE JOB_ID = P_JOB_ID;
    END IF;
END;
*/

--프로시저의 디폴트 매개변수
EXECUTE NEW_JOB_PROC('SM_MAN1', 'sample test222'); --매개변수가 일치 하지 않기때문에 에러

/*
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
    (P_JOB_ID IN JOBS.JOB_ID%TYPE,
     P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
     P_MIN_SAL IN JOBS.MIN_SALARY%TYPE := 0,
     P_MAX_SAL IN JOBS.MAX_SALARY%TYPE := 1000
    )
IS
    V_COUNT NUMBER := 0; --지역변수
BEGIN

    SELECT COUNT(*)
    INTO V_COUNT
    FROM JOBS
    WHERE JOB_ID = P_JOB_ID; -- V_COUNT의 데이터개수 할당
    -- V_COUNT가 없다면 INSERT 있다면 UPDATE
    IF V_COUNT = 0 THEN
        INSERT INTO JOBS 
        VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);    
    ELSE 
        UPDATE JOBS 
        SET JOB_TITLE = P_JOB_TITLE,
            MIN_SALARY = P_MIN_SAL,
            MAX_SALARY = P_MAX_SAL
        WHERE JOB_ID = P_JOB_ID;
    END IF;
END;
*/
--디폴트 매개변수
EXECUTE NEW_JOB_PROC('SA_MAN2', 'sample test222'); 
EXECUTE NEW_JOB_PROC('SA_MAN3', 'sample test222', 1000, 5000); 

----------------------------------------------------------
--매개변수 OUT
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
    (P_JOB_ID IN JOBS.JOB_ID%TYPE,
     P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
     P_MIN_SAL IN JOBS.MIN_SALARY%TYPE := 0,
     P_MAX_SAL IN JOBS.MAX_SALARY%TYPE := 1000,
     P_RESULT OUT VARCHAR2 --OUT
    )
IS
    V_COUNT NUMBER := 0; --지역변수
BEGIN

    SELECT COUNT(*)
    INTO V_COUNT
    FROM JOBS
    WHERE JOB_ID = P_JOB_ID; -- V_COUNT의 데이터개수 할당
    -- V_COUNT가 없다면 INSERT 있다면 UPDATE
    IF V_COUNT = 0 THEN
        INSERT INTO JOBS 
        VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);    
        
        --OUT변수에 '성공' 할당
        P_RESULT := '성공';       
    ELSE 
        UPDATE JOBS 
        SET JOB_TITLE = P_JOB_TITLE,
            MIN_SALARY = P_MIN_SAL,
            MAX_SALARY = P_MAX_SAL
        WHERE JOB_ID = P_JOB_ID;
        
        --OUT변수에 '업데이트' 할당
         P_RESULT := '업데이트';       
        
    END IF;
    
    --예외처리
    EXCEPTION WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');
   
END;
-- OUT변수를 활용하려면 익명블록에서 실핼
DECLARE
    STR VARCHAR2(20); -- OUT을 돌려받을 변수
BEGIN
    NEW_JOB_PROC('SA_MAN4', 'SAMPLE', 0, 1000, STR);
    DBMS_OUTPUT.PUT_LINE(STR); --결과
END;

-------------------------------------------------------------------------------
--실습
--EMPLOYEE_ID를 받아서 EMPLOYEES에 존재하면, 근속년수를 출력
--없다면, ID는 없습니다 를 출력하는 프로시저.


CREATE OR REPLACE PROCEDURE EMP_YEAR_PROC
    (P_EMP_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE
    )
IS --프로시저에서 사용할 매개변수
    V_COUNT NUMBER := 0;
    V_YEAR NUMBER := 0;
BEGIN
    SELECT COUNT(*)
    INTO V_COUNT
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMP_ID;

    --
    IF V_COUNT = 0 THEN --없는경우
        DBMS_OUTPUT.PUT_LINE(P_EMP_ID || '는 없습니다');
    ELSE --존재하는 경우
        SELECT TRUNC((SYSDATE - HIRE_DATE) / 365)
        INTO V_YEAR
        FROM EMPLOYEES 
        WHERE EMPLOYEE_ID = P_EMP_ID;
        
        DBMS_OUTPUT.PUT_LINE(P_EMP_ID || '의 근속년수:' || V_YEAR);
    END IF;
   
END;

EXECUTE EMP_YEAR_PROC(101);

 

4.  트리거

/*
trigger는 테이블에 부착한 형태로써, insert, update, delete 작업이 수행될 때 
특정 코드가 작동되도록 하는 구문입니다.

트리거는 실행할 때 범위를 지정하고 f5버튼으로 부분 실행 해야 합니다.
그렇지 않으면 하나의 구문으로 인식되어 정상 동작하지 않습니다.
*/

create table tbl_test (
    id number(10),
    text varchar2(20)
);

select * from tbl_test;

-- 
set SERVEROUTPUT on;
/*
CREATE or REPLACE TRIGGER trg_test 
    AFTER update or delete -- 삭제, 수정 이후에 동작
    on tbl_test -- 부착할 테이블
    for each row -- 각 행에 적용
DECLARE -- 지역변수 선언

BEGIN
    dbms_output.put_line('트리거가 동작함');
end;
*/

insert into tbl_test values(1, '홍길동');
insert into tbl_test values(2, '이순신');
update tbl_test set text = '홍길동' where id = 1;
delete from tbl_test where id = 2;

--------------------------------------------------------------------------------
-- before vs after
/*
after 트리거 - insert, update, delete 작업 이후에 동작하는 트리거를 의미합니다.
before 트리거 - insert, update, delete 작업 이전에 동작하는 트리거를 의미합니다.

:OLD = 참조 전 열의 값(insert: 입력 전 자료, update: 수정 전 자료, delete: 삭제할 자료)
:NEW = 참조 후 열의 값(insert: 입력 할 자료, update: 수정 된 자료)
*/

create table tbl_user_backup(
    id varchar2(20),
    name varchar2(20),
    address varchar2(30),
    updatedate date default sysdate,
    m_type char(10), -- 변경 타입
    m_user varchar2(20) -- 변경한 사용자
);
select * from tbl_user_backup;

create table tbl_user( --update나 delete가 실행 되면 기존 데이터는 삭제 됨(기존에 있던 데이터를 backup 테이블에 이전)
    id varchar2(20) primary key,
    name varchar2(20),
    address varchar2(30)
);
select * from tbl_user;

-- update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관(f5 하기)
create or replace trigger trg_user_backup
    after update or delete 
    on tbl_user 
    for each row 
declare -- 변수 선언
    vn_type varchar2(10);

begin

    if updating then -- 업데이트 or 딜리트 
        vn_type := '수정';
    elsif deleting then
        vn_type := '삭제';
    end if;
    
    -- user_backup에 인서트
    insert into tbl_user_backup values (:old.id, :old.name, :old.address, sysdate, vn_type, user() ); -- 마지막, 현재 로그인된 유저 
    
end;

insert into tbl_user values('test01', 'admin', '서울');
insert into tbl_user values('test02', 'admin', '경기');
insert into tbl_user values('test03', 'admin', '부산');
update tbl_user set address = '부산' where id = 'test01'; --동작
delete from tbl_user where id = 'test02'; -- 동작

select * from tbl_user_backup;
select * from tbl_user;

-- before 
create or replace trigger trg_user_insert
    before insert -- tbl_user에 insert되기 이전에, :new-새롭게 들어가는 데이터
    on tbl_user
    for each row
declare

begin
   :new.name := substr(:new.name, 1, 1) || '**'; -- 문자열 자르기: 가장 앞자리를 자름(뒤에 변경된 데이터가 new.name으로 들어옴)/입력 전 데이터 ** 붙임
end;

insert into tbl_user values('test04', 'admin', '부산');
insert into tbl_user values('test05', 'admin', '경기');


select * from tbl_user;