[DB] 17. PLSQL

PLSQL (프로그래밍 SQL)

 

• 오라클에서 제공되는 프로그래밍 기능이다.
• 일반적인 프로그래밍과는 차이가 있지만, 오라클 내부에서 아주 적절한 방식을 적용해준다.
• 쿼리문의 집합으로 어떠한 동작을 일괄처리하기 위한 용도로 사용된다.
• 절차형 sql(PLSQL)은 코드 부분만 선택하여 PS로 컴파일 하여 실행한다.
• 실행시킬 부분만 드래그 해서 F5하면 실행된다.

 

➰ PLSQL 기본 사용 틀.

-- 출력을 허용함
set serveroutput on;

-- 변수를 선언할 수 있는 부분 declare
-- DML문을 선언 begin

DECLARE
    vi_num NUMBER; --변수 선언
BEGIN
    vi_num := 100; -- 대입
    DBMS_OUTPUT.PUT_LINE(vi_num); -- 출력
END;

➰ PLSQL 연산자

• 일반 SQL문의 모든 연산자의 사용이 가능하고
• 특별하게 **은 제곱을 의미한다.

DECLARE
    a NUMBER := 2*2*3*2; --4*9
BEGIN
    DBMS_OUTPUT.PUT_LINE('a = '||to_char(a)); -- 출력
END;

➰ PLSQL DML문

• DML문은 사용이 불가능하고, 일반적인 SQL문의 select절을 사용하는데
• 특이한 점은 select절 아래에 into절을 사용해 변수에 할당한다.

INTO로 변수가 대입되는 것을 설명

--이렇게 하면 에러남
declare
    v_emp_name varchar2(50); -- 사원명 변수 (문자열 변수는 길이 제약이 필요)
    v_dep_name varchar2(50); -- 부서명 변수
begin
    select a.first_name, b.department_name
    from employees a
    left outer join departments b
    on a.department_id = b.department_id
    where employee_id = 100;
end;

-- INTO구문 사용하여 만든 변수에 SELECT된 값을 대입
declare
    v_emp_name varchar2(50); -- 사원명 변수 (문자열 변수는 길이 제약이 필요)
    v_dep_name varchar2(50); -- 부서명 변수
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;



-- 테이블과 같은 값의 TYPE을 주는 방법
declare
    --해당 테이블과 같은 타입의 컬럼 변수를 선언하려면 테이블명. 컬럼명%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(
    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. 구구단 중 3단을 출력하는 익명 블록을 만들어보자
declare
    dan number := 3;
    num1 number := 1;
begin
    loop
    dbms_output.put_line(dan || '*' || num1 || '=' || dan * num1);
    num1 := num1+1;
    exit when num1 > 9;
    end loop;
end;

--2. 사원 테이블에서 201번 사원의 이름과 이메일 주소를 출력하는 익명 블록을 만들어보자.
declare
    e_name varchar(100);
    e_email employees.email%type;
begin
    select concat(e.first_name||' ', e.last_name), e.email
    into e_name, e_email
    from employees e
    where e.employee_id = 201;
    
    dbms_output.put_line('이름: ' || e_name || ' , 이메일: ' || e_email);
end;

select first_name, email from employees where employee_id = 201;

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

declare
    input_id emps.employee_id%type;
begin
    select max(e.employee_id)
    into input_id
    from employees e;
    
    insert into emps (employee_id, last_name, email, hire_date, job_id)
    values(input_id + 1, 'steven', 'stevenjobs', sysdate, 'ceo');
end;

select * from emps;

select * from (select max(employee_id) from employees);

 

 

PLSQL 제어문

➰ 랜덤한 수 출력

-- 랜덤수 출력
declare
    --ROUND로 감싸서 정수만 얻기...
    v_num number := round(dbms_random.value(0,10));
begin
    dbms_output.put_line(v_num);
end;

➰ IF문

-- IF문장
-- IF문 (IF (조건) THEN ELSE END IF);
/*
if (조건) then 구문 -- {}대신 then을 사용한다.
else 구문
end if -- 꼭들어가줘야 됨.
*/
declare
    num1 number := 5;
    num2 number := trunc(dbms_random.value(1,11)); -- 랜덤수로 지정.
begin
    if (num1 >= num2) then 
        dbms_output.put_line('1 - ' || num1 || '이(가) 큰 수 입니다.');
    else
        dbms_output.put_line('2 - ' || num2 || '이(가) 큰 수 입니다.');
    end if;
end;

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

➰ 실습

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

declare
    --round에서 -1을 해주면 정수 첫번째 자리에서 반올림(즉 10단위로 나온다)
    ran_num number := round(dbms_random.value(10,120), -1);
    compare_salary employees.salary%type;
begin
    dbms_output.put_line(ran_num);
    
    select salary 
    into compare_salary
    from employees 
    where department_id = ran_num and rownum = 1;
    
    if compare_salary >= 9000 then
        dbms_output.put_line(compare_salary || '는 높음');
    elsif compare_salary >= 5000 then
        dbms_output.put_line(compare_salary || '는 중간');
    else
        dbms_output.put_line(compare_salary || '는 낮음');
    end if;
end;

select a.* from (select rownum as rn, e.* from employees e where department_id = 20) a where a.rn in 1;

select salary from employees where department_id = 20 and rownum = 1;

➰ CASE문

--CASE문

declare
    --round에서 -1을 해주면 정수 첫번째 자리에서 반올림(즉 10단위로 나온다)
    ran_num number := round(dbms_random.value(10,120), -1);
    compare_salary employees.salary%type;
begin
    dbms_output.put_line(ran_num);
    
    select salary 
    into compare_salary
    from employees 
    where department_id = ran_num and rownum = 1;
    
    CASE WHEN compare_salary >= 9000 then dbms_output.put_line(compare_salary || '는 높음');
        WHEN compare_salary >= 5000 then dbms_output.put_line(compare_salary || '는 중간');
        ELSE dbms_output.put_line(compare_salary || '는 낮음');
    end CASE;
end;

 

➰ WHILE문

--반복문 WHILE
DECLARE
    A NUMBER := 3;
    B NUMBER := 1;
BEGIN
    WHILE B <= 9 
    LOOP
        dbms_output.put_line(A || '*' || B || '=' || A * B);
        B := B + 1;
    END LOOP;
END;

--탈출문 EXIT WHEN 조건
DECLARE
    A NUMBER := 3;
    B NUMBER := 1;
BEGIN
    WHILE B <= 9 
    LOOP
        dbms_output.put_line(A || '*' || B || '=' || A * B);
        B := B + 1;
        EXIT WHEN B = 5; -- 탈출
    END LOOP;
END;

➰ FOR문

--FOR구문 FOR변수면 IN 1...9
DECLARE
    A NUMBER := 3;
BEGIN
    FOR I IN 1..9
    LOOP
    CONTINUE WHEN I = 5;
        DBMS_OUTPUT.PUT_LINE(A || 'X' || I || '=' || A*I);
    END LOOP;
END;

 

➰ 실습

--실습
/*
1.모든 구구단을 출력하는 익명블록을 만드세요
2. INFO 테이블(시퀀스사용)에 INSERT를 300번 실행하는 익명블록을 처리하세요.
*/

-- 1.모든 구구단을 출력하는 익명블록을 만드세요
DECLARE
    dan number := 1;
    num1 number := 1;
BEGIN
   
    while dan <= 9
    loop
       
        while num1 <= 9
        loop
            dbms_output.put_line(dan||'*'||num1||'='||dan*num1);
            num1 := num1 + 1;
        end loop;
        num1 := 1; --다시 초기화
        dan := dan + 1;
        
    end loop;
    
END;

-- 2. INFO 테이블(시퀀스사용)에 INSERT를 300번 실행하는 익명블록을 처리하세요.
SELECT * FROM INFO;
delete from info where id = 1;

create SEQUENCE test_seq
    increment by 1
    start with 1
    maxvalue 1000
    minvalue 1
    nocache
    nocycle;

insert into info values (1, 'a','a', sysdate, 1);

DECLARE
    c number := 1;
BEGIN
    while c <= 300
    LOOP
        insert into info values (seq_info.nextval, 'title', 'content', sysdate, c);
        c := c+1;
    END LOOP;
END;