PL/SQL 기초 실습

set serveroutput on;
begin
dbms_output.put_line('안녕 PL/SQL');
end;


-- 스칼라 변수를 선언한다.
declare
    sonno number(4);
    sonname varchar2(12);
-- 실행문을 시작한다.
begin
    sonno := 1001;
    sonname := '홍길동';
    dbms_output.put_line(' 사번 이름');
    dbms_output.put_line(' ---------------');
    dbms_output.put_line(' ' || sonno || ' ' || sonname);
    -- 실행문을 종료한다.
end;


-- 레퍼런스 변수를 선언한다.
declare
    sonno emp.empno%type;
    sonname emp.ename%type;
-- 실행문을 시작한다.
begin
    select empno, ename into sonno, sonname
    from emp
    where ename = 'SMITH';
    -- 화면에 출력한다.
    dbms_output.put_line(' 사번 이름');
    dbms_output.put_line(' ---------------');
    dbms_output.put_line(' ' || sonno || ' ' || sonname);
    --dbms_output.put_line( sonno||sonname);
    -- 실행문을 종료한다.
end;


-- 레퍼런스 변수를 선언한다.
declare
    sonemp emp%rowtype;
    sonsal number(7,2);
begin
    select * into sonemp
    from emp
    where ename='SMITH';
    -- 커미션이 null 일 경우를 조건에 지정하고 수행한다.
    if (sonemp.comm is null) then
    sonsal := sonemp.sal*12;
    -- 조건이 false 이거나 null 이면 수행을 종료한다.
    end if;
    dbms_output.put_line(' 사번 이름 커미션');
    dbms_output.put_line('--------------------------');
    dbms_output.put_line(' '||sonemp.empno||' '||sonemp.ename||' '||sonsal);
end;

-- 레퍼런스 변수를 선언한다.
declare
sonemp emp%rowtype;
sonsal number(7,2);
begin
select * into sonemp
from emp
where ename='ALLEN';
-- 커미션이 null 일 경우를 조건에 지정하고 수행한다.
if (sonemp.comm is null) then
sonsal := sonemp.sal*12;
-- 조건이 false 이거나 null 이면 수행을 종료한다.
else sonsal := 0;
end if;
dbms_output.put_line(' 사번 이름 커미션');
dbms_output.put_line('--------------------------');
dbms_output.put_line(' '||sonemp.empno||' '||sonemp.ename||' '||sonsal);
end;


-- 저장 프로시저에 사용할 테이블을 emp 테이블에서 복사한다.
create table empcopy
as
select * from emp;
/*실습 2:
생성한 테이블 정보를 조회한다.*/
-- 생성한 테이블의 정보를 조회한다.
select * from empcopy;
/*실습 3: 
저장 프로시저를 생성한다.*/
-- 저장 프로시저를 생성한다.
create or replace procedure del_all
is
begin
-- empcopy 테이블의 저장된 데이터를 삭제한다.
delete from empcopy;
-- 트랜잭션 작업이 성공하여 완료한다.
commit;
end;

-- 저장 프로시저를 실행하여 empcopy 테이블에 저장된 데이터를 삭제한다.
execute del_all;
select * from empcopy;

-- user_source 시스템 테이블의 구조를 확인한다.
desc user_source;

-- 저장 프로시저의 코드를 확인한다.
select name, type, line, text from user_source;

--3) 저장 프로시저의 매개변수
-- 저장 프로시저에 사용할 테이블을 emp 테이블에서 복사한다.
create table empcopy2
as
select * from emp;

-- 생성한 테이블의 정보를 조회한다.
select * from empcopy2;
-- 매개변수가 존재하는 저장 프로시저를 생성한다.
create or replace procedure del_ename(sonename empcopy2.ename%type)
is
begin
-- empcopy 테이블의 저장된 데이터를 조건에 맞게 삭제한다.
delete from empcopy2 where ename like sonename;
-- 트랜잭션 작업이 성공하여 완료한다.
commit;
end;

execute del_ename('S%');
select * from empcopy2;


--4) 바인드 변수를 통한 부서 정보 조회
-- 매개변수가 존재하는 저장 프로시저를 생성한다.
create or replace procedure dept_select (
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달한다.
vdeptno in dept.deptno%type,
-- out 모드로 dept 테이블의 dname 컬럼값을 받아와서 vdname 매개변수에서 호출한다.
vdname out dept.dname%type,
-- out 모드로 dept 테이블의 loc 컬럼값을 받아와서 vloc 매개변수에서 호출한다.
vloc out dept.loc%type)
is
begin
-- select 문으로 수행한 결과값을 into 문 뒤에 선언한 변수에 저장한다.
select dname, loc into vdname, vloc from dept
where deptno=vdeptno;
end;

/*
컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를
선언한다.
variable 명령어를 사용하여 PL/SQL 블록에서도 사용할 수 있다.
저장 프로시저의 매개변수와 바인드 변수의 이름은 같을 필요는 없지만, 데이터 타입은 반드시
같아야 한다.
*/
variable vdname varchar2(30);
variable vloc varchar2(30);
-- 바인드 변수를 호출하는 저장 프로시저를 실행하고 바인드 변수는 :(콜론)을 덧붙여
--사용한다.
execute dept_select(40, :vdname, :vloc);
-- 바인드 변수는 print 명령어로 출력할 수 있으므로 print 명령어로 출력한다.
print vdname;
print vloc;


--5) 바인드 변수를 통한 사원정보 조회

--사원정보를 조회하는 저장 프로시저를 생성한다.
-- 매개변수가 존재하는 저장 프로시저를 생성한다.
create or replace procedure sel_empno (
sonempno in emp.empno%type,
sonename out emp.ename%type,
sonsal out emp.sal%type,
sonjob out emp.job%type)
is
begin
select ename, sal, job into sonename, sonsal, sonjob
from emp
where empno=sonempno;
end;
--실습 2: 
--바인드 변수로 데이터를 저장하고 사원 번호로 사원정보를 조회한다.
--컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를
--선언한다.
variable var_ename varchar2(15);
variable var_sal number;
variable var_job varchar2(9);
-- 바인드 변수를 호출하는 저장 프로시저를 실행하고 바인드 변수는 :(콜론)을 덧붙여
--사용한다.
execute sel_empno(7369, :var_ename, :var_sal, :var_job);
-- 바인드 변수는 print 명령어로 출력할 수 있으므로 print 명령어로 출력한다.
print var_ename;
print var_sal;
print var_job;

--6) 저장 프로시저의 insert 데이터 조작어
--dept 테이블에 데이터를 입력할 저장 프로시저를 생성한다.
create or replace procedure dept_insert(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달한다.
vdeptno in dept.deptno%type,
-- in 모드로 dept 테이블의 dname 컬럼에 입력한 값을 vdname 매개변수에 전달한다.
vdname in dept.dname%type,
-- in 모드로 dept 테이블의 loc 컬럼에 입력한 값을 vloc 매개변수에 전달한다.
vloc in dept.loc%type)
is
begin
insert into dept values(vdeptno, vdname, vloc);
end;
--실습 2:
--dept 테이블에 데이터를 입력할 저장 프로시저를 실행한다.
-- 저장 프로시저로 데이터를 입력한다.
execute dept_insert(80, '기획실','서울');
execute dept_insert(70, '안전과2','인천');
--실습 3: 
--dept 테이블에서 입력한 저장 프로시저 실행을 확인한다.
-- 입력한 저장 프로시저에 대한 실행을 확인한다.
select * from dept;


--7) 저장 프로시저의 update 데이터 조작어
--dept 테이블에 데이터를 수정할 저장 프로시저를 생성한다.
create or replace procedure dept_update(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달한다.
vdeptno in dept.deptno%type,
-- in 모드로 dept 테이블의 dname 컬럼에 입력한 값을 vdname 매개변수에 전달한다.
vdname in dept.dname%type,
-- in 모드로 dept 테이블의 loc 컬럼에 입력한 값을 vloc 매개변수에 전달한다.
vloc in dept.loc%type)
is
begin
update dept set deptno= vdeptno, dname= vdname, loc=vloc
where deptno = vdeptno;
end;
--실습 2: 
--dept 테이블에 저장 프로시저로 데이터를 수정한다.
-- 저장 프로시저로 데이터를 수정한다.
execute dept_update(50, '기획실','부산');
--실습 3:
--dept 테이블에서 수정한 저장 프로시저 실행을 확인한다.
-- 수정한 저장 프로시저에 대한 실행을 확인한다.
select * from dept;


--8) 저장 프로시저의 delete 데이터 조작어

--실습 1: 
--dept 테이블에 데이터를 삭제할 저장 프로시저를 생성한다.
create or replace procedure dept_delete(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달한다.
vdeptno in dept.deptno%type)
is
begin
delete from dept where deptno = vdeptno;
end;
--실습 2: 
--dept 테이블에 저장 프로시저로 데이터를 삭제한다.
-- 저장 프로시저로 데이터를 삭제한다.
execute dept_delete(60,70,80);
--실습 3: 
--dept 테이블에서 삭제한 저장 프로시저 실행을 확인한다.
-- 삭제한 저장 프로시저에 대한 실행을 확인한다.
select * from dept;


--1) 급여 200% 보너스 지급

-- 매개변수가 존재하는 저장 함수를 생성한다.
create or replace function cal_bonus(
-- in 모드로 emp 테이블의 empno 컬럼에 입력한 값을 sonempno 매개변수에 전달한다.
sonempno in emp.empno%type)
-- number 데이터 타입의 값을 반환한다.
return number
is
sonsal number(7, 2);
begin
select sal into sonsal
from emp
where empno = sonempno;
-- 조회 결과로 얻어진 급여로 200% 보너스를 구해서 함수의 결과값으로 반환한다.
return (sonsal * 2);
end;
--실습 2: 
--바인드 변수로 데이터를 저장하고 급여를 조회한다.
--컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를
--선언한다.
variable var_res number;
-- 바인드 변수를 호출하는 저장 함수를 실행하고 바인드 변수는 :(콜론)을 덧붙여 사용한다.
execute :var_res := cal_bonus(7369);
-- 바인드 변수는 print 명령어로 출력할 수 있으므로 print 명령어로 출력한다.
print var_res;


--1) 명시적 커서로 dept 테이블의 모든 데이터 조회

create or replace procedure cursor_call
is
-- %rowtype 속성으로 dept 테이블의 모든 컬럼을 참조하는 레퍼런스 sonemp 
--변수를 선언한다.
sondept dept%rowtype;
-- soncursor 커서를 생성한다.
cursor soncursor
is
select * from dept;
begin
dbms_output.put_line('부서번호 부서명 지역명');
dbms_output.put_line('--------------------------------------');
-- 생성한 soncursor 커서를 연다.
open soncursor;
-- end loop 문에서 제어권을 전달받고 반복한다.
loop
-- soncursor 커서에서 행을 정보를 획득하고 저장하고 다음 행으로 이동한다.
fetch soncursor into sondept.deptno, sondept.dname, sondept.loc;
-- 해당 커서 안에 수행해야 할 데이터가 없을 때 루프를 종료한다.
exit when soncursor%notfound;
dbms_output.put_line(sondept.deptno||' '||sondept.dname||' 
'||sondept.loc);
-- 제어권을 loop 문으로 전달한다.
end loop;
-- 사용한 soncursor 커서를 닫는다.
close soncursor;
end;
-- 화면 출력기능을 활성화한다.
set serveroutput on
-- 저장 프로시저를 실행하여 dept 테이블의 모든 컬럼을 출력한다.
execute cursor_call;



/*p_var1은 IN 매개변수로 써, 사용자가 입력한 값인 ‘A’ 그대로 출력된 것을 확인하실 수 있습니다.
OUT 매개변수인 p_var2 자리에 v_var2 변수를 넣어 ‘B’ 라는 값을 넘겨 줬음에도 불구하고 아무런 값도 없이 
출력된 것을 확인하실 수 있습니다. 반대로, IN OUT 매개변수인 p_var3에는 ‘C’ 란 값을 넘겨 줬더니 프로시저 
내부에서 ‘C’ 라는 값을 한 번 출력하고 다시 ‘B2’ 라는 값을 할당 받아서 최종적으로 v_var3 값은 ‘B2’ 가 
된 것을 확인하실 수 있습니다.*/
CREATE OR REPLACE PROCEDURE my_INOUT_proc
(
    p_var1 VARCHAR2,
    p_var2 OUT VARCHAR2,
    p_var3 IN OUT VARCHAR2
)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('p_var1 = ' || p_var1);
    DBMS_OUTPUT.PUT_LINE('p_var2 = ' || p_var2);
    DBMS_OUTPUT.PUT_LINE('p_var3 = ' || p_var3);
    p_var2 := 'A2';
    p_var3 := 'B2';
END;


DECLARE
    v_var1 VARCHAR2(100) := 'A';
    v_var2 VARCHAR2(100) := 'B';
    v_var3 VARCHAR2(100) := 'C'; 
BEGIN
    my_INOUT_proc(v_var1, v_var2, v_var3);
    DBMS_OUTPUT.PUT_LINE ('v_var2 = ' || v_var2);
    DBMS_OUTPUT.PUT_LINE ('v_var3 = ' || v_var3);
END;



--4. 트리거

-- emp_son 테이블을 생성한다.
create table emp_son(
empno number(4) primary key,
ename varchar2(12),
job varchar2(21)
);

-- 트리거를 생성한다.
create or replace trigger trg_son
-- insert 문이 실행되고 난 후에 트리거를 실행한다.
after
-- emp_son 테이블에 데이터가 입력할 때 이벤트를 발생시킨다.
insert on emp_son
begin
dbms_output.put_line('신입사원이 입사했습니다.');
end;

-- 화면 출력기능을 활성화한다.

insert into emp_son values(1, '홍길동', '대리');