PL/SQL 시작하기

-------------------------------------------------------------------------------------------
PL/SQL을 하기전 준비와 가벼운 워밍업
-------------------------------------------------------------------------------------------
##테이블스페이스 생성
C:\> sqlplus sys as sysdba
우선 sys계정으로 접속

CREATE TABLESPACE raison_data
         DATAFILE ’D:\oracle\product\10.2.0\oradata\raison\raison_data.dbf’ SIZE 100M
         DEFAULT STORAGE
             (INITIAL    10K
              NEXT      10K
              MINEXTENTS 20
              MAXEXTENTS 500
              PCTINCREASE 500)
##User 생성
CREATE USER dev1 IDENTIFIED BY dev1
DEFAULT TABLESPACE raison_data

##User 연결 권한부여
GRANT connect, resource TO dev1 ;

##테이블생성
CREATE TABLE test_zipcode(
        postnum    NUMBER CONSTRAINT test_pk_postnum PRIMARY KEY,
        seq        VARCHAR2(3),
        addr    VARCHAR2(100),
        udate    VARCHAR2(8));

CREATE TABLE test_zipcode3(
        postnum01    VARCHAR2(3),
        postnum02    VARCHAR2(3),
        seq        VARCHAR2(3),
        addr    VARCHAR2(100),
        udate    VARCHAR2(8))

##테이블을 생성하면서 data copy
create table test_zipcode2
AS SELECT * FROM test_zipcode

##컬럼이 다르게 생성되어진 테이블에 data copy
insert into test_zipcode3
select to_number(substr(postnum, 1, 3)) as postnum1, to_number(substr(postnum, 4, 6)) as postnum2, seq, addr, udate from test_zipcode2

##procedure를 이용한 테이블 생성
SQL> grant create any table to scott;
SQL> grant execute on dbms_sql to scott;
대상 user에게 dbms_sql을 사용할 수 있는 권한을 부여

create or replace procedure table_copy(tname in varchar2)
    is
        rows_inserted integer;
        cur integer := dbms_sql.open_cursor;        --SQL문의 실행에 필요한 새로운 cursor를 열고 cursor ID number를 return한다.
        c_tname varchar2(50) := 'copy_' || tname;
        credbsql varchar2(100) := 'create table ' || c_tname || ' as select * from ' || tname;
    begin
        dbms_output.enable;
        dbms_sql.parse(cur, credbsql, dbms_sql.native);        --statement를 check하고 cursor와 결합시킨다.
        rows_inserted := dbms_sql.execute(cur);                --SQL문을 실행하고 처리된 Row의 수를 return한다.(insert, update, delete인 경우에만 해당)
        dbms_sql.close_cursor(cur);

        dbms_output.put_line('복사된 테이블명: ' || c_tname);
        dbms_output.put_line('복사된 테이블라인: ' || rows_inserted);
        dbms_output.put_line('success!!');
    end;
/
execute table_copy('test_zipcode');

SQL> select * from tab;
현재 user가 가지고 있는 테이블목록

##function을 이용한 간단한 예제
  부서번호를 입력받아 해당 부서 급여의 합을 출력(sum사용하지 않음)
create or replace function f_sum_sal(p_deptno emp.deptno%type)
    return number;
    is
        v_sumsal number := 0;
    begin
        dbms_output.enable;
        for sal_sumlist in (select sal from emp where deptno = p_deptno)
        loop
            v_sumsal := v_sumsal+sal_sumlist.sal;
        end loop;
        dbms_output.put_line(v_sumsal);
        return v_sumsal;
    end f_sum_sal;
    /
execute sum_sal(20);

##insert, update, delete시 commit이 필요
create or replace procedure p_update_sal(v_deptno in number)
    is

    begin
        update emp
        set sal = sal+1000
        where deptno = v_deptno;
        commit;
    end p_update_sal;
    /
execute update_sal(20);

-------------------------------------------------------------------------------------------
PL/SQL 혼자 공부하면서 풀어본 몇개 문제들
-------------------------------------------------------------------------------------------
##부서번호를 입력받아 해당 부서의 급여에 1000을 인상시키는 프로시저를 작성하라.
create or replace procedure update_sal(v_deptno in number)
    is

    begin
        update emp
        set sal = sal+1000
        where deptno = v_deptno;
        commit;
    end update_sal;
    /
execute update_sal(20);

##사원번호를 입력받아 이름,부서명,연급여를 출력하라.
create or replace procedure emp_info(p_empno in emp.empno%type)
    is
        v_empno emp.empno%type;
        v_deptno emp.deptno%type;
        v_sal   emp.sal%type;
        v_dname dept.dname%type;
    begin
        dbms_output.enable;
        select empno, sal, deptno
        into v_empno, v_sal, v_deptno
        from emp
        where empno = p_empno;
       
        select dname
        into v_dname
        from dept
        where deptno = v_deptno;

        dbms_output.put_line('사원번호: ' ||v_empno);
        dbms_output.put_line('부서명: ' ||v_dname);
        dbms_output.put_line('연급여: ' ||v_sal*12);
    end emp_info;
    /
execute emp_info(7369);

##부서번호를 입력받아 해당 부서 급여의 합을 출력하라 세자리마다 구분기호를 표시하고 원화를 표시하라.
create or replace procedure sum_sal(p_deptno emp.deptno%type)
    is
        v_sumsal number := 0;
    begin
        dbms_output.enable;
        select sum(sal)
        into v_sumsal
        from emp
        where deptno = p_deptno;
        dbms_output.put_line('부서급여 합계: ' ||to_char(v_sumsal, '999,999,999,999')||'\');
    end sum_sal;
    /
execute sum_sal(20);

##사원번호,사원명,부서명,상사명을 RECORD TYPE 의 변수를 선언하여 임의의 사원을 출력하라.
create or replace procedure test_record(p_empno in emp.empno%type)
    is
        type emp_rec_type is
        record(    v_ename emp.ename%type,
                v_mgr emp.mgr%type,
                v_deptno emp.deptno%type,
                v_dname dept.dname%type,
                v_kname emp.ename%type);

        emp_rec emp_rec_type;
    begin
        dbms_output.enable;
        select ename, mgr, deptno
        into emp_rec.v_ename, emp_rec.v_mgr, emp_rec.v_deptno
        from emp
        where empno = p_empno;

        select ename
        into emp_rec.v_kname
        from emp
        where empno = emp_rec.v_mgr;

        select dname
        into emp_rec.v_dname
        from dept
        where deptno = emp_rec.v_deptno;

        dbms_output.put_line('사원번호: ' ||p_empno);
        dbms_output.put_line('사원명: ' ||emp_rec.v_ename);
        dbms_output.put_line('부서명: ' ||emp_rec.v_dname);
        dbms_output.put_line('상사명: ' ||emp_rec.v_kname);
    end test_record;
    /
execute test_record(7369);

##임의의 사원번호,사원명,급여,급여등급,상한값,하한값을 출력하라.
create or replace procedure test_ifloop(p_empno in emp.empno%type)
    is
        v_emp emp%rowtype;
        v_salgrade salgrade%rowtype;
    begin
        dbms_output.enable;
        select ename, sal
        into v_emp.ename, v_emp.sal
        from emp
        where empno = p_empno;

        for sal_list in (select * from salgrade)
        loop
            if v_emp.sal >= sal_list.losal and v_emp.sal <= sal_list.hisal
            then
                v_salgrade.grade := sal_list.grade;        --등급
                v_salgrade.losal := sal_list.losal;        --하한값
                v_salgrade.hisal := sal_list.hisal;        --상한값
            end if;
        end loop;

        dbms_output.put_line('사원번호: ' ||p_empno);
        dbms_output.put_line('사원명: ' ||v_emp.ename);
        dbms_output.put_line('급여: ' ||v_emp.sal);
        dbms_output.put_line('급여등급: ' ||v_salgrade.grade);
        dbms_output.put_line('상한값: ' ||v_salgrade.hisal);
        dbms_output.put_line('하한값: ' ||v_salgrade.losal);
    end test_ifloop;
    /
execute test_ifloop(7369);

##사원번호를 입력받아 급여가 3000 이상이면 보너스가 60%,
2000 이상이면 70%
그 이하이면 80%를 지급하고
사원번호,이름,급여,지급된 보너스를 출력하라.(IF문)
create or replace procedure test_bonus(p_empno in emp.empno%type)
    is
        bonus emp.sal%type;
        v_emp emp%rowtype;
    begin
        dbms_output.enable;
        select sal, ename
        into v_emp.sal, v_emp.ename
        from emp
        where empno = p_empno;

        /* 급여에 따른 보너스 */
        if v_emp.sal >= 3000
        then
            bonus := v_emp.sal * 60/100;
        elsif v_emp.sal >= 2000
        then
            bonus := v_emp.sal * 70/100;
        else
            bonus := v_emp.sal * 80/100;
        end if;

        dbms_output.put_line('사원번호: ' ||p_empno);
        dbms_output.put_line('이름: ' ||v_emp.ename);
        dbms_output.put_line('급여: ' ||v_emp.sal);
        dbms_output.put_line('지급된 보너스: ' ||bonus);
    end;
/
execute test_bonus(7369);