Oracle 기본정리 - Procedure(if,for,while,in,out 예제)

Oracle 기본정리 - Procedure(if,for,while,in,out 예제)

------------------------------------------------------------
--문제2 if문
/*
2000미만 low
2000~4000미만 middle
4000이상 high

if...
deptno=10 'a'
deptno=20 'a'
deptno=30 'a'
deptno=40 'a'

사원번호 이름 급여 급여결과 부서코드 부서명
*/
 
create or replace procedure proc8(a number)
is
    TYPE emp_rec_type is record(empno emp.empno%type
                                ,ename emp.ename%type
                                ,sal emp.sal%type
                                ,deptno emp.deptno%type);
    v_emp_rec emp_rec_type;
    v_dname varchar2(1);
    v_result varchar2(6);
   
    begin
        select empno, ename, sal, deptno into v_emp_rec
        from emp
        where empno=a;
       
        if (v_emp_rec.sal < 2000) then
            v_result:='low';
        elsif (v_emp_rec.sal between 2000 and 4000) then
            v_result:='middle';
        else
             v_result:='high';
        end if;
       
        if (v_emp_rec.deptno=10) then
            v_dname:='a';
        elsif (v_emp_rec.deptno=20) then
           v_dname:='b';
        elsif (v_emp_rec.deptno=30) then
            v_dname:='c';
        elsif (v_emp_rec.deptno=40) then
            v_dname:='d';
        end if;
       
        dbms_output.put_line(v_emp_rec.empno);
        dbms_output.put_line(v_emp_rec.ename);
        dbms_output.put_line(v_emp_rec.sal);
        dbms_output.put_line(v_result);
        dbms_output.put_line(v_emp_rec.deptno);
        dbms_output.put_line(v_dname);
       
        exception when NO_DATA_FOUND then dbms_output.put_line('값이 없습니다.');
end;
       
execute proc8(7844);
       
 
-------------------------------------------------------------------------------------       
--while문 연습
declare
    n number(4):=0;
begin
    loop
        dbms_output.put_line(n);
        n:=n+1;
        if n>=5 then
            exit;
        end if;
    end loop;
end;
 
 
--------------------------------------------
--for문 연습
declare
begin
    for i in 1..5 loop
        dbms_output.put_line(i);
    end loop;
end;
 
 
----------------------------------------------------------
--is table of 사용해보기 => map<Integer , varchar2(10)> 라고생각하면 쉽다
create or replace procedure getdept(dcode emp.deptno%type)
is
    type emp_table_ename_type is table of emp.ename%type
    index by PLS_INTEGER;
    vemp_ename emp_table_ename_type;
   
    begin
        select ename bulk collect into vemp_ename
        from emp
        where deptno=dcode;
       
        for i IN vemp_ename.first .. vemp_ename.last LOOP
             dbms_output.put_line(vemp_ename(i));
        END LOOP;
       
    end;
 
execute getdept(10);
 
-----------------------------------------------------------------------
--문제
/*
특정부서 정보 다뽑기
exec getdept01(10);
exec getdept01(20);
*/
 
create or replace procedure getdept01(getdept emp.deptno%type)
is
    type emp_table_type is table of emp%rowtype
    index by pls_Integer;
    emp_row emp_table_type;
   
    begin
        select * bulk collect into emp_row
        from emp
        where deptno=getdept;
   
    for i in emp_row.first .. emp_row.last loop
        dbms_output.put_line(emp_row(i).empno);
        dbms_output.put_line(emp_row(i).ename);
        dbms_output.put_line(emp_row(i).sal);
        dbms_output.put_line(emp_row(i).hiredate);
    end loop;
   
    exception when VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('데이터 없음!');
end;
 
execute getdept01(100);
 
------------------------------------------------------------------------
create or replace procedure getdept02(a number)
is
    TYPE emp_rec_type is record(empno emp.empno%type
                           ,ename emp.ename%type
                           ,job emp.job%type
                           ,sal emp.sal%type);
   
    TYPE emp_table_type is table of emp_rec_type
    index by pls_integer;
   
    emp_row emp_table_type;
   
    begin
        select empno, ename, job, sal bulk collect into emp_row
        from emp
        where deptno=a;
       
        for i in emp_row.first .. emp_row.last loop
            dbms_output.put_line(emp_row(i).empno);
            dbms_output.put_line(emp_row(i).ename);
            dbms_output.put_line(emp_row(i).job);
            dbms_output.put_line(emp_row(i).sal);
        end loop;
    end;
           
execute getdept02(10);
 
 
---------------------------------------------------------------------------
--매개변수 in(받는 변수), out(출력 변수)
create or replace procedure proc_emp(
                                vempno in emp.ename%type
                                ,vename out emp.ename%type
                                ,vsal out emp.sal%type
                                )
is
    begin
        select ename, sal into vename, vsal
        from emp
        where empno=vempno;
        end;
   
    variable v_name varchar2(20);
    variable v_sal number;
   
    execute proc_emp(7844,:v_name,:v_sal);
   
    print v_name;
    print v_sal;